#DDL操作数据库

#DDL:操作数据库

#查询所有的数据库
SHOW DATABASES
#创建数据库
CREATE DATABASE 数据库名称
#创建数据库（判断，如果不存在则创建）
CREATE TABLE IF NOT EXISTS 数据库名称
#删除数据库
DROP DATABASE 数据库名称
#删除数据库（判断，如果存在则删除）
DROP DATABASE IF EXISTS 数据库名称
#使用数据库
USE 数据库名称
#查看当前使用的数据库
SELECT DATABASE();

#DDL操作表

#查询当前数据库下所有的表名称
SHOW TABLES;
#查询表结构
DESC 表名称;
#创建表
CREATE TABLE 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
#删除表
DROP TABLE 表名;
#删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
#修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
#添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
#修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
#修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
alter table stu change address add varchar(50);
#删除列
ALTER TABLE 表名 DROP 列名

#DML数据增删改操作

#给指定列添加数据
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);
#修改表数据
UPDATE 表名 SET 列名1=值1,列名1=值1,...[WHERE 条件];
#删除数据
DELETE FROM 表名 [WHERE 条件];
#删除所有数据
DELETE FROM 表名

#DQL基础查询

#查询多个字段
SELECT 字段列表 FROM 表名;
#查询所有数据
SELECT * FROM 表名;
#去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
#起别名
AS:AS #也可以省略

#语法

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

#查询年龄大于20的学员信息
select * from stu where age>20;
#查询年龄大于等于20并且年龄小于等于30的
select * from stu where age>=20 and age<=30;
#betwen查询
select * from stu where age between 20 and 30;
#查询1入学时间在1998-09-01到1999-09-01之间的学员信息
select * from stu where hire_date between '1998-09-01' and '1999-09-01';
#查询年龄等于18的学员信息
select * from stu where age=18;
#查询年龄不等于18的学员信息
select * from stu where age!=18;
select * from stu where age<>18;
#查询年龄等于18或者年龄大于等于20或者年龄等于22的学员信息
select * from stu where age=18 or age=20 or age=22;
select * from stu where age in (18,20,22);
#查询英语成绩为null的学员信息
#null值比较不能用=或者!=。需要用is 或者 is not
select * from stu where english is null;
#模糊查询使用like关键字
#_:代表单个任意字符
#%:代表任意个数字符
#查询姓马的学员信息
select * from stu where name like '马%';
#查询第二个字是花的学员信息
select * from stu where name like '_花%';
#查询名字中包含德的学员信息
select * from stu where name like '%德%'
#排序查询
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],排序字段名2[排序方式2];
ASC:升序排列（默认值）
DESC：降序排列
#注：如果有多个排序条件，当前门的条件之一样时，才会根据第二条件进行排序
#查询学生辛，按照年龄升序排序
select * from stu order by age;
#查询学生信息，按照数学成绩降序排列
select * from stu order by math desc;
#查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math desc,english asc;

#聚合函数

count(列名)：统计数量
max(列名)：最大值
min(列名)：最小值
sum(列名)：求和
avg(列名)：求平均值
SELECT 聚合函数名(列名) FROM 表;#null值不参与所有聚合函数的运算
#统计班级一共多少学生
select count(id) from stu;
#查询数学成绩最高分
select max(math) from stu;
#查询数学成绩最低分
select min(math) from stu;
#查询数学成绩总分
select sum(math) from stu;
#查询数学成绩的平均分
select avg(math) from stu;

#分组查询

SELECT 字段列表 FROM 表名[WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
#注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
#查询男同学和女同学各自的数学平均分
select sex,avg(math) from stu group by sex;
#查询男同学和女同学各自的数学平均分及各自人数
select name,sex,avg(math),count(*) from stu group by sex;
#查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex,avg(math),count(*) from stu where math>70 group by sex;
#查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组后人数大于4个
select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>4;
#where和having的区别：
*执行实际不一样：where是分组之前进行限定，不满足where条件，则不参与分组，
而having是分组之后对结果进行过滤
*可判断的条件不一样:where不能对聚合函数进行判断，having可以。

#分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
#注：上述语句中的起始索引从0开始
#从0开始查询查询3条数据
select * from stu limit 0,3;
#每页显示3条数据，查询第一页数据
select * from stu limit 0,3;
#每页显示3条数据，查询第二页数据
select * from stu limit 3,3;
#每页显示3条数据，查询第三页数据
select * from stu limit 6,3;
索引计算公式
起始索引=(当前页码-1)*每页显示的条数

约束

非空约束：NOT NULL
唯一约束：UNIQUE
主键约束：PRIMARY KEY
检查约束：CHECK
默认约束：DEFAULT
外键约束：FOREIGN KEY

1.3
非空约束
创建表时添加非空约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL,
...
);
建表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
删除约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;
1.4
唯一约束:用于保证列中所有数据各不相同
创表时添加唯一约束
CREATE TABLE 表名(
列名 数据类型 UNIQUE [AUTO_INCREMENT],
--AUTO_INCREMENT:当不指定值时自动增长
)
建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
删除约束
ALTER TABLE 表名 DROP INDEX 字段名
1.5
主键约束：主键是一行数据的唯一表示，要求非空且唯一
一张表只能有一个主键
添加约束
创建表时添加主键约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
...
);
CREATE TABLE 表名(
列名 数据类型,
[CONSTRATNT] [约束名称] PRIMARY KEY(列名)
);
建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
删除约束
ALTER TABLE 表名 DROP PRIMARY KEY;
1.6
默认约束：保存数据时，为指定值则采用默认值
添加约束
创建表时添加默认约束
CREATE TABLE 表名(
	列名 数据类型 DEFAULT 默认值,
	...
);
建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
删除约束
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

1.8
外键约束：让两个表的数据之间建立连接，保证数据的一致性和完整性
添加外键约束：
创建表时添加外键约束
CREATE TABLE 表名(
	列名 数据类型,
	...
	[CONSTRATNT] [外键名称] FOREIGN KEY (外键字段名称) DEFERENCES 主表名称(主表列名称)
);
删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

数据库设计

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造处最优的数据存储模型。

建立数据库中的表结构以及表与表之间关联关系的过程

数据库设计步骤

需求分析——逻辑分析（ER图）——物理设计——维护设计（1、对新的需求进行建表，2、表优化）

表关系

一对一：用户和用户详情

实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）

一对多：部门和员工

实现方式：在多的一方建立外键，指向一的一方的主键

多对多：商品和订单

实现方式：建立第三张中间表，中间表至少包含两个外键，分别管理两方主键

多表查询

创建部门表
create table dept(
did int primary key auto_increment,
dname varchar(20)
);
创建员工表
create table emp(
id int primary key auto_increment,
name varchar(10),
gender char(1),
salary double,
join_date date,
dep_id int,
foreign key(dep_id) references dept(did)#外键关联部门表
);
添加部门数据
insert into dept(dname) values ('研发部'),('市场部'),('财务部'),('销售部');
添加员工数据
insert into emp(name,gender,salary,join_date,dep_id) VALUES
('小米','男',7200,'2013-02-24',1),
('华为','男',5000,'2011-02-24',2),
('荣耀','男',3600,'2013-02-24',3),
('oppo','女',6000,'2007-02-24',4),
('vivo','女',1500,'2006-02-24',2),
('iPhone','男',9000,'2004-02-24',1);
查询所有，重复显示多条相同数据
select * from emp,dept;
去除无效数据
select * from emp,dept where emp.dep_id=dept.did;
连接查询
	内连接查询：相当于查询交集
	外连接查询
			左连接查询：相当于查询A表所有数据和交集部门数据
			右连接查询：相当于查询B表所有数据和交集部门数据

内连接查询

语法：
隐式内连接
SELECT 字段列表 FROM 表1,表2...WHERE 条件;
显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
内连接相当于查询AB交集数据
例：隐式内连接
SELECT * FROM emp,dept WHERE emp.dep_id =dept.did;
查询emp的name,gender,dept表的dname
SELECT emp.name,emp.gender,dept.dname FROM emp,dept WHERE emp.dep_id=dept.did;
给表指别名
SELECT t1.name,t1.gender,t2.dname FROM emp t1,dept t2 WHERE t1.dep_id=t2.did;
显示内连接
SELECT * FROM emp INNER JOIN dept ON emp.dep_id=dept.did;
省略inner
SELECT * FROM emp JOIN dept ON emp.dep_id=dept.did;

外连接查询

左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
右外连接
SELECT 字段列表 FROM RIGHT [OUTER] JOIN 表2 ON 条件;
例：
查询emp表所有数据和对应的部门信息(左外连接)
SELECT * FROM emp LEFT JOIN dept ON emp.dep_id=dept.did;
查询dept表所有数据和对应的员工信息(右外连接)
SELECT * FROM emp RIGHT JOIN dept ON emp.dep_id=dept.did;
查询部门表中所有的数据，通过左外连接实现
SELECT * FROM dept LEFT JOIN emp ON emp.dep_id=dept.did;

子查询

概念：查询中嵌套查询,称嵌套查询为子查询
查询工资比OPPO高的员工信息
1:查询OPPO的工资
SELECT salary FROM emp WHERE name='oppo';
2:查询工资高于OPPO的员工信息
SELECT * from emp WHERE salary >6000;
3:将第一步的sql语句替换
SELECT * FROM emp where salary >(SELECT salary from emp WHERE name='oppo');
子查询根据查询结果不同，作用不同
·子查询语句是单行单列，子查询语句作为条件值，使用= != > <等条件判断
·子查询语句结果是多行单列，子查询语句作为条件值，使用IN等关键字进行条件判断
·子查询语句结果是多行多列，子查询语句作为虚拟表
例：查询'财务部'和'市场部'所有员工的部门did
SELECT did FROM dept WHERE dname='财务部' OR dname='市场部';

SELECT * FROM emp WHERE dep_id IN (SELECT did FROM dept WHERE dname='财务部'OR dname='市场部');

查询入职日期是'2011-11-11'之后的员工信息和部门信息
SELECT * FROM emp WHERE join_date >'2011-11-11';
将上面的语句的结果作为虚拟表和dept表进行内连接查询
SELECT * FROM (SELECT * FROM emp WHERE join_date>'2011-11-11')t1,dept
WHERE t1.dep_id=dept.did;

案例练习：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;#把之前建立的表删除，避免建表失败
部门表
CREATE TABLE dept(
did int primary key,
dname varchar(50),		#部门名称
loc varchar(50)		#部门所在地
);
职务表，职务名称，职务描述
CREATE TABLE job(
id int PRIMARY KEY,
jname varchar(20),
description varchar(50)
);
员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id 
ename VARCHAR(50), -- 员工姓名 
job_id INT, -- 职务id 
mgr INT , -- 上级领导 
joindate DATE, -- 入职日期 
salary DECIMAL(7,2), -- 工资 
bonus DECIMAL(7,2), -- 奖金 
dept_id INT, -- 所在部门编号 
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), 
CONSTRAINT emp_deptdid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (did) 
);
工资等级表
CREATE TABLE salarygrade(
grade int primary key,
losalary int,		#最低工资
hisalary int		#最高工资
);

添加四个部门
insert into dept(did,dname,loc)values
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
select * from dept;
添加四个职务
insert into job(id,jname,description)values
(1,'董事长','管理公司，接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');
SELECT * from job;
添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20), 
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
 (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30), 
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), 
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), 
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), 
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10), 
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20), 
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10), 
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30), 
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), 
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), 
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20), 
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 添加5个工资等级 
select * from salarygrade
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000), 
(3,14010,20000), 
(4,20010,30000), 
(5,30010,99990);

需求
1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
方式一：隐式内连接
select emp.id,emp.ename,emp.salary,job.jname,job.description
from emp,job where emp.job_id=job.id;
方式二：显示内连接
select emp.id,emp.ename,emp.salary,job.jname,job.description
from emp inner join job on emp.job_id=job.id;
2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
方式一：隐式内连接
select emp.id,emp.ename,emp.salary,job.description,dept.dname,dept.loc
from emp,job,dept where emp.job_id=job.id and dept.did=emp.dept_id;
方式二：显示内连接
select emp.id,emp.ename,emp.salary,job.description,dept.dname,dept.loc
from emp inner join job on emp.job_id=job.id
inner join dept on emp.dept_id=dept.did;
3. 查询员工姓名，工资，工资等级
select emp.ename,emp.salary,t2.*
from emp,salarygrade t2
where emp.salary>=t2.losalary
and emp.salary<=t2.hisalary;
4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
显示内连接
select emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,t2.grade
from emp inner join job on emp.job_id=job.id 
inner join dept on emp.dept_id=dept.did
inner join salarygrade t2 on emp.salary between t2.losalary and t2.hisalary;
5. 查询部门编号，部门名称，部门位置，部门人数
select dept.*,t1.count from dept,(select dept_id,count(*) count 
from emp group by dept_id) t1 where dept.did=t1.dept_id;

事务

数据库的事务时一种机制，一个操作序列，包含了一组数据库操作命令。

事务把所有的命令作为一个整体一起向系统提交或者撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

事务是一个不可分割的工作逻辑单元。

语法

开启事务
START TRANSACTION
或者
BEGIN;
提交事务
commit;
回滚事务
rollback;

事务的四大特性

·原子性：事务是不可分割的最小操作单位，要么同时成功，要么同时失败

·一致性：事务完成时，必须使所有的数据保持一致状态

·隔离性：多个事务之间，操作的可见性

·持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的

说明：MySQL中事务时自动让提交的

也就是说，我们不提交事务执行SQL语句，语句执行完毕会自动的提交事务

也可以通过下面的语句查询默认提交方式：

SELECT @@autocommit;
查询到的结果时1则表示自动提交，结果是0则表示手动提交，当然也可以通过语句来修改提交方式
set @@autocommit=0;