约束条件
约束条件与数据类型的宽度一样,都是可选参数
作用:用于保证数据的完整性和一致性
介绍
主要分为:
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 |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| 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 id,name from emp;
去除重复(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 id,name,age from emp where age > 20;
select id,name,age from emp where age >= 20 and age <= 38;
select id,name,age from emp where age between 20 and 38;
select id,name,age from emp where age <= 20 or age >= 38;
select id,name,age from emp where age not between 20 and 38;
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;
转载于:https://blog.51cto.com/dzm911/2083121