Mysql基本操作

mysql的基本操作包括增、删、改、查，本书中前三章简单的介绍MySQL为何物，查是mysql中非常重要的功能，4-6章展示了mysql的查（查询——select）的简单实现，mysql的增（插入——insert）是第19章，改（更新——update）和删（删除——delete）是第20章，基本操作还包括21章表操作。

所以这篇文章归纳的是1-6,19-21章的笔记。

第一章了解SQL

主键
- 含义：一列（或一组列），其值能够唯一区分表中每个行。
- 习惯：
  - 不更新主键列中的值；
  - 不重用主键列的值；
  - 不在主键列中使用可能会更改的值。（例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键。）

第二章 MySQL简介

略

第三章使用MySQL

连接

mysql -u root -p

-u：用户名
-p：输入密码

补充：

-h：主机地址
-P：端口
mysql --help：查看MySQL命令行选项

选择数据库

use 数据库名

了解数据库和表

show databases;

SHOW DATABASES;返回可用数据库的一个列表。

show tables;

SHOW TABLES;返回当前选择的数据库内可用表的列表。

SHOW COLUMNS FROM tablename;

示例：

mysql> show columns from blog;

+---------+--------------+------+-----+---------+----------------+

| Field   | Type         | Null | Key | Default | Extra          |

+---------+--------------+------+-----+---------+----------------+

| id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |

| name    | varchar(255) | NO   |     | NULL    |                |

| handle  | varchar(255) | NO   |     | NULL    |                |

| user_id | bigint(20)   | YES  | MUL | NULL    |                |

+---------+--------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

SHOW也可以用来显示表列。

SHOW COLUMNS 要求给出一个表名。

DESCRIBE语句

MySQL支持用DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式。换句话说，DESCRIBE customers;是 SHOW COLUMNS FROM customers;的一种快捷方式。

推荐使用DESCRIBE customers这种方式！

mysql> describe blog;

+---------+--------------+------+-----+---------+----------------+

| Field   | Type         | Null | Key | Default | Extra          |

+---------+--------------+------+-----+---------+----------------+

| id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |

| name    | varchar(255) | NO   |     | NULL    |                |

| handle  | varchar(255) | NO   |     | NULL    |                |

| user_id | bigint(20)   | YES  | MUL | NULL    |                |

+---------+--------------+------+-----+---------+----------------+

其他show语句：

SHOW STATUS，用于显示广泛的服务器状态信息
SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句

mysql> show create table blog;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                  |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| blog  | CREATE TABLE `blog` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `handle` varchar(255) NOT NULL,

  `user_id` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_blog_user_id` (`user_id`),

  CONSTRAINT `fk_blog_user_id` FOREIGN KEY (`user_id`) REFERENCES `jhi_user` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限
SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。
执行命令 HELP SHOW;显示允许的SHOW语句

第四章检索数据

检索单个列

-- 检索products表中的prod_name列

SELECT prod_name FROM products;

检索多个列

-- 检索products表中的prod_id，prod_name和prod_price列

SELECT prod_id, prod_name, prod_price FROM products;

检索所有列（使用`*`通配符）

--检索products表中的所有列

SELECT * FROM products;

检索不同的行(使用`DISTINCT`关键字)

SELECT返回所有匹配的行。但是，如果你不想要每个值每次都出现，怎么办？

解决办法是使用DISTINCT关键字，顾名思义，此关键字指示MySQL只返回不同的值。

-- DISTINCT关键字必须直接放在列名的前面，不能部分使用DISTINCT，DISTINCT关键字应用于所有列而不仅是前置它的列。

SELECT DISTINCT vend_id FROM products;

示例：

mysql> select * from blog;

+----+-------------------------+------------------------------------+---------+

| id | name                    | handle                             | user_id |

+----+-------------------------+------------------------------------+---------+

|  1 | Integration             | syndicate Sleek                    |    NULL |

|  2 | Chief Analyst           | Savings Account disintermediate    |    NULL |

|  3 | panel payment Group     | Producer                           |    NULL |

|  4 | Executive open-source   | generate Saint Martin              |    NULL |

|  5 | Tuna modular systems    | Canyon transmit                    |    NULL |

|  6 | Intranet                | quantifying                        |    NULL |

|  7 | Accounts                | deposit repurpose                  |    NULL |

|  8 | Multi-layered           | Home Loan Account Balboa US Dollar |    NULL |

|  9 | Plastic Diverse Dynamic | JBOD New Zealand Buckinghamshire   |    NULL |

| 10 | payment                 | website                            |    NULL |

| 11 | 罗翔说刑法              | admin                              |       3 |

| 12 | 法外狂徒张三            | user                               |       4 |

| 13 | 法外狂徒张三            | user                               |       4 |

+----+-------------------------+------------------------------------+---------+

如上，最后是有两行是重复的，如果我不想得到重复的数据：

mysql> select distinct name from blog;

+-------------------------+

| name                    |

+-------------------------+

| Integration             |

| Chief Analyst           |

| panel payment Group     |

| Executive open-source   |

| Tuna modular systems    |

| Intranet                |

| Accounts                |

| Multi-layered           |

| Plastic Diverse Dynamic |

| payment                 |

| 罗翔说刑法              |

| 法外狂徒张三            |

+-------------------------+

12 rows in set (0.00 sec)

限制显示结果（使用`limit`关键字）

-- 返回不多于5行

SELECT prod_name FROM products LIMIT 5;

-- 返回从第5行开始的5行

SELECT prod_name FROM products LIMIT 5,5;

示例：

mysql> select name from blog limit 10;

+-------------------------+

| name                    |

+-------------------------+

| Integration             |

| Chief Analyst           |

| panel payment Group     |

| Executive open-source   |

| Tuna modular systems    |

| Intranet                |

| Accounts                |

| Multi-layered           |

| Plastic Diverse Dynamic |

| payment                 |

+-------------------------+

10 rows in set (0.00 sec)

mysql> select name from blog limit 5,5;

+-------------------------+

| name                    |

+-------------------------+

| Intranet                |

| Accounts                |

| Multi-layered           |

| Plastic Diverse Dynamic |

| payment                 |

+-------------------------+

5 rows in set (0.00 sec)

检索出来的第一行为行0，因此LIMIT 1,1检索出来的是第二行而不是第一行 MySQL 5 支持LIMIT的另一种替代语法 LIMIT 4 OFFSET 3为从行3开始取4行，同LIMIT 3,4

使用完全限定的表名

SELECT products.prod_name FROM products;

SELECT products.prod_name FROM book.products;

第五章排序数据

order by

ELECT prod_name

FROM products

ORDER BY prod_name;

按多个列排序

SELECT prod_id, prod_price, prod_name

FROM products

ORDER BY prod_price, prod_name;

对于上述例子中的输出，仅在多个行具有相同的prod_price 值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

指定排序方向

默认升序排序，降序使用DESC关键字

SELECT prod_id, prod_price, prod_name

FROM products

ORDER BY prod_price DESC;

升序关键字ASC，可省略

如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

SELECT prod_id, prod_price, prod_name

FROM products

ORDER BY prod_price DESC, prod_name DESC;

找出一列中最高或最低的值

ORDET BY 与 LIMIT 的组合，能够找出一个列中最高或最低的值

SELECT prod_proce FROM products

ORDER BY prod_price DESC LIMIT 1;

给出ORDER BY句子时，应保证位于FROM句子之后，如果使用LIMIT，应位于ORDER BY之后。

第六章过滤数据

使用WHERE子句

-- 返回prod_price为2.50的行

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50

在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误

WHERE子句操作符

符号	说明
=	等于
<>	不等于
！=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

使用示例

检查单个值

返回prod_name为Fuses的一行（匹配时默认不区分大小写）

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';

列出小于10美元的所有产品

SELECT prod_name, prod_price FROM products WHERE prod_price < 10;

列出小于等于10美元的所有产品

SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;

不匹配检查

列出不是1003的所有产品

SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

-- 或者

SELECT vend_id, prod_name FROM products WHERE vend_id ！= 1003;

范围值检查

检索价格在5-10美元之间的所有产品

SELECT prod_name, prod_price FROM products

WHERE prod_price BETWEEN 5 AND 10;

空值检查

返回价格为空的所有产品

SELECT prod_name FROM products WHERE prod_price IS NULL;

第十九章插入数据

增删改查中的增功能就是insert，你可以使用insert完成如下操作：

插入完整的行
插入行的一部分
插入多行
插入某些查询的结果

插入完整的行

INSERT INTO Customers

VALUES(NULL,

    'Pep E. LaPew',

    '100 Main Street',

    'Los Angles',

    'CA',

    '90046',

    'USA',

    NULL,

    NULL);

语法简单但不安全。更安全的方法为：

INSERT INTO customers(cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country,

    cust_contact,

    cust_email)

VALUES('Pep E. LaPew',

    '100 Main Street',

    'Los Angeles',

    'CA',

    '90046'

    'USA'

    NULL,

    NULL);

安全体现在写了列名，插入的数据会根据列名填充。

如下操作填充的结果与上面代码相同，但它的填充次序可以不一样，这就是安全的体现：

INSERT INTO customers(cust_name,

    cust_contact,

    cust_email,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country)

VALUES('Pep E. LaPew',

    NULL,

    NULL,

    '100 Main Street',

    'Los Angles',

    'CA',

    '90046',

    'USA');

补充： INSERT语句一般不会产生输出。

插入多个行

INSERT INTO customers(cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country)

VALUES('Pep E. LaPew',

    '100 Main Street'

    'Los Angeles',

    'CA',

    '90046',

    'USA');

INSERT INTO customers(cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country)

VALUES('M. Martian',

    '42 Galaxy Way'

    'New York',

    'NY',

    '11213',

    'USA');

太麻烦了，可以使用组合句，一般也是这样写

INSERT INTO customers(cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country)

VALUES('Pep E. LaPew',

    '100 Main Street'

    'Los Angeles',

    'CA',

    '90046',

    'USA')，

    ('M. Martian',

    '42 Galaxy Way'

    'New York',

    'NY',

    '11213',

    'USA');

每组值用一对圆括号括起来，用逗号分隔。

插入检索出的数据

使用insert和select组合，可以将select出来的数据插入到表中。

INSERT INTO customers(cust_id,

    cust_contact,

    cust_email,

    cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country)

SELECT cust_id,

    cust_contact,

    cust_email,

    cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country

FROM custnew;

为简单起见，这个例子在INSERT和 SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，MySQL甚至不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据是非常有用的。

第二十章更新和删除数据

更新数据

可以使用update完成：

更新表中特定行
更新表中所有行
- 尽量不要省略where子句，稍不注意就会更新表中所有的行。

update预计由3部分组成：

要更新的表
列名和它们的新值
确定要更新行的过滤条件

如：客户10005现在有了电子邮件地址，因此他的记录需要更新：

UPDATE customers

SET cust_email = 'elmer@fudd.com'

WHERE cust_id = 10005;

更新多个列的语法稍有不同：

UPDARTE customers

SET cust_name = 'The Fudds',

cust_email = 'elmer@fudd.com'

WHERE cust_id = 10005;

在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）。

IGNORE关键字:如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：UPDATE IGNORE customers…

删除数据

使用DELETE语句

从表中删除特定的行
从表中删除所有的行
- 尽量不要省略where子句，稍不注意就会删除表中所有的行。

示例：

DELETE FROM customers

WHERE cust_id = 10006;

更新和删除的指导原则

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

第二十一章创建和操纵表

使用具有交互式创建和管理表的工具；
表也可以直接用MySQL语句操纵。

表创建基础

使用CREATE TABLE创建表，注意事项：

新表的名字，在关键字CREATE TABLE之后给出；
- 指定的表名必须不存在，否则报错
- 如果要创建一个新的同名表，必须手工删除原有表，然后在用创表语句重建它，CREATE TABLE是无法覆盖原有表的。
- 可以在表名后面使用IF NOT EXISTS检查表是否已经存在。
表列的名字和定义，用逗号分隔。

CREATE TABLE customers

(

	cust_id			int 		NOT NULL AUTO_INCREMENT,

    cust_name		char(50)	NOT NULL,

    cust_address 	char(50) 	NULL,

    cust_city		char(50)	NULL,

    cust_state		char(5) 	NULL,

    cust_zip 		char(10) 	NULL,

    cust_country	char(50) 	NULL,

    cust_contact 	char(50) 	NULL,

    cust_email 		char(255)	NULL,

    PRIMARY KEY (cust_id)

) ENGINE = InnoDB;

上面的建表语句要注意几个细节：

NULL/NOT NULL
AUTO_INCREMENT
PRIMARY KEY
ENGINE = InnoDB

使用NULL值

每个表列或者是NULL列或者是NOT NULL列，这种状态在创建时由表的定义规定

CREATE TABLE orders

(

    order_num int NOT NULL AUTO_INCREMENT,

    order_date datetime NOT NULL,

    cust_id int NOT NULL,

    PRIMARY KEY (order_num)

) ENGINE = InnoDB;

建表是可以混合NULL和NOT NULL列的，如上一个例子中就混有。

理解NULL

不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定''（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

解释一下什么是空串：

空串（empty string）：长度为零（即不含任何字符）的字符串。

主键

主键值必须唯一。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

示例：创建多个列组成的主键

CREATE TABLE orderitems

(

    order_num int NOT NULL,

    order_item int NOT NULL,

    prod_id char(10) NOT NULL,

    quantity int NOT NULL,

    item_price decimal(8,2) NOT NULL,

    PRIMARY KEY (order_num, order_item)

)ENGINE = InnoDB;

主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。

本文AUTO_INCREMENT讲得比较晦涩，我补充几点个人理解。

理解1：MySQL的中AUTO_INCREMENT类型的属性用于为一个表中记录自动生成ID功能.

理解2：使用AUTO_INCREMENT的注意事项：

AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列
设置AUTO_INCREMENT属性的数据列应该是一个正数序列
AUTO_INCREMENT数据列必须有唯一索引（索引是MySQL高级部分的知识，这里不展开），以避免序号重复(即是主键或者主键的一部分)。
AUTO_INCREMENT数据列必须具备NOT NULL属性。

理解3：当进行全表删除时，MySQL AUTO_INCREMENT会从1重新开始编号。

MySQL先把数据表里的所有数据和索引删除，然后重建数据表。
如果想删除所有的数据行又想保留序列编号信息，可以用last_insert_id（）获取刚刚自增过的值。

理解4：

auto_increment是用于主键自动增长的，从1开始增长，当你把第一条记录删除时，再插入第二跳数据时，主键值是2，不是1。

我们使用上面创建表的一个例子：

CREATE TABLE orders

(

    order_num int NOT NULL AUTO_INCREMENT,

    order_date datetime NOT NULL,

    cust_id int NOT NULL,

    PRIMARY KEY (order_num)

) ENGINE = InnoDB;

新增一条语句，不手动插入order_num

mysql> insert into orders (order_date,cust_id) values ("2021-01-01",10086);

Query OK, 1 row affected (0.01 sec)

看看结果：

mysql> select * from orders;

+-----------+---------------------+---------+

| order_num | order_date          | cust_id |

+-----------+---------------------+---------+

|         1 | 2021-01-01 00:00:00 |   10086 |

+-----------+---------------------+---------+

1 row in set (0.00 sec)

再删除它

mysql> delete from orders where cust_id =10086;

Query OK, 1 row affected (0.00 sec)

mysql> select * from orders;

Empty set (0.00 sec)

再次插入，不手动插入order_num

mysql> insert into orders (order_date,cust_id) values ("2021-01-02",10010);

Query OK, 1 row affected (0.00 sec)

看看结果：

mysql> select * from orders;

+-----------+---------------------+---------+

| order_num | order_date          | cust_id |

+-----------+---------------------+---------+

|         2 | 2021-01-02 00:00:00 |   10010 |

+-----------+---------------------+---------+

1 row in set (0.00 sec)

order_num变为2，说明每新增一条记录，字段值自动加 1。

我们可以使用last_insert_id（）获取当前的值

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

|                2 |

+------------------+

1 row in set (0.00 sec)

指定默认值

DEFAULT

CREATE TABLE orderitems

(

    order_num int NOT NUL,

    order_item int NOT NULL,

    prod_id char(10) NOT NULL,

    quantity int NOT NULL DEFAULT 1,

    item_price decimal(8,2) NOT NULL,

    PRIMARY KEY (order_num,order_item)

) ENGINE = InnoDB;

MySQL不允许使用函数作为默认值，只支持常量

引擎类型（属于mysql高级的知识，先了解即可）

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。引擎类型可以混用。外键不能跨引擎混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

使用ALTER TABLE更改表的结构，必须给出以下信息：

在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
所做更改的列表。

例：为表vendors新增一列vend_phone

ALTER TABLE vendors

ADD vend_phone CHAR(20);

例：删除刚增加的列

ALTER TABLE vendors

DROP COLUMN vend_phone;

为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

用新的列布局创建一个新表；
使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
检验包含所需数据的新表；
重命名旧表（如果确定，可以删除它）；
用旧表原来的名字重命名新表；
根据需要，重新创建触发器、存储过程、索引和外键。

使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

DROP TABLE customers2;

重命名表

RENAME TABLE customers2 TO customers;

#对多个表重命名

RENAME TABLE backup_customers TO customers,

	         backup_vendors TO vendors,

             backup_products TO products;

补充

推荐使用navicat来学习mysql，就不用面对cmd的黑框框没有提示或补全，像本文一样一个一个字打。而且看表数据表结构也更加美观和直观。

MySQL的注释

#单行注释可以使用"#"

-- 单行注释也可以使用"--"，注意与注释之间有空格

/*

用于多行注释

*/

MySQL必知必会笔记-Mysql基本操作