终极mysql

mysql

修改linux的机器名字
sudo vi /etc/hostname

给linux命令提示起名字
在配置文件 ～／.bashrc 或./profile
export PS1=‘(-_-)test:’



数据
1结构化数据
Stu 	(id, name,age)

2.半结构数据
点击流

stu.xml	
 <stus>
	<stu>
		<id>1</id>
		<name>lisi</name>
	</stu>
	<stu>
		<id>1</id>
		<name>lisi</name>
	</stu>
	<tea>
		<id>2</id>
		<name>lisi</name>
	</tea>
</stus>

3.无结构化数据
音频，视频，图片，网页，邮件	


数据库：存储数据的仓库
关系型数据数库：、

select id
from user;
sql 第四代语言：只关注结果不关注过程
高级语言 java c c++等
汇编语言

navicat
元组


mysqlxxxx.tar.gz

mysql的配置文件
/etc/mysql/mysql.conf.d／mysqld.cnf

命令放在/usr/bin

mysql数据库开启操作
sudo service mysql start
		   stop
		   restart


mysql -u用户名 -p密码 [-h数据库所在计算机的ip -p端口]
mysql -uroot -proot [-h127.0.0.1 -p3306]

注意：数据库的端口的3306

查看字符编码（系统变量）
查看系统变量
show variables\G;
查看部分变量
show variables like ‘char%’ 
模糊匹配
_ 代表任意一个字符
% 代表0到多个任意字符

注意：字符串和时间在数据库中表示都要加单引号

设置mysql的编码以及编码集
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

让别的计算机可以连入数据库，注释配置文件中
#bind-address=127.0.0.1

配置：
/etc/mysql/mysql.conf.d／mysqld.cnf





展示数据库
show databases [like ‘统配’];

创建自己的数据库
create database 数据库的名字;



使用具体的数据库
use 数据库名字
e.g.：
   use mysql;



展示数据库中所有表
show tables [like ‘统配’];
e.g. 
show tables like ‘eg%’
或
查看其他数据库中的表
show tables from 数据库的名字 [like ‘’]
e.g.:
show tables [from mysql [like 'use%’]]

列出建表语句
show create table 表名；

列出数据库中表的结构
desc [数据库的名字.]表名
e.g.: desc user;
或
show columns from table_name [from db_name like ‘’]
table_name 表名
db_name 数据库的名字[可选]

show columns from user from mysql;

列出表中的索引
show index from table_name
e.g.:
show index from user\G;

列出某个数据库中表中的索引
show index from table_name from db_name;
e.g.:
show index from user from mysql\G;

show status 列出mysql中运行的状态信息

显示当前有多少线程连入
show full processlist;

列出表中的状态信息
show table status from db_name;

show table status from mysql


查看某个用户的权限
show grants for 用户

show grants for root@localhost;



列出数据库中所有的引擎
（引擎：一段代码，维护数据的录入，查询，数据库的安全，
事务的支持，联机处理或联机分析）
show engines;

select直接做运算
select 1+1


查看系统时间
select	 sysdate() as tim
注意：as后面别名

查看系统实时时间
select now();

查看当前日期
select current_date;


mysql中的数据类型

tinyint 1字节 8位 =》255

tinyint(3) unsigned zerofill
012
smallint 2字节	16 =》2^16-1

float(M,D) zerofill
M有效位数	D小数点后几位

注意：整数的位数=M-D
    小数点后多位的时候截取会四舍五入
    zerofill只会补齐小数点的位数
create table test(
id float(4,2) zerofill
);

char(M) binary
M表示的字符长度 
binary 二进制方式存储

create table test(
gender char(1)
);

insert into test values('中');
注意：数据库存储长度不够空格补齐，char定长
   ‘中   ’
一般是char申明固定长度的值

varchar(M) [binary]         21845个长度
存储可变长度的字符串,执行insert的时候会检测改varchar类型的
值的长度，基于该长度分配空间
M是限制字符不能超过多少长度
create table test(
name varchar(20)
);

insert into test values(‘中’);／／使用一个长度


tinyblob 和tinytext一样的 
注意：blob底层基于二进制存储 255字符

blob 存储64K，存储图片，视频，音频，文章，小说等

Text 存储65535字符

MEDIUMBLOB 存储16M
mediumtext 存储16777215(2^24-1)字符


LONGBLOB 存储4G
LONGTEXT 存储4294967295(2^32-1)字符


enum(‘val’,’val1’…)
枚举类型只能从指定的值中取值,只能取一个值,
最多可以设置65535个

create table stu(
gender enum(‘man’,’female’)
);

insert into stu values(‘test’);

set(‘val1’,’val2’…)
取值可以取0个到多个
只能设置64个

create table stu(
gender set(‘man’,’female’)
);

插入多个值
insert into stu values(‘male,female’);
插入0个值
insert into stu values();//null

日期类型 java.sql.Date
Date	日期格式yyyy-mm-dd
create table stu(
id int,
birth date
);

insert into stu values(2,’2019-9-10');


datetime YYYY-MM-DD HH:MM:SS
time 	HH：MM：SS
timestamp(M) java.sql.Timestamp
M=14=>YYYYMMDDHHMMSS
M=12=>YYMMDDHHMMSS
M=8=>YYYYMMDD
M=6=>YYMMDD

YEAR[(2|4)]	
2=>yy
4=>yyyy

表：存储数据
组成：二维表格：行和列
每一行叫做元组

建表语句语法：
create [temporary] table [if not exists] table_name(
列的声明
…..
)[table选项] engine=innodb default charset=utf8;

temporary 临时表，使用完没了，一般存储计算结果；
if not exists  如果表存在不建表，表存在不报错
table_name 表名
engine=innodb 设置表的默认引擎，看默认设置，默认设置
本身是innodb ，建表的时候不需要设置 show engines
default charset=utf8 设置表的编码，查看系统变量
如果系统变量设置过编码，建表的时候不需要设置
show variables like ‘char%’;

建立列级约束的将表
create table  table_name(
列名 	类型	约束,
列名 	类型	约束,
列名 	类型	约束,
列名 	类型	约束
);
注意：约束是对列的限制
primary key :主键约束
not null    ：非空约束，指定的列值不能为空
unique	    ：唯一约束，指定的列值的值必须唯一
foreign key ：外键约束，不能用于列级约束申明
check->mysql没有效果

表级约束
create table  table_name(
列名 	类型,
列名 	类型,
列名 	类型,
列名 	类型,
约束,
约束,
约束,
约束
);

注意：表级约束和列约束写法可以混合使用

1主键约束：能够唯一标识一行内容的列
	 主键primary key修饰的列会自动带有索引
	 主键：非空且唯一
学生：学号（pk），姓名，年龄，性别

列级约束
create table student(
id int	primary key,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’)
);
等价
表级约束
create table student(
id int,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’),
primary key(id)
);

联合主键：表中任一一列都不能唯一标识一行数据，可以选择多列
作为联合唯一标识一行记录
注意：联合主键只能用表级约束申明
学生的编号和名字作为主键
create table student(
id int,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’),
primary key(id,name)
);
insert into student  
	values(2,'wangwu',19,'男');
insert into student  
	values(2,’wangwu’,22,’女’);//error
联合主键：主键的组合不能重复

非空约束  not null
学生名字不能为空
create table student(
id int,
name varchar(20) not null,
age tinyint unsigned,
gender enum(‘男’,’女’),
primary key(id)
);
insert into student(id,age,gender)  
			values(2,19,’男’);//名字没有默认值 

insert into student  
	values(2,'wangwu',19,'男'); //ok

注意：not null只能是列级约束
create table student(
id int,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’),
primary key(id),
not null(name)
);//语法错误

unique：唯一约束，要求录入列中的数据不能重复
      可以是空值（多个） 

列级约束
create table student(
id int,
name varchar(20) unique,
age tinyint unsigned,
gender enum(‘男’,’女’),
primary key(id)
)
等价于
表级约束
create table student(
id int,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’),
primary key(id),
unique(name)
);

表级约束，给唯一约束起名字
unique 约束名字(列名)

约束名字：唯一约束 student_un_name
	主键    student_pk_id
	       student_nn_name


create table student(
id int,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’),
primary key(id),
unique student_un_name(name)
);

联合唯一：表级约束申明
学号和名字作为唯一约束
create table student(
id int,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’),
unique(id,name)
);
等价
create table student(
id int,
name varchar(20),
age tinyint unsigned,
gender enum(‘男’,’女’),
unique student_un_id_name(id,name)
);

和主键连用（主键是数字类型），自增，默认值1，步长1
auto_increment

create table student(
id int auto_increment primary key,
name varchar(20) not null,
age tinyint
);

注意：auto_increment修饰的列插入数据的时候
可以忽略
insert into student(name,age)
		values(‘lisi’,30);

会话：session	
打开终端链接数据库或jdbc程序连入数据库获取链接对象
会话的结束：关闭终端或程序结束关闭链接对象

show variables like ‘%increment%’
查询auto_increment的默认值和步长
auto_increment_increment 1 步长
auto_increment_offset	 1 默认初始值

查看全局的
show global variables like ‘%increment%’
auto_increment_increment 1 步长
auto_increment_offset	 1 默认初始值

会话级别（只针对当前终端或程序有效）
show session variables like ‘%increment%’
auto_increment_increment 1 步长
auto_increment_offset	 1 默认初始值

注意：步长是最大65535
     默认值最大65535

建表语句结束括号后设置auto_increment=20，表示初始值
create table student(
id int auto_increment primary key,
name varchar(20) not null,
age tinyint
)auto_increment=20 engine=innodb default charset=utf8;

外键约束foreign key：
1.维护表于表之间关系
2.外键对应的列一定引自于另外一张表的主键或唯一约束的列
3.外键对应的列可以为空
4.在mysql中，外键必须用表级约束，列级约束不生效
5.外键列的类型和引用表的列的类型一致

一对一：
class Wife{
private long id;
private String name;
private Hus hus;
get/set
}
class Hus{
private long id;
private String name;
private Wife wife;
get/set..
}
表于表单边维护，一对一外键随便建在那一方都可以
wife 			 hus
id(pk) name hus_id	 id(pk|un) name
1	rose 1		  1	    Jake


create table hus(
id int auto_increment primary key,
name varchar(20)
);

create table wife(
id int auto_increment primary key,
name varchar(20),
hus_id int references hus(id)
);//mysql不支持，其他如oracle，db2支持的
等价
create table wife(
id int auto_increment primary key,
name varchar(20),
hus_id  references hus(id)
);

mysql支持的外键表级约束
create table wife(
id int auto_increment primary key,
name varchar(20),
hus_id int,
foreign key(hus_id) references hus(id)
);
注意：foreign key(外键列-》本表中列)


联合外键：
create table hus(
id int,
name varchar(20),
primary key(id, name)
);

create table wife(
id int primary key,
name varchar(20),
hus_id int,
hus_name varchar(20),
foreign key(hus_id,hus_name) 
		references hus(id, name)
);

default给表中某列设置默认值：用户录入数据以用户录入的为准
没有录入，采用默认值

create table tea(
id int,
name varchar(20) default ’tom’,
birth date default ‘2019-09-11’,
age int default 20
);

insert into tea(id) values(1);

建立索引:提高查询效率用的
索引一般不用建立，什么情况建索引？
1.表中的数据基本不会变动
2.表中某一列常常作为查询条件
3.每次查询的结果数据是总数据的4%左右

语法
index 索引的名字(表中建索引的列)

create table tea(
id int,
name varchar(20),
key index_key(id)
);
等价于
create table tea(
id int,
name varchar(20),
index index_key(id)
);
等价于
create table tea2( 
id int key, 
name varchar(20) 
);

给约束起名字
列级
create table tea(
id int primary key,
name varchar(20) not null,
phone char(11) unique
);
给约束起名字代码加在 列类型声明和约束中间
create table tea(
id int constraint tea_pk_id primary key,
name varchar(20) constraint tea_nn_name  not null,
phone char(11) constraint tea_un_phone unique
);//mysql不支持

mysql支持给约束起名字，表级约束，
直接在约束的前面加constraint 约束的名字
create table tea(
id int,
name varchar(20),
primary key(id),
unique(name)
);

create table tea(
id int,
name varchar(20),
constraint tea_pk_id primary key(id),
constraint tea_un_name unique(name);
);

check检查约束：
限定录入数据值的范围（现在一般不用，一般前台或代码验证）

mysql不支持：
create table tea（
id int primary key,
name varchar(20),
gender char(1) check(gender=‘男’ or gender=‘女’)		
age int check(age between 10 and 20)
);

[10,20] 	age<=20 and age>=10

		列级约束		表级约束		联合约束
关键字：
not null/null	Y		N		N
default		Y		N		N
unique		Y		Y		Y
primary key	Y		Y		Y	一个表中只有一个(主表)
foreign key	N		Y		Y	一个表中多个（外表）
key		Y		Y		Y
index		N		Y		Y	对象
auto_increment  Y		N		N	table_option设置起始值


建表三范式：
第一范式：原子性，列不可分

stu
id	name	age 	addr_id		
1	lisi	30	1		

解决方法：把能拆分的列做一张表
address
id 	province city   street
1	江苏省     昆山市. 巴城镇

第二范式：非主属性必须依赖于主键
stu
学生编号
id(id)	name	course_id
1	lisi	java	


解决方案：
多对多关系的维护接住第三张表（桥表）
course	
id	name	score 

第三范式：不能存在依赖传递
course	
id	name	score  tea_id tea_name

id->tea_id->tea_name



解决方案：去掉多余的列，（多余的列维护）


表与表之间的关系
一对一:关系（外键）在任意一方都可以
hus _ wife

注意：类对应数据库的表
    java对象对应数据库表中一行
    java中的属性对应表中的列（关系除外）


一对多:关系在多的一方维护
person	1
id(pk)	 name	age	
1	 lisi	30	


	
car	n
id(pk)	name 	 price	person_id
1	test1	 19	1
2	test2	12	1
3	test3	20	1

category
id	name	
类
class	Person{
 	private long id;
	private String name;
	private int age;
	private Set<Car> cars;

	get/set…
}
class Car{
	private long id;
	private String name;
	private double price;
	private Person person;
	private Category cate;
}

class Category{
	private long id;
	private String name;
	private Set<Car> cars;
}
表:
create table person(
id char(18) primary key,
name varchar(20) not null,
age  tinyint
);
create table car(
id varchar(20) primary key,
name varchar(20),
price double,
person_id char(18),
cate_id int,
foreign key(person_id) references person(id),
foreign key(cate_id) references cate(id)
);
create table cate(
id int primary key,
name varchar(20) 
);

多对多：关系在第三张表维护
stu：		
id	name	
1	lisi	
2 	jake	

course：
id	name	
1	Hadoop	
2 	corejava
3	mysql

类：
class Stu{
  private long id;
  private String name;
  private Set<Course> cours; 
}
class Course{
  private long id;
  private String name;
  private Set<Stu> stus;
}

表：
create table stu(
id int auto_increment primary key,
name varchar(20)
);
create table course(
id int auto_increment primary key,
name varchar(20)
);

create table stu_course(
 stu_id int,
 course_id int,
 foreign key(stu_id) references stu(id),
 foreign key(course_id) references course(id)
);
		
stu_course
stu_id	course_id
1	1
1	2
1	3
2	2
2	3




order	n
id	name	buy_date 	

product	n
id	name  price  

orderline(订单项) 第三表
num

class Order{
 private long id;
private String name;
private date  ;
private Set<OrderLine> orderlines;
}

class Product{
 private long id;
private String name;
private double price;
private Set<OrderLine> orderliness;

}

class OrderLine{
 //数量
 private float num;
 private Order order;
 private Product product;

}
表：

create  table order(
 id int auto_increment primary key,
 name varchar(20),
 buy_date date
);

create table product(
 id int auto_increment primary key,
  name varchar(20),
  price double
);

create table orderline(
order_id int,
pro_id int,
num float,
foreign key(order_id) references order(id),
foreign key(pro_id) references product(id)
);



表结构的修改：
create table tea( 
id int 
);
表中添加列
语法
alter table table_name add column 
	列名 类型 约束 [after|first 已存在的列名];
e.g.:
alter table tea add column name varchar(20) not null;
在id列后面插入age列
alter table tea add age int after id;


添加主键：
语法：alter table table_name add  
	表级约束;
e.g.: alter table tea add  primary key(id);

添加唯一约束
语法：alter table table_name add  
	表级约束;
e.g.:
alter table tea add unique un_index(name);
alter table tea add unique(name);


设置默认值：
语法：alter table 表名 alter [column] 列名 set default 默认值;

注意：默认值如果是字符串和时间加单引号
e.g.:
alter table tea alter age set default 30;

删除默认值：
alter table 表名 alter [column] 列名 drop default;
alter table tea alter age drop default;


修改列的类型（约束）或者改列的名字
alter table 表名 change 旧的名字 新的名字 varchar(20);
更改age从int类型变成varchar类型
e.g.:
alter table tea change age age varchar(20);
更改列的名字
e.g.:
alter table tea change age passwd varchar(20) not null;

修改列的类型
语法：
 alter table 表名 modify 列名 类型 [约束];
e.g.：
 alter table tea modify passwd int;

删除列
语法：
alter table 表名 drop 列名
e.g.:
alter table tea drop passwd;

删除主键：
语法
alter table 表名 drop primary key
e.g.:
alter table tea drop primary key


删除索引的名字
alter table 表名 DROP INDEX index_name

修改表名
语法
alter table 表名 rename 新的表名;
alter table tea rename teacher;


DML操作（insert delete update）：
语法：
insert LOW_PRIORITY | DELAYED into 表名[(column_name...)]	
	values(val….)
	

LOW_PRIORITY | DELAYED执行插入操作的时候，如果有其他的终端
在操作数据，等其他终端操作完成在执行；

create table tea(
 id int,
 name varchar(20),
 age tinyint
);

insert into tea values(1,’liis’,30);
注意：tea表后没有列名，插入整行数据，	
     数据的顺序和建表的顺讯一致

insert into tea(name,id) values(‘jake’,2);
注意：插入部分数据，表名后必须写插入的列名，
 values中插入数据和表名后的列位置对应

mysql中可以同时插入多行
insert into tea value(1,’lisi’,30),(2,’jake’,33)(3,’wangwu’,20);


insert into tea(id, name) value(1,’lisi’),(2,’jake’)(3,’wangwu’);


插入的数据来源于其他的表
insert into cu
	select id,name
	from tea;

insert into cu(id,name)
	select id,name
	from tea;


注意：表后的列个数和类型和select后面跟的列的个数及类型要一致


删除数据
delete [LOW_PRIORITY] from 表明 where 条件 limit num
注意：num表示数据，到第几行

注意：delete只删除数据不删除数据占据的磁盘空间
truncate 截取表，把表中数据及数据所占的空间全部回收
语法：
truncate 表名

清空表中的所有数据
删除表前5条数据
delete from tea limit 5;

加where ，删除符合where条件的数据

找出所有名字叫lisi结果，删除前3行
delete from tea where name=‘lisi’ limit 3;



> < >= <= <> != between and 
in,not in等

age between 10 and 20
等价
age<=20 and age >=10

update修改表中的数据
语法
update [LOW_PRIORITY] table_name 
set column_name=val,column_name1=val1….
where 条件 limit num;

修改名字叫做lisi的学生信息，名字改为王五，年龄改为30
update tea set name=‘wangwu’,age=30
	where name=‘lisi’

不跟where条件整张表全部修改
update tea set name=‘wangwu’,age=30

修改整张表的前10行数据
update tea set name=‘wangwu’,age=30 limit 10;

找出所有名字叫做jake的行，前10行修改数据
update tea set name=‘wangwu’,age=30  
		where name=‘jake’ limit 10;

mysql中整理表
Optimize table table_name;

optimize table tea;


操作符和函数

执行sql文件的过程

第一步：sql文件放在家目录下
第二步：家目录下执行mysql -uroot -proot 进入数据库
第三步:切入要导入表的数据库
	use	 briup
第四步：执行sql文件
source briup.sql

show tables

数据字典	；
s_emp 员工表
  id:员工编号
  last_name
  first_name
  userid 用户编号
  start_date入职时间
  comments个人评价
  manager_id 经理的编号
  title 职位
  dept_id 部门编号
  salary
  commission_pct 提成
s_dept 部门表
 id 部门编号
 name 部门的名字
 region_id 区域编号
s_region 区域表
 id 区域的编号
 name 区域的名字


表和表之间的关系
s_emp		s_dept	 	s_region
id(pk)		
dept_id(fk)	id(pk)
		region_id(fk)	id(pk)


算术操作符号
+—* ／ div mod %
| & << >>  ^ ~ !

／ div 除
mod % 取余

select mod(1600,3);

select mod(salary,3)
from s_emp;




select 列名,列名 别名,列名
from 表名；
查询员工年薪；
select id,last_name,salary*13 sal
from s_emp;

比较运算符
< <= > >= != <> =

在1990年3月8号之后入职的员工
select id, last_name,start_date
from s_emp
where start_date>’1990-03-08’;

执行顺序
from->where->select
where 条件后执行条件的结果true false

查询名字为Smith的员工信息
select	id,last_name,salary
from s_emp
where last_name=‘Smith’;

注意：字符串中注意大小写

逻辑操作符
between and
not between and

查询薪水在1000到2000的员工信息
select	id,last_name,salary
from s_emp
where salary>=1000 and salary<=2000;
等价
select	id,last_name,salary
from s_emp
where salary between 1000 and 2000;
[1000,2000]
注意 and 表示并且，前后都要成立，数据会保留
T and F ->F

查询薪水不在1000到2000的员工信息，除去1000和2000
select id,last_name,salary 
from s_emp 
where salary not between 1100  and 1525;
1100) (1525


case when then else end
多个条件判断语句
查询员工的名字，名字为Patel的输出ok
名字为Smith输出no bye 其他的输出go go

select id, case 
	   when last_name=‘Patel’ then ‘ok’
	   when last_name=‘Smith’ then ’no bye’
	   else ‘go go..’
	   end name,salary
from s_emp;


in 表示某个范围
查询员工在31或41或33部门的员工信息
or 前后链接2个条件， T or F ->T 
		  T or T->T
		  F or F ->F

select id,last_name,dept_id
from s_emp
where dept_id=31 or dept_id=41 or dept_id=33;
等价
select id,last_name,dept_id
from s_emp
where dept_id in(31,41,33);

not in 表示不在指定的范围
查询员工不在31或41或33部门的员工信息
select id,last_name,dept_id
from s_emp
where dept_id  not in(31,41,33)

exists(查询操作) 查询有结果返回为true，否则为false


查询员工表，有Smith员工，就展示所有员工信息
select id,last_name
from s_emp
where exists(select id,last_name 
	    from s_emp 
	     where last_name=‘Smith’);

not exists 查询有结果返回为false，否则为true

is null	
为空
is not null	
不为空

查询没有提出的员工信息
select id,last_name,commission_pct
from s_emp
where commission_pct is null;

update s_emp set commission_pct='10' where id=1;
查询有提出的员工信息
select id,last_name,commission_pct
from s_emp
where commission_pct is not null;

like 	模糊查询
_ 代表一个任意的字符
% 代表0个到多个任意字符
\转义符

查询名字第二字母为m的所有员工信息
select id,last_name
from s_emp
where last_name like ‘_m%’;

查询以_开头的员工信息
select id,last_name
from s_emp
where last_name like ‘\_%’;

函数：
字符函数
LOWER 转化小写
select lower(‘HELLO’);

upper 转化为大写
select id,upper(last_name)
from s_emp;

CONCAT 拼接字符串
select concat(‘hello’,’bye’);//hellobye

拼接处员工表员工的完整名字 last_name.first_name
注意：函数可以相互之间嵌套
select concat(last_name,concat(‘.’,first_name))
from s_emp;


SUBSTR 截取字符串
select substr(‘hello’,2);
注意：两个参数，第二个参数表示从第几个开始截取
select substr(‘hello’,2,2);
注意：两个参数，第二个参数表示从第几个开始截取
第三个参数表示截取的位数

length 获取字符串的长度
select length(‘hello’);

数字函数

round 四舍五入
select round(35.545);//36
select round(35.545,2);//34.55
select round(35.545,-1);//40
select round(35.545,-2);//0
正数表示小数点后取到第几位
负数小数点前看到第几位
trunc 截取（mysql5.7不支持）
select trunc(35.545);//35
select trunc(35.545,2);//35.54
select trunc(35.545,-1);//30
select trunc(35.545,-2);//0
mod 取余
Abc 绝对值（mysql5.7不支持）
select Abc(-3);//3

日期函数
curdate当前时间 年月日
select curdate();
curtime 当前时间 小时分钟秒
select curtime();
now当前时间 年月日小时分钟秒
select now();
subdate 表示指定时间的前多少天
select subdate(curdate(),2);
select subdate(’2019-08-23’,2);
adddate 表示指定时间的后多少天
select adddate(curdate(),1);
select adddate(’2019-08-23’,2);
subtime 指定时间的前几秒
select subtime(curtime(),5)
additime 指定时间的后几秒
select addtime(curtime(),5)
datediff 两个时间相差多少天
select datediff(‘2019-09-15’,’2019-09-30’);//-15
last_day 指定日期的当前月份最后一天
select last_day(‘2019-09-15’);

select year(now())//2019
select month(now());
select week(now());
select dayofmonth(now());
select day(now());
//查看当前是第几个季度
select quarter(now());
select hours(now());
select minute(now());	
select second(now());
将时间转化为特定格式的时间输出
date_format

select date_format(now(),’%b’);
将字符串格式的时间转化为标准时间
str_to_date
select str_to_date(‘20190919’,’%Y%m%d’)


千年虫:
在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，
假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个
情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对
年份的计算就会出现问题。这个事情当时被称为千年虫

数据库中表示日期中年份的有俩种: yy和rr
之前一直使用的时候yy格式,后来才有的rr格式
yy表示使用一个俩位数表示当前年份:
1990 ---yy数据库格式---> 90
1968 ---yy数据库格式---> 68
1979 ---yy数据库格式---> 79
	
rr格式表示: 另外参照图片:rr日期格式规则.png
如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：
规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。
规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。
规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。
规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。

注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。


其他函数（对字符串内容加密和解密）
第一组：
加密
AES_ENCRYPT(key1,key)
第一个参数是需要加密的字符串，
第二个参数密钥
select AES_ENCRYPT(‘hello’,’key’);
解密
AES_DECRYPT(key,key1)
select AES_DECRYPT(AES_ENCRYPT(‘hello’,’key’),’key’)

第二组：
加密
ENCODE(key,key1)
第一个参数是需要加密的字符串，
第二个参数密钥
select encode(‘hello’,’index’);
解密
DECODE
select decode(encode('hello','index'),'index');

第三组：
MD5
select md5(‘hello’);

第四组：
ENCRYPT	在window下不行，ENCRYPT采用的算法是linux内置的
select ENCRYPT(‘hello’);

第五组：
PASSWORD 数据库支持的
select password(‘root’);

select查询

select [SQL_SMALL_RESULT|SQL_BIG_RESULT|HIGH_PRIORITY|DISTINCT]	
		列名,列名1 alias_name,列名2
from 表名1，表名2...
where 条件，对表中查询的结果进行一行一行的筛选
group by  分组：把相同列的值归位一个组
having	  条件 ：对分组之后的结果进行一行一行的筛选
order by 对展示的数据排序
limit	限制展示的函数

注意：SQL_SMALL_RESULT 查询的结果比较小，from查询出来的数据用临时表存储，
SQL_BIG_RESULT 查询结果比较大，可以用磁盘存储
HIGH_PRIORITY 当前操作最优先执行

执行顺序：
from->where ->group by ->order by->having ->limit


distinct 	去重操作
员工表中名字相同的只保留一个
select distinct salary
from s_emp;
注意：distinct只能在select后面
     distinct 去重是select后的列组合去重

select distinct last_name,salary
from s_emp;

last_name salary
lisi	1000
lisi	1200
lisi	1000


order by 排序
ASC 默认升序排序
DESC 降序


查询员工信息，按照薪水降序排序
select	last_name,salary
from s_emp
order by salary desc;
查询员工信息，按照薪水降序排序,如果薪水相同的，按照名字升序排序
select	last_name,salary
from s_emp
order by salary desc,last_name;

注意：order by后跟多列，先按照第一列排序，第一列排序相等的按照第二列
排序，依次列推。。

order by 后面的列可以用数字表示，
数字指的的select后的位置，位置从1开始
select last_name,salary
from s_emp
order by 2 desc,1;


笛卡尔积
A={a,b,c} B={1,2,3}
A*B->{a,1}{a,2}{a,3}{b,1}{b,2}{b,3}{c,1}{c,2}{c,3}

多表查询
s_emp 				s_dept
id	last_name dept_id	id	name
1	lisi	  1	        1	web
2	wangwu	  1		2	bigData
3	briup	  2		3	php
4	jake	  3

select *
from s_emp,s_dept
注意：1.from来源两种表，结果第二张表的每一行匹配第一行的所有数据
     2.多表查询中给表起别名
select s.id,s.last_name,d.id,d.name
from s_emp s,s_dept d
结果
id	last_name dept_id（fk）	id（pk）	name
1	lisi	  1		1	web
2	wangwu	  1		1	web
3	briup	  2		1	web
4	jake	  3		1	web
1	lisi	  1		2	bigData
2	wangwu	  1		2	bigData
3	briup	  2		2	bigData
4	jake	  3		2	bigData
1	lisi	  1		3	php
2	wangwu	  1		3	php
3	briup	  2		3	php
4	jake	  3		3	php


等值链接（内链接）
从多表得到的笛卡尔集中基于关系找出具有关联关系的数据
	
查询员工信息及所属部门
select s.last_name,s.salary,s.dept_id,d.id,d.name
from s_emp s,s_dept d
where s.dept_id=d.id;
结果：
id	last_name dept_id（fk）	id（pk）	name
1	lisi	  1		1	web
2	wangwu	  1		1	web
3	briup	  2		2	bigData
4	jake	  3		3	php		

等价的写法：
select s.last_name,s.salary,s.dept_id,d.id,d.name
from s_emp s inner join s_dept d
on s.dept_id=d.id; 



straight_join强制顺序链接，由straight_join链接的多张表顺序
加载求笛卡尔积 (了解)
select s.last_name,s.salary,s.dept_id,d.id,d.name
from s_emp s straight_join s_dept d
on s.dept_id=d.id;

不等值链接
两张表没有主外键关联关系，基于某个附加条件把两张表
联系在一起
				等级表
s_emp 			     	s_grade
id	last_name salary	id	name	minsal maxsal
1	lisi	  1000	        1	‘金领’	2500	4000
2	wangwu	   800		2	‘蓝领’	1500	2499
3	briup	  3000		3	‘白领’	0	1499
4	jake	  2500

create table s_grade(
id tinyint  primary key,
name varchar(20),
minsal int,
maxsal int
);
insert into s_grade values(1,‘金领’,2500,4000);
insert into s_grade values(2,‘蓝领’,1500,2499);
insert into s_grade values(3,‘白领’,0,1499);

查询每个员工所属的等级
select
from s_emp s,s_grade g
结果
id	last_name salary	id	name	minsal maxsal
1	lisi	  1000	     	 1	‘金领’	2500	4000 	
2	wangwu	   800		 1	‘金领’	2500	4000
3	briup	  3000		 1	‘金领’	2500	4000
4	jake	  2500 		 1	‘金领’	2500	4000
1	lisi	  1000	       	 2	‘蓝领’	1500	2499
2	wangwu	   800		 2	‘蓝领’	1500	2499
3	briup	  3000		 2	‘蓝领’	1500	2499
4	jake	  2500		 2	‘蓝领’	1500	2499
1	lisi	  1000	       	 3	‘白领’	0	1499
2	wangwu	   800		 3	‘白领’	0	1499
3	briup	  3000		 3	‘白领’	0	1499
4	jake	  2500 		 3	‘白领’	0	1499

select s.last_name,s.salary,g.name
from s_emp s,s_grade g
where s.salary between g.minsal and g.maxsal
结果:
id	last_name salary	id	name	minsal maxsal
3	briup	  3000		 1	‘金领’	2500	4000
4	jake	  2500 		 1	‘金领’	2500	4000
1	lisi	  1000	       	 3	‘白领’	0	1499
2	wangwu	   800		 3	‘白领’	0	1499


外链接
 	from s_emp left outer join s_dept
 左外链接  left outer join
 在等值链接的基础之上，left outer join 链接的前面是主表，
 后面的是从表，找出具有关联的数据之外，在列出所有主表不匹配的数据
 s_emp 				s_dept
 id	last_name dept_id	id	name
 1	lisi	  1	        1	web
 2	wangwu	  1		2	bigData
 3	briup	  2		3	php
 4	jake	  3		4	java
 5 	briup			

 select
 from s_emp s,s_dept d
 where s.dept_id=d.id
 等值链接的结果
 id	last_name dept_id（fk）	id（pk）	name
 1	lisi	  1		1	web
 2	wangwu	  1		1	web
 3	briup	  2		2	bigData
 4	jake	  3		3	php
 左链接
 select s.last_name,s.dept_id,d.id,d.name
 from s_emp s left outer join s_dept d
 on s.dept_id=d.id;
 结果：
 id	last_name dept_id（fk）	id（pk）	name
 1	lisi	  1		1	web
 2	wangwu	  1		1	web
 3	briup	  2		2	bigData
 4	jake	  3		3	php
 5 	briup

 select 
 from  s_dept d left outer join s_emp s 
 on s.dept_id=d.id;
 结果：
 id	last_name dept_id（fk）	id（pk）	name
 1	lisi	  1		1	web
 2	wangwu	  1		1	web
 3	briup	  2		2	bigData
 4	jake	  3		3	php
				4	java



 右外链接	 right outer join
 在等值链接的基础之上，right outer join 链接的前面是主表，
 后面的是从表，找出具有关联的数据之外，在列出所有从表不匹配的数据
 
 查询员工信息及所属部门，部门没有员工也要显示出来
 右外链接
 select s.last_name,s.dept_id,d.id,d.name
 from s_emp s right join s_dept d
 on s.dept_id=d.id
 
	


 全链接	 full outer join
 在等值链接的基础之上，full outer join 链接的前面是主表，
 后面的是从表，找出具有关联的数据之外，在列出所有主表从表不匹配的数据
 select s.last_name,s.dept_id,d.id,d.name
 from s_emp s full outer join s_dept d
 on s.dept_id=d.id (mysql不支持)



select s.last_name,s.dept_id,d.id,d.name
 from s_emp s left outer join s_dept d
 on s.dept_id=d.id
 union
select s.last_name,s.dept_id,d.id,d.name
 from s_emp s right outer join s_dept d
 on s.dept_id=d.id;





union 并集去重
select id
from s_emp
where id>=2 and id<=7
union 
select id
from s_emp
where id>=5 and id<=9

//2,3,4,5,6,7
//5,6,7,8,9

union 	all 并集不去重
select id
from s_emp
where id>=2 and id<=7
union all
select id
from s_emp
where id>=5 and id<=9


自链接：表自身链接自身
查询员工及员工的经理信息
select s.last_name,s.manager_id,m.id,m.last_name
from s_emp s,s_emp m
where s.manager_id=m.id;


limit 限制返回结果。  分页
后面跟1个值，返回多少行
后面跟两个值，第一个值表示从第几条开始，第二个值取几行


select id,last_name,salary
from s_emp
limit 15;

select id,last_name,salary
from s_emp
order by salary desc
limit 15;


select id,last_name,salary
from s_emp
limit 5,5;

组函数
select	
from
where	
group	 by
having	
order by
limit

注意：group by后跟列，分组的标准（值相等的归为一组
分组之后的结果只能给出一行内容）

avg 求平均值
查询所有员工的平均工资
select avg(salary)
from s_emp

注意：没有group by的时候select后只跟组函数

查询所有员工的平均薪水，但是Smith工资不算入
select avg(salary)
from s_emp
where last_name !=’Smith’;

count 计数
查询有多少员工
select count(*)
from s_emp;
查询有多少员工,同名只算一次
select count(distinct last_name)
from s_emp;

max 求最大值
查询员工中最高工资
select max(salary)
from s_emp;

min求最小值
select min(salary)
from s_emp;

stddev 标准差
求员工薪资的标准差
select stddev(salary)
from s_emp;

sum 求合
求员工总的工资是多少
select sum(salary)
from s_emp;


查询每一个部门的最高工资
s_emp
id 	last_name   salary 	dept_id
1	lisi	    2000	1
2	jake	    3000	2
3	wangwu	    1450	1
4	lili	    2500	3
5	tom	    3500	1
6 	briup	    1000	2
基于部门编号分组
select 
from s_emp
group by dept_id
结果：
1组
id 	last_name   salary 	dept_id
1	lisi	    2000	1	
3	wangwu	    1450	1
5	tom	    3500	1
2组
2	jake	    3000	2
6 	briup	    1000	2
3组
4	lili	    2500	3

select dept_id,max(salary)
from s_emp
group by dept_id
结果:
max(salary)	dept_id
3500		1	
3000		2
2500		3

注意：group by后面出现的列可以出现在select后面也可以不跟
     select后面出现的列必须要出现在group by后面
     group by后面如果出现多列，分组是基
	于group by后面的列的组合分组

having 对分组之后的结果进行限定的
查询部门最高工资高于1300的所有部门
select dept_id,max(salary)
from s_emp
group by dept_id
having max(salary)>1300;

注意：
where 和having




子查询(嵌套查询)：一个查询是另外一个查询的条件
查询工资比Simth工资高的员工信息
第一步：Simth工资
select salary
from s_emp
where last_name=’Smith’;
第二步；
select last_name,salary
from s_emp
where salary>(select salary
	     from s_emp
              where last_name=’Smith’);


注意:注意子查询给出的结果（列和行）

查询平均工资比1247.5高的部门中员工信息

第一步：
平均工资比1247.5高的部门
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5

第二步
select last_name,salary
from s_emp
where dept_id in(select dept_id
		from s_emp
		group by dept_id
		having avg(salary)>1247.5);

第二种解法(把查询的结果看是一张表操作)
select dept_id,avg(salary) avg
from s_emp
group by dept_id
having avg(salary)>1247.5


select s.last_name,m.dept_id,m.avg
from s_emp s,() m		
where s.dept_id=m.dept_id
查询平均工资比41号部门的平均工资高的部门中员工的信息
1查询41部门的平均工资
select avg(salary)
from s_emp
where dept_id=41;
2平均工资比41号部门的平均工资高的部门
select dept_id
from s_emp
group by dept_id
having avg(salary)>(1)
3员工的信息
select last_name,salary
from s_emp
where dept_id in(2)

练习
查询s_emp表中部门的平均工资大于等于1400的部门,
并且显示出这些部门的名字,同时按照部门编号进行排序

select s.dept_id,d.name,avg(salary)
from s_emp s,s_dept d
where s.dept_id=d.id
group by dept_id,d.name
having avg(salary)>1400
order by dept_id;

或
第一步
平均工资大于等于1400的部门
select dept_id
from s_emp
group by dept_id
having avg(salary)>1400

select named
from s_dept
where id in()
或
select dept_id,avg(salary) avg
from s_emp
group by dept_id
having avg(salary)>1400

select d.name,d.id,m.avg
from s_dept d,() m
where d.id=m.dept_id



查询s_emp表每个部门的最大工资数,并且显示出这个最
大工资的员工名字以及该部门的名字和该部门所属区域,并
且使用部门编号进行排序
1每个部门的最大工资数
select max(salary) max,dept_id
from s_emp
group by dept_id
2员工名字
select s.last_name,m.id,r.name,m.max,d.name
from s_emp s,(1) m,s_dept d,s_region r
where s.dept_id=m.dept_id and s.salary=m.max
and s.dept_id=d.id and d.region_id=r.id
order by s.dept_id;


查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显
1示出当前部门的平均工资
41号部门的平均工资
select	avg(salary)
from s_emp
where dept_id=41
2平均工资比 41号部门的平均工资 高的部门
select dept_id,avg(salary) avg
from s_emp
group by dept_id
having avg(salary)>(1)


select s.last_name,m.avg
from s_emp s,(2) m
where s.dept_id=m.dept_id;



查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并
且显示出当前部门的平均工资,同时显示出部门的名字
select s.last_name,m.avg,d.name
from s_emp s,(2) m,s_dept d
where s.dept_id=m.dept_id and s.dept_id=d.id;

查询员工信息,这些员工的工资要比自己所在部门的平均工资高
1所在部门的平均工资
select dept_id,avg(salary) avg 
from s_emp
group by dept_id

2
select s.last_name,s.dept_id,s.salary,m.avg
from s_emp s,(1) m
where s.dept_id=m.dept_id and s.salary>m.avg





查询员工信息,这些员工的工资要比自己所在部门的平均工资高,同时
显示部门的名称以及所在地区
select s.last_name,s.dept_id,s.salary,m.avg
from s_emp s,(1) m,s_dept d,s_region r
where s.dept_id=m.dept_id and s.salary>m.avg
and s.dept_id=d.id and d.region_id=r.id

查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工
所在部门的平均工资 也要 比Ngao所在部门的平均工资要高
1.Ngao所在部门
select dept_id
from s_emp
where last_name=’Ngao’

2Ngao所在部门平均工资 
select avg(salary) avg
from s_emp
where dept_id=(1)

3员工所在部门的平均工资 也要 比Ngao所在部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(2)

4.员工信息
select
from s_emp s,(3) m
where s.dept_id=m.dept_id and s.salary>(2)
and m.avg>(2)


或
3
select dept_id
from s_emp
group by dept_id
having avg(salary)>(2)

4.
select last_name	
from s_emp
where salary>(2) and (dept_id in(3))


查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员
工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显
示当前部门的平均工资
select	s.last_name,m.avg
from s_emp s,(3) m
where s.dept_id=m.dept_id and s.salary>(2)
and m.avg>(2)

查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工
所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显示当
前部门的平均工资以及部门的名字和所在地区
select	s.last_name,m.avg
from s_emp s,(3) m,s_dept d,s_region r
where s.dept_id=m.dept_id and s.salary>(2)
and m.avg>(2)	and s.dept_id=d.id and
 d.region_id=r.id


s_emp	table
id last_name salary…


select dept_id,avg(salary)
from s_emp
group by dept_id		

s_view 
dept_id	avg(salary)



select id,last_name
from s_emp
where id<23


s_view1
id	last_name


视图：视图是一个虚拟表，它由存储的查询结果构成，可
以将它的输出看作是一张表，所谓视图就是提取一张或者
多张表的数据生成一个映射，管理视图可以同样达到操作
原表的效果，方便数据的管理以及安全操作，同时能够把
一些重要的数据隐藏起来。

视图的特点：
1.视图的列可以来自于不同的表，是表的抽象和逻辑意义
上建立新的关系
2.视图是由基本表（实表）产生的表（虚表）
3.视图的建立和删除不影响基本表
4.对视图内容的更新（添加删除修改）直接影响基本表（仅限于简单视图）



构建视图的语法：
create [algorithm=算法] view view_name
as [查询操作]

create view test_view
as
select id,last_name
from s_emp;

注意：algorithm=merge/temptable/undifined
Merge:当引用视图时，引用视图的语句与定义视图的语句合并
意味着视图只是一个语句，创建视图时，把创建视图的语句记录，
在查询视图时，解析查询视图的语义，并和创建视图时记录的语
句结合，形成新的查询语句
Temptable:当引用视图时，根据视图的创建语句建立一个临时表
undefined：未定义，自动让系统帮你选

删除视图
drop view view_name

修改视图
alter view view_name as [查询操作]

注意：查询操作可以是针对单表查询或子查询也可以是多表查询

例子：创建视图，工资大于1000的员工，然后再此视图上查询，工资小于3000的员工
查询视图的过程中，其实是把两个语句合并在一起
真正发挥作用的是 where salary <3000 and salary >1000
最终只是拼凑了语句，然后去查询s_emp表


简单视图	
表与表一对一映射（列于列直接映射）

复杂视图
	映射的多表
	group by



create algorithm=temptable view test5
as
select id,name
from s_dept

create view test2
as
select id,last_name,salary,dept_id
from s_emp
where salary>1000


select dept_id,avg(salary)
from test2
group by dept_id;


拼接sql语句
select id,last_name,salary
from s_bmp
where salary>1000 and salary<3000

insert into test2 values(23,’briup’,3000);


insert into s_emp(id,last_name,salary) values(23,’briup’,3000)





触发器

事件源  事件对象   监听器

监视的是哪张表（订单）
监视的是哪种行为（增删改）
触发什么行为（增删改）
什么时间触发（after/before）

作用：监视某种情况，并触发某种操作
使用触发器，可以帮助我们完成上述问题，我们可以监视某张表的变化，当发生某种变化时，立刻触发某个操作

触发器能监视的操作
增      删      改

触发器能触发的操作
增		删 		改
触发器的语法：
create trigger t1 
after/before    insert/update/delete
on 表名（监听的表）
for each row #这句话是固定的，mysql没有其他数据库触发器功能强
begin
sql语句;
sql语句;
… 	
end

注意：
1 分割多条sql语句
2 提交执行sql语句
delimiter # 	定义结束符


电商平台，用户购买了商品，库存就会减少；
观察一下场景
一个电子商城
商品表 good
主键     商品名      库存
1 	电脑	   28
2        自行车      12
create table goods(
 	id int primary key,
	good_name varchar(50),
	kc int
)
insert into goods values
(1,'电脑',50),
(2,'手机',50),
(3,'自行车',50)#

订单表 order
订单主键     商品外键      购买数量
1            1             3
2            2             5
create table order1(
	id int primary key,
	good_id int ,
	num int
)
常规实现方式：

当购买了3个电脑后，电脑库存立刻-3
//sql代表下订单
insert into order(gid,num) values(1,3)
//下单后立刻会做的事
update goods set kc = kc-oreder.num where id=order.good_id;


触发器实现


create trigger tg1 
after  insert 
on order1
for each row
begin
update goods set kc = kc-new.num
where new.good_id = id;
end#






1 撤销一个订单，库存相应增加
删除一个旧行，可以用old 来表示行中每一列的值，用old.列名

create trigger tg2 
after  delete 
on order1
for each row
begin
	update goods set kc = kc + old.num
	where id = old.good_id;
end#

列出所有的触发器
show triggers;

2 修改一个订单的数量时，库存相应改变
old获得没更改之前的行中的值，
new获得更改之后的行中的值

3 研究一下before 和 after 的区别
按照以上写法，如果订单数量较大，库存会变成负数

after先完成数据的增删改，再触发，触发中的语句执行晚于增删改，不能对增删改做出产生影响
before是先完成触发，再进行增删改，触发的语句执行早于增删改，可以对增删改做出影响
begin
	if 逻辑表达式
		then 做某某事情
	else 做某某事情
	end if
end 



练习：对于所下订单进行判断，如果订单数量>5，认为是恶意订单，强制把所订的商品数量改成5

create trigger tg5 
before insert on order1 
for each row 
begin 
    if new.num>5 
    then set new.num=5;
    end if; 
    update goods set kc = kc-new.num 
    where id = new.good_id; 
    end$


删除触发器
drop trigger 触发器的名字；


事务：是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的
一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要
么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）
事务的特点
1、数据库事务必须具备ACID特性，ACID是Atomic（原子性）、
Consistency（一致性）、Isolation（隔离性）和Durability
（持久性）的英文缩写。
1）原子性（Atomicity）
一个事务(transaction)中的所有操作，要么全部完成，要么
全部不完成，不会结束在中间某个环节。事务在执行过程中发生错
误，会被回滚（Rollback）到事务开始前的状态，就像这个事务
从来没有执行过一样。
2）一致性（Consistency）
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须
处于一致性状态。如果事务成功地完成，那么系统中所有变化将正确
地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的
所有变化将自动地回滚，系统返回到原始状态。

3）隔离性（Isolation）
指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务
都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发
事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另
一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不
会查看到中间状态的数据。

A1000	B 100

4）持久性（Durability）
指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。
即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成
功结束时的状态。

选取正确的存储引擎
InnoDB  BDB



开启事务
在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后
就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN
或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁
止使用当前会话的自动提交。
start transaction /执行DML语句
sql...
[SAVEPOINT 名字;  ]
sql...
[rollback to 名字]  (不会结束事务)
sql...
commit/rollback代表事务结束
有一些语句会影响事务的结束
事务没结束开启下一次事务
  DDL语句，ALTER DATABASE、ALTER EVENT、ALTER PROCEDURE、ALTER TABLE、ALTER VIEW、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等；
  修改MYSQL架构的语句，CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD；
	  管理语句，ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE等。


事务：insert ，update，delete


事务隔离级别
	数据库操作过程中存在的3个问题：
	不可重复读：
		在同一事务中，两次读取同一数据，得到内容不同
        	会话1：查询一条记录
              -------------->会话2：更新会话1查询的记录
              -------------->会话2：调用commit进行提交
        	会话1：再次查询上次的记录
		此时会话1对同一数据查询了两次，可得到的内容不同，称为不可重复读

	幻影读：
		同一事务中，用同样的操作读取两次，得到的记录数不相同
       	会话1：查询表中所有记录
                          --------------> 会话2：插入一条记录
                          --------------> 会话2：调用commit进行提交
        	会话1：再次查询表中所有记录
        	此时事务1两次查询到的记录是不一样的，称为幻读

	脏读：会话1读到会话2未提交的数据。

事务的隔离级别指的是 隔离性的强弱，主要是针对以下三个问题
这三个问题，有时候在逻辑中是允许出现，那就要求隔离
不那么强有时候在这个问题在逻辑中不允许出现，那就要求
隔离性比较强
为了处理这些问题，SQL标准定义了4类隔离级别，包括了一些具体规则，
用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离
级一般支持更高的并发处理，并拥有更低的系统开销。
READ UNCOMMITTED 幻影读、不可重复读和脏读都允许。
READ COMMITTED 允许幻影读、不可重复读，不允许脏读
REPEATABLE READ  (默认)允许幻影读，不允许不可重复读和脏读
SERIALIZABLE 幻想读、不可重复读和脏读都不允许

查看当前mysql支持的事务隔离级别
全局级别的：
select @@global.tx_isolation;
会话级别的:
select @@tx_isolation;
select @@session.tx_isolation;

设置隔离级别
set global|session transaction isolation level READ COMMITTED ;

mysql的配置文件中设置全局的永久生效
transaction-isolation=Read-Committed


Read Uncommitted（读取未提交内容）

   在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少
用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称
之为脏读（Dirty Read）。
Read Committed（读取提交内容）

   这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简
单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的
不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其
间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（可重读）

   这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会
看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。
简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，
当用户再读取该范围的数据行时，会发现有新的“幻影” 行。	InnoDB和Falcon存储
引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）
    这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。 



数据库备份和恢复
导出库 ，也可以导出表
mysqldump -uroot -proot [-A] [-B] 库 表1 表2 表n > 目标地址
mysqldump -uroot -proot db_test account > /home/slave1/account.sql

-A 后不用跟其他，导出所有库
-B 代表后跟的名字都是数据库名

mysqldump -uroot -proot briup s_emp > /home/slave1/s_emp.sql

mysqldump -uroot -proot -A > /home/slave1/all.sql

恢复
1 进入mysql 命令行
恢复库级备份文件
source /Users/zhaojing/all.sql
mysql -uroot -proot < /home/slave1/all.sql
2 进入命令行
use 某库
source 表级备份文件
mysql -uroot -proot 库名 < filename

卸载mysql数据库
卸载： sudo apt-get remove mysql-common
卸载：sudo apt-get autoremove --purge mysql-server-5.7
清除残留数据：dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P
再次查看MySQL的剩余依赖项：dpkg --list|grep mysql
继续删除剩余依赖项，如：sudo apt-get autoremove --purge mysql-apt-config


赋予某个用户身份可以远程操作数据库
1.注解配置文件中的bind-address=127.0.0.1
2.执行赋予权限命令
grant 权限 on 数据库.表 to 用户@ip identified by 密码
with grant option

权限：select delete update insert,alter  ,create view
     create trigger …

grant select on briup.tea to ‘root’@‘192.168.43.129’
identified by ‘root’ with grant option;

grant all privileges on *.* to ‘root’@‘%’ 
	identified by ‘root’ with grant option;
3.回收权限
回收权限的语法：
revoke 权限 on 数据库.表 from 用户@ip

revoke all privileges on *.* from ‘root’@‘%’;

立刻生效
flush privileges；