学学 MySQL（1）

什么是MySQL？

MySQL是一个开源的关系型数据库管理系统，它基于结构化查询语言（SQL）进行数据操作。MySQL由瑞典MySQL AB公司开发，目前属于Oracle公司。

MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL支持大型的数据库，可以处理拥有上千万条记录的大型数据库。

MySQL使用标准的SQL数据语言形式，可以与多种编程语言配合使用，如C、C++、Python、Java、Perl、PHP等。

MySQL适合用于Web应用开发，因为它可以快速地存储和检索数据，并且可以定制和扩展。

总之MySQL是一个关系型数据库，用于快速的存储查询各种数据。

MySQL的命令

用于对数据库的操作指令，以 ; 结尾。

基础命令

MySQL的基础命令是一些用于管理数据库服务器或客户端的命令。

登录命令

        mysql -u 用户 -p  密码  使用用户和密码登录MySQL数据库。 

SHOW  类命令用于显示数据库，表，列，索引，变量，状态，权限等信息的命令。

        SHOW DATABASES;  可以显示所有的数据库名称，返回数据库信息与数据库的个数

        SHOW TABLES;  可以显示当前数据库中的所有表名称，返回表信息与表的个数

        SHOW VARIABLES;  可以显示MySQL服务器的配置变量等。

SET  类命令用于设置或更改MySQL服务器的配置变量或会话变量的命令。

        SET GLOBAL max_connections = 1000; 命令可以设置MySQL服务器允许的最大连接数为1000

        SET NAMES utf8;  命令可以设置当前会话的字符集为utf8等。

USE：用于选择要操作的数据库的命令。

        USE tydb;  可以切换到名字为 tydb 数据库，之后的所有操作都在 tydb 数据库中进行。

案例：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atguigu            |
| bjpowernode        |
| book               |
| filemanage         |
| graph              |
| mybatis            |
| mybatis_plus       |
| mydb               |
| mysql              |
| performance_schema |
| test               |
| user_db            |
+--------------------+
15 rows in set

mysql> USE book;
Database changed

mysql> SHOW TABLES;
+----------------+
| Tables_in_book |
+----------------+
| t_user         |
+----------------+
1 row in set

mysql> SET GLOBAL max_connections = 1000
    -> ;
Query OK, 0 rows affected

mysql> SET NAMES utf8;
Query OK, 0 rows affected

数据定义语言（DDL）相关的命令

MySQL数据定义语言（DDL）是一组用于定义或修改数据库结构和模式的SQL命令，包括创建、修改和删除数据库、表、视图、索引等的命令。

CREATE：用于创建数据库、表、视图、索引等对象的命令。

        CREATE DATABASE tydb;  可以创建一个名为 tydb 的数据库，

        CREATE TABLE student (id INT, name VARCHAR(20));  可以创建一个包含id和name两个字段的student表，

        CREATE INDEX idx_name ON student (name);  可以创建一个基于name字段的索引idx_name等。

DROP：用于删除数据库、表、视图、索引等对象的命令。

        DROP DATABASE tydb;  可以删除名为 tydb 的数据库，

        DROP TABLE student;  可以删除student表，

        DROP INDEX idx_name ON student;  可以删除student表上的idx_name索引等。

ALTER：用于修改数据库、表、视图、索引等对象的属性或结构的命令。

        ALTER DATABASE tydb CHARACTER SET utf8mb4;  可以修改 tydb 数据库的字符集为utf8mb4，

        ALTER TABLE student ADD COLUMN age INT;  可以给student表添加一个age字段，

        ALTER TABLE student RENAME TO stu;  可以把student表重命名为stu等。

SHOW CREATE TABLE student;  命令可以显示student表的创建语句等。

案例：

mysql> create database tydb;
Query OK, 1 row affected (0.00 sec)

mysql> use tydb;
Database changed
mysql> create table student(name VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create index idx_name ON student(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student;
+---------+------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table
                                                    |
+---------+------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `name` varchar(20) DEFAULT NULL,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database tydb CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> alter table student add column age int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER table student rename to stu;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table stu;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                            |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

数据查询语言（DQL）相关的命令

MySQL数据查询语言（DQL）是用来从数据库中检索数据的语言，主要使用SELECT语句

SELECT：可以指定查询的列、表、条件、排序、分组、聚合等功能。常见语句：

        SELECT * FROM emp;  命令查询所有列和所有记录，返回emp表中的所有列和所有记录。

        SELECT name, gender, age FROM emp;查询指定列和所有记录，返回emp表中的name, gender, age三列和所有记录。

        SELECT name, gender, age FROM emp WHERE gender = '男' AND age > 30;  命令查询指定列和满足条件的记录，这例句返回emp表中的name, gender, age三列和满足性别为男且年龄大于30的记录。

        SELECT name, gender, age FROM emp WHERE gender = '男' AND age > 30 ORDER BY age DESC;   命令查询指定列和满足条件的记录，并按照某一列排序，返回emp表中的name, gender, age三列和满足性别为男且年龄大于30的记录，并按照age列降序排序。

        SELECT workaddress, COUNT(*) AS num, AVG(age) AS avg_age FROM emp WHERE gender = '女' GROUP BY workaddress;  查询指定列和满足条件的记录，并按照某一列分组，并使用聚合函数计算统计值，返回emp表中的workaddress列，以及每个workaddress对应的女性员工数量（num）和平均年龄（avg_age），并按照workaddress列分组。

        SELECT name, salary FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);  命令查询指定列和满足条件的记录，并使用子查询，返回emp表中的name, salary两列和满足工资大于全体员工平均工资的记录，其中子查询（SELECT AVG(salary) FROM emp）用于计算全体员工平均工资。

SELECT语句的关键字不能随便的使用，是有一定的顺序的：

SELECT：指定要查询的列或者表达式，可以使用别名、聚合函数、DISTINCT等修饰。

FROM：指定要查询的表或者视图，可以使用别名、子查询、连接等修饰。

WHERE：指定查询的过滤条件，只有满足条件的记录才会被返回。

GROUP BY：指定按照哪些列或者表达式来分组数据，可以使用HAVING子句来过滤分组。

HAVING：指定分组后的过滤条件，只有满足条件的分组才会被返回。

ORDER BY：指定按照哪些列或者表达式来排序数据，可以使用ASC或者DESC来指定升序或者降序。

LIMIT：指定要返回的记录数目，可以使用OFFSET来指定偏移量。

数据操作语言（DOL）相关的命令

数据操作语言（DOL）是SQL语言的一部分，主要用于对数据库中的数据进行插入、更新、删除等操作。

INSERT：用于向数据库表中插入新的记录，可以指定要插入的列和值，也可以一次插入多条记录。

        INSERT INTO student (name, course, grade) VALUES ('张飞', '语文', 90);  命令向student表中插入一条记录。

        INSERT INTO student (name, course, grade) VALUES ('刘备', '数学', 70), ('关羽', '历史', 25);  命令向student表中插入多条记录

UPDATE：用于更新数据库表中的已有记录，可以指定要更新的列和值，也可以使用WHERE子句来过滤要更新的记录。

        UPDATE student SET grade = grade * 1.1;  命令更新student表中所有记录的grade列，将其乘以1.1

        UPDATE student SET grade = 18 WHERE name = '关羽';  命令更新student表中姓名为关羽的记录的grade列，将其设为18。

DELETE：用于删除数据库表中的记录，可以使用WHERE子句来过滤要删除的记录，也可以使用TRUNCATE命令来删除表中的所有记录。

        DELETE FROM student WHERE name = '关羽';  命令是删除student表中姓名为关羽的记录。

        TRUNCATE TABLE student;  命令是删除student表中的所有记录，但保留表结构。

数据控制语言（DCL）相关的命令

DCL（Data Control Language）是一种用于控制不同数据段之间的许可和访问级别的语句。它可以定义数据库、表、字段、用户的访问权限和安全级别。

操作完权限后都要执行  FLUSH PRIVILEGES;  命令来刷新权限。

GRANT：命令是用于给用户或角色赋予对数据库对象的访问权限的。

        GRANT SELECT, UPDATE ON students TO alice;  命令是让用户alice可以对表students进行查询和更新操作。

REVOKE： 命令是用于收回用户或角色对数据库对象的访问权限的。

        REVOKE UPDATE ON students FROM alice;  命令是用于取消用户alice对表students的更新权限

COMMIT 命令是用于提交事务处理的。

        START TRANSACTION;                                     //开启事务
        INSERT INTO students (id, name, age) VALUES (1001, 'Bob', 18);
        COMMIT;                                                             //提交事务

当你执行一个事务处理时，你可以使用COMMIT命令来确认对数据库的修改，并使其永久生效。比如上面在表students中插入一条记录，并提交这个操作

注：事务处理是指一组对数据库进行的操作，它们要么全部成功，要么全部失败。

ROLLBACK 命令是用于回滚事务处理的。

当执行一个事务处理时，如果发生了错误或者想撤销对数据库的修改，可以使用ROLLBACK命令来恢复到事务开始之前的状态。比如你在表students中插入一条记录，但是后悔了，可以使用以下语句来取消这个操作：

        START TRANSACTION;
        INSERT INTO students (id, name, age) VALUES (1001, 'Bob', 18);
        ROLLBACK;

SAVEPOINT 命令是用于设置保存点的。

比如在表students中更新了两条记录，但是只想保留第一条更新，可以使用以下语句来实现：

        START TRANSACTION;
        UPDATE students SET age = 19 WHERE id = 1001;
        SAVEPOINTA;
        UPDATE students SET age = 20 WHERE id = 1002;
        ROLLBACK TO A;
        COMMIT;

注：保存点是指在事务中的某个特定的状态，可以在事务中设置多个保存点，并且可以根据需要回滚到任意一个保存点。

LOCK 命令是用于对数据库的特定部分进行锁定的。

语法如下，table是要锁定的表名，locktype是要使用的锁类型。

        LOCK TABLES table1 locktype, table2 locktype, ...;

当使用LOCK命令时，必须在一个单独的语句中获取所有需要的锁，并且在获取锁期间，只能访问被锁定的表。如果想释放所有的锁，可以使用 UNLOCK 命令。

注：锁的类型：

READ：所有的用户只能读取被锁表，不能对表进行修改（包括执行LOCK的用户），当表不存在写锁时，读锁被执行。

READ LOCAL：除了允许INSERT命令以外，执行的锁与READ相同。

WRITE：除了当前用户被允许读取和修改被锁表外，其他用户的所有访问被完全阻止。一个写锁被执行仅当所有其他锁取消时。

LOW PRIORITY WRITE：低优先级的写锁，在等待时间内（等待其他锁取消），其他用户的访问将被认为是执行了读锁，因此将增加等待时间。

注：锁定是指防止其他用户对数据库对象进行修改的一种机制。当对数据库进行操作时，可以使用LOCK命令来保证数据的一致性和完整性。

MySQL数据类型

MySQL的数据类型可以分为三大类：数值类型、日期/时间类型和字符串类型。

整数类型：

TINYINT：很小的整数，占用1个字节，范围是-128到127（有符号）或0到255（无符号）。

SMALLINT：小的整数，占用2个字节，范围是-32768到32767（有符号）或0到65535（无符号）

MEDIUMINT：中等大小的整数，占用3个字节，范围是-8388608到8388607（有符号）或0到16777215（无符号）

INT或INTEGER：普通大小的整数，占用4个字节，范围是-2147483648到2147483647（有符号）或0到4294967295（无符号）

BIGINT：大整数，占用8个字节，范围是-9223372036854775808到9223372036854775807（有符号）或0到18446744073709551615（无符号）

可以在声明整数类型时指定是否为无符号（UNSIGNED）或有符号（SIGNED），默认为有符号。也可以指定是否为自增（AUTO_INCREMENT），这意味着每次插入新记录时，该字段的值会自动加1。

浮点数类型：

FLOAT：单精度浮点数，占用4个字节，精度为7位有效数字，范围是-3.402 823 466 E+38到-1.175 494 351 E-38，0，以及1.175 494 351 E-38到3.402 823 466 E+38。默认10位数，2位小数。

DOUBLE：双精度浮点数，占用8个字节，精度为15位有效数字，范围是-1.797 693 134 862 315 7 E+308到-2.225 073 858 507 201 4 E-308，0，以及2.225 073 858 507 201 4 E-308到1.797 693 134 862 315 7 E+308。默认16位数，小数4位数。

可以在声明浮点数类型时指定总位数（M）和小数位数（D），例如FLOAT(M,D)或DOUBLE(M,D)。如果没有指定M和D，则使用默认值。

定点数类型：

用于存储精确的数值数据，如货币、税率、百分比等。定点数类型是以字符串形式存储的，因此不会发生精度损失。

DECIMAL(M,D)：精确的数值数据，M表示总位数，D表示小数位数，范围是-10(M-D)到10(M-D)-1。例如，DECIMAL(7,4)可以存储的数据范围为-999.9999到999.9999。

DECIMAL：默认的M为10，D为0，即DECIMAL(10,0)。这意味着整数的总位数为10，没有小数部分。

DECIMAL(M)：默认的D为0，即DECIMAL(M,0)。这意味着整数的总位数为M，没有小数部分。

可以在声明定点数类型时指定总位数（M）和小数位数（D），例如DECIMAL(M,D)。如果没有指定M和D，则使用默认值。定点数类型的存储大小取决于M和D的值。

日期/时间类型

DATE：用于存储日期值，格式为YYYY-MM-DD，范围是1000-01-01到9999-12-31。

TIME：用于存储时间值或持续时间，格式为HH:MM:SS，范围是-838:59:59到838:59:59

DATETIME：用于存储混合日期和时间值，格式为YYYY-MM-DD HH:MM:SS，范围是1000-01-01 00:00:00到9999-12-31 23:59:59。

TIMESTAMP：用于存储时间戳值，格式为YYYY-MM-DD HH:MM:SS，范围是1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC。TIMESTAMP类型有自动初始化和更新的特性，可以根据时区进行转换。

YEAR：用于存储年份值，格式为YYYY，范围是1901到2155。

字符串类型

字符串类型可以用于存储和处理各种文本或二进制数据，如姓名、地址、图片、音乐等。

CHAR(M)：固定长度的非二进制字符串，M表示最大字符数，范围是0到255。CHAR类型在存储和检索时会删除尾部空格。

VARCHAR(M)：可变长度的非二进制字符串，M表示最大字符数，范围是0到6553512

VARCHAR类型在存储和检索时会保留尾部空格。

BINARY(M)：固定长度的二进制字符串，M表示最大字节数，范围是0到255。BINARY类型在排序和比较时按照字节值进行。

VARBINARY(M)：可变长度的二进制字符串，M表示最大字节数，范围是0到6553512

VARBINARY类型在排序和比较时按照字节值进行。

TINYTEXT：非常小的非二进制文本，最大长度是255个字符。

TEXT：小的非二进制文本，最大长度是65535个字符。

MEDIUMTEXT：中等大小的非二进制文本，最大长度是16777215个字符。

LONGTEXT：大的非二进制文本，最大长度是4294967295个字符。

TINYBLOB：非常小的二进制数据，最大长度是255个字节。

BLOB：小的二进制数据，最大长度是65535个字节。

MEDIUMBLOB：中等大小的二进制数据，最大长度是16777215个字节。

LONGBLOB：大的二进制数据，最大长度是4294967295个字节。

ENUM(‘value1’,‘value2’,…)：枚举类型，只能取指定列表中的一个值。枚举值在内部用整数表示，列表中每个值都有一个索引编号。

SET(‘value1’,‘value2’,…)：集合类型，可以取指定列表中的零个或多个值。集合值在内部用位向量表示，列表中每个值都有一个位编号。