DDL–数据库、表的操作

操作数据库

查询数据库

1
SHOW DATABASES;

创建数据库

1
2
CREATE DATABASE 数据库名称;
CREATE DATABASE IF NOT EXISTS 数据库名称;

删除数据库

1
2
DROP DATABASE 数据库名称;
DROP DATABASE IF EXISTS 数据库名称;

使用数据库

1
2
SELECT DATABASE(); -- 查看当前使用的数据库
USE 数据库名称; -- 使用数据库

操作表

查询表

1
2
SELECT TABLES; -- 查询当前数据库下的表
DESC 表名; -- 查看表结构

创建表

1
2
3
4
5
6
CREATE TABLE 表名 (
    字段名1 数据类型1,
    字段名2 数据类型2,
    ...
    字段名n 数据类型n
);

删除表

1
2
DROP TABLE 表名；
DROP TABLE IF EXISTS 表名;

修改表

1
2
3
4
5
ALTER TABLE 表名 RENAME TO 新表名; -- 修改表名
ALTER TABLE 表名 ADD 列名 数据类型; -- 添加新的列
ALTER TABLE 表名 MODIFY 列名 新数据类型; -- 修改数据类型
ALTER TABLE 表名 CHANGE 新列名 新数据类型; -- 修改列名和数据类型
ALTER TABLE 表名 DROP 列名; -- 删除列

DML–表中数据的增删改

添加数据

1
2
3
4
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,...); -- 批量添加数据

修改数据

1
UPDATE 表名 SET 列名1=值1，列名2=值2,...[WHERE 条件]; -- 不加条件所有的数据都会修改

删除数据

1
DELETE FROM 表名 [WHERE 条件]; -- 不加条件所有的数据都会删除

DQL–表中数据的查询

查询语法

1
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件 ORDER BY 排序字段 LIMIT 分页限定;

基础查询

1
2
3
4
SELECT 字段列表 FROM 表名;
SELECT *FROM 表名; 
SELECT DISTINCT 字段列表 FROM 表名; -- 去除重复记录
AS -- 别名

条件查询

1
SELECT 字段列表 FROM 表名列表 WHERE 条件列表;
符号 功能 示例
= 等号，检测两个值是否相等，如果相等返回true (A = B) 返回false
<> 或 != 不等于，检测两个值是否相等，如果不相等返回true (A != B) 返回 true
> 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true (A > B) 返回false
< 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true (A < B) 返回 true
>= 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true (A >= B) 返回false
<= 小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true (A <= B) 返回 true
BETWEEN … AND … 在某个范围之间(都包含)
IN(…) 多选一
LIKE 占位符 模糊查询 _单个任意字符 %多个字符 where like ‘字符%’ 以字符开头
IS NULL 是NULL
IS NOT NULL 不是NULL
AND 或 && 并且,与
OR 或 || 或者,或
NOT 或 ！ 非,不是

排序查询

语法

1
2
3
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式1] ...;
-- 排序方式：ASC:升序(默认) DESC: 降序
-- 有多个排序条件时,当前一个条件值一样时才用根据第二个条件排序

分组查询

聚合函数

以列数据为整体,纵向计算

函数名 功能
count(列名) 统计数量(一般不包含NULL的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均

语法

1
SELECT 聚合函数名(列名) FROM 表; -- NULL值不参与所有聚合函数的运算

窗口函数

函数名 功能
rank
dense_rank
row_number

分组查询语法

1
SELECT 字段列表 FROM 表名 [WHERE 分组条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]; -- 分组后,查询的字段为聚合函数和分组字段,查询其他字段无意义

where和having的区别:

  • 执行的时机不一样:where是分组之前进行限定，不满足条件不参与分组，而having是分组后对结果进行过滤。
  • 可判断的条件不一样:where不能对聚合函数进行判断,having可以。

执行顺序:where > 聚合函数 > having

分页查询

语法

1
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;

DCL–数据库的权限控制

约束

名称 描述 关键字
非空约束 保证列中所有数据不能有null值 NOT NULL
唯一约束 保证列中所有数据各不相同 UNIQUE
主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
检查约束 保证列中的值满足某一条件 CHECK
默认约束 保存数据时，未指定值则采用默认值 DEFAULT
外键约束 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性 FOREIGN KEY

Mysql没有检查约束

外键

1
2
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_从表名_主表名 FPOREIGN KEY (dep_id) REFERENCES dept(id)

多表查询

内连接

1
2
3
4
5
-- 隐式查询
SELECT 字段列表 FROM 表1,表2 WHERE 条件;
-- 显示内连接
SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件; 
SELECT 字段列表 FROM 表1 JOIN 表2 ON 条件;

外连接

1
2
3
4
5
6
-- 左连接
select 字段列表 from 表1 left outer join 表2 on 条件;
select 字段列表 from 表1 left join 表2 on 条件;
-- 右连接
select 字段列表 from 表1 right outer join 表2 on 条件;
select 字段列表 from 表1 right join 表2 on 条件;

子查询

1
2
3
4
5
6
-- 单行单列:作为条件值,使用 =!=><等进行条件判断
select 字段列表 from 表 where 字段名 = (子查询);
-- 多行单列:作为条件值,使用in等关键字进行条件判断
select 字段列表 from 表 where 字段名 in (子查询);
-- dip
select 字段列表 from (子查询) where 条件;

事务

事务简介

  • 数据库的事务(Transaction)是一种机制、一个操作序列，包含了一组数据库操作命令
  • 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
  • 事务是一个不可分割的工作逻辑单元
    1
    2
    3
    4
    5
    6
    7
    8
    -- 开启事务
    start transaction;
    或
    begin;
    -- 提交事务
    commit;
    -- 回滚事务
    rollback;

    事务的四大特征

  • 原子性(Atomicity):事务是不可分割的最小操作单位，要么同时成功，要么同时失败
  • 一致性(Consistency):事务完成时，必须使所有的数据保持一致状态
  • 隔离性(Isolation):多个事务之间，操作的可见性
  • 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

    提交事务

    1
    2
    3
    4
    5
    -- 查看事务的默认提交方式
    select @@autocommit;
    -- 1 自动提交 0 手动提交
    -- 修改事务提交方式
    set @@autocommit = 0;

Oracle 默认手动提交事务