MySQL笔记（二）——SQL基础

分手后的思念是犯贱 2023-07-03 07:28 38阅读 0赞

# 总览 #

SQL语句主要分为三大类，分别是：  
DDL（数据定义语言）：*用于定义数据库、表、列、索引等数据库对象，常用语句关键字为create、drop、alter。*  
DML（数据操纵语言）：*用于添加、删除、更新和查询数据库记录，常用语句关键字为insert、delete、update和select等。*  
DCL（数据控制语言）:*用于定义数据库、表、列和用户的访问权限，常用语句关键字是grant和revoke。*  
除此之外，一般还习惯从DML中划分出数据查询语言DQL，因为查询在日常使用中占用了很大的比重，语句也很多样。  
对于三种SQL语言类型，我们可以简单理解为，DDL语句大多是用于对库、表的增删改操作，DML语句大多是用于对数据的增删改查操作，DCL语句一般开发人员不会使用，主要用于用户权限管理。

# DDL #

## 库操作 ##

### 操作示例 ###

1、创建数据库：  
create database \[database\_name\] charset \[charset\] collate \[collate\]; 其中，必须设置字符集，而collate为可选  
创建一个名为practice的数据库，其字符集为utf8，校对规则为utf8\_bin：  
*create database practice charset utf8 collate utf8\_bin;*

2、修改数据库：*字符集及校验规则，目标字符集一定是原字符集的严格超集*  
alter database \[database\_name\] charset \[charset\];  
将practice库的字符集改为utf8mb4：  
*alter database practice charset utf8mb4 collate utf8mb4\_bin;*

3、删除数据库：*危险操作*  
drop database \[database\_name\];  
删除practice库：  
*drop database practice;*

4、查看系统中存在的数据库：  
*show databases;*

5、查看数据库创建语句：  
show create database \[dbname\];  
show create database practice;

### 建库规范 ###

*1、库名使用小写字符  
2、库名不能以数字开头  
3、库名不能是数据库内部的关键字  
4、建库必须设置字符集*

## 表操作 ##

### 操作示例 ###

#### 创建 ####

1、创建新表：*表名 + 列名 + 列属性 + 表属性（存储引擎 + 字符集）*  
create table \[tablename\] (  
column\_name\_1 column\_type\_1 constraints,  
column\_name\_2 column\_type\_2 constraints,  
…  
column\_name\_n column\_type\_n constraints,  
) ENGINE \[enginee\_name\] charset \[charset\];  
创建一个employee表，存储引擎为innodb，字符集为utf8mb4：  
*create table `employee`(  
`id` int primary key not null auto\_increment comment ‘工号’ ,  
`sname` varchar(255) not null comment ‘姓名’,  
`age` tinyint unsigned not null default 0 comment ‘年龄’,  
`gender` enum(‘male’, ‘female’, ‘unknown’) not null default ‘unknown’ comment ‘性别’,  
`intime` datetime not null default now() comment ‘入职时间’  
) engine innodb charset utf8mb4;*

*列属性：  
primary key：主键约束，非空且唯一  
not null：非空约束，该列值不允许为空，尽量所有列都非空  
unique key：唯一键约束，该列不允许有重复值  
default：配合not null使用  
unsigned：无符号数，一般配合数字列，非负数  
comment：注释  
auto\_increment：自增长的列*

*常用数据类型：  
1、数字类型：  
tinyint：1字节，能够表示256个数字（0 ~ 255或-128 ~ 127），主要用于存放较小的整数  
smallint：2字节，能够表示2^16个数字（0 ~ 2^16 - 1或-2^15 ~ 2^15 - 1）  
mediumint：3字节，能够表示2^24个数字（0 ~ 2^24 - 1或-2^23 ~ 2^23 - 1）  
int：4字节，能够表示2^32个数字（0 ~ 2^32 - 1或-2^31 ~ 2^31 - 1）  
2、字符串类型：  
char：0~255字节，定长字符串，存储时直接分配指定大小的空间  
varchar：0~65535字节，可变长字符串，存储时会根据数据的实际长度分配空间，但会降低存取效率  
enum：枚举类型  
3、时间类型：  
date：YYYY-MM-DD  
time：HH:mm:ss  
datetime：YYYY-MM-DD HH:mm:ss（1000-01-01 00:00:00.000000~9999-12-31 23:59:59.999999）  
timestamp：YYYY-MM-DD HH:mm:ss（1970-01-01 00:00:00.000000~2038-01-19 03:14:07.999999）*

2、创建与employee表相同的test表：  
create table \[tablename\] like \[tablename\];  
*create table test like employee;*

#### 修改 ####

3、在原表基础上新增一列：  
alter table \[tablename\] add column\_definition \[first | after col\_name\];  
对employee表新增qq列：  
*alter table test add `qq` varchar(16) not null unique key comment ‘QQ号’;*  
对employee表新增wechat列，新增列的位置在intime之后：  
*alter table test add `wechat` varchar(255) not null unique key comment ‘QQ号’ after intime;*

4、修改列属性：*所有属性都会被修改，哪怕只是修改某一个属性也要把其他不修改的写完整*  
alter table \[tablename\] modify column\_definition \[first | after col\_name\];  
*alter table test modify `gender` char(10) not null default ‘unknown’ comment ‘性别’;*

5、同时修改列名和列属性：  
alter table \[tablename\] change old\_col\_name column\_definition \[first | after col\_name\];  
*alter table test change `gender` `sex` char(10) not null default ‘unknown’ comment ‘性别’;*

6、删除表（危险操作）  
*drop table test;*

7、查看建表语句  
*show create table employee;*

8、查看表结构  
*desc employee;*

### 建表规范 ###

*1、表名使用小写字符，不能以数字开头，不能是数据库内部关键字，使用与业务有关的表名  
2、选择合适的数据类型及长度  
3、每个列设置not null + default，对于数字列以0填充，对于字符串使用有效字符串填充  
4、每个列都必须设置注释  
5、表必须设置存储引擎类型和字符集  
6、主键列尽量是无关数字列，最好是自增长  
7、enum类型不能保存数字，只能是字符串类型  
8、alter语句会导致锁表，谨慎使用*

# DML #

1、插入数据：  
insert into … values(…)  
（1）单条数据  
*insert into `employee`(`id`, `sname`, `age`, `gender`, `intime`) values(1, ‘Jack’, 25, ‘male’, now());*

（2）多条数据  
*insert into `employee`(`sname`, `age`, `gender`) values(‘Luna’, 23, ‘female’), (‘Jerry’, 26, ‘male’), (‘Mike’, 22, ‘male’);*

2、修改记录：  
update … set … where …  
*update `employee`  
set  
`sname` = ‘Will’,  
`age` = 27  
where  
`id` = 3;*

3、删除记录：  
delete from … where …  
*delete from `employee`  
where  
`id` = 2;*

## 数据查询——select语句 ##

使用官方提供的world库进行查询语句的练习，这个库中都是很老的数据了，仅做练习不必较真。  
查看world库的建库语句（字符集、校对规则）:  
*show create database world;*  
查看world库内表的情况：  
*show tables;*  
查看city表结构:  
*desc city;*

1、select语句结合from子句：  
select \[column\] from \[table\];  
（1）查询city表中所有记录（对于数据较大的情况，性能极低，生产中几乎没有这样的需求）：  
*select \* from city;*

（2）查询city表中所有的城市（Name）以及其对应的人口数（Population）：  
*select Name, Population from city;*

2、select语句结合where子句：  
select \[column\] from \[table\] where \[condition\]  
（1）等值条件查询，查询city表中所有的中国城市（CountryCode）以及其对应的人口数（Population）：  
*select Name, Population from city where CountryCode = ‘CHN’;*

（2）不等值条件查询，主要针对数字列，查询city表中人口数小于100的城市及其对应的人口数：  
*select Name, Population from city where Population < 100;*

（3）配合逻辑连接符（and、or）：  
查询city表中人口数大于8000000的中国城市以及其对应的人口数：  
*select Name, Population from city where CountryCode = ‘CHN’ and Population > 8000000;*  
查询city表中，人口数大于5000000小于6000000的中国城市一及其对应的人口数：  
*select Name, Population from city where CountryCode = ‘CHN’ and Population between 5000000 and 6000000;*

（4）配合like子句实现模糊查询，查询city表中国家编码以’CH’开头的所有城市信息：  
*select \* from city where CountryCode like ‘CH%’;*  
**注意，不能出现类似于’%CH%’这类前后都有‘%’的语句，因为这样不走索引，导致性能极差。如果业务中有类似需求（模糊查询），使用其他替代方法实现**

（5）配合in子句，查询city表中所有属于中国和美国的城市信息：  
*select \* from city where CountryCode in (‘CHN’, ‘USA’);*

3、select语句结合group by子句 + 聚合函数应用：  
常用聚合函数：sum(), count(), avg(), min(), max(), group\_concat()  
（1）统计每个国家城市的个数：  
*select CountryCode, count(id) from city group by CountryCode;*

（2）统计每个国家的总人口数：  
*select CountryCode, sum(population) from city group by CountryCode;*

（3）统计每个国家省的个数：  
*select CountryCode, count(distinct district) from city group by CountryCode;*

（4）统计中国每个省的人数：  
*select district, sum(population) from city where CountryCode = ‘CHN’ group by district;*

（5）统计中国每个省城市的个数：  
*select district, count(id) from city where CountryCode = ‘CHN’ group by district;*

（6）统计中国每个省城市的名字列表：  
*select concat(district, ': ', group\_concat(Name)) from city where CountryCode = ‘CHN’ group by district;*

4、select语句结合having子句（having子句不走索引）：  
统计city表中所有国家的总人口数量，将总人口数大于1亿的过滤出来：  
*select CountryCode, sum(Population) from city group by CountryCode having sum(Population) > 100000000;*

5、select语句结合order by子句：  
统计city表中所有国家的总人口数量，将总人口数大于5000万的过滤出来，并且按照从大到小顺序排列：  
*select CountryCode, sum(Population) from city group by CountryCode having sum(Population) > 50000000 order by sum(Population) desc;*

6、select语句结合limit子句（limit m, n与limit m offset n）：  
（1）统计city表中所有国家的总人口数量，将总人口数大于5000万的过滤出来，并且按照从大到小顺序排列，只显示前三名：  
*select CountryCode, sum(Population) from city group by CountryCode having sum(Population) > 50000000 order by sum(Population) desc limit 3;*  
（2）统计city表中所有国家的总人口数量，将总人口数大于5000万的过滤出来，并且按照从大到小顺序排列，只显示前4~5名：  
*select CountryCode, sum(Population) from city group by CountryCode having sum(Population) > 50000000 order by sum(Population) desc limit 2 offset 3;*

**语句顺序：from -> where -> group by -> having -> order by**

7、多表连接查询（内连接）  
作用：同时在多个表中进行查询（join … on …）  
查询city表中小于100人的城市所在的国家名、国土面积和人口数（city、country）  
*desc country;  
select country.Name, country.SurfaceArea, city.Name, city.Population from city join country on city.CountryCode = country.Code where city.Population < 1000 order by city.Population desc;*

*基本语法：  
1、最核心的是，找到多张表之间的关联条件列  
2、列书写时，必须是：table\_name.column\_name  
3、所有涉及到的查询列，都放在select后  
4、将所有的过滤、分组、排序等条件按顺序写在on的后面*

别名应用  
1、表别名：全局调用  
2、列别名：having和order by调用

8、元数据查询  
元数据：*用于表示数据的数据，只要不是由用户存放到库中的数据，几乎都是元数据，如库名、表名、列名、用户以及各种属性、约束等。元数据存储于基表中，mysql中无法直接管理基表，只能通过专用的DDL、DCL语句进行元数据的修改，通过专用视图进行元数据的查询。*

视图：*相当于为语句定义别名，在使用时起到简化语句的作用。例如：多表连接查询中的例子，可以定义为一个视图：*  
*CREATE VIEW test\_view AS  
SELECT  
country.Name as country\_name,  
country.SurfaceArea,  
city.Name as city\_name,  
city.Population  
FROM  
city  
JOIN  
country ON city.CountryCode = country.Code  
WHERE  
city.Population < 1000  
ORDER BY city.Population DESC;*  
以上语句在创建视图时，实际上是根据as后面的select语句创建了一个名为test\_view的虚拟表，虚拟表中不能有重名的列，故需要对country表和city表中的Name列取别名区分。有了视图test\_view之后，再执行这条查询语句，则可以直接使用：  
*select \* from test\_view;*  
删除视图的操作和删除表类似：  
*drop view test\_view;*

查询元数据主要有两种方法，一个是使用information\_schema，一个是使用show语句。information\_schema中保存了大量元数据查询的视图，可以将它也看做一个数据库那样使用，而show是封装好的、用于元数据查询的语句。

# DCL #

DCL语句在笔记（一）中已有使用，主要是用户权限授予（grant）和回收（revoke）。