MySql

导读

• mysql5.7：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

1. mysql -h 主机名 -P 端口 -u 用户名 -p密码

2. 登录前保持服务启动

   1、服务方式启动

   2、net stop mysql

   3、net start mysql

• navicat安装：navicat.com.cn/products/
• sqlyog安装：sqlyog.en.softonic.com

一、数据库

创建数据库

• 案例1：创建一个名为robber_db01数据库
  • create database if not exists robber_db01
• 案例2：创建一个使用utf8字符集的数据库
  • create database robber_db02 character set utf8
• 案例3：创建以恶搞使用utf8字符集，并带校对规则的robber_db03
  • create database robber_db03 character set uft8 collate utf_in

校对规则用于区分大小写

①select * form student where name = “Tom”

①select * form student where name = “tom”

查看、删除数据库

• 显示数据库语句：select database
• 显示数据库创建语句：select create database robber_db01
• 数据库删除：drop database if exists robber_db01

若数据库存在关键字则使用反引号 create

备份恢复数据库

• 备份数据库（DOS执行）：mysqldump -u 用户名 -p 数据库1 数据库2 数据库n > d:\\文件名.sql
• 恢复数据库（注意：进入sqllog再执行)：Source d:\\文件名.sql
• 案例：备份robber_db01和robber_db02并恢复数据库

二、表

创建

• 案例：创建一个员工表

create table emp(
    id int,
    name varchar(32),
    sex char(1),
    birthday date,
    entry_date date,
    job varchar(32),
    salary decimal,
    resume text
)character set utf8 collate utf8_bin engine innodb

数据类型

数值类型	数据类型
整形	bit(M) tinyint【1字节】 smallint【2字节】 mediumint【3字节】 int【4字节】 bigint【8字节】
小数类型	float【单精度4个字节】 double【双精度8字节】 decimal(M,D)【大小不确定】

decimal注意：如果省略了M和D，则M的默认大小就是10 D则默认是0

并且decimal可以存放比bigint还要大的数值

文本类型	数据类型
字符串类型	char（size）【0-255字符】 varchar【0-65535字节】 text【0~2^16-1】

如果使用utf8则varchar只能存放（65532-3）/ 3

如果使用gbk则varchar只能存放（65532-3）/ 2

二进制类型	数据类型
二进制	blob【0~2^16-1】 longblob【0~2^32-1】

日期类型	数据类型
日期	data【日期 年月日】 time【时间 时分秒】 datatime【年月日 时分秒 YYY-MM-DD HH:mm:ss】

删除

• 删除表：drop table employee
• 删除表中的所有数据但是留下表结构：drop employee

修改

• 添加列（不能为空默认为‘ ’,插入到末尾）

  alter table emp 
  	age int not null default ''
  	after resume
  

• 修改列表

  alter table emp
  	modify job varcahr(60) not null default ''
  

• 删除列

  alter table emp
  	drop sex
  

• 修改表明：rename table 表名 to 新表名

  rename table emp to employee
  

• 修改表字符集：alter table 表名 character set 字符集

  alter table employee character set utf8
  

• 修改字段名

  alter table employee
  	change `name` `user_name` varcahr(64) not null default ''
  

• 查看表结构：desc employee

三、新删改查

insert语句

细节

1. 插入的数据应该与字段的数据类型相同
2. 数据的长度应在列的规定范围内
3. values必须

insert into employee(id,goods_name,price,)
values
(10,'华为手机',20000)

update语句

若没有指定where语句 则更新所有行

• 案例1：修改所有员工薪水

update employee
	set salary = 5000

• 案例2：修改robber的薪水修改3003

update employee
	set salary = 3003
	where user_name = '小妖怪'

• 案例3：修改robber的薪水修改3003

update employee
	set salary = salary + 1000, job = '出主意'
	where user_name = '小妖怪'

delete语句

不适用where语句会删除所有数据

• 删除robber

delete from employee
	where user_name = 'robebr'

select

关键字的顺序：

from -> join -> on -> where -> group by -> 聚合函数 -> having -> select -> distinct -> order by -> limit

• distinct：去重

• 案例1：查询所有信息

  select * from student
  

• 案例2：只显示英语成绩和姓名

  select `name`,english from student
  

• 案例3：过滤重复数据

  select distinct english form student
  

  只有每一列相同才能去重

• 案例4：统计每个学生的总分

  select user_name (english+chinese+math) as all from student 
  

• 案例5：学生总分加十分

  select user_name (english + chinese + math + 10) as all from student 
  

单表查询

运算符

比较运算符	> < <= >= = <> !=	大于、小于、大于等于、不等于
比较运算符	between … and…	显示在某一区间的数据
比较运算符	in(set)	显示在in列表中的数值
比较运算符	like ‘张pattern’	模糊查询
比较运算符	is null	判断是否为空

逻辑运算符	and	多条件同时成立
	or	多条件任意任一成立
	not	不成立，例：where not(salary >100)

• 案例一：查询总分大于200分，并且数学成绩小于语文成绩的姓韩的同学

  select * from student
  	where (math+english+chinese) > 200
  	and  math < chinese
  	and name like '韩%'
  

• 案例二：查询英语分数在80-90分数之间的同学

  select * from student
  	where english between 80 and 90
  	
  select * from student
  	where english >=80 and english <= 90
  

• 案例三：查询89，90, 91

  select * from student 
  	where math in (89,90,91)
  

order by 排序

• 对数学成绩升序排序

select * from student
	order by math

• 对总分降序

select * from student
	order by math desc

• 对性韩的学生成绩总分升序排序

select user_name,(math,english,chinese) as total_score from student
	where user_name like name '韩%'
	order by total_score

group by 分组

having

having 跟 where 区别：

• 第一个区别是，如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连而 HAVING 是先连接后筛选。这一点，就决定了在关联查询中，WHERE比HAVING更高效。因为WHERE可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也就比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
• 第二个区别是，WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

注意：

1. having必须跟group by 配合使用
2. having子句即可包含聚合函数作用的字段也可包括普通的标量字段

	优点	缺点
where	先筛选数据再进行链接，执行效率高	不能用分组计算函数对 数据筛选
having	可以使用分组计算函数进行筛选	再最后的结果集中对数据进行筛选，执行效率低

• 显示平均工资低于2000的部门号和它的平均工资

select avg(salary),deptno from emp 
	group by deptno
	having avg(salary) <2000 

合并查询

union all 跟 union区别

union可以自带去重

• union all

  select * from emp 
  union all
  select * from dept
  

• union

  select * from emp 
  union
  select * from dept
  

单表加强

where语句

• 案例：查询1992.1.1以后入职的员工

select * from emp where hiredate > '1992-01-01'

• like操作符号

  注意：

  ​ %：表示0到多个字符，

  ​ _：表示单个字符，可以用来占位

  • 案例1：显示首字符为S的员工姓名

  select * from 
  	where name like 'S%'
  

  • 案例2：显示第三个字符为大写O的员工

  select * from 
  	where name like '__O%'
  

order by 子句

• 案例：按照部门号升序而雇员的工资降序排序

  select * from emp
  	order by depno asc, salary desc
  

分页查询

• limit 每页显示记录数 * （第几页 - 1）， 每页显示记录数

• 案例：按员工的id升序取出，没页显示3条，请分别显示

  -- 第一页
  select * from emp
  	order by emono asc
  	limit 0, 3
  	
  -- 第二页
  select * from emp
  	order by emono asc
  	limit 3, 3
  	
  

group by 分组

• 案例：显示每种岗位的员工总数、平均工资

  select count(*), avg(sal) 
  	from emp
  	group by job
  

• 案例：显示员工共总数，以及获得补助的员工数

  select count(*) count(comm)
  	from emp
  

• 案例：显示管理者的总人数

  select count(distinct mrg)
  	from emp
  

  注意：count聚合函数是先执行内部表达式，在统计字数

• 案例：显示员工工资的最大差额

  select user_name,max(salary) - min(salary)
  	from emp
  

• 案例：统计各个部门的平均工资，并且大于1000的，并且按照平均工资从高到低排序，取出前两行

  select avg(sal)
  	from emp
  	group by depno
  	having sal > 1000
  	order by desc
  	limit 0,2
  

四、函数

合计函数

count 统计

• 统计总分大于250的人数

select count(*) from student
	where (math+english+chinese) > 250

count (*) 和 count（列）区别

count（*）：返回满足条件的记录的行数

count（列）：统计满足条件某列有多少，但是排除为null的数据

sum 函数

仅对数值起作用否则报错

• 案例：统计一个班级数学总成绩

select sum(math) from student

• 案例：统计一个班级语文成绩平均分

select sum(chinese) / count(*) from student

ave 平均分

• 求一个班级总分平均分

select avg(math+english+chinese) from student

max 和 min 使用

• 总分最高分和最低分

select max(math),min (math)
	from student

字符串函数

函数	说明
charset(str)	返回字符串字符集
concat(string2, […])	链接字符串
instr(string, substring)	返回substring在string总的位置
ucase(string2)	大写
lcase(string2)	小写
left(string2, length)	从左边截取字符
length(string)	长度
replace(str, search_str, replace_str)	替换
strcmp(string1, string2)	逐字比较字符串大小
substring(str, position, [length])	从str的position开始取length长度
ltrim(string) rtrim(string) trim	去除空格

• 案例：去除ename的第一个字符转为小写

select concatl( case(substring(ename,1,1)), substring(ename,1) )
	from emp

数学函数

函数	说明
abs（num）	绝对值
bin（decimal_number）	转成二进制
ceiling（num）	天花板函数
conv（num,from _base,to_base）	10进制转2进制 例：conv（8, 10, 2）
floor(num)	地板函数
format(num,decimal_places)	保留小数
hex(deciamlnum)	十六进制
least(number, number2, […])	最小值
mod	求余
rang([seed])	返回随机数值（0 <= v <= 1.0）

日期函数

函数	说明
crrent_date()	当前日期
crrent_time()	当前时间
current_timestamp()	当前时间戳
last_day()	当前月最后一天

date_add() 中的interval 后面可以是 year mintur second day等

• 案例：显示所有新闻信息，发布日期只显示 日期，不显示时间

select id, content, date(send_time)
	from mes;

• 案例：请查询在10分钟内发布的新闻

select * from mes
	where date_add(send_time, interval 10 minute) >= now()

• 案例：相差多少天

select datediff('2011-11-11','1990-01-01') from dual

• 案例：如果你能活到80随，求出你能活多少天

select datediff(date_add('1986-11-11',interval 80 year), now() )from dual

函数	说明
timediff(date1,date2)	两个时间差(多少小时多少分钟多少秒)
now()	当前时间
year|month|day(datetime)	年月日
unix_timestamp()	返回秒数
from_unixtime(秒数，‘%Y-%m-%s’)	格式化

加密函数和系统函数

• 案例：查看用户

select user() from dual

• 案例：当前数据库

select database()

• 案例：加密

select md5('hsp') from dual

• 案例：加密（password）

select password('hso') from dual

流程控制函数

函数	说明
if(expr,result1,result2)	如果expr1为真返回result1，否则reuslt2
ifnull(expr,result)	如果expr为空则返回result
select case when expr1 then result1 when expr2 then result2 else expr3 end;	只会返回第一个为真的表达式

• 如果user_name为 robber 那么就是本人，如果user_name other 那么就是其他

select user_name,(select case
                     when user_name = 'robber' then '本人'
                     else user_name then '其他' end
                 ) as 'job'
form emp;

五、多表

注意：多表查询的条件最少要 【表的个数-1】

• 案例：显示部门号 10 的部门名、员工和工资

  select ename,sal,dname
  	from emp,dept
  	where emp.deptno = emp.deptno and emp.deptno = 10
  

• 案例：显示各个员工姓名，工资，及其工资的级别

  select ename,sal,
  	from emp, salgrade
  	-- where emp.sal >= salgrade.losal and emp.sal <= hisal
  	where sal between losal and hisal
  

自连接

将一张表当作两张表来使用

• 思考题：显示公司员工名字和他的上级的名字

  select e1.ename,e2.ename
  	from emp e1,emp e2
  	where e1.mgr = e2.empno
  

单列子查询

• 案例：显示与SMITH同一部门的所有员工？

  select * 
  	from emp
  	where depno = (
      	select depno
          	from emp
          	where ename = 'SMITH'
      )
  

• 案例：查询和10号部门工作相同的员工的信息，但是不包含10自己的员工

  select * 
  	from emp
  	where job in (
      	select job 
          from dep
          where depno = 10
      ) and depno != 10
  

• 案例：找到各个种类中价格最高的商品名称

  select goods_id,temp.cart_id ,goods_name,shop_price
  	from (
      	select max(shop_price) as max_prince,cat_id
              from ecs_goods
              group by car_id
      ) temp, ecs_goods goods
      where temp.cart_id = goods.cart_id
      and temp.max_price = goods.shop_price
  

all any 操作符

• 案例：显示工资比部门30的所有员工的工资高的员工的信息

  select * 
  	from emp
  	where sal > all(
      	select sal
          	from emp 
          	where deptno = 30
      )
    
    select * 
    	from emp 
    	where sal > (
      	select max(sal)
          	from emp
          	where deptno = 30
      )
  

• 案例：显示工资比部门30的其中一个员工的工资高的员工的信息

  select * 
  	from emp 
  	where sal > any(
      	select sal
          	from emp
          	where depno = 30
      )
      
   select * 
  	from emp 
  	where sal > (
      	select min(sal)
          	from emp
          	where depno = 30
      )
  

多列子查询

• 案例：查询与smith的部门和岗位完全相同的所有雇员，并且不包括smith本人

  select * 
  	from emp
  	where (deptno,job) = (
      	select job,depno
  			from emp
  			where ename = 'smith'	
      )
  

• 案例：和松江数学，英语，语文成绩完全相同的学生

  select * 
  	from student	
  	where (math,english,chinese) = (
      	select math,english,chinese
          	from student
          	where ename = '宋江'
      )
  

练习

• 案例：查询每个员工工资高于本部门平均工资的人的信息

  select * 
  	from (
      	select deptno,avg(sal) avg_sal
              from emp
              group by emptno
      ) temp, emp
      where emp.sal > temp.abg_sal
      and emp.deptno = temp.deptno
  

• 案例：查询每个部门工资最高的人的信息

  select * 
  	from emp
  	where (depno, sal) in (
      	select deptno,max(sal) max_sal
              from emp
              group by depno
      )
  
  select * 
  	from (
      	select deptno,max(sal) max_sal
              from emp
              group by deptno
      ) temp, emp
      where temp.deptno = emp.deptno
      and temp.max_sal = temp.sal
  

• 案例：查询每个部门的信息（部门名，编号，地址）和人员数量

  select dept.dname,dept.deptno,dept.loc,temp.person_num
  	from (
      	select deptno,count(*) as person_num
          	from emp
          	group by deptno
      ) temp,dept
      where emp.deptno = dept.deptno
  

六、表复制

1. 将emp表的记录复制到 my_test01

   insert into my_test01
   	(id, 'name', sal, job, deptno)
   	select empno,eame,sal,job,deptno
   		from emp
   

2. 自我复制

   insert into my_test01
   	select * from my_test01
   

3. 面试题：删除表中重复的数据

   1、创建一个临时表
   2、将目标表去去重后插入临时表中
   3、清空表中的数据
   4、将临时表的数据插入目标表中
   5、删除临时表
   
   create table temp(
   	id int,
       name varchar(255)
   )
   insert into temp
   	select distinct * from target
   drop target
   insert into target
   	select * from temp
   drop table temp
   

七、外连接

1. 左外链接（左侧的表完整显示）

   select * 
   	from student left join exam
   	on student.id = exam.id
   

2. 右外链接（右侧的表完整显示）

   select * 
   	from student right join exam
   	on student.id = exam.id
   

3. 案例：显示所有部门名称和这些部门的员工信息（名字和工作），同时列出哪些没有员工的部门

   select emp.ename,emp.job
   	from dept left join emp
   	on dept.deptno = emp.deptno
   

八、约束

primary key 主键

注意：

1. primary key不能重复而且不能为null
2. 一张表最多只有一个逐渐，但可以符合主键
3. 主键的指定方式有两种
   1. 字段名 primary key
   2. 表定义最后 primary key
4. 使用desc 查看表解构

• 添加主键

  -- 单主键
  create table temp(
  	id INT primary key,
      name varchar(255)
  )
  -- 复合主键
  create table temp(
  	id INT,
      name varchar(255),
      primary key (id, name)
  )
  

  注意：定了符合主键要同时满足【id和name】完全相同才会报错

auto_increment 自增长

注意：

1. 一般自增长要和primary key（unique）使用
2. 自增长修饰的字段整数型（很少使用小数）
3. 自增长默认从1开始，你可以通过命令修改 alter table temp auto_increment = value
4. 如果你添加数据的时候制定了数据，就会根据指定数据增加

unique 和 not null 唯一

注意：

1. 如果没有指定not null，则unique字段可以有多个null
2. 一张表可以指定多个unique字段

create table temp(
	id INT primary key,
    user_name varchar(255) unique not null 
)

alter table temp user_name add unique

foreign key 外键

​ 用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有从表约束或者是unique，当定义外键约束后，要求外键列数据在主表的主键列存在或者是为null

列举：学生表和班级表

· 若王五存在11班，此时我们直接删除11班的班级表数据就会报错

外键的优劣性：

1. 好处
   1. 保证了数据库数据的一致性和完整性
   2. 级联操作方便，减轻了程序代码量…
2. 坏处
   1. **增加了工作的复杂性：**每次做DELETE或者UPDATE时都必须考虑外键约束，这样我们在开发和测试数据时会极其痛苦；并且外键的主从关系是固定的，当需求发生变化进行修改时会带来很多麻烦。
   2. **对分库分表不友好：**因为分库分表下外键是无法生效的。

注意：

1. 外键必须是主键
2. 表的类型是innodb
3. 外键字段的类型要和主键一致
4. 外键的数值必须在在主表字段中出现过
5. 一旦建立主外表的关系，数据不能随意删除了

-- 学生表
create table my_stu(
	id INT PRIMARY KEY,
    stu_name VARCHAR(32) NOT NULL DEFAULT '',
   	class_id INT,
    FOREIGN KEY (class_id) references my_class(id)
)

-- 班级表
create table my_class(
	id INT PRIMARY KEY,
    class_nane VARCHAR(32) UNIQUE NOT NULL,
)

check 检查

用于强制行数据必须满足的条件，假定在sal列上定义check约束，要求sal在1000~2000范围。

注意：oracle 和 sql server 均支持check，但是支持mysql5.7还不支持

create table temp(
	id INT PRIMARY KEY,
    sex VARCHAR(6) CHECK (sex in ('man','woman'))
)

商店销售案例

create table goods(
	goods_id INT primary key,
    goods_name VARCHAR(90) NOT NULL DEFAULT '',
    unitprice DECIMAl(10,2) NOT NULL DEFAULT 0 CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
    category VARCHAR(255) NOT NULL DEFAULT '',
    provider VARCHAR(64) NOT NULL DEFAULT ''
)

create table customer(
	customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL DEFAULT '',
    address VARCHAR(255) NOT NULL DEFAULT '',
    email VARCHAR(255) UNIQUE NOT NULL,
    sex CHAR(6) ENUM('男','女') NOT NULL,
    card_id CHAR(18)
)

create table purchase(
	order_id INT UNSIGNED PRIMARY KEY,
    customer_id CHAR(8) NOT NULL DEFAULT '',
    goods_id INT NOT NULL DEFAULT 0,
    nums INT NOT NULL DEFAULT 0,
    FOREIGN KEY goods_id REFERENCES goods(goods_id)
    FOREIGN KEY customer_id REFERENCES customer(customer_id)
)

九、索引

添加索引

1. 需要指定列添加索引
2. 需要占用磁盘空间

CREATE INDEX empno_index on emp (empno)

索引机制

原因：找到了id = 1的数据以就会进行全盘扫描

索引的原理：

1、会形成一个索引二叉搜索树

2、对数据解构有影响（update、delete、insert），影响性能

索引类型

如何选择：如果某列的数值，是不会重复的，则优先考虑unique索引，否在选择使用普通索引

• 索引类型
  • 主键索引，主键自动的为主键索引（primary key）
  • 唯一索引（UNIQUE）
  • 普通索引（INDEX）
  • 全文索引（FULLTEXT）

-- 添加唯一索引
CREATE UNIQUE INDEX id_index on test (id);
-- 添加普通索引
CREATE INDEX id_index on test(od);
-- 添加普通索引2
ALTER TABLE TEST ADD INDEX id_index (id)
-- 添加主键索引
ALTER TABLE test ADD PRIMARY KEY (id)


-- 删除索引
DROP INDEX id_index on test
-- 删除主键索引
ALTER TABLE TEST DROP PRIMARY KEY


-- 查看索引
SHOW INDEX FROM test
SHOW INDEXES FROM test
SHOW KEYS FROM test

创建索引规则

1. 较频繁的组为查询条件应该创建搜因

   SELECT * FROM EMP WHERE empno = 1
   

2. 唯一性太差的字符段不适合单独创建索引，即使频繁作为查询条件

   SELECT * FROM emp WHERE sex = '男'
   

3. 更新非常频繁的字段不适合创建索引

   SELECT * FROM emp WHERE logincout = 1
   

4. 不会出现在WEHRE不应该创建索引

十、事务

​ 事务用于保证数据的一致性，它由一组相关的dml语句组件，该组的dml语句要么全部成功，要么全部失败，如：转账就用用来管理事务，用于保证数据的一致性

• 事务和锁
  • 当事务操作时（dml语句），mysql会在表上加锁，放置其他用户改表的数据，这对用户来讲十非常重要的
• mysql几个重要操作
  • start transaction：开启事务
  • savepoint：设置保存点
  • rollback to：回退事务
  • rollback：退回全部事务
  • commit
    • 使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]

事务管理

1. 不开启事务，dml操作默认自动提交，不能回滚
2. 没有设置保存点，会默认回退到事务开始的状态
3. 可以设置多个保存点
4. mysql的食物机制需要innodb的存储引擎才可以使用myisam不支持
5. 开始一个食物 start transaction，set autocommit=off

-- 开启事务
start transaction
-- 设置保存点a
savepoint a
INSERT INTO test
	values
	(NULL,'Robber')
-- 设置保存点b
savepoint b
INSERT INTO test
	values
	(NULL,'Robber')
	
-- 回退到a
rollback to a

-- 回退到开启事务的地方
rollback

隔离级别

1. 多个连接开启各自事务操作数据中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据的准确性。
2. 如果不考虑隔离性，可能会引发如下问题：
   1. 脏读
   2. 不可重复读
   3. 幻读

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交（Read uncommitted）	✔	✔	✔	不加锁
读已提交(Read committed)	❌	✔	✔	不加锁
可重复读（Repeatable read）	❌	❌	❌	不加锁
可串行化(Serialiable)	❌	❌	❌	加锁

脏读

• 当一个事务读取另一个事务尚未提交的修改时，产生脏读

不可重复读

• 同一查询在同一事务中多次进行，由于其他提交事务所作的修改或删除，每次返回不同的结果集，此时发生不可重复读

幻读

• 统一查询在统一事务中多次进行，由于其他提交事务所作的插入操作，每次返回会不同的结果集，此时产生幻读

隔离级别指令

1. 查看当前隔离级别

   select @@tx_isolation

2. 查看系统当前隔离级别

   select @@global.tx_isolation

3. 设置当前会话隔离级别

   set session transaction isolation level repeatable read

4. 设置系统当前隔离级别

   set global transaction isolation level repeatable read

5. mysql 默认的事务隔离级别十 repeatable read，一般情况下，没有特殊要求，没有必要修改（因为改级别可以满足大部门项目需求）

ACID

1. 原子性（Atomicity）
   1. 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要
      么都不发生。
2. 一致性（Consistency）
   1. 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3. 隔离性（Isolation）
   1. 事务的隔离形式多个用户并发访问数据库时，数据库为每一个用户默认开启一个事务，不能被其他事务的操作数据所干扰，多个事务之间要相互隔离
4. 持久化
   1. 持久化是一个事务一旦被提交，它对数据库的数据的改变就是永久性的，接下来即使是数据库发生故障也不应该对其有影响

十一、存储引擎

1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
2. 支持事务
3. 支持外键
4. 支持行级锁
5. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
6. 添加速度快
7. 不支持外键和事务
8. 支持表级锁
9. MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉, 表的结构还在。
10. 数据存储在内存中（关闭mysql服务，数据丢失）
11. 执行速度很快（没有IO）
12. 默认支持索引（hash表）
13. 如何选择表的粗出引擎
    1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择,速度快
    2. 如果需要支持事务，选择InnoDB
    3. Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I/0的等待速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法 用户的在线状态）

十二、视图

– 视图是一个虚拟表，其内容由查询定义。同基表（真实表）一样，试图包含列，其数据来自对应的真是表

细节：

1. 通过视图修改的数据会相互影响
2. 只会产生结构文件，不会生成数据文件
3. 视图中可以再使用视图

基本使用

1. crate vie 试图名 as select 语句
2. alter view 试图名 as select 语句
3. show create view 视图名
4. drop view 视图名1，试图名2

• 案例：创建一个视图，支撑只能查询emp（empno,ename）

  create view emp_view
  	as
  	select * empno,ename
  

视图最佳时间

1. 安全：一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能：关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时数据库查询通常会用到连接==(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN==查询数据。
3. 灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很
   多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接
   映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

• 案例：针对emp,dept 和 salgrade 三张表，创建一个视图只显示编号、名字、部门名称和薪水级别

  SELECT * 
  	FROM emp,dept,salgrade
  	WHERE emp.deptno = dept.deptno
  	AND emp.sal BETWEEN losal AND hisal
  

十三、MySql管理

原因：当我们做项目开发的时候，可以根据不同的开发人员，赋予不同的mysql操作权限权限

细节：

1. 如果不指定host，则为%，表示用户可以远程登录
2. create user ‘xx’@'xxx.xxx.xxx.%'表示用户可以在这个网段都可以登录
3. 在删除用户的时候，如果host不是%，需要明确指定’用户’@‘地址’

1. 创建新用户

   CREATE USER 'robber'@'localhost' IDENTIFIED BY '12345'
   
   -- 查看 mysql.user 表
   SHOW * 
   	FROM mysql.user
   

2. 删除用户

   DROP USER 'robber'@'localhost' 
   

3. 修改密码

   -- 修改自己的密码
   SET PASSWORD = PASSWORD('123')
   
   -- 修改别人的密码，需要权限
   SET PASSWORD FOR 'robber'@'localhost' = PASSWORD('123456')
   

用户权限授权

1. 基本语法

   GRANT 权限列表 on 库.表 to 'robber'@'localhost' [IDENTIFIED BY '密码']
   

2. 说明

   1. 列表多个权限用逗号分开
      1. grant select on …
      2. grant select,delete,create on …
      3. grant all [privileges] on… //赋予所有权限
   2. 特别说明
      1. 星.星：代表本系统中的所有数据库的对象（表，视图，存储过程）
      2. 库.星：表达某个数据库中的所有数据库对象(表，视图，存储过程)
   3. identified by：可以省略，也可以写出来
      1. 如果用户存在，就是修改该用户的密码
      2. 如果用户不存在，就创建用户

3. 回收权限

   revoke 权限列表 on 库.对象名 from '用户名'@'登录位置'
   

4. 权限生成指令

   -- 如果不起效果则需要执行
   flush privileges
   

用户管理练习

1. 创建一个用户(你的名字，拼音)，密码 123，并且只可以从本地登录，不让远程登录mysql
2. 创建库和表 tesgb 下的 news 表 ，要求: 使用root 用户创建.
3. 给用户分配查看 hews 表和添加数据的权限
4. 测试看看用户是否只有这几个权限
5. 修改密码为 abc ，要求: 使用root 用户完成
6. 重新登录
7. 使用 root 用户篇除你的用户

-- 创建用户
create user 'robber'@'localhost' identified by '123'
-- 创建表
create table news(
)
-- 赋予权限
grant select insert on testdb.news to 'robber'@'localhost'
-- 修改密码
set password for 'robber'@'localhost' = password ('abc')
-- 移除用户
drop user 'robber'@'localhost'

十四、练习

1. 使用简单查询语句完成

   1. 显示所有部门名称。
   2. 显示所有雇员名及其全年收入 13月(工资+补助),并指定列别名”年收入“

2. 限制查询数据

   1. 显示工资超过2850的雇员姓名和工资
   2. 显示工资不在1500到2850之间的所有雇员名及工资
   3. 显示编号为7566的雇员姓名及所在部门编号。
   4. 显示部门10和30中工资超过1500的雇员名及工资
   5. 显示无管理者的雇员名及岗位

   -- 1. 显示工资超过2850的雇员姓名和工资
   SELECT ename,sal
   	FROM emp
   	WHERE sal > 2850
   -- 2. 显示工资不在1500到2850之间的所有雇员名及工资
   SELECT ename,sal
   	FROM emp
   	WHERE !(sal BETWEEN 1500 AND 2850)
   --  3. 显示编号为7566的雇员姓名及所在部门编号。
   SELECT ename,deptno
   	FROM emp
   	WHERE empno = 7566
   --  4. 显示部门10和30中工资超过1500的雇员名及工资
   SELECT ename,sal,deptno
   	FROM emp
   	WHERE sal > 1500 
   	AND deptno IN (10,30)
   --  5. 显示无管理者的雇员名及岗位
   SELECT ename,job,mgr
   	FROM emp
   	WHERE mgr IS NULL
   

3. 排序数据。

   1. 显示在1991年2月1日到1991年5月1日之间雇用的雇员名,岗位及雇佣日期,并以庭佣日期进行
   2. 显示获得补助的所有雇员名,工资及补助,并以工资降序排序

   -- 1. 显示在1991年2月1日到1991年5月1日之间雇用的雇员名,岗位及雇佣日期,并以庭佣日期进行
   SELECT ename,job,hiredate
   	FROM emp
   	WHERE DATEDIFF(hiredate,'1991-02-01') <= DATEDIFF('1991-05-01','1991-02-01')
   	AND DATEDIFF(hiredate,'1991-02-01') >= 0
   	ORDER BY hiredate DESC
   
   SELECT ename,job,hiredate
   	FROM emp
   	WHERE hiredate >= '1991-02-01' AND hiredate <= '1991-05-01'
   	ORDER BY hiredate DESC
   -- 2. 显示获得补助的所有雇员名,工资及补助,并以工资降序排序
   SELECT ename,sal,comm
   	FROM emp
   	WHERE comm IS NOT NULL
   	ORDER BY sal DESC
   

4. 员工表

   1. 选择部门30中的所有员工
   2. 列出所有办事员(CLERK)的姓名，编号和部门编号.
   3. 找出佣金高于薪金的员工.
   4. 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
   5. 找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
   6. 找出收取佣金的员工的不同工作
   7. 找出不收取佣金或收取的佣金低于100的员工
   8. 找出各月倒数第3天受雇的所有员工
   9. 找出早于12年前受雇的员工
   10. 以首字母小写的方式显示所有员工的姓名
   11. 显示正好为5个字符的员工的姓名

   -- 1. 选择部门30中的所有员工
   SELECT * FROM emp WHERE deptno = 30
   -- 2. 列出所有办事员(CLERK)的姓名，编号和部门编号.
   SELECT ename,empno,deptno FROM emp WHERE job = 'CLERK'
   -- 3. 找出佣金高于薪金的员工.
   SELECT * FROM emp WHERE IF(comm ,comm,0)> sal
   -- 4. 找出佣金高于薪金60%的员工
   SELECT * FROM emp WHERE IF(comm ,comm,0) > sal * 0.6
   -- 5. 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
   SELECT * 
   	FROM emp
   	WHERE deptno = 10 AND job = 'MANAGER'
   	OR deptno = 20 AND job = 'CLERK'
   
   -- 6. 找出部门10中所有经理(MANAGER),
   -- 部门20中所有办事员(CLERK),
   -- 还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.------
   SELECT * 
   	FROM emp
   	WHERE deptno = 10 AND job = 'MANAGER'
   	OR deptno = 20 AND job = 'CLERK'
   	OR (job != 'MANAGER' AND job != 'CLERK' AND sal >= 2000)
   -- 7. 找出收取佣金的员工的不同工作.------
   SELECT DISTINCT job
   	FROM emp
   	WHERE comm IS NOT NULL
   -- 8. 找出不收取佣金或收取的佣金低于100的员工-
   SELECT *
   	FROM emp
   	WHERE comm IS NULL 
   	OR comm < 100
   -- 9. 找出各月倒数第3天受雇的所有员工
   SELECT DAY(LAST_DAY(NOW())) FROM DUAL
   
   SELECT *,LAST_DAY(hiredate) - 2
   	FROM emp
   	WHERE DAY(hiredate) =  DAY(LAST_DAY(hiredate)) - 2
   -- 10. 找出早于12年前受雇的员工
   SELECT *
   	FROM emp 
   	WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW()
   -- 11. 以首字母小写的方式显示所有员工的姓名
   SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2,LENGTH(ename)))
   	FROM emp
   	
   -- 12.显示正好为5个字符的员工的姓名
   SELECT * 
   	FROM emp
   	WHERE ename LIKE '_____'
   

5. emp表

   1. 显示不带有"R"的员工的姓名
   2. 显示所有员工姓名的前三个字符.
   3. 显示所有员工的姓名,用a替换所有"A"
   4. 显示满10年服务年限的员工的姓名和受雇日期
   5. 显示员工的详细资料 按姓名排序.
   6. 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面工作和薪金,按工作降序排序,若工作相同则按薪金排序.
   7. 显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年分
   8. 显示所有员工的姓名、的员工排在最前面.
   9. 显示在一个月为30天的情况所有员工的日薪金,忽略余数
   10. 找出在(任何年份的)2月受聘的所有员工-
   11. 对于每个员工,显示其加入公司的天数
   12. 显示姓名字段的任何位置包含"A"的所有员工的姓名
   13. 以年月日的方式显示所有员工的服务年限。(大概)

   -- 1. 显示不带有"R"的员工的姓名
   SELECT * 
   	FROM emp
   	WHERE !(ename LIKE 'R%')
   
   -- 2. 显示所有员工姓名的前三个字符.
   SELECT SUBSTRING(ename,1,3) '姓名'
   	FROM emp
   	
   
   -- 3. 显示所有员工的姓名,用a替换所有"A"
   SELECT REPLACE(ename,'A','a')
   	FROM emp
   -- 4. 显示满10年服务年限的员工的姓名和受雇日期
   SELECT ename,hiredate
   	FROM emp
   	WHERE hiredate <=  DATE_ADD(NOW(),INTERVAL -10 YEAR) 
   -- 5. 显示员工的详细资料 按姓名排序.
   SELECT * 
   	FROM emp
   	ORDER BY ename DESC
   -- 6. 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面工作和薪金,
   
   SELECT ename,hiredate,DATEDIFF(NOW(),hiredate) temp,sal,job
   	FROM emp
   	ORDER BY  temp DESC
   	
   -- 7. 显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年分
   SELECT ename,MONTH(hiredate) month_date,YEAR(hiredate) year_date
   	FROM emp 
   	ORDER BY month_date DESC, year_date ASC
   -- 9. 显示在一个月为30天的情况所有员工的日薪金,忽略余数
   SELECT ROUND(sal / 30)
   	FROM emp
   	
   	
   	
   -- 10. 找出在(任何年份的)2月受聘的所有员工-
   SELECT * 
   	FROM emp
   	WHERE MONTH(hiredate) = 2
   -- 11. 对于每个员工,显示其加入公司的天数
   SELECT DATEDIFF(NOW(),hiredate) 
   	FROM emp
   
   -- 12. 显示姓名字段的任何位置包含"A"的所有员工的姓名
   SELECT * 
   	FROM emp
   	WHERE ename LIKE '%A%'
   -- 13. 以年月日的方式显示所有员工的服务年限。(大概)
   SELECT CONCAT(FLOOR(DATEDIFF(NOW(),hiredate) / 365),'年',FLOOR(DATEDIFF(NOW(),hiredate) / 365 % 12),'月',FLOOR(DATEDIFF(NOW(),hiredate) / 365 % 12 % 30),'天')  '服务年限'
   	FROM emp
   

6. emp + dept

   1. 列出至少有一个员工的所有部门
   2. 列出薪金比“SMITH”多的所有员工。
   3. 列出受雇日期晚于其直接上级的所有员工
   4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
   5. 列出所有“CLERK”(办事员)的姓名及其部门名称。
   6. 列出最低薪金大于1500的各种工作。
   7. 列出在部门“SALES” (销售部) 工作的员工的姓名
   8. 列出薪金高于公司平均薪金的所有员工。

   -- 1. 列出至少有一个员工的所有部门
   SELECT DISTINCT dept.dname,dept.deptno 
   	FROM dept,emp
   	WHERE dept.deptno = emp.deptno
   	
   -- 2. 列出薪金比“SMITH”多的所有员工。
   SELECT *
   	FROM emp
   	WHERE sal + comm > (
   		SELECT  IFNULL(0,comm) + sal
   			FROM emp
   			WHERE ename = 'SMITH'
   	)
   
   -- 3. 列出受雇日期晚于其直接上级的所有员工
   SELECT e1.ename,e1.hiredate,e1.mgr,e2.ename,e2.hiredate,e2.empno
   	FROM emp e1,emp e2
   	WHERE e1.mgr = e2.empno
   	AND e1.hiredate > e2.hiredate
   -- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
   SELECT *
   	FROM dept LEFT JOIN emp
   	ON dept.deptno = emp.deptno
   -- 5. 列出所有“CLERK”(办事员)的姓名及其部门名称。
   SELECT ename,dname,job
   	FROM emp,dept
   	WHERE emp.deptno = dept.deptno
   	AND job = 'CLERK'
   -- 6. 列出最低薪金大于1500的各种工作。
   SELECT MIN(sal),job
   	FROM emp
   	GROUP BY job
   	HAVING MIN(sal) > 1500
   	
   
   -- 7. 列出在部门“SALES” (销售部) 工作的员工的姓名
   SELECT deptno
   	FROM dept
   	WHERE dname = 'SALES'
   	
   SELECT ename
   	FROM emp
   	WHERE deptno = (
   		SELECT deptno
   			FROM dept
   			WHERE dname = 'SALES'
   	)
   -- 8. 列出薪金高于公司平均薪金的所有员工。
    SELECT * 
   	FROM emp
   	WHERE sal > (
   		SELECT AVG(sal)
   			FROM emp
   			
   	)
   

7. emp + dept

   1. 列出与“SCOTT”从事相同工作的所有员工
   2. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
   3. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
   4. 列出所有员工的姓名、部门名称和工资
   5. 列出所有部门的详细信息和部门人数。
   6. 列出各种工作的最低工资列出MANAGER (经理)的最低薪金
   7. 列出所有员工的年工资,按年薪从低到高排序。

   -- 1. 列出与“SCOTT”从事相同工作的所有员工
   SELECT * 
   	FROM emp
   	WHERE deptno = (
   		SELECT deptno
   			FROM emp
   			WHERE ename = 'SCOTT'
   	)
   -- 2. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
   SELECT ename,sal
   	FROM emp 
   	WHERE  sal > ALL (
   		SELECT sal + IFNULL(0,comm)
   			FROM emp
   			WHERE deptno = 30
   	)
   -- 3. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
   SELECT COUNT(*),AVG(sal),FLOOR(AVG(DATEDIFF(NOW(),hiredate)))
   	FROM emp
   	GROUP BY deptno
   -- 4. 列出所有员工的姓名、部门名称和工资
   SELECT ename,dname,sal
   	FROM emp,dept
   	WHERE emp.deptno = dept.deptno
   -- 5. 列出所有部门的详细信息和部门人数。
   SELECT dept.dname,COUNT(*)
   	FROM dept,emp
   	WHERE dept.deptno = emp.deptno
   	GROUP BY dept.deptno
   
   SELECT COUNT(*), deptno
   	FROM emp
   	GROUP BY deptno
   	
   SELECT temp.person_sum,dept.*
   	FROM dept,(
   		SELECT COUNT(*) person_sum, deptno
   			FROM emp
   			GROUP BY deptno
   	) temp
   	WHERE temp.deptno = dept.deptno
   -- 6. 列出各种工作的最低工资
   SELECT MIN(sal)
   	FROM emp
   	GROUP BY job
   -- 列出MANAGER (经理)的最低薪金
   SELECT MIN(sal)
   	FROM emp
   	WHERE job = 'MANAGER'
   
   -- 7. 列出所有员工的年工资,按年薪从低到高排序。
   SELECT (sal+IFNULL(0,comm)) *12 salary
   	FROM emp
   	ORDER BY salary ASC
   

8. 建表