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从小到大排列
# 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 ; 竖着看，查看表的创建代码
分组的实现，注意事项，总结
#分组 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; #注意在这个例子中 学号和姓名是唯一对应的，所以可以直接选姓名，而不用聚合函数
连表操作
''' 连表最好是外键中的子表父表连 连表是支持 不同数据类型之间连接的,但是这样子操作没意义 ''' #连表： #两张表没关系，则不显示： # 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 最好