Mysql

1.约束
2.数据库设计
3.多表查询
4.事物
5.新增/删除/修改语句
6.单表条件查询
7.函数
8.创建数据库/数据表
9.索引概述
10.存储引擎
11.设计表

执行顺序

第一步：from 指定要操作的表

第二步：join 连接表生成一个笛卡尔积

第三步：on 对笛卡尔积进行筛选

第四步：where 筛选条件

第五步：group by 对数据进行分组

第六步：max min avg count sum 执行分组函数

第七步：having 对分组后的数据进行过滤

第八步：select 选取结果

第九步：distinct 去除重复结果

第十步：order by 将结果进行排序

第十一步：limit 取结果集中的部分数据

约束

• 非空约束，保证列表中数据，不能为null (NOT NULL)
• 唯一约束，保证列中所有数据各不相同 (UNIQUE)
• 主键约束，主键字段是唯一标识，非空且唯一，一张表只有一个 (PRIMARY KEY)
• 检查约束，保证列中值满足某一条件（mysql不支持） (CHECK)
• 默认约束，保存数据时，未指定值采用默认值 (DEFAULT)
• 外键约束，外键约束让两个表之间建立连接，保证数据的一致性和完整性 (FOREIGN KEY)
• 自动增长，auto_increment

约束运用

-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
joindate DATE NOT NULL , -- 入职日期，非空
salary DOUBLE(7,2) NOT NULL , -- 工资，非空
bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);
1
2
3
4
5
6
7
8

添加非空约束

--新建：
-- 创建表时添加非空约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL,
…
);
-- 建完表后删除
ALTER TABLE 表名 MODIFY 字段名 数据类型;
-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
-- 删除非空约束
ALTER TABLE emp MODIFY ename VARCHAR(50);
-- 新建非空约束
ALTER TABLE emp MODIFY ename VARCHAR(50) NOT NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

添加唯一约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
列名 数据类型 UNIQUE [AUTO_INCREMENT],
-- AUTO_INCREMENT: 当不指定值时自动增长
…
);
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [约束名称] UNIQUE(列名)
);

-- 删除唯一约束
 ALTER TABLE emp DROP INDEX ename;
-- 新建唯一约束
 ALTER TABLE emp MODIFY ename VARCHAR(50) UNIQUE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

默认约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
列名 数据类型 DEFAULT 默认值,
…
);

--  删除默认
ALTER TABLE emp ALTER bonus DROP DEFAULT;
--  新建默认约束
ALTER TABLE emp ALTER bonus SET DEFAULT 0;
1
2
3
4
5
6
7
8
9
10

主键约束

-- 删除主键约束，要先删除自增
alter table emp modify id int;
-- 删除主键约束
ALTER TABLE emp DROP PRIMARY KEY;
-- 添加主键约束
ALTER TABLE emp ADD PRIMARY KEY(id);
-- 添加自增
alter table emp modify id INT  auto_increment;
-- 设置自增默认值
alter table emp  auto_increment=3; 
1
2
3
4
5
6
7
8
9
10

外键约束
添加外键约束，当主表想要删除关联从表的数据时，需要看从表有没有关联的数据，如果有的话，不能删除，需要删除从表的数据后，才可以删除主表的数据

-- 部门表
CREATE TABLE dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
-- CONSTRAINT fk_emp_dept(别名) FOREIGN KEY(dep_id)->从表连接id  REFERENCES dept(id)->主表连接id
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

数据库设计

需求分析-设计-编码-测试-安装部署
一对多：在多的一方建立外键，指向主表
多对多：中间表包含两个外键
一对一：在任意一方建立外键，关联主键，设置唯一

多表查询

多表查询
内连接：相当于查询A B的交集数据
外连接：（左外连接/右外连接）查询A表所有的数据和与B表有交集的数据
内连接
select 字段 from 表1，表2 where 条件
select 字段 from表1 join 表2 on 条件

-- 隐式内连接
SELECT a1.NAME,a1.gender,a2.dname FROM emp as a1, dept as a2 WHERE a1.dep_id = a2.did
-- 显示内连接
SELECT a1.NAME,a1.gender,a2.dname FROM emp as a1 JOIN dept as a2  ON a1.dep_id = a2.did;
1
2
3
4

外连接
左外连接
右外连接
子查询

语法：

单行单列->作为作为值 使用 > < = != 作为判断：

SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '猪八戒')
1

多行单列 ->当查询的结果存在多个时：

SELECT * from emp WHERE dep_id in (SELECT did FROM dept WHERE dname ='财务部' or dname = "市场部"); 
1

多行多列 作为虚拟表 -> 查询出的表为结果集时，将当前当作虚拟表来看：
跟在from后面：

SELECT * FROM (SELECT * FROM emp WHERE join_date > '2011-11-11')  t1 , dept where  t1.dep_id = dept.did
1

事物

事物：事物把所有命令看作一个整体提交或撤销，即一组数据要么同时成功，要么同时失败. 开启事物时，先进性虚拟存储，成功或回滚后，才能持久化操作.
1.开启事物 begin / start Transaction
2.操作
3.提交事物 commit 或者 回滚事物 Rollback

事物的四大特征：
ACID
1.原则性（Atomicity）事物要么同时成功，要么同时失败
2.一致性 （Consistency）事物完成时，必须保持一致
3.隔离性 （Isolation）多个事物之间，可以同时操作数据
4.持久性 （Durability）事物一旦提交或回滚，数据库中的数据永久保存

mysql事物是默认提交的
select @@autocommit 查看事物的提交方式
set @@autocommit = 0； 修改事物的提交方式

新增语句

INSERT INTO 表名(字段) VALUES (字段对应的值)
INSERT INTO 表名 VALUES (字段对应的值)
1
2

删除语句

-- 删除单条数据
DELETE FROM 表名 WHERE 字段 = 条件 ;
-- 删除多条数据
DELETE FROM 表名
1
2
3
4

更新语句

-- 更新一条数据
UPDATE user  SET 字段 = 将要修改的值 WHERE 字段 = 修改的条件; 
-- 更新全部数据
UPDATE user  SET  字段 = 将要修改的值  更新表中的全部数据
1
2
3
4

单表条件查询

字段去除重复

查看当前去除重复的字段
SELECT DISTINCT 字段 FROM 表名;
1
2

WHERE 条件判断

SELECT * FROM 表名 WHERE 字段 <   >  =    <=   >=   !=   值
1

BETWEEN AND 在xx 与 xx之间

当前字段的数据  值1 <= 字段数据 <= 值2
SELECT * FROM 表名 WHERE 字段 BETWEEN 值1 AND 值2 
1
2

Like 模糊查询

Select * from emp where 字段 like 'M%';    M开头的
案例
Select * from 表名 where 字段 like '5%';       以为5开头的数据
Select * from 表名 where 字段 like '%1';       结尾以1开始数据
Select * from 表名 where 字段 like '%5%';      中间为5的数据
Select * from 表名 where 字段 like '_5%';      任意数据开头+5的数据

1
2
3
4
5
6
7

in 多条件查询

SELECT * FROM 表名 WHERE 字段 IN (值1，值2...)
1

REGEXP 当前字段数据是否在指定范围

SELECT * FROM 表名 WHERE 字段 REGEXP   '[a-e]';      字段包含a-e的全部数据
SELECT * FROM 表名 WHERE 字段 REGEXP   '[^a-e]';     字段不包含a-e的全部数据
1
2

OR 或者 AND 而且

语法 
select * from 表名 where 字段 (条件表达式=<>) 字段值  AND/or  字段 （条件表达式=<>） 
-- 用法
userid = 2 并且 username = b
select * from user1 where userid = 2 AND username = 'b';
userid = 2 或者 username = a
select * from user1 where userid = 2 or username = 'a';
1
2
3
4
5
6
7

LIMIT分页每页显示的数据

select * from 表名 LIMIT 每页显示的数据;
使用：
查询10条数据，索引从0到9，第1条记录到第10条记录
select * from 表名 LIMIT 10; 
查询8条数据，索引从5到12，第6条记录到第13条记录
select * from 表名 LIMIT 5,8;
1
2
3
4
5
6

表/字段起别名

表起别名
select a.字段,b.字段 from 表名 as a, 表名 as b where  xxxxxxxx;
字段起别名
SELECT 字段 AS 别名, 字段 AS 别名  FROM 表名;
1
2
3
4

GROUP BY 分组函数

SELECT * FROM 表名 GROUP BY 分组值;
使用->根据名称分组，查询同组的相同记录数
SELECT count(username),username '记录数' FROM user1 GROUP BY username;
1
2
3

order by 排序

ASC 从小到大
select * from 表名 order by 字段 ASC;
DESC 从大到小
select * from 表名 order by 字段 DESC;
1
2
3
4

函数

搭配group by进行查询

select 函数 from 表名
聚合函数 使用
count -> 返回总记录数  count(*) 所有的记录数  count(字段) 当前字段的记录数 
select count(*) from emp 
max(列名)  -> 最大值 
select max(salary) from emp 
min(列名)  ->  最小值
select min(salary) from emp 
sum(求和)  ->  求和
select sum(salary) from emp 
avg(字段)
SELECT avg(salary) FROM emp
1
2
3
4
5
6
7
8
9
10
11
12

创建数据库/表

创建数据库
CREATE DATABASE   库名；


删除数据库
drop database  数据库名;

选择数据库
use 数据库名;

新建数据表
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
joindate DATE NOT NULL , -- 入职日期，非空
salary DOUBLE(7,2) NOT NULL , -- 工资，非空
bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

删除数据表
DROP table 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

索引