python单表查询

创建表

create table employee(
    id int not null unique auto_increment,
    name varchar(20) not null,
    sex enum('male','female') not null default 'male', #大部分是男的
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int, #一个部门一个屋子
    depart_id int
);

插入记录

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部，全都是老师
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

语法

SELECT distinct 字段1,字段2 from 库名.表名
    where 条件            #查找符合条件的记录
    GROUP BY field(字段)  #分组
    HAVING 筛选           #过滤
    ORDER BY field(字段)  #按照后边的字段进行排序
    LIMIT 限制条数         #限制显示条数

关键字的执行优先级

from
where
group by
select
distinct
having
order by
limit

1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select(去重)
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数

单表查询

select 字段1,字段2 from 表名;

字段查询去重distinct

select distinct post,sex from employee;

查询字段数据并计算(四则运算:加减乘除都可以)

SELECT name, salary*12 FROM employee;

给显示的字段重命名

SELECT name, salary*12 Annual_salary FROM employee;

CONCAT: 就是做字符串拼接的,并且拼接之后的结果

select concat('姓名:',name,'年薪:',salary*12) Text from employee;

CONCAT_WS(): 第一个参数为分隔符来进行字符串拼接

SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary from employee;
SELECT CONCAT(name,':',salary*12) Annual_salary from employee;

WHERE约束

where 后面跟条件
1. 比较运算符：= > < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100)  值是80或90或100
4. like 'egon%'
　　pattern可以是%或_，
　　%表示任意多字符
　　_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

单条件查询

SELECT name FROM employee WHERE post='sale';
#注意优先级,where的优先级比select要高,所以顺序是先找到这个employee表,然后按照post='sale'的条件,然后去表里面select数据

多条件查询

SELECT name,salary FROM employee WHERE post='teacher' AND salary>10000;

关键字BETWEEN AND 写的是一个区间

#就是salary>=10000 and salary<=20000的数据
SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;

#加个not,就是不在这个区间内,薪资小于10000的或者薪资大于20000的,注意没有等于
SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;

关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) 判断null只能用is

SELECT name,post_comment FROM employee WHERE post_comment IS NULL;

#IS NOT NULL不等于NULL
ELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL;

#注意''是空字符串,不是null,两个是不同的东西,null是啥也没有,''是空的字符串的意思,是一种数据类型,null是另外一种数据类型
SELECT name,post_comment FROM employee WHERE post_comment='';

关键字IN集合查询

SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;

#写太麻烦了,写一大堆的or,下面用in这个简单的写法来搞
SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;

SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000);

关键字LIKE模糊查询,模糊匹配,可以结合通配符来使用
通配符’%’  #匹配任意所有字符
SELECT * FROM employee WHERE name LIKE 'eg%';

通配符’_’  #匹配任意一个字符   
SELECT * FROM employee WHERE name LIKE 'al__';
#注意我这里写的两个_,用1个的话,匹配不到alex,因为al后面还有两个字符ex

select id,name,age from employee where id>7;
首先先找到employee表,找到这个表之后,mysql会拿着where后面的约束条件去表里面找符合条件的数据,然后遍历你表中所有的数据,查看一下id是否大于7,逐条的对比,然后只要发现id比7大的,它就会把这一整条记录给select,但是select说我只拿id、name、age这个三个字段里面的数据,然后就打印了这三个字段的数据,然后where继续往下过滤,看看id是不是还有大于7的,然后发现一个符合条件的就给select一个,然后重复这样的事情,直到把数据全部过滤一遍才会结束.这就是where条件的一个工作方式

GROUP BY: 分组查询

分组指的是: 将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等,分组发生在where之后,即分组是基于where之后得到的记录而进行的
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,':',salary) as emp_members FROM employee GROUP BY post;

GROUP BY一般都会与聚合函数一起使用,聚合是什么意思: 聚合就是将分组的数据聚集到一起,合并起来,拿到一个最后的结果

#按照岗位分组,并查看每个组有多少人,每个人都有唯一的id号,我count是计算一下分组之后每组有多少的id记录,通过这个id记录我就知道每个组有多少人了
select post,count(id) as count from employee group by post;

关于集合函数,mysql提供了以下几种聚合函数: count、max、min、avg、sum等,上面的group_concat也算是一个聚合函数了,做字符串拼接的操作

注意:
如果我们用设置了unique约束的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数
强调: 聚合函数聚合的是组的内容,若是没有分组,则默认一组

示例:
#count是统计个数用的
SELECT COUNT(*) FROM employee;
#后面跟where条件的意思是统计一下满足depart_id=1这个的所有记录的个数
SELECT COUNT(*) FROM employee WHERE depart_id=1;
#max()统计分组后每组的最大值,这里没有写group by,那么就是统计整个表中所有记录中薪资最大的,薪资的值
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;

HAVING

having的语法格式和where是一模一样的,只不过having是在分组之后进行的进一步的过滤,where不能使用聚合函数,having是可以使用聚合函数的
执行优先级从高到低：where > group by > having
1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数
2. Having发生在分组groupby之后,因而Having中可以使用分组的字段,无法直接取到其他字段,having是可以使用聚合函数

统计各部门年龄在30岁及以上的员工的平均薪资，并且保留平均工资大于10000的部门
select post,avg(salary) as new_sa from employee where age>=30 group by post having avg(salary) > 10000;
注意: having只能在group by后面运行

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;
#先按照age升序,如果年纪相同,则按照薪资降序
SELECT * from employee ORDER BY age,salary DESC;
#注意排序的条件用逗号分隔

LIMIT: 限制查询的记录数

#取出工资最高的前三位,默认初始位置为0,从第一条开始顺序取出三条
SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
#从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5;
#从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; 

正则表达式查询
之前用like做模糊匹配,只有%和_，局限性比较强,所以用正则匹配

SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';

小结: 对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';