day42 字段的增删改查详细操作

复习

# 1.表的详细操作 create table nt like ot; # 只复制表的结构包括约束 create table nt select * from ot where 1=2; # 复制表结构,但不复制键(相关)的约束 truncate t1; 清空表,自增记录会被重置 # 2.表中字段的详细操作 alter table t1 add ntag1 类型[(宽度) 约束],...,add ntag2 类型[(宽度) 约束]; alter table t1 add ntag 类型[(宽度) 约束] first; alter table t1 add ntag 类型[(宽度) 约束] after otag; alter table t1 modify ntag 类型[(宽度) 非键约束] first; # 移动到最前 alter table t1 modify ntag 类型[(宽度) 非键约束] after otag; # 移动到otag后 # 3.用户管理 grant all on db1.* to [email?protected] identified by ‘owen‘; # (创建用户)设置权限 # 4.表关系 create table 从表( id int primary key auto_increment,... 主表_id int,# 只是在从表中起了一个名字,该名字和主表主键对应,所有起了个见名知义的名字 foreign key(主表_id) references 主表(唯一字段名id) on update cascade on delete cascade ); # 作者与作者详情 # 外键直接影响数据库效率,但会提高数据的完整性(安全),一般首先效率,因为安全可以通过其他方式来处理

今日内容 (*******)

1.完整语法的增删改 2.单表查询 => 查询条件 3.多表查询

增语法

1.所有数据按顺序插入
insert [into] 表名 values (值1,值n)[,(值1,值n)]; 2.指定字段匹配插入，可以任意顺序 insert [into] 表名(字段2,字段1,字段n) values (值2,值1,(值2,值n)]; 3.插入查询结果 insert [into] 表1(字段1,字段n) select 字段1,字段n from 表2 [条件]; # eg: 1 create table t1( id int auto_increment,x int,y int,primary key(id) ); insert t1 values (1,2,3),(2,20,30); # 按顺序插入 insert into t1(y,x) values (300,200); # 按规定字段顺序指定插入 create table nt1 like t1; # 复制表即完整结构 insert into nt1 select * from t1; # 复制所有数据 create table tt1( x int,z int ); insert into tt1 values (999,888); insert into nt1(x) select x from tt1; # 将tt1中指定字段插入到nt1中指定的字段 insert into nt1(x,y) select x,z from tt1; # tt1x及z字段的结果赋值给nt1中x,y字段

删语法

1.会记录自增信息，操作会被日志记录，效率低
delete from 表名 [条件]; delete from t1; # 没有条件的情况下是清空所有数据,但会记录自增信息 insert into t1(x,y) values(6,66); 2.清空表，会重置自增信息 truncate table 表名; truncate table nt1; insert into nt1(x,66);

改语法

update 表名 set 字段1=值1[,...,字段n=值n] [条件] update tt1 set x=666; # 无条件,全改 update tt1 set x=777,z=555 where z < 888; # 只修改满足条件的行

查语法

select [distinct] 字段1 [as 别名],字段n [as 别名] from [库名.]表名 [ where 约束条件 group by 分组依据 having 过滤条件 order by 排序的字段 limit 限制显示的条数 ]; 注： 1.查表中所有字段用*表示 2.条件的书写规则严格按照语法顺序书写，可以缺省，但不可以错序 3.约束条件的流程：from -> where -> group by -> having -> distinct -> order by -> limit 4.字段可以起别名 5.字段可以直接做运算 select age + 1 ‘new_age‘ from emp; 6.分组后的条件均可以使用聚合函数 ‘‘‘ ‘‘‘ 3. def from(): return "查询的文件" def where(file): return "条件筛选后的结果" def group_by(res): return "分组后的结果" def having(res): return "再次过滤后的结果" def distinct(res): return "去重后的结果" def order_by(res): return "排序后的结果" def limit(res): return "限制条数后的结果" def select(from=from,where=null,limit=null): file = from() res = where(file) if where else file res = group_by(res) if group_by else res ... res = limit(res) if limit else res return res select(where=where,group_by=group_by)

单表数据

CREATE TABLE `emp` ( `id` int(0) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,`gender` enum(‘男‘,‘女‘,‘未知‘) NULL DEFAULT ‘未知‘,`age` int(0) NULL DEFAULT 0,`salary` float NULL DEFAULT 0,`area` varchar(20) NULL DEFAULT ‘中国‘,`port` varchar(20) DEFAULT ‘未知‘,`dep` varchar(20),PRIMARY KEY (`id`) ); INSERT INTO `emp` VALUES (1,‘yangsir‘,‘男‘,42,10.5,‘上海‘,‘浦东‘,‘教职部‘),‘engo‘,38,9.4,‘山东‘,‘济南‘,‘教学部‘),(3,‘jerry‘,30,3.0,‘江苏‘,‘张家港‘,(4,‘tank‘,28,2.4,‘广州‘,‘广东‘,(5,‘jiboy‘,‘苏州‘,(6,‘zero‘,8.8,‘中国‘,‘黄浦‘,‘咨询部‘),(7,‘owen‘,‘安徽‘,‘宣城‘,(9,‘ying‘,36,1.2,‘芜湖‘,(10,‘kevin‘,5.8,(11,‘monkey‘,‘青岛‘,(12,‘san‘,9.0,(13,‘san1‘,6.0,(14,‘san2‘,‘浦西‘,‘教学部‘);

常用函数

concat(字段1,字段n)：完成字段的拼接(形成一个临时表,可以用as起别名) concat_ws(x,字段n)：完成字段的拼接，x为连接符 lower()：小写 upper()：大写 ceil()：向上取整 floor()：向下取整 round()：四舍五入

简单查询

select concat(area,‘-‘,port) as ‘家乡‘ from emp; # 上海-浦东... 属于 起的别名 家乡 列 select concat_ws("-",name,area,port) ‘信息‘ from emp; # 以"-"字符拼接后面的所有字段 select upper(name) ‘name‘,gender,age from emp; # 可以指定多个字段 select name,ceil(salary),floor(salary),round(salary) from emp where name=‘kevin‘; # 数学函数 # 去重前提: 所查所有字段的综合结果完全相同,才认为是重复的,只保留重复中的一行数据 select distinct area from emp; select distinct area,port from emp;

where条件

1.比较运算符 = | < | > | <= | >= | != select * from emp where area!="上海";

2.区间运算符 between 10 and 20：10~20 in(10,30)：10或20或30 select * from emp where id between 3 and 5; # [3,5],闭合区间,包含3和5,三行数据 select * from emp where id in(2,4,6,8,10,12,14,16,18); # 分离的区间,14都会被显示 3.逻辑运算符 and | or | not select * from emp where area="山东" and port="济南"; 4.相似运算符 like ‘_owen%‘：模糊匹配字符串owen，_表示一个字符，%表示任意字符 # 匹配的字段为en,想得到的结果为owen select * from emp where name like ‘__en%‘; # 在en前可以出现2个任意字符,之后可以出现0或多个任意字符 ‘‘‘ # 需求： # 查找姓名有数字的员工信息

正则匹配

# why: like完成模糊匹配,但功能局限,可以模糊个数,但不能模糊类型,正则可以完成类型及个数的模糊匹配
‘‘‘
语法：字段 regexp ‘正则表达式‘ 注：只支持部分正则语法 ‘‘‘ # 完成需求: select * from emp where name regexp ‘.*[0-9]+.*‘;

group by 分组

分组：根据字段相同值形成不同的类别，不明确分组其实整个表就为一个默认大组
原因：把以值共性得到的类别作为考虑单位，不再关系单条记录，而且一组记录

结果：只能考虑组内多条数据的聚会结果（聚合函数结果），分组的字段同样是聚合结果，如：组内的最大最小值
需要把数据库模式修改成由group严格按照聚合结果取值的状态 sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 聚合函数： max()：最大值 min()：最小值 avg()：平均值 sum()：和 count()：记数 group_concat()：组内字段拼接，用来查看组内其他字段 eg:1 每个部门的平均薪资 select dep,avg(salary) ‘平均薪资‘ from emp group by dep; eg:2 每个部门都有哪些人(‘每‘这个字后面的内容就是group by的字段) select dep,group_concat(name) from emp group by dep; 需求： 各性别中附属于教学部的最高薪资 select max(salary) ‘最高薪资‘,gender from emp where dep=‘教学部‘ group by gender; 思考： 想知道需求中员工的姓名 => 子查询 上方结果: 男的最高薪资对应的人名,女的最高薪资对应的人名 # select group_concat(name),max(salary) ‘最高薪资‘,gender from emp where dep=‘教学部‘ group by gender; 错误,会显示出部门下,所有人的名字,而不是最高工资的人名 ‘‘‘ ‘‘‘ 1. 14条数据部门有3个,并且每个部分有多条记录,可以作为分组依据,同理,性别也可以 # select * from emp group by dep; # 非分组安全模式下,可以查询非聚合结果,显示的是第一条记录,没有意义,分组安全模式下不能查询非聚合结果的字段 select dep from emp group by dep; 2. 如果就像以姓名进行分组,可以,但没多大意义,原因name值基本上都不相同,以组考虑会导致组内大多只要一条记录(自成一组),组的利用就不是很强烈,此类分组是无意义的 select name from emp group by name; # 可以分组,意义不大 考虑的三个问题: 以什么分组(相同数据较多的字段) 分组后的考虑单位(组并非组内的每一条记录) 可以查询的结果(当前分组的字段及聚合函数形成的聚合结果)

子查询

子查询：将一条查询结果作为另外一条查询的条件
语法：一条select语句用()包裹得到的结果作为另一条select语句的条件 # 伪sql: select * from emp where salary =|in (select salary from emp where 条件) 单行子查询： 子查询语句的结果为一行数据，可以结合 = | < | > | <= | >= | != 运算符来完成父查询 select salary from emp where salary > 10; # => 作为子查询 # 查询姓名,性别.地区,基于薪资大于10的结果的查询结果 eg: 1 select name,area from emp where salary = (select salary from emp where salary > 10); 多行子查询： 子查询语句的结果为多行数据，可以结合 in | all | any 运算符来完成父查询 in：任意单一值，一次只能考虑子查询中的一个结果 all：全部值，将子查询结果作为一个整体考虑 any：任意多个值：子查询的每一个结果都可以作为参考依据 eg: 2 # 子查询的结果 (9.4,3) select name from emp where salary in (select max(salary) ‘最高薪资‘ from emp where dep=‘教学部‘ group by gender); # 遍历14条数据,14条数据的salary在(9.4,3)区域中,就可以完成匹配,结果为两条(9.4和3那两条) select * from emp where salary < all(select max(salary) ‘最高薪资‘ from emp where dep=‘教学部‘ group by gender); # 遍历14条数据,salary要小于(9.4,3)中的每一个,反映就是小于3,结果为薪资1.2,2.4的那四条数据 select * from emp where salary > any(select max(salary) ‘最高薪资‘ from emp where dep=‘教学部‘ group by gender); # 遍历14条数据,salary大于9.4或大于3的数据均满足条件,结果就是刨除小于等于3的那几条数据

having 筛选

why：完成在分组之后的筛选
注意：having条件是实现聚合结果层面上的筛选 => 拿聚合结果完成判断

需求：
1.各部门的平均薪资
select dep,avg(salary) ‘平均薪资‘ from emp group by dep; 2.平均薪资大于6w的部门(部门与部门的平均薪资) 解决: 以dep进行分组,以avg(salary)作为判断条件(筛选) select dep,avg(salary) ‘平均薪资‘ from emp group by dep having avg(salary) > 6; # 总结: having通过聚合函数结果完成筛选 select max(salary) from emp having max(salary) > 9.4; # 虽然没有明确书写group by,但在having中使用了聚合函数,所以该查询就将整个表当做一个默认大表来考虑,所以查询的字段只能为聚合函数的结果

order by 排序

why：完成排序
注意：可以使用聚合函数，哪怕没有明确group by 升序 | 降序：asc | desc eg：order by age desc => 按照年龄降序 select * from emp order by age desc; 需求： 将部门按照部门平均工资降序方式排序 select dep,avg(salary) from emp group by dep order by avg(salary) desc;

limit 限制

why：限制最终结果的数据行数
注意：limit只与数字结合使用

应用：
limit 1：只能显示一行数据
limit 6,5：从第6+1行开始显示5条数据(索引从0开始)

select * from emp limit 1; select * from emp limit 6,5; 需求: 获得薪资最高的人的一条信息 select * from emp order by salary desc limit 1;