1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| create database `sql_tutorial`;
show databases;
# drop database `sql_tutorial`;
use `sql_tutorial`;
create table student(
`student_id` int ,
`name` varchar(20),
`major` varchar(20),
primary key(`student_id`)
);
describe `student`;
drop table `student`;
alter table `student` add `gpa` decimal(3,2);
alter table `student` drop column `gpa`;
|
每次使用的时候都需要用
插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| create table student(
`student_id` int ,
`name` varchar(20),
`major` varchar(20),
primary key(`student_id`)
);
select * from `student`;
# 插入一个数据
insert into `student` values(1,'小白','历史');
insert into `student` values(2,'小绿',null);
insert into `student` (`name`,`major`,`student_id`) values('小黑','历史',3);
# 可以只写两个，不写的那个默认为 null
insert into `student` (`major`,`student_id`) values('语文',4);
|
限制 约束
1
2
3
4
5
6
| create table student(
`student_id` int ,
`name` varchar(20) not null, 不为空
`major` varchar(20) unique, 唯一
primary key(`student_id`)
);
|
1
| `major` varchar(20) default '历史',
|
这个是错的
1
2
3
4
5
6
7
8
9
10
11
| create table student(
`student_id` int auto_increment,
`name` varchar(20) not null,
`major` varchar(20) default '历史',
primary key(`student_id`)
);
select * from `student`;
drop table `student`;
insert into `student` value('小白'); 错误
|
1
| insert into `student`(`name`) value('小白');
|
上面这个才是对的
且自动从1开始计数
修改删除 资料
如果我们想要更新分数为45为100
1
2
3
| update `student`
set `score` = 100
where `score` = 45;
|
还可以修改多个
1
2
3
| update `student`
set `score` = 100 , `major` = '物理'
where `score` = 45 or `major` = '历史';
|
如果没有where属性，则修改全部
删除数据
1
2
| delete from `student`
where `student_id` = 2;
|
多个判断条件
1
2
3
4
5
6
7
| delete from `student`
where `student_id` = 2 and `major` = '物理';
delete from `student`
where `score` < 60;
不写where删全部
|
汇总
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| SET SQL_SAFE_UPDATES = 0;
create table student(
`student_id` int auto_increment,
`name` varchar(20) not null,
`major` varchar(20) default '历史',
`score` int,
primary key(`student_id`)
);
select * from `student`;
drop table `student`;
insert into `student`(`name`,`score`) value('小蓝',45);
update `student`
set `score` = 100 , `major` = '物理'
where `score` = 45 or `major` = '历史';
delete from `student`
where `score` < 60;
|
搜寻资料
1
2
3
4
5
6
7
8
9
10
11
12
13
| select * from `student`;
select `name` from `student`;
select `name`,`score` from `student`;
select `name`
from `student`
order by `score`; 从低到高 asc
select `name`
from `student`
order by `score` desc; 从高到低
|
1
2
3
| select *
from `student`
order by `score` asc, `student_id` desc;
|
上面这个写多个排序规则
取分数由低到高前两位
1
2
3
4
| select *
from `student`
order by `score` asc
limit 2;
|
1
2
3
| select *
from `student`
where `score` = 100;
|
1
2
3
4
5
| select *
from `student`
where `score` in(100,60);
等价于
where `score` = 100 or `score` = 60;
|
如果score在100，或60
创建公司表格
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| create table `employee`(
`emp_id` int primary key,
`name` varchar(20),
`birth_date` date,
`sex` varchar(1),
`salary` int,
`branch_id` int,
`sup_id` int
);
create table `branch`(
`branch_id` int primary key,
`branch_name` varchar(20),
`manager_id` int,
foreign key(`manager_id`) references `employee`(`emp_id`) on delete set null
);
# 一定要加不然报错 这是我搞错id了
ALTER TABLE `employee`
ADD `branch_id` INT;
# 在emloyee表格 中增加foreign key
alter table `employee`
add foreign key(`branch_id`)
references `branch`(`branch_id`)
on delete set null;
alter table `employee`
add foreign key(`sup_id`)
references `employee`(`emp_id`)
on delete set null;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| create table `clint`(
`clint_id` int primary key,
`clint_name` varchar(20),
`phone` varchar(20)
);
create table `work_with`(
`emp_id` int,
`clint_id` int,
`total_sales` int,
primary key(`emp_id`,`clint_id`),
foreign key(`emp_id`) references `employee` (`emp_id`) on delete cascade,
foreign key(`clint_id`) references `clint` (`clint_id`) on delete cascade
);
|
如何插入数据
1
| insert into `employee` values(260,'小黄','1998-10-08','F',50000,1,null);
|
但是会报错
我们需要将有foreign key的先设置为null
1
2
3
4
5
6
7
8
9
10
11
12
13
| insert into `branch` values (1,'研发',null);
insert into `branch` values (2,'行政',null);
insert into `branch` values (3,'咨询',null);
insert into `employee` values(260,'小黄','1998-10-08','F',50000,null,1);
insert into `employee` values(207,'小黄','1998-10-08','F',50000,260,2);
insert into `employee` values(208,'小黄','1998-10-08','F',50000,260,3);
insert into `employee` values(209,'小黄','1998-10-08','F',50000,207,3);
insert into `employee` values(210,'小黄','1998-10-08','F',50000,207,1);
update `branch`
set `manager_id` = 208
where `branch_id` = 3;
|
1
2
3
4
5
| insert into `clint` values(400,'阿狗','254354335');
insert into `clint` values(401,'阿猫','254364335');
insert into `clint` values(402,'万财','254354735');
insert into `clint` values(403,'路西','254354325');
insert into `clint` values(404,'艾瑞克','254304335');
|
1
2
3
4
5
| insert into `work_with` values(260,400,'70000');
insert into `work_with` values(207,401,'2400');
insert into `work_with` values(208,402,'80000');
insert into `work_with` values(208,403,'9000');
insert into `work_with` values(210,404,'10000');
|
取得公司资料
1
2
3
4
5
6
7
8
9
10
11
12
| select *
from `clint`;
select *
from `employee` order by `salary`;
select *
from `employee` order by `salary` desc
limit 3;
select `name`
from `employee`;
|
我不要重复的
1
2
| select distinct `name`
from `employee`;
|
聚合函数 aggregate function
- 取得员工人数
1
2
| select count(*) from `employee` ;
也可以将 * 具体化
|
1
2
| select count(`sup_id`) from `employee` ;
如果有数据的sup_id为null的话，就不考虑计数
|
- 取得所有出生年龄在 1970-01-01 之后的女性员工个数
1
| select count(*) from `employee` where `birth_date` > '1970-01-01' and `sex` = 'F';
|
- 取得所有员工的平均薪水
1
| select avg(`salary`) from `employee` ;
|
- 取得员工薪水综合
1
| select sum(`salary`) from `employee`;
|
- 最高最低薪水
1
2
3
| select max(`salary`) from `employee`;
select min(`salary`) from `employee`;
|
