一、SQL分类
(1)DDL(Data Definition Language)数据定义语言:
用来定义数据库对象
(2)DML(Data Manipulation Language)数据操作语言:
用来对数据库中表的数据进行增删改查
(3)DQL(Data Query Language)数据查询语言:
用来查询数据库中表的记录(数据)
(4)DCL(Data Control Language)数据控制语言:
用来定义数据库的访问权限和安全级别,及创建用户
二、DDL:操作数据库、表
1. 操作数据库:CRUD
(1)C(Creat):创建
- 创建数据库:
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.操作表
(1)C(Creat):创建
- 创建表:
CREATE TABLE 表名(
列名1 数据类型1,
列名2 数据类型2,
...
列名n 数据类型n
);
- 复制表:
CREATE TABLE 表名 LIKE 被复制的表名;
注:
数据类型 | 关键字 |
---|---|
整数类型 | int |
小数类型 | double |
日期(只包含年月日) | date |
日期(包含时分秒) | datetime |
时间戳类型 | timestamp |
字符串 | varchar |
(2)R(Retrieve):查询
- 查询某个数据库中所有表的名称:
SHOW TABLES;
- 查询表结构:
DESC 表名;
(3)U(Update):修改
- 修改表名:
ALTER TABLE 表名 RENAME TO 新的表名;
- 修改表的字符集:
ALTER TABLE 表名 ADD 列名 数据类型;
- 添加一列:
ALTER TABLE 表名 CHARACTER SET 字符集名称;
- 修改列名称、列的数据类型:
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
--或者
ALTER TABLE 表名 MODIFY 列名 新数据类型;
- 删除列:
ALTER TABLE 表名 DROP 列名;
(4)D(Delete):删除
- 删除表:
DROP TABLE 表名;
- 判断表是否存在,存在再删除表:
DROP TABLE IF EXISTS 表名;
三、DML:增删改表中的数据
1.添加数据
INSERT INTO
表名
(列名1,列名2,...,列名n)
VALUES
(值1,值2,...,值n);
注:
- 列名和值要一一对应;
- 如果表名后,不定义列名,则默认给所有列添加值;
- 除了数字类型,其他类型需要使用引号引起来;
2.删除数据
(1)删除满足条件的数据:
DELETE FROM
表名
WHERE
条件;
(2)删除所有数据(先删除表,然后再创建一张一样的表):
TRUNCATE TABLE 表名;
3.修改数据
UPDATE
表名
SET
列名1 = 值1,
列名2 = 值2,
...
列名n = 值n
WHERE
条件;
四、DQL:查询表中的记录
1.查询语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组之后的条件
ORDER BY
排序
LIMIT
分页限定;
2.基础查询
(1)多个字段的查询:
SELECT
字段1,
字段2,
...
FROM
表名;
注:如果查询所有字段,则可以使用*来代替字段列表。
SELECT * FROM 表名;
(2)去除重复:
DISTINCT
(3)计算列:
一般可以使用四则运算计算一些列的值
/**
表达式1:哪些字段需要判断是否是null
表达式2:如果该字段为null后的替换值
*/
IFNULL(表达式1,表达式2):
(4)起别名:
AS
3.条件查询
(1)where子句后跟条件
(2)运算符:
> 、< 、<= 、>= 、= 、<>
BETWEEN ... AND
IN(集合)
LIKE:模糊查询。占位符:"_"表示单个任意字符;"%"表示任意多个字符;
IS NULL
AND 或 &&
OR 或 ||
NOT 或 !
4.排序查询
(1)语法:
ORDER BY
排序字段1 排序方式1,
排序字段2 排序方式2,
...
(2)排序方式:
- ASC:升序,默认的
- DESC:降序
注:若有多个排序条件,则当前面的条件值一样时,才会判断第二条件。
5.聚合函数
(1)定义:
将一列数据作为一个整体,进行纵向的计算
(2)内容:
- count():计算个数(一般选择非空的列——主键)
- max():计算最大值
- min():计算最小值
- sum():计算和
- avg():计算平均值
注:聚合函数的计算,排除null值。
6.分组查询
(1)语法:
group by 分组字段;
(2)分组后查询的字段:
分组字段、聚合函数
(3)where和havin的区别:
- where在分组之前进行限定,若不满足条件,则不参与分组;having在分组之后限定,若不满足结果,这不会被查询出来。
- where后不可以跟聚合函数,having可以进行聚合函数的判断。
7.分页查询
(1)语法:
limit 开始的索引,每页查询的条数;
(2)公式:
开始的索引 = (当前页码 - 1)* 每页显示的条数
8.约束
- 概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。
(1) 主键约束:primary key
- 创建表时添加约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY
);
- 创建完表后添加主键
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
- 主键的自动增长:如果某列为数值类型,使用auto_increment可以用来完成值的自动增长
--在创建表时,添加主键约束,并完成主键自增长
CREATE TABLE 表名(
列名 数据类型 AUTO_INCREMENT
);
--删除自增长
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
注:主键就是表中记录的唯一标识。主键非空且唯一,一张表只能有一个字段为主键。
(2)非空约束:not null
- 创建表时添加约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL
);
- 创建完表后添加约束
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
- 删除非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型;
(3)唯一约束:unique
- 创建表时添加约束
CREATE TABLE 表名(
列名 数据类型 UNIQUE
);
注:MySQL中,唯一约束限定的列的值可以有多个null。
- 创建完表后添加约束
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
- 删除约束
ALTER TABLE 表名 DROP INDEX 列名;
(4)外键约束:foreign key
外键约束让表和表产生关系,从而保证数据的正确性。
- 创建表时添加外键
CREATE TABLE 表名(
CONSTRAINT 外键名称 FOREIGN KEY REFERENCES 主表名称
);
- 创建完表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY REFERENCES 主表名称;
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
- 级联操作
级联更新:ON UPDATE CASCADE
级联删除:ON DELETE CASCADE
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY REFERENCES 主表名称 ON UPDATE CASCADE ON DELETE CASCADE;
五、多表查询
1.查询语法
SELECT
列名列表
FROM
表名列表
WHERE
...
2.多表查询的分类
(1)内连接查询
- 隐式内连接:使用where条件消除无用数据
SELECT
t1.name,
t1.gender,
t2.name
FROM
emp t1,
dept t2
WHERE
t1.'dep_id' = t2.'id';
- 显示内连接
SELECT
字段列表
FROM
表名1
JOIN
表名2
ON
条件;
(2)外连接查询
- 左外连接:查询的是左表所有数据及其交集部分。
SELECT
字段列表
FROM
表名1
LEFT JOIN
表名2
ON
条件;
- 右外连接:查询的是右表所有数据及其交集部分。
SELECT
字段列表
FROM
表名1
RIGHT JOIN
表名2
ON
条件;
(3)子查询: 查询中嵌套查询,称嵌套查询为子查询。
六、事务
1.事务的基本介绍
(1)概念:如果一个包含多个步骤的业务操作被事务管理,那么这些操作要么同时成功,要么同时失败。
(2)操作:
- 开启事务
START TRANSACTION;
- 回滚
ROLLBACK;
- 开启事务
COMMIT;
(3)事务的提交:
- 自动提交:
MySQL就是自动提交的;一条DML(增删改)语句会自动提交一次事务。 - 手动提交:
需要先开启事务,再提交。 - 修改事务的默认提交方式:
--查看事物的默认提交方式
SELECT @@AUTOCOMMIT; -- 1 代表自动提交 0 代表手动提交
--修改事务的默认提交方式
SET @@AUTOCOMMIT = 0;
2.事务的四大特征
(1)原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
(2)持久性:当事务提交或回滚后,数据库会持久化的保存数据。
(3)隔离性:多个事务之间,相互独立。
(4)一致性:事务操作前后,数据总量不变。
3.事务的隔离
(1)事务隔离产生的问题:
- 脏读: 一个事务读取到另一个事务中没有提交的数据。
- 虚读(不可重复读): 在同一个事务中,两次读取到的数据不一样。
- 幻读: 一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
(2)隔离级别:
- 读未提交
--产生的问题:脏读、虚读、幻读
READ UNCOMMITTED;
- 读已提交
--产生的问题:虚读、幻读
READ COMMITTED;
- 可重复读
--产生的问题:幻读
REPEATABLE READ;
- 串行化
--可以解决所有问题
SERIALIZABLE;
(3)隔离级别的查询和设置
- 查询隔离级别
SELECT @@TX_ISOLATION;
- 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别字符串;