MYSQL——表约束条件，表之间的三种关系，表记录的增删改查操作
一、表的约束条件
1、约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
2、主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引（索引是一种树状结构）组织表，一张表中必须有且只有一个主键
ps: 无 primary key字段 ，无not null+unique
就会自动生成一个隐藏字段，
所以建立表的时候要有id字段，不为空且唯一的结构建立主键
作用：加速查询，表结构，表数据文件
# not null defaultcreate table t1(x int not null);
insert into t1 values();
create table t2(x int not null default 111);
insert into t2 values();
# unique
# 单列唯一
create table t3(name varchar(10) unique);
insert into t3 values("egon");
insert into t3 values("tom");
mysql> insert into t3 values("egon");
ERROR 1062 (23000): Duplicate entry "egon"for key "name"
# 联合唯一
create table server(
id int,
name varchar(10),
ip varchar(15),
port int,
unique(ip,port),
unique(name)
);
insert into server values (1,"web1","10.10.0.11",8080);
insert into server values (2,"web2","10.10.0.11",8081);
mysql> insert into server values(4,"web4","10.10.0.11",8081);
ERROR 1062 (23000): Duplicate entry "10.10.0.11-8081"for key "ip"
mysql>
# not null 和unique的化学反应=>会被识别成表的主键
create table t4(id int,name varchar(10) not null unique);
create table t5(id int,name varchar(10) unique);
# 主键primary key
# 特点
# 1、主键的约束效果是not null+unique
# 2、innodb表有且只有一个主键，但是该主键可以是联合主键
create table t6(
id int primary key auto_increment,
name varchar(5)
);
insert into t6(name) values
("egon"),
("tom"),
("to1"),
("to2");
# 联合主键（了解）
create table t7(
id int,
name varchar(5),
primary key(id,name)
);
二、表之间的三种关系
多对一
多对多
一对一
# 引入#
先创建被关联表create table dep(
id int primary key auto_increment,
name varchar(6),
comment varchar(30)
);
# 再创建关联表
create table emp(
id int primary key auto_increment,
name varchar(10),
gender varchar(5),
dep_id int,
foreign key(dep_id) references dep(id) on delete cascade on update cascade
);
# 先往被关联表插入数据
insert into dep(id,name) values
(1,"技术部"),
(2,"人力资源部"),
(3,"销售部");
# 先往关联表插入数据
insert into emp(name,gender,dep_id) values
("egon","male",1),
("alex1","male",2),
("alex2","male",2),
("alex3","male",2),
("李坦克","male",3),
("刘飞机","male",3),
("张火箭","male",3),
("林子弹","male",3),
("加特林","male",3)
;
# 多对一
# 多对多
create table author(
id int primary key auto_increment,
name varchar(10)
);
create table book(
id int primary key auto_increment,
name varchar(16)
);
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id) on delete cascade on update cascade,
foreign key(book_id) references book(id) on delete cascade on update cascade
);
# 一对一
create table customer(
id int primary key auto_increment,
name varchar(16),
phone char(11)
);
create table student(
id int primary key auto_increment,
class varchar(10),
course varchar(16),
c_id int unique,
foreign key(c_id) references customer(id) on delete cascade on update cascade
);
三、表记录相关操作
增加记录
修改记录
删除记录
查询记录（单表查询，多表联合）
单表查询语法(一定要注意执行顺序！)select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n;
# 插入mysql> create table user(name varchar(16),password varchar(10));
Query OK, 0 rows affected (0.29 sec)
mysql>
mysql> insert into user select user,password from mysql.user;
# 删除
delete from 表 where 条件;
# 更新
update 表 set 字段=值 where 条件;
# 单表查询语法
select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n;
# 简单查询
select name,sex from emp;
select name as 名字,sex 性别 from emp;
select * from emp;
# 避免重复（针对的是记录）
select distinct post from emp;
# 进行四则运算
select name as 名字,salary*12 as 年薪 from emp;
# concat()拼接记录的内容
select name ,concat(salary*12,"$") from emp;
select name ,concat("annual_salary",":",salary*12) as 年薪 from emp;
select name ,concat("annual_salary",":",salary*12,":","$") as 年薪 from emp;
select name ,concat_ws(":","annual_salary",salary*12,"$") as 年薪 from emp;
select (
case
when name="egon" then
name
when name="alex" then
concat(name,"_dsb")
else
concat(name,"_sb")
end
) as 名字 from emp;
===========================================where
select * from emp where id >= 3 and id <= 5;
select * from emp where id between 3 and 5;
select * from emp where id not between 3 and 5;
select * from emp where id=3 or id=5 or id=7;
select * from emp where id in (3,5,7);
select * from emp where id notin (3,5,7);
select * from emp where id=3 or id=5 or id=7;
select * from emp where name like "jin%";
select * from emp where name like "jin___";
select * from emp where name regexp "n$";
mysql> select * from emp where post_comment isnot null;
Empty set (0.00 sec)
mysql> update emp set post_comment="" where id=3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp where post_comment isnot null;
+----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | | 8300.00 | 401 | 1 |
+----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+
1 row in set (0.00 sec)
mysql>
select * from emp where name="丫丫";
select * from emp where name regexp "丫$";
select * from emp where name like "丫_";
select * from emp where name regexp "^程";
select hex(name) from t4 where hex(name) regexp "e[4-9][0-9a-f]{4}";
===========================================group by
分完组之后只能看到分组的字段以及聚合的结果
max()
min()
avg()
sum()
count()
select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id;
# 每个部门都有多少个人
select depart_id,count(id) from emp group by depart_id;
# 每个职位男生的平均薪资
select post,avg(salary) from emp where sex="male" group by post;
select post, group_concat(name) from emp group by post;
select post, group_concat(name) from emp where sex="male" group by post;
===========================================having
# having与where本质区别就是在于having是在分组之后发生过滤，可以使用聚合函数
mysql> select max(salary) from emp where max(salary) > 100000;
ERROR 1111 (HY000): Invalid use of group function
mysql> select max(salary) from emp having max(salary) > 100000;
+-------------+
| max(salary) |
+-------------+
| 1000000.31 |
+-------------+
1 row in set (0.00 sec)
mysql>
# 找出来男生平均薪资大于3000的职位
select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 8000;
===========================================order by排序
select * from emp order by salary;
select * from emp order by salary desc;
select * from emp order by age,id desc;
ps:asc升序（默认为升序，一般就不要写了）
desc降序select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000;
mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000;
+-----------------------------------------+---------------+
| post | avg(salary) |
+-----------------------------------------+---------------+
| operation | 16000.043333 |
| teacher | 175650.051667 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+-----------------------------------------+---------------+
3 rows in set (0.00 sec)
mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000 order by avg(salary);
+-----------------------------------------+---------------+
| post | avg(salary) |
+-----------------------------------------+---------------+
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
| operation | 16000.043333 |
| teacher | 175650.051667 |
+-----------------------------------------+---------------+
3 rows in set (0.00 sec)
mysql> select post,avg(salary) as v from emp where sex="male" group by post having avg(salary) > 3000 order by v;
+-----------------------------------------+---------------+
| post | v |
+-----------------------------------------+---------------+
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
| operation | 16000.043333 |
| teacher | 175650.051667 |
+-----------------------------------------+---------------+
3 rows in set (0.00 sec)
mysql>
分页显示：（比如淘宝里商品页面显示，可以分好多页）
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 limit 20,5;
四、
五、
---44---
以上是 MYSQL——表约束条件，表之间的三种关系，表记录的增删改查操作 的全部内容， 来源链接： utcz.com/z/529895.html
