SQL 分类
前面讲过,SQL 分类如下:
DDL
:数据定义语言,即Data Definition Language
,用来定义数据库对象,如数据库,表,列等;DML
:数据操作语言,即Data Manipulation Language
,用来对数据库中表的数据进行增删改;DQL
:数据查询语言,即Data Query Language
,用来查询数据库中表的记录(数据);DCL
:数据控制语言,即Data Control Language
,用来定义数据库的访问权限和安全级别,及创建用户;
下面记录了 MySQL 中常用的 SQL 语句。
DDL
操作数据库
查询
显示数据库:
SHOW DATABASES;
创建
创建数据库:
CREATE DATABASE 数据库名;
创建数据库(加了判断,如果数据库不存在则创建):
CREATE DATABASE IF NOT EXISTS 数据库名;
删除
删除数据库:
DROP DATABASE 数据库名;
删除数据库(加了判断,如果存在该数据库则删除):
DROP DATABASE IF NOT EXISTS 数据库名;
使用
查看当前使用的数据库:
SELECT DATABASE();
使用数据库:
USE 数据库名;
操作表
查询表
查询当前数据库下所有表的名称(查看有哪些表):
SHOW TABLES;
查询指定表的结构(查看表结构):
DESC 表名;
创建表
- 创建表的语法:
CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
字段名3 数据类型3,
...
字段名n 数据类型n,
);
需要注意,创建表的时候,最后一行不用加逗号。
- 创建表的例子:
字段需求:
- 编号
- 姓名(最长不超过10个汉字)
- 生日(取值为 年月日)
- 成绩(小数点后保留两位)
- 邮件地址(最大长度不超过64)
- 联系电话(不一定是手机号码,可能出现 - 字符)
建表语句如下:
create table student (
id int,
name varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(64),
tel varchar(15)
);
删除表
删除表:
DROP TABLE 表名;
删除表时判断表是否存在:
DROP TABLE IF EXISTS 表名;
修改表
修改表名:
ALTER TABLE 表名 RENAME TO 新表名;
添加一列:
ALTER TABLE 表名 ADD 列名 数据类型;
修改数据类型:
ALTER TABLE 表名 MODIFY 列名 新数据类型;
修改列名和数据类型:
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
删除列:
ALTER TABLE 表名 DROP 列名;
DML
添加数据
给指定的列添加数据:
INSERT INTO 表名(列名1, 列名2, ...) VALUES(值1, 值2, ...);
给全部列添加数据:
INSERT INTO 表名 VALUES(值1, 值2, ...);
给指定的列批量添加数据:
INSERT INTO
表名(列名1, 列名2, ...)
VALUES
(值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...),
...
;
给全部的列批量添加数据:
INSERT INTO
表名
VALUES
(值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...),
...
;
修改数据
修改表数据:
UPDATE 表名 SET 列名1=值1, 列2=值2, ... [WHERE 条件];
注意:如果 update
语句没有添加 where
子句,则会修改表中的所有数据。
删除数据
DELETE FROM 表名 [WHERE 条件];
注意:如果 delete
语句没有添加 where
子句,则会删除表中的所有数据。
DQL
基础查询
查询多个字段:
SELECT 字段列表 FROM 表名;
查询全部字段(查询整张表):
SELECT * FROM 表名;
去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;
起别名:
AS 别名;
注意,使用AS
关键字起别名时,AS
关键字可以省略。
条件查询
条件查询的语法:
SELECT
字段列表
FROM
表名
WHERE
条件列表;
在条件列表中常用的操作符如下:
>
:大于<
:小于>=
:大于或等于<=
:小于或等于=
:等于<>
或!=
:不等于BETWEEN ... AND ...
:在某个范围之内(都包含)IN()
:多选一LIKE 占位符
:模糊查询(_表示单个任意字符,%表示多个任意字符)IS NULL
:是NULLIS NOT NULL
:不是NULLAND
或&&
:并且OR
或||
:或者NOT
或!
:非、不是
排序查询
排序查询的语法:
SELECT
字段列表
FROM
表名
ORDER BY
排序字段1[排序方式1],
排序字段2[排序方式2],
...
排序字段n[排序方式n]
;
排序方式有两种:
ASC
:升序排列(默认值)DESC
:降序排列
注意:如果有多个排序条件,则当前边的条件值一样时,才会根据第二条件进行排序,以此类推。
聚合函数
概念:
- 将一列数据作为一个整体进行纵向计算,就可以使用聚合函数。
聚合函数的分类:
count(字段名)
:统计不为null
的列的数量;max(字段名)
:求最大值;min(字段名)
:求最小值;sum(字段名)
:求和;avg(字段名)
:求平均值;
聚合函数的语法:
SELECT
聚合函数(字段名)
FROM
表名;
需要注意的是,null
值不参与所有聚合函数的运算。
分组查询
SELECT
字段列表
FROM
表名
WHERE
分组前的条件限定
GROUP BY
分组字段名
HAVING
分组后条件过滤;
注意:
WHERE
子句和HAVING
子句可以省略- 分组之后,查询的字段为聚合函数和分组字段,查询其他字段将无任何意义。
执行顺序:
where > 聚合函数 > having
where 和 having 的区别:
- 执行时机不同:
where
是分组之前进行限定,不满足where
条件,则不参与分组,而having
是分组之后对结果进行过滤。 - 可判断的条件不一样:
where
不能对聚合函数进行判断,having
可以。
举几个例子:
-- 分为两组,查询男同学和女同学各自的数学平均分以及各自人数,要求分数低于70的不参与分组,并且分组后的人数必须大于2人:
SELECT
sex, AVG(math), COUNT(*)
FROM
stu
WHERE
math > 70
GROUP BY
sex
HAVING
COUNT(*) > 2;
分页查询
分页查询的语法如下:
SELECT
字段列表
FROM
表名
LIMIT
起始索引, 查询条目数;
需要注意:
- 起始索引从 0 开始
- 计算公式为:起始索引 = (当前页码 - 1) * 每页显示的条数
- 另外,分页查询的关键字
LIMIT
是MySQL 数据库的方言,比如 Oracle 分页查询使用rownumber
,SQL Server 分页查询使用top
举一些例子:
-- 从 0 开始查询,查询 5 条数据:
SELECT * FROM tb1 LIMIT 0, 5;
-- 每页显示 5 条数据,查询第 1 页数据:
SELECT * FROM tb1 LIMIT 0, 5;
-- 每页显示 5 条数据,查询第 2 页数据:
SELECT * FROM tb1 LIMIT 5, 5;
-- 每页显示 5 条数据,查询第 2 页数据:
SELECT * FROM tb1 LIMIT 10 , 5;
DQL 小结
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定