一、约束条件
-
not null
标识该字段不能为空和default
设置默认值
没设置默认值时插入空数据会报错:
设置默认值后插入空数据:
-
unique
设置单列唯一
-
not null
加上unique
>>>>会被识别成表的主键
-
联合唯一
两个或多个字段都相同才能被判断为相同
- 主键
primary key
特点:主键的约束效果是not null+unique,innodb表有且只有一个主键,但是该主键可以是联合主键.(通常把id当作主键)
create table t5(
id int primary key auto_increment,
name varchar(5)
);
insert into t5(name) values
("people1"),
("people2"),
("people3"),
("people4");
二、表之间的三种关系
先创建被关联表,再创建关联表;先往被关联表插入数据,再往关联表插入数据。
1、一对多(多对一)
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
);
2、多对多
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
);
3、一对一
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 update cascade on delete cascade
);
三、记录相关操作
1、单表查询语法
select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n;
2、简单查询
select name,sex from emp;
select name as 名字,sex 性别 from emp;
select * from emp;
3、避免重复(针对的是记录)
select distinct post from emp;
4、进行四则运算
select name as 名字,salary*12 as 年薪 from emp;
5、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;
6、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 not in (3,5,7);
select * from emp where id=3 or id=5 or id=7;
7、like(模糊查询)
select * from emp where name like 'jin%';
select * from emp where name like 'jin___'; # 一个_匹配一个字符
select * from emp where name regexp 'n$'; # 正则查询
8、group by(分组)
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post; # 按照岗位分组,并查看组内成员名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;
# 按照岗位分组,并查看每个组有多少人
9、聚合函数
强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
示例:
SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;
10、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;
11、order by
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
按多列排序:先按照age排序,如果年纪相同,则按照薪资排序
SELECT * from employee
ORDER BY age,
salary DESC;
12、限制查询的记录数limit
示例:
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; # 默认初始位置为0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; # 从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; # 从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
13、between…and…
指点范围查找
SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;
SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;
网友评论