Python Day11 MySQL 02

FragranceM

约束条件
　　约束条件与数据类型的宽度一样，都是可选参数
　　作用：用于保证数据的完整性和一致性

介绍
　　主要分为：

　　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) &gt; 1; #报错

having 过滤条件
　　having要在group by之后使用
　　执行优先级从高到低：where > group by > having
　　select post,avg(salary) from emp group by post having avg(salary) &gt; 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;