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

时间：2021-06-08 23:28:43

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去重

相关文章