一、SQL的定义与作用
- SQL定义：,Structured Query Language结构化查询语言
- SQL作用:
- 是一种所有关系型数据库的查询规范，不同的数据库都支持。
- 通用的数据库操作语言，可以用在不同的数据库中。
- 不同的数据库 SQL 语句有一些区别
网络异常，图片无法展示|
二、SQL 语句分类
- Data Definition Language (DDL 数据定义语言) 如：建库，建表
- Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改
- Data Query Language(DQL 数据查询语言)，如：对表中的查询操作
- Data Control Language(DCL 数据控制语言)，如：对用户权限的设置
三、SQL语法
3.1 大小写区分与注释
- 每条语句以分号结尾，如果在 SQLyog 中不是必须加的。
- SQL 中不区分大小写，关键字中认为大写和小写是一样的
- 3 种注释：
网络异常，图片无法展示|
3.2DDL 操作数据库
3.2.1 创建数据库的几种方式
#创建数据库 CREATE DATABASE 数据库名; #判断数据库是否已经存在，不存在则创建数据库 CREATE DATABASE IF NOT EXISTS 数据库名; #创建数据库并指定字符集 CREATE DATABASE 数据库名 CHARACTER SET 字符集;
具体操作：
-- 直接创建数据库 db1 create database db1; -- 判断是否存在，如果不存在则创建数据库 db2 create database if not exists db2; -- 创建数据库并指定字符集为 gbk create database db3 default character set gbk;
3.2.2 查看数据库
#按 tab 键可以自动补全关键字 #查看所有的数据库 show databases; #查看某个数据库的定义信息 show create database db3;
3.2.3 修改数据库
#修改数据库默认的字符集 ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
具体操作：
#将 db3 数据库的字符集改成 utf8 alter database db3 character set utf8;
3.2.4 删除数据库
# 删除数据库的语法 DROP DATABASE 数据库名;
具体操作
# 每行SQL语句需要选中再执行，可以按 F9 # 删除 db2 数据库 drop database db2;
3.2.5 使用数据库
# 查看正在使用的数据库 SELECT DATABASE(); --使用的一个 mysql 中的全局函数 # 使用/切换数据库 USE 数据库名;
3.3 DDL操作表结构
前提先使用某个数据库
3.3.1 创建表
# 创建表的格式： CREATE TABLE 表名 ( 字段名 1 字段类型 1, 字段名 2 字段类型 2);
关键字说明：
网络异常，图片无法展示
|
3.3.2 MySQL 数据类型
常使用的数据类型如下：
网络异常，图片无法展示
|
详细的数据类型如下:
网络异常，图片无法展示
|
具体操作:
# 创建student表包含id,name,birthday字段 create table student ( id int, -- 整数 name varchar(20), -- 字符串 birthday date -- 生日，最后没有逗号 );
3.3.3 查看表
# 查看某个数据库中的所有表 SHOW TABLES; # 查看表结构 DESC 表名; # 查看创建表的SQL语句 SHOW CREATE TABLE 表名;
具体操作：
# 查看day21数据库中的所有表 use day21; show tables; # 查看student表的结构 desc student; 执行结果：  # 查看 student 的创建表 SQL 语句 show create table student;
执行结果：
CREATE TABLE `student` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `birthday` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
存在的目的是为了避免关键字的冲突
3.3.4 快速创建一个表结构相同的表
语法 ： CREATE TABLE 新表名 LIKE 旧表名;
具体操作：
# 创建 s1 表，s1 表结构和 student 表结构相同 create table s1 like student; desc s1;
3.3.5 删除表
# 直接删除表 DROP TABLE 表名; # 判断表是否存在，如果存在则删除表 DROP TABLE IF EXISTS 表名;
具体操作：
# 直接删除表 s1 表 drop table s1; # 判断表是否存在并删除s1表 drop table if exists `create`;
注：与直接删除的区别 如果表不存在，不删除，存在则删除
3.3.6 修改表结构
- 1.添加表列 ADD
ALTER TABLE 表名 ADD 列名 类型;
举例：为学生表添加一个新的字段 remark,类型为 varchar(20)
alter table student add remark varchar(20);
- 2.修改列类型 MODIFY
ALTER TABLE 表名 MODIFY 列名 新的类型;
举例：将 student 表中的 remark 字段的改成 varchar(100)
alter table student modify remark varchar(100);
- 3．修改列名 CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
举例:将 student 表中的 remark 字段名改成 intro，类型 varchar(30)
alter table student change remark intro varchar(30);
- 4．删除列 DROP
ALTER TABLE 表名 DROP 列名;
删除 student 表中的字段 intro
alter table student change remark intro varchar(30);
- 5．修改表名
RENAME TABLE 表名 TO 新表名;
举例：将学生表 student 改名成 student2
rename table student to student2;
- 6.修改字符集 character set
ALTER TABLE 表名 character set 字符集;
举例：将 student2 表的编码修改成 gbk
alter table student2 character set gbk;
3.4 DML 操作表中的数据
用于对表中的记录进行增删改操作
3.4.1 插入记录
- 插入记录
INSERT [INTO] 表名 [字段名] VALUES (字段值) -- INSERT INTO 表名：表示往哪张表中添加数据 -- (字段名 1, 字段名 2, …)：要给哪些字段设置值 -- VALUES (值 1, 值 2, …)：设置具体的值
1.插入全部字段
所有的字段名都写出来： INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3); 不写字段名 INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);
注：没有添加数据的字段会使用 NULL 具体操作:
# 向表中插入所有字段 -- 插入所有列 insert into student values (3, '孙悟饭', 18, '男', '龟仙人洞中'); -- 如果只插入部分列，必须写列名 insert into student values (3, '孙悟饭', 18, '男'); select * from student;
3.4.2 更新表记录
语法：
UPDATE 表名 SET 列名=值 [WHERE 条件表达式] # UPDATE: 需要更新的表名 # SET: 修改的列值 # WHERE: 符合条件的记录才更新 你可以同时更新一个或多个字段。 你可以在 WHERE 子句中指定任何条件。
- 不带条件修改数据
UPDATE 表名 SET 字段名=值; -- 修改所有的行
- 带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
具体操作：
# 不带条件修改数据，将所有的性别改成女 update student set sex = '女'; # 带条件修改数据，将 id 号为 2 的学生性别改成男 update student set sex='男' where id=2; #一次修改多个列，把 id 为 3 的学生，年龄改成 26 岁，address 改成北京 update student set age=26, address='北京' where id=3;
3.4.3 删除表记录
删除表记录语法
DELETE FROM 表名 [WHERE 条件表达式]
注：如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。 你可以在 WHERE 子句中指定任何条件
1.不带条件删除数据
DELETE FROM 表名;
1.带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
1.使用 truncate 删除表中所有记录
TRUNCATE TABLE 表名;
注：truncate 和 delete 的区别： truncate 相当于删除表的结构，再创建一张表。
具体操作：
# 带条件删除数据，删除 id 为 1 的记录 delete from student where id=1; # 不带条件删除数据,删除表中的所有数据 delete from student;
3.5 DQL 查询表中的数据
查询不会对数据库中的数据进行修改.只是一种显示数据的方式 查询表中数据语法：
SELECT 列名 FROM 表名 [WHERE 条件表达式] # SELECT 命令可以读取一行或者多行记录。 # 你可以使用星号（*）来代替其他字段，SELECT 语句会返回表的所有字段数据 # 你可以使用 WHERE 语句来包含任何条件。
3.5.1 简单查询
1.查询表所有行和列的数据 使用*表示所有列
SELECT * FROM 表名;
举例：查询所有的学生：
select * from student;
1.查询指定列
查询指定列的数据,多个列之间以逗号分隔
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
查询 student 表中的 name 和 age 列
select name,age from student;
3.5.2 指定列的别名进行查询
1) 使用关键字 AS
使用别名的好处： 显示的时候使用新的名字，并不修改表的结构。
3） 语法： 对列指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
对列和表同时指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
具体操作：
# 使用别名 select name as 姓名,age as 年龄 from student; # 表使用别名 select st.name as 姓名,age as 年龄 from student as st --表使用别名的原因：用于多表查询操作
3.5.3 清除重复值
# 查询指定列并且结果不出现重复数据 SELECT DISTINCT 字段名 FROM 表名;
具体操作：
#查询学生来至于哪些地方 select address from student; # 去掉重复的记录 select distinct address from student;
3.5.4 查询结果参与运算
1.某列数据和固定值运算
SELECT 列名 1 + 固定值 FROM 表名;
1.某列数据和其他列数据参与运算
SELECT 列名 1 + 列名 2 FROM 表名;
注意: 参与运算的必须是数值类型 具体实现： 准备数据：添加数学，英语成绩列,给每条记录添加对应的数学和英语成绩，查询的时候将数学和英语的成绩相加 实现：
select * from student; # 给所有的数学加 5 分 select math+5 from student; # 查询 math + english 的和 select * from student; select *,(math+english) as 总成绩 from student; # as 可以省略 select *,(math+english) 总成绩 from student;
3.5.5 条件查询
1.为什么要条件查询? 如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤。 1.条件查询的语法
SELECT 字段名 FROM 表名 WHERE 条件;
流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回.
准备数据 创建一个学生表，包含如下列：
CREATE TABLE student3 ( id int,-- 编号 name varchar(20), -- 姓名 age int, -- 年龄 sex varchar(5), -- 性别 address varchar(100), -- 地址 math int, -- 数学 english int -- 英语 ); INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'xx',55,'男',' 杭州',66,78);
运算符****
网络异常，图片无法展示
|
具体操作：
# 查询 math 分数大于 80 分的学生 select * from student3 where math>80; # 查询 english 分数小于或等于 80 分的学生 select * from student3 where english <=80; # 查询 age 等于 20 岁的学生 select * from student3 where age = 20; # 查询 age 不等于 20 岁的学生，注：不等于有两种写法 select * from student3 where age <> 20; select * from student3 where age != 20;
逻辑运算符****
网络异常，图片无法展示
|
具体操作：
# 查询 age 大于 35 且性别为男的学生(两个条件同时满足) select * from student3 where age>35 and sex='男'; # 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足) select * from student3 where age>35 or sex='男'; # 查询 id 是 1 或 3 或 5 的学生 select * from student3 where id=1 or id=3 or id=5;
in 关键字
SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
in 里面的每个数据都会作为一次条件，只要满足条件的就会显示具体操作：****
# 查询 id 是 1 或 3 或 5 的学生 select * from student3 where id in(1,3,5); # 查询 id 不是 1 或 3 或 5 的学生 select * from student3 where id not in(1,3,5);
范围查询****
BETWEEN 值 1 AND 值 2
表示从值 1 到值 2 范围，包头又包尾
比如：age BETWEEN 80 AND 100 相当于： age>=80 && age<=100
查询 english 成绩大于等于 75，且小于等于 90 的学生
select * from student3 where english between 75 and 90;
like 关键字:LIKE 表示模糊查询
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
MySQL 通配符
网络异常，图片无法展示
|
# 查询姓马的学生 select * from student3 where name like '马%'; select * from student3 where name like '马'; # 查询姓名中包含'德'字的学生 select * from student3 where name like '%德%'; # 查询姓马，且姓名有两个字的学生 select * from student3 where name like '马_';、
3.5.6 排序
通过ORDER BY子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序) 。
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC]; # ASC: 升序，默认值 # DESC: 降序
1.单列排序 单列排序： 只按某一个字段进行排序，单列排序。
# 查询所有数据,使用年龄降序排序 select * from student order by age desc;
- 组合排序
什么是组合排序? 同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。
组合排序的语法：
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
举例：
# 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序 select * from student order by age desc, math asc;
3.5.7 聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
- SQL中的聚合函数
| SQL中的聚合函数 | 作用 |
| max(列名) | 求这一列的最大值 |
| min(列名) | 求这一列的最小值 |
| avg(列名) | 求这一列的平均值 |
| count(列名) | 统计这一列有多少条记录 |
| sum(列名) | 对这一列求总和 |
- 聚合函数的语法：
SELECT 聚合函数(列名) FROM 表名;
举例：
# 查询学生总数 select count(id) as 总人数 from student; select count(*) as 总人数 from student;
我们发现对于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL 也统计进去呢？
IFNULL(列名，默认值)
如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。
# 查询 id 字段，如果为 null，则使用 0 代替 select ifnull (id,0) from student;
我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏 。
select count(ifnull(id,0)) from student;
# 查询年龄大于 20 的总数 select count(*) from student where age>20; # 查询数学成绩总分 select sum(math) 总分 from student; # 查询数学成绩平均分 select avg(math) 平均分 from student; # 查询数学成绩最高分 select max(math) 最高分 from student; # 查询数学成绩最低分 select min(math) 最低分 from student;
3.5.8 分组
分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组 。 语法：
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
GROUP BY 怎么分组的？ 将分组字段结果中相同内容作为一组，如按性别将学生分成 2 组。 GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。 分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
# 按性别进行分组，求男生和女生数学的平均分 select sex, avg(math) from student3 group by sex;
效果如下：
网络异常，图片无法展示
|
# 查询男女各多少人 1) 查询所有数据,按性别分组。 2) 统计每组人数 select sex, count(*) from student3 group by sex;
查询年龄大于 25 岁的人,按性别分组,统计每组的人数 1) 先过滤掉年龄小于 25 岁的人。 2) 再分组。 3) 最后统计每组的人数 select sex, count(*) from student3 where age > 25 group by sex ; 查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据以下代码是否正确？ SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2; （错误写法） 正确写法： -- 对分组查询的结果再进行过滤 SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2; 只有分组后人数大于 2 的`男`这组数据显示出来
having 与 where 的区别
网络异常，图片无法展示
|
面试题：
有如下订单表 Orders 表数据如下所示，执行如下 SQL 语句，运行结果是？
网络异常，图片无法展示
|
select product,sum(price) from orders group by product where sum(price) > 30;
运行有误，group by 后面不能出现 where，使用 having
3.5.9 limit 语句
limit 的作用：
LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。
LIMIT 语法格式：
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子 句][LIMIT 子句];
举例：
# 查询学生表中数据，从第 3 条开始显示，显示 6 条。 select * from student3 limit 2,6;
LIMIT 的使用场景：
分页：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。 假设我们每页显示 5 条记录的方式来分页。SQL 语句如下：
# 如果第一个参数是 0 可以省略写： select * from student3 limit 5; # 最后如果不够 5 条，有多少显示多少 select * from student3 limit 10,5;
3.5.10 SQL语句的执行顺序
(8)SELECT (9)DISTINCT (11)<Top Num> <select list> (1)FROM [left_table] (3)<join_type> JOIN <right_table> (2)ON <join_condition> (4)WHERE <where_condition> (5)GROUP BY <group_by_list> (6)WITH <CUBE | RollUP> (7)HAVING <having_condition> (10)ORDER BY <order_by_list>
- FROM： 对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
- ON： 对VT1应用ON筛选器。只有那些使为真的行才被插入VT2。
- OUTER(JOIN)： 如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
- WHERE： 对VT3应用WHERE筛选器。只有使为true的行才被插入VT4.
- GROUP BY： 按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
- CUBE|ROLLUP： 把超组(Suppergroups)插入VT5,生成VT6.
- HAVING： 对VT6应用HAVING筛选器。只有使为true的组才会被插入VT7.
- SELECT： 处理SELECT列表，产生VT8.
- DISTINCT： 将重复的行从VT8中移除，产生VT9.
- ORDER BY： 将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
- TOP： 从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。
后记
喜欢我的文章的朋友点点喜欢、收藏，也欢迎朋友们评论区留下你的意见和建议，恕毅在此拜谢！
