第13讲MySql增删改查精选课件.ppt

1、第十三讲 MYSQL-MYSQL-增删改查插入记录操作插入记录操作语法：叙述：如果表名后面没写字段名，则默认是向所有的字段添加值，另外字符串值应该用 或“”引号括起来举例举例1 1：向people表中添加一条记录：insert into people(name,age)values(“zhangsan”,20);INSERT INTO 列名列名 VALUES 插入案例创建一张学生信息表，往表中插入数据create table students(scode int not null auto_increment,sname varchar(20)not null,saddress varchar

2、(20)default未知,sage int,semail varchar(20),ssex bit,primary key(scode);插入数据行 1注意事项注意事项1:1:每次插入一行数据，不可能只插入半行或者几列数据，因每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；此，插入的数据是否有效将按照整行的完整性的要求来检验；INSERT INTO Students(SName,SAddress,Sage,SEmail,SSEX)VALUES(小明)插入数据行 2注意事项注意事项2:2:每个数据值的数据类型、精度和小数位数必须与相应的列

3、每个数据值的数据类型、精度和小数位数必须与相应的列匹配匹配；INSERT INTO Students(SName,SAddress,Sage,SEmail,SSEX)VALUES(张青裁,上海松江,ZQC,ZQCSohu,男)插入数据行3注意事项注意事项4:4:如果在设计表的时候就指定了某列不允许为空，则必须插如果在设计表的时候就指定了某列不允许为空，则必须插入数据入数据；INSERT INTO Students(SAddress,Sage,SEmail,SSEX)VALUES(上海松江,6,ZQCSohu,0)插入数据行4注意事项注意事项6:6:具有缺省值的列，具有缺省值的列，可以使用可以使

4、用DEFAULTDEFAULT（缺省）关键字来代（缺省）关键字来代替插入的数值替插入的数值INSERT INTO Students(SName,SAddress,Sage,SEmail,SSEX)VALUES(张青裁,DEFAULT,6,ZQCSohu,0)插入多行数据INSERT STUDENTS(SName,Sage,SSex)VALUES(测试女生1,35,0),(测试女生2,17,0),(测试女生3,13,0),(测试男生1,11,1),(测试女生4,20,0),(测试男生2,24,1),(测试女生5,11,0),(测试男生3,23,1);INSERT INTO(列名)VALUES()

5、,(),()插入多行数据INSERT INTO TongXunLu(姓名,地址,电子邮件)SELECT SName,SAddress,SEmailFROM StudentsINSERT INTO(列名)SELECT FROM 学生、课程、成绩插入数据学生信息学生信息95001,95001,洛燕妮洛燕妮,女女,20,20,信息系信息系 95002,95002,欧阳炎欧阳炎,男男,19,19,计算机系计算机系 95003,95003,何东升何东升,男男,21,21,美术系美术系 95004,95004,刘晨刘晨,男男,18,18,计算机系计算机系 95005,95005,沙明华沙明华,女女,21,

6、21,美术系美术系 95006,95006,欧阳宝宝欧阳宝宝,男男,19,19,美术系美术系课程信息课程信息1,JAVA1,JAVA2,C2,C3,.NET3,.NET成绩信息成绩信息95001,1,98 95002,1,50 95004,1,70 95001,1,98 95002,1,50 95004,1,70 95003,4,95 95005,4,40 95006,4,77 95003,4,95 95005,4,40 95006,4,77 95001,2,80 95002,3,58 95006,2,7095001,2,80 95002,3,58 95006,2,70更改记录操作更改记录操作

7、语法：叙述：where 子句是判断语句，用来设定条件，限制只更新匹配的行,如果不带where子句，则更新所有行数据。举例：将student表中的所有学生名称为Alex的改为Tom:update student set sname=Tom where sname=Alex;UPDATE SET WHERE 更新数据行UPDATE Student SET SSEX=0UPDATE Student SET Sdept=家政系 WHERE Sdept=刺绣系UPDATE ScSET Grade=Grade+5WHERE Grade=95删除记录操作删除记录操作语法：叙述：此语句删除表中的行，如果不带w

8、here子句，则删除整个表中的记录，但是表不被删除。举例：删除student表中的所有年龄小于18岁的记录：delete from student where sage18;DELETE FROM WHERE 补充说明删除学生编号为95005的学生信息包括成绩信息delete from student,sc using student,sc where student.sno=sc.studentid and student.sno=95005;说明：表和字段的引用方式有两种：绝对引用和相对引用绝对引用：数据库名.表名(.字段名)相对引用：表名.(字段名)删除数据行河南新乡赵可以0010016

9、河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012.地址地址姓名姓名学号学号980010016数学810010013语文670010012语文740010013数学880010012数学.分数学号科目DELETE FROM 学员信息表 WHERE 学号=0010012 什么是查询？Application Logic客户程序查询请求查询结果集SQL SERVERA B C D E F G SELECT*FROM SALES查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显

10、示出来怎么查的？学员编号学员姓名地址所在班级.001001张明全湖南长沙S201S201002002李菲湖北宜昌S202S202003003于寄谦甘肃天水S204S204004004刘国正山东荷泽S201S201005005周接轮台湾新竹S204S204006006巩小妹香港龙湾S203S203007007巩大妹香港龙湾S203S203008008张明敏北京顺义S202S202009009矛十八四川棉阳S204S204010010罗林光陕西临潼S202S202011011司马坡新疆喀什S201S201S202陕西临潼陕西临潼罗林光罗林光010S202湖北宜昌湖北宜昌李菲李菲002S202北京顺

11、义北京顺义张明敏张明敏008SELECT*FROM Student WHERE 所在班级=S202SQL语法SELECT DISTINCT|DISTINCTROW|ALL select_expression,.FROM table_references WHERE where_definition GROUP BY col_name,.HAVING where_definition ORDER BY unsigned_integer|col_name|formula ASC|DESC,.LIMIT offset,rows PROCEDURE procedure_name 查询记录操作fromf

12、rom子句：指定查询数据的表wherewhere子句：查询数据的过滤条件group bygroup by子句：对匹配where子句的查询结果进行分组havinghaving子句：对分组后的结果进行条件限制order byorder by子句：对查询结果结果进行排序，后面跟descdesc降序或ascasc升序（默认）。limitlimit子句：对查询的显示结果限制数目procedureprocedure子句：查询存储过程返回的结果集数据查询举例SELECT FROM WHERE ORDER BY ASC或或DESC列名称列名称SELECT Scode,SName,Sdept表名表名FROMSt

13、udents过滤条件过滤条件WHERESSEX=0排序条件排序条件ORDER BYSageSELECT Scode,SName FROM Students WHERE SSEX=0 ORDER BY Sage举例1:1:查询全体学生的学号与姓名。select sno,sname from student;举例2:2:查询全体学生的详细记录。select sno,sname,ssex,sage,sdept from student;或select *from student;举例3 3:查全体学生的姓名及其出生年份。select sname,2019-sage from student；举例4

14、4:查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。select sname,year of birth:,2019-sage,islower(sdept)from student;举例5 5:查询选修了课程的学生学号(去掉重复的记录)select distinct studentid from sc;举例6:6:查询全体学生的学号与姓名，用中文显示列名。select sno as 编号,sname as 姓名 from student;举例7:7:给表设置别名。select s.sno,s.sname from student as s;举例8 8:查询年龄在20以下的学生

15、的姓名。select sname from student where sage70;在WHERE子句中使用谓词:BETWEEN AND :在两数之间NOT BETWEEN AND：不在两数之间IN：是否在特定的集合里（枚举）NOT IN：与上面相反LIKE：是否匹配于一个模式IS NULL（为空的）或 IS NOT NULL（不为空的）REGEXP:检查一个值是否匹配一个常规表达式。举例举例17:17:查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。select sname,sdept,sage from student where sage between 20 a

16、nd 23;举例举例18:18:查询年龄不在2023岁之间的学生姓名、系别和年龄。select sname,sdept,sage from student where sage not between 20 and 23;举例举例19:19:查询信息系、美术系和计算机系学生的姓名和性别。select sname,ssex from student where sdept in(信息系,美术系,计算机系);举例20:20:查询学号为95001的学生的详细情况。select*from student where sno like 95001；等价于：select*from student wher

17、e sno=95001;举例21:21:查询所有姓刘学生的姓名、学号和性别。select sname,sno,ssex from student where sname like 刘%;举例22:22:某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。select studentid,courseid from sc where grade is null;举例23:23:查所有有成绩的学生学号和课程号。select studentid,courseid from sc where grade is not null;举例24:24:查询计

18、算机系年龄在20岁以下的学生姓名。select sname from student where sdept=计算机系 and sage20;举例举例25:25:查询信息系、美术系和计算机系学生的姓名和性别select sname,ssex from student where sdept in(信息系,美术,计算机系);可改写为：Select sname,ssex from student where sdept=信息系 or sdept=美术 or sdept=计算机系;举例举例26:26:查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。select studentid,g

19、rade from sc where courseid=3 orber by grade desc;举例举例27:27:查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select*from student order by sdept,sage desc;多表查询问题学员内部测试成绩查询的每次显示的都是学员的编号信息，因为该表中只存储了学员的编号；实际上最好显示学员的姓名，而姓名存储在学员信息表；如何同时从这两个表中取得数据？多表查询分类连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件内连接(INNER JOIN)外连接左外联结 (LE

20、FT JOIN)右外联结 (RIGHT JOIN)外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出：SELECT S.SName,C.CourseID,C.Grade From Sc AS CINNER JOIN Students AS SON C.StudentID=S.SNoSc122300100100200297896776300381猜一猜：这样写，返回的查询结果是一样的吗？SELECT S.SName,C.CourseID,C.Grade From Students AS SINNER JOIN Sc AS

21、 CON C.StudentID=S.SNo再猜一猜：以下返回多少行？SELECT S.SName,C.CourseID,C.Grade From Students AS SINNER JOIN Sc AS CON C.StudentID S.SNo多表查询内连接-1Stundents梅超风陈玄风陆乘风曲灵风1234查询结果查询结果梅超风陈玄风陈玄风陆乘风00100100200297896776陆乘风00381多表查询内连接-2SELECT Students.SName,Sc.CourseID,Sc.GradeFROM Students,ScWHERE Students.SNo=Sc.Stud

22、entID多表查询三表连接SELECT S.SName AS 姓名,CS.CName AS 课程,C.Grade AS 成绩FROM Students AS S INNER JOIN Sc AS C ON(S.SCode=C.StudentID)INNER JOIN Course AS CS ON(CS.CourseID=C.CourseID)Sc122300100100200297896776300381多表查询左外连接Stundents梅超风梅超风陈玄风陈玄风陆乘风陆乘风曲灵风曲灵风1234查询结果查询结果梅超风梅超风陈玄风陈玄风陈玄风陈玄风陆乘风陆乘风001001002002978967

23、76陆乘风陆乘风00381曲灵风曲灵风NULLNULLSELECT S.SName,C.CourseID,C.Grade From Students AS SLEFT JOIN Sc AS CON C.StudentID=S.SNo猜一猜：这样写，返回的查询结果是一样的吗？SELECT S.SName,C.CourseID,C.Grade From Sc AS CLEFT JOIN Students AS SON C.StudentID=S.SNo多表查询右外连接SELECT Titles.Title_id,Titles.Title,Publishers.Pub_nameFROM titles

24、 RIGHT OUTER JOIN Publishers ON Titles.Pub_id=Publishers.Pub_id子查询：将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为子查询。一个SELECT-FROM-WHERE语句称为一个查询块例如：select sname from student where sno in(select studentid from sc where courseid=2);例如：查询选修了课程名为“Java”的学生学号和姓名 select sno,sname from student where sno in (select studentid from sc where cno=(select cno from course where cname=Java);复制表复制表我们来创建一个副本：复制表复制表可以通过在SELECT查询中指定字段来限制出现在附表中的字段复制表复制表通过使用SELECT 语句创建已存在表的空副本，并且返回一个空结果集。数据库之路未完，待续.