MySQL 学习记录- 数据行 基本操作 Day17

数据行

（1）增

insert into t1(name,gender,age) values('young','男',26),('peppa','女',28)
insert into t1(name,gender,age) select name,gender,age from t2
指定字段名插入&不指定字段名插入：
insert into 表名(字段名1[,字段名2,...]) values(字段值 1[,字段值 2,...]);
insert into 表名 values(字段值 1[,字段值 2,...]);

（2）删

delete from tb12;
delete from tb12 where id !=2 
delete from tb12 where id =2 
delete from tb12 where id > 2 
delete from tb12 where id >=2 
delete from tb12 where id >=2 or name='alex'

delete和truncate的区别：
①delete可以添加where子句删除表中部分数据， truncate只能删除表中全部数据
②delete删除表中数据保留表结构， truncate直接把表删除(drop table)然后再创建一张新表(create table)，执行速度比delete快。

delete from emp;
truncate table emp;

（3）改

update tb12 set name='peppa',age=28 where id>12 and name='young'

（4）查

select id,name from tb12 where id > 10 or name ='young';#查询指定字段	
select id,name as cname from tb12 where id > 10 or name ='young';#name起别名为cname

#where 筛选
select * from tb12 where id != 1;#where条件筛选
select * from tb12 where id in (1,5,12);#只要满足条件范围内的一个值即为匹配项
select * from tb12 where id not in (1,5,12);#用来检索不在条件范围内的记录
select * from tb12 where id in (select id from tb11);#子查询充当条件范围
select * from tb12 where id between 5 and 12;#表示指定范围内的值				
select * from tb12 where id not between 5 and 12;#表示指定范围外的值
select * from tb12 where name like "a%";#通配符%表示任意长度的字符
select * from tb12 where name like "a_";#通配符_表示一个字符
select * from tb12 where name is null;#查询空值

#limit 限制查询
select * from tb12 limit 10;#查询前十条记录
select * from tb12 limit 0,10;#查询前十条记录，与上面的效果一样
select * from tb12 limit 10,10；#查询第11-20条记录
select * from tb12 limit 2,1;#查询第三条记录

#order by 排序
select * from tb12 order by id asc;#小到大
select * from tb12 order by id desc;#大到小
select * from tb12 order by age desc,id asc;#先按年龄降序排，遇到相同再按id升序

#group by 分组
select course_id,sum(number),avg(number),count(1) from score group by course_id;
						#按照学科分组求总分，平均分，选课人数
select course_id,sum(number),avg(number),count(1) from score group by course_id having avg(number)>=80;		#按照学科分组求平均分大于80分的科目的总分，平均分，选课人数
#**** 如果对于聚合函数结果进行二次筛选时必须使用having ****
select student_id,group_concat(course_id) from score group by student_id;
						#按照学生分组，并把学生所选的课成全部显示出来
select student_id,count(course_id) from score group by student_id with rollup;
						#按照学生分组，并在最后一列显示所有学生所选学科数目总和


#left join...on... 连表
select * from score left join student on score.student_id=student.sid.
#score表与student表根据score表的student_id=student表的sid关系进行连接

select * from score right join student on score.student_id=student.sid.
#当用right join时，student右边全部显示，left join则相反

select * from score inner join student on score.student_id=student.sid.
#inner join的作用就是将出现null值的哪一行隐藏掉

（5）导出数据

select * from emp where deptno>15 and sal>1500
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/t.csv' 
fields terminated by ','; 

解析：第一行：查询结果
第二行：将查询结果导出至指定的安全路径安全的意思？
第三行：以逗号作为分隔符
注意：路径中不能有中文，’\‘在编程语言中是转义符，因此要改为’'或‘/’
PS：按住Shift右键文件，可以直接复制文件的路径，另外在mysql workbench 中可以用Ctrl+H快捷键来实现查找替换。