Python Day11 MySQL 02
约束条件约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
介绍
主要分为：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
说明：
[*]是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
[*]字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
[*]是否是key 主键 primary key
外键 foreign key
索引 (index,unique...)
not null与default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
CREATE TABLE tb1 ( nid INT NOT NULL defalut 2, num INT NOT NULL );
primary key
从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
一个表中可以：
举例：
单列做主键
CREATE TABLE t5 ( id INT PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(20)
);
多列做主键（复合主键）
CREATE TABLE t6 ( id int,
name char(20),
PRIMARY KEY (id, name)
);
unique key
设置唯一约束 UNIQUE
not null+unique:会在没有指定主键的情况下被识别为主键
create table service1(
id int,
name char(20) not null unique
);
mysql> desc service1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
|>
| name| char(20) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
联合唯一
create table service2( id int primary key auto_increment,
name char(20) unique,
ip char(15),
port int,
unique(ip,port) #指的是ip+port一起时不能唯一
auto_increment 自增
搜索mysql的变量
show variables like '%auto%';
结果
auto_increment_increment | 1 # 步长
auto_increment_offset | 1 # 起始位置
强调：起始位置的值必须<=步长
修改设置
set global auto_increment_increment=5;
set global auto_increment_offset=3;
foreign key 外键
创建表：
[*] 先创建被关联的表：部门表
#强调：被关联的字段必须唯一
create table dep(
id int primary key auto_increment,
name char(10)
);
[*]再创建关联表：员工表
create table emp( id int primary key auto_increment,
name char(16),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);
插入记录时：
1、先往被关联表插入：部门表
insert into dep(name) values
('外交部'),
('技术哈哈部'),
('销售部');
2、再往关联表插入：员工表
insert into emp(name,age,dep_id) values
('egon',19,1),
('alex',74,2),
('wxx',38,2),
('yxx',22,3);
错误操作
insert into emp(name,age,dep_id) values ('lhf',29,4);
表之间的关系
一对多或称为多对一
举例：书 出版社
关联方式：foreign key
多对多
举例：作者 书
关联方式：foreign key+一张新的表
一对一
举例：客户表学生表
关联方式：foreign key+unique
单表查询
单表语法：
select distinct
字段1,
字段2,...
from
表名
where 约束条件
group by 分组条件
having 过滤条件
order by 排序字段
limit n;
简单查询语句
select * from emp;
select>
去除重复（distinct）
select distinct post from emp;
四则运算+别名（as可以省略）
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp;
定义显示格式concat
select
concat('姓名: ',name) as new_name,
concat('年薪: ',salary*12) as annual_salary
from
emp;
select concat(name,'_SB') as new_name from emp;
select concat(name,'_SB') as new_name from emp where name != 'egon';
指定条件处理
select
(
case
when name = 'egon' then
name
when name = 'alex' then
concat(name,'_BIGSB')
else
concat(name,'_SB')
end
) as new_name
from
emp;
如同if else
select concat(name,':',salary,':',sex) from emp;
拼接分隔符concat_ws
select concat_ws(':',name,salary,sex) from emp;
where约束
where字句中可以使用：
[*]比较运算符：> < >= <= <> !=
[*]between 80 and 100 值在10到20之间
[*]in(80,90,100) 值是10或20或30
[*]like 'egon%' pattern可以是%或_，
%表示任意多字符
_表示一个字符
[*]逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
select>select>
select>
select>
select>
select * from emp where age=18 or age=28 or age=38;
select * from emp where age in (18,28,38);
select * from emp where age=18 or sex='male';
select * from emp where name like 'jin%'; #%代表任意多个字符
select * from emp where name like '__'; #_一个横杠代表一个任务字符
group by分组
分组依据应该找重复度比较高的字段
如果没有写group by进行分组默认也会把整张表作为一个分组后的结果
分组之后只能查到分组字段，或者聚合的结果
调整sql模式：
set global sql_mode='strict_trans_tables,only_full_group_by';
在分组之后使用聚合函数
select post,max(salary) from emp group by post;
select post,min(salary) from emp group by post;
select post,sum(salary) from emp group by post;
select post,avg(salary) from emp group by post;
select post,count(id) from emp group by post;
roup_concat可以以逗号分隔显示组内详细内容
select post,group_concat(name) from emp group by post;
错误示例，where max(salary) > 1;分组前能使用聚合函数
select count(id) from emp where max(salary) > 1; #报错
having 过滤条件
having要在group by之后使用
执行优先级从高到低：where > group by > having
select post,avg(salary) from emp group by post having avg(salary) > 20000;
order by排序
select * from emp order by age asc; # 默认升序，从小到大
select * from emp order by age desc; #从大到小
年龄相同的情况下按照id降序排
select * from emp order by age asc,id desc;
select post,avg(salary) from emp group by post order by avg(salary);
limit n
select * from emp limit 3;
#从0开始往后取5条
select * from emp limit 0,5;
select * from emp limit 5,5;
select * from emp limit 10,5;
select * from emp limit 15,5;
正则
select * from emp where name regexp '^jin.*$';
多表查询
语法：
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
[*] 笛卡尔积
select * from emp,dep;
select * from emp,dep where emp.dep_id=dep.id;
[*] 内连接inner join：取两张表的共同部分
select * from emp inner join dep on emp.dep_id=dep.id;
[*] 左连接left join：在内连接的基础上保留左表的记录
select * from emp left join dep on emp.dep_id=dep.id;
[*] 右连接right join：在内连接的基础上保留右表的记录
select * from emp right join dep on emp.dep_id=dep.id;
[*]全外连接full join：在内连接的基础上保留左、右表的记录
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
页:
[1]