mysql sql语句大全 mysql sql语句大全
一 、常用操作数据库的命令
1.查看所有的数据库 : show databases;
2.创建一个数据库 : create database if not exists 数据库名;
3.删除一个数据库 : drop database if exists 数据库名;
4.选择一张表 (注意在建表之前必须要选择数据库) : use `表名`;
* --tab 键的上面，如果你的表名或字段名是一个特殊字段符，就需要带 `` *
5.在选中的数据库之中查看所有的表 : show tables;
6.查看创建数据库的语句 ：show create database 数据库名;
7.查看student数据表的定义语句 ：show create table 表名;
8.显示表的结构 ：desc 表名;
9.删除表 : drop table 表名;
10.查看创建库的详细信息 ：show create database 库名;
11.查看创建表的详细信息 : show create table 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
二、建一张表
– 目标:创建一个schoo1数据库
– 创建学生表(列,字段)使用SQL 创建
– 学号int 登录密码varchar(20)姓名,性别varchar(2),出生日期(datatime)，家庭住址，emai1
分析：
id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
id ：字段名 （要加个票 Tab键下面的 ）
INT(4)：类型(长度)
NOT NULL：不为空
AUTO_INCREMENT：自增
COMMENT ‘xxx’：注释 （这里 ’ ’ 是回车键隔壁的那个 ‘ ）
DEFAULT ‘xxx’：默认值xxx
-- 注意点，使用英文()，表的名称 和 字段 尽量使用 `` (Tab键下面的) 括起来
-- AUTO_ INCREMENT 自增
-- 字符串使用单引号括起来!
-- 所有的语句后面加，(英文的)，最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键!
CREATE TABLE IF NOT EXISTS `student2`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARBINARY(100) DEFAULT NULL COMMENT'家庭住址',
`email` VARBINARY(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY(`id`) --设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8 --设置编码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
格式
CREATE TABLE [IF NOT EXISTS] `表名`（
`字段名` 列类型[属性][索引][注释],
`字段名` 列类型[属性][索引][注释],
`字段名` 列类型[属性][索引][注释] (最后一句可以不要逗号)
）[表类型][表的字符集设置][注释]``
1
2
3
4
5
常用命令
SHOW CREATE DATABASE school – 查看创建数据库的语句
SHOW CREATE TABLE student – 查看student数据表的定义语句
DESC student – 显示表的结构
三、修改表的命令
3.1 修改
修改
1. 修改表名: alter table 旧表名 rename as 新表名
例子：alter table teacher rename as teacher1
2. 增加表的字段: alter table 表名 add 表字段名 字段类型
例子：alter table teacher1 add age int(11)
3. 修改表的字段的类型
--alter table 表名 modify 字段名 字段类型[ ]
例子：alter table teacher1 moify age varchar(11) -- 修改约束(例如：由int修改为varchar类型)
4. 修改指定的字段名
--alter table 表名 change 旧名字 新名字 字段类型[ ]
例子：alter table teacher1 change age age1 int(1) -- 字段重命名
5. 删除表的字段 : alter table 表名 deop 字段名
例子：alter table teacher1 drop age1
6. 添加字段并指定位置 alter table 表名 add 字段 字段类型 after 字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
3.2 删除
删除
1. 删除表（如果表存在再删除）
例子：drop table if exists teacher1
1
2
3
4
四、DML 数据库操作语言（重要）
4.1 添加
添加 insert
语法：
insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……) values (`值1`,`值2`,`值3`, ……)
1
2
还可以同时插入多条数据，VALUES后面的值需要使用，隔开即可
语法：
insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……) values (`值1`,`值2`,`值3`, ……),(`值1`,`值2`,`值3`, ……),……
1
2
3
例子
-- 一般写插入语句，我们一定要数据和字段一一对应。
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');
--单独只插入一个字段
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaa','男')
--可以同时插入多条数据，VALUES后面的值需要使用，隔开即可
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES ('李四','aaaaa','男'),('王五','23232','女')
1
2
3
4
5
6
7
8
9
10
11
4.2 修改
修改：
update 修改谁（条件） set 原来的值=新值
语法：
update 表名 set `字段名1` = '值1' ,`字段名2` = '值2' … where [条件]
1
2
-- 修改学员名字
UPDATE `student` SET `name`='囷' WHERE id =1;
-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='233'
-- 语法；
-- UPDATE 表名 set column_name,[] = value where 条件
1
2
3
4
5
6
7
8
条件：where 子句 运算符 id 等于 某个值，大于某个值，在某个区间内修改
操作符返回布尔值
注意：
column_name 是数据库的列，带上
条件，是筛选的条件，如果没有指定，则会修改所有的列
value 是一个具体的值，也可以是一个变量
多个设置的属性之间，使用英文逗号隔开
UPDATE `student` SET `birthday`=CURRENT_TIME where `name`='李四' AND SEX = '男'
1
4.3 删除
delete 命令
语法 delete from 表名 [where 条件]
1
-- 删除数据 (避免这样写,会删除所有的数据)
DELETE FROM `student`
-- 删除指定
DELETE FROM `student` where id= 1
1
2
3
4
5
TRUNCATE 命令
作用：完全清空一个数据库，表的结构和索引不会变
delete 和 truncate 区别
相同点： 都能删除数据，都不会删除表结构
不同：
- TRUNCATE 重新设置自增列 计数器会归零
- TRUNCATE 不会影响事务
测试代码：
-- 测试delete 和 truncate 区别
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
了解即可：delete删除的问题 重启数据库，现象
innoDB 自增列会从1开始（存在内存当中，断电即失）
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）
五、DQL查询数据（最重点）
5.1 DQL
(Data Query Language) :数据查询语言
所有的查询操作都用它 Select（select 选择）
简单的查询，复杂的查询它都能做
数据库中最核心的语言，最重要的语句
使用频率最高的语言
5.2 指定查询字段
1. 查询全部的学生：select *(通配符) from 表名
例子：select * from student
2.查询指定字段：select `字段1`,（英文逗号隔开）`字段2`,… from 表
例子：select `studentNo` , `studentName` from student
3.别名，给查询结果表头起一个名字 as 可以给字段起别名，也可以给表起别名
例子：selsct `studentNo` as 学号 , `studentName` as 学生名字 from student as s
4.函数 Concat（a,b）给查出来的表添加字体
例子：select concat('姓名：' ,StudentName) as 新名字 from student
1
2
3
4
5
6
7
8
9
10
11
12
去重(将查询出来重复的数据去除掉)
语法：在select后面加上一个distinct
select distinct `字段名` from 表名
1
2
数据库的列 (表达式)
select 表达式 from 表
5.3where 条件子句
作用：检索数据中符合条件的值
逻辑运算符
运算符 语法 结果
and && a and b , a&&b 逻辑与,两个都为真，结果为真
or || a or b , a||b 逻辑或，其中一个为真，则结果为真
Not != not a ,!a 逻辑非，真为假，假为真！
尽量使用英文
题目：查询考试成绩在95分到100分之间
方式一：
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100
方式二：
-- 模糊查询（区间）
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
方式三：
-- 除了100分学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult != 100
方式四： != not
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentResult = 100
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
模糊查询：比较运算符
运算符 语法 描述
IS NULL a is null 如果操作符为null 结果为真
IS NOT NULL a is not null 如果操作符为not null 结果为真
BETWEEN a between b and c 若a在b 和 c之间则为真
LIKE a like b SQL匹配，如果a 匹配到b 则为真
IN a in (a1,a2,a3…) 假设a 在 a1,a2,a3其中的某一个中，为真
==================like （% 和 _ 只能用在like中）===========================
-- 查询姓刘的同学
-- like结合 %（代表0到任意字符） _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';
-- 查询姓刘的同学，名字后只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';
-- 查询姓刘的同学，名字后只有两个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';
-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';
===================IN(具体的一个或者多个值)===========================
-- 查询1001 1002 1003 学员信息
之前的方法：
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003
用in来操作的方法：
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京的学生（注意in查询的是一个具体的值才能查询出来）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛阳');
===================NULL NOT NULL===================================
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL
-- 查询有出生日期的同学 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
5.4 联表查询
JOIN 对比
操作 描述
Inner join 如果表中至少有一个匹配，就返回行
left join 即使左表中没有匹配，也会从左表中返回所有的值
right jion 即使右表中没有匹配，也会从右表中返回所有的值
两表查询
语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1 as(as可以省略) 别名1
(inner\left\right) join 表2 as 别名2
(where\on) 别名1.共同字段 = 别名2.共同字段
1
2
3
4
5
/*
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo
-- JION（表） ON （判断的条件）连接查询
-- where 等值查询
SELECT studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo
--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo
--LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
有条件的联表查询
语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1 as(as可以省略) 别名1
(inner\left\right) join 表2 as 别名2
on 别名1.共同字段 = 别名2.共同字段
where 条件
and 条件2 and 条件3……
1
2
3
4
5
6
7
查询没有考试的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL
1
2
3
4
5
6
7
三表或多表查询
语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1 别名1
(inner\left\right) join 表2 别名2
on 别名1.共同字段1 = 别名2.共同字段1
(inner\left\right) join 表3 别名3
on 别名1.共同字段2 = 别名3.共同字段2
1
2
3
4
5
6
7
8
查询了参加考试同学的信息：学号：学生姓名：科目名：分数
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
-- 我要查询哪些数据 SELECT ....
-- 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
-- 假设存在一中多张表查询，先查询两章表，然后再慢慢增加
FROM a LEFT JOIN b 左为准
FROM a RIGHT JOIN b 右为准
1
2
3
4
5
6
7
8
9
10
11
12
13
14
5.5 分页和排序
============================分页 limit 和排序order by=================
-- 排序： 升序ASC 降序 DESC
SELECT xx
FROM xx
JOIN xx
WHERE xx
ORDER BY xx
ASC || DESC
1
2
3
4
5
6
7
8
9
10
排序
语法：order by 通过那个字段排序 怎么排
order by 字段名 asc（升序）\desc（降序）
分页
语法： limit 起始项，每页的数量
-- 为什么要分页
-- 缓解数据库压力，给人的体验更好
-- 分页，每页显示五条数据
-- 语法： limit 当前页，页面的大小
-- limit 0,5 1-5
-- limit 1,5 1-5
-- limit 6,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
WHERE subjectName='数据结构-1'
ORDER BY StudentResult ASC
LIMIT 0,5
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit 5*（n-1）,5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
5.6 子查询
where（这个值是计算出来的，即不确定的），之前的where后面是跟一个固定的值
本质：在where语句中嵌套一个子查询语句
where （select * from）
解析：where 后加( ) 括号里面放新的查询语句
-- ===========================where=========================
-- 1.查询 数据库结构-1的所有考试结构（学号，科目编号，成绩） 降序
-- 方式一： 连接查询
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
-- 方式二：使用子查询(由里及外)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC
-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80
-- 在这个基础上 增加一个科目 ，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
SELECT Subject FROM `subject`
WHERE SubjectName='高等数学-2'
)
-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等数学-2'
WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80
-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2'
)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
六、MySQL函数
6.1 常用函数
数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 返回字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我爱编程helloword',1,2,'超级热爱') -- 从某个位置开始替换某个长度 ：1为替换的开启下标，2为替换的字字符的长度 结果为：超级热爱编程helloword
SELECT UPPER('Abc') --小写字母
SELECT LOWER('Abc') --大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现的字符串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6) --返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('清晨我上马') -- 反转
-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
6.2 聚合函数（常用）
函数名称 描述
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
… …
分组
group by
分组后有条件判断用
naving 条件
6.3 数据库级别MD5加密（拓展）
什么是MD5
主要增强算法复杂度不可逆性。
MD5不可逆，具体的MD5是一样的
MD5破解原理，背后有一个字典，MD5加密后的值，加密前的值
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1 -- 加密全部
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'红',MD5('123456'))
-- 如何校验，将用户传递过来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
明码
MD5加密后
插入数据的时候就加密：
————————————————
版权声明：本文为CSDN博主「橙子的胖胖」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_52211542/article/details/120240210
