MySQL学习

1.数据类型

1.1数值

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-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)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-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)	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，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

---

 

 1.2 日期和时间

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

---

 

1.3 字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

---

2.数据库操作

#创建数据库
mysql> CREATE DATABASE 数据库名;
#显示表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| study_db           |
| sys                |
+--------------------+
5 rows in set (0.04 sec)

#选择使用数据库
mysql> use study_db;
Database changed

2.1创建数据库表

不想字段为 NULL 可以设置字段的属性为 NOT NULL;

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1;

PRIMARY KEY关键字用于定义列为主键;

ENGINE 设置存储引擎，CHARSET 设置编码;

CREATE TABLE table_name (column_name column_type);

CREATE TABLE IF NOT EXISTS `tb1`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_study_db |
+--------------------+
| tb1                |
+--------------------+
1 row in set (0.00 sec)

#删除
DROP TABLE table_name ;

2.2MySQL 插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

例子，NOW()函数为MySQL函数，返回日期和时间：

mysql> insert into tb1
    -> (title,author,submission_date)
    -> values 
    -> ("java语言",'教程作者',NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> select * from tb1;                                                       
+----+------------+--------------+-----------------+
| id | title      | author       | submission_date |
+----+------------+--------------+-----------------+
|  1 | java       | 蒋新         | 2016-05-06      |
|  2 | java语言   | 教程作者     | 2019-06-07      |
+----+------------+--------------+-----------------+
2 rows in set (0.00 sec)

---

 2.3 where 查询

 

1）基本语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

[LIMIT N]限制数量
[ OFFSET M]偏移量，默认0，初始位置

如果where 的条件里有字符串比对，MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

mysql> select * from tb1 where author='蒋新';
+----+-------+--------+-----------------+
| id | title | author | submission_date |
+----+-------+--------+-----------------+
|  1 | java  | 蒋新   | 2016-05-06      |
+----+-------+--------+-----------------+
1 row in set (0.00 sec)

 

2）例子 

# 内容

mysql> select * from book;
+--------------+------+-----------------------+
| name         | age  | desc                  |
+--------------+------+-----------------------+
| java入门     |   20 | java入门出版20年      |
| python入门   |   15 | python入门            |
+--------------+------+-----------------------+

#结构

mysql> desc book;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | text       | YES  |     | NULL    |       |
| age   | bigint(20) | YES  |     | NULL    |       |
| desc  | text       | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+


#查询age>15
mysql> select name,age
    -> from book
    -> where age>15;
+------------+------+
| name       | age  |
+------------+------+
| java入门   |   20 |
+------------+------+
1 row in set (0.00 sec)

---

2.4 update操作

基本语法：

UPDATE table_name SET field1=new_value1, field2=new_value2
[WHERE Clause]

如更新tb1表中id=1的数据，设置title='java v 2.0' 

mysql> update tb1 set title='java v 2.0' 
    -> where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1 where author='蒋新';
+----+------------+--------+-----------------+
| id | title      | author | submission_date |
+----+------------+--------+-----------------+
|  1 | java v 2.0 | 蒋新   | 2016-05-06      |
+----+------------+--------+-----------------+
1 row in set (0.00 sec)

---

 

2.5 delete操作

删除操作语法和查询操作一致，不同的是DELETE会删除查到的内容：

DELETE FROM table_name [WHERE Clause]

# 原来的查询内容
mysql> select * from tb1;
+----+------------+--------------+-----------------+
| id | title      | author       | submission_date |
+----+------------+--------------+-----------------+
|  1 | java v 2.0 | 蒋新         | 2016-05-06      |
|  2 | java语言   | 教程作者     | 2019-06-07       |
+----+------------+--------------+-----------------+
2 rows in set (0.00 sec)

# 删除id=2 的
mysql> delete from tb1 where id=2;
Query OK, 1 row affected (0.01 sec)

# 新的表数据
mysql> select * from tb1;
+----+------------+--------+-----------------+
| id | title      | author | submission_date |
+----+------------+--------+-----------------+
|  1 | java v 2.0 | 蒋新   | 2016-05-06      |
+----+------------+--------+-----------------+
1 row in set (0.00 sec)

2.6 like操作

where 会精确匹配，like会基于规则模糊匹配。SQL LIKE中使用百分号 %字符来表示任意字符

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'some%'

例子： 

# 插入新的数据
mysql> insert into tb1
    -> (title,author,submission_date)
    -> values
    -> ('c++','谭浩强',NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from tb1;
+----+------------+-----------+-----------------+
| id | title      | author    | submission_date |
+----+------------+-----------+-----------------+
|  1 | java v 2.0 | 蒋新      | 2016-05-06      |
|  3 | c++        | 谭浩强    | 2019-06-07      |
+----+------------+-----------+-----------------+
2 rows in set (0.00 sec)

like 通配 author 为'谭'开头的内容：

mysql> select * from tb1 where author like '谭%';
+----+-------+-----------+-----------------+
| id | title | author    | submission_date |
+----+-------+-----------+-----------------+
|  3 | c++   | 谭浩强    | 2019-06-07      |
+----+-------+-----------+-----------------+
1 row in set (0.00 sec)

---

2.7 union、排序、分组

union

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。语法格式：

SELECT ch1, ch2, ... chn
FROM tables1
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT c1, c2, ... cn
FROM table2
[WHERE conditions]
ORDER BY 列名称;

expression1, expression2, ... expression_n: 要检索的列。tables: 要检索的数据表。

DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据。

ALL: 可选，返回所有结果集，包含重复数据。

mysql> select name from book union select num from mydf;
+--------------+
| name         |
+--------------+
| java入门     |
| python入门   |
| 12           |
| 34           |
| 56           |
| 89           |
+--------------+
6 rows in set (0.00 sec)

ORDER BY

可以基于指定字段排序，ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。排序语法：

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

分组GROUP BY

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

 数据表：

mysql> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  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 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

使用 GROUP BY 语句将数据表name进行分组，并统计name的个数：

mysql> select name ,COUNT(*) from employee_tbl group by name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小明   |        3 |
| 小王   |        2 |
| 小丽   |        1 |
+--------+----------+
3 rows in set (0.00 sec)

WITH ROLLUP

可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

按照name分组，并且计算对应的singin次数和：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽   |            2 |
| 小明   |            7 |
| 小王   |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.01 sec)

---

JOIN 连接使用：从多个数据表中读取数据

MySQL 的 JOIN 在两个或多个表中查询数据，

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

两个不相关的表：

mysql> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  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 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

mysql> select * from book;
+--------------+------+-----------------------+
| name         | age  | desc                  |
+--------------+------+-----------------------+
| java入门     |   20 | java入门出版20年      |
| python入门   |   15 | python入门            |
+--------------+------+-----------------------+
2 rows in set (0.00 sec)

 

---

2.8 NULL值与正则化处理

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算，

SELECT * FROM tbl WHERE name IS NULL;

正则匹配语法，匹配st开头的name字段：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

匹配ok结尾的name： 

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

 

---

2.9 事物

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这些数据库操作语句就构成一个事务。

MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。4个条件，原子性：一个事务中的所有操作，要么全部完成，要么全部不完成；一致性：事务开始之前和事务结束以后，数据库的完整性没有被破坏；隔离性：多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致；持久性：处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失；

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作

要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

---

2.10 ALTER命令

#原表
mysql> desc testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

#修改后
mysql> ALTER TABLE testalter_tbl  DROP i;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#新的表
mysql> desc testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

MySQL提供的关键字 FIRST (设定位第一列)，AFTER字段名（设定位于某个字段之后）。

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

重置数据表字段的位置需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。 

MODIFY 修改字段类型，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)：

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

CHANGE 关键字之后，是要修改的字段名，新字段名 ，类型，修改 i 为 j 类型为BIGINT：

ALTER TABLE testalter_tbl CHANGE i j BIGINT;

可为NULL值设定默认值，NOT NULL DEFAULT 100：

ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

mysql> desc testalter_tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| j     | bigint(20) | YES  |     | NULL    |       |
| c     | char(10)   | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc testalter_tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| j     | bigint(20) | NO   |     | 100     |       |
| c     | char(10)   | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

---

 

2.11 索引

 

2.12 临时表

 

2.13 复制表