介绍
MySQL连接
打开DOS输入 mysql -u root -p
,之后输入密码
SQL语句书写规范
- SQL语句由
;
结尾 - SQL语句不区分大小写(但为了方便理解,一般关键字大写,数据库名、表名、字段名小写。插入表中的数据是区分大小写的)
- 插入数据中如有字符串需要由单引号
' '
包住 - SQL语句中的所有符号都应使用半角符号
- 表名的开头建议以
t_
或tbl_
开头,这是为了便于区分数据库中表与其它数据
数据库指令
查看数据库
SHOW DATABASES;
查看或显示当前用户权限范围以内的数据库
创建数据库
CREATE DATABASES [数据库名];
创建一个数据库
使用数据库
USE [数据库名];
使用一个数据库
如果出现[数据库名] changed
则表明选择数据库成功
数据表指令
创建数据表
1 | CREATE TABLE [数据表名]( |
其中,非必须填写的有
[字段约束],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 | SELECT 5 |
多表查询
INNER JOIN
内连接
注意:内连接中的表是同级关系。INNER
关键字可以省略
例如:
查询所有学生与班级 SELECT studentName,claasName FROM t_students JOIN t_classes;
但结果是这样的,并没有什么意义
1 | +-------------+------------+ |
为表设置别名
与为字段设置别名一样,查询的字段需要这样写[表的别名].[字段名]
例如:
查询所有学生与班级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 | +-------+----------+----------+ |
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)
- 还有部分来源于网络