SQL语句详解

 2024/09/02 

SQL是一种结构化的查询语言，是关系型数据库通用的命令

遵循了SQL92的标准

SQL的常用种类

DDL definition 数据定义语言
DCL control 数据控制语言
DML Manipulation 数据操作语言
DQL Query 数据查询语言

数据库的逻辑结构

库属性：字符集、排序规则

表属性：存储引擎类型、字符集、排序规则

列属性：数据类型、约束、其他属性

字符集

相当于编码表
最开始是ASCII码
国内常用utf8、utf8mb4、gbk
utf8 每个汉字3字节
utf8mb4 每个汉字4字节(推荐)、支持emoji

排序规则（校对规则）collation

影响对于英文字符串大小写的敏感度

一般选择bin结尾的
utf8mb4_bin 大小写敏感(包括拼音、日文)
utf8mb4_general_ci 大小写不敏感

数据类型（列的属性，在表中定义键时进行定义）

数字型

小整型TINYINT 0-255
整型INT
浮点数float
位数 bit 0或1

字符串型

定长字符串char(100) 立即申请固定空间，剩余用空格填充
变长字符串varchar(100) 每次存储之前都判断长度，来分配空间，如果少于255就会单独申请一个字符长度存储空间，如果超过255会占用两个存储空间

如何选择char与varchar：
1.少于255字符长度的、定长的列值，选择char
2.多余225字符长度、变长的字符串，可以选择varchar

枚举型

enum 枚举数据类型，类似于列表
将所有可能性放入其中的数据类型，比如
name enum(’wangsheng’,’xuehuiying’)

时间型

DATE
格式YYYY-MM-DD

TIME
格式hh:mm:ss[毫秒]

DATETIME
结合了date和time的用法，支持1000-9999年

TIMESTAMP
结合了date和time的用法，是一个常量，1970年到现在的秒数，支持1970-2038年，并且支持时区自动变更

DDL语句

数据定义语言，对库和表进行定义

库定义

创建库
CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;
↑
字符集utf8mb4
排序规则utf8mb4_bin

查看库
show databases;
show create database zabbix;

删除数据库（需要严格控制权限
DROP DATABASE zabbix;

修改数据库：

字符集修改：
需要从小往大修改，即utf8改为utf8mb4
目标字符集需要是原字符集的严格超级,
create database test2 charset utf8;
alter database test2 charset utf8mb4;
SHOW CREATE DATABASE test2;

其他例子：
联合主键
create table t1 (id int not null auto_increment,name varchar(20),primary key (id,name));

create table t2 (id int primary key not null auto_increment,name varchar(20));

库的定义规范

1.建库使用小写字符——因为开发环境用windows不区分大小写，测试环境使用linux小写

2.库名不能以数字开头

3.不能是数据库内部关键字

4.建库必须设置字符集
也可以在my.cnf中添加默认字符集参数
character-set-server=utf8mb4

表定义

建表
表名、表属性、列名、列属性

使用软件点点点创建表

可以看到表属性有：表名称、存储引擎、字符集、核对规则

可以看到列属性有：列名、数据类型、长度、默认值、主键约束、是否为空、是否自增、

使用命令创建表和列

相关列属性：
PRIMARY KEY 主键约束，非空且唯一
NOT NULL 非空约束，不允许空值
UNIQUE KEY 唯一键约束，比如电话号，卡号，身份证号等
DEFAULT 默认值、一般和 NOT NULL 一起使用
UNSIGNED 无符号，一般配合数字列且非负数
COMMENT 注释
AUTO_INCREMENT 自增长列

相关表属性：
ENGINE 存储引擎
CHARSET 编码格式

例：
CREATE TABLE xuehuiyinger2 (  
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',  
    aname VARCHAR(225) NOT NULL COMMENT '名字',  
    age TINYINT UNSIGNED NOT NULL COMMENT '年龄',  
    gender ENUM('man','woman','none') NOT NULL DEFAULT 'none' COMMENT '性别',  
    intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'  
) ENGINE=InnoDB CHARSET=utf8mb4;

表定义规范

1.表名使用小写字母，不能数字开头，不能关键字、保留字符

2.选择合适的数据类型与长度

3.每个列都设置not null+默认值这种形式，对于数字就默认0，对于字符就默认空格

4.每个列必须加注释comment

5.表必须设置存储引擎类型和字符集（也可以在my.cnf中加入）

6.主键尽量设置无关数字列，并且最好自增长

7.enum类型不要保存数字

修改表

查询表信息
show tables;
SHOW CREATE TABLE xuehuiyinger2;

复制一张表结构一样的表
CREATE TABLE test LIKE xuehuiyinger2;

删除表
DROP TABLE test;

查看表的列结构
desc xuehuiyinger2

修改表结构:

添加一个列(常用)
ALTER TABLE xuehuiyinge3 ADD qq VARCHAR(15) NOT NULL COMMENT 'QQ号';
最原始的方法-复制表、修改表、表改名
会锁表——不能在生产高峰期

在某列后添加一个列
ALTER TABLE xuehuiyinger2 ADD wechat VARCHAR(32) NOT NULL UNIQUE COMMENT '微信' AFTER aname;

在第一列前添加列
alter table xuehuiyinger2 add num int not null unique comment '身份证' first;

删除列

修改列(键)的数据类型的属性
DESC xuehuiyinge3;
ALTER TABLE xuehuiyinge3 MODIFY aname CHAR(64) NOT NULL COMMENT '名字';

修改列的名称
ALTER TABLE xuehuiyinge3 CHANGE gender sex CHAR(4) NULL COMMENT '性别';

DML语句

DML只有三个命令
insert update delete

对于数据行的操作，即插入、修改、删除

DESC xuehuiyinge3;
INSERT INTO xuehuiyinge3(id,aname,wechat,age,sex,intime) VALUE(1,'ws','qweqwe',18,'man',NOW());
简略写法↓
INSERT xuehuiyinge3 VALUE(1,'ws','qweqwe',18,'man',NOW());
INSERT xuehuiyinge3 VALUE(2,'xhy','qwes',17,'woman',NOW());

包含的默认值的说法，直接针对列
insert into xuehuiyinge3(sname) value ('xuehuiyg');

一次性录入多行数据
insert into xuehuiyinge3 (sname,age) value
('aa',10),
('bb',11),
('cc',12);

其他例
desc ws01;
alter table ws01 modify id char(10);
alter table drop index id;
insert into ws01()

DQL语句

select
show
DQL是要求最高的语句，因为使用频率高，需要优化的频率比较高

select的应用

select单独使用

查找函数
select @@basedir;
select @@server_id;
select @@port;
select @@innodb_flush_log_at_trx_commit;
↓
show variables like "%commit";
show variables like "%innodb%"
使用函数查找结果
show database;
select database();

select通用语法（常用）
from 表
where 列条件，过滤，类似于grep
group by 条件
order by 条件
limit 条件

from:
select * from ws01;(不要用)
select * from ws01 limit 5;
select user,host from mysql.user;

where:
等值条件查询
select host from mysql.user where user='root';

不等值——范围查询
select name,population from city where population>8000000;

select name,population from city
where country='CN' and population>8000000;

select name,population from city
where country='CN' or country='USA'; 
select name,population from city
where country in ('CN','USA');

select name from city
where population>5000000 and population<8000000;

模糊查询like
select * from city
where country like 'C%';
注：不要使用前后都加%的搜索，性能很差，可以使用es来查询

聚合函数与group by

常用聚合函数
max(),min(),avg(),count(),sum()等

类似于excle中的筛选和排序、函数使用

group by后面跟的是分组条件

统计每个国家的城市个数
select country,count(id) from city
group by country;

统计每个国家的总人口数
select country,sum(population) from city
group by country;

统计每个国家的省个数
distinct去重复
select country,count(distinct district) from city
group by country;

统计中国所有省的人口
select country,sum() from city
where country='CN' group by district;

统计中文每个城市的个数
select country,count() from city
where country='CN' group by district;

统计中国每个省中，城市名称的列表
GROUP_CONCAT() 列转行
select district,group_concat(name) from city
where country='CN' group by disctrict;

将城市名称的列表和省份名称组合，以一个对象输出（和awk类似）
concat() 拼接
GROUP_CONCAT() 列转行
select CONCAT(district,":",group_concat(name)) from city
where country='CN' group by disctrict;

having子句

在group by后如果要进行筛选，用where就会变形：此时还没有筛选呢，用where需要根据筛选的后果进行过滤，就不行

此时就需要使用having

统计所有国家的总人口数，且过滤人口数大于1e的
select country,sum(population) from city
group by country having sum(population)>10000;

注：
having不走索引，如果大量使用就会导致性能变差

order by子句

是一个排序功能。同have，需要以结果为目的进行排序的情况下使用

统计所有国家的总人口数量，过滤人口数大于5000w
并按照从大到小排序
select country,sum(population) from city
group by country
having sum(population)>50000000
order by sum(population);
默认就是从小到大排序

从大到小：加一个desc
select country,sum(population) from city
group by country
having sum(population)>50000000
order by sum(population) desc;

limit子句

和head less类似

统计所有国家的总人口数量，过滤人口数大于5000w，并按照从大到小排序
只显示前三名

select country,sum(population) from city
group by contry
having sum(population)>5000000
order by sum(population)
limit 3;

跳过前3行，显示2行
limit 3,2;
或(偏移量)
limit 3 offest 2;

union和union all

提供多个结果期合并查询的功能

其索引等级比使用or或and等逻辑判断高，所以其性能更好

select * from city where country='CN' or country='USA';
↓
select * from city where country='CN'
union all
select * from city where country='USA';

union all 不去重
union     去重

综合示例

统计中国每个省的总人口数，只打印总人口数小于100w的
select district,sum(population) city
where country='CN'
group by district
having sum(population)>1000000;

统计中国所有城市，按人口数进行从大到小排序
select name from city
where country='CN'
order by population desc;

统计中国每个省的总人口数，按总人口从大到小排序
select district,sum(population) from city
where country='CN'
group by district
order by sum(population);

统计中国每个省的总人口，找出总人口数大于500w，按总人口从大到小排序，只显示前三名
select district,sum(population) from city
where country='CN'
group by district
having sum(pupolation)>5000000
order by sum(population) desc
limit 3;

select多表连接查询

多表查询，就是内连接

要查找的内容是多个表查询的结果的组合，就需要进行多表连接查询

查询世界上小于100人的城市、所在国家名、国土面积、城市名、人口数

单独查：
select country,name,population from city where population<100;
获取国家名PCN，再拿到第二个表中查 
select name,surfaceArea from country where code='PCN';

多表语法规则

1.找到多张表之间的关联条件列
在上面这个例子中，就是country名 PCN

2.在多表连接查询中，需要指定表名.列名
（也就是从另外一张表中进行引用，比较像父类子类的关系）

3.所有查询列都直接放在select后

4.所有过滤分组等都放在on后

5.多张表join和on，
A join B
on A.x = B.y
join C
on B.x = C.y

6.在表设计中
join on时尽量将数据行数较少的放前面
后续的关联列尽量是主键或唯一键
至少建立一个索引

select country.name,country,surfaceArea,
city.name,city.population 
from city join country
on city.country = country.code
where city.population<100;

多表语法案例

项目搭建语句
DROP DATABASE school;
CREATE DATABASE school CHARSET utf8;
USE school;

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');

INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

student表：
sno学号
sname学生姓名
sage学生年龄
ssex学生性别

teacher表：
tno教师编号
tname教师名字

course表：
cno课程编号
cname课程名字
tno教师编号

sc表
sno学号
cno课程编号
srore成绩

需求1
统计zhang3一共学习了几门课
select student.sname,count(sc.cno)
from student join sc
on student.sno=sc.sno
where student.sname='zhang3';
group by student.sno;

查询zhang3学习的课程名称
select student.sname,course.cname from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
↓
也可以使用group_concat()进行聚合：
SELECT student.sname,GROUP_CONCAT(course.cname) FROM student
JOIN sc
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno
WHERE student.sname='zhang3'
GROUP BY student.sname

oldguo老师教的学生名和个数
SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname) FROM teacher
JOIN course
ON  teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tname;

oldguo所教课程的平均分
SELECT teacher.tname,AVG(sc.score) FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
WHERE teacher.tname='oldguo';

查询每个老师的平均分并排序
SELECT teacher.tname,AVG(sc.score) FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tname,course.cname ←——以课程名称区分是大多数情况
ORDER BY AVG(sc.score) DESC;

查询oldguo所教的不及格的学生姓名
SELECT teacher.tname,GROUP_CONCAT(student.sname),sc.score FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo' AND sc.score<60;

查询所有老师所教的不及格的学生姓名
SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score))
FROM teacher
JOIN course ON teacher.tno = course.tno
JOIN sc ON course.cno = sc.cno
JOIN student ON sc.sno = student.sno
WHERE sc.score<60
GROUP BY teacher.tno;
或
SELECT teacher.tname,student.sname,sc.score
FROM teacher
JOIN course ON teacher.tno = course.tno
JOIN sc ON course.cno = sc.cno
JOIN student ON sc.sno = student.sno
WHERE sc.score<60;

select别名

一直写表名.列名太麻烦了，所以搞个别名

表别名

修改之前
SELECT teacher.tname,student.sname,sc.score
FROM teacher
JOIN course 
ON teacher.tno = course.tno
JOIN sc 
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;

修改之后
SELECT t.tname,s.sname,sc.score
FROM teacher as t
JOIN course as c
ON t.tno = c.tno
JOIN sc
ON c.cno = sc.cno
JOIN student as s 
ON sc.sno = s.sno
WHERE sc.score<60;

列别名

修改之前
SELECT teacher.tname,student.sname,sc.score
FROM teacher
JOIN course 
ON teacher.tno = course.tno
JOIN sc 
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;

修改后
SELECT teacher.tname as 老师名,student.sname as 学生名,sc.score as 分数
FROM teacher
JOIN course 
ON teacher.tno = course.tno
JOIN sc 
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;

显示的列名就改变了，也可以被having、order by被调用

show语句

一些常用的
show databases;
show tables;
show create database/table xx;
show variables..like 'xxx';
show processlist;
show charest;支持的字符集
show collation; 校对规则
show grants for xx;
show engines;
show index from xxx
show engine indodb status\G
show binary logs;
show binlog events in '';
show master status; 当前使用的二进制日志信息
show slave status\G 主从复制从库状态
show relaylog events in '';
show status like ''; 数据库整体状态信息

原文作者：王盛

原文链接：https://akemi.zj.cn/2024/09/02/MySQL-SQL/

发表日期：September 2nd 2024, 10:27:53 pm

更新日期：February 20th 2025, 6:37:27 pm

Next Post

MySQL元数据与information_schema
Previous Post

MySQL多实例

CATALOG

1. 数据库的逻辑结构
2. DDL语句
3. DML语句
4. DQL语句