SQL语句

操作数据库语句（DDL语句）

连接数据库

语句：mysql -u用户名 -p
按空格后会显示 enter password：输入密码

举例 sqlyog连接数据库时设置的用户名是root，密码为root。
那么在终端输入的时候：
mysql -uroot -p 【按回车】
enter password：****【*代表root】
如下图所示：

退出数据库

exit 或者 quit
在终端输入exit 或者quit
注意：不需要加括号。即exit()

查看已经创建的数据库

语句：show databases ；
注意：
输入show databases后会显示->符号此时再输入 ；符号即可显示

• mysql不严格区分大小写
• 分号作为结束符，必须写
  

查看数据库版本

语句：select version();

创建数据库

语句：
CREATE DATABASE 数据库名;
CREATE DATABASE 数据库名 charset=utf8;

注意

charset：指定编码为utf8

查看创建数据库的命令

HOW CREATE DATABASE 数据库名;

查看当前使用的数据库

语句：select database()；

切换使用的数据库

语句：use 数据库名 ；
注意：如果数据库名称有扛比如 my_test 将英文输入状态下的`(esc下方的点键)中进行输入，例：use ·mytest·；

删除数据库

DROP DATABASE 数据库名;

注意

• SQL语句不区分大小写
• 每行必须以英文状态下分号作为结束
• 当数据库名称有特殊字符时，使用``包括。如：Logic_edu

操作数据表

查看当前数据库中所有表

语句：show tables;
下图中代表当前数据库中没有任何表

创建表（表的结构）

语句：create table 数据表名（字段 类型 约束[,字段 类型 约束]）；

需求：

• 创建表名为 mytable1，字段id为int并且字段name为varchar（30）
  注意1：在DQLyog等中注释和pychram有所区别，在pychram中是""" “”“或者” "代表注释。而在mysql中注释是–。
  

注意2：参数和参数之间由英语状态的逗号进行分割，但是最后一个参数后面不能跟逗号，否则会报错。如下图所示：

• 创建表名为 mytable2， 指定id为主键并且字段name为varchar（30）
  注意：约束和约束之间没有先后顺序。
  

查看表的创建语句

语句：SHOW CREATE TABLE 数据表名；

查看表描述信息（查看表的结构）

语句：DESC 数据表名；

修改引擎配置方式

寻找到MySQL路径
因为之前是通过phpstudy使用mySQL的。所以在phpstudy中找到Extensions。进入文件夹后，找到MySQL+版本号文件夹。进入后找到配置设置（my.ini）用笔记本方式打开。如下图所示：

在笔记本中修改默认引擎。如图所示：

保存文本后，重新启动pypstudy，即可。

练习

• 创建 student表 （id、name、age、high、gender、cls_is）

-- 创建 student表 （id、name、age、high、gender、cls_is）
-- 分析：id是需要整数非空且唯一和自增。name是名字需要字符。
-- age需要整数(是正数即无符号)，有默认值。higt可以有小数保留两位。
-- gender，男女保密可选，默认保密。cls_is正数
CREATE TABLE student(
		ID INT NOT NULL PRIMARY KEY auto_increment,
		name VARCHAR(30),
		age TINYINT UNSIGNED DEFAULT 18,
		high FLOAT(5,2),
		gender enum('男','女','保密') DEFAULT'保密',
		cls_id INT
		);
				

• 创建 classes表 (id、name）

-- 创建 classes表 (id、name）
-- id 非空且唯一整数自增。 name字符串
CREATE TABLE classes(
id INT NOT NULL PRIMARY KEY auto_increment, 
name VARCHAR(30)
);

注意

当终端出现下图报错的时候，其实并不是报错，而是因为在创建表的时候没有设置指定编码。因为出现了“？”，这个问号就相当于我们的乱码。

解决方法：
在创建表格的时候在分号前面输入“ENGINE=INNODB DEFAULF CHARSET=utf8”
下面为完整版：

CREATE TABLE student(
		ID INT NOT NULL PRIMARY KEY auto_increment,
		name VARCHAR(30),
		age TINYINT UNSIGNED DEFAULT 18,
		high FLOAT(5,2),
		gender enum('男','女','保密') DEFAULT'保密',
		cls_id INT
		)ENGINE=INNODB DEFAULF CHARSET=utf8;
			

添加表字段

语句：alter table 数据表名 add 字段 类型；
要求：
给student表添加一个生日字段

ALTER TABLE student ADD birthday DATE;

修改表字段

语句：
ALTER TABLE 数据表名 MODIFY 字段 类型; – 不重命名
要求：
给生日字段指定默认值

ALTER TABLE stundent MODIFY birthday DATE DEFAULT"1990-01-01";

ALTER TABLE 数据表名 CHANGE 原字段名 新字段名 类型及约束; – 将字段重命名
要求：修改birthday字段名称为birth

ALTER TABLE stundent CHANGE birthday birth DATE DEFAULT"1990-01-01";

删除表字段

语句：
ALTER TABLE 数据表名 DROP 字段;
要求：
删除high字段

ALTER TABLE student DROP high；

删除表

语句： DROP TABLE 需要删除的表名；
要 求：
删除mytable1

DROP TABLE mytabale1；

操作数据

新增数据

整行插入（如果没有指定字段，默认整行插入）

INSERT INTO 数据表名 VALUES(值1,值2,值3…);

注意：id字段也需要填写否则会报错。

指定列中插入数据

INSERT INTO tabname (字段1, 字段2,…) VALUES (值1,值2,…);

注意：字段需要括号，哪怕是一个字段也需要括号。否则会报错。

指定列中插入多条数据

INSERT INTO 数据表名 (字段1, 字段2,…) VALUES (值1,值2,…),(值1,值2,…);

练习

• Student表中添加整行数据

-- 注意：整行插入的时候id字段虽然设置了自增，但还是需要填写。否则会报错。
INSERT INTO student VALUES(1,"cyan",21,"男",1,"2020-01-01");

代码中值一一对应字段需要填写的内容。即从id、name、age、gender、cls_id、birth。
添加后的效果图如下：

• Student表中添加name，gender两个字段的数据

-- 在表名后面➕括号填入需要添加的字段数据的字段名
INSERT INTO student (NAME,cls_id) VALUES("dake",2);

注意：
1、字段名必须要有括号。哪怕是添加一个字段的数据也需要括号否则会报错。

2、枚举类型可以通过下标取值，根据插入顺序，并且从1开始。比如性别的类型是男、女、保密，三种类型。从1开始为下标取值。1代表男、2代表女、3代表保密。我们代码输入INSERT INTO student (NAME,gender) VALUES("ss",1);会在gender字段数据中显示男。
上述代码完整版样式图如下：

• student表中同时添加多行数据

INSERT INTO student (NAME,gender) VALUES("dake",2),("bx",1);

下图红框部分代表多行数据插入成功：

修改数据

修改一整个字段的所有值：
UPDATE 数据表名 SET 字段1=新值;

修改两个字段的值：
UPDATE 数据表名 SET 字段1=新值, 字段2=新值;

修改精确点的字段值：(where 后面跟筛选条件)
UPDATE 数据表名 SET 字段1=新值 WHERE 字段2=需要修改的name在字段2中有包含的值；

练习

• 将姓名全部修改为amy。

-- 将name字段的所有值都重置为amy
UPDATE student SET name="amy";

因为很少有人会将一整个字段的值都重写为同一个值，所有就有了更加具体的需求，即更多的条件进行筛选。

• 将性别为女的名字修改为rose

UPDATE student SET name="rose" WHERE gender="女";

• 将id为3的年龄修改为22，并且性别改为女

UPDATE student SET age=22,gender="女" WHERE id=3;

删除数据

DELETE FROM 数据表名 [WHERE 条件];

练习

将姓名为rose的数据删除——真正意义上的删除（物理删除，即真的被删了没有删除缓冲情况。）

DELETE FROM student WHERE name="rose";

逻辑删除（目前只说逻辑具体操作后期再教）

逻辑删除和物理删除的区别：
物理删除就是真正的删除，而逻辑删除就是一个删除缓冲区。比如删除账号有60天的缓冲时间，在60天内重新登陆账号，那么这个账号还存在并且重新激活不会被删除。如果是物理删除，那么在删除的那一刻这个账号就不存在了无法登陆。

逻辑思维：
通过判断一个字段的值给客户呈现是否删除的现象，实际上数据并没有真正删除；
1、新增一个标识字段：is_del 用来表示数据是否删除。
2、is_del为0 表示未删除
3、is_del为1 表示已删除

-- 创建新字段is_del，设置为整数默认值为0即默认未被删除。
ALTER TABLE student ADD is_del INT DEFAULT 0;

-- 删除id为5的账户（之后就可以提醒这个账户已注销后续操作后期再教）
UPDATE student SET is_del=1 WHERE id=5;

查询数据

查询整个表数据

SELECT * FROM 数据表名;

在开发中并不推荐这个用法，效率很慢。
一般查询有效的字段（指定字段）

查询指定字段数据

SELECT 字段1,字段2 FROM 数据表名;

注意：字段顺序颠倒不影响显示的结果，但是显示的字段顺序会改变。
比如 name,gender 和 gender,name

查询指定字段数据，并给字段起别名

SELECT 字段1 as 别名,字段2 as 别名 FROM 数据表名;

要求：将name字段重命名（起昵称）为“姓名”，gender重命名（起昵称）为“性别”

SELECT gender as "性别",name as "姓名" FROM student;

注意：这个只是显示的重命名。并没有真正改变表的字段名称，所以称为别名。

表也可以去别名
SELECT 表的别名.字段名 FROM 表的名称 AS 表的别名;
要求：
将student表别名为s并查询gender字段

SELECT s.gender FROM student AS s;

表名.表字段为表的字段调用。

查询指定字段并去重

SELECT DISTINCT 字段1 FROM 数据表名;

多个字段去重：
SELECT DISTINCT 字段1, 字段2,字段N FROM 数据表名;

那么结果是有字段1+字段2+字段N不一样才去重。如下所示：（之后在name和age都重复的情况下才会去重）

SELECT DISTINCT name,age FROM student;

练习

• 查询Student表所有数据

SELECT * FROM student;

• 查询Student表中name与gender字段的数据

SELECT name,gender FROM student;

• 查询Student表中name字段的数据并且去重

SELECT DISTINCT name FROM student;

where子句

where子句通常结合增删改查使用，用于做筛选的条件。

比如，查询当id=1的数据

select * from Student where id=1;

语句：
select *【✳️可以用下面聚合函数替换】 from 表名 where 筛选条件
不仅如此，经常结合运算符使用。

比较运算符

练习

1.查询id大于3的数据

select * from Student where id>3;

2.查询年龄大于18岁的信息

select * from Student where age>18;

3.查询姓名不是amy的数据

select * from Student where name !="amy";

逻辑运算符

练习

1.查询21~22之间的所有学生信息

select * from Student where age>=21 and age<=22;

2.查询id大于3的女同学

select * from Student where id>3 and gender="女";

3.查询id小于2或者id大于4的学生信息

select * from Student where id<2 or id>4;

4.查询年龄不是 18的女同学

select * from Student where not (age=18 and gender="男");

--也可以是：
select * from Student where age!=18 and gender="女";

--最标准的：
select * from Student where (not age!=18) and gender="女";

模糊查询

like关键字用来进行模糊查询，并且结合%以及_使用。

• % 表示任意多个任意字符
• _ 表示一个任意字符。即两个下划线代表2个字符。

练习

以下练习都基于下图这个完整表进行操作

1.查询名字以a开始的学生信息

select * from Student where like "a%";

2.查询名字含有a的学生信息

select * from Student where like "%a%";

3.查询名字仅有2个字符的学生信息

select * from Student where like "__";

解释：此时还没有id为9的aa姓名的信息。所以不显示。

4.查询名字至少有2个字符的学生信息

select * from Student where like "__%";

范围查询

练习

1.查询id是1或者3或者6的学生信息

select * from Student where id in (1,3,6);

2.查询年龄为18或者21的学生信息

select * from Student where age IN (18,21);

--也可以是
select * from Student where age=18 or age=21;

3.查询年龄不是18,21的学生信息

select * from Student where age!=18 and age!=21;

select * from Student where NOT IN (18,21);

4.查询id是2至5的学生信息

select * from Student where id between 2 and 5;

5.查询id是3-5的男同学信息

-- 代码可读性不强
select * from Student where id between 2 and 5 AND gender="男";

-- 最好版本加括号。
select * from Student where (id between 2 and 5) AND gender="男";

6.查询年龄不在18至21之间的学生信息

select * from Student where age not between 18 and 21;

-- 
select * from Student where NOT age between 18 and 21;

空判断

练习

1.查询没有填写班级的学生

select * from Student where cls_id is null;

2、查询已经有班级的学生

select * from Student where cls_id is not null;

常用聚合函数

聚合函数：由多变一。

下面所用的表格完整数据如下所示：

count 总数

• 求student总人数（求整个表记录的总数）

SELECT COUNT(*) FROM student;

• 求男性的人数

SELECT COUNT(*) FROM student WHERE gender="男";

求出男性有4位。

• 女性的人数

SELECT COUNT(*) FROM student WHERE gender="女";

求出女生有3位。

max()最大值

• 查询最大的年龄

SELECT MAX(age) FROM student;

最大的年龄就是22

• 女性最大的id

SELECT MAX(age) FROM student WHERE gender="女";

注意：

SELECT MAX(age),name FROM student;

单这样查询是不会显示对应的姓名的反而会报错，那是需要运用到分组聚合方式。

min()最小值

• 查询未删除的学生的最小编号

SELECT MIN(id) FROM student WHERE is_del=0;

sum求和

• 查询男生年龄和

SELECT SUM(age) FROM studen WHERE gender=1;

gender=1是运用了枚举的下标法。也可以gender=“男”

avg平均值

• 保留小数ROUND(AVG(age),需要保留的位数)
• 查询未删除女生的年龄的平均值

SELECT AVG(age) FROM student WHERE gender=2 and is_del=0;

默认保留了很多位小数

• 计算男性的平均年龄，保留2位小数

SELECT ROUND(AVG(age),2) FROM student WHERE gender=1;

分组与分组之后的筛选

分组

如下图所示：根据性别分组。分为男生一组、女生一组、保密一组。

在数据库中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组。

	select ... from students group by 需要分组字段;   

SELECT * FROM student GROUP BY gender;

如此分组会报错的：无法进行聚合的字段（列），因为把男性和女性分别分为一组，其中的一些具体的值是无法呈现的。

将性别分组：

SELECT gender FROM student GROUP BY gender;

但是看结果发现，这样没有什么意义。我们需要每组人的具体数值。这就是聚合。聚合分组就是我们将每组分组后的数据聚合（聚合组中数据结果）。

练习：

• 计算男生和女生中的人数

SELECT COUNT(*) FROM student GROUP BY gender;

• 需要显示性别对应人数

SELECT gender,COUNT(*) FROM student GROUP BY gender;

• 男生最大年龄与女生最大年龄

SELECT gender,MAX(age) FROM student GROUP BY gender;

• 需要显示性别对应人数以及总人数

SELECT gender,COUNT(*) FROM student GROUP BY gender WITH ROLLUP;

group_concat(…)

我们通过group_concat(…)查看每组的详细信息

练习：

查询同种性别(分组后)的姓名

SELECT gender,GROUP_CONCAT(name) FROM student GROUP BY gender;

SELECT GROUP_CONCAT(gender,name) FROM student GROUP BY gender;

下图是两种代码效果图：

查询组内年龄,姓名

SELECT gender,GROUP_CONCAT(name,age) FROM student GROUP BY gender;

SELECT gender,GROUP_CONCAT(name,":",age) FROM student GROUP BY gender;

下图为两种代码效果：

补充

• 语句：WITH ROLLUP
  用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息。

SELECT gender,COUNT(*) FROM student GROUP BY gender WITH ROLLUP;

• 拼接
  两个字段为参数的时候，在两个字段中添加"需要拼接的样式"
  例如：需要将名字对应年龄。
  下图是没有拼接的样式：
  
  拼接语句：(name,":",age)

SELECT gender,GROUP_CONCAT(name,":",age) FROM student GROUP BY gender;

这是拼接后的样式：

分组后的筛选

语句：having

对原始数据集（即我们的数据表）进行条件过滤（筛选）使用where。

进行了where过滤后得到的就是结果集（一个已经被过滤过的结果集合），再来进行分组就需用到group_by。

通过group_by分组后，有得到了一个结果集，对于分组后的结果集就用having进行过滤（筛选）

要求：
查询每组性别的总数之后，需要筛选出总数大于2的。

SELECT gender,COUNT(*) FROM student GROUP BY gender HAVING COUNT(*)>2;

注意

分组之后的筛选不能直接用到where。会报错。要用到having。

练习：

• 男生女生总数大于2

SELECT gender,COUNT(*) FROM student GROUP BY gender HAVING COUNT(*)>2;

• 查询男生女生总数大于2的姓名

SELECT gender,COUNT(*),GROUP_CONCAT(name) FROM student GROUP BY gender HAVING COUNT(*)>2;

• 查询男生女生平均年龄超过18岁的性别，以及姓名

SELECT gender,AVG(age),GROUP_CONCAT(name) FROM student GROUP BY gender HAVING AVG(age)>18;

排序

order by 字段 ：默认升序
order by asc 字段 ：指定升序
order by 字段 desc ：指定降序

练习：

• 查询年龄在18到26之间的男同学，按照年龄从小到大排序

SELECT * FROM student WHERE (age BETWEEN 18 AND 26) AND gender="男", ORDER BY age;

• 查询年龄在18到20岁之间的女同学，id从高到低排序

SELECT * FROM student WHERE (age BETWEEN 18 AND 20) AND gender="女", ORDER BY id DESC;

• 查询年龄在18-23岁之间的女性，年龄从高到低降序，当年龄相同时，按照身高从低到高升序。

SELECT gender,name,age,high FROM student WHERE (age BETWEEN 18 AND 23) AND gender="女" ORDER BY age DESC,high ASC;

注意

多个条件进行排序的时候，当第一个排序有相同的时候，根据第二个要求进行排序。以此类推。优先第一个排序，之后是第二个排序（有优先级）。

限制

limit默认前1000行的数据进行限制操作（limit有数量限制）

limit start,count

• start 为偏移量，默认起始0
• count 为条数

要求：

• 只想要显示表格的前两条信息

SELECT * FROM student LIMIT 0,2;

代码中逗号前面的默认为从0开始。所以可以直接写2。 即 LIMIT 2;
2代表条数。

• 显示从第三条开始的三条信息。

SELECT * FROM student LIMIT 2,3;

因为是从0开始计数的，所以第三条即是2（0，1，2）。

注意：

• limit 后面不能写数学公式（在代码中）
• limit只能写在末尾（在代码中）

表连接

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回mysql。这时我们就需要使用表连接。

分类：

• 内连接
• 外连接
  • 左连接
  • 右连接

内连接（笛卡尔积）

内连接仅选出两张表中互相匹配的记录

select * from 表1 inner  join 表2 on 表1.列 = 表2.列;

student表格数据：

classes表格数据：

要求：
将两个表进行内连接

SELECT * FROM student INNER JOIN classes;

显示结果：

student中的信息都会扩展为两条都和classes表有关。

这样显示没有意义。需要一些条件将这链接有意义。
要求：
只有student.cls_id = classes.id才进行连接

-- AS 为取别名。将student.cls_id过长了，将student取别名s。同理取别名c。
SELECT * FROM student AS s INNER JOIN classes AS c ON s.'cls_id'=c."id";

效果图：

练习：

• 显示学生的所有信息，但只显示班级名称

SELECT s.*,c.name FROM student AS s INNER JOIN classes AS c ON s.'cls_id'=c."id";

分析：
s.*：代表显示student表所有数据。
c.name：代表显示classes表的name信息。
当s.‘cls_id’=c."id"的时候，两表内连接。

• 将班级名称显示在第一列

SELECT c.name,s.* FROM student AS s INNER JOIN classes AS c ON s.'cls_id'=c."id";

颠倒一下显示代码的顺序即可。

• 查询有能够对应班级的学生以及班级信息，按照班级进行排序

SELECT c.name,s.* FROM student AS s INNER JOIN classes AS c ON s.'cls_id'=c.'id', ORDER BY s.'cls_id';

• 当同一个班级时，按照学生的id进行从小到大排序

SELECT c.name,s.* FROM student AS s INNER JOIN classes AS c ON s.'cls_id'=c.'id', ORDER BY s.'cls_id',s.'id' ASC;

左连接

查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充

	select * from 表1 left  join 表2 on 表1.列 = 表2.列;

要求：
使用student表左连接classes表

SELECT * FROM student LEFT JOIN  classes ON student.'cls_id'=classes.'id'; 

左连接以左表为主，只要student表中有数据，classes表中有无数据都没事，可以在右表将不存在的数据使用null填充。

注意

左连接必须要有on条件，而右连接则可以没有on条件。

练习：

• students表左连接classes表 并查看班级为null的数据

SELECT * FROM student LEFT JOIN  classes ON student.'cls_id'=classes.'id' WHERE classes.id IS null; 

• 左连接 并且 查询 s.id_del=1 并且 s.name=“amy” 的数据

SELECT * FROM student LEFT JOIN  classes ON student.'cls_id'=classes.'id' HAVING student.id_del=1 并且 student.name="amy" ; 

右连接

查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充。

以右表为主，和左连接同理。

	select * from 表1 right join 表2 on 表1.列 = 表2.列;

子查询

某些情况下，当进行查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。

即一个查询语句作为另一个查询语句的条件。就是子查询。

student表数据：

查询身高最高的男生（已知最高身高男生是1.70）：

SELECT * FROM student WHERE high=1.70；

SELECT MAX(high) FROM student WHERE gender=1；

此时只知道最高身高，但不知道是谁。

显示身高最高的男生以及对应的姓名。
这时候需要用到子查询：
1、先查询所有学生的身高
2、指定条件：查问最高的男生身高

SELECT NAME,high FROM student s WHERE s.'high'=(SELECT MAX(high) FROM student WHERE gender=1)；

解析：
子语句：SELECT MAX(high) FROM student WHERE gender=1
查询最高的身高。条件是男生，即查询男生中身高最高的。即1.70

主语句：SELECT NAME,high FROM student s WHERE s.‘high’
查询名字和身高。条件是身高等于子语句即等于1.70。

练习

• 查询高于平均身高的学生信息

SELECT * FROM student WHERE high>(SELECT AVG(high) FROM student)；

• 查询学生班级号能够对应的学生信息（即cls_id能够对应学生信息）

SELECT * FROM student WHERE cls_id in (SELECT id FROM classes)；

解析：
只取出了id在cls_id中有数据的数据。那些cls_id为null的对应id数据都不显示（过滤掉了）。

• 查询最大年龄的女生的id

SELECT * FROM student WHERE age= (SELECT MAX(age) FROM student WHERE gender=2) AND gender=2；

注意：
子语句：SELECT MAX(age) FROM student WHERE gender=2。得出的结果就是28

主语句：SELECT * FROM student WHERE age= (SELECT MAX(age) FROM student WHERE gender=2)。就是查询年龄等于28的。那么男女都会有了。所以需要在补充条件性别为女。