1.SQL介绍
●注释:
单行:-- #(MySQL特有)
多行:/* */
●SQL分类:
DDL(Data Definition Language): 数据定义语言。
DML(Data Manipulation Language): 数据操作语言。用来对数据库中表的数据进行增删改
DQL(Data Query Language): 数据查询语言。用来查询数据库中表的记录(数据)
DCL(Data Control Language): 数据控制语言。用来定义数据库的访问权限和安全级别,及创建用户
2.DDL
●DDL 查询和创建数据库
查询所有数据库: SHOW DATABASES;
查询数据库的创建语句: SHOW CREATE DATABASE 数据库名称;
创建数据库: CREATE DATABASE 数据库名称;
创建数据库(判断): CREATE DATABASE IF NOT EXISTS 数据库名称;
创建数据库(指定字符集): CREATE DATABASE 数据库名称 CHARACTER SET字符集名称;
●DDL 修改、删除、使用数据库
修改数据库(修改字符集):ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称
删除数据库(判断): DROP DATABASE( IF EXISTS) 数据库名称
使用数据库: USE 数据库名称
查询当前使用的数据库: SELECT DATABASE();
●DDL 查询数据表
查询所有数据表: SHOW TABLES;
查询表结构: DESC 表名;
查询表数据集: SHOW TABLE STATUS FROM 表名 LIKE '表名';
●DDL 创建数据表
CREATE TABLE 表明(
);
int 整数类型
double 小数类型
data 日期类型。yyyy-MM-dd
datatime 日期类型 yyyy-MM-dd HH:mm:ss
timestamp 时间戳类型 yyyy-MM-dd HH:mm:ss
*如果不给该列赋值,或赋值为null,则默认使用当前系统时间自动赋值
varchar(长度) 字符串类型
●DDL 修改数据表
修改表名: ALTER TABLE 表名 RENAME TO 新表名;
修改表的字符集: ALTER TABLE 表名 CHARACTER SET 字符集名称
单独添加一列: ALTER TABLE 表名 ADD 列名 数据类型;
修改某列数据类型: ALTER TABLE 表名 MODIFY 列名 新数据类型
修改某列名和数据类型: ALTER TABLE 表名 MODIFY 列名 新列名 新数据类型
删除某一列: ALTER TABLE 表名 DROP 列名;
●DDL 删除数据表(判断)
DROP TABLE( IF EXISTS )表名;
●DDL 新增表数据
给指定列添加数据: INSERT INTO 表名(列1,列2,...) VALUEs(值1,值2,...);
给全部列添加数据: INSERT INTO 表名 VALUES(值1,值2,...);
批量添加数据: INSERT INTO 表名(列1,列2,...) VALUES(值1,值2,...),...;
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),...;
3.DML 修改和删除表数据
●修改表中的数据
UPDATE 表名 SET 列名1=值1,列名2=值2,...[WHERE 条件]
DELETE FROM 表名 [WHERE条件]
4.DQL 表数据的查询
●查询全部
查询全部的表数据: SELECT * FROM 表名;
查询指定字段(去重)的表数据: SELECT( DISTINCT) 列名1,列名2,... FROM 表名;
计算列的值(四则运算): SELECT 列名1 运算符(+-*/) 列名2 FROM 表名;
起别名查询: SELECT 列名 AS 别名 FROM 表名;
●条件查询
SELECT 列名 FROM 表名 WHERE 条件
●聚合函数查询
count(列名)、max(列名)、min(列名)、sum(列名)、avg(列名)
SELECT 函数名(列名) FROM 表名 [WHERE 条件];
●排序查询
ASC-升序,DESC-降序
如果有多个排序条件,只有当前面的条件值一样时,才会判断第二条件
SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 排序列名 排序方式,排序列名 排序方式;
●分组查询
SELECT 列名 FROM 表名 [WHERE条件] GROUP BY 分组列名
[HAVING 分组后的条件过滤]
[ORDER BY 排序列名 排序条件];
●分页查询
SELECT 列名列表 FROM 表名
[WHERE 条件]
[GROUP BY 分组列名]
[HAVING 分组后的条件过滤]
[ORDER BY 排序列名 排序方式]
LIMIT 当前页数,每页显示的条数
当前页数 = (当前页数-1) * 每页显示的条数
5. 约束
什么是约束:对表中的数据进行限定,保证数据的正确性、有效性、完整性
●主键约束
非空,唯一
一张表只能有一个主键
主键一般用于表中数据的唯一标识
建表时添加主键约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY,
...
列名 数据类型 约束
);
删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;
建表后单独添加主键约束(相当于修改表结构)
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
●主键自增约束
建表时添加主键自增约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
...
列名 数据类型 约束
);
删除主键自增约束(相当于修改表结构)
ALTER TABLE 表名 MODIFY 列名 数据类型; --不会删除主键约束
建表后单独添加主键自增约束(相当于修改表结构)
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
MySQL中的自增约束,必须配合键的约束一起使用
●唯一约束
建表时添加唯一约束
CREATE TABLE 表名(
列名 数据类型 UNIQUE,
...
列名 数据类型 约束
);
删除唯一约束
ALTER TABLE 表名 DROP INDEX 列名;
建表后单独添加唯一约束
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
●非空约束
建表时添加非空约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL,
...
列名 数据类型 约束
);
删除非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型;
建表后单独添加非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
●外键约束:让表与表之间产生关联关系,从而保证数据的准确性
建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型 约束,
...
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
);
删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
建表后单独添加外键约束
ALTER TABLE 表名 ADD
CONSTRAINT 外键名 FOREIGN KEY(本表外键列名) REFERENCES 主表名(主键列名);
●外键级联操作
外键的级联更新和级联删除
级联更新:当对主表中的数据进行修改时,从表中有关联的数据也会随之修改
级联删除:当把主表中的数据进行删除时,从表中有关联的数据也会随之删除
添加级联更新
ALTER TABLE 表名 ADD
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE;
添加级联删除
ALTER TABLE 表名 ADD
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名 (主键列名)
ON DELETE CASCADE;
同时添加级联更新和级联删除
ALTER TABLE 表名 ADD
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATA CASCADE ON DELETE CASCADE;
6. 多表操作
多表概念:多张数据表之间有一定的关联关系,这种关联关系通过外键约束实现
●一对一
适用场景:人和身份证。一个人只能有一个身份证,一个身份证只能对应一个人。等
建表原则:在任意一个表建立外键,去关联另外一个表的主键
●一对多
适用场景:用户和订单。一个用户可以有多个订单
商品分类和商品。一个分类下可以有多个商品
建表原则:在多的一方,建立外键约束,来关联一的一方的主键
●多对多
适用场景:学生和课程。一个学生可以选择多个课程,一个课程也可以被多个学生选择
建表原则:需要借助第三张中间表,中间表至少包含2个列。这2个列作为中间表的外键列,分别关联2两张表的主键。
●多表查询的数据准备
●内连接查询
查询原理:内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
查询语法:
显式内连接
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
隐式内连接
SELECT 列名 FROM 表名1,表名2 WHERE 条件;
●外连接查询
1. 左外连接
查询原理:查询左表的全部数据,和左右两张表有交集部分的数据
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
2. 右外连接
查询原理:查询右表的全部数据,和左右两张表有交集部分的数据
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
●子查询
概念:查询语句中嵌套了查询语句,嵌套的查询称为子查询
结果是单行单列的
查询作用:可以将查询的结果作为另一条语句的查询条件
SELECT 列名 FROM 表名 WHERE 列名 =(SELECT 列名 FROM 表名 [WHERE 条件]);
结果是多行单列的
查询作用:可以作为条件,使用运算符 IN 或 NOT IN 进行判断
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE条件]);
结果是多行多列的
查询作用:查询的结果可以作为一张虚拟表参与查询
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE条件]) [别名] [WHERE条件];
●自关联查询
概念:在同一张表中数据有关联性,我们可以把这张表当成多个表来查询