MySQL—增删改查,分组,连表,limit,union,alter,排序,去重

MySQL增删改查

在表格的增删改查中,查的内容是最多的,包括group by ,join,limit,union,alter,排序都是服务于查的

#sql语句数据行操作补充

#增加:
#insert into table_name(字段1,字段2) values('属性1','属性2'),('属性1','属性2') 插入多行数据
#insert into t1(字段1,字段2) select 字段1,字段2 from t2; (某张表的某几列数据选择出来,插入) #删除:delete from table_name [where id =/!=/>/< /<> 2] and/or name='啥啥啥'
#truncate table t1; #改: update t1 set name='gkx' ,age=19 where id>12 and name='x' #可以同时改多个字段
'''
#查:内容较多:
# 查所有,查部分字段,后面跟 where条件,查完后排序
#select id,name from t1 where id>10 and name = 'aaa'
#select id as setid ,name as setname from t1 where id>10 and name = 'aaa' # as 别名
#as 别名
#select name as cname ,age,1 from t1;
#这里的 1表示 增加一列,列名为1,该列所有行属性也都为1
#select * from t1 where id in (1,2,5); #如果id在 1,2,5就选择出来
#select * from t1 where id not in (1,2,5); #id除了1,2,5其他都选择出来
#select * from t1 where id in (select id from t2 where id>2) ;# 根据选择来选择
#select * from t1 where id between 5 and 12;
#闭区间 5和12也能取到
#select * from dmsxb where b_dm in (select b_dm from dmsxb group by b_dm having count(*)>1) ##还可以选择查询语句,不过这个语句结果需要为一个常量 select age,name,(select count(1) from tb) from tb1;
#习题17和21 #条件 ## case when 条件 then 【字段或者默认值】 else 字段 END
#select course_id,max(num),min(num),cname,
# case when min(num) < 10 THEN 0 else min(num) end from score left join course on score.course_id = course.cid group by course_id;
#if 三元运算
# select course_id,cname,tname,avg(if(isnull(score.num),0,score.num)) from score #通配符:
#下划线 : 表示后边只能匹配一位,select * from tb12 where name like "a_"
#百分号 : select * from tb12 where name like "a%"
''' #limit:比如百度搜索,一次只显示一页,放置C/S端压力
#select *from t1 limit 10; #显示前十条 相当于select *from t1 limit 0,10;
#select * from t1 limit 4,5; #从第4行开始的5行 (不包含第4行)
#select * from t1 limit 5 offset 4 #从第4行开始的五行
#实现分页:
#page = int(input('page')) select * from limit (page-1)*10 , 10; 前者为起始位置
#select * from t1 order by id desc limit 10; (后10行:先降序排列,再选前十行)
'''
limit 数值越大越慢,比如 limit 300000,10 它会先扫描30万行,然后再取下10行
''' #排序:
#select * from t1; #默认是从小到大排 即 asc ----> ascending
#select * from t1 order by 列名 desc; 即 desc ----> descending
#select * from t1 order by 列1 desc,列2 asc ; #根据列1从大到小排列,如果相同按列2从小到大排列

增删改查,limit,order by

# Mysql增加主键或者更改表的列为主键的sql语句

# 添加表字段
# alter table table1 add transactor varchar(10) not Null;
# alter table table1 add id int unsigned not Null auto_increment primary key # 修改某个表的字段类型及指定为空或非空
# alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
# alter table 表名称 modify 字段名称 字段类型 [是否允许非空]; # 修改某个表的字段名称及指定为空或非空
# alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空 # 删除某一字段
# ALTER TABLE mytable DROP 字段 名;
#
# 添加唯一键
# ALTER TABLE `test2` ADD UNIQUE ( `userid`) # 修改主键
# ALTER TABLE `test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )
#
# 增加索引
# ALTER TABLE `test2` ADD INDEX ( `id` ) #修改字段类型,并设置主键
# ALTER TABLE `category ` MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`id`); #建表后创建唯一索引:
# create unique index UK_student_name on student (name);
# 建表后添加约束
# alter table student add constraint uk_student_name unique (name); #修改表的列属性
# alter table 表名 change 原列名 新列名 类型;  --修改表的列属性名
# alter table 表名 modify 列名 类型 ;   --修改表的类类型
# alter table 表名 drop 列名;   --删除表的某一列
# alter table 表名 add  列名 类型; --添加某一列
# alter table 表名 rename 新表名;  --修改表名
''' #1. select now(); 打印当前时间 #2. desc table_name; 查看字段的属性
#3. show create table table_name ; 查看表的创建代码
#3. show create table table_name \G ; 竖着看,查看表的创建代码

ALTER命令

分组的实现,注意事项,总结

#分组 group by:
#通过group by 按照所选列分组显示,按所选列分组,所选列如果 不重复 的项有4个,就分成4组
# select part_id from t1 group by part_id;
#错误:select part_id,name from t1 group by part_id; #这是不行的,因为它按part_id分组,聚合在一起,此时不知道 相同part_id但是不同name的,该如何取舍哪个name所以会报错
#可以这么写:
# select part_id,max(id) from t1 group by part_id;
#当part_id 分组完后,留下id列中,相同part_id里,id最大的那行
# select part_id,min(id) from t1 group by part_id; #结合count等聚合函数使用: 可以用as改名哦!!!
#select count(id),part_id from t1 group by part_id; 用part_di分组,新增一列count(id),计算每组id数
#类似还有 count,max,min,sum,avg(比如avg(分数),取每个分组中,分数这个列的平均值i)
# select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid #如果对于聚合函数结果进行二次筛选时,必须使用having!
#select num from 表 group by num having max(id) > 10
#选择出最大id大于10的,按num分组的,所有分组
#加条件: select part_id from user where id !=1 group by part_id having count(*)>1; #··特别的:group by 必须在where之后,order by之前
#select num,nid from 表 where nid > 10 group by num,nid order nid desc #group 多列, 用两列做分组,
#select num,nid from 表 where nid > 10 group by num,nid order nid desc
#选出nid大于10的,num,nid两列分组后的 num,nid。并用nid排序
#比如:姓名和部门,用姓名和部门进行分组,选出年龄大于30的,同姓名同部门的所有用户 #总结:
#group by 是用来分组的,把某一列都聚合到同一组,所以select出的字段要不就是这一列,要不要使用函数,要不和分组字段是唯一对应关系的
#聚合函数有:(不能用where)
#count(列名) max(列名) min(列名) sum(列名) avg(列名)
#可以用as改名哦,select count(id) as count_id,part_id from user group by part_id having count(*)>1;
#对于聚合函数的条件筛选不能用where,要用 having
#group by可以多列进行联合分组
#group by要写在where 后面,order by 前面 # -- 7、查询学过“1”课程并且也学过编号“2”课程的同学的学号、姓名;
# -- score和student连表,找出所有选过1,2课程的学号,并且按学号分组
# select student.sid,student.sname from score left join student on score.student_id = student.sid where course_id in (1,2) group by student_id;
#注意在这个例子中 学号和姓名是唯一对应的,所以可以直接选姓名,而不用聚合函数

分组—group by

连表操作

'''
连表最好是外键中的子表父表连
连表是支持 不同数据类型之间连接的,但是这样子操作没意义
'''
#连表:
#两张表没关系,则不显示:
# select A.num, A.name, B.name from A,B Where A.nid = B.nid #建议不要用这种,不直观
# select A.num, A.name, B.name from A inner join B on A.nid = B.nid #其实只要记一个left join就好,把表换位置就好啦
# left join(左联接) A表所有显示,如果B中无对应关系,则值为null
#语法:select * from A left join B on A.aID = B.bID
#展示左边表所有的值,依据条件连接右边表, 右边表有,左边表没有的值,将在结果中右边置为空 # right join(右联接) B表所有显示,如果B中无对应关系,则值为null
#语法:select * from A right join B on A.aID = B.bID
#展示右边表所有的,依据条件连接左边表, 左边表有,右边表没有的值,将在结果中左边置为空 # inner join(等值连接) 只返回两个表中联结字段相等的行
#语法:select * from A inner join B on A.aID = B.bID
#只展示两边表有的值,没有的值不设置null,直接不显示
#···无对应关系则不显示!!!!
#三者区别 : https://www.cnblogs.com/pcjim/articles/799302.html #连多张表: 图在:20181007 mysql连表
#select * from 表A left join 表B on A.ID = B.ID
# left join 表C on A.ID = C.ID
# left join 表D on A.ID = D.ID
# select 的时候,字段可以 表示为 表名.字段名
# 当表连接后,接下俩的join 就可以随意使用 #举例:
# -- 20、课程平均分从高到低显示(显示任课老师);
# select course_id,cname,tname,avg(num) from score
# left join course on score.course_id = course.cid
# left join teacher on course.teacher_id = teacher.tid
# group by course_id order by avg(num) desc;

连表操作

#组合
# 组合,自动处理重合
# select nickname from A union select name from B
#
# 组合,不处理重合
# select nickname from A union all select name from B

组合

临时表,distinct去重以及和group by的区别

#临时表
# (select * from t1 where id > 2) as 临时表名
#用法:
# select sid from (select * from score where num>60) as b;

临时表

# https://blog.csdn.net/ljl890705/article/details/70602442
#distinct简单来说就是用来去重的,而group by的设计目的则是用来聚合统计的,两者在能够实现的功能上有些相同之处,但应该仔细区分,因为用错场景的话,效率相差可以倍计。
# ·····单纯的去重操作使用distinct,速度是快于group by的。
#
# distinct (说是效率不高)
# distinct支持单列、多列的去重方式。
# 单列去重的方式简明易懂,即相同值只保留1个。
# 多列的去重则是根据指定的去重的列信息来进行,即只有所有指定的列信息都相同,才会被认为是重复的信息。 #单列去重:
#select distinct(mobile) from talk_test; # 多列去重:
# mysql> select distinct name,mobile from talk_test; #结合count使用:
# select count(distinct col) from A;
# select count(1) from (select 1 from A group by col) alias; # 两个极端:
# 1.数据列的所有数据都一样,即去重计数的结果为1时,用distinct最佳
# 2.如果数据列唯一,没有相同数值,用group 最好

distinct去重

上一篇:kali视频(1-5)


下一篇:阿里云API网关(4)快速入门(开放 API)