数据库设计

一、DDL

1、定义

数据定义语言，用来定义数据库对象(数据库，表，字段)；

2、数据库操作

#创建数据库

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;

#查询所有数据库和查询单个数据库

show databases ;        select   database;

#删除数据库

drop  database [ if exists ] 数据库名 ;

#切换数据库

use 数据库名称；

2、表操作

#查看表结构

desc 表名；

#查询指定表的建表语句

show create table 表名；

#创建表结构

CREATE TABLE 表名 (

字段 1 字段 1 类型 [ COMMENT 字段 1 注释 ],

字段 2 字段 2 类型 [COMMENT 字段 2 注释 ],

字段 3 字段 3 类型 [COMMENT 字段 3 注释 ],

......

字段 n 字段 n 类型 [COMMENT 字段 n 注释 ]

) [ COMMENT 表注释 ] ;

注意 : [...] 内为可选参数，最后一个字段后面没有逗号

#表添加字段

ALTER TABLE 表名 ADD 字段名 类型 ( 长度 ) [ COMMENT 注释 ] [ 约束 ];

#修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型 ( 长度 );

# 修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 ( 长度 ) [ COMMENT 注释 ] [ 约束 ];

#删除字段

ALTER TABLE 表名 DROP 字段名 ;

#修改表名

ALTER TABLE 表 名 RENAME TO 新表名 ;

#删除表

DROP TABLE [ IF EXISTS ] 表名 ;

#删除指定表 , 并重新创建表

TRUNCATE TABLE 表名;

二、DML

1、定义

用来对数据库中表的数据记录进行增、删、改操作。例如INSERT、UPDATE、DELETE。

2、具体操作

#添加语句

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

注意：多个insert语句同时执行，每个语句后面都要加上“；”

  #批量添加语句

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 条件 ] ;

#删除语句

DELETE FROM 表名 [ WHERE 条件 ] ;

三、DQL

1、定义

数据查询语言，用来查询数据库中表的记录。

2、具体操作和语法

#基本查询操作

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

SELECT * FROM 表名 ;

#去重操作

SELECT DISTINCT 字段列表 FROM 表名;

#条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

#聚合函数

SELECT 聚合函数(字段列表) FROM 表名 ;

聚合函数是——统计个数：count、最大/小值：max/min、平均值：avg、求和：sum

#分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组

后过滤条件 ];

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

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

#排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段 1 排序方式 1 , 字段 2 排序方式 2 ;

排序方式：ASC升序（默认）、DESC降序

#分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询记录数 ;

注意： ①起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数、 ②分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。 ③如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

总结执行顺序： from— where— group by— having— select— order by— limit

 四、约束

1、概念

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2、目的

保证数据库中数据的正确、有效性和完整性。

3、各类约束

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY  KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
外键约束	用来让两张表的数据之间建立连接，保证数据的一致 性和完整性	FOREIGN KEY

4、外键约束

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

#添加外键

CREATE TABLE 表名( 字段名 数据类型, ......

[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)

REFERENCES 主表 (主表列名) ;

#删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

五、多表查询

1、表之间的存在关系

一对多、多对一、多对多、一对一。

2、内连接

内连接查询的是两张表交集部分的数据。分为隐式内连接和显式内连接

①隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

给表起别名操作

表1 as 别名1 , 表2 as 别名2 ; 或者  表1 别名1 , 表2 别名2 ;

如果你给表起别名，就不能再使用表名来指定对应的字段了，只能够使用别名来指定字段

②显式内连接 

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

3、外连接 

①左外连接：左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

 ②右外连接：右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

 4、自连接

①自连接查询

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

注意： 在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段

②联合查询 ：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...

UNION [ ALL ]

SELECT 字段列表 FROM 表B ....;

注意：① 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。②union all 会将全部的数据直接合并在一起， union 会对合并之后的数据去重。③ 如果多条查询语句查询出来的结果，字段数量不一致，在进行 union/union all 联合查询时，将会报错。

六、事务

1、什么是事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

2、事务操作

#查看/设置事务提交方式

SELECT @@autocommit ;

SET @@autocommit = 0 ;  //0是手动提交；1是自动提交

#提交事务

commit

#回滚事务

rollback

#开启事务

START TRANSACTION 或 BEGIN ;

3、事务的四大特性 

①原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

②一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。

③隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

④持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

 4、并发事务

①赃读：一个事务读到另外一个事务还没有提交的数据。

②不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。

③幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 "幻影"。

5、事务的隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read( 默认 )	×	×	√
Serializable	×	×	×

#查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

#设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意： 事务隔离级别越高，数据越安全，但是性能越低。