使用Java实现数据库编程—03 第三章 高级查询（一）
1、 修改表：
|
（1）修改表名语法： ALTER TABLE <旧表名> RENAME [ TO ] <新表名>； 注意：其中[TO]为可选参数，使用与否不影响结果，仅修改表名，表结构不变； |
|
（2）添加字段语法： ALTER TABLE 表名 ADD 字段名 数据类型 [属性]； |
|
（3）修改字段语法： ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性]； 其中，”原字段名“指修改前的字段名，“ 新字段名”指修改后的字段名，“数据类型”指修改后的数据类型， 如果不需要修改数据类型，则和原数据类型保持一致，但“数据类型”不能为空。 注意：由于不同的类型的数据储存方式和长度不同，修改数据类型可能会影响数据表中已有的数据，因此，此种情况下不应轻易修改数据类型。 |
|
（4）删除字段语法： ALTER TABLE 表名 DROP字段名； |
|
Eg： DROP TABLE IF EXISTS `demo01`; #创建表 CREATE TABLE `demo01`( `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(8) NOT NULL ); #修改表名 ALTER TABLE `demo01` RENAME `demo02`; #添加字段 ALTER TABLE ` demo02 ` ADD `password` VARCHAR(32) NOT NULL; #修改字段 ALTERTABLE ` demo02 ` CHANGE `name` `username`CHAR(10) NOT NULL; #删除字段 ALTERTABLE ` demo02 ` DROP `password`; |
2、 添加主外键：
|
（1）添加主键约束语法： ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名（主键字段）; Eg：设置grade表中gradeId字段为主键 ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARY KEY `grade`(`gradeId`); |
|
（2）添加外键约束语法： ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY （外键字段） REFE RENCES 关联表名（关联字段）； Eg：将student表的gradeId字段和grade表的gradeId字段建立外键关联： ALTER TABLE `student` ADD CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) REFERENCES `grade` (`gradeId`); |
|
提示：在MySQL中，InnoDB储存类型的表支持外键， MyISAM储存类型的表不支持外键，因此对于MySISAM储存类型的表，也可以通过建立逻辑关联的方式保证数据的完整性和一致性， 这种关联实际上是依照设计之初就定义好的固有逻辑，按照这种固有逻辑存取数据即可，不需要在数据库层面进行“硬绑定”。 |
3、 DML(数据操作语言)：
|
（1）插入数据记录： （A）插入单行数据记录语法： INSERT INTO 表名 [（字段名列表）] VALUES（值列表）; Eg: INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`) VALUES('123','黄小平',1,'13956799999','1996-5-8'); 其中： 1.表的字段名是可选的，如果省略，则依次插入所有字段。 2.多个列表和多个值之间使用逗号分隔。 3.值列表必须和字段名列表数量相同，且数据类型相符。 4.如果插入的是表中部分数据，字段名列表必须填写。 （B）插入多行数据语法：在MySQL中INSERT语句支持一次插入多条记录，插入时可指定多个值列表，每个值列表之间逗号隔开。 INSERT INTO 新表（字段名列表）VALUES（值列表1）,（值列表2）,……（值列表n）； 注意:插入数据时需要指定具体字段名。 Eg： INSERT INTO `subject`(`subjectNo`,`subjectName`,`classHour`,`gradeID`) VALUES(1,'Logic Java',220,1),(2,'HTML',160,1),(3,'Java OOP',230,2);
注意：为避免表结构发生变化引发的错误，建议插入数据时写明具体字段名！ 插入时间类型也需要 ‘ ’ 。 |
|
（2）将查询结果插入到新表语法： CREATE TABLE 新表（SELECT 字段1，字段2，..........FROM 原表）； 注意：新表无需事先创建，如新表已存在，将会报错！ |
|
（3）更新数据记录语法： UPDATE 表名 SET 字段1=值，字段2=值，。。。。字段n=值[WHERE 条件]； |
|
（4）删除数据记录语法： DELETE FROM 表名 [WHERE 条件]； 除此之外，也可以使用TRUNCATE语句删除表中所有行。语法： TRUNCATE TABLE 表名； 注意：TRUNCATE 语句实现删除表中所有数据，删除后将重置自增列，表结构及其字段、约束、索引保持不变执行速度比DELETE语句快。 |
|
SQL Sever数据库与MySQL数据库把数据插入新表语法对比：
|
4、数据查询：
●查询回顾：SQL Server中查询语句在MySQL中同样支持；
|
SQL Service中的数据查询语法： SELECT<字段名列表> FRMO<表名或视图> [WHERE <查询条件>] [GROUP BY<分组字段名>] [HAVING<条件>] [OGDER BY<排序的字段名>[ASC 或 DESC]]
Eg： SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` FROM `student` WHERE `gradeId` = 1 ORDER BY studentNo; |
|
|
其中： 1.字段名列表为要查询的字段名、各字段之间使用逗号分隔、若查询表中所有字段、则使用“* ”号表示。 2.FROM后的表名为要查询的数据的来源，可以单个可以多个。 3.WHERE子句为可选项，指定查询的条件。 4.GROUP BY子句表明查询出来的数据按指定字段进行分组。 5.HAVING子句用于筛选组。 6.OGDER BY子句指定按什么顺序显示查询出来的数据，什么字段的升序（ASC）或降序（DESC） |
●LIMIT子句：MySQL查询语句中使用LIMIT子句限制结果集；
|
SELECT <字段名列表> FROM <表名或视图> [WHERE <查询条件>] [GROUP BY <分组的字段名>] [ORDER BY <排序的列名> [ASC 或DESC]] [LIMIT [位置偏移量, ]行数]; 其中：
此参数可选，当省略是从第一条记录开始显示。 2.行数：指显示记录的条数。 |
5、常用函数：
|
聚合函数： AVG（）； 作用：返回某字段的平均值。 COUNT（）； 作用：返回某字段的行数。 MAX（）； 作用：返回某字段的最大值。 MIN（）； 作用：返回某字段的最小值。 SUM（）； 作用：返回某字段的和。 |
数学函数： CEIL（x）；作用：返回大于或等于数值 x 的最小整数。 举例：SELECT CEIL （2.3）；返回：3 FLOOR（x）；作用：返回小于或等于数值 x 的最大整数 举例：SELECT FLOOR（2.3）；返回：2 RAND（）；作用：返回0~1的随机数。 举例：SELECT RAND（）；返回：0.59464161 |
- ●字符串函数：
|
CONCAT（str1,str2,...,strln）；作用：链接字符串str1,str2.....strln为一个完整的字符串。 举例：SELECT CONCAT（"My"，"S"，"QL")； 返回：MySQl INSERT（str,pos,len,newstr）；作用：将字符串 str 从 pos 位置开始，len 个字符长的子串替换为字符串 newstr 举例：SELECT INSERT（'这是SQL Server 数据库'，3，10，'MySQL'）； 返回：这是MySQl数据库 LOWER（str）;作用：将字符串 str 中所有字符变为小写 举例：SELECT LOWER('MySQL'); 返回：mysql UPPER（str）;作用：将字符串 str 中所有字符变为大写 举例：SELECT UPPER('MySQL'); 返回：MYSQL SUBSTRING（str，num，len）;作用：返回字符串 str 的第num 个位置开始长度为len 的子字符串 举例：SELECT SUBSTRING('JavaMySQLOracle'，5，5); 返回：MySQL |
- ●时间日期函数：
|
CURDATE（）；作用：获取当前日期 举例：SELECT CURDTE（）； 返回：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 YEAR（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（），‘2018-8-8’）；返回：2881 ADDDATE（date，n）；作用：计算日期参数 date 加上 n 天后的日期。 举例：SELECT ADDDATE（NOW（），5）； 返回：2016-09-02 09：37：07 |
6、子查询：以查询为查询条件的查询
Eg1：
|
eg： |
|
|
实现方法二：采用子查询实现： 1、 子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询 2、子查询在WHERE语句中的一般用法 子查询语法： SELECT ．．．． FROM 表１ WHERE 字段１ 比较运算符（子查询）； 其中： 子查询语句必须放置在一对圆括号内。 比较运算符包括：＞，＝，＜，＞＝，＜＝。 外面查询称为父查询，圆括号中嵌入的查询称为子查询。 执行时，先执行子查询，在执行父查询，返回返回值。 注意;将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个 |
|
Eg2: 使用子查询替换表连接；
|
实现方法一：采用表连接 ：内连接（等值连接） 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` = 'Logic Java'; |
|
|
实现方法二：采用子查询 SELECT `studentName` FROM `student` WHERE `studentNo` = ( SELECT `studentNo` FROM `result` INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo WHERE `studentResult`=60 AND `subjectName`='Logic Java' ); 1、子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据 2、表连接更适合于查看多表的数据 |
7、IN和NOT IN 子查询：
●IN子查询：使用=，>等比较运算符时，要求子查询只能返回，一条或空的记录，
在MySQL中，当子查询跟随在=、！=、<、>、>=和<=时，不允许子查询返回多条记录。