【数据库】SQL语言总结

SQL语言总结

本篇文章中将会系统性的总结SQL相关的知识点，包括DDL、DML、DQL、DCL这四类。

DDL

数据定义语言，用以定义数据库对象：库、表、列

数据库操作

查询

SHOW DATABASES;

创建

CREATE DATABASE name;
CREATE DATABASE IF NOT EXISTS name;

删除

DROP DATABASES name;
DROP DATABASES IF EXISTS name;

使用

USE name;
SELECT DATABASE();

数据表操作(CRUD)

创建

CREATE TABLE table_name(field1 type,
						field2 type,
						field3 type,
						...
						);

查询

SHOW TABLES;
DESC table_name

更新

-- 修改表的名称
ALTER TABLE table_name RENAME TO new_table_name;
-- 添加一列
ALTER TABLE table_name ADD field_name type;
-- 修改列数据类型
ALTER TABLE table_name MODIFY field_name new_type;
-- 修改列名和数据类型
ALTER TABLE table_name CHANGE field_name new_field_name new_type;
-- 删除列
ALTER TABLE table_name DROP field_name;

删除

DROP table_name;
DROP IF EXISTS table_name;

DML

数据操作语言，用以对数据库中的表进行增、删、改

增加

INSERT INTO table_name(field1, field2,...) VALUES(value1, value2,...);

INSERT INTO table_name VALUES(value1, value2,...);

INSERT INTO table_name(field1, field2,...) VALUES(value1, value2,...),
												 (value1, value2,...),
												 (value1, value2,...),
												 (value1, value2,...);
												 
INSERT INTO table_name VALUES(value1, value2,...),
							 (value1, value2,...),
							 (value1, value2,...),
							 (value1, value2,...);

删除

-- 使用WHERE删除符合条件的记录，不适用则全部删除。
DELETE FROM table_name [WHERE]

修改

-- 使用WHERE修改符合条件的记录，不使用则修改所有记录。
UPDATE table_name SET field1=value1, field2=value2,... [WHERE]

DQL

数据查询语言，用以对数据库表中的记录进行查询
整体的结构如下：

SELECT field_name 
FROM table_name WHERE requirement 
GROUP BY group_field_name HAVING group_requirement 
ORDER BY order_field_name 
LIMIT page;

基础查询

-- 查询字段
SELECT field_name FROM table_name;
-- 查询所有字段
SELECT * FROM table_name;
-- 去重查询
SELECT DISTINCT field_name FROM table_name;
-- 别名 AS 关键字可以在任何的字段和表后面添加
SELECT field_name AS new_field_name FROM table_name AS new_table_name;

条件查询

-- 基础条件，大于、大于等于、小于、小于等于、等于、不等于
SELECT field_name FROM table_name WHERE field* > value*;
SELECT field_name FROM table_name WHERE field* >= value*;
SELECT field_name FROM table_name WHERE field* < value*;
SELECT field_name FROM table_name WHERE field* <= value*;
SELECT field_name FROM table_name WHERE field* = value*;
SELECT field_name FROM table_name WHERE field* != value*;
SELECT field_name FROM table_name WHERE field* <> value*;
-- 范围查询
SELECT field_name FROM table_name WHERE field* < value1* && field* > value2*;
SELECT field_name FROM table_name WHERE field* < value1* and field* > value2*;
SELECT field_name FROM table_name WHERE field BETWEEN value1* AND value2*;
-- 可数范围查询
SELECT field_name FROM table_name WHERE field* = value1 OR field* = value2 OR field* = value3;
SELECT field_name FROM table_name WHERE field* IN (value1, value2, value3);
-- 是否为null
SELECT field_name FROM table_name WHERE field* IS null;
SELECT field_name FROM table_name WHERE field* IS NOT null;
-- 模糊查询 _ 单个任意字符 % 多个任意字符
SELECT field_name FROM table_name WHERE field* LIKE 'value%';
SELECT field_name FROM table_name WHERE field* LIKE '_value%';
SELECT field_name FROM table_name WHERE field* LIKE '%value%';

分组查询

聚合函数

null 值不参与聚合函数计算

SELECT COUNT(field_name) FROM table_name;
SELECT MAX(field_name) FROM table_name;
SELECT MIN(field_name) FROM table_name;
SELECT SUM(field_name) FROM table_name;
SELECT AVG(field_name) FROM table_name;

分组统计

分组后查询的字段需要为聚合函数或分组字段，其余字段无意义。

SELECT field_name FROM table_name [WHERE requirement] GROUP BY group_field_name [HAVING group_requirement]
-- 配合聚合函数使用分组查询
SELECT count(field_name) FROM table_name GROUP BY group_field_name;
-- 配合聚合函数和WHERE条件语句使用分组查询，WHERE条件用来限制参与聚合的数据
SELECT count(field_name) FROM table_name WHERE field1 > value1 GROUP BY group_field_name;
-- 配合聚合函数和HAVING条件，HAVING条件用来限制聚合后的数据
SELECT count(field_name) FROM table_name WHERE field1 > value1 GROUP BY group_field_name HAVING count(group_field_name) > value2;

排序查询

-- ASC 升序 DESC 降序
SELECT field_name FROM table_name ORDER BY field_name1 [ASC,DESC]
-- 多字段排序时，在前一字段值相同的情况下再去对第二个字段进行排序，默认null排在最后
SELECT field_name FROM table_name ORDER BY field_name1 [ASC,DESC]

分页查询

SELECT file_name FROM table_name LIMIT start_index, count;

DCL

数据控制语言，用来定义数据库的配置，创建用户等

约束

约束是针对表中的字段而言的。因此可以在建表或修改表时为字段添加以下约束

非空约束

CREATE TABLE table_name(field1 type NOT NULL,
						...
						);

唯一约束

CREATE TABLE table_name(field1 type UNIQUE,
						...
						);

主键约束

CREATE TABLE table_name(field1 type PRIMARY KEY,
						...
						);

-- 主键自动增长
CREATE TABLE table_name(field1 type PRIMARY KEY AUTO_INCREMENT,
						...
						);

默认约束

CREATE TABLE table_name(field1 type DEFAULT value,
						...
						);

外键约束

用来建立两个表之间数据的关联性

-- table1
CREATE TABLE table_name_1(field1_1 type,
						  field1_2 type,
						...
						);

-- 建表时添加外键；
-- table2
CREATE TABLE table_name_2(field2_1 type,
						  field2_2 type,
						...
						)
CONSTRAINT fk_table_name_2_table_name_1 FOREIGN KEY (field2_1) REFERENCES table_name_1(field1_1);

-- 建表后添加外键；
ALTER TABLE  table_name_2 ADD CONSTRAINT fk_table_name_2_table_name_1 FOREIGN KEY (field2_1) REFERENCES table_name_1(field1_1);

-- 删除外键
DROP TABLE table_name_2 DROP FOREIGN KEY fk_table_name_2_table_name_1;

多表查询

连接查询

内连接

内连接可以被是为两张表基于一个相同字段所产生的交集。

-- 隐式内连接
SELECT field_name FROM table_name_1 AS t1, table_name_2 AS t2 WHERE t1.id = t2.id;
-- 显式内连接
SELECT field_name FROM table_name_1 AS t1 [INNER] JOIN table_name_2 AS t2 ON t1.id = t2.id;

外连接

左外和右外连接分别表示需要保留的非交集部分数据来自哪里。

-- 左外连接
SELECT field_name FROM table_name_1 AS t1 LEFT [OUTER] JOIN table_name_2 AS t2 ON t1.id = t2.id;

-- 右外连接
SELECT field_name FROM table_name_1 AS t1 RIGHT [OUTER] JOIN table_name_2 AS t2 ON t1.id = t2.id;

子查询

查询中嵌套查询

-- 子查询结果为单个属性的单个数值
SELECT field_name FROM table_name_1 WHERE field_name > (SELECT field_name FROM table_name_1 WHERE field_name = value);
-- 子查询结果为单个属性的多个数值
SELECT field_name FROM table_name_1 WHERE field_name IN (SELECT field_name FROM table_name_1 WHERE field_name = value);
-- 子查询结果为多个属性的多个数值
SELECT field_name FROM (SELECT * FROM table_name_1 WHERE field_name = value) WHERE field_name > value;