mysqlSQL 语句主要分为： DQL：数据查询语言，用于对数据进行查询，如 select DML：数据操作语言，对数

SQL 语句主要分为：

DQL：数据查询语言，用于对数据进行查询，如 select
DML：数据操作语言，对数据进行增加、修改、删除，如 insert、update、delete
TPL：事务处理语言，对事务进行处理，包括 begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如 grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，如 create、drop

注意：

对于开发人员来说，重点是数据库的增、删、改、查。必须熟练编写 DQL、DML，能够编写 DDL 完成数据库、表的操作，其它操作如 TPL、DCL 了解、会用即可。
SQL 语言不区分大小写

MySQL 的配置文件

配置文件路径为：/etc/mysql/mysql.conf.d/mysqld.cnf

数据类型

常用数据类型：

整数：int，bit
小数：decimal
字符串：varchar,char
日期时间：date, time, datetime
枚举类型 (enum)

数据类型使用说明：

decimal 表示浮点数，如 decimal(5, 2) 表示共存 5 位数，小数占 2 位。
char 表示固定长度的字符串，如 char(3)，如果填充'ab'时会补一个空格为'ab '，3 表示字符数。
varchar 表示可变长度的字符串，如 varchar(3)，填充'ab'时就会存储'ab'，3 表示字符数。
对于图片、音频、视频等文件，一般不存储在数据库中，而是上传到服务器上，然后在表中存储这个文件的保存路径。
text 表示存储大文本，当字符大于 4000 时推荐使用，例如文章正文内容。

数据约束

约束是指数据在数据类型限定的基础上额外增加的要求。

常见的约束：

主键 primary key: 物理上存储的顺序。MySQL 建议所有表的主键字段都叫 id，类型为 int unsigned。
非空 not null: 此字段不允许填写空值。
惟一 unique: 此字段的值不允许重复。
默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准。
外键 foreign key: 对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常。

登录和登出数据库

mysql -u root -p

说明：

-u 后面是登录的用户名
-p 后面是登录密码，如果不填写，回车之后会提示输入密码

登出 (退出) 数据库

quit 或 exit 或 \q

数据库操作的 SQL 语句

查看所有数据库

show databases;

创建数据库

create database 数据库名 charset=utf8mb4;

-- 例如：

create database hello charset=utf8mb4;

使用数据库

use 数据库名;
-- 例如：
use hello;

查看当前使用的数据库

select database();

删除数据库 - 慎重

drop database 数据库名;
-- 例如：
drop database hello;

操作表结构的 SQL 语句

查看当前数据库中所有表

show tables;

创建表

create table users(
 id int unsigned primary key auto_increment not null,
 name varchar(20) not null,
 age tinyint unsigned default 0,
 height decimal(5,2),
 gender enum('男','女','其他')
);

create table 表名(
字段名称 数据类型  可选的约束条件,
column1 datatype contrai,
...
);

修改表 - 添加字段

alter table 表名 add 列名 类型 约束;
-- 例如：
alter table users add birthday datetime;

修改表 - 修改字段类型

alter table 表名 modify 列名 类型 约束;
-- 例如：
alter table users modify birthday date not null;

说明：

modify: 只能修改字段类型或者约束，不能修改字段名

修改表 - 修改字段名和字段类型

alter table 表名 change 原名 新名 类型及约束;
-- 例如：
alter table users change birthday birth datetime not null;

change: 既能对字段重命名又能修改字段类型还能修改约束

修改表 - 删除字段

alter table 表名 drop 列名;
-- 例如：
alter table users drop birth;

操作表数据的 SQL 语句

#  查询所有列
select * from 表名;
-- 例如：
select * from users;

#  查询指定列
select 列1,列2,... from 表名;
-- 例如：
select id,name from users;

添加数据

#  全列插入：值的顺序与表结构字段的顺序完全一一对应
insert into 表名 values (...)
-- 例如：
insert into users values(0, '花木兰', default, default, '女');

#  部分列插入：值的顺序与给出的列顺序对应
insert into 表名 (列1,...) values(值1,...)
-- 例如：
insert into users(name, age) values('貂蝉', 15);

#  全列多行插入
insert into 表名 values(...),(...)...;
-- 例如：
insert into users values(0, '王昭君', 16, 1.65, '女'),(0, '杨玉环', 20, 1.60, '女');

#  部分列多行插入
insert into 表名(列1,...) values(值1,...),(值1,...)...;
-- 例如：
insert into users(name, height) values('刘邦', 1.70),('项羽', 1.75);

说明：

主键列是自动增长，但是在全列插入时需要占位，通常使用空值 (0 或者 null 或者 default)
在全列插入时，如果字段列有默认值可以使用 default 来占位，插入的数据就是之前设置的默认值

修改数据

update 表名 set 列1=值1,列2=值2... where 条件
-- 例如：
update users set age = 18, gender = '女' where id = 6;

删除数据

delete from 表名 where 条件
-- 例如：
delete from users where id=5;

登录数据库：mysql -uroot -p
退出数据库：quit 或者 exit 或者 ctr + d
创建数据库：create database 数据库名 charset=utf8;
使用数据库：use 数据库名;
删除数据库：drop database 数据库名;
创建表：create table 表名 (字段名字段类型约束，...);
修改表 - 添加字段：alter table 表名 add 字段名字段类型约束
修改表 - 修改字段类型：alter table 表名 modify 字段名字段类型约束
修改表 - 修改字段名和字段类型：alter table 表名 change 原字段名新字段名字段类型约束
修改表 - 删除字段：alter table 表名 drop 字段名;
删除表：drop table 表名;
查询数据：select * from 表名; 或者 select 列 1，列 2,... from 表名;
插入数据：insert into 表名 values (...) 或者 insert into 表名 (列 1,...) values(值 1,...)
修改数据：update 表名 set 列 1=值 1，列 2=值 2... where 条件
删除数据：delete from 表名 where 条件

as 关键字

就是给字段取一个别名，一般是为了可读性，或者多表关联查询有同名字段时使用。

select id as 序号, name as 名字, gender as 性别 from users;

使用 as 给表起别名

-- 如果是单表查询 可以省略表名
select id, name, gender from users;

-- 表名.字段名
select users.id,users.name,users.gender from users;

-- 可以通过 as 给表起别名 
select u.id,u.name,u.gender from users as u;

distinct 关键字

distinct 可以去除重复数据行

select distinct 列1,... from 表名;

-- 例： 查询用户表的性别
select name, gender from users;

-- 如果有重复数据，可以使用 distinct 进行去重
select distinct name, gender from users;

as 关键字用来给表中字段或者表名起别名
distinct 关键字用来去除重复的数据行。

where 条件查询语法格式如下：

select * from 表名 where 条件;
-- 例如：
select * from users where id = 1;

-- 查询编号大于3的用户:
select * from users where id > 3;

-- 查询编号不大于4的用户:
select * from users where id <= 4;

-- 查询姓名不是“曹操”的用户:
select * from users where name != '曹操';

-- 查询被删除的用户:
select * from users where deleted=1;

逻辑运算符查询

-- 查询编号大于3的女用户:
select * from users where id > 3 and gender='女';

-- 查询编号小于4或未被删除的用户:
select * from users where id < 4 or deleted=0;

-- 查询年龄不在10岁到15岁之间的用户:
select * from users where not (age >= 10 and age <= 15);

模糊查询

like 是模糊查询关键字
%表示任意多个任意字符
_表示一个任意字符

-- 查询姓刘的学生:
select * from users where name like '刘%';

-- 查询姓刘并且“名”是一个字的学生:

select * from users where name like '刘_';

-- 查询姓刘或叫操的学生:
select * from users where name like '刘%' or name like '%操';

范围查询

between .. and .. 表示在一个连续的范围内查询
in 表示在一个非连续的范围内查询

-- 查询id为3至7的用户:
select * from users where id between 3 and 7;

-- 查询id不是3至7的女性:
select * from users where (not id between 3 and 7) and gender='女';

-- 查询id为3和7的用户:
select * from users where id in (3, 7);

空判断查询

判断为空使用：is null
判断非空使用：is not null

-- 查询没有填写身高的学生:
select * from users where height is null;

常见的比较运算符有 >、<、>=、<=、!=
逻辑运算符 and 表示多个条件同时成立则为真，or 表示多个条件有一个成立则为真，not 表示对条件取反
like 和%结合使用表示任意多个任意字符，like 和_结合使用表示一个任意字符
between-and 限制连续性范围 in 限制非连续性范围
判断为空使用：is null
判断非空使用：is not null

排序查询

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

先按照列 1 进行排序，如果列 1 的值相同时，则按照列 2 排序，以此类推
asc 从小到大排列，即升序
desc 从大到小排序，即降序
默认按照列值从小到大排列（即 asc 关键字）

-- 查询未删除男性信息，按id降序:
select * from users where gender='男' and deleted=0 order by id desc;

-- 显示所有的用户信息，先按照年龄从大->小排序，当年龄相同时 按照身高从高->矮排序:
select * from users order by age desc, height desc;

排序使用 order by 关键字
asc 表示升序
desc 表示降序

分页查询的语法

select * from 表名 limit start,count

limit 是分页查询关键字
start 表示开始行索引，默认是 0
count 表示查询条数

-- 查询前3行男性信息:
select * from users limit 0,3;
-- 简写
select * from users limit 3;

分页查询原理

假设，当前页 current_page=1，要求每页显示 2 条数据，也就是 page_size=2，那么

current_page=1 页查询的数据就是 0, 2
current_page=2 页查询的数据就是，2, 2
current_page=3 页查询的数据就是，4, 2

其他依次类推，可得到公式，limit 的值就是：(当前页-1) * 每页显示条数, 每页显示条数

select * from users limit (current_page-1) * current_page, page_size;

-- 例如
select * from users limit 0, 2;
select * from users limit 2, 2;
select * from users limit 4, 2;
select * from users limit 6, 2;

用来对数据进行统计和计算，经常会结合分组 (group by) 查询来使用。

常用的聚合函数：

count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值

求总行数

-- 返回非NULL数据的总行数.
select count(height) from users; 
-- 返回总行数，包含null值记录;
select count(*) from users;

求最大值

-- 查询ID最大值
select max(id) from users;

-- 查询最小ID
select min(id) from users;

-- 查询男性的总身高
select sum(height) from users where gender = '男';

-- 平均身高
select sum(height) / count(*) from users where gender = '男';

-- 求男性的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from users where gender = '男';

-- 求男性的平均身高, 包含身高是null的
select avg(ifnull(height,1.80)) from users where gender = '男';

连接查询

内连接（inner join）查询
左连接（left join）查询
右连接（right join）查询
全连接 (full，join，注意：MySQL 中没有)
自连接查询

CREATE TABLE `articles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `articles` (`id`, `category_id`, `title`)
VALUES
    (1,1,'过节不出门，出门不过节'),
    (2,2,'人类踏上火星指日可待'),
    (3,4,'天生我才必有用');

CREATE TABLE `categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `categories` (`id`, `name`)
VALUES
    (1,'旅行'),
    (2,'科技'),
    (3,'娱乐');

左连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用 null 值填充

select 字段 from 表 1 left join 表 2 on 表 1.字段 1 = 表 2.字段 2

使用左连接查询文章表与分类表：

select * from articles as a left join categories as c on a.category_id = c.id;

右连接查询

select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

select * from articles as a right join categories as c on a.category_id = c.id;

内连接，就是查询两个表中符合条件的共有记录

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

说明：

inner join 就是内连接查询关键字
on 就是连接查询条件

例 1：使用内连接查询文章表与分类表：

select * from articles as a inner join categories as c on a.category_id = c.id;

-- 也可以不定义别名
select * from articles inner join categories on articles.category_id = categories.id;

自连接查询

左表和右表是同一个表，根据连接查询条件查询两个表中的数据。

CREATE TABLE `department` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT 0,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `department` (`id`, `parent_id`, `name`)
VALUES
    (1,0,'研发部'),
    (2,0,'市场部'),
    (3,1,'后端组'),
    (4,1,'前端组'),
    (5,1,'移动端开发组'),
    (6,2,'销售组'),
    (7,2,'售后组');

查询研发部下面所有二级部门

select x.id, x.name, x.parent_id, y.name from department as x inner join department as y on x.parent_id = y.id where y.name = '研发部';

子查询的使用

例 1. 查询大于平均年龄的用户：

select * from users where age > (select avg(age) from users);

查询文章所在分类的所有分类名字：

select id, name from categories where id in (select category_id from articles);

查找年龄最大，身高最高的用户：

select * from users where (age, height) =  (select max(age), max(height) from users