41、数据库3

今日内容详解

约束条件

1. unsigned  设置无符号
2. zerofill  零填充
3. not null 非空
	null不等于''
4.default 默认值
5.unique 唯一
	# 单列唯一
		create table t(id int, name varchar(16) unique);
	
    mysql -h 127.0.0.1 -P 3306 -u root -p 111 password 222
	# 多列唯一
		create table t(
			id int,
			host varchar(32),
			port int,
			unique(host, port)
			);
6. primary key
	# 1.非空且唯一
		id int primary key == id int not null unique
		
		create table t (
		id int primary key
		);
		
	# Innodb存储引擎规定一张表中必须有一个主键
	  如创建表没有加primary key，是因为Innodb引擎已经帮你创建了一个隐藏的主键，这个主键看不到也不能用
	
	# 主键的功能：查询速度快
				主键本质也是一种索引
7. auto_increment
	与primary key 配合使用，自增
	
	create table t(
		id int primary key auto_increment,
		name varchar(16)
	)
	
	# 结论
		id int primary key auto_increment

清空表格数据

1. delete from t;
2. truncate from t;  # 只要是清空表数据推荐使用truncate

binlog  恢复数据

删除方式：	
	物理删除 从磁盘删掉，需要用binlog恢复
	软删除  基本每张表都要建is_delete字段 0为正常 1为已被删除

外键

数据表中一般不存中文


一对多：

create table dep(
	id int primary key auto_increment,
	dep_name char(16),
	dep_desc char(32)
);

create table emp(
	id int primary key auto_increment,
	name char(16),
	gender enum('male', 'female', 'other') default 'male',
	dep_id int,
	foreign key(dep_id) references dep(id)
	on update cascade
	on delete cascade
);


多对多：

create table auther(
	id int primary key auto_increment,
	name varchar(16)
);


create table book(
	id int primary key auto_increment,
	book_name varchar(32)
);

create authertobook(
	auther_id primary key auto_increment,
	book_id int,
	foreign key(book_id) references book(id)
	on update cascade
	on delete cascade
);

查询语句

修改表

修改表名
	alter table t1 rename t2;

增加字段：
	alter table t1 add name varchar(16) 约束条件 first/after;

删除字段
	alter table t1 drop name;

修改字段
	alter table t1 modify name char(8) 约束条件;
	
	alter table t1 change name newname varchar(16) 约束条件;

复制表

create table t2 select * from t1;  # 不能复制主键外键

create table t2 select * from t1 where id>3;

查询语句

select * from t1 where salary >100 or salary<5;

select * from t1 where salary in(200,300);

'''

模糊查询：
    %匹配任意个数字符
    _匹配一个任意字符
    %在第一个则不按索引查询
    elasticsearch 有自己的查询方式，有索引但和Innodb不一样

    select name salary from emp where name like '%0%';


查询符合字符长度为4记录
select name, salary from emp where char_length(name)=4;


查询id小于3或大于6的数据
select * from emp where id>3 or id<6;
select * from emp where (not) id between 3 and 6;


查询薪资不在20，18，17范围的数据
not in 不走索引
select * from emp where salary not in(20,18,17);


查询岗位描述为空的员工名与岗位名
select * from emp where post_comment is null;  (is不能为=，null不能为'')


查询不走索引：
	not in
	模糊查询中第一个为%


concat：将id，name，score组合成一个字符查询出来
	select concat (id, name, score) as info from tt2;

group_concat():将参数里的字段查询出来的值放在一起
	selrct name, group_concat(id) from tt2 group by name;

'''

聚合函数

max
min
avg
count
sum

严格模式

STRICT_TRANS_TABLES : 严格模式
NO_ENGINE_SUBSTITUTION : 无引擎提交
PAD_CHAR_TO_FULL_LENGTH : 填补字符到全长度

set global sql_mode='strict_trans_tables,only_full_group_by'
set global sql_mode='pad_char_to_full_length,only_full_group_by'

查询关键字之分组—group by

可以直接乘
select name, max(salary)*2 from emp group by post;

select min(salary) from emp group by post;

起别名：as  给表是临时起别名
select avg(salary) as '平均薪资' from emp group by post;

select post,count(id) from emp group by post;

group_concat  # 获取分组之后每个字段对应的所有值，还可以拼接操作
select post, group_concat(name), from emp group by post;

每个名字后面加__
select post, group_concat(name, '__'), from emp group by post;

给字段对应的值设立对应名：
select concat('NAME',name), concat('SAL',salary) from emp;b