一、約束條件
-
not null標(biāo)識(shí)該字段不能為空和default設(shè)置默認(rèn)值
沒設(shè)置默認(rèn)值時(shí)插入空數(shù)據(jù)會(huì)報(bào)錯(cuò)：
設(shè)置默認(rèn)值后插入空數(shù)據(jù)：
-
unique設(shè)置單列唯一
-
not null加上unique>>>>會(huì)被識(shí)別成表的主鍵
聯(lián)合唯一
兩個(gè)或多個(gè)字段都相同才能被判斷為相同
- 主鍵
primary key
特點(diǎn)：主鍵的約束效果是not null+unique,innodb表有且只有一個(gè)主鍵，但是該主鍵可以是聯(lián)合主鍵.（通常把id當(dāng)作主鍵）
create table t5(
id int primary key auto_increment,
name varchar(5)
);
insert into t5(name) values
("people1"),
("people2"),
("people3"),
("people4");
二、表之間的三種關(guān)系
先創(chuàng)建被關(guān)聯(lián)表，再創(chuàng)建關(guān)聯(lián)表；先往被關(guān)聯(lián)表插入數(shù)據(jù)，再往關(guān)聯(lián)表插入數(shù)據(jù)。
1、一對(duì)多（多對(duì)一）
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、多對(duì)多
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、一對(duì)一
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
);
三、記錄相關(guān)操作
1、單表查詢語法
select distinct 字段1,字段2,字段3,... from 表名
where 過濾條件
group by 分組的條件
having 篩選條件
order by 排序字段
limit n;
2、簡(jiǎn)單查詢
select name,sex from emp;
select name as 名字,sex 性別 from emp;
select * from emp;
3、避免重復(fù)（針對(duì)的是記錄）
select distinct post from emp;
4、進(jìn)行四則運(yùn)算
select name as 名字,salary*12 as 年薪 from emp;
5、concat()拼接記錄的內(nèi)容
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___'; # 一個(gè)_匹配一個(gè)字符
select * from emp where name regexp 'n$'; # 正則查詢
8、group by（分組）
單獨(dú)使用GROUP BY關(guān)鍵字分組
SELECT post FROM employee GROUP BY post;
注意：我們按照post字段分組，那么select查詢的字段只能是post，想要獲取組內(nèi)的其他相關(guān)信息，需要借助函數(shù)
GROUP BY關(guān)鍵字和GROUP_CONCAT()函數(shù)一起使用
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post; # 按照崗位分組，并查看組內(nèi)成員名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
GROUP BY與聚合函數(shù)一起使用
select post,count(id) as count from employee group by post; # 按照崗位分組，并查看每個(gè)組有多少人
9、聚合函數(shù)
強(qiáng)調(diào)：聚合函數(shù)聚合的是組的內(nèi)容，若是沒有分組，則默認(rèn)一組
示例：
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本質(zhì)區(qū)別就是在于having是在分組之后發(fā)生過濾，可以使用聚合函數(shù)
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排序，如果年紀(jì)相同，則按照薪資排序
SELECT * from employee
ORDER BY age,
salary DESC;
12、限制查詢的記錄數(shù)limit
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; # 默認(rèn)初始位置為0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; # 從第0開始，即先查詢出第一條，然后包含這一條在內(nèi)往后查5條
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; # 從第5開始，即先查詢出第6條，然后包含這一條在內(nèi)往后查5條
13、between…and…
指點(diǎn)范圍查找
SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;
SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;
