JavaWeb学习——MySql使用

CREATE TABLE [IF NOT EXISTS] 数据表名称(
字段名1 列类型(长度) [修饰属性] [键/索引] [注释],
字段名2 列类型(长度) [修饰属性] [键/索引] [注释],
字段名3 列类型(长度) [修饰属性] [键/索引] [注释],
......
字段名n 列类型(长度) [修饰属性] [键/索引] [注释]
) [ENGINE = 数据表类型][CHARSET=字符集编码] [COMMENT=注释];

示例：创建学生表，表中有字段学号、姓名、性别、年龄和成绩

CREATE TABLE IF NOT EXISTS student(
`number` VARCHAR(30) NOT NULL PRIMARY KEY COMMENT '学号，主键',
name VARCHAR(30) NOT NULL COMMENT '姓名',
sex TINYINT(1) UNSIGNED DEFAULT 0 COMMENT '性别：0-男 1-女 2-其他',
age TINYINT(3) UNSIGNED DEFAULT 0 COMMENT '年龄',
score DOUBLE(5, 2) UNSIGNED COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

修改数据表

修改表名

ALTER TABLE 表名 RENAME AS 新表名;

示例：将student表名称修改为 stu

ALTER TABLE student RENAME AS stu;

增加字段

ALTER TABLE 表名 ADD 字段名列类型(长度) [修饰属性] [键/索引] [注释];

示例：在 stu 表中添加字段联系电话(phone)，类型为字符串，长度为11，非空

ALTER TABLE stu ADD phone VARCHAR(11) NOT NULL COMMENT '联系电话';

查看表结构

DESC 表名; -- 查看表结构

修改字段

-- MODIFY 只能修改字段的修饰属性
ALTER TABLE 表名 MODIFY 字段名列类型(长度) [修饰属性] [键/索引] [注释];
-- CHANGE 可以修改字段的名字以及修饰属性
ALTER TABLE 表名 CHANGE 字段名新字段名列类型(长度) [修饰属性] [键/索引] [注释];

示例：将 stu 表中的 sex 字段的类型设置为 VARCHAR ，长度为 2 ，默认值为 ' 男 ' ，注释为 " 性别，男，女，其他"。

ALTER TABLE stu MODIFY sex VARCHAR(2) DEFAULT '男' COMMENT '性别：男，女，其他';

示例：将 stu 表中 phone 字段修改为 mobile ，属性保持不变

ALTER TABLE stu CHANGE phone mobile VARCHAR(11) NOT NULL COMMENT '联系电话';

删除字段

ALTER TABLE 表名 DROP 字段名;

删除数据表

DROP TABLE [IF EXISTS] 表名;

4、列类型

在 MySQL 中，常用列类型主要分为数值类型、日期时间类型、字符串类型。

数值类型

日期时间类型

字符串类型

列类型修饰属性

三、DML学习

1、介绍

DML全称为 Data Manipulation Language ，表示数据操作语言。主要体现于对表数据的增删改操作。因此DML 仅包括 INSERT 、 UPDATE 和 DELEETE 语句。

2、insert语句

-- 需要注意，VALUES后的字段值必须与表名后的字段名一一对应
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值
n);
-- 需要注意，VALUES后的字段值必须与创建表时的字段顺序保持一一对应
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n);
-- 一次性插入多条数据
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值
n),(字段值1, 字段值2, ..., 字段值n), ... , (字段值1, 字段值2, ..., 字段值n);
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n), (字段值1, 字段值2, ..., 字段值
n), ..., (字段值1, 字段值2, ..., 字段值n);

示例：向课程表中插入数据

INSERT INTO course(`number`, name, score, `time`) VALUES (1, 'Java基础', 4, 40);
INSERT INTO course VALUES (2, '数据库', 3, 20);
INSERT INTO course(`number`, score, name, `time`) VALUES (3, 5, 'Jsp', 40);
INSERT INTO course(`number`, name, score, `time`) VALUES (4, 'Spring', 4, 5),(5,
'Spring Mvc', 2, 5);
INSERT INTO course VALUES (6, 'SSM', 2, 3), (7, 'Spring Boot', 2, 2);

3、update语句

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2, ..., 字段名n=字段值n] [WHERE 修改条件];

示例：将数据库的学分更改为4 ，学时更改为 15

UPDATE course SET score=4, `time`=15 WHERE name='数据库';

4、delete语句

DELETE FROM 表名 [WHERE 删除条件];

示例：删除课程表中课程编号为1 的数据

DELETE FROM course WHERE `number`=1;

5、TRUNCATE语句

-- 清空表中数据
TRUNCATE [TABLE] 表名;

示例：清空课程表数据

TRUNCATE course;

6、DELETE与TRUNCATE区别

        DELETE语句根据条件删除表中数据，而TRUNCATE语句则是将表中数据全部清空；如果DELETE语句要删除表中所有数据，那么在效率上要低于TRUNCATE语句。
        如果表中有自增长列，TRUNCATE语句会重置自增长的计数器，但DELETE语句不会。
        TRUNCATE语句执行后，数据无法恢复，而DELETE语句执行后，可以使用事务回滚进行恢复。

四、DQL语句

1、介绍

DQL全称是 Data Query Language ，表示数据查询语言。体现在数据的查询操作上，因此， DQL 仅包括 SELECT语句。

2、基本查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件

解释说明

ALL表示查询所有满足条件的记录，可以省略； DISTINCT 表示去掉查询结果中重复的记录 AS可以给数据列、数据表取一个别名。

示例：从课程表中查询课程编号小于5的课程名称

SELECT name FROM course WHERE `number`<5;

示例：从课程表中查询课程名称为"Java基础"的学分和学时

SELECT score, `time` FROM course WHERE name='Java基础';

3、条件查询

在Java 中，条件的表示通常都是使用关系运算符来表示，在 SQL 语句中也是一样，使用 >, <, >=, <=, != 来表示。不同的是，除此之外，SQL 中还可以使用 SQL 专用的关键字来表示条件。这些将在后面的 DQL 语句中详细讲解。

在Java 中，条件之间的衔接通常都是使用逻辑运算符来表示，在 SQL 语句中也是一样，但通常使用 AND 来表示逻辑与(&&) ，使用 OR 来表示逻辑或 (||)。

示例

WHERE time > 20 && time < 40; <=> WHERE time > 20 and time <40;

示例：从课程表查询课程名为NULL的课程信息

SELECT * FROM course WHERE name IS NULL;

示例：从课程表查询课程名不为NULL的课程信息

SELECT * FROM course WHERE name IS NOT NULL;

示例：从课程表查询学分在2~4之间的课程信息

SELECT * FROM course WHERE score BETWEEN 2 AND 4;

示例：从课程表查询课程名包含"V"的课程信息

SELECT * FROM course WHERE name LIKE '%v%';

示例：从课程表查询课程名以"J"开头的课程信息

SELECT * FROM course WHERE name LIKE 'J%';

示例：从课程表查询课程名以"p"结尾的课程信息

SELECT * FROM course WHERE name LIKE '%p';

示例：从课程表查询课程编号为1,3,5的课程信息

SELECT * FROM course WHERE `number` IN (1, 3, 5);

4、分组查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n;

分组查询所得的结果只是该组中的第一条数据。

示例：从学生表查询成绩在 80 分以上的学生信息并按性别分组

SELECT * FROM student WHERE score>80 GROUP BY sex;

示例：从学生表查询成绩在60~80之间的学生信息并按性别和年龄分组

SELECT * FROM student WHERE score BETWEEN 60 AND 80 GROUP BY sex, age;

5、聚合函数

COUNT() ：统计满足条件的数据总条数
示例：从学生表查询成绩在80分以上的学生人数

SELECT COUNT(*) total FROM student WHERE score>80;

SUM() ：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的总和

示例：从学生表查询不及格的学生人数和总成绩

SELECT COUNT(*) totalCount, SUM(score) totalScore FROM student WHERE
score<60;

AVG() ：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的平均值

示例：从学生表查询男生、女生、其他类型的学生的平均成绩

SELECT sex, AVG(score) avgScore FROM student GROUP BY sex;

MAX() ：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最大值

示例：从学生表查询学生的最大年龄

SELECT MAX(age) FROM student;

MIN() ：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最小值

示例：从学生表查询学生的最低分

SELECT MIN(score) FROM student;

6、分组查询结果筛选

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n HAVING 筛选条件;

示例：从学生表查询年龄在20~30之间的学生信息并按性别分组，找出组内平均分在74分以上的组

SELECT * FROM student WHERE age BETWEEN 20 AND 30 GROUP BY sex HAVING
avg(score)>74;

7、排序查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件 ORDER BY 字段名1 ASC|DESC，字段名2 ASC|DESC,..., 字段名n ASC|DESC;

ORDER BY 必须位于 WHERE 条件之后。

示例：从学生表查询年龄在 18~30 岁之间的学生信息并按成绩从高到低排列，如果成绩相同，则按年龄从小到大排列

SELECT * FROM student WHERE age BETWEEN 18 AND 30 ORDER BY score DESC, age ASC;

8、分页查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件 LIMIT 偏移量, 查询条数;

LIMIT 的第一个参数表示偏移量，也就是跳过的行数。

LIMIT 的第二个参数表示查询返回的最大行数，可能没有给定的数量那么多行。

示例：从学生表分页查询成绩及格的学生信息，每页显示 3 条，查询第 2 页学生信息

SELECT * FROM student WHERE score>=60 LIMIT 3, 3;

注意：

如果一个查询中包含分组、排序和分页，那么它们之间必须按照分组 -> 排序 -> 分页的先后顺序排列。

五、多表关系

1、主外键关联关系

如图所示，此时学生表和班级表并没有任何关系，然而实际上学生和班级是存在归属关系。可以在学生表中添加一个字段，表名该学生所属班级，该字段值使用的是班级表中的主键，在学生表中称之为外键。这样学生表中的所属班级（外键）与班级表中的编号（主键）就产生关联关系，这种关联关系称为主外键关联关系。

定义方式

DROP TABLE IF EXISTS cls;
CREATE TABLE cls(
number INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '班级编号，主键',
name VARCHAR(20) NOT NULL COMMENT '班级名称',
grade VARCHAR(20) NOT NULL COMMENT '年级'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='班级表';
DROP TABLE IF EXISTS student;
CREATE TABLE student(
number BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '学号，主键',
name VARCHAR(20) NOT NULL COMMENT '姓名',
sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
age TINYINT(3) DEFAULT 0 COMMENT '年龄',
cls_number INT(11) NOT NULL COMMENT '所属班级',
PRIMARY KEY(number),
-- 字段cls_number与cls表中的number字段相关联
FOREIGN KEY(cls_number) REFERENCES cls(number)
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

上面的参考代码就展现了一个主外键定义。

注意

2、约束

主键约束

-- 添加主键约束：保证数据的唯一性
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2, ..., 字段名n)
-- 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

外键约束

-- 添加外键约束
ALTER TABLE 表名1 ADD CONSTRAINT 外键名称 FOREIGN KEY(表名1的字段名) REFERENCES 表名
2(表名2的字段名);
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

唯一约束

-- 为字段添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE(字段名1, 字段名2, ..., 字段名n);
-- 删除字段的唯一约束
ALTER TABLE 表名 DROP KEY 约束名称;

非空约束

-- 为字段添加非空约束
ALTER TABLE 表名 MODIFY 字段名列类型 NOT NULL;
-- 删除字段非空约束
ALTER TABLE 表名 MODIFY 字段名列类型 NULL;

默认值约束

-- 为字段添加默认值
ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;
-- 删除字段的默认值
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;

自增约束

-- 为字段添加自增约束
ALTER TABLE 表名 MODIFY 字段名列类型 AUTO_INCREMENT;
-- 为字段删除自增约束
ALTER TABLE 表名 MODIFY 字段名列类型;

注意：这里展示的仅是约束的部分内容，如果想要深度了解，就得去多搜索了。

六、索引

1、介绍

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

2、作用

保证数据的准确性

提高检索速度

提高系统性能

3、类型

唯一索引（ UNIQUE ）：不可以出现相同的值，可以有 NULL 值

普通索引（ INDEX ）：允许出现相同的索引内容

主键索引（ PRIMARY KEY ）：不允许出现相同的值

全文索引（ FULLTEXT INDEX ）：可以针对值中的某个单词，但效率确实不敢恭维

组合索引： 实质上是将多个字段建到一个索引里，列值的组合必须唯一

4、索引的创建、查看、删除

-- 创建索引
ALTER TABLE 表名 ADD INDEX 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 创建全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名称;

5、使用索引时的注意事项

虽然索引大大提高了查询速度，但也会降低更新表的速度，比如对表进行INSERT,UPDATE 和 DELETE操作，此时，数据库不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。如果索引创建过多（尤其是在字段多、数据量大的表上创建索引），就会导致索引文件过大，这样反而会降低数据库性能。因此，索引要建立在经常进行查询操作的字段上。

不要在列上进行运算（包括函数运算），这会忽略索引的使用。

不建议使用like 操作，如果非使用不可，注意正确的使用方式。 like '% 查询内容 %' 不会使用索引，而like ' 查询内容 %' 可以使用索引。

避免使用IS NULL 、 NOT IN 、 <> 、 != 、 OR 操作，这些操作都会忽略索引而进行全表扫描。

七、多表查询

1、笛卡尔积

笛卡尔积又称为笛卡尔乘积，由笛卡尔提出，表示两个集合相乘的结果。

笛卡尔积与多表查询有什么关系呢？每一张表可以看做是一个数据的集合，多表关联串时，这些表中的数据就会形成笛卡尔积。

2、内连接

内连接相当于在笛卡尔积的基础上加上了连接条件。当没有连接条件时，内连接上升为笛卡尔积。

SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1 [INNER] JOIN 表2 [ON 连接条件];
SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1, 表2 [WHERE 关联条件 AND 查询条件];

示例：

SELECT COUNT(*) FROM stu INNER JOIN score ON stu.id=score.stu_id;
SELECT COUNT(*) FROM stu, score WHERE stu.id=score.stu_id;

3、外连接

外连接涉及到两张表：主表和从表，要查询的信息主要来自于哪张表，哪张表就是主表。

外连接查询的结果为主表中所有的记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示 null 。

外连接查询的结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录

外连接分为左外连接和右外连接两种。左外连接使用LEFT JOIN 关键字， LEFT JOIN 左边的是主表；右外连接使用RIGHT JOIN 关键字， RIGHT JOIN 右边的是主表。

4、左外连接

SELECT 字段名1, 字段名2, ..., 字段名n FROM 主表 LEFT JOIN 从表 [ON 连接条件];

示例：

SELECT * FROM stu a LEFT JOIN score b ON a.id=b.stu_id WHERE score IS NULL;

5、右外连接

SELECT 字段名1, 字段名2, ..., 字段名n FROM 从表 RIGHT JOIN 主表 [ON 连接条件];

示例：

SELECT * FROM stu a RIGHT JOIN score b ON a.id=b.stu_id;

八、子查询

1、介绍

子查询就是嵌套在其他查询中的查询。因此，子查询出现的位置只有3 种情况：在 SELECT...FROM 之间、在FROM...WHERE 之间、在 WHERE 之后。

2、SELECT ... FROM之间

执行时机是在查询结果出来之后

查询 stu 表所有学生信息，并将性别按男、女、其他展示

SELECT

        id,

        `name` ,

        ( SELECT text FROM dict WHERE type = 'sex' AND value = sex ) sex,

        birthday,

        class

FROM

        stu;

3、 FROM ... WHERE 之间

执行时机是一开始就执行

查询年龄与 Java 成绩都与汤辰宇的年龄与 Java 成绩相同的学生信息

SELECT c. * , d. * FROM stu c

INNER JOIN

        score d ON c .id = d .stu_id

INNER JOIN

        ( SELECT

                TIMESTAMPDIFF ( YEAR , a .birthday ,NOW ()) age,

                b .score

        FROM stu a INNER JOIN score b ON a .id = b .stu_id

        WHERE a .name = ' 汤辰宇 '

        AND b .course = 'Java' ) e

ON TIMESTAMPDIFF ( YEAR , c .birthday ,NOW ()) = e .age AND d .score = e .score

WHERE d .course = 'Java' ;

4、 WHERE 之后

查询Java成绩最高的学生信息

SELECT a. * , b. * FROM stu a INNER JOIN score b ON a .id = b .stu_id

WHERE b .score = ( SELECT MAX ( score ) FROM score WHERE course = 'Java' )

AND b .course = 'Java' ;

九、事务

1、介绍

事务( Transaction) 是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行 , 要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

2、事务的特性( ACID)

原子性（Atomicity ）

事务的各元素是不可分的（原子的） , 它们是一个整体。要么都执行，要么都不执行。

一致性（Consistency ）

当事务完成时，必须保证所有数据保持一致状态。当转账操作完成时，所有账户的总金额应该保持

不变，此时数据处于一致性状态；如果总金额发生了改变，说明数据处于非一致性状态。

隔离性（Isolation ）

对数据操作的多个并发事务彼此独立，互不影响。比如张三和李四同时都在进行转账操作，但彼此

都不影响对方。

持久性（Durability ）

对于已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。

十、常用函数

1、常用数学函数

2、常用字符串函数

练习

查询计科和软工各有多少人

SELECT LEFT(class, 2), COUNT(*) FROM stu GROUP BY LEFT(class, 2);

查询名字有4个字的学生信息

SELECT * FROM stu WHERE CHAR_LENGTH(`name`)=4;

查询成绩能够被10整除的考试信息

SELECT * FROM score WHERE MOD(score, 10)=0;

3、日期和时间函数

练习

查询年龄在 20 岁以上的学生信息

SELECT * FROM stu WHERE TIMESTAMPDIFF(YEAR, birthday, NOW()) > 20;

查询今天过生日的学生信息

SELECT * FROM stu WHERE MONTH(birthday)=MONTH(NOW()) AND DAYOFMONTH(birthday)=DAYOFMONTH(NOW());

查询本周过生日的学生信息

SELECT * FROM stu WHERE RIGHT(birthday, 5) > RIGHT(DATE_FORMAT(ADDDATE(NOW(), -DAYOFWEEK(NOW())), '%Y-%m-%d'), 5) AND RIGHT(birthday, 5) <=
RIGHT(DATE_FORMAT(ADDDATE(NOW(),7-DAYOFMONTH(NOW())), '%Y-%m-%d'), 5);

十一、条件判断函数

1、IF(条件, 表达式1, 表达式2)

如果条件满足，则使用表达式 1, 否则使用表达式 2

示例：将学生成绩展示为及格和不及格

SELECT id,stu_name,course, IF(score>=60, '及格','不及格') score FROM score;

2、 CASE WHEN

语法

CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END;

如果条件1 满足，则使用表达式 1 ；【如果条件 2 满足，则使用表达式 2 ， ... 】否则，使用表达式 n 。相当于Java 中的多重 if..else 语句。

示例

SELECT (CASE WHEN (course = 'Java') THEN score ELSE 0 END) Java FROM score;

行转列：查询每位学生的各课程成绩

SELECT
        stu_name,
        course,
        MAX(CASE WHEN (course = 'Java') THEN score ELSE 0 END) Java,
        MAX(CASE WHEN (course = 'Html') THEN score ELSE 0 END) Html,
        MAX(CASE WHEN (course = 'Jsp') THEN score ELSE 0 END) Jsp,
        MAX(CASE WHEN (course = 'Spring') THEN score ELSE 0 END) Spring
FROM score
GROUP BY stu_name;

3、 CASE ... WHEN语句

语法

CASE 表达式 WHEN 值1 THEN 表达式1 [WHEN 值2 THEN 表达式2 ...] ELSE 表达式n END;

如果表达式的执行结果为值1 ，则使用表达式 1 ；【执行结果为值 2 ，则使用表达式 2 ， ... 】否则，使用表达式n 。相当于 Java 中的 switch 语句。

示例

SELECT (CASE course WHEN 'Java' THEN score ELSE 0 END) Java FROM score;

行转列：查询每位学生的各课程成绩

SELECT
        stu_name,
        course,
        MAX(CASE course WHEN 'Java' THEN score ELSE 0 END) Java,
        MAX(CASE course WHEN 'Html' THEN score ELSE 0 END) Html,
        MAX(CASE course WHEN 'Jsp' THEN score ELSE 0 END) Jsp,
        MAX(CASE course WHEN 'Spring' THEN score ELSE 0 END) Spring
FROM score
GROUP BY stu_name;