高级查询—— 使用Java实现数据库编程

一、修改表

1. 修改表名

alter table <旧表名> rename <新表名>;

2. 添加字段

alter table 表名 add 字段名 数据类型 [属性];

3. 修改字段

alter table 表名 change 原字段名 新字段名 数据类型 [属性];

4. 删除字段

alter table 表名 drop 字段名;

5. 添加主键

alter table 表名 add constraint 主键名 primary key 表名（主键字段名）;

6. 添加外键约束

alter table 从表 add constraint 外键名 foreign key (从表的外键) references 主表 （主键）;

二、DML语句 数据的赠删改查

1. 插入数据
   1. 插入单行数据

insert into 表名 （字段1，字段2，......） values (值1，值2，......);

​ 2. 插入多行数据

insert into 表名 （字段1，字段2，......） values (值列表1)，(值列表1)，...... ;

​ 3.将查询的结果插入新表

insert into 新表 （字段1，字段2，......）
select 字段1，字段2,......
from 原表

create table 新表 (select 字段1，字段2,...... from 原表) ;

​ 第二个无需提前创建

2. 更新数据记录

update 表名 set 字段1=值1，字段2=值2，......，字段n=值n where 条件;

3. 删除数据记录

delete from 表名 where 条件;

​ 使用truncate删除表中所有行

truncate table 表名;

​ truncate 语句实现删除表中所有的数据，删除后将重置自增列，表结构及字段，约束，索引保持不变，执行速度比 delete 语句快

4. 查询

select  <目标列名序列> --需要哪些列
from <表名> --来自哪张表
[where <行选择条件>]
[group by <分组依据列>]
[having <组>]
[order by <排序依据列>]
[limit [位置偏移量，] 行数]

select 子句用于指定输出的字段；

from 子句用于指定数据的来源；

where子句用于指定数据的选择条件；

group by子句用于对检索到的记录进行分组；

having 子句用于指定组的选择条件；

order by 子句用于对查询的结果进行排序；

limit 位置偏移量值从结果集中的第几天数据开始显示，第一条是0 ，第二条是1 以此类推 行数值显示记录的条数

以上子句中，SELECT 子句和FROM子句是必需的，其它是可选的。

三、常用函数

1. 聚合函数

函数名	作用
avg ( )	返回某字段的平均值
count ( )	返回某字段的行数
max ( )	返回某字段的最大值
min ( )	返回某字段的最小值
sum ( )	返回某字段的和

2. 字符串函数

函数名	作用	举例
concat（str1,str1…strn）	字符串连接	SELECT CONCAT(‘My’,‘S’,‘QL’); 返回：MySQL
insert (str,pos,len,newstr)	字符串替换	SELECT INSERT( ‘这是SQL Server数据库’, 3,10,‘MySQL’); 返回：这是MySQL数据库
lower(str)	将字符串转成小写	SELECT LOWER(‘MySQL’);返回：mysql
upper(str)	将字符串转为大写	SELECT UPPER(‘MySQL’);返回：MYSQL
substring(str,num,len)	字符串截取	SELECT SUBSTRING( ‘JavaMySQLOracle’,5,5); 返回：MySQL

3. 时间日期函数

函数名	作用	举例
curdate( )	获取当前日期	SELECT CURDATE();返回：2016-08-08
curtime( )	获取当前时间	SELECT CURTIME();返回：19:19:26
now( )	获取当前日期和时间	SELECT NOW();返回：2016-08-08 19:19:26
week(date)	返回日期date为一年中的第几周	SELECT WEEK(NOW());返回：26
yeat(date)	返回日期date的年份	SELECT YEAR(NOW());返回：2016
hour(time)	返回时间time的小时值	SELECT HOUR(NOW());返回：9
minute(time)	返回时间time的分钟值	SELECT MINUTE(NOW());返回：43
datediff(date1,date2)	返回日期参数date1和date2之间相隔的天数	SELECT DATEDIFF(NOW(), ‘2008-8-8’);返回：2881
adddate(date,n)	计算日期参数date加上n天后的日期	SELECT ADDDATE(NOW(),5);返回：2016-09-02 09:37:07

4. 数学函数

函数名	作用	举例
ceil(x)	返回大于或等于数值x的最小整数	SELECT CEIL(2.3) 返回：3
floor(x)	返回小于或等于数值x的最大整数	SELECT FLOOR(2.3) 返回：2
fand()	返回0~1间的随机数	SELECT RAND() 返回：0.5525468583708134

四、子查询

1. 子查询在 where 语句中一般用法如下

select ...... from 表1 where 字段1 比较运算符（子查询）;

​ **比较运算符包括 > = < >= <= **

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

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

2. in 和 not int 子查询

   1. in 子查询

   in 后面的子查询可以返回多条记录，用于限制筛选范围。

    1. 常用IN替换等于（＝）的子查询
    	2. IN后面的子查询可以返回多条记录
   

   2. not in 子查询

      not 就是不包含

五、一些例子

1. 修改表名

ALTER TABLE student RENAME student2;

2. 添加字段

ALTER TABLE student2 ADD test VARCHAR(100) DEFAULT '测试';

3. 修改字段

ALTER TABLE student2 CHANGE test demo INT;

4. 删除字段

ALTER TABLE student2 DROP demo;

5. 添加主键

ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY student(studentNo);

6. 添加外键

ALTER TABLE student ADD CONSTRAINT fk_student_grade FOREIGN KEY (gradeId) REFERENCES grade (gradeID);

7. 查询2016年2月17日考试前5名的学员的学号和分数

SELECT studentNo,studentResult FROM result WHERE examDate='2016-2-17' LIMIT 5;

8. 将所有女学生按年龄从大到小排序，从第2条记录开始显示6名女学生的姓名、年龄、出生日期、手机号信息

SELECT studentName,FLOOR(DATEDIFF(CURDATE(),bornDate)/365),bornDate,phone FROM student WHERE sex='女' ORDER BY DATEDIFF(CURDATE(),bornDate) ASC LIMIT 1,6;

9. 按出生年份分组统计学生人数，将各组中人数达到2人的年份和人数显示出来

SELECT COUNT(*),YEAR(bornDate) FROM student GROUP BY YEAR(bornDate) HAVING COUNT(*)>1;

10. 查询参加2016年2月17日考试的所有学员的最高分、最低分、平均分

SELECT MAX(studentResult),MIN(studentResult),AVG(studentResult) FROM result WHERE examDate='2016-2-17';

11. 查询参加最近一次Logic Java考试成绩的学生的最高分和最低分

SELECT MAX(studentResult),MIN(studentResult) FROM result 
WHERE examDate=(
	SELECT MAX(examDate) FROM result WHERE subjectNo=(
		SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'
	)
) AND subjectNo=(
	SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'
)

12. 查询S1学期开设的课程

SELECT subjectName FROM `subject` WHERE gradeID = 
	(
	SELECT gradeID FROM grade WHERE gradeName='S1'
	) ;

13. 查询未参加“HTML”课程最近一次考试的在读学生名单

SELECT * FROM student WHERE studentNo NOT IN 
(
	SELECT studentNo FROM result 
	WHERE examDate=
	(
		SELECT MAX(examDate) FROM result WHERE subjectNo=
		(
			SELECT subjectNo FROM `subject` WHERE subjectName='HTML'
		)
	)
	AND subjectNo = 
	(
		SELECT subjectNo FROM `subject` WHERE subjectName='HTML'
	)
)
 AND gradeId=
 (
		SELECT gradeId FROM `subject` WHERE subjectName='HTML'
)

六、总结

七、exists 和 not exists 子查询

​ 用来检测数据库对象是否存在

1. exists子查询

   1. 可以使用 exists 语句判断数据库对象是否存在，返回值是 true 或 false

      #例如 如果存在数据表temp 则先删除它，然后重写创建。
      drop table if exists temp;
      

   2. exists 也可以作为 where 语句的子查询

      select ..... from 表名 where exists(子查询);
      

      exists 关键字后面的参数是一个任意的子查询，如果该子查询有返回行，则 exists 子查询的结果为 true 此时在执行外层查询语句。如果子查询没有返回行，则 exists 查询的结果为 false ，此时外层语句不再查询。

2. not exists 查询

   1. exists 和 in 一样，同样允许添加 not 关键词实现取反操作，not exists 表示不存在。
   2. exists 和 not exists 的结果只取决于是否有返回记录，不取决于这些记录的内容，所有 exists 或 not exists 子查询后 select 语句中的字段列表通常是无关紧要的。

八、子查询注意事项

1. 子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置。

   1. 嵌套在父查询SELECT语句的子查询可包括
      select 子句
      from 子句
      where 子句
      group by 子句
      having 子句

2. 只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

   1. 多层嵌套的子查询最终数据集质保含父查询的 select 子句出现的字段。而子查询的输出结果通常 作为其外层子查询数据源或用于数据判断匹配。

九、总结