沈师PTA 数据库

单表查询

---

理论

 orderbyfield()
 按照自定义的要求排序
 field(value,str1,str2,str3,str4)，value与str1、str2、str3、str4比较，返回1、2、3、4，如遇到null或者不在列表中的数据则返回0.
 ORDERBY name desc,age asc    (不同条件排序规则，先按名字降序排，再按年龄升序排)

​

 LEFT()函数
 LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分。
 LEFT()函数接受两个参数：
 str是要提取子字符串的字符串。length是一个正整数，指定将从左边返回的字符数。

​

​

 查询姓王的读者的账号、姓名和性别，要求使用left函数
 select 账号  , 姓名 , 性别
 from 读者
 whereLEFT(姓名 ,1)like'王';

case !!

​

 1.简单Case函数
 CASE sex
 WHEN '1' THEN '男'
 WHEN '2' THEN '女'
 ELSE '其他' END
 ​
 ​
 2.Case搜索函数
 CASE WHEN sex = '1' THEN '男' 
 WHEN sex = '2' THEN '女' 
 ELSE '其他' END  
 ​
 SELECT  SUM(population), 
 CASE country  WHEN '中国' THEN '亚洲' 
   WHEN '印度' THEN '亚洲' 
   WHEN '日本' THEN '亚洲' 
   WHEN '美国' THEN '北美洲' 
   WHEN '加拿大'  THEN '北美洲' 
   WHEN '墨西哥'  THEN '北美洲' 
 ELSE '其他' END 
 FROM    Table_A 
 GROUP BY
 CASE country 
 WHEN '中国' THEN '亚洲' 
 WHEN '印度' THEN '亚洲'
 WHEN '日本' THEN '亚洲' 
 WHEN '美国' THEN '北美洲' 
 WHEN '加拿大'   THEN '北美洲' 
 WHEN '墨西哥'   THEN '北美洲' 
 ELSE '其他' END; 
 ​
 ------------------------------------
 order by
      case when status=1 then 0
              when status=0 then 1
              when status=4 then 2
              when status=3 then 3
              when status=2 then 4 ,
     add_time desc
 此句sql的意思是： 先根据用户状态排序：状态为1的排在最前面  >其次是状态为0  >状态为4  >状态为3  >状态为2 ; 再根据添加时间降序排序

查询图书的条形码，书名，出版社和出版日期，要求结果按出版社升序排列，出版社相同的数据按出版日期降序排列sql

 select 条形码 ,书名, 出版社, 出版日期
 from 图书
 order by 出版社 ,  出版日期 desc;--------按出版社升序排列，出版社相同的数据按出版日期降序排列

查询2018年以后出版的图书的全部信息

用year()函数获取年份

​

 select*
 from 图书
 whereYEAR(出版日期)>=2018;

查询年龄18-20之间的学生信息

 select sno as  学号  , sname as 姓名 , sex as 性别  , mno as 专业 ,(YEAR('2020-03-01')-YEAR(birdate))as 年龄 , memo as 备注
 from stu
 where(YEAR('2020-03-01')-YEAR(birdate))between18and20;

数据更新

---

理论

 语法 :
 insertinto 表名(列名1 , 列名2 , 列名3 ....)values(数据1 , 数据2 , 数据3 ....);
 ​
 insertinto 表名 values(数据 ....) 前提是插入的是表中的全部列时才可以使用

​

ps:

如果插入一条数据，末尾可以不写分号；如果多条数据，每条语句后面必须写分号

数据添加的时候,除了数值类型,全部要加单引号

新增订单统计信息

 insertinto results
 (select CustomerID  ,count(OrderID)from orders groupby
 CustomerID )

批量插入学生记录

插入从某表中选出的子表
insert into softstu 
(select sno , sname from stu where mno = '02')

添加学生成绩记录

insert into sc values ('S012' , 'C001'  , 90);
insert into sc valuse ('S012' , 'C002' , null);

理论

delete from 表名 where 查询条件

删除选修C语言课程的女生成绩记录

delete from sc
where sno in (select sno from stu where sex = 0)
and cno in (select cno from cou where cname = 'C语言')

删除成绩为空的学生选课记录

delete from sc
where grade is null 

删除没有销售过的产品

delete  from product 
where pid not in (select pid from orders)

理论

UPDATE <表名> SET 字段 1 = 值 1 [,字段 2=值 2… ]
[WHERE 子句 ]
[ORDER BY 子句] 
[LIMIT 子句]

R10-26 修改女生成绩

update stu
set grade  = grade * 1.05
where grade < 75 and sno in (select sno from stu where sex = 0)

修改学生数据表

涉及到相关子查询 , 查询每个学生的平均成绩 , 要和外面的表中的数据连接 ,传递条件才可以找到自己的
update student 
set avg_grade  = (select avg(grade)  from score  where student.sno = score.sno )

修改杰克的购物记录

update recorder
set quantity  = 1 
where cid  in (select cid from customer where cname = '杰克') and gid = 'G006'

把选修了“平板撑”课程而成绩不及格的学生的成绩全改为空值(NULL)

update sc
set score = null
where score < 60 and cno in (select cno from course where cname = '平板撑')