MySQL笔记（二）——SQL基础

总览

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）。