抱歉,您的浏览器无法访问本站
本页面需要浏览器支持(启用)JavaScript
了解详情 >

TAOG's Blog


Loading...

介绍

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

MySQL连接

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

SQL语句书写规范

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

数据库指令

查看数据库

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

创建数据库

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

使用数据库

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


数据表指令

创建数据表

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

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

[字段类型]

常用的字段类型有

类型 最大长度 描述
INT 2147483647 存储整形
BIGINT 9223372036854775807 存储长整形
FLOAT 存储浮点型
CHAR 255 存储字符串,固定长度,比VARCHAR效率高
VARCHAR 65535 Byte 存储字符串,动态调整长度,效率不如VARCHAR
DATE 1000-01-01 ~ 9999-12-31 存储日期
BLOB 存储二进制大对象(图片,视频等流媒体信息
CLOB 存储字符大对象,可以存储好长好长好长…的字符串

注: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 [ 数据表名]

这里就写字段所在的表名

同时查询多张表

多表连接

[分组函数]

分组函数 作用
sum() 求和
avg() 求平均值
max() 球最大值
min() 求最小值
count() 计算个数

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

例如:

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

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

WHERE [查询条件]

需用到比较符

比较符 作用
= 等于
<=> 安全的等于
<>或者!= 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于
IS NULL或ISNULL 判断一个值是否为空
IS NOT NULL 判断一个值是否不为空
BETWEEN AND 判断一个值是否落在两个值之间

例如:

取成绩(一个字段)大于等于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%';

添加多个条件

条件 作用
AND 满足所有条件时才会被查询出来
OR 满足其中一个条件时就会被查询出来
XOR 记录满足其中一个条件,并且不满足另一个条件时,才会被查询出来

例如:

取学生成绩在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可视化工具

可使用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)
- 还有部分来源于网络

评论