Loading... # MySQL基础 ## 连接MySQL - 通过Shell控制台连接MySQL - ```sh [root@host]# mysql -u root -p Enter password:****** ``` - 也可以创建其他用户之后，使用其他用户登陆 - 推出mysql使用exit命令 - 除此之外，Java可以通过JDBC连接到MySQL数据库 ## MySQL基本操作 - sql语句是操作关系型数据库的一种语句，一般以分号结尾。 - sql语句不区分大小写 ### 创建数据库 ```mysql CREATE DATABASE 数据库名; ``` #### 使用mysqladmin创建数据库 ```sh [root@host]# mysqladmin -u root -p create ABC Enter password:****** ``` 以上语句创建了数据库ABC ### 删除数据库 ```mysql drop database 数据库名; ``` #### 使用mysqladmin删除数据库 ```mysql [root@host]# mysqladmin -u root -p drop ABC Enter password:****** ``` 删除了名为ABC的数据库 注意⚠️一旦删除数据库，该数据库所有内容将消失且不可恢复 ### 选择数据库 ```mysql use ABC; ``` 以上语句选择了ABC数据库，意思是之后进行的操作将是对数据库ABC进行的操作 ### MySQL数据类型 MySQL支持很多种类型，主要可以分为：数值、字符串、日期三种类型 #### 数值类型 ##### 整数类型 - 分为很多种，TINYINT、SMALLINT、MEDIUMINT、INT或INTEGER、BIGINT分别占用1、2、3、4、8字节空间，且全部有有符号类型和无符号类型。 ##### 浮点类型 - 三种 - FLOAT 占用4Bytes $(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)$ - DOUBLE 占用8Bytes $(-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)$ - DECIMAL 精确浮点类型，对于DECIMAL(M,D)有M位有效数字，D位小数 ##### 日期和时间 - DATE - 占用3Bytes - 范围$1000-01-01/9999-12-31$ - 格式为年-月-日 - TIME - 占用3Bytes - 范围$-838:59:59/838:59:59$ - 格式为小时:分钟:秒 - YEAR - 占用1Bytes - 范围$1901/2155$ - DATETIME - 占用8Bytes - 范围1000-01-01 00:00:00/9999-12-31 23:59:59 - TIMESTAMP - 占用4Bytes - 范围1970-01-01 00:00:00到格林威治时间2038年1月19日 凌晨 03:14:07 - 时间戳 ##### 字符串类型 -  ### 创建数据表 ```mysql CREATE TABLE table_name (column_name column_type); ``` - 例 ```mysql CREATE TABLE IF NOT EXISTS student( student_id INT UNSIGNED AUTO_INCREMENT, student_name VARCHAR(100) NOT NULL, date DATE, PRIMARY KEY (student_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` 注：AUTO_INCREMENT表示自增（每次会自动加一）一般用于主键 如果不希望某一列为空，可以使用NOT NULL PRIMARY KEY (student_id)将student_id设置为主键 ENGINE 设置存储引擎，CHARSET 设置字符编码。 - 注意：在添加表之前，先使用use切换到想要操作的数据库中 ### 删除数据表 ```mysql DROP TABLE table_name; ``` ### 插入数据 ```mysql INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); ``` - 例 ```mysql INSERT INTO table_name (student_id, student_name, date) VALUES (1, "YorkWu", NOW()); ``` 如果要插入的是字符串类型，需要使用双引号引起来。 日期类型，可以使用类似“2022-04-11”的方法注入（包括引号） ### 查询数据 ```mysql SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [OFFSET M] ``` - 查询中可以选择一个或者多个表，用逗号隔开 - 可以读取一条或多条记录 - 可以使用星号\*来代替其他字段，表示选择全部 - 可以使用WHERE包含一些条件 - 可以用LIMIT表示限制返回几条数据 - 可以通过OFFSET设置开始查询的偏移量，默认为0 ### WHERE ```mysql SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2..... ``` - 在WHERE后可以指定任何条件 - 同样适用于DELETE或者UPDATE命令 - 包含=,<,>,<>或!=,<=,>=运算符 - 默认**不区分**大小写 - 如果想要区分大小写，可以使用**BINARY**关键字 - 例 ```mysql SELECT * from student WHERE stydent_name='YorkWu';//不区分大小写 SELECT * from student WHERE BINARY student_name='yorkwu';//区分大小写 ``` ### UPDATE ```mysql UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] ``` - 可以一次更新多个字段 - 可以在WHERE中指定某些条件 - 例 ```mysql UPDATE student SET student_name='wyy' WHERE runoob_id=1; ``` ### DELETE ```mysql DELETE FROM table_name [WHERE Clause] ``` - 如果未使用WHERE指定，则相当于清空表 ### LIKE - 我们知道，在使用更新，删除，等语句中，可以使用WHERE限定修改的条目，而WHERE只能做一些精确选择操作例如某种列为某个字符串（student_name="YorkWu"），但是有时候我们需要筛选例如所有含有某个字段的项（比如所有含有.com字段的文本）。 - 就需要用到LIKE，在LIKE中%可以表示任何字符，类似于SELECT中\*的作用，如果不使用%则与WHERE中的=没有本质区别 - ```mysql SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue' ``` - LIKE可以用来代替=，也就是说，LIKE是用在WHERE内部的 - 例 ```mysql SELECT * from student WHERE student_name LIKE '%Wu'; ``` 则所有student_name中含有Wu字串的项都会被选中 ### 排序 - 在MySQL中可以使用ORDER BY来以指定关键字排序 - ```mysql SELECT field1, field2,...fieldN FROM table_name1, table_name2... ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]] ``` - 可以设置多个字段排序 - 使用ASC或者DESC来指定是升序排序或者降序排序，默认使用升序排列 - 可以使用WHERE/LIKE设置条件 - SELECT * from student ORDER BY student_name ASC; ### 分组 - GROUP BY语句根据一个或多个列对结果集进行分组 - 在分组的列上可以使用count,sum,avg等函数 - ```mysql SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; ``` - function替换为count, sum,avg - 对于一张表 ```mysql +----+--------+---------------------+--------+ | id | name | date | signin | +----+--------+---------------------+--------+ | 1 | 小明 | 2016-04-22 15:25:33 | 1 | | 2 | 小王 | 2016-04-20 15:25:47 | 3 | | 3 | 小丽 | 2016-04-19 15:26:02 | 2 | | 4 | 小王 | 2016-04-07 15:26:14 | 4 | | 5 | 小明 | 2016-04-11 15:26:40 | 4 | | 6 | 小明 | 2016-04-04 15:26:54 | 2 | +----+--------+---------------------+--------+ ``` - 以姓名统计，并统计每个姓名出现次数 ```mysql SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*) | +--------+----------+ | 小明 | 3 | | 小王 | 2 | | 小丽 | 1 | +--------+----------+ ``` - (with rollup)以姓名统计，统计之后计算每个人的signin之和，最后再计算总数 ```mysql SELECT name, SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------+--------------+ | name | signin_count | +--------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | NULL | 16 | +--------+--------------+ ``` 对于NULL可以使用函数coalesce(a, b, c)修改，如果a为空则返回b，b若也为空返回c ```mysql SELECT coalesce(name, 'tot'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +-----------------------+--------------+ | coalesce(name, 'tot') | signin_count | +-----------------------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | tot | 16 | +-----------------------+--------------+ ``` ### 使用连接获取多个表 - 之前已经学习了从一个表中获取数据，使用连接可以从多个表中获取数据。 #### JOIN - 可以在SELECT, UPDATE, DELETE等语句中使用JOIN - 分为三类 - INNER JOIN（内连接、等值连接）：获取两个表中字段匹配关系 - LEFT JOIN（左连接）：获取左边表中所有记录，如果与右表有匹配，输出对应匹配的值 - RIGHT JOIN（右连接）：与左连接相反 ##### INNER JOIN - 使用INNER JOIN合并两个表具有相同关键字的记录 ```mysql mysql> select * from a; +------+-------+ | name | count | +------+-------+ | a | 1 | | b | 2 | | c | 3 | | d | 4 | +------+-------+ 4 rows in set (0.00 sec) mysql> select * from b; +----+------+---------------------+ | id | name | date | +----+------+---------------------+ | 1 | abc | 2022-04-15 19:36:34 | | 2 | a | 2022-04-07 19:36:51 | | 3 | c | 2022-04-27 19:37:04 | +----+------+---------------------+ ``` ```mysql mysql> SELECT x.name, y.id, x.count, y.date FROM a x INNER JOIN b y ON x.name = y.name; +------+----+-------+---------------------+ | name | id | count | date | +------+----+-------+---------------------+ | a | 2 | 1 | 2022-04-07 19:36:51 | | c | 3 | 3 | 2022-04-27 19:37:04 | +------+----+-------+---------------------+ 2 rows in set (0.00 sec) ``` ##### LEFT JOIN - 读取左边表中所有数据，如果右表有对应数据则添加 ```mysql mysql> SELECT x.name, y.id, x.count, y.date FROM a x LEFT JOIN b y ON x.name = y.name; +------+------+-------+---------------------+ | name | id | count | date | +------+------+-------+---------------------+ | a | 2 | 1 | 2022-04-07 19:36:51 | | b | NULL | 2 | NULL | | c | 3 | 3 | 2022-04-27 19:37:04 | | d | NULL | 4 | NULL | +------+------+-------+---------------------+ 4 rows in set (0.00 sec) ``` 如上，会读取左表所有数据，其中不存在于右表的数据用NULL代替。 ##### RIGHT JOIN - 同LEFT JOIN相反 ```mysql mysql> SELECT x.name, y.id, x.count, y.date FROM a x RIGHT JOIN b y ON x.name = y.name; +------+----+-------+---------------------+ | name | id | count | date | +------+----+-------+---------------------+ | NULL | 1 | NULL | 2022-04-15 19:36:34 | | a | 2 | 1 | 2022-04-07 19:36:51 | | c | 3 | 3 | 2022-04-27 19:37:04 | +------+----+-------+---------------------+ 3 rows in set (0.00 sec) ``` #### 对于NULL的处理 - 对于NULL，在WHERE中发现无法使用=或者!=符号判断，将其改为IS NULL和IS NOT NULL即可 - 例如 ```mysql mysql> SELECT x.name, y.id, x.count, y.date FROM a x RIGHT JOIN b y ON x.name = y.name WHERE x.name IS NULL; +------+----+-------+---------------------+ | name | id | count | date | +------+----+-------+---------------------+ | NULL | 1 | NULL | 2022-04-15 19:36:34 | +------+----+-------+---------------------+ 1 row in set (0.00 sec) mysql> SELECT x.name, y.id, x.count, y.date FROM a x RIGHT JOIN b y ON x.name = y.name WHERE x.count IS NOT NULL; +------+----+-------+---------------------+ | name | id | count | date | +------+----+-------+---------------------+ | a | 2 | 1 | 2022-04-07 19:36:51 | | c | 3 | 3 | 2022-04-27 19:37:04 | +------+----+-------+---------------------+ 2 rows in set (0.00 sec) ``` ### MySQL 事务 - 事务；在一个事务中可以包含多个语句，且统一执行，如果成功则全部成功，如果失败则全部失败。 - 一般只有使用InnoDB数据库引擎才支持事务 - 有时候多个操作同时成功才有意义，如果一些成功，一些失败，不但不能成功反而会扰乱数据库的数据。 - 事务需要满足的四个条件（四个性质）： - 原子性：不可分割性，表示事务不可进行分割。 - 一致性：在事务开始前和执行后，数据库没有被破坏。即进行的操作符合预期。因此此性质需要由操作人保证，事物本身不能保证此性质。 - 隔离性：多个并发事务同时执行的情况下执行正确的能力。数据库允许并发，隔离性用于防止多个事务交叉执行（顺序不一定）导致数据不一致的问题。事务隔离分为不同级别：包括读未提交，读提交，可重复读，和可串行化。 - 在并发的情况下，由于多个进程同时对一个数据进行读写操作，可能出现Dirty Read（赃读），Non-Repeatable Read（不可重复读），Phantom（幻读）三种错误情况。 - 赃读：在事务中读到了其他线程未提交的数据 - 不可重复读：在事务中读取两次，前后的数据不一样 - 幻读：在两次连续查找操作中，前后查找结果不一样（注意：幻读与不可重复读类似，但幻读更偏向于增加和删除，而不可重复读偏向于修改） - 对于读未提交级别，对本线程尚未提交的事务可以读到其他未被提交的事务修改的数据，则会发生Dirty Read； - 对于读提交级别，对本线程未提交的事务，只能读到其他已被提交的事务，但可能会发生Non-Repeatable Read； - 对于可重复读级别，只有本线程提交时，才能读到其他已经提交的事务修改的数据，但有可能在未提交时读到其他线程已提交的新增或者删除操作，因此可能出现幻读（Phantom）。 - 对于可串行化，不会出现上述三种读写异常。 - 一般，使用可重复读级别，也是默认级别，其相对稳定且对数据库效率影响较小。 - 持久性：一旦提交事务，对数据的修改就是永久的，即使断电也不会丢失（保存在海量存储器中） #### 事务控制语句 - 默认每一句话都提交一遍（隐式提交） - BEGIN显示地开启一个事务 - COMMIT显示地提交一个事务 - ROLLBACK回滚一个事务（回滚到BEGIN） - SAVEPOINT xxx新建一个保存点xxx，允许存在多个保存点 - RELEASE SAVEPOINT xxx删除名为xxx的保存点 - ROLLBACK TO xxx回滚到名为xxx的保存点 - SET TRANSACTION设置事务的隔离级别 Last modification：April 18, 2022 © Allow specification reprint Like 如果觉得我的文章对你有用，请随意赞赏