MySQL 基础操作

1 创建数据库命令格式：`CREATE DATABASE 数据库名;`

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

2 删除数据库命令格式：`drop database 数据库名;`

mysql> drop database test;
Query OK, 0 rows affected (0.02 sec)

3 选择数据库命令格式：`use 数据库名;`

mysql> use TEST;
Database changed

4 数据类型：数值、日期/时间和字符串(字符)类型。

包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

5 创建数据表

创建MySQL数据表需要：表名、表字段名、定义每个表字段
命令格式：CREATE TABLE table_name (column_name column_type);

mysql> CREATE DATABASE TEST;
Query OK, 1 row affected (0.01 sec)


mysql> use TEST;
Database changed
mysql> CREATE TABLE myTable(
    -> myTable_id INT NOT NULL AUTO_INCREMENT,
    -> myTable_title VARCHAR(100) NOT NULL,
    -> myTable_author VARCHAR(40) NOT NULL,
    -> submission_data DATE,
    -> PRIMARY KEY (myTable_id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。
注意：MySQL命令终止符为分号 ; ,-> 是换行符标识。

6 删除数据表命令格式：`DROP TABLE table_name ;`

mysql> use TEST;
Database changed
mysql> DROP TABLE myTable;
Query OK, 0 rows affected (0.02 sec)

7 插入数据命令格式：

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

mysql> use TEST;
Database changed

mysql> CREATE TABLE myTable(
    -> myTable_id INT NOT NULL AUTO_INCREMENT,
    -> myTable_title VARCHAR(100) NOT NULL,
    -> myTable_author VARCHAR(40) NOT NULL,
    -> submission_date DATE,
    -> PRIMARY KEY (myTable_id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)


mysql> INSERT INTO myTable
    -> (myTable_title,myTable_author,submission_date)
    -> VALUES
    -> ("php","教程",NOW());
Query OK, 1 row affected, 1 warning (0.02 sec)


mysql> INSERT INTO myTable
    -> (myTable_title,myTable_author,submission_date)
    -> VALUES
    -> ("MySQL","教程",NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> INSERT INTO myTable
    -> (myTable_title,myTable_author,submission_date)
    -> VALUES
    -> ("JAVA","com.cn",NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

8 查询数据

命令格式：

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

mysql> select*from myTable;       #读取数据表
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          3 | JAVA          | com.cn         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
3 rows in set (0.00 sec)

查询语句中可以使用一个或者多个表，表之间使用逗号分割，并使用WHERE语句来设定查询条件。SELECT 命令可以读取一条或者多条记录。
可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据。
可以使用 WHERE 语句来包含任何条件。
可以使用 LIMIT 属性来设定返回的记录数。
可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

9 MySQL WHERE 子句

从 MySQL 表中使用 SQL SELECT 语句来读取数据。如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。
命令格式：
SELECT field1, field2,...fieldN FROM table_name1, table_name2...[WHERE condition1 [AND [OR]] condition2.....
查询语句中可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
可以在 WHERE 子句中指定任何条件。可以使用 AND 或者 OR 指定一个或多个条件。WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

mysql> select*from myTable WHERE myTable_author="教程";
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
+------------+---------------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select*from myTable WHERE myTable_author="COM.CN";
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          3 | JAVA          | com.cn         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。但是使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。



mysql> SELECT*from myTable WHERE BINARY myTable_author="COM.CN";
Empty set (0.00 sec)


mysql> SELECT*from myTable WHERE BINARY myTable_author="com.cn";
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          3 | JAVA          | com.cn         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)

10 MySQL UPDATE 更新

需要修改或更新 MySQL 中的数据，可以使用 SQL UPDATE 命令来操作
命令格式：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
可以同时更新一个或多个字段。可以在 WHERE 子句中指定任何条件。可以在一个单独表中同时更新数据。当需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

mysql> UPDATE myTable SET myTable_title="C++" WHERE myTable_id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> SELECT*from myTable WHERE myTable_id=3;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          3 | C++           | com.cn         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)


mysql> select*from myTable;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          3 | C++           | com.cn         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
3 rows in set (0.00 sec)

11 MySQL DELETE语句

命令格式：DELETE FROM table_name [WHERE Clause]
如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
可以在 WHERE 子句中指定任何条件
可以在单个表中一次性删除记录。
当想删除数据表中指定的记录时 WHERE 子句是非常有用的。

mysql> use TEST;
Database changed
mysql> DELETE FROM myTable WHERE myTable_id=3;
Query OK, 1 row affected (0.00 sec)


mysql> SELECT*from myTable;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
+------------+---------------+----------------+-----------------+
2 rows in set (0.00 sec)

12 LIKE子句

SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

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

mysql> use TEST;
Database changed
mysql> SELECT*from myTable;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          4 | Python        | cn.com         | 2019-09-17      |
|          5 | LINUX         | cn.com         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
4 rows in set (0.00 sec)


mysql> SELECT*from myTable WHERE myTable_author LIKE "%com";
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          4 | Python        | cn.com         | 2019-09-17      |
|          5 | LINUX         | cn.com         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
2 rows in set (0.00 sec)

13 UNION 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
注：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

mysql> SELECT*from APP;
+--------+---------------+---------+
| APP_id | APP_name      | country |
+--------+---------------+---------+
|      1 | stackoverflow | IND     |
+--------+---------------+---------+
1 row in set (0.00 sec)


mysql> SELECT*from WEB;
+--------+----------+---------+
| WEB_id | WEB_name | country |
+--------+----------+---------+
|      1 | BaiDu    | China   |
|      2 | Google   | USA     |
+--------+----------+---------+
2 rows in set (0.00 sec)


mysql> SELECT country FROM APP
    -> UNION
    -> SELECT country FROM WEB
    -> ORDER BY country;
+---------+
| country |
+---------+
| China   |
| IND     |
| USA     |
+---------+
3 rows in set (0.00 sec)

14 排序

SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据

mysql> SELECT*from myTable;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          4 | Python        | cn.com         | 2019-09-17      |
|          5 | LINUX         | cn.com         | 2019-09-17      |
+------------+---------------+----------------+-----------------+


mysql> SELECT*from myTable ORDER BY myTable_title DESC;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          4 | Python        | cn.com         | 2019-09-17      |
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          5 | LINUX         | cn.com         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
4 rows in set (0.00 sec)


mysql> SELECT*from myTable ORDER BY myTable_title ASC;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          5 | LINUX         | cn.com         | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          1 | php           | 教程           | 2019-09-17      |
|          4 | Python        | cn.com         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
4 rows in set (0.00 sec)

15 JOIN 连接

使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取myTable表中所有myTable_author字段在myTable_count表对应的myTable_coun字段值:


mysql> SELECT*from myTable_count;
+------------+----------------+--------------+
| myTable_id | myTable_author | myTable_coun |
+------------+----------------+--------------+
|          1 | 教程           |           10 |
|          2 | cn.com         |           20 |
+------------+----------------+--------------+
2 rows in set (0.00 sec)


mysql> SELECT*from myTable;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          4 | Python        | cn.com         | 2019-09-17      |
|          5 | LINUX         | cn.com         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
4 rows in set (0.00 sec)


mysql> SELECT a.myTable_id,a.myTable_author,b.myTable_coun FROM myTable a INNER
JOIN myTable_count b ON a.myTable_author=b.myTable_author;
+------------+----------------+--------------+
| myTable_id | myTable_author | myTable_coun |
+------------+----------------+--------------+
|          1 | 教程           |           10 |
|          2 | 教程           |           10 |
|          4 | cn.com         |           20 |
|          5 | cn.com         |           20 |
+------------+----------------+--------------+
4 rows in set (0.00 sec)

16 NULL 值处理

MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

mysql> use TEST;
Database changed
mysql> CREATE TABLE myTable_test(
    -> myTable_author VARCHAR(40) NOT NULL,
    -> myTable_coun INT
    -> );
Query OK, 0 rows affected (0.05 sec)


mysql> INSERT INTO myTable_test (myTable_author,myTable_coun) values ("教程",NUL
L);
Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO myTable_test (myTable_author,myTable_coun) values ("Google",N
ULL);
Query OK, 1 row affected (0.00 sec)


mysql> INSERT INTO myTable_test (myTable_author,myTable_coun) values ("CN",20);
Query OK, 1 row affected (0.00 sec)


mysql> SELECT*from myTable_test;
+----------------+--------------+
| myTable_author | myTable_coun |
+----------------+--------------+
| 教程           |         NULL |
| Google         |         NULL |
| CN             |           20 |
+----------------+--------------+
3 rows in set (0.00 sec)

以下实例中可以看到 = 和 != 运算符是不起作用的：

mysql> SELECT*from myTable_test WHERE myTable_coun = NULL;
Empty set (0.00 sec)


mysql> SELECT*from myTable_test WHERE myTable_coun != NULL;
Empty set (0.00 sec)

查找数据表中 myTable_test列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL，如下实例:

mysql> SELECT*from myTable_test WHERE myTable_coun IS NULL;
+----------------+--------------+
| myTable_author | myTable_coun |
+----------------+--------------+
| 教程           |         NULL |
| Google         |         NULL |
+----------------+--------------+
2 rows in set (0.00 sec)


mysql> SELECT*from myTable_test WHERE myTable_coun IS NOT NULL;
+----------------+--------------+
| myTable_author | myTable_coun |
+----------------+--------------+
| CN             |           20 |
+----------------+--------------+
1 row in set (0.00 sec)

17 MySQL事物

MySQL 事务主要用于处理操作量大，复杂度高的数据。
事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。MYSQL 事务处理主要有两种方法：
1）、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2）、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

mysql> use TEST;
Database changed
mysql> CREATE TABLE myTable_transaction_test(id int(5)) engine=innodb;  #创建数据表
Query OK, 0 rows affected (0.05 sec)


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


mysql> begin; #开始事务
Query OK, 0 rows affected (0.00 sec)


mysql> insert into myTable_transaction_test value(5);
Query OK, 1 row affected (0.00 sec)


mysql> insert into myTable_transaction_test value(6);
Query OK, 1 row affected (0.00 sec)


mysql> commit;   #提交事务
Query OK, 0 rows affected (0.00 sec)






mysql> select*from myTable_transaction_test;
+------+
| id   |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.00 sec)


mysql> begin;  #开始事务
Query OK, 0 rows affected (0.00 sec)


mysql> insert into myTable_transaction_test value(7);
Query OK, 1 row affected (0.00 sec)


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


mysql> select*from myTable_transaction_test;  #因为回滚所以数据没有插入
+------+
| id   |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.00 sec)

18 ALTER命令

需要修改数据表名或者修改数据表字段时，需要使用到MySQL ALTER命令。
1）创建一张表

mysql> use TEST;
Database changed
mysql> CREATE TABLE myTable_testALTER(
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2）删除、添加或修改表字段 ——用ALTER命令和DROP子句删除表的i字段

mysql> ALTER TABLE myTable_testALTER DROP i;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

如果数据表中只剩一个字段则无法使用DROP来删除字段，使用ADD子句向数据表添加列：

mysql> ALTER TABLE myTable_testALTER ADD i INT;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

可以看到 i字段自动添加到数据表字段的末尾。
指定新增字段的位置，关键字FIRST(设定位第一列)：

mysql> ALTER TABLE myTable_testALTER DROP i;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE myTable_testALTER ADD i INT FIRST;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> ALTER TABLE myTable_testALTER DROP i;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE myTable_testALTER ADD i INT AFTER c;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3）修改字段类型及名称

在ALTER命令中使用MODIFY或CHANGE子句

mysql> ALTER TABLE myTable_testALTER MODIFY c CHAR(10);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c     | char(10) | YES  |     | NULL    |       |
| i     | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> ALTER TABLE myTable_testALTER CHANGE i j BIGINT;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


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


mysql> ALTER TABLE myTable_testALTER CHANGE j j INT;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c     | char(10) | YES  |     | NULL    |       |
| j     | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4）ALTER TABLE 对NULL值和默认值的影响

修改字段时，可以指定是否包含值或者是否设置默认值

指定字段j为NOT NULL且默认值为100：

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


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

如果不设置默认值，MySQL会自动设置该字段默认为NULL。
5）修改字段默认值

mysql> ALTER TABLE myTable_testALTER ALTER c SET DEFAULT 1000;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM myTable_testALTER;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | 1000    |       |
| j     | bigint(20) | NO   |     | 100     |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

可以使用ALTER命令及DROP子句删除字段的默认值。

19 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

插入1000万数据，不加索引时查询需要1.9秒，加索引后查询只需要0.02秒。

20 临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。


mysql> use TEST;
Database changed
mysql> CREATE TEMPORARY TABLE SalesSUmmary(
    -> product_name VARCHAR(50) NOT NULL,
    -> total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    -> avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
    -> total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
    -> );
Query OK, 0 rows affected (0.01 sec)


mysql> INSERT INTO SalesSUmmary(
    -> product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber',100.25,90,2);
Query OK, 1 row affected (0.00 sec)


mysql> SELECT*from SalesSUmmary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

使用SHOW TABLES命令显示数据表列表时，无法看到数据表。退出当前MySQL会话，则临时表自动被销毁。

手动删除临时表：

mysql> DROP TABLE SalesSUmmary;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT*from SalesSUmmary;
ERROR 1146 (42S02): Table 'test.salessummary' doesn't exist

21 复制表

完全的复制MySQL的数据表，包括表的结构，索引，默认值等。如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。
如何完整的复制MySQL数据表，步骤如下：

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。
步骤一：获取数据表的完整结构


mysql> SHOW CREATE TABLE myTable \G;
*************************** 1. row ***************************
       Table: myTable
Create Table: CREATE TABLE `mytable` (
  `myTable_id` int(11) NOT NULL AUTO_INCREMENT,
  `myTable_title` varchar(100) NOT NULL,
  `myTable_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`myTable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified

步骤二：修改SQL语句的数据表名，并执行SQL语句


mysql> CREATE TABLE `clone_myTable` (
    -> `myTable_id` int(11) NOT NULL AUTO_INCREMENT,
    -> `myTable_title` varchar(100) NOT NULL,
    -> `myTable_author` varchar(40) NOT NULL,
    -> `submission_date` date DEFAULT NULL,
    -> PRIMARY KEY (`myTable_id`)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

步骤三：在数据库中创建新的克隆表，拷贝数据表的数据可以使用INSERT INTO ... SEKECT


mysql> INSERT INTO clone_myTable (myTable_id,
    -> myTable_title,
    -> myTable_author,
    -> submission_date)
    -> SELECT myTable_id,myTable_title,
    -> myTable_author,submission_date
    -> FROM myTable;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT*from clone_myTable;
+------------+---------------+----------------+-----------------+
| myTable_id | myTable_title | myTable_author | submission_date |
+------------+---------------+----------------+-----------------+
|          1 | php           | 教程           | 2019-09-17      |
|          2 | MySQL         | 教程           | 2019-09-17      |
|          4 | Python        | cn.com         | 2019-09-17      |
|          5 | LINUX         | cn.com         | 2019-09-17      |
+------------+---------------+----------------+-----------------+
4 rows in set (0.00 sec)

22 处理重复数据

1）防止表出现重复数据
在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键）或者 UNIQUE（唯一）索引来保证数据的唯一性。
一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。



mysql> CREATE TABLE person_tb (
    -> first_name CHAR(20),
    -> last_name CHAR(20),
    -> sex CHAR(10)
    -> );
Query OK, 0 rows affected (0.04 sec)

设置表中字段 first_name，last_name 数据不能重复，可以设置双主键模式来设置数据的唯一性，如果设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：

mysql> CREATE TABLE person_tb (
    -> first_name CHAR(20) NOT NULL,
    -> last_name CHAR(20) NOT NULL,
    -> sex CHAR(10),
    -> PRIMARY KEY (last_name,first_name)
    -> );
Query OK, 0 rows affected (0.04 sec)

设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：


mysql> INSERT IGNORE INTO person_tb (last_name,first_name)
    -> VALUES('Jay','Thomas');
Query OK, 1 row affected (0.01 sec)


mysql> INSERT IGNORE INTO person_tb (last_name,first_name)
    -> VALUES('Jay','Thomas');
Query OK, 0 rows affected, 1 warning (0.00 sec)

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。
另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：


mysql> CREATE TABLE person_tb (
    -> first_name CHAR(20) NOT NULL,
    -> last_name CHAR(20) NOT NULL,
    -> sex CHAR(10),
    -> UNIQUE (last_name,first_name)
    -> );
Query OK, 0 rows affected (0.04 sec)

统计重复数据


mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tb
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

以上查询语句将返回 person_tbl表中重复的记录数。一般情况下，查询重复的值，请执行以下操作：
确定哪一列包含的值可能会重复。
在列选择列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句设置重复数大于1。

3)过滤重复数据
要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据：

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tb;

也可以使用 GROUP BY 来读取数据表中不重复的数据：


mysql> SELECT last_name, first_name
    -> FROM person_tb
    -> GROUP BY (last_name, first_name);

4）删除重复数据



mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tb  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tb;
mysql> ALTER TABLE tmp RENAME TO person_tb;

也可以在数据表中添加 INDEX（索引）和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：



mysql> ALTER IGNORE TABLE person_tb
    -> ADD PRIMARY KEY (last_name, first_name);

参考：菜鸟教程

posted @ 2019-09-17 21:15 Christine_7 阅读(193) 评论(0) 编辑收藏举报

刷新页面返回顶部

Christine

仰望星空脚踏实地

MySQL 基础操作

1 创建数据库命令格式：`CREATE DATABASE 数据库名;`

2 删除数据库命令格式：`drop database 数据库名;`

3 选择数据库命令格式：`use 数据库名;`

4 数据类型：数值、日期/时间和字符串(字符)类型。

5 创建数据表

6 删除数据表命令格式：`DROP TABLE table_name ;`

7 插入数据命令格式：

8 查询数据

9 MySQL WHERE 子句

10 MySQL UPDATE 更新

11 MySQL DELETE语句

12 LIKE子句

13 UNION 操作符

14 排序

15 JOIN 连接

16 NULL 值处理

17 MySQL事物

18 ALTER命令

19 索引

20 临时表

21 复制表

22 处理重复数据

公告

Christine

仰望星空 脚踏实地

MySQL 基础操作

1 创建数据库 命令格式：CREATE DATABASE 数据库名;

2 删除数据库 命令格式：drop database 数据库名;

3 选择数据库 命令格式：use 数据库名;

4 数据类型： 数值、日期/时间和字符串(字符)类型。

5 创建数据表

6 删除数据表 命令格式：DROP TABLE table_name ;

7 插入数据 命令格式：

8 查询数据

9 MySQL WHERE 子句

10 MySQL UPDATE 更新

11 MySQL DELETE语句

12 LIKE子句

13 UNION 操作符

14 排序

15 JOIN 连接

16 NULL 值处理

17 MySQL事物

18 ALTER命令

19 索引

20 临时表

21 复制表

22 处理重复数据

公告

仰望星空脚踏实地

1 创建数据库命令格式：`CREATE DATABASE 数据库名;`

2 删除数据库命令格式：`drop database 数据库名;`

3 选择数据库命令格式：`use 数据库名;`

4 数据类型：数值、日期/时间和字符串(字符)类型。

6 删除数据表命令格式：`DROP TABLE table_name ;`

7 插入数据命令格式：