【三小时入门sql笔记（三）】取得某些资料、排列顺序、有条件的选取资料、创建资料库、聚合函数（最大、最小、平均、求和）

一、取得资料的筛选

1、进选取某些元素的展示

用逗号隔开能回传多个属性
全部都要就用*

SELECT `name`  from `student2`;
SELECT `name`,`major`  from `student2`;

结果：

2、对选取资料进行排序 order by

默认是从小到大
在后面加desc 变成从大到小

SELECT *  from `student2` order by `score`;
SELECT *  from `student2` order by `score` desc;

结果：

这边是先根据score进行从低到高排列，如果分数相同据根据studentid排列

SELECT *  from `student2` order by `score`,`student_id`;

3、切片选取资料显示

注意分号位置

SELECT *  from `student2` 
limit 3;

也可以跟order by结合

SELECT *  from `student2` order by `score`
limit 3;

4、有条件的选取资料显示

SELECT *  from `student2` 
where `name`='小白' and `major`='生物';

结果：

SELECT *  from `student2` 
where `major`in ('历史','英语','生物');

SELECT *  from `student2` 
where `major`= '历史'or'英语'or'生物'

二、创建公司资料库

假设我们需要创建下面资料库

SET SQL_SAFE_UPDATES=0;
CREATE TABLE `employee`(
`emp_id` INT PRIMARY KEY ,
`name`VARCHAR(20) ,
`birth_dae` 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
);
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;
create table`client`(
`client_id` INT PRIMARY KEY ,
`client_name`VARCHAR(20) ,
`phone`VARCHAR(20) 
);
create table `works_with`(
`emp_id`int,
`client_id`int,
`total_sales`int,
primary key (`emp_id`,`client_id`),
foreign key(`emp_id`)references`employee`(`emp_id`)on delete cascade,
foreign key(`client_id`)references`client`(`client_id`)on delete cascade
);
insert into `branch`values(1,'研发',null);
insert into `branch`values(2,'行政',null);
insert into `branch`values(3,'资讯',null);
insert into `employee`values(206,'小黄','1988-10-08','F',50000,1,null);
insert into `employee`values(207,'小绿','1985-09-16','F',29000,2,206);
insert into `employee`values(208,'小黑','2000-12-19','F',35000,3,206);
insert into `employee`values(209,'小白','1997-01-22','F',39000,3,207);
insert into `employee`values(210,'小蓝','1925-11-10','F',84000,1,207);
update `branch`
set`manager_id`=208
where `branch_id` =3;
insert into `client`values(400,'阿狗','2548917081');
insert into `client`values(401,'阿猫','4355243231');
insert into `client`values(402,'旺来','6252377211');
insert into `client`values(403,'露西','2454672453');
insert into `client`values(404,'艾瑞克','524345362');

三、对资料库进行搜寻

下面distinct表示搜寻出来展现不重复的值

select * from `employee`;
select * from `client`;
select * from `employee` order by `salary`;
select * from `employee` order by `salary` desc limit 3;
select `name` from `employee`;
select distinct`sex` from `employee`;

四、聚合函数

表明有这个属性的资料有几笔

select count(*)from `employee`;

select count(*)from `employee`;
select count(*)from `employee` where 'birth_date'>'1970-01-01'and `sex`='F';
select avg(`salary`)from `employee`;
select sum(`salary`)from `employee`;
select MAX(`salary`)from `employee`;
select MIN(`salary`)from `employee`;