MySQL-基础语句

此文为我学习MySQL基本语句时所记录的笔记

打开DOS输入 mysql -u root -p，之后输入密码

• SQL语句由;结尾
• SQL语句不区分大小写(但为了方便理解，一般关键字大写，数据库名、表名、字段名小写。插入表中的数据是区分大小写的)
• 插入数据中如有字符串需要由单引号' '包住
• SQL语句中的所有符号都应使用半角符号
• 表名的开头建议以t_或tbl_开头，这是为了便于区分数据库中表与其它数据

查看数据库

SHOW DATEBASES; 查看或显示当前用户权限范围以内的数据库

创建数据库

CREATE DATEBASES [数据库名]; 创建一个数据库

使用数据库

USE [数据库名]; 使用一个数据库
如果出现[数据库名] changed 则表明选择数据库成功

创建数据表

1
2
3
4

CREATE TABLE [数据表名](
　　[字段名] [字段类型] [字段约束]
　　......
)engine [存储引擎] charest [字符编码];

其中，非必须填写的有
[字段约束]，engine [存储引擎]，charest [字符编码]

[字段类型]

常用的字段类型有

注：INSERT语句无法插入数据到BLOB类型与CLOB类型，需要用到程序中的流来处理

该使用哪种字符串类类型

　　- CHAR 可以放一些固定长度的数据，如性别(男/女)等
　　- VARCHAR 可以动态调整长度，非固定的数据，如姓名(可能有两字/三字/四字等)
　　- CLOB 可以放甚至4个G的字符串，一般放较长的字符串进去
　　- CHAR每个英文字符占用1字节，中文字符占用2字节
　　- VARCHAR每个字符都占用2字节

设置字段显示宽度

可见->这里

[字段约束]

NOT NULL 非空约束

可以保证该字段不等于Null

DEFAULT 默认值约束

可以使该字段拥有默认值，而不是Null

UNIQUE 唯一约束

可以使字段具有唯一性，数据不会重复

PRIMARY KEY 主键约束

即可以使该字段不为空，又可以使该字段的数据不会重复，一个表中只能设置一个主键约束

FOREIGN KEY 外键约束

在多个表的情况下使用，可以保证该字段的数据来自于被关联的列的值，被关联的字段必须设置了主键约束
　　- 比如students表中的class字段(表示学生的班级)与classes表中的classNum字段(表示班级代号)关联，其中classNum列中只有三条数据，101，102，103，那么class字段的值只能是这三者之一，不可能是104，105……

engine [存储引擎]

默认为INNODB
还有MYISAM，MEMORY等许多引擎，一般这三个用的最多
它们的区别可见->这里

charest [字符编码]

默认为UTF-8
还有GBK等，这里不多写

查看数据表结构

desc [数据表名];
可以查看该数据表的结构

删除数据表

注意:删的是数据表而不是数据

使用 DROP TABLE [数据表名]; 可以删除一个数据表

查询语句

SELECT [字段名] FROM [数据表名] WHERE [查询条件] GROUP BY [分组依据] HAVING [分组过滤] ORDED BY [查询排序] LIMIT [分页查询条数]; 这是一个完整的查询语句

SELECT [字段名] FROM [数据表名];这是一个最基本的查询语句，里面都是必选项

[字段名]

就是要查询的字段，如果需要查询全部字段，可以用*代替

查询多个字段，字段名之间应该用,隔开

为查询的字段设置别名

[字段名] as [别名] 可以进一步省略为 [字段名] [别名]

如果设置了别名，查询结果中该字段将显示为别名

FROM [ 数据表名]

这里就写字段所在的表名

同时查询多张表

见多表连接

[分组函数]

以上分组函数会自动过滤Null值

例如:

查询全部学生分数的平均值:SELECT avg(studentScores) '平均分数' FROM t_students;

注意:分组函数不可在WHERE中使用，它常常与GROUP BY配合使用，所以叫分组函数

WHERE [查询条件]

需用到比较符

例如:

取成绩(一个字段)大于等于90分的学生数据 SELECT * FROM t_students WHERE studentScore >= 90;

取学生成绩在70分到80分之间的数据(使用BETWEEN AND)SELECT * FROM t_students WHERE studentScore BETWEEN 70 AND 80;

LIKE模糊查询

LIKE 关键字支持百分号%和下划线_通配符，%表示可能有多个字符，_表示只有一个字符，当只知道数据中的某个字符时，LIKE关键字就比较方便

例如:

取所有学生名字(一个字段)由Y开头的学生名字SELECT studentName FROM t_students WHERE studentName LIKE 'Y%';

添加多个条件

例如:

取学生成绩在70分到80分之间的数据(使用< 与 >)SELECT * FROM t_students WHERE studentScore > 70 AND studentScore < 80;

GROUP BY [分组依据]

注意:当一条SQL语句没有GROUP BY的话，整张表的数据会自成一组

这里可以填写一个字段，查询结果会以这个字段进行分组

注意:查询的字段只能是参与排序的字段、分组函数或拥有主键约束的字段，否则查询结果将没有任何意义(在非MySQL数据库会直接报错)

例如:

查询所有班级(并以班级为依据进行分组)SELECT studentClass FROM t_students GROUP BY studentClass;

GROUP BY与分组函数的搭配使用

例如:

求每个班级的最高成绩SELECT studentClass,max(studentScore) FROM t_students GROUP BY studentClass;

HAVING [分组过滤]

与WHERE高度相似，但WHERE是在GROUP BY之前执行，而HAVING是在GROUP BY之后执行

考虑到效率问题，能在WHERE中解决的就不要在HAVING中解决

注意:与WHERE不同的是，HAVING可以使用分组函数

例如:

查询所有班级的最高分数，其中平均分低于70分的班级不查询 SELECT studentClass,max(studentScore) FROM t_students GROUP BY studentClass HAVING avg(studentScore) > 70;

ORDED BY [查询排序]

可以填写一个字段，查询结果会以这个字段为依据进行排序

默认为升序(ASC)，也可以设置为降序(DESC)

例如:

查询所有学生，并以学生ID的降序进行排序 SELECY studentName,studentID FROM t_students ORDED BY studentID DESC;

多字段排序

可以在ORDED BY后面填写多个字段，它将会按照先后顺序逐个进行排序

LIMIT [分页查询条数]

注意:这个比较重要

LIMIT 关键字可以指定查询结果从哪条记录开始显示，显示多少条记录

可以这样写 LIMIT [起始位置], [记录条数]

注意:[起始位置] 与 [记录条数]都应该是正整数。并且[起始位置]是基零的(第一条数据就是0)。[起始位置]可以省略，默认0

这里有个公式[起始位置], [记录条数] = (pageNO - 1) * pageSize, pageSize

其中 pageNO为显示第几页，pageSize为每页显示多少条记录

执行顺序

以下是MySQL中SELECT语句的执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT	5
...
FROM	1
JOIN		(1)
ON			(2)
...
WHERE	2
... 
GROUP BY	3
... 
HAVING	4
...
ORDER BY	6
...
LIMIT	7
...

多表查询

INNER JOIN 内连接

注意:内连接中的表是同级关系。INNER 关键字可以省略

例如:
查询所有学生与班级 SELECT studentName,claasName FROM t_students JOIN t_classes;

但结果是这样的，并没有什么意义

1
2
3
4
5
6
7
8
9
10
11
12
13

+-------------+------------+
| studentName | className  |
+-------------+------------+
| 张三        | 一年级一班 |
| 张三        | 一年级二班 |
| 张三        | 一年级三班 |
| 李四        | 一年级一班 |
| 李四        | 一年级二班 |
| 李四        | 一年级三班 |
| 王五        | 一年级一班 |
| 王五        | 一年级二班 |
| 王五        | 一年级三班 |
+-------------+------------+

为表设置别名

与为字段设置别名一样，查询的字段需要这样写[表的别名].[字段名]

例如:

查询所有学生与班级SELECT stu.studentName,cla.className FROM t_students stu JOIN t_classes cla;

ON 关键字

ON 关键字就是连接条件

例如:

查询学生与对应的班级名称 SELECT stu.studentName,cla.className FROM t_students stu JOIN t_classes cla ON stu.studentClass = cla.classID;(studentClass与classID为班级代号，例如101，102……)

外连接

外连接与内连接不同的地方在于:

内连接不满足连接条件的数据都不会在查询结果中显示

外连接如果不满足条件则会以NULL表示

​ - 左外连接LEFT JOIN

以左边的表为基准，当左表不符合连接条件时则会以NULL表示，而不是舍去

​ - 右外连接RIGHT JOIN

与左外连接相反

去重

使用 distinct 关键字，这个关键字应放在SELECT的后面，所有 [字段名] 的前面

这个关键字可以去除查询结果中数据完全一样的数据

子查询

子查询就是将一个查询结果放在另一个SELECT查询语句中，即为嵌套

WHERE子查询

把子查询放在WHERE关键字后面

例如:查询成绩高于平均成绩的学生信息

第一步:

查询平均成绩 SELECT avg(studentScore) FROM t_students; 记住这个结果

第二步:

SELECT * FROM t_students WHERE studentScore > [第一步的结果];

使用子查询:

SELECT * FROM t_students WHERE studentScore > (SELECT avg(studentScore) FROM t_students);

注意:别忘了WHERE关键字后面不可以使用分组函数

FROM子查询

把子查询放在FROM关键字后面，可以将子查询结果当成一张新表来查询

例如:

首先，取每个班的平均成绩 SELECT studentClass,avg(studentScore) scores FROM t_students GROUP BY studentClass;

最后联合起来，以上面的语句为子查询，查询每个班级的平均成绩并划分等级 SELECT sco.studentClass,sco.scores,gra.grade FROM t_grade gra JOIN (SELECT studentClass,avg(studentScore) scores FROM t_students GROUP BY studentClass) sco ON sco.scores BETWEEN gra.minScore AND gra.maxScore;

注意:假设等级是这样的一张表

1
2
3
4
5
6
7

+-------+----------+----------+
| grade | minScore | maxScore |
+-------+----------+----------+
| A     |       80 |      100 |
| B     |       70 |       80 |
| C     |        0 |       60 |
+-------+----------+----------+  // 100-80分为A,70-60分为B,60分以下为C

SELECT 子查询

这里不多写，就是把子查询放到SELECT关键字的后面

插入语句

INSERT INTO [数据表名]([字段1名,字段2名......]) VALUES ([插入值1,插入值2......]);

这是一个完整的插入语句，可以进一步简写为

INSERT INTO [数据表名] ([插入值1,插入值2......]);

[字段名]

这里填写要插入数据的字段名，如需插入到全部字段，可以省略

注意:如果不插入某一个字段，那么那一个字段将以默认值或NULL代替

[插入值]

这里填写要插入的数据，如果是字符串，则需要加单引号' '

需要与[字段名]一一对应，如果省略了[字段名]，那么需要按照表中设置字段的顺序来填写

删除语句

DELETE FROM [数据表名] WHERE [删除条件];，这是一个删除语句

后面还可加[ORDED BY] [LIMIT]等，但在这不写

[删除条件]

和SELECT语句中的WHERE一样，在这里，符合条件的数据将被删除

注意:如果不写[删除条件]，那么整张表的数据都会被删掉

修改语句

UPDATE [数据表名] SET [字段名1 = 新值1],[字段名2 = 新值2......] WHERE [修改条件];，这是一个修改语句

与删除语句一样，后面还可加[ORDED BY] [LIMIT]等，但在这不写

[字段名 = 新值]与[修改条件](还是WHERE)也不写

事务的特性

事务有4个特性，分别是

- 原子性(事务中各元素不可再分，事务中的所有元素必须作为一个整体提交或回滚)
- 一致性(当事务完成时，数据必须处于一致状态)
- 隔离性(对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的)
- 持久性(事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的)

事务隔离

MySQL中，事务的隔离等级一共有4个，重低到高分别是

• 读未提交(当一个事务还未提交时就可以访问它的数据。问题:可能出现脏读)
• 读已提交(当一个事务提交后才可以访问它数据。问题:不可重复读)
• 可重复读(问题:可能出现幻读)
• 可串行化(相当于单线程，它解决了以上三个问题，但效率较低)

具体的还可见->这里

事务的作用

事务适合处理处理操作量大，复杂度高的数据

事务具有原子性，当事务中一条语句出错时，其它的语句将不会生效

例如:
事务处理机制在程序开发过程中有着非常重要的作用，它可以使整个系统更加安全，例如在银行处理转账业务时，如果A账户中的金额刚被发出，而B账户还没来得及接收就发生停电，这会给银行和个人带来很大的经济损失。采用事务处理机制，一旦在转账过程中发生意外，则程序将回滚，不做任何处理。

执行事务

启动事务

默认情况下，事务是自动提交的

可使用START TRANSACTION;来手动启动一个事务

提交事务

可使用COMMIT;来手动提交一个事务

回滚事务

可使用ROLLBACK;来手动回滚一个事务

什么是索引

索引就想字典的目录一样，可以更快捷的获取数据的内容

如果在没有索引的情况下使用SELECT语句的话，就像在字典中寻找一个字而不用目录，一页一页的翻总会翻到，但是很慢

如果有了索引就不一样了

索引的原理

比较复杂，与Java中的Map类似

适用的场景

- 数据量庞大
- 该字段很少DML操作
- 该字段经常出现在`WHERE`语句中

查看执行计划

使用EXPLAIN [SQL语句];可输出该SQL语句的执行计划

创建索引

注意:具有主键约束或唯一约束的字段会自动添加索引

使用 CREATE INDEX [索引名] ON [数据表名]([字段名]); 可以根据[数据表名]中的[字段名]字段来创建一个索引

删除索引

使用 DROP INDEX [索引名称] ON [数据表名]; 可以删除一个索引

什么是视图

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

注意:修改视图的数据也会影响到数据表中的数据

创建视图

使用 CREATE VIEW [视图名] AS [SELECT语句] 可以创建一个视图

*注意:SELECT*语句中不可以包含子查询

[SELECT语句]

执行此语句就是查询视图的结果

导入

使用 SOURCE [.sql文件的路径] 可以导入一个.sql文件

导出

使用 MYSQIDUMP-u [用户名] -p [数据库名] > [导出的文件名] 可以导出整个数据库

使用 MYSQIDUMP -u [用户名] -p [数据库名] [表名] > [导出的文件名] 可以导出单张数据表

可使用mysql-front

可前往->这里获取下载地址

- [C语言中文网](http://c.biancheng.net/mysql/)
- [动力节点视频](https://b23.tv/TmGf9I)
- [MySQL—内连接和外连接区别_johnhan9的博客-CSDN博客_mysql内连接和外连接的区别](https://blog.csdn.net/johnhan9/article/details/88686288)
- [mysql数据库导入导出文件sql文件_Allen . Liu-CSDN博客_mysql导出导入sql文件](https://blog.csdn.net/qq_41856814/article/details/114481099)
- [mysql系列教材 (how2j.cn)](https://how2j.cn/k/mysql/mysql-install/377.html)
- 还有部分来源于网络