MySQL数据库（二）

第一章、DDL语句

一、create：创建表

在SQL中，创建表是用于定义和存储数据的基础操作之一。您可以使用CREATE TABLE语句创建一个新的数据库表，并在其中定义表的结构（列名、数据类型等）。

1、语法

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ... //最后一个字段后不用加 , 逗号
);

• table_name：要创建的表的名称。表名在数据库当中一般建议以：t_ 或者tbl_ 开始
• column1, column2, column3, ...：表的列名。您可以为表定义多个列，每个列都需要指定一个列名和数据类型。
• datatype：每个列的数据类型，用于指定列中存储的数据的类型。数据类型可以是文本、数字、日期等，具体取决于您的需求和数据库支持的数据类型。
• constraint：对字段的约束

二、SQL语言中的数据类型

        当在数据库中创建表时，需要为每个字段指定适当的数据类型。不同的数据库管理系统（DBMS）可能支持略有不同的字段类型，但以下是常见的表字段类型及其详细解释：

1、整数类型（Integer Types）

• INT 或 INTEGER
  • 用于存储整数值，通常为带符号的32位整数，范围约为 -2,147,483,648 到 2,147,483,647。
  • 类似于java中的int
• SMALLINT
  • 用于存储较小范围的整数值，通常为带符号的16位整数，范围约为 -32,768 到 32,767
  • 类似于java中的short
• BIGINT
  • 用于存储较大范围的整数值，通常为带符号的64位整数，范围约为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
  • 类似于java中的long

2、浮点数和小数类型（Floating-Point and Decimal Types）

• FLOAT：用于存储单精度浮点数。
  • 类似于java中的float
• DOUBLE或REAL：用于存储双精度浮点数。
  • 类似于java中double
• DECIMAL(precision, scale)：用于存储精确的十进制数值，其中precision表示总位数，scale表示小数点后的位数。例如，DECIMAL(10, 2) 表示最多10位数，其中2位为小数。

3、字符串类型（Character Types）

sql语句中字符串使用单引号括起来

注意：标准sql语句中要求字符串使用单引号括起来，虽然mysql支持双引号，尽量别用，因为不通用，在oracle数据库中就不行了

• CHAR(n)
  • 用于存储固定长度的字符串，n 表示字符的最大长度。例如，CHAR(10) 表示最多包含10个字符，不足10个字符会用空格填充。
  • 类似于java中的String
• VARCHAR(n)
  • 用于存储可变长度的字符串，n 表示字符的最大长度。例如，VARCHAR(255) 表示最多包含255个字符，实际使用的存储空间取决于字符串的长度。
  • 类似于java中的StringBuilder/StringBuffer

3.1、char和varchar的选择

char的执行效率较高，varchar的执行效率较低，因为varchar底层要进行if语句判断，根据传入的值进行操作，char底层不要进行判断，直接分配内存空间

在实际的开发中，

• 当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别，生日等都是采用char
• 当某个字段的数据长度不确定得，例如：简介，姓名等都是采用varchar

4、日期与时间类型（Date and Time Types）

sql中的日期与时间类型 使用 单引号 括起来

• DATE
  • 用于存储日期，格式通常为 YYYY-MM-DD
  • 类似于java中的java.sql.Date
• TIME：用于存储时间，格式通常为 HH:MM:SS。
• DATETIME 或 TIMESTAMP：用于存储日期和时间的组合，格式通常为 YYYY-MM-DD HH:MM:SS。

5、布尔类型（Boolean Type）

• BOOL 或 BOOLEAN
  • 用于存储真（True）或假（False）的布尔值。
  • 类似于java中的boolean

6、二进制类型（Binary Types）

• BLOB：
  • 二进制大对象  Binary Large OBject
  • 用于存储二进制数据，例如图片、音频或视频等。
  • 类似于java中的Object
• CLOB
  • 字符大对象  Character Large OBject
  • 存储较大文本，比如：可以存储4G的字符串
  • 类似于java中的Object

6.1、BLOB和CLOB类型的使用

7、自动增量类型（Auto-Increment Types）

通常用于主键列，自动递增，每次插入新行时自动增加值。

• AUTO_INCREMENT（MySQL）
• SERIAL（PostgreSQL）
• IDENTITY（SQL Server）

8、JSON 类型

• JSON：用于存储 JSON（JavaScript Object Notation）格式的数据。

9、枚举类型

• ENUM：用于定义一组预定义的值，只能从这组值中选择一个。

        这些是一些常见的表字段类型，具体数据库可能还会有其他类型或扩展类型。在创建表时，选择合适的字段类型对于确保数据的正确性、节省存储空间和提高数据库性能非常重要。

三、drop：删除表

在SQL中，DROP语句用于删除数据库中的对象，通常是表、索引、视图、存储过程、函数或整个数据库。DROP语句是一个强有力的操作，因为它会永久删除对象，因此在使用DROP语句之前务必小心谨慎，并确保您了解它的影响。以下是DROP语句的一般语法和一些重要的概念：

DROP OBJECT_TYPE [IF EXISTS] object_name;

下面是对上述语法的详细说明：

• OBJECT_TYPE：指定要删除的对象类型，例如TABLE（表）、INDEX（索引）、VIEW（视图）、PROCEDURE（存储过程）、FUNCTION（函数）等。
• IF EXISTS（可选）：如果指定了此选项，并且要删除的对象不存在，则不会引发错误。这是一种防止意外删除的方式。
• object_name：指定要删除的对象的名称。

以下是一些DROP语句的示例：

DROP TABLE customers;

这将永久删除名为"customers"的表以及其包含的所有数据。

四、约束 Constraint

1、概述

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性，有效性，完整性

常见的约束：

• 主键约束：primary key，约束的字段既不能为NULL，也不能重复（简称PK）
• 外键约束：foreign key，（检查FK）
• 非空约束：not null，约束的字段不能为NULL
• 唯一约束：unique，约束的字段不能重复
• 默认约束：default
• 检查约束：check，注意Oracle数据库有check约束，但是MySQL没有，目前MySQL不支持该约束

1、非空约束：not null

限制字段的值不能为null，添加此约束后，则不能添加default null约束

not null约束只有列级约束，没有表级约束

drop table if exists t_user;

create table t_user(
	id int,
	username varchar(255) not null,
	password varchar(255)
);

//以下SQL报错：ERROR 1364 (HY000): Field 'username' doesn't have a default value
insert into
	t_user (id, password)
values
	(1, "123");

mysql> desc t_user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(255) | NO   |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

insert into
    t_user
values
    (1, 'zhangsan', '123');

mysql> select * from t_user;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | zhangsan | 123      |
+------+----------+----------+
1 row in set (0.00 sec)

2、唯一性约束：unique

• 唯一约束确保某列或一组列的值在表中是唯一的，但不要求列的值不能为空。
• 唯一约束可用于创建唯一性索引

唯一性约束修饰的字段具有唯一性，不能重复，但可以为NULL

drop table if exists t_user;

create table t_user(
	id int default null,
	username varchar(255) unique
);

insert into
	t_user (id, username)
values
	(1, 'zhangsan');

//该条语句报错：
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_user.username'
insert into
	t_user (id, username)
values
	(1, 'zhangsan');

insert into t_user(id) values (2);
insert into t_user(id) values (3);
insert into t_user(id) values (4);

mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
|    1 | zhangsan |
|    2 | NULL     |
|    3 | NULL     |
|    4 | NULL     |
+------+----------+
4 rows in set (0.00 sec)

 2.1、unique和unique()的区别

unique：列级约束，对某个列添加约束

unique()：表级约束，多个字段联合起来添加1个约束

drop table if exists t_user;

create table t_user(
    id int,
    usercode varchar(255),
    username varchar(255),
    unique(usercode, username) //多个字段联合起来添加1个约束unique  表级约束
);

insert into t_user values (1, '111', 'zs');
insert into t_user values (2, '111', 'ls');
insert into t_user values (3, '222', 'zs');

select * from t_user;
+------+----------+----------+
| id   | usercode | username |
+------+----------+----------+
|    1 | 111      | zs       |
|    2 | 111      | ls       |
|    3 | 222      | zs       |
+------+----------+----------+
3 rows in set (0.00 sec)


insert into t_user values (1, '111', 'zs'); //ERROR 1062 (23000): Duplicate entry '111-zs' for key 't_user.usercode'


drop table if exists t_user;

create table t_user(
    id int,
    usercode varchar(255) unique, //列级约束
    username varchar(255) unique
);


insert into t_user values (1, '111', 'zs');
insert into t_user values (2, '111', 'ls'); //ERROR 1062 (23000): Duplicate entry '111' for key 't_user.usercode'

3、主键约束：primary key

• 主键是一个唯一标识表中每一行的列或一组列。主键确保表中的每一行都具有唯一的标识符。
• 主键约束要求列的值不为空（NOT NULL）且唯一（UNIQUE）。

一张表的主键约束只能有一个

drop table if exists t_user;

create table t_user(
    id int primary key, //列级约束
    username varchar(255),
    email varchar(255)
);

insert into t_user(id, username, email) values (1, 'zs', 'zs@123.com');
insert into t_user(id, username, email) values (2, 'ls', 'ls@123.com');
insert into t_user(id, username, email) values (3, 'ww', 'ww@123.com');

select * from t_user;
+----+----------+------------+
| id | username | email      |
+----+----------+------------+
|  1 | zs       | zs@123.com |
|  2 | ls       | ls@123.com |
|  3 | ww       | ww@123.com |
+----+----------+------------+
3 rows in set (0.00 sec)

insert into t_user(id, username, email) values (1, 'jzq', 'jzq@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 't_user.PRIMARY'

insert into t_user(username, email) values ('jzq', 'jzq@123.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

根据以上的测试得出：id是主键 primary key，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复
主键的特点：不能为NULL，也不能重复

 3.1、主键的作用

主键相关的术语：

• 主键约束：primary key
• 主键字段：id字段添加primary key之后，id叫做主键字段
• 主键值：id字段中的每一个值都是主键值

标的设计三范式中有要求，第一范式就要求任何一张表都应该有主键

主键的作用：

主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样）

3.2、主键的分类

根据主键字段的字段数量来划分

• 单一主键
  • 推荐的，常用的
• 复合主键
  • 多个字段联合起来添加一个主键约束
  • 复合主键不建议使用，因为复合主键违背三范式

根据主键性质来划分

• 自然主键
  • 推荐的，常用的
  • 主键值最好就是一个和业务没有任何关系的自然数
• 业务主键
  • 不推荐使用
  • 主键值和系统的业务挂钩，例如：拿着银行卡的卡号作为主键，拿着身份证号码作为主键
  • 最好不要拿着和业务挂钩的字段作为主键，因为以后得业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复

3.3、一张表的主键约束只能有一个

一张中表中的primary key 只能有一个

3.4、使用表级约束方式定义主键

drop table if exists t_user;

create table t_user(
    id int,
    username varchar(255),
    primary key(id)
);

insert into t_user(id, username) values(1, 'zs');
insert into t_user(id, username) values(2, 'ls');
insert into t_user(id, username) values(3, 'ww');

select * from t_user t;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
|  3 | ww       |
+----+----------+
3 rows in set (0.00 sec)

insert into t_user(id, username) values(3, 'zl');
ERROR 1062 (23000): Duplicate entry '3' for key 't_user.PRIMARY'

以下内容演示复合主键，不需要掌握
drop table if exists t_user;

create table t_user(
    id int,
    username varchar(255),
    password varchar(255),
    primary key(id, username)
);

3.5、主键自增：auto_increment

auto_increment修饰的字段自动维护一个自增的数字，从1开始，以1递增

提示：Oracle中也提供了一个自增机制，叫做：序列（sequence）对象

drop table if exists t_user;

create table t_user(
    id int primary key auto_increment, //id字段自动维护一个自增的数字，从1开始，以1递增
    username varchar(255) default null
);

insert into t_user (username) values ('a');
insert into t_user (username) values ('b');
insert into t_user (username) values ('c');
insert into t_user (username) values ('d');
insert into t_user (username) values ('e');

select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
|  5 | e        |
+----+----------+
5 rows in set (0.00 sec)

mysql> insert into t_user values ('f');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

insert into t_user values (null, 'f');
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
|  5 | e        |
|  6 | f        |
+----+----------+
6 rows in set (0.00 sec)

4、外键约束：foreign key

外键是一个或多个列，用于建立与另一个表之间的关联关系。它用于确保引用表的完整性。

外键约束要求 外键值 必须在被引用表中的被引用字段中存在。

foreign key：

        外键约束，约束数据范围，不用在数据类型后面，引用字段和被引用字段，必须数据类型一致，关系名随意且唯一，被引用表为父表，引用表为子表，字段名不一定相同

关于外键约束的相关术语：

• 外键约束：foreign key
• 外键字段：添加有外键约束的字段
• 外键值：外键字段中的每一个值

4.1、父子表

t_student中的classno字段引用t_class表中的cno，此时

t_student表叫做 子表，引用表

t_class表叫做 父表，被引用表

4.2、父子表顺序要求

• 删除数据的时候，先删除子表，再删除父表
• 添加数据的时候，先添加父表，在添加子表
• 创建表的时候，先创建父表，再创建子表
• 删除表的时候，先删除子表，再删除父表

4.3、语法

添加约束：

[alter table 表名 add]

constraint  关系名  foreign key  (引用字段)  references  被引用表  (被引用字段); 

解除外键约束：

alter  table  表名（被引用表） drop  foreign  key  关系名；

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
    cno int primary key,
    classname varchar(255) default null
);

create table t_student(
    sno int primary key,
    sname varchar(255) default null,
    classno int default null,
    constraint fk1 foreign key (classno) references t_class (cno)  
);

insert into t_class (cno, classname) values (101, 'aaa');
insert into t_class (cno, classname) values (102, 'bbb');

insert into t_student values (1, 'zs1', 101);
insert into t_student values (2, 'zs2', 101);
insert into t_student values (3, 'zs3', 102);
insert into t_student values (4, 'zs4', 102);
insert into t_student values (5, 'zs5', 102);

select * from t_class;
+-----+-----------+
| cno | classname |
+-----+-----------+
| 101 | aaa       |
| 102 | bbb       |
+-----+-----------+

select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
|   5 | zs5   |     102 |
+-----+-------+---------+

insert into t_student values (6, 'zs6', 103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_student`, CONSTRAINT `fk1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

4.4、外键值可以为null

insert into t_student (sno, sname) values (6, 'zs6');

mysql> select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
|   5 | zs5   |     102 |
|   6 | zs6   |    NULL |
+-----+-------+---------+

4.5、父表中被引用的字段不一定是主键，但至少具有unique约束

父表中被引用的字段不一定是主键，但至少具有unique约束，确保唯一性

5、default：默认约束

default 值：添加默认约束，若没有的话，默认为null

• 默认值约束指定某列的默认值，如果在插入新行时未提供该列的值，则将使用默认值。
• 默认值可以是一个具体的值或表达式。

一般在创建表是未添加字段约束，则自动添加 default null

create table t_student(
	no bigint,
	name varchar(255),
	sex char(1) default '1',
	classno int,
	birth char(10)	
);

mysql> insert into
    -> t_student (name)
    -> values
    -> ('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+----------+------+---------+-------+
| no   | name     | sex  | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1    |    NULL | NULL  |
+------+----------+------+---------+-------+
1 row in set (0.00 sec)

五、表的复制

        在SQL中，复制表是将一个现有表的结构和数据复制到一个新表的过程。表的复制可以通过多种方法来实现，具体取决于您所使用的数据库管理系统（DBMS）。下面是一些常见的方法和示例，以便详细了解如何在SQL中复制表：

1、使用CREATE TABLE AS SELECT语句

将查询结果当做表创建出来

使用CREATE TABLE AS SELECT语句可以将现有表的结构和数据复制到一个新表。以下是一个示例：

CREATE TABLE new_table AS
SELECT * FROM old_table;

这将创建一个名为"new_table"的新表，并将"old_table"中的所有数据复制到新表中。

create table emp1
as
select * from emp;

2、使用INSERT INTO SELECT语句

您可以使用INSERT INTO语句将一个表中的数据插入到另一个表中，从而实现表的复制。首先，您需要创建一个新表，然后使用INSERT INTO将数据从旧表复制到新表。以下是示例：

-- 创建新表
CREATE TABLE new_table (
    column1 datatype,
    column2 datatype,
    ...
);

-- 复制数据到新表
INSERT INTO new_table
SELECT * FROM old_table;

这将创建一个名为"new_table"的新表，并将"old_table"中的所有数据复制到新表中。

create table dept1 
as 
select * from dept;

mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

insert into
	dept1
select * from dept;

mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
8 rows in set (0.00 sec)

3、注意事项

在复制表时，要确保考虑以下因素：

• 表的结构（列名、数据类型、约束等）必须与目标表匹配或满足复制需求。
• 如果需要，确保创建目标表并分配适当的权限。
• 选择合适的方法（CREATE TABLE AS SELECT、INSERT INTO等）来复制数据。
• 考虑数据转换、筛选或其他数据处理需求。

最重要的是，仔细检查复制结果，确保数据的完整性和正确性。如果表中有外键或触发器等其他相关对象，还需要考虑它们的处理方式。

六、alter：对表结构修改

对于表结构的修改，这里省略，使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具进行操作。修改表结构的语句不会出现在java代码当中。出现在java代码当中的SQL包括：insert ，delete，update，select（这些都是对表中的数据进行操作）

增删改查有一个术语：CRUD操作

• Create：增
• Retrieve：检索
• Update：修改
• Delete：删除

第二章、DML语句

一、insert into：表中插入数据

表名在数据库当中一般建议以：t_ 或者tbl_ 开始

创建学生表：

学生信息包括：

• 学号，姓名，性别，班级编号，生日
  • 学号：bigint
  • 姓名：varchar
  • 性别：char
  • 班级编号：int
  • 生日：char

create table t_student(
	no bigint,
	name varchar(255),
	sex char(1),
	classno int,
	birth char(10)	
);

1、语法

给指定字段添加数据

insert into 
    表名 （字段1，字段2，.....)
values 
    (值1，值2，.....),
    (值1，值2，.....),
    .......;
给指定字段添加数据

给所有字段添加数据

insert into 
    表名 
values 
    (值1，值2，...),
    (值1，值2，...),
    .....;
给全部字段添加数据，全部都有值

2、注意事项

要注意以下几点：

• 插入的数据数量必须和表中字段的数量一致
• 插入的数据值必须与表中的列的数据类型相匹配。否则，将会导致数据插入错误。
• 如果表中有自增列（例如，自动递增的主键），通常不需要指定该列的值，数据库会自动为其分配新的唯一值。
• INSERT语句还可以与子查询一起使用，允许从另一个表中选择数据插入目标表。

在执行INSERT操作时，务必小心，确保数据的准确性和完整性，以避免不必要的错误和问题。

3、当一条insert语句执行成功之后，表格当中必然会多一行记录

当一条insert语句执行成功之后，表格当中必然会多一行记录

即使多的这行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新

insert into 
	t_student (no, name, sex, classno, birth)
values
	(1, 'zhangsan', '1', 100);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

insert into 
	t_student (no, name, sex, classno, birth)
values
	(1, 'zhangsan', '1', 100, '1950-10-12');

mysql> select * from t_student;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    |     100 | 1950-10-12 |
+------+----------+------+---------+------------+
1 row in set (0.00 sec)

insert into 
	t_student (name, sex, classno, birth, no)
values
	('lisi', '1', 100, '1950-10-12', 2);

mysql> select * from t_student;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    |     100 | 1950-10-12 |
|    2 | lisi     | 1    |     100 | 1950-10-12 |
+------+----------+------+---------+------------+
2 rows in set (0.00 sec)

//除name字段之外，剩下的所有字段自动插入null
insert into
    t_student (name)
values
    ('wangwu');

mysql> select * from t_student;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    |     100 | 1950-10-12 |
|    2 | lisi     | 1    |     100 | 1950-10-12 |
| NULL | wangwu   | NULL |    NULL | NULL       |
+------+----------+------+---------+------------+
3 rows in set (0.00 sec)


insert into
    t_student (no)
values
    (3);

mysql> select * from t_student;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    |     100 | 1950-10-12 |
|    2 | lisi     | 1    |     100 | 1950-10-12 |
| NULL | wangwu   | NULL |    NULL | NULL       |
|    3 | NULL     | NULL |    NULL | NULL       |
+------+----------+------+---------+------------+
4 rows in set (0.00 sec)

4、表名后的字段可以省略不写，但是后面的value对数量，顺序和类型都有要求

insert into
	t_student
values
	(1, 'zhangsan', '1', 100, '1949-10-01');

mysql> select * from t_student;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    |     100 | 1949-10-01 |
+------+----------+------+---------+------------+
1 row in set (0.00 sec)

mysql> insert into
    -> t_student
    -> values
    -> (1, 'zhangsan', '1', 100);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

5、一次插入多行数据

//一次插如多行数据
insert into
    t_student
values
    (2, 'lisi', '1', 101, '2023-10-01'),
    (3, 'wangwu', '0', 101, '2023-10-01');

mysql> select * from t_student;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    |     100 | 1949-10-01 |
|    2 | lisi     | 1    |     101 | 2023-10-01 |
|    3 | wangwu   | 0    |     101 | 2023-10-01 |
+------+----------+------+---------+------------+
3 rows in set (0.00 sec)

二、update：修改表中数据

在SQL中，UPDATE语句用于更新数据库表中的现有记录的数据。使用UPDATE语句，您可以更改表中的一个或多个列的值，同时指定要更新哪些行。以下是UPDATE语句的一般语法和一些重要的概念：

1、语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];

当value为null时，为删除某个字段，一次只能执行一个条件

修改语句的条件可以有，也可以没有，如果没条件，则修改整列数据

下面是对上述语法的详细说明：

• table_name：指定要更新数据的目标表的名称。

• column1, column2, ...：指定要更新的列名，以及要为这些列设置的新值。

• value1, value2, ...：为上述列指定新的值。每个列和新值都必须按照相同的顺序一一对应。

• WHERE condition：指定要更新的行的筛选条件。只有满足条件的行才会被更新。如果省略WHERE子句，则将更新表中的所有行。

2、注意事项

要注意以下几点：

• 使用UPDATE语句时要小心，确保仔细测试和验证更新操作的影响，以避免不必要的数据损失或错误。
• 使用WHERE子句来指定更新的行是一个重要的安全措施，避免无意中更新了整个表。
• 在更新表中的数据之前，请确保您有足够的权限来执行此操作。
• 更新操作是一个事务，可以在需要时使用COMMIT和ROLLBACK来管理事务的提交或回滚。

UPDATE语句是SQL中用于修改表数据的强大工具，但务必小心使用以确保数据的准确性和完整性。

3、更新多个列的值

案例：将部门10的LOC修改为shanghai，将部门名称修改为 renshibu

update
	dept1 d
set
	d.loc = 'shanghai',
	d.dname = 'renshibu'
where
	d.deptno = 10;

mysql> select * from dept1 d;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | renshibu   | shanghai |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | renshibu   | shanghai |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
8 rows in set (0.00 sec)

4、更新列中所有行的值

案例：将dept1表中的dname修改为x，loc修改为y

update
	dept1 d
set
	d.dname = 'x',
	d.loc = 'y';

mysql> select * from dept1 d;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | x     | y    |
|     20 | x     | y    |
|     30 | x     | y    |
|     40 | x     | y    |
|     10 | x     | y    |
|     20 | x     | y    |
|     30 | x     | y    |
|     40 | x     | y    |
+--------+-------+------+
8 rows in set (0.00 sec)

三、delete：删除表中数据

在SQL中，DELETE语句用于从数据库表中删除记录。DELETE语句允许您指定要删除的行的条件，以便只删除满足条件的行。以下是DELETE语句的一般语法和一些关键概念：

1、语法

DELETE FROM 
    table_name
WHERE 
    condition;

如果有条件，就删除条件的行，如果没条件，则会删除整张表的所有数据 

下面是对上述语法的详细说明：

• table_name：指定要删除数据的目标表的名称。

• WHERE condition：指定要删除的行的筛选条件。只有满足条件的行才会被删除。如果省略WHERE子句，则将删除表中的所有行，这是一个非常危险的操作。

2、注意事项

要注意以下几点：

• 使用DELETE语句时要小心，确保仔细测试和验证删除操作的影响，以避免不必要的数据损失。
• 使用WHERE子句来指定要删除的行是一个重要的安全措施，避免无意中删除整个表中的数据。
• 删除操作是一个事务，可以在需要时使用COMMIT和ROLLBACK来管理事务的提交或回滚。
• 在删除表中的数据之前，请确保您具有足够的权限来执行此操作。

总之，DELETE语句是SQL中用于从表中删除数据的重要命令，但务必小心使用以确保数据的准确性和完整性。

3、删除满足条件的行

案例：删除10部门数据

delete from
	dept1 d	
where
	d.deptno = 10;

mysql> select * from dept1;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     20 | x     | y    |
|     30 | x     | y    |
|     40 | x     | y    |
|     20 | x     | y    |
|     30 | x     | y    |
|     40 | x     | y    |
+--------+-------+------+
6 rows in set (0.00 sec)

4、删除表中的所有行

delete from
    dept1;

mysql> select * from dept1;
Empty set (0.00 sec)

5、delete和truncate删除数据的区别

delete from 表名=truncate table 表名；（truncate不支持where条件）

• delete删除效率低，表中数据的内存空间不释放，支持回滚，支持where条件
• truncate删除效率高，表中数据的内存空间释放，不支持回滚，不支持where条件

第三章、存储引擎

一、概述

MySQL支持多种存储引擎（Storage Engine），存储引擎是MySQL用于管理数据存储和检索的内部组件。不同的存储引擎提供不同的功能、性能和特性，因此在选择存储引擎时需要根据应用程序的需求进行考虑。

1、完整的建表语句

注意：在MySQL中，凡是标识符是可以使用飘号括起来的。飘号最好别用，不通用

建表的时候可以指定存储引擎，也可以指定字符集

MySQL默认使用的存储引擎是InnoDB方式

默认采用的字符集是UTF8

mysql> show create table t_x;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| t_x   | CREATE TABLE `t_x` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

//以下是完整的建表语句
CREATE TABLE `t_x` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

2、存储引擎概念

存储引擎这个名字只有在MySQL中存在，Oracle中有对应的机制，但是不叫做存储引擎，Oracle中没有特殊的名字，就是“表的存储方式”

MySQL支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式

每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎

3、查看当前MySQL支持的存储引擎

show engines；

show engines \G;

MySQL 8.0.33 所支持的存储引擎是 11个，默认存储引擎是InnoDB

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

二、MyISAM存储引擎

   Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

MyISAM存储引擎是MySQL中最常用的引擎，但这种存储引擎不是默认的

优点：

        可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率

缺点：

        MyISAM存储引擎不支持事务

它管理的表具有以下特征：

• 使用三个文件表示每个表
  • 格式文件：存储表结构的定义（mytable.frm）

    • 在MySQL 8.0.33及更高版本中，MyISAM存储引擎的表结构（包括表的元数据信息）不再使用传统的.frm文件来存储。相反，MySQL引入了新的数据字典（Data Dictionary）体系结构，用于管理表的元数据信息。

      数据字典是MySQL 8.0版本引入的关键变化之一，它将表、列、索引等的元数据信息存储在系统表中，而不再依赖于.frm文件。这个改变带来了一些好处，包括更好的性能和数据一致性。

      因此，在MySQL 8.0.33及更高版本中，MyISAM表的元数据信息（例如表的结构、列的定义等）存储在系统表中，而不再需要单独的.frm文件。这使得MySQL更加灵活和可维护，并提供了更好的元数据管理。

      需要注意的是，MyISAM存储引擎在MySQL 8.0版本中已经不再是默认的存储引擎，而是InnoDB。在选择存储引擎时，请根据您的需求和应用程序的特性来做出明智的选择。如果您需要使用MyISAM存储引擎，不需要关心.frm文件，因为元数据信息由数据字典管理。

  • 数据文件：存储表中的数据（mytable.MYD）
  • 索引文件：存储表上索引（mytable.MYI）
• 灵活的AUTO_INCREMENT 字段处理
• 可被转换为压缩、只读来节省空间

三、InnoDB存储引擎

      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES 支持事务
          XA: YES
  Savepoints: YES

优点：

        支持事务，行级锁，外键等。这种存储引擎数据的安全得到保障

• 数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
• 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制
• InnoDB支持级联删除和级联更新

.ibd 文件是MySQL中的 InnoDB 存储引擎使用的文件扩展名，用于存储表的数据和索引。InnoDB 存储引擎是MySQL的默认存储引擎，它以一种高度可靠和事务安全的方式管理数据。

以下是关于.ibd 文件的一些重要信息：

• 数据和索引存储：每个InnoDB表都有一个关联的.ibd文件，其中包含了表的数据和索引。这些文件位于数据库目录中，通常是data_dir/database_name/table_name.ibd。

• 表空间：.ibd 文件包含表的数据和索引，但不包括表的结构定义。表的结构信息通常存储在数据字典中。.ibd 文件是InnoDB表空间的一部分。

• 表分区：InnoDB 存储引擎支持表分区，每个分区都有一个对应的.ibd 文件。

• 压缩和加密：InnoDB 存储引擎支持数据压缩和加密，因此.ibd 文件可以存储压缩的数据或加密的数据，以提高存储效率和数据安全性。

• 恢复和备份：.ibd 文件是数据库恢复和备份的关键部分。您可以使用备份工具来备份.ibd 文件，以便在需要时还原数据。

• 数据完整性：InnoDB 存储引擎提供了ACID事务支持，.ibd 文件用于确保数据的一致性和完整性。

总之，.ibd 文件是InnoDB 存储引擎用于存储表的数据和索引的文件扩展名。这些文件在MySQL数据库中起着关键作用，用于管理数据、支持事务和确保数据的完整性。

四、MEMERY存储引擎

      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO

缺点：不支持事务。数据容易丢失，因为所有数据和索引都是存储在内存当中的。

优点：查询速度最快

以前叫做HEAP引擎

第四章、事务 Transaction

一、概述

事务是一种特殊的线程，同时也是一种保证数据安全和完整性的机制

start transaction；执行之后相当于自己管理事务，并开启了数据自动备份

1、事务概念

一个事务就是一个完整的业务逻辑单元，不可再分

比如：银行账户转账，从“act-001”账户向“act-002”账户转账10000，需要执行两条update语句

update t_act set balance = balance - 10000 where actno = 'act-001';

update t_act set balance = balance + 10000 where actno = 'act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败

要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”

2、只有DML语句和事务相关，其他语句均和事务无关

因为DML语句：insert, delete, update 都是和数据库表当中的“数据”相关的

事务的存在是为了保证数据的完整性，安全性

3、事务存在的意义

假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务。

但实际情况不是这样的，通常一个“事儿（事务[业务]）”需要多条DML语句共同联合完成

4、事务的原理

二、事务的四大特性 ACID

事务是数据库管理系统（DBMS）中的一个核心概念，具有四个重要的特性，通常被称为ACID特性，以确保数据的完整性和一致性。以下是ACID特性的详细解释：

1、原子性（Atomicity）

事务是最小的工作单元，不可再分

• 原子性指的是事务是一个不可分割的工作单元，要么全部执行，要么全部回滚。如果在事务中发生了错误，整个事务将被回滚到初始状态，不会部分应用更改。
• 这确保了数据的一致性，因为事务要么完全成功，要么没有影响，不会留下不一致的中间状态。

2、一致性（Consistency）

事务必须保证多条DML语句同时成功或者同时失败

• 一致性确保了事务的执行使数据库从一个一致状态转换到另一个一致状态。这意味着事务在执行前和执行后必须满足一组预定义的一致性规则。
• 一致性保证了数据的完整性，防止数据损坏或不一致。

3、隔离性（Isolation）

多个事务之间互相隔离，互不影响

• 隔离性指的是每个事务都应该在独立的执行环境中运行，不受其他事务的干扰。即使多个事务同时运行，它们也不应该相互影响。
• 隔离性通常通过锁定和多版本控制等机制来实现，以防止并发事务之间的数据干扰。

4、持久性（Durability）

最终数据必须持久化到硬盘文件中，事务才算成功的结束

• 持久性确保一旦事务成功提交，其更改将永久保存在数据库中，即使发生系统崩溃或重新启动。这意味着数据将在系统故障后仍然可用。
• 持久性通常通过将事务更改写入磁盘或其他持久存储介质来实现。

这些ACID特性确保了数据库事务的可靠性和完整性。无论何时进行数据库操作，都需要考虑这些特性，以确保数据的正确性和一致性。不同的数据库管理系统实现ACID特性的方式可能会有所不同，但这些特性是数据库设计和应用程序开发的关键基础

三、事务的隔离级别

        在数据库管理系统（DBMS）中，事务的隔离级别是指多个并发事务之间的相互影响程度。隔离级别定义了事务在并发执行时看到其他事务更改的方式。SQL标准定义了四个常见的隔离级别，从最低到最高依次为：

1、READ UNCOMMITTED（读未提交）

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据

读未提交存在脏读（Dirty Read）现象：表示读到了脏数据，数据还在内存中，不在硬盘上，随时可变，可丢失，极其不稳定

• 这是最低的隔离级别。在这个级别下，一个事务可以看到其他事务未提交的更改，可能会导致脏读、不可重复读和幻读的问题。
• 此级别通常不建议使用，因为它的数据完整性风险很高。

设置事务的全局隔离级别：

set global transaction isolation level read uncommitted;

查看事务的全局隔离级别：MySQL设置和查看全局隔离级别

select @@global.transaction_isolation;

+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

2、READ COMMITTED（读已提交）

对方事务提交之后的数据我方可以读取到

这种隔离级别解决了：脏读现象没有了

读已提交存在的问题是：不可重复读，就是我方事务从开始到结束一直读取到的是对方事务修改后的最新的数据，数据一直在变化，不能重复读取到一个稳定的数据

• 在这个级别下，一个事务只能看到其他已提交事务的更改，因此脏读的问题不会发生。然而，不可重复读和幻读仍然可能发生。
• 大多数DBMS的默认隔离级别是READ COMMITTED。
• Oracle数据默认的隔离级别是：读已提交

3、REPEATABLE READ（可重复读）

这种隔离级别解决了：不可重复度的问题

存在的问题是：读取到的数据是幻像，就是说事务总是读取到其开始时已存在的数据，但是数据修改后不能读取到最新的数据，这样读取到的数据是假的，除非将该事务结束后，重新开启，在进行读取

• 在这个级别下，一个事务可以看到其开始时已存在的数据，并且在事务结束前不会看到其他事务的更改。这可以防止脏读和不可重复读，但仍然可能发生幻读。
• MySQL的InnoDB存储引擎的默认隔离级别是REPEATABLE READ。

4、SERIALIZABLE（串行化）

解决了所有问题

效率低，需要事务排队

• 这是最高的隔离级别。在这个级别下，事务按顺序执行，彼此不会产生任何交叉。这完全防止了脏读、不可重复读和幻读。
• 串行化级别的性能较低，通常只在需要绝对数据一致性的情况下使用。

在实际应用中，选择正确的隔离级别非常重要，需要根据应用程序的需求和数据完整性要求来决定。更高的隔离级别通常会导致更多的锁定和性能开销，因此需要在数据一致性和性能之间进行权衡。

不同的数据库管理系统支持不同的隔离级别，并且可能会提供额外的非标准隔离级别。了解数据库的隔离级别设置以及如何在SQL中指定它们是非常重要的，以确保数据库事务按预期执行。

四、演示事务

MySQL事务默认情况下是自动提交的。

自动提交：只要执行任意一条DML语句则提交一次

关闭自动提交：start transaction;

准备表：

drop table if exists t_user;

create table t_user(
    id int primary key auto_increment,
    username varchar(255)
);

 演示：MySQL中的事务是支持自动提交的，只要执行一条DML语句，则提交一次

mysql> insert into t_user(username) values ('zhangsan');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

演示：使用start transaction; 关闭自动提交机制

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values ('lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> insert into t_user(username) values('wangwu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

演示commit语句

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into t_user(username) values ('lisi'),('wangwu'), ('zhaoliu');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  4 | lisi     |
|  5 | wangwu   |
|  6 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  4 | lisi     |
|  5 | wangwu   |
|  6 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  4 | lisi     |
|  5 | wangwu   |
|  6 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)

第五章、索引 index

一、概述

1、索引定义

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

在数据库方面，查询一张表的时候有两种检索方式

• 第一种方式：全表扫描
• 第二种方式：根据索引检索（效率很高）

2、索引的作用

提高检索效率：其实最根本的原理是缩小了扫描的范围

 3、不能随意添加索引

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引。因为数据一旦修改，索引需要重新排序，进行维护。

4、索引是在某一个字段或者某些字段上添加的

select enaem, sal from emp where ename = 'SMITH'；

当ename字段上没有添加索引的时候，以上的sql语句会进行全表扫描，扫描ename字段中的所有值

当ename字段上添加索引的时候，以上的sql语句会根据索引扫描，快速定位。

5、添加索引的条件

• 数据流庞大（根据客户的需求，根据线上的环境）、
• 该字段很少的DML操作（因为字段进行修改操作，索引也需要维护）
• 该字段经常出现在where子句中（经常根据哪个字段查询）

6、主键和具有unique约束的字段自动添加索引

根据主键查询效率较高，尽量根据主键检索

7、查看sql语句的执行计划：explain SQL语句

只有MySQL中才有explain语法

添加索引前，全部字段值扫描
mysql> explain select e.ename, e.sal from emp e where e.sal = 5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

给字段添加索引
mysql> create index emp_sal_index on emp(sal);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加索引后，快速定位
mysql> explain select e.ename, e.sal from emp e where e.sal = 5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | e     | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

二、创建和删除索引

1、创建索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

2、删除索引

DROP INDEX index_name ON table_name;

三、索引的实现原理

1、索引底层采用的数据结构是：B+Tree

通过B+Tree缩小了扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

        select e.ename from emp where e.ename = 'Smith';

        通过索引转换为：

        select e.ename from emp e where 物理地址 = '0x3';

四、索引的分类

1、单一索引

给单个字段添加索引

2、复合索引

给多个字段联合起来添加1个索引

3、主键索引

主键上会自动添加索引

4、唯一索引

有unique约束的字段上会自动添加索引

五、索引的失效

select e.ename from emp e where e.ename like '%A%';

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的

第六章、视图

一、概述

1、定义

站在不同的角度区看到数据。（同一张表的数据，通过不同的角度去看待）

2、对视图进行增删改查，会影响到原表数据

通过视图影响原表数据的。不是直接操作的原表

3、可以对视图进行CRUD操作

可以对视图进行增删改查操作

二、创建和删除视图

1、创建视图

create view 视图名 as DQL语句;

2、删除视图

drop view 视图名;

三、面向视图操作

mysql> create view myView as select * from emp e;
Query OK, 0 rows affected (0.01 sec)

mysql> select e.ename, e.sal from myView e;
+--------+---------+
| ENAME  | SAL     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

create table emp_bak as (select * from emp);

create view myview as (select empno, ename, sal from emp_bak);

update myview set ename = 'zs', sal = 1 where empno = 7369; //通过视图修改原表数据

delete from myview where empno = 7369; //通过视图删除原表数据

四、视图的作用

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD

第七章、BDA命令

一、将数据当中的数据导出

在windows的dos命令窗口中执行：

导出整个数据库

mysqldump 数据库名 > D:\bjpowernode.sql -uroot -p123456

导出数据库中的某个表

mysqldump 数据库名 表名 > D:\bjpowernode.sql -uroot -p123456

二、导入数据

在MySQL窗口中执行

create database bjpowernode；

use bjpowernode；

source 文件路径；

第八章、远程连接MySQL

要允许远程登录到MySQL数据库，您需要执行一些配置步骤。以下是一般步骤：

一、MySQL用户账号和权限：

• 首先，确保在MySQL中创建一个具有远程访问权限的用户账号。您可以使用以下命令在MySQL中创建用户：

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password';

• 这将创建一个名为 remote_user 的用户，允许从任何主机（'%'表示通配符，允许来自任何IP地址的连接）进行连接，并使用指定的密码。

• 接下来，为该用户分配必要的权限，例如SELECT、INSERT、UPDATE、DELETE等，或者您可以为其授予超级用户权限（不建议在生产环境中使用）。授权可以使用如下命令进行：

GRANT ALL PRIVILEGES ON database_name.* TO 'remote_user'@'%';

这将授予 remote_user 用户对 database_name 数据库的所有权限。

二、MySQL配置文件修改：

• 打开 MySQL 的配置文件，通常是 my.cnf 或 my.ini，具体文件名和位置取决于您的操作系统。

• 查找 bind-address 配置项并将其设置为允许来自远程主机的连接。将其设置为MySQL服务器的公共IP地址或0.0.0.0（允许所有IP连接）。例如：

bind-address = 0.0.0.0

三、防火墙和网络配置

• 确保防火墙允许MySQL服务器使用的端口（默认端口是3306）的流量进入服务器。

• 确保网络配置允许MySQL服务器从远程主机接受连接。这可能涉及到您的网络设置和路由器配置。

四、重启MySQL服务

• 重启MySQL服务器，以使配置更改生效。

五、远程连接

• 使用具有远程访问权限的用户帐户，您可以在远程主机上使用MySQL客户端工具连接到MySQL服务器。例如：

mysql -u remote_user -h mysql_server_ip -p

        其中，remote_user 是您创建的具有远程访问权限的用户名，mysql_server_ip 是MySQL服务器的IP地址或主机名，-p 将提示您输入密码。

        确保在生产环境中仔细配置和保护远程访问权限，以提高数据库的安全性。避免使用具有过高权限的用户进行远程连接，并仅为远程用户授予最小必要的权限以限制潜在的风险。

第九章 、数据库设计三范式

一、概述

范式是设计表的依据，按照这个三范式设计的表不会出现数据冗余

数据库设计三范式是一组用于规范关系型数据库设计的指导原则，旨在消除数据冗余并提高数据库的整体性能和可靠性。它由埃德加·科德（Edgar F. Codd）在1970年提出，并随后发展为三个范式，分别是第一范式（1NF）、第二范式（2NF）和第三范式（3NF）

二、第一范式（1NF）

任何一张表都应该有主键，并且每一个字段原子性不可再分

要求数据库表中的每个列都是不可再分的基本数据项，且每个列都不包含重复的组。换句话说，每个单元格必须是原子的，不能包含多个值。

三、第二范式（2NF）

建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖

也就是说不能有复合主键

在满足第一范式的基础上，要求表中的非主键列完全依赖于全部候选键而不是部分候选键。换句话说，如果有复合主键，非主键列必须依赖于所有的列而不是其中的一部分。

多对多？三张表，关系表两个外键

四、第三范式（3NF）

建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖

在满足第二范式的基础上，要求表中的所有列都只依赖于主键，而不依赖于其他非主键列。换句话说，任何非主键列都不应该直接依赖于其他非主键列。

一对多？两张表，多的表加外键

五、以客户需求为主，有时会拿冗余换执行速度

在实际开发中，一满足客户的需求为主，有的时候会拿数据冗余换取执行速度

通过遵循三范式，可以最大程度地减少数据冗余和不一致性，提高数据库的性能和可维护性。然而，在实际应用中，有时会出现需要违反范式的情况，为了满足某些特定的需求或提高性能，可能需要根据具体情况灵活应用这些范式原则。

第十章、日期函数

一、概述

        在 SQL 中，日期函数是用来处理和操作日期和时间数据的函数。它们使得在数据库中处理日期和时间更加容易。不同的数据库管理系统（DBMS）可能会有不同的日期函数。

二、常见的时间格式

1、年：year

表示日期类型数据中的 年 位置处数据

2、月：month

表示日期类型数据中的 月 位置处数据

3、日：day

表示日期类型数据中的 日 位置处数据

4、时：hour

表示日期类型数据中的 时 位置处数据

5、分：minute

表示日期类型数据中的 分 位置处数据

6、秒：second

表示日期类型数据中的 秒 位置处数据

三、常见日期函数

1、CURDATE() 或 CURRENT_DATE: 返回当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2023-10-20 |
+------------+
1 row in set (0.00 sec)

2、NOW() 或 CURRENT_TIMESTAMP: 返回当前日期和时间

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2023-10-20 14:30:36 |
+---------------------+
1 row in set (0.00 sec)

3、DATE(): 提取日期或日期/时间表达式的日期部分

mysql> SELECT DATE('2023-10-20 12:34:56');
+-----------------------------+
| DATE('2023-10-20 12:34:56') |
+-----------------------------+
| 2023-10-20                  |
+-----------------------------+
1 row in set (0.00 sec)

4、EXTRACT(): 从日期/时间值中提取子部分，如年、月、日、小时、分钟等

mysql> SELECT EXTRACT(YEAR FROM '2023-10-20');
+---------------------------------+
| EXTRACT(YEAR FROM '2023-10-20') |
+---------------------------------+
|                            2023 |
+---------------------------------+
1 row in set (0.00 sec)

5、DATE_ADD() 或 DATE_SUB(): 用于对日期进行加法或减法操作

间隔：interval

mysql> SELECT DATE_ADD('2023-10-20', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2023-10-20', INTERVAL 1 DAY) |
+----------------------------------------+
| 2023-10-21                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2023-10-20', INTERVAL 1 MONTH);
+------------------------------------------+
| DATE_SUB('2023-10-20', INTERVAL 1 MONTH) |
+------------------------------------------+
| 2023-09-20                               |
+------------------------------------------+
1 row in set (0.00 sec)

6、DATEDIFF(被减数日期， 减数日期): 计算两个日期之间的天数差

mysql> SELECT DATEDIFF('2023-10-20', '2023-10-10');
+--------------------------------------+
| DATEDIFF('2023-10-20', '2023-10-10') |
+--------------------------------------+
|                                   10 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2023-10-10', '2023-10-20');
+--------------------------------------+
| DATEDIFF('2023-10-10', '2023-10-20') |
+--------------------------------------+
|                                  -10 |
+--------------------------------------+
1 row in set (0.00 sec)

7、TimeStampDiff(间隔类型，前一个日期，后一个日期)：计算两个日期之间的差

间隔类型：

second：秒

minute：分

hour：小时

day：天

week：星期

month：月

quarter：季度

year：年

mysql> select timestampdiff(year, e.hiredate, now()) from emp e;
+----------------------------------------+
| timestampdiff(year, e.hiredate, now()) |
+----------------------------------------+
|                                     42 |
|                                     42 |
|                                     42 |
|                                     42 |
|                                     42 |
|                                     42 |
|                                     42 |
|                                     36 |
|                                     41 |
|                                     42 |
|                                     36 |
|                                     41 |
|                                     41 |
|                                     41 |
+----------------------------------------+
14 rows in set (0.00 sec)

8、DATE_FORMAT('原日期时间格式’，‘转换后的格式’): 用于格式化日期或时间

mysql> SELECT DATE_FORMAT('2023-10-20', '%Y');
+---------------------------------+
| DATE_FORMAT('2023-10-20', '%Y') |
+---------------------------------+
| 2023                            |
+---------------------------------+
1 row in set (0.00 sec)

第十一章、悲观锁和乐观锁

一、概述

        悲观锁和乐观锁是并发控制的两种不同策略，用于处理多个用户或进程对共享资源进行读写操作时可能发生的冲突。它们通常用于数据库管理系统或并发编程中。

1、悲观锁

        悲观锁是一种保守的并发控制策略，它假设会发生并发冲突，并因此在访问共享资源时始终持有锁。它会在读取数据时阻止其他事务对数据进行修改，或在修改数据时阻止其他事务对相同数据进行读取或修改。这种策略通常会造成系统资源的浪费，因为它假设会发生冲突，即使在大多数情况下并没有发生冲突。

2、乐观锁

         乐观锁是一种较为乐观的并发控制策略，它假设冲突发生的可能性较低。它不会在访问共享资源时立即加锁，而是在更新数据时检查数据是否已被其他事务修改。通常，乐观锁会使用版本号或时间戳来检测数据是否发生了变化。如果数据未被修改，则更新可以继续进行；如果数据已被修改，则乐观锁策略可能会中止更新并通知用户或进程重新尝试操作。

        选择悲观锁还是乐观锁取决于具体的应用场景和数据访问模式。悲观锁适用于并发冲突较为频繁的场景，而乐观锁适用于并发冲突较为罕见的场景。

3、添加悲观锁/行级锁的语法

        悲观锁是一种并发控制机制，用于防止多个事务同时访问或修改相同数据时发生冲突。在数据库中，悲观锁通常使用 SQL 语句中的 FOR UPDATE 子句来实现。例如，在 SQL 中，可以使用以下语法来添加悲观锁：

SELECT * FROM table_name WHERE condition_column = 'some_value' FOR UPDATE;

        在这个例子中，table_name 是表的名称，condition_column 是用来指定条件的列，some_value 是要匹配的特定值。FOR UPDATE 子句用于锁定选定的行，确保在事务完成之前其他事务无法修改或访问这些行。在事务完成后，锁将释放。

        需要注意的是，悲观锁可能会导致性能下降，因为它会阻止其他事务对被锁定数据的访问，直到拥有锁的事务释放锁为止。因此，在使用悲观锁时需要权衡考虑并发性和性能之间的折衷。

4、乐观锁图示

 二、演示悲观锁

dbinfo.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/bjpowernode
user=root
password=123456

JDBCUtil.java

package util;

import java.sql.*;
import java.util.ResourceBundle;

public class JDBCUtil {
    private JDBCUtil(){}

    static{
        ResourceBundle bundle = ResourceBundle.getBundle("util/dbinfo");
        String driver = bundle.getString("driver");
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        ResourceBundle bundle = ResourceBundle.getBundle("util/dbinfo");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        return DriverManager.getConnection(url, user, password);
    }

    public static void close(Statement stmt, Connection conn) throws SQLException{
        if (stmt != null) {
            stmt.close();
        }

        if (conn != null) {
            conn.close();
        }
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) throws SQLException{
        if (rs != null) {
            rs.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if (conn != null) {
            conn.close();
        }
    }
}

JDBCTest01.java

package 演示悲观锁和乐观锁;

import util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 这个程序开启了一个事务，这个事务专门进行查询，并且使用行级锁/悲观锁，锁住相关信息
 */
public class JDBCTest01 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();

            //开启事务
            conn.setAutoCommit(false);
            String sql = "select ename, job, sal from emp where job = ? for update";
            ps= conn.prepareStatement(sql);
            ps.setString(1, "MANAGER");
            rs = ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("ename") + "," + rs.getString("job") + "," + rs.getDouble("sal"));
            }
            //提交事务（事务结束）
            conn.commit();
        } catch (SQLException e) {
            if(conn != null){
                try {
                    //回滚事务（事务结束）
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            try {
                JDBCUtil.close(rs, ps, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JDBCTest02.java

package 演示悲观锁和乐观锁;

import util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 这个程序负责修改被锁定的记录
 */
public class JDBCTest02 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql = "update emp set sal = sal * 1.1 where job = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "MANAGER");
            int count = ps.executeUpdate();
            System.out.println(count);

            conn.commit();
        } catch (SQLException e) {
            if(conn != null){
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            try {
                JDBCUtil.close(ps, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

第十二章、utf8和utf-8和UTF-8的区别

        在实际应用中，"utf8"、"utf-8"和"UTF-8"一般被视为相同的编码方式。事实上，它们之间并没有任何本质区别，只是大小写的不同写法而已。UTF-8（Unicode Transformation Format-8）是一种针对Unicode的可变长度字符编码，可以用来表示Unicode标准中的任何字符。它是一种流行的编码方式，广泛用于在计算机系统中存储和处理文本。所以，无论你使用"utf8"、"utf-8"还是"UTF-8"，它们都指向同一种字符编码方案。