一,字段下的数据操作

1.插入数据

insert into

insert into 表名(字段名) values(字段对应的值)

2,修改数据

update

update 表名 set 新字段名=[ ] where [条件]

3.删除数据

delete from

delete from 表名 where [ 删除条件 ]
truncate table 表名//清空表名

4.面试题:delete 和 truncate 清空数据的不同??

delete from 表名—— 删除表(一条一条删除数据,自增序列不变)
#delete from 表名 where 条件—-删除满足筛选条件的数据
#truncate table 表名—-删除表(直接drop删除整个表,然后重新创建,自增序列重置),数据量大的时候,truncate效率更高

5.结合练习

  1. #创建数据库名字为bdqn,使用该数据库
  2. CREATE DATABASE bdqn;
  3. USE bdqn;
  4. #创建表，表名student1,字段id自增长，name varchar(20)
  5. DROP TABLE student1;
  6. CREATE TABLE student1(
  7. id INT(18) NOT NULL PRIMARY KEY  COMMENT'学号',
  8. `name` VARCHAR(20) NOT NULL COMMENT'姓名'
  9. )AUTO_INCREMENT=1 COMMENT'学生表';
  10. #添加字段 age 类型为int
  11. #alter table student1 drop age;
  12. ALTER TABLE student1 ADD age INT(18) COMMENT'年龄';
  13. #修改表字段name  修改为stuName 类型为varchar(50)
  14. ALTER TABLE student1 CHANGE `name` stuName VARCHAR(50);
  15. #修改表名为student
  16. ALTER TABLE student1 RENAME student;
  17. #添加数据(张三，18)(李四，20)(王五，45)
  18. INSERT INTO student(id,stuName,age)VALUES(1,'张三',18),(2,'李四',20),(3,'王五',45);
  19. #修改数据，将张三修改为16岁
  20. UPDATE student SET age=16 WHERE stuName='张三';
  21. #删除张三这条数据
  22. DELETE FROM student WHERE stuName='张三';
  23. #修改数据，将李四和王五的年龄修改为30岁
  24. UPDATE student SET age=30 WHERE stuName='李四' OR stuName='王五';
  25. #添加数据(小明,25)
  26. INSERT INTO student(id,stuName,age)VALUES(4,'小明',25);
  27. #删除王五和小明两条数据
  28. DELETE FROM student WHERE stuName='王五' OR stuName='小明';
  29. #清空student表数据(truncate)删除    
  30. TRUNCATE TABLE student;
  31. #删除student表
  32. DROP TABLE student;
  33. #删除数据库bdqn
  34. DROP DATABASE bdqn;
  1. #插入数据 insert into
  2. INSERT INTO grade(gradeID,gradeName) VALUES(1,'大一');
  3. #插入多条数据
  4. INSERT INTO grade(gradeID,gradeName) 
  5. VALUES(2,'大二'),(3,'大三'),(4,'大四');
  7. #向student表添加数据
  8. INSERT INTO student(studentNo,studentName,sex,gradeID,phone,address,email,identityCard,loginPwd) 
  9. VALUES(1011,'郭靖','男',1,'13500000001','北京海淀区中关村大街1号','guojing@bdqn.cn','450323198612111000','123456'),
  10. (1012,'李文才','男',2,'13500000002','河南洛阳','liwencai@bdqn.cn','450323198112311000','1234567'),
  11. (1013,'李梅','女',3,'13500000015','上海卢湾区','limei@bdqn.cn','450323198612311000','12345678');
  12. #向subject表添加数据
  13. INSERT INTO `subject`(subjectNo,subjectName,classHour,gradeID) VALUES(1,'高等数学-1',120,1),(2,'高等数学-2',110,2),(3,'高等数学-3',100,3),(4,'高等数学-4',130,4);
  15. #修改数据---学号1013的邮箱改为student1013@bdqn.cn
  16. UPDATE student SET email='student1013@bdqn.cn' WHERE studentNo=1013;
  17. UPDATE student SET email='student1013@bdqn.cn',loginPwd='000000' WHERE studentNo=1013;
  18. #update student set studentNo=1012 where studentNo=1013;1012被占用,报错,改成1014是可以的
  19. #subject表中课时>110且学号=1的,课时减少10
  20. UPDATE `subject` SET classHour=classHour-10 WHERE classHour>110 AND gradeID=1;
  22. #删除数据,选中DELETE FROM grade,删除表中所有数据;gradeID=1,表示删除年级编号为1的一整行
  23. DELETE FROM grade WHERE gradeID=1;
  24. TRUNCATE TABLE grade;
  25. #面试题:delete和truncate在清空表数据有什么不同??
  26. #delete from 表名---- 删除表(一条一条删除数据,自增序列不变)
  27. #delete from 表名 where 条件---删除满足筛选条件的数据
  28. #truncate table 表名---删除表(直接drop删除整个表,然后重新创建,自增序列重置),数据量大的时候,truncate效率更高

二,数据查询

image.png

1.select关键字

2,查询所有数据

select * from 表名;

3,查询指定字段的所有数据

select studentNo,studentName,phone from 表名;

4起别名

select stu.studentNo from 表名 as stu;——给表起别名
select stu.studentNo as ‘学号’ from 表名 as stu;——给字段起别名

5,计算后起别名

SELECT s.studentNo+1 AS ‘学号加一’ FROM student s;

6,#查询成绩表中的课程编号

SELECT r.subjectNo FROM result AS r;

7,#去重—-DISTINCT

SELECT DISTINCT r.subjectNo FROM result AS r;

8,#返回数据库版本号—后面不加表名

SELECT VERSION();

9,#简单计算—-后面不加表名

SELECT 100*2;

10,#课程升级—所有课程的学时均需加10,可省略as

SELECT su.subjectName ‘课程名称’,su.classHour+10 ‘新课时’ FROM subject su;

11#查询成绩表中80-90的成绩记录

SELECT FROM result r WHERE r.studentResult>=80 AND r.studentResult<=90;
SELECT FROM result r WHERE r.studentResult BETWEEN 80 AND 90;

12#查询学生表出生日期为null的数据

IS NULL,不为null,IS NOT NULL
SELECT FROM student s WHERE s.birthdy IS NULL;
SELECT FROM student s WHERE s.birthdy IS NOT NULL;

三,like模糊查询

1,查询学生表学生姓李的记录

(%是0-多个字符,返回两个人;_是单个字符,返回一个人)
SELECT * FROM student s WHERE s.studentName LIKE ‘李%’

2,#学生表姓李的并且名字是2个字的记录

SELECT * FROM student s WHERE s.studentName LIKE ‘李_’;

3,#学生表姓李的并且名字是3个字的记录

SELECT * FROM student s WHERE s.studentName LIKE ‘李__’;

4,#查询学生表地址有区关键字

SELECT * FROM student s WHERE s.address LIKE ‘%区%’;

5,OR和IN

查询课程表中课时为100,110,130时的所有数据 IN 包含在或者or
SELECT FROM subject s WHERE s.classHour=100 OR s.classHour=110 OR s.classHour=130;
SELECT FROM subject s WHERE s.classHour IN (100,110,130);
#查询成绩表中成绩为100或者90的记录
SELECT * FROM result r WHERE r.studentResult IN (100,90);

四,多表联合查询

  1. #内连接 inner join
  2. SELECT su.`subjectName`,g.`gradeName` FROM `subject` su INNER JOIN grade g ON su.`gradeID`=g.`gradeID`;
  3. #查询学生表的姓名和年级的名称
  4. SELECT stu.`studentName`,g.`gradeName` FROM student stu INNER JOIN grade g ON stu.`gradeID`=g.`gradeID`;
  5. UPDATE student SET gradeID =2 WHERE studentName='李梅';
  6. #内连接--等值连接
  7. SELECT stu.`studentName`,g.`gradeName` FROM student stu,grade g WHERE stu.`gradeID`=g.`gradeID`;
  8. #非等值连接  笛卡尔积(两个结果的乘积)
  9. SELECT stu.`studentName`,g.`gradeName` FROM student stu,grade g;
  10. #左外连接
  11. #查询姓名和成绩
  12. SELECT s.`studentName`,r.`studentResult` FROM student s LEFT JOIN result r ON s.`studentNo`=r.`studentNo`;#显示大聪明
  13. SELECT s.`studentName`,r.`studentResult` FROM student s INNER JOIN result r ON s.`studentNo`=r.`studentNo`;#不显示大聪明
  14. #面试题:对比inner join 和 left join的区别????
  15. INSERT INTO student (studentNo,studentName,sex,gradeID,loginPwd) VALUES(1014,'大聪明','男',4,'');
  16. #总结:内连接表示2个表所有的匹配项的返回结果
  17. #左外连接表示左表全部数据匹配上右表的 数据
  18. SELECT s.`studentName`,r.`studentResult` FROM student s RIGHT JOIN result r ON s.`studentNo`=r.`studentNo`;

面试题:

多表联合查询中内连接和外连接的区别?
首先,内连接包括等值连接和inner join ON;外连接包括左外连接和右外连接;
其次,内连是匹配所有项,左外连接是左表全部匹配右项,右外连接是右表全部匹配左项.

五,自连接查询

自连接查询—-把一张表看成两张表 运用的等值连接

  1. DROP TABLE category;
  2. CREATE TABLE category(
  3. categoryId INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,    
  4. pid INT(10) NOT NULL,
  5. categoryName VARCHAR(32) NOT NULL
  6. )COMMENT '分类表';
  8. INSERT INTO  category VALUES
  9. (2,1,"美术设计"),
  10. (3,1,"软件开发"),
  11. (4,3,"数据库基础"),
  12. (5,2,"Photoshop基础"),
  13. (6,2,"色彩搭配学"),
  14. (7,3,"PHP基础"),
  15. (8,3,"一起学JAVA");
  16. #自连接查询---把一张表看成两张表  运用的等值连接
  17. SELECT c1.`categoryName` '一级分类',c2.`categoryName` '二级分类' FROM category c1 , category c2 WHERE c1.`categoryId`=c2.`pid`;
  19. CREATE TABLE region(
  20. regionId INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  21. pid INT(10) NOT NULL,
  22. regionName VARCHAR(30) NOT NULL,
  23. PRIMARY KEY (regionId)
  24. )COMMENT'省市表';
  25. INSERT INTO  region VALUES
  26. (3,1,"辽宁省"),
  27. (4,1,"吉林省"),
  28. (5,3,"沈阳市"),
  29. (6,3,"大连市"),
  30. (7,4,"长春市"),
  31. (8,5,"和平区"),
  32. (9,6,"四平区");
  33. SELECT r1.`regionName`'一级',r2.`regionName`'二级' FROM region r1, region r2 WHERE r1.`regionId`=r2.`pid`;

六,综合练习

DISTINCT——-去重

  1. #查询学生成绩大于80分的学生姓名
  2. SELECT DISTINCT s.`studentName` FROM result r ,student s WHERE r.`studentNo`=s.`studentNo` AND r.`studentResult`>90;
  4. SELECT DISTINCT s.`studentName` FROM student s JOIN result r ON s.`studentNo`=r.`studentNo` WHERE r.`studentResult`>90;
  5. #查询姓李的学生的考试成绩(学生姓名、课程名称、成绩)
  6. SELECT s.`studentName`,su.`subjectName`,r.`studentResult`  FROM student s,`subject` su,result r  WHERE r.`subjectNo`=su.`subjectNo` AND r.`studentNo`=s.`studentNo` AND s.`studentName` LIKE '李%';
  9. #查询大二年级的学生成绩(学生姓名、学生成绩)
  10. SELECT s.`studentName`,r.`studentResult` FROM student s,grade g,result r WHERE s.`studentNo`=r.`studentNo` AND s.`gradeID`=g.`gradeID` AND g.`gradeName`='大二';
  13. #查询高等数学-1的考试成绩(学生姓名、学生成绩)
  14. SELECT s.`studentName`,r.`studentResult` FROM student s,result r ,`subject` su WHERE s.`studentNo`=r.`studentNo` AND su.`subjectNo`=r.`subjectNo` AND su.`subjectName`= '高等数学-1';

七,分页查询,子查询

order by默认升序ASC排序,降序要用DESC
limit 分页查询,limit 2代表查询前两条,limit 1,2代表从第二个开始到第四条
#子查询
SELECT *
FROM student s JOIN result r
ON s.studentNo=r.studentNo
WHERE r.subjectNo=(
SELECT su.subjectNo FROM subject su
WHERE su.subjectName=’高等数学-2’
)
AND r.studentResult>=80;
count()返回总条数,sum()和,max()最大值,min()最小值,avg()平均值
group by分组
having筛选条件,搭配group分组使用

SELECT COUNT(1) FROM student s
WHERE s.gradeID=(
SELECT g.gradeID FROM grade g WHERE g.gradeName=’大一’
);

  1. #查询高等数学-1的考试成绩,由高到低进行排序
  2. #order by默认ASC升序排序;DESC降序排序
  3. SELECT * FROM student s,result r ,`subject` su 
  4. WHERE s.`studentNo`=r.`studentNo` AND su.`subjectNo`=r.`subjectNo` 
  5. AND su.`subjectName`= '高等数学-2' ORDER BY r.`studentResult` DESC;
  7. #limit分页查询    后面一个x,返回表的前x条数据
  8. SELECT * FROM result LIMIT 1;
  9. SELECT * FROM result LIMIT 2;
  10. #limit分页查询    后面一个x一个y,从表第x后(不包含第x条)的y条数据
  11. SELECT * FROM result LIMIT 2,3;
  12. #查询高等数学-2的学生成绩,由高到低排序并分页查询前两条记录
  13. SELECT s.`studentNo`,s.`studentName`,su.`subjectName`,r.`studentResult`
  14. FROM student s,result r,`subject` su
  15. WHERE s.`studentNo`=r.`studentNo` AND su.`subjectNo`=r.`subjectNo`
  16. AND su.`subjectName`='高等数学-2' ORDER BY r.`studentResult` DESC  LIMIT 2;
  17. #查询高等数学-2的学生成绩,由高到低排序并分页查询2-3条记录
  18. SELECT s.`studentNo`,s.`studentName`,su.`subjectName`,r.`studentResult`
  19. FROM student s,result r,`subject` su
  20. WHERE s.`studentNo`=r.`studentNo` AND su.`subjectNo`=r.`subjectNo`
  21. AND su.`subjectName`='高等数学-2' ORDER BY r.`studentResult` DESC  LIMIT 1,2;
  23. SELECT  s.`studentNo`,s.`studentName`,su.`subjectName`,r.`studentResult`
  24. FROM student s,`subject` su,result r
  25. WHERE s.`studentNo`=r.`studentNo` AND su.`subjectNo`=r.`subjectNo`
  26. AND su.`subjectName`='高等数学-3' AND r.`studentResult`>=60 LIMIT 2;
  27. #查询课程为高等数学-2,且分数不小于80分的学生的学号和姓名
  28. #子查询
  29. SELECT *
  30. FROM student s JOIN result r
  31. ON s.`studentNo`=r.`studentNo`
  32. WHERE r.`subjectNo`=(
  33. SELECT su.`subjectNo` FROM `subject` su
  34. WHERE su.`subjectName`='高等数学-2'
  35. )
  36. AND r.`studentResult`>=80;
  38. #count 用来统计返回结果的总条数
  39. SELECT COUNT(*) FROM result;
  40. #考试成绩>70分的有多少人
  41. SELECT COUNT(*) AS '大于70分的人数'FROM result  r WHERE r.`studentResult`>70;
  42. #count(1)和count(*)的区别?
  43. #一般情况效率count(1)>count(*)
  44. #查询李梅的总分是多少?
  45. SELECT SUM(r.`studentResult`) FROM result r WHERE r.`studentNo`=(SELECT s.`studentNo` FROM student s WHERE s.`studentName`='李梅');
  46. #查询李梅的平均分是多少?
  47. SELECT AVG(r.`studentResult`) FROM result r WHERE r.`studentNo`=(SELECT s.`studentNo` FROM student s WHERE s.`studentName`='李梅');
  48. #查询李梅的最大分和最小分是多少?
  49. SELECT MAX(r.`studentResult`) FROM result r WHERE r.`studentNo`=(SELECT s.`studentNo` FROM student s WHERE s.`studentName`='李梅');
  50. SELECT MIN(r.`studentResult`) FROM result r WHERE r.`studentNo`=(SELECT s.`studentNo` FROM student s WHERE s.`studentName`='李梅');
  51. #按照课程进行分组,查询每个课程的平均分,最高分和最低分
  52. #对于低于80分平均分的不予显示
  53. SELECT  su.`subjectName`'课程名称',
  54. AVG(r.`studentResult`)'平均分' ,
  55. MAX(r.`studentResult`)'最高分',
  56. MIN(r.`studentResult`)'最低分'
  57. FROM result r,`subject` su WHERE r.`subjectNo`=su.`subjectNo` 
  58. GROUP BY r.`subjectNo`
  59. HAVING AVG(r.`studentResult`)>=80;
  60. #查询每个学生的总分
  61. SELECT s.`studentName`'姓名',
  62. SUM(r.`studentResult`)'总分'
  63. FROM result r,student s WHERE s.`studentNo`=r.`studentNo`
  64. GROUP BY s.`studentNo`;
  65. #从高到低排序
  66. SELECT s.`studentName`'姓名',
  67. SUM(r.`studentResult`)'总分'
  68. FROM result r,student s WHERE s.`studentNo`=r.`studentNo`
  69. GROUP BY s.`studentNo`
  70. ORDER BY SUM(r.`studentResult`) DESC;

八,综合练习题

※难点:嵌套查询和子查询

  1. # 查询《高等数学-1》的所有考试结果(学号、姓名、课程名、成绩)，并按成绩由高到低排列
  2. SELECT s.`studentNo`,s.`studentName`,su.`subjectName`,r.`studentResult` FROM student s,result r,`subject` su WHERE s.`studentNo`=r.`studentNo` AND r.`subjectNo`=su.`subjectNo`AND su.`subjectName`='高等数学-1' ORDER BY r.`studentResult` DESC;
  3. # 查询《高等数学-2》的考试结果的第二名(学号、姓名、课程名、成绩)
  4. SELECT s.`studentNo`,s.`studentName`,su.`subjectName`,r.`studentResult` FROM student s,result r,`subject` su WHERE s.`studentNo`=r.`studentNo` AND r.`subjectNo`=su.`subjectNo`AND su.`subjectName`='高等数学-2' ORDER BY r.`studentResult` DESC LIMIT 1,1;
  6. #查询课程为《高等数学-2》成绩前2名分数大于60的学生信息（学号，姓名，课程名，分数）
  7. SELECT s.`studentNo`,s.`studentName`,su.`subjectName`,r.`studentResult` FROM student s,result r,`subject` su WHERE s.`studentNo`=r.`studentNo` AND r.`subjectNo`=su.`subjectNo`AND su.`subjectName`='高等数学-2' AND r.`studentResult`>60 ORDER BY r.`studentResult` DESC LIMIT 2 ;
  9. #查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
  10. SELECT s.`studentNo`,s.`studentName` FROM student s,result r,`subject` su WHERE s.`studentNo`=r.`studentNo` AND r.`subjectNo`=su.`subjectNo`AND su.`subjectName`='高等数学-2' AND r.`studentResult`>=80;
  12. #查询《高等数学-3》的前2名学生成绩信息(学号、姓名、成绩)
  13. SELECT s.`studentNo`,s.`studentName`,r.`studentResult` FROM student s,result r,`subject` su WHERE s.`studentNo`=r.`studentNo` AND r.`subjectNo`=su.`subjectNo`AND su.`subjectName`='高等数学-3' ORDER BY r.`studentResult` DESC LIMIT 2 ;
  15. #按照不同的课程，分别算出其平均分、最高分和最低分，
  16. #对于低于60分平均分的不予显示
  17. #显示的结果（课程名、最高分、最低分、平均分）
  18. SELECT su.`subjectName`'课程名',
  19. MAX(r.`studentResult`)'最高分',
  20. MIN(r.`studentResult`)'最低分',
  21. AVG(r.`studentResult`)'平均分'  
  22. FROM `subject` su,result r WHERE su.`subjectNo`=r.`subjectNo` GROUP BY su.`subjectNo`
  23. HAVING AVG(r.`studentResult`)>=60;
  24. #查询大一年级的总人数#1
  25. SELECT COUNT(1) FROM grade g,student  s WHERE g.`gradeID`=s.`gradeID` AND g.`gradeName` LIKE '%大一%';
  26. #子查询方法
  27. SELECT COUNT(1) FROM student s 
  28. WHERE s.`gradeID`=(
  29. SELECT g.`gradeID` FROM grade g WHERE g.`gradeName`='大一'
  30. );
  31. #查询每个年级的最高分、最低分、平均分
  32. #(年级、最高分、最低分、平均分)
  33. SELECT  g.`gradeName`'年级',
  34. MAX(r.`studentResult`)'最高分',
  35. MIN(r.`studentResult`)'最低分',
  36. AVG(r.`studentResult`)'平均分'
  37. FROM result r,grade g,student s WHERE s.`studentNo`=r.`studentNo`AND s.`gradeID`=g.`gradeID`
  38. GROUP BY s.`gradeID`;
  39. #查询每个年级的总分按照由高到低进行排名
  40. #(年级、总分)
  41. SELECT  g.`gradeName` '年级',
  42. SUM(r.`studentResult`)'总分'
  43. FROM result r,grade g,student s WHERE s.`studentNo`=r.`studentNo`AND s.`gradeID`=g.`gradeID`
  44. GROUP BY s.`gradeID` ORDER BY SUM(r.`studentResult`) DESC;
  45. #查询学生平均成绩大于70分的学生姓名
  46. SELECT  s.`studentName`'姓名'
  47. FROM student s,result r  WHERE s.`studentNo`=r.`studentNo`
  48. GROUP BY s.`studentNo`
  49. HAVING AVG(r.`studentResult`)>70;
  51. #查询总分最高的男生和女生的姓名和总分数
  52. #嵌套查询
  53. SELECT na'姓名',su '总分',s '性别'FROM(
  54. SELECT s.`studentName` na , SUM(r.`studentResult`) su ,s.`sex` s 
  55. FROM student s,result r
  56. WHERE s.`studentNo` = r.`studentNo`
  57. GROUP BY s.`studentNo`
  58. ORDER BY SUM(r.`studentResult`) DESC
  59. )stu GROUP BY stu.s;

九.事务.索引.备份和恢复

1.面试题:
image.png
2.
#关闭事务的自动提交
SET autocommit=0;
#开启事务处理
START TRANSACTION;
UPDATE student SET loginPwd =’000’ WHERE studentNo=1014;
SELECT * FROM student;
#事务提交
COMMIT;
#事务的回滚
ROLLBACK;
#开启事务的自动提交
SET autocommit=1;
3.理解:当前窗口上修改表的信息后,提交事务前,另一窗口可对比查询修改后的结果,另一窗口的信息并未修改,在当前窗口点击commit(同步)后,另一窗口信息才被修改;rollback,是提交前可让当前窗口信息回归原始状态
4.

  1. 当前窗口
  2. CREATE DATABASE shop;
  3. DROP TABLE account;
  4. CREATE TABLE account(
  5. id INT(11) NOT NULL PRIMARY KEY ,
  6. `name`     VARCHAR(32) NOT NULL,
  7. cash DECIMAL(9,2) NOT NULL 
  8. )AUTO_INCREMENT=1 COMMENT '银行信息';
  10. INSERT INTO account (id,`name`,cash) VALUES(1,'A',2000.00),(2,'B',10000.00);
  11. COMMIT;
  13. 下一窗口
  14. USE shop;
  15. SELECT * FROM account;
  16. #模拟转账过程----ACID特性体现
  17. START TRANSACTION;
  18. UPDATE account SET cash=cash-500 WHERE `name`='A';
  19. UPDATE account SET cash=cash+500 WHERE `name`='B';
  20. COMMIT;
  22. ※※理解中的另一窗口,需要再次打开mysql

十.创建索引.添加.查看.删除索引

  1. #创建索引
  2. #在学生表的手机号添加唯一索引
  3. #唯一索引之后,手机号不能出现重复,否则报错
  4. ALTER TABLE student ADD UNIQUE (phone);
  5. UPDATE student SET Phone ='13500000001'
  6. WHERE studentName='大聪明';
  7. #添加普通索引
  8. ALTER TABLE student ADD INDEX(email);
  9. #查看索引
  10. SHOW INDEX FROM student;
  11. #删除索引
  12. ALTER TABLE student DROP INDEX phone;

索引准则:
1.索引不是越多越好
2.不要对经常变动的数据加索引
3.小数据量的表建议不要加索引
4.索引一般应加在查找条件/分组/排序的字段中

十一.备份和恢复

  1. #中高级开发,mysql执行计划
  2. EXPLAIN
  3. SELECT * FROM student s WHERE s.`studentName`='李梅';
  4. ALTER TABLE student ADD INDEX (studentName);
  6. #备份和恢复
  7. #备份,找到表,右键,导出--转储---勾选最后的结构---找到存储路径起个名字---导出
  8. #恢复,在文件里复制过来,选择运行,表即可恢复
  9. DROP TABLE category;

十二.练习

1.两个表子查询

2.多个表子查询

3.表链接和子查询共有

  1. #查询姓名为三个字的学员编号、姓名、年级名 
  2. SELECT s.`studentNo`,s.`studentName`,g.`gradeName` FROM  student s,grade g WHERE s.`gradeID`=g.`gradeID` AND s.`studentName`LIKE '___';
  3. #查找李梅所有考试的成绩（科目名称、考试成绩）
  4. SELECT su.`subjectName`,r.`studentResult` FROM student s,`subject` su,result r WHERE s.`studentNo`=r.`studentNo` AND su.`subjectNo`=r.`subjectNo` AND s.`studentName`='李梅'; 
  5. #使用表连接方式查询所有大一年级学员学号、姓名及年级名
  6. SELECT s.`studentNo`,s.`studentName`,g.`gradeName` FROM student s,grade g WHERE s.`gradeID`=g.`gradeID` AND g.`gradeName`='大一';
  7. #使用子查询方式查询所有大一年级学员学号、姓名、年级ID
  9. SELECT  s.`studentNo`,s.`studentName`
  10. FROM student s  
  11. WHERE s.`gradeID`=(
  12. SELECT g.`gradeID` FROM grade g WHERE g.`gradeName`='大一'
  13. );
  15. #使用表连接方式查询“高等数学-2”课程大于80分的学生姓名
  16. SELECT s.`studentName` FROM student s,`subject` su,result r WHERE s.`studentNo`=r.`studentNo` AND r.`subjectNo`=su.`subjectNo` AND r.`studentResult`>80 AND su.`subjectName`='高等数学-2';
  17. #使用子查询方式查询“高等数学-2”课程大于80分的学生
  18. SELECT s.`studentName`
  19. FROM student s
  20. WHERE s.`studentNo`=(
  21. SELECT r.`studentNo` FROM result r 
  22. WHERE r.`subjectNo`=(
  23. SELECT su.`subjectNo` FROM `subject` su WHERE su.`subjectName`='高等数学-2' AND r.`studentResult`>80
  24. )
  25. );
  27. #使用子查询方式+表连接方式混合查询“高等数学-2”课程大于80分的学生
  28. SELECT s.`studentName`
  29. FROM student s JOIN result r
  30. ON s.`studentNo`=r.`studentNo`
  31. WHERE r.`subjectNo`=(
  32. SELECT su.`subjectNo` FROM `subject` su WHERE  su.`subjectName`='高等数学-2' AND r.`studentResult`>80
  33. );

十三、练习

1、查询重名的学生—in

查询student表中重名的学生(全部学生信息)
#in 包含在

2、不同课程的成绩比较

查询课程001的成绩比课程002的成绩高的所有学生的学号和姓名

3、平均分大于60分

查询平均成绩大于60分的同学的学号和平均成绩

4、选课数

查询所有同学的学号、姓名、选课数、总成绩

5、查询姓“张”的老师的个数

6、没学过某老师的课程的人

查询没学过“张三”老师课的同学的学号、姓名

7、同时学过两个课程的学生

查询同时学过课程001和课程002的同学的学号、姓名

8、学过所有课程的人

查询学过所有课程的所有同学的学号、姓名

  1. #1.查询student表中重名的学生(全部学生信息)
  2. #in 包含在
  3. SELECT * FROM student s1 WHERE s1.`Sname` IN(
  4. SELECT s.`Sname` FROM student s GROUP BY s.`Sname` HAVING COUNT(1)>1);
  6. SELECT s1.* FROM student s1,student s2 WHERE s1.`Sname`=s2.`Sname` AND s1.`Sid`!=s2.`Sid`;
  8. #2.查询课程001的成绩比课程002的成绩高的所有学生的学号和姓名
  9. SELECT a.si1,s.`Sname` FROM(
  10. (SELECT sc1.`score` s1,sc1.`Sid` si1 FROM sc sc1 WHERE sc1.`Cid`='001') a,
  11. (SELECT sc2.`score` s2,sc2.`Sid` si2 FROM sc sc2 WHERE sc2.`Cid`='002') b
  12. )JOIN student s ON s.`Sid`=a.si1 WHERE a.s1>b.s2 AND a.si1=b.si2;
  14. #3.查询平均成绩大于60分的同学的学号和平均成绩
  15. SELECT s.`Sid`,AVG(sc1.`score`) 
  16. FROM student s,sc sc1 
  17. WHERE s.`Sid`=sc1.`Sid`
  18. GROUP BY s.`Sid`
  19. HAVING AVG(sc1.`score`)>60;
  21. #4.查询所有同学的学号、姓名、选课数、总成绩
  22. SELECT stu.sid,stu.sname,COUNT(cid),SUM(s.`score`)
  23. FROM student stu JOIN sc s ON stu.`Sid` = s.`Sid`
  24. GROUP BY stu.`Sid`;
  26. #5.查询姓“张”的老师的个数
  27. SELECT COUNT(1) FROM teacher t WHERE t.`Tname` LIKE '张%';
  28. #6.查询没学过“张三”老师课的同学的学号、姓名
  29. SELECT DISTINCT s.`Sid`,s.`Sname` FROM student s,teacher t,sc sc1,course c WHERE s.`Sid`=sc1.`Sid` AND sc1.`Cid`=c.`Cid` AND c.`Tid`=t.`Tid` AND t.`Tname`!='张三';
  31. #子查询方法
  32. SELECT * FROM student s WHERE s.`Sid` IN(
  33. SELECT DISTINCT sc.sid FROM sc WHERE sc.`Cid` NOT IN (
  34. SELECT c.`Cid` FROM course c WHERE c.`Tid` = (
  35. SELECT t.`Tid` FROM teacher t WHERE t.`Tname` = '张三')
  36. ));
  37. #7.查询同时学过课程001和课程002的同学的学号、姓名
  38. #select s.`Sid`,s.`Sname` from student s,sc sc1,course c where s.`Sid`=sc1.`Sid` and c.`Cid`=sc1.`Cid` and c.`Cid`='001'and c.`Cid`='002';
  39. SELECT a.si1,s.`Sname` FROM(
  40. (SELECT sc1.`score` s1,sc1.`Sid` si1 FROM sc sc1 WHERE sc1.`Cid`='001') a,
  41. (SELECT sc2.`score` s2,sc2.`Sid` si2 FROM sc sc2 WHERE sc2.`Cid`='002') b
  42. )JOIN student s ON s.`Sid`=a.si1 WHERE a.si1=b.si2;
  44. #8.查询学过所有课程的所有同学的学号、姓名
  45. SELECT * FROM student s WHERE s.`Sid` IN(
  46. SELECT sc.`Sid`
  47. FROM sc GROUP BY sc.`Sid`
  48. HAVING COUNT(1) = (SELECT COUNT(1)
  49. FROM course));

十四、视图

也为虚拟表

  1. #创建视图
  2. CREATE VIEW stu_res AS 
  3. SELECT s.*,r.`subjectNO`,r.`examDate`,r.`studentresult` 
  4. FROM student s,result r WHERE s.studentNo=r.studentNo;
  5. #使用视图--和使用表一样
  6. SELECT * FROM stu_res;
  7. #删除视图
  8. DROP VIEW stu_res;