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的注释
-
#单行注释可以使用"#" -- 单行注释也可以使用"--"，注意与注释之间有空格 /* 用于多行注释 */
-