MySQL数据操作管理——学习笔记

8.1插入数据

8.1.1为表的所有字段插入数据

insert into student values(‘0’,’小李’,’1班’);
Insert into student （sno,sname,ssex,sclass）values (‘1’,’李明’，’男’，’1班’);

注意：第一种比较简单，第二种可以不按照字段顺序插入，同时记录也要随之更改。

8.1.2为表指定字段插入字段

Insert into student(sno,sname) values (‘2’,’小红’);

没有赋值的字段，数据库系统会为其插入默认值，如NULL，否则会报错。

注意：可任意改变字段顺序。

8.1.3同时插入多条记录

Insert into student values(),(),();

注意：没有指定字段时则向所有字段插入数据，否则按指定字段来。

8.1.4从目标表中插入值

Insert into student 【列名列表】select 列名列表from 表名

注意：一个或多个表；返回结果和插入字段类型要保持一致。

8.1.5replace语句

Replace into student values();

注意：如果新记录的主键值或者唯一性的字段值与已有的记录相同，则已有的被删除后再添加新纪录。

8.2修改数据

Update student set sname=’李凯’,sbirth=’2000-08’ where sno=’1’;

注意：update语句会更新所有满足条件的记录，但在mysql中是需要一条一条执行的。

8.3删除数据

8.3.1使用delete删除表数据

Delete from student where sno=’1’;

注意：如果不加上where条件表达式，数据库系统会删除指定表中的所有数据。

8.3.2使用truncate清空数据

Truncate table student；

Truncate、delete、drop的区别：

• Truncate table：删除内容、释放空间、不删除定义
• Delete table：删除内容、不释放空间、不删除定义
• Drop table：删除内容、释放空间、删除定义

8.4单表查询

8.4.1简单查询

1、所有字段

select zno,zclass,sno,sname from student;
select * from student;

2、指定字段查询

Select sno,zname from student;

3、避免重复数据查询

Distinct 关键字可以去除重复的查询记录

Select distinct sclass from student;

4、为表和字段取别名

Select sno’学号’,grade’成绩’ from student;

8.4.2条件查询

Select sno,grade from sc where grade>90;
Select  *  from sc where grade>=70 and grade <=80;
Select  *  from sc where grade [not] in (66,87,98);
Select  *  from sc where [not] between 75 and 80;
Select sno,grade from sc where zno is null;(查询是否为空值)
带like关键字查询 [not] like ‘字符串’

注意：字符串必须要加单引号或者双引号，字符串除了是一个完整的字符串之外，还可以是百分号%或者下划线_的通配符

• “%”代表任意长度的字符串，长度可以为0

• “_”只能是单个字符，例如B_，就是以B开头的两个字符
• regexp关键字匹配正则表达式

Select * from student where sname like ‘蓝莓’;

Select * from student where sname like ‘李%’;

Select * from student where sname like ‘李__’;

Select * from student where sname not like ‘李__’;

注意：当字符串本身含有通配符“%”,“_”时，那么使用“\_”表示“_”，用escape ’’ 表示“”。

8.4.3高级查询

1、分组查询 group by

Select * from student group by ssex;

往往加上“having”条件表达式，限制输出结果。

Select ssex,count(ssex) from student group by ssex having count(ssex)>=10;

注意：having条件表达式与where条件表达式都是用来限制显示的，但是where作用于表和视图，having作用于分组后的记录。

2、对查询结果进行排序

order by [ASC | DESC]

ASC为升序（1,2,3），DESC为降序（3,2,1,）默认ASC；

Select * from student order by zno,ASC,sno DESC;

3、限制结果数量

Select * from student order by sno limit 2,3;第3条记录显示3条

4、聚合函数

Select count(*) as ‘学生总人数’ from student;

Select zno,count(*) as ‘专业人数’ from student group by zno;

Select sno,sum(grade) from student where sno=’1’;

Select cno,avg(grade) as ‘平均成绩’ from student group by cno;

Select sno,cno,max(grade) from sc group by cno;

Select sno,cno,min(grade) from sc group by cno;

注意：以ascii表比较大小，a最小，z最大。

5、合并查询结果

查询女生的信息或者出生日期“**”以后出生的学生信息

Select * from student where ssex=’女’ 

union select * from student where sbirth>’1997-01-08’

8.5多表查询

多表数据记录连接查询又称连接查询，连接查询又分为内连接查询和外连接查询；主要区别在于，内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。

8.5.1内连接查询

1、等值连接

Select * from sc inner join course on sc.no=course.no limit 4;

2、自然连接（可理解等值连接中吧重复的属性列去掉则为自然连接）

Select *from sc natural join course limit 4;

3、不等值连接

Select * from sc inner join course on sc.no!=course.no limit 4;

8.5.2外连接查询

1、左外连接

select course.no,course.cname from course left join sc on course.cno=sc.cno limit 10;

2、右外连接

select course.no,course.cname from course right join sc on course.cno=sc.cno limit 10;

8.5.3子查询

1、有时候需要的条件是另一个select语句的结果

Select * from student where sno IN (select sno from sc);

2、如果有金融这个专业就显示出课程信息

Select * from course where EXISTS(select * from specialty where zname=’金融’);

3、查询比其他班级某一个同学年龄小的学生的姓名和年龄

Select sname,sbirth from student where sbirth>ANY(select sbirth from student where sclass=’计算102’);

4、查询比其他班级所有同学年龄大的学生的姓名和年龄

Select sname,sbirth from student where sbirth<ALL(select sbirth from student where sclass=’计算102’);