SQL

一、SQL 

MySQL 是服务器软件，安装了 MySQL 的计算机就是服务器了，其他计算机作为客户端可以访问该 MySQL 服务器

 1、SQL 的概念

Structured Query Language：结构化查询语言

SQL 定义了操作所有关系型数据库的规则

2、SQL 通用语法

（1）SQL 语句可以单行或多行书写，以分号结尾

（2）可使用空格和缩进来增强语句的可读性

（3）MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写

（4）三种注释

单行注释：

　　方法一：-- 注释内容 

　　方法二：# 注释内容 

多行注释：

　　/* 注释 */

3、SQL 分类

（1）DDL（Data Definition Language）数据定义语言

• 用来定义数据库对象：数据库、表、列等
• 关键字：create、drop、alter 等

（2）DML（Data Manipulation Language）数据操作语言

• 用来对数据库中表的数据进行增删改
• 关键字：insert、delete、update 等

（3） DQL（Data Query Language）数据查询语言

• 用来查询数据库中表的记录（即数据）
• 关键字：select、where 等

 

二、DDL

1、操作数据库：CRUD

（1）C（Create）：创建

① 创建数据库：
create database 数据库名称;

② 创建数据库，判断不存在，再创建：
create database if not exists 数据库名称;

③ 创建数据库，并指定字符集
create database 数据库名称 character set 字符集名;

（2）R（Retrieve）：查询

① 查询所有数据库的名称:
show databases;

② 查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;

（3）U（Update）：修改

修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

（4）D（Delete）：删除

① 删除数据库
drop database 数据库名称;

② 判断数据库存在，存在再删除
drop database if exists 数据库名称;

（5）使用数据库

① 查询当前正在使用的数据库名称
select database();

② 使用数据库
use 数据库名称;

2、操作表：CRUD

（1）C（Create）：创建

create table 表名(
                列名1 数据类型1,
                列名2 数据类型2,
                ....
                列名n 数据类型n
);

数据类型：

• int：整数类型，例如：age int
• double：小数类型，例如：score double(5,2)
• date：日期类型，只包含年月日，格式为 yyyy-MM-dd
• datetime：日期类型，包含年月日时分秒，格式为 yyyy-MM-dd HH:mm:ss
• timestamp：时间戳类型，包含年月日时分秒，格式为 yyyy-MM-dd HH:mm:ss
• varchar：字符串类型，例如：name varchar(20)，指的是姓名最大20个字符

提示：如果将来不给 timestamp 这个字段赋值，或赋值为 null，则默认使用当前的系统时间，来自动赋值

（2）R（Retrieve）：查询

① 查询某个数据库中所有的表名称
show tables;

② 查询表结构
desc 表名;

（3）U（Update）：修改

① 修改表名
alter table 表名 rename to 新的表名;

② 修改表的字符集
alter table 表名 character set 字符集名称;

③ 添加一列
alter table 表名 add 列名 数据类型;

④ 修改列名称 类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;

⑤ 删除列
alter table 表名 drop 列名;

（4）D（Delete）：删除

drop table 表名;
drop table  if exists 表名 ;

（5）复制表

create table 表名 like 被复制的表名;

 

三、DML

1、添加数据

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

注意：

• 列名和值要一一对应。
• 如果表名后，不定义列名，则默认给所有列添加值
• 除了数字类型，其他类型需要使用引号（单双都可以）引起来

2、删除数据

delete from 表名 [where 条件]

注意：

• 如果不加条件，则删除表中所有记录
• 如果要删除所有记录，如下：

delete from 表名;    -- 不推荐使用
有多少条记录就会执行多少次删除操作

TRUNCATE TABLE 表名;    -- 推荐使用
效率更高 先删除表，然后再创建一张一样的表

3、修改数据

update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

注意：如果不加任何条件，则会将表中所有记录全部修改。

 

四、DQL

1、基础查询

① 多个字段的查询
select 字段名1，字段名2... from 表名；

② 查询所有字段
select * from 表名；

2、条件查询

（1）where 子句后跟条件

（2）运算符

•  > 、< 、<= 、>= 、= 、<>
• BETWEEN...AND
• IN( 集合)
• LIKE：模糊查询
• 占位符：

　　　　_：单个任意字符

　　　　%：多个任意字符

• IS NULL
• and 或 &&
• or 或 ||
• not 或 !

（3）举例

 1 -- 查询年龄大于20岁
 2 SELECT * FROM student WHERE age > 20;
 3 SELECT * FROM student WHERE age <= 20;
 4 
 5 -- 查询年龄等于20岁
 6 SELECT * FROM student WHERE age = 20;
 7 
 8 -- 查询年龄不等于20岁
 9 SELECT * FROM student WHERE age != 20;
10 SELECT * FROM student WHERE age <> 20;
11 
12 -- 查询年龄大于等于20 小于等于30
13 SELECT * FROM student WHERE age >= 20 &&  age <=30;
14 SELECT * FROM student WHERE age >= 20 AND  age <=30;
15 SELECT * FROM student WHERE age BETWEEN 20 AND 30;
16 
17 -- 查询年龄22岁，18岁，25岁的信息
18 SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
19 SELECT * FROM student WHERE age IN (22,18,25);
20 
21 -- 查询英语成绩为null
22 -- 错误做法：null 值不能使用 = （!=）判断
23 SELECT * FROM student WHERE english = NULL; 
24 -- 正确做法：用 IS 判断    
25 SELECT * FROM student WHERE english IS NULL;
26 
27 -- 查询英语成绩不为null
28 SELECT * FROM student WHERE english  IS NOT NULL;
29 
30 -- 查询姓马的有哪些
31 SELECT * FROM student WHERE NAME LIKE '马%';
32 
33 -- 查询姓名第二个字是化的人
34 SELECT * FROM student WHERE NAME LIKE "_化%";
35 
36 -- 查询姓名是3个字的人
37 SELECT * FROM student WHERE NAME LIKE '___';
38 
39 -- 查询姓名中包含德的人
40 SELECT * FROM student WHERE NAME LIKE ‘%德%’;

3、排序查询

order by 排序字段1 排序方式1 , 排序字段2 排序方式2...

其中，排序方式：

• ASC：升序（默认排序方式）
• DESC：降序

注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件

4、聚合函数

指的是将一列数据作为一个整体，进行纵向的计算

• count：计算个数 

　　　　一般选择非空的列：主键

　　　　count(*)

• max：计算最大值
• min：计算最小值
• sum：计算和
• avg：计算平均值

注意：聚合函数的计算，要排除 null 值

• 解决方案：1. 选择不包含非空的列进行计算  2. IFNULL 函数

5、分组查询

group by 分组字段；

（1）where 和 having 的区别

• where 在分组之前进行限定，如果不满足条件，则不参与分组；having 在分组之后进行限定，如果不满足结果，则不会被查询出来
• where 后不可以跟聚合函数，having可以进行聚合函数的判断

（2）举例

-- 按照性别分组，分别查询男、女同学的数学平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学平均分、人数
SELECT sex , AVG(math) , COUNT(id) FROM student GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学平均分、人数 
-- 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math) , COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学平均分、人数 
-- 要求：分数低于70分的人，不参与分组；分组之后，人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

6、分页查询

limit 开始的索引,每页查询的条数;

开始的索引 = (当前的页码 - 1) * 每页显示的条数

注意：limit 是 MySQL 数据库独有的 SQL 语句

举例：

-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页