DML语句

insert使用

• 先建一个表

create table students(
    scode int not null auto_increment,
    sname varchar(20) not null,
    saddress varchar(20) default‘未知’,
    sgrade int,
    semail varchar(20),
    ssex int,
    primary key(scode)
);

• insert 语法

  • 插入单行
    • INSERT [INTO] <表名> [列名] VALUES <值列表>
  • 插入多行
    • INSERT INTO <表名>(列名)
      VALUES(<列名值>), (<列名值>), (<列名值>)

• 注意点

  1. 每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；
  2. 每个数据值的数据类型、精度和小数位数必须与相应的列匹配；
  3. 如果在设计表的时候就指定了某列不允许为空，则必须插入数据；
  4. 具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值

  insert students(sname,sgrade,ssex)
  values('测试女生1',75,0),
  ('测试女生2',77,0),
  ('测试女生3',83,0),
  ('测试男生1',81,1),
  ('测试女生4',90,0),
  ('测试男生2',94,1),
  ('测试女生5',51,0),
  ('测试男生3',53,1);
  

  Paste_Image.png

update使用

• 使用范例
  • UPDATE <表名> SET <列名 = 更新值>
    [WHERE <更新条件>]

  update students set semail='@qq.com';
  

  Paste_Image.png

   update students set semail='@163.com' where sname='测试女生1';
  

  Paste_Image.png

delete使用

• 使用范例
  • DELETE FROM <表名> [WHERE <删除条件>]
  • 此语句删除表中的行，如果不带where子句，则删除整个表中的记录，但是表不被删除。

select查询

• select 各类实用
  • select * from XXX(students)
    • 查询产生一个虚拟表，看到的是表表现形式的结果，产生的结果并不真正存储，而是从表中查询数据并表现出来。

  • select address from students;

    
    Paste_Image.png

  • select distinct address from students;(去重)

    
    Paste_Image.png
• 查询记录操作
  • from子句：指定查询数据的表
  • where子句：查询数据的过滤条件
  • group by子句：对匹配where子句的查询结果进行分组
  • having子句：对分组后的结果进行条件限制
  • order by子句：对查询结果结果进行排序，后面跟desc降序或asc升序（默认）。

  select * from students order by sgrade asc;//desc降序
  

  Paste_Image.png
  • limit子句：对查询的显示结果限制数目

  select * from students limit 2,5;
  

  Paste_Image.png

  select * from students limit 5;
  

  Paste_Image.png
  • procedure子句：查询存储过程返回的结果集数据
  • 查询中文姓名

  sleect sname as '姓名' from srudents;
  

  Paste_Image.png
  • 查询年龄最大的前3个学生的姓名和年龄，或第4、5个学生

  select sname,sage from student order by sage desc limit 3;或（limit 3,2）
  

  • 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。

  select sname,'year of birth: ',2008-sage, lower(sdept) from 
  student;
  

集函数

• SQL提供的统计函数被称为集函数

• 主要的集函数：

  • 记数函数： count(列名) 计算元素的个数

  select count(sgrade) from students;
  

  Paste_Image.png
  • 求和函数： sum(列名) 对某一列的值求和，但属性必须是整型

  select sum(grade) from students;
  

  Paste_Image.png
  • 计算平均值：avg(列名)对某一列的值计算平均值
  • 求最大值： max(列名) 找出某一列的最大值
  • 求最小值： min(列名) 找出某一列的最小值

  select sum(sgrade),sname from students group by sname;
  

  Paste_Image.png
  Paste_Image.png

• 注意点：where不能用集函数,使用having代替

  select avg(sgrade),sname from students group by sname having avg(sgrade)>59;
  

  Paste_Image.png

• 案例

  • 查询学生总数。

  select count(*) from student;
  

  • 查询选修了课程的学生人数。

  select count(distinct studentid) from sc;
  

  • 查询1号课程的学生平均成绩。

  select avg(grade) from sc where courseid=1;
  

  • 查询1号课程的学生最高分和最低分。

  select max(grade) as ‘最高分’,min(grade) as ‘最低分’ from sc where courseid=1;
  

  • 查询每个学生的平均成绩。

  select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid;
  

  • 查询学生的平均成绩在70分以上的。

  select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid having avg(grade)>70;
  

  • 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 。

  select sname,sdept,sage from student where sage between 20 and 23;
  

  • 查询年龄不在20~23岁之间的学生姓名、系别和年龄。

  select sname,sdept,sage from student where sage not between 20 and 23;
  

  • 查询'信息系'、'美术系'和'计算机系'学生的姓名和性别。

  select sname,ssex from student where sdept  in (‘信息系',‘美术系',‘计算机系');
  

  • 查询学号为95001的学生的详细情况。

  select * from student where sno like '95001'；
  等价于：select * from student where sno='95001'; 
  

  • 查询所有姓刘学生的姓名、学号和性别。

  select sname,sno,ssex from student where sname like ‘刘%';
  

  • 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

  select studentid,courseid from sc                           where grade is null;
  

  • 查所有有成绩的学生学号和课程号。

  select studentid,courseid from sc                                where grade is not null;
  

  • 查询计算机系年龄在20岁以下的学生姓名。

  select sname from student where sdept=‘计算机系' and sage<20;
  

  • 查询信息系、美术系和计算机系学生的姓名和性别

  select sname,ssex from student where sdept in (‘信息系','美术','计算机系');
  可改写为：
  Select sname,ssex from student where sdept='信息系' or sdept='美术' or sdept='计算机系';
  

  • 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

  select studentid,grade from sc where courseid=3 orber by grade desc;
  

  • 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

  select * from student order by sdept,sage desc; 
  

多表查询

• 连接查询
  • 同时设计多个表的查询称为连接查询
  • 同来连接两个表的条件称为连接条件

  create table students(sname char(20),sno int);
  creat table score(studentSid int,courseId int,grade int);
  insert into students values('mcf',1),('cxg',2),('lcf',3),('qlf',4);
  insert into score values(1,1,97),(2,1,89),(2,2,67),(3,2,76),(3,3,90);
  select students.sname,score.courseId,score.grade from students inner join score on students.sno=score.studentSid;
  //select students.sname,score.courseId,score.grade from students,score where students.sno=score.studentSid;
  

  Paste_Image.png

  select students.sname,score.courseId,score.grade from students inner join score on students.sno!=score.studentSid;
  

  Paste_Image.png
• 外连接
  • 内连接中不满足连接主体的也一并输出
  • 分为左右连接，实际上是相同的。

  select students.sname,score.courseId,score.grade from students left join score on students.sno=score.studentSid;
  

  Paste_Image.png
• 子查询
  • 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为子查询。一个SELECT-FROM-WHERE语句称为一个查询块

  select sname from student where sno in (select studentid from sc where courseid=2);
  

• 创建空副本

  create table xxx select sname from students;
  

  Paste_Image.png