欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

使用Java实现数据库编程—03 第三章 高级查询(一)

程序员文章站 2023-04-03 17:34:44
1、 修改表: (1)修改表名语法: ALTER TABLE <旧表名> RENAME [ TO ] <新表名>; 注意:其中[TO]为可选参数,使用与否不影响结果,仅修改表名,表结构不变; (2)添加字段语法: ALTER TABLE 表名 ADD 字段名 数据类型 [属性]; (3)修改字段语法 ......

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数据库把数据插入新表语法对比:

使用Java实现数据库编程—03 第三章 高级查询(一)

 

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 [位置偏移量, ]行数];

其中:

  1. 位子偏移量:指从结果集中第几条数据开始显示(第一条记录的位置偏移量是:0,第二条记录的位置偏移量是:1,............),

此参数可选,当省略是从第一条记录开始显示。

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:

    使用Java实现数据库编程—03 第三章 高级查询(一)

使用Java实现数据库编程—03 第三章 高级查询(一)

实现方法二:采用子查询实现:

 使用Java实现数据库编程—03 第三章 高级查询(一)

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

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

子查询语法:

    SELECT .... FROM 表1 WHERE 字段1 比较运算符(子查询);

其中:

子查询语句必须放置在一对圆括号内。

比较运算符包括:>,=,<,>=,<=。

外面查询称为父查询,圆括号中嵌入的查询称为子查询。

执行时,先执行子查询,在执行父查询,返回返回值。

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

     Eg2: 使用子查询替换表连接;

 使用Java实现数据库编程—03 第三章 高级查询(一)使用Java实现数据库编程—03 第三章 高级查询(一)

 

  实现方法一:采用表连接 :内连接(等值连接)

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中,当子查询跟随在=、!=、<、>、>=和<=时,不允许子查询返回多条记录。