2021/9/22 Java数据库编程 第三章 SQL高级(一)

目录

一.SQL语言的四个分类

二.修改表

1.修改表名

2.添加字段<列>

3.修改字段<列>

4.删除字段

5.添加主键

6.添加外键约束

三.数据操作(DML)语句

 1.插入单行数据

2.插入多行数据

3.将查询结果插入到新表

4.更新数据记录

5.删除数据

四.数据查询语句（DQL）

１．SQLserver语句语法

2 .MySQL的LIMIT子句

3 .常用的函数

1.聚合函数

2.字符串函数

3.时间日期函数

4.数学函数

五.子查询

 1.简单的子查询

 2.IN子查询

 3.NOT IN子查询

六.总结

---

一.SQL语言的四个分类

1. DML（Data Manipulation Language）（数据操作语言）：用来插入，修改和删除表中的数据，如INSERT,UPDATE,DELECT。
2. DDL（Data Definition Language）（数据定义语言）：创建或删除数据库对象操作，有CREATE,DROP,ALTER三个语法组成。
3. DQL (STructured Query Language)（数据查询语言）：用来对数据库中的语言进行查询，指SELECT语句。
4. DCL(Data Control Language)（数据控制语言）：用来控制数据库组建的存取许可，存取权限等，如GRANT,REVOKE. <GRANT 授权  REVOKE 取消授权>

二.修改表

1.修改表名

语法：     ALTER　TABLE <旧表名> RENAME  [TO] <新表名>；

其中[TO]为可选参数，不影响结果，仅修改表名，表结构不变。

2.添加字段<列>

语法：     ALTER  TABLE 表名 ADD　字段名  数据类型 [属性]；

例： ALTER TABLE `student`  ADD  `password `  VARCHAR(32)  NOT  NULL;向学生表中添加密码的字段。

3.修改字段<列>

语法： ALTER  TABLE 表名 CHANGE  原字段名  新字段名 数据类型[属性]；

例： ALTER TABLE `student`  CHANGE  `password`  `pwd`CHAR(12)  NOTNULL;修改学生表中的密码字段的名称和属性。

4.删除字段

语法： ALTER TABLE 表名  DROP 字段名；

例： ALTER  TABLE　`student` DROP  `pwd`;  删除学生表中的密码字段

5.添加主键

语法： ALTER  TABLE  表名  ADD  CONSTRAINT 主键名 PEIMARY  KEY 表名(主键字段)；

例： ALTER  TABLE `student`  ADD CONSTRAINT ‘pk_student’  PRIMARY  KEY `student`(`studentNO`);把学生表中的学号设置为主键列

6.添加外键约束

语法： ALTER  TABLE  主表名  ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段)REFERENCES  关联从表名(关联字段);

例:  ALTER  TABLE  `student`  ADD  CONSTRAINT  `fk_student_grade` FOREIGN  KEY(`gradeID`)  REFERENCES  `grade`(`gradeID`);把学生表和年级表中的年级字段设置为主外键关系。

三.数据操作(DML)语句

 1.插入单行数据

语法: INSERT INTO 表名 [(字段名列表)]  VALUES (值列表)；

插入数据的注意事项：

1）  表的字段名时可选的，如果省略，则需插入表中的所有字段。

2）  多个列表和多个值之间使用逗号“，”分开。

3）  值列表必须和字段列表一一对应，且值类型相同。

4）  如果插入表中部分数据，字段名列表必须填写，且遵循3）。

2.插入多行数据

语法：INSERT INTO 表名 [(字段名列表)]  VALUES (值列表1)，(值列表2)….. (值列表n);

3.将查询结果插入到新表

语法一： 需要按插入字段的类型，顺序，个数先创建表，才能插入数据

                   SELECT 字段1，字段2…….INTO 新表名  FROM  原表名；

语法二：无需先创建表

                   CREATE  TABLE  新表(SELECT 字段1，字段2……FROM  原表);

4.更新数据记录

语法: UPDATE  表名  SET  字段1=值1，字段2=值2…字段n=值n  WHERE 条件;

5.删除数据

语法一:  DELETE FROM  表名 WHERE条件;

语法二：TRUNCATE  TABLE  表名；

它们的区别是delete按条件删除表中的数据，精确性更高；truncate是删除整个表，效率更高，并且删除后可以重置自增列。

四.数据查询语句（DQL）

１．SQLserver语句语法

SELECT <字段名列表>

FROM <表名或视图>

WHERE 查询条件

GROUP BY<分组的字段名>

HAVING<筛选分组的条件>

ORDER BY<排序的字段名>[ASC升序/DESC降序]

2 .MySQL的LIMIT子句

SELECT <字段名列表>

FROM <表名或视图>

WHERE 查询条件

GROUP BY<分组的字段名>

ORDER BY<排序的字段名>[ASC升序/DESC降序]

LIMIT 位置偏移量,显示数据的行数;

其中位置的偏移量是指从查询结果集中第几条数据开始显示,第一条记录的位置是0,此参数为可选项,默认从第一条显示.

3 .常用的函数

1.聚合函数

        AVG()平均值；COUNT()统计字段的行数； MAX()最大值;  MIN()最小值； SUM()求和

2.字符串函数

函数名	作用	举例
CONCAT(str1,str2…)	连接括号内的字符串	SELECT CONCAT(‘my’,’s’,’ql’); 返回：mysql
INSERT(str,pos,len,newstr)	将字符串str从pos位置开始，len个字符长的子串替换为字符串newstr	SELECT INSERT(‘这是SQLserver数据库’,3,10,’mysql’) 返回：这是mysql数据库
LOWER(str)	将字符串str全部变为小写
UPPER(str)	将字符串str全部变为大写
SUBSTRING(str,num,len)	返回字符串str的第num个位置开始长度为len的子字符串	SELECT SUBSTRING(‘javamysqlOrcle’,5,5); 返回：mysql

3.时间日期函数

函数名	作用	举例
CURDATE()	获取当前的日期	SELECT CURDATE(); 返回:2021-09-22
CURTIME()	获取当前的时间	SELECT CURTIME(); 返回:16:33:43
NOW()	获取当前的日期和时间	SELECT NOW(); 返回：2021-09-22 16:33:43
WEEK(date)	返回日期date为一年中的第几周	SELECT WEEK(NOW()); 返回:26
YEAR(date)	返回日期date的年份	SELECT YEAR(NOW()); 返回:2021
HOUR(time)	返回事件time的小时值	SELECT HOUR(NOW()); 返回：16，返回现在几点
MINUTE(time)	返回时间的分钟值
DATEDIFF(date1,date2)	返回日期参数date 1和date2之间相隔的天数	SELECR DATEDIFF(NOW(),2009-09-1); 返回:2281
ADDDATE(date,n)	计算日期date加上天数n天之后的日期	SELECT ADDDATE(NOW(),5); 返回:2017-09-28 12:34:00
TIMESTAMPDIFF(YEAR,'1999-01-17',CURDATE())	计算年龄	SELECT  TIMESTAMPDIFF(YEAR,'1999-01-17',CURDATE()) 返回:23
MONTH(date)	返回日期date的月份	SELECT MONTH(NOW()); 返回:9

4.数学函数

函数名	作用	举例
CELT(x)	返回大于或等于x的最小整数	SELECT CELT(2.3); 返回:3
FLOOR(x)	返回小于或等于x的最小整数	SELECR FLOOR(2.3); 返回:2
RAND()	返回0~1之间的随机数	SELECT RAND(); 返回：0.551111245245016

五.子查询

 1.简单的子查询

定义：子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询

子查询在WHERE语句中的一般用法：

  将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

例：查询“Java Logic”课程至少一次考试刚好等于60分的学生

第一步：查询“Java Logic”课程的课程编号

第二步：根据课程编号查询成绩是60分学生的学号

第三步：根据学号查询得到学生姓名

法一：采用表连接

SELECT `StudentName` FROM `Student` stu

  INNER JOIN `Result` r ON stu.StudentNO = r.StudentNo

  INNER JOIN `Subject` sub ON r.SubjectNo = sub.SubjectNo

  WHERE `StudentResult` = 60 AND `SubjectName` = 'Java Logic'

GO

实现方法二：采用子查询

SELECT `StudentName` FROM `Student` WHERE `StudentNo` = (

    SELECT `StudentNo` FROM `Result`

        INNER JOIN `Subject` ON Result.SubjectNo= Subject.SubjectNo

        WHERE StudentResult=60 AND SubjectName='Java Logic'

  )

GO

1）一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换

2）子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据

3）表连接更适合于查看多表的数据

 2.IN子查询

使用IN关键字可以使父查询匹配子查询返回多个字段值。

例：查询参加“Java Logic”课程最近一次考试的在读学生名单

第一步：获得Java Logic课程的课程编号

SELECT SubjectNo FROM Subject

WHERE SubjectName='Java Logic'

第二步：根据课程编号查询得到Java Logic课程最近一次的考试日期

SELECT MAX(ExamDate) FROM Result WHERE SubjectNo=(

   SELECT SubjectNo FROM Subject

   WHERE SubjectName='Java Logic' )

第三步：根据课程编号和最近一次的考试日期查询出在读学生信息

SELECT StudentNo, StudentName FROM Student

WHERE StudentNo  IN (

  SELECT StudentNo FROM Result

  WHERE SubjectNo IN (

      SELECT SubjectNo FROM Subject

      WHERE SubjectName='Java Logic'

   ) AND ExamDate = (

        SELECT MAX(ExamDate) FROM Result

        WHERE SubjectNo = (

             SELECT SubjectNo FROM Subject

             WHERE SubjectName='Java Logic' 

        )

   )

)

 3.NOT IN子查询

例：查询未参加“Java Logic”课程最近一次考试的在读学生名单

SELECT StudentNo, StudentName FROM Student

WHERE StudentNo  NOT IN (

  SELECT StudentNo FROM Result

  WHERE SubjectNo = (

    SELECT SubjectNo FROM Subject

    WHERE SubjectName='Java Logic'

  )  AND ExamDate = (

    SELECT MAX(ExamDate) FROM Result

    WHERE SubjectNo = (

      SELECT SubjectNo FROM Subject

      WHERE SubjectName='Java Logic' ) ) )

AND GradeId = (

  SELECT GradeId FROM Subject WHERE SubjectName = 'Java Logic'

)

六.总结

1.查询的方法有三种

联合——合并多个数据集中的行

子查询——将一个查询嵌套在另一个查询中

连接——合并多个数据表中的列

2.比较运算符后面的子查询只能返回单个数值。

IN子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否存在于某个范围中。

在子查询中使用EXISTS子句，可以对子查询中的行是否存在进行检查。子查询可以出现在表达式出现的任何位置。

3.修改字段时要注意，因为不同类型的数据存储方式和长度不同，修改数据类型可能会影响数据表中已有的数据，所以在已有数据情况下不应轻易修改数据类型。

4.给表添加外键约束时，需要注意表的存储引擎。InnoDB存储类型的表支持外键，MyISAM存储类型的表不支持外键。

5.TRUNCATE语句删除表中所有数据时会重置自增列，表结构及其字段丶约束丶索引保持不变，执行速度比DELETE语句快。

6.在MySQL中，使用LIMIT子句实现查询结果的分页显示。

7.在使用子查询时需要注意，子查询语句必须放在括号中。

练习小作业：

1. 为prescription表添加主键约束：病人编号丶开处方的科室编号丶检查项目编号丶检查时间构成联合主键。
2. 为department_checkitem表添加外键约束：
   1. 主表checkitem表和从表department_checkitem表通过checkItemID字段建立主外键关联。
   2. 主表department表和从表department_checkitem表通过depID字段建立主外键关联。
3. 为病人(patient)表添加数据，请使用一条insert语句实现所有数据的插入。
4. 为科室(department)表添加数据，请使用一条insert语句实现所有数据的插入。
5. 为科室检查项目关系(department_checkitem)表添加数据，请使用一条insert语句实现所有数据的插入。
6. 为处方(prescription)表添加数据，请使用一条insert语句实现所有数据的插入。
7. 将姓名为"刘占波"的病人的密码修改为8765。
8. 将血常规检查的价格减少2元。
9. 查询2020年2月以前做过血常规检查的病人编号丶检查结果和检查时间。
10. 查询所有年龄大于20岁的病人，并按年龄从小到大排序，显示第3~5条记录的病人的姓名丶性别丶年龄丶手机号码和住址。
11. 查询所有病人的最大年龄丶最小年龄和平均年龄。
12. 查询姓名为"夏颖"的病人，第一次做血常规检查和最后一次做血常规检查的时间及检查结果。