10月25日学习内容整理：数据操作：增加更新删除，单表查询操作
》》\G是按行显示，必须是大写
》》插入数据：补充另一种插入记录的方法
——>insert into 表名1（字段1，字段2，...） select 字段1，字段2，... from 表名2
把表名2的数据添加到表名1中，注意两边字段的类型要对应，否则会出问题，还有宽度也要注意
——>这个方法也可以用到创建表的时候：但是要注意两边的字段名要一样，类型也要对应，名字不一样的话插入的数据会放在后面，不会放在原本的字段上
create table 表名1(字段名1 类型，字段名2 类型，...) select 字段名1，字段名2，... from 表名2
create table user(host char(60),user char(16)) select host,user from mysql.user
——>字段名 as 字段名 就是给字段换名字，这种方法就可以解决上面字段名不一致导致数据错乱的问题，其它情况也可以用
》》更新修改数据：就是之前讲过的update和delete，还有表初始化truncate
一、单表查询数据》》》》》》》》重点重点重点
》》》查询优先级：from > where > group by > having > select > distinct > order by > limit
》》》按照这种形式写：当然每种优先级不是必须有的
select distinct 字段 from 表名
where 约束条件
group by 分组的字段
having 过滤条件
order by 排序字段
limit 限制条数
》》》补充：
>>>拼接字段 concat（'姓名 ：'，字段名，' '，'工资 ：'，字段名），这样就会按照这样的形式读取数据
>>>另一种拼接字段 concat_ws（‘分隔符’，字段1，字段2，...），这样显示的时候每个字段都会以设定的分隔符来隔开
>>>select支持加减乘除运算
1、where + 约束条件
》》比较运算：< > >= = <= !=（<>也是不等于的意思，但是通常用!=就行了）
》》between 80 and 100 在80到100之间，也包含80和100
》》in （80，90，100）括号里什么类型都可以，表示80或90或100
》》like 'e%' 模糊匹配，只有%和_两种符号，%代表任意长度的字符，_代表任意一个字符
》》逻辑运算： and or not is
》》》》》》补充：null不属于任何类型，所以把null作为约束条件时要用is，不能用空字符串来表示
》》》》》》补充：not的位置一般在约束条件的最前面或者遵循英语语法
2、group by + 分组字段名
（1）分组的概念：分组就是指把一张表中的所有记录按照某个字段中相同的字段值分成几组，分组的目的是为了能够对一个范围内的数据进行处理，所以单个记录对分组来说是没有意义的
（2）需要注意的地方：
》》》分组之后，select只能查看到分组的字段，意思就是以什么字段分组后select就只能查看这个字段，比如以部门分组，那么select就只能查看分组后部门的信息，其它例如名字和id等虽然也能查看，但是只会默认显示第一条，这对分组来说没有意义
》》》分组之后，要想查看组内内容没法直接看，需要借助聚合函数：
——聚合函数》max（字段名）求最大值，min（字段名）求最小值，sum（字段名）求和，avg（字段名）求平均值，count（字段名）计算该字段值的个数
——补充》还有一种函数group_concat（字段），会显示该字段下的所有值，可以应用在想查看某一个字段的所有信息例如部门中所有人的名字，那聚合函数显然没法用
》》》分组是为了以组为单位来处理记录，而不是单独的处理记录
》》》把被unique约束的字段作为分组的依据是没有意义的
补充：
《《若用聚合函数但是并没有用group by分组，那么默认整体为一组，这就不存在分组依据的
《《怎样去选择分组依据：多条记录的某个字段值相同就可以作为分组的依据，比如员工的所属部门，性别等
（3）sql_mode的设置：only_full_group_by
》》为了从根本上杜绝对组内某一条记录的处理，需要设置参数。就是上面所说的组内除了分组依据字段的其它字段只会默认显示第一条的问题，让用户只能查看分组依据字段和使用聚合函数
》》命令：退出重新登录生效
set global sql_mode = 'only_full_group_by';
select @@global.sql_mode;
3、having + 过滤条件
》》》注意：因为having优先级是在分组之后，所以只能用分组依据字段和聚合函数作为过滤条件，若没有分组，就将整体作为一组
》》》where支持的约束条件过滤条件也支持，都可以拿来用
》》》和where的区别：1）where发生在分组之前，所以可以有任意字段，但是约束条件不能用聚合函数；2）having发生在分组之后，不能直接查看其它字段过滤条件可以用聚合函数
4、disinct 去重：将筛选出的字段中的重复值去掉
5、order by + 排序字段名
》》》默认升序：order by 字段名 asc asc就是升序，不用写默认的，从小到大
》》》设置降序：order by 字段名 desc desc就是降序，从大到小
》》》相同值的处理： order by 字段名1 asc ，字段名2 desc，... 意思就是先按照字段名1升序排序，若遇到相同值的话就按照降序比较字段名2的值排序，以此类推
6、limit ：限制查询的记录数
》》》limit 3 代表只显示前3条信息
》》》limit 0,3 代表以0为起始位置，往后显示3条记录，若是3，3那就是以第3条记录为起始位置往后再取3条记录
》》》缺点：每次查询都是从开始遍历的一直到指定的起始位置，若数据量很庞大时会很浪费时间，所以还是要把经常访问的数据或者链接放到内存的缓冲区里，这样效率会提高
7、补充：使用正则表达式查询
》》》select * from 表名 where 字段名 regexp '正则表达式'
例如：select * from employee where name regexp '^e.*n$';
》》》》综合举例：
company.employee 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int #创建表 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) ; #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk 准备表和记录
#简单查询 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT name,salary FROM employee; #避免重复DISTINCT SELECT DISTINCT post FROM employee; #通过四则运算查询 SELECT name, salary*12 FROM employee; SELECT name, salary*12 AS Annual_salary FROM employee; SELECT name, salary*12 Annual_salary FROM employee; #定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee; CONCAT_WS() 第一个参数为分隔符 SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;
where约束：
#练习 1. 查看岗位是teacher的员工姓名、年龄 select name,age from employee where post='teacher'; 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 select name,age from employee where post='teacher' and age > 30; 3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资 select name,age,salary from employee where post='teacher' and salary between 9000 and 10000; 4. 查看岗位描述不为NULL的员工信息 select * from employee where not post_comment is null; 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 select name,salary*12 from employee where post='teacher' and name like 'jin%';
group by分组：
#练习 1. 查询岗位名以及岗位包含的所有员工名字 select post,group_concat(name) from employee group by post; 2. 查询岗位名以及各岗位内包含的员工个数 select post,count(id) from employee group by post; 3. 查询公司内男员工和女员工的个数 select sex,count(id) from employee group by sex; 4. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select sex,avg(salary) from employee group by sex;
having过滤：
#练习 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 select post,group_concat(name),count(id) from employee group by post having count(id) < 2; 3. 查询各岗位平均薪资大于10000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary) > 10000; 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
order by 排序：
#查看每个部门的最高工资 select post,max(salary) from employee group by post order by max(salary); select post,max(salary) as m from employee group by post order by m; #取出平均工资>10000的部门以及它的平均工资 select post,avg(salary) from employee group by post having avg(salary) > 10000; #错误，执行having时，select还没有执行呢 select post,avg(salary) as x from employee group by post having x > 10000; #取出平均工资>10000的部门以及它的平均工资 select post,avg(salary) as x from employee group by post having avg(salary) > 10000 order by x desc
