数据库MySQL

主要语句

一、数据库

-- 创建数据库
CREATE DATABASE demo；
-- 删除数据库
DROP DATABASE demo；
-- 查看数据库
SHOW DATABASES;
-- 创建数据表
CREATE TABLE demo.test( 
字段名 类型, 
字段名 类型); 
-- 查看表结构
DESCRIBE demo.test;
-- 查看所有表
SHOW TABLES;

其他

SHOW DATABASES
describe demo.test;
user demo; show tables;

二、数据表

创建表 like

CREATE TABLE demo.importheadhist
LIKE demo.importhead;

 

主键

ALTER TABLE demo.testADD COLUMN itemnumber int PRIMARY KEY AUTO_INCREMENT;

* ALTER TABLE，表示修改表；

* ADD COLUMN，表示增加一列；

* PRIMARY KEY，表示这一列是主键；

* AUTO_INCREMENT，自增；

插入

INSERT INTO demo.test
(barcode,goodsname,price)
VALUES ('0001','本',3);

修改

-- 修改字段类型语句
ALTER TABLE demo.goodsmaster;
MODIFY COLUMN price DOUBLE;

添加字段

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST|AFTER 字段名;

更新

UPDATE 表名
SET 字段名=值
WHERE 条件

 

推荐的字段类型

整数：INT。
小数：DECIMAL。
字符串：TEXT。
日期与时间：DATETIME。(包含日期和时间)

　　TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

 

 

 三、主键的选择

尽量不是选择业务字段作为主键，因为很有可能因为后续业务的发展导致主键重复。可以选择自增的字段

修改主键

删掉原先的主键
ALTER TABLE demo.membermaster
DROP PRIMARY KEY;
重新赋予别的字段为主键
ALTER TABLE demo.membermaster
ADD id INT PRIMARY KEY AUTO_INCREMENT;

三、外键

CREATE TABLE demo.importdetails( 
        listnumber INT, 
        itemnumber INT, 
        quantity DECIMAL(10,3), 
        importprice DECIMAL(10,2), 
        importvalue DECIMAL(10,2),
        CONSTRAINT fk_importdetails_importhead FOREIGN KEY (listnumber) REFERENCES importhead (listnumber));

运行这个 SQL 语句，我们就在创建表的同时定义了一个名字叫“fk_importdetails_importhead”的外键约束。同时，我们声明，这个外键约束的字段“listnumber”引用的是表 importhead 里面的字段“listnumber”。

内连接

select 
 a.transactionno,  a.itemnumber,  a.quantity,  a.price,  a.transdate,  b.membername
     from 
          demo.trans as a 
               join 
          demo.membermaster as b on (a.cardno = b.cardno)

外连接

（1）左连接，一般简写成 LEFT JOIN，返回左边表中的所有记录，以及右表中符合连接条件的记录。

SELECT 
a.transactionno, a.itemnumber, a.quantity, a.price, a.transdate, b.membername
    FROM demo.trans AS a
        LEFT JOIN 
    demo.membermaster AS b
    ON (a.cardno = b.cardno);

 

 （2）右连接，一般简写成 RIGHT JOIN，返回右边表中的所有记录，以及左表中符合连接条件的记录。

SELECT 
a.transactionno, a.itemnumber, a.quantity, a.price, a.transdate, b.membername, a.cardno
    FROM demo.trans AS a
        right join 
    demo.membermaster AS b
    ON (a.cardno = b.cardno);

 

 

 其实，在 MySQL 中，外键约束不是关联查询的必要条件。很多人往往在设计表的时候，觉得只要连接查询就可以搞定一切了，外键约束太麻烦，没有必要。

但是，虽然你不用外键约束，也可以进行关联查询，但是有了它，MySQL 系统才会保护你的数据，避免出现误删的情况，从而提高系统整体的可靠性。

 

为什么在 MySQL 里，没有外键约束也可以进行关联查询呢？
原因是外键约束是有成本的，需要消耗系统资源。对于大并发
的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，
可能会因为外键约束的系统开销而变得非常慢。所以，MySQL 
允许你不使用系统自带的外键约束，在应用层面完成检查数据
一致性的逻辑。也就是说，即使你不用外键约束，也要想办法
通过应用层面的附加逻辑，来实现外键约束的功能，确保数据
的一致性。

四、where 和 having

如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

mysql> SELECT 
-> a.transdate, 
-> c.operatorname, 
-> SUM(b.quantity), 
-> SUM(b.salesvalue) 
-> FROM 
-> demo.transactionhead AS a 
-> JOIN 
-> demo.transactiondetails AS b ON (a.transactionid = b.transactionid) 
-> JOIN 
-> demo.operator AS c ON (a.operatorid = c.operatorid) 
-> WHERE a.transdate in ('2020-12-12','2020-12-11') -- 先按日期筛选 
-> GROUP BY a.transdate , operatorname 
-> HAVING SUM(b.salesvalue)>100; -- 后按金额筛选
+---------------------+--------------+-----------------+-------------------+
| transdate           | operatorname | SUM(b.quantity) | SUM(b.salesvalue) |
+---------------------+--------------+-----------------+-------------------+
| 2020-12-11 00:00:00 | 李强          | 2.000           | 178.00            |
+---------------------+--------------+-----------------+-------------------+
1 row in set (0.00 sec)

包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

 五、索引

index

创建索引

CREATE INDEX index_trans_branchnumber ON demo.trans (branchnumber);
CREATE INDEX 索引名                    ON TABLE 表名  (字段);
创建表时顺便创建索引
CREATE TABLE 表名(字段 数据类型,….{ INDEX | KEY } 索引名(字段))
修改表时顺便创建索引
ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段);

索引有单个字段的索引也有多个字段组合起来的组合索引

六、事务

用经典的消费问题就可以介绍，你从微信账号里提现 100 元到银行卡上，这个动作就包括了相互关联的 2 个步骤，首先是微信账号减 100 元，然后是银行卡账号加 100 元（这里假设没有手续费）。假如因为某种异常，这 2 个操作只执行了一个，另外一个没有执行，就会出现你的钱少了 100 元，或者你的钱多了 100 元的情况，这肯定是不能接受的。

事务有 4 个主要特征，分别是原子性（atomicity）、一致性（consistency）、持久性（durability）和隔离性（isolation）。

原子性：表示事务中的操作要么全部执行，要么全部不执行，像一个整体，不能从中间打断。

一致性：表示数据的完整性不会因为事务的执行而受到破坏。

隔离性：表示多个事务同时执行的时候，不互相干扰。不同的隔离级别，相互独立的程度不同。

持久性：表示事务对数据的修改是永久有效的，不会因为系统故障而失效。

mysql> START TRANSACTION;   -- 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO demo.mytrans VALUES (1,1,5); -- 插入流水
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE demo.inventory SET invquantity = invquantity - 5 WHERE itemnumber = 1;                         -- 更新库存
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;                          -- 提交事务
Query OK, 0 rows affected (0.06 sec)

MySQL 支持 4 种事务隔离等级。

READ UNCOMMITTED（读未提交）：可以读取事务中还未提交的被更改的数据。

READ COMMITTED（读已提交）：只能读取事务中已经提交的被更改的数据。

REPEATABLE READ（可重复读）：表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这也是 MySQL 的默认选项。

SERIALIZABLE（串行化）：表示任何一个事务，一旦对某一个数据进行了任何操作，那么，一直到这个事务结束，MySQL 都会把这个数据锁住，禁止其他事务对这个数据进行任何操作。

这么看起来比较不好理解，我们把它们的名字丰富一下就比较容易了。

读未提交：“可读未提交的”，你可以看到别人修改的值但还没提交的；

读已提交：“只可读已提交的”，你只能看到别人commit后的数据；

可重复读：在你的事务开始之后，你所处的版本就是在你开启视图时的当前版本，在你commit之前，你不认可所有在你事务期间的所有改变，你只沉浸在你的世界里。“可重复读”的意思就是，你在事务开启的时候看到的东西，在你事务里的任何时刻你都可以看到完全一样的东西。在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

串行化：就是加锁。

七、存储过程

mysql> DELIMITER // -- 修改分隔符为 //
mysql> CREATE PROCEDURE demo.mytest() -- 创建存储过程
-> BEGIN -- 开始程序体
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; -- 定义SQL操作发生错误是自动回滚
-> START TRANSACTION; -- 开始事务
-> INSERT INTO demo.mytrans VALUES (1,5);
-> UPDATE demo.inventory SET invquantity = invquantity - 5;
-> COMMIT; -- 提交事务
-> END
-> // -- 完成创建存储过程
Query OK, 0 rows affected (0.05 sec) 
mysql> DELIMITER ; -- 恢复分隔符为；
mysql> CALL demo.mytest(); -- 调用存储过程
Query OK, 0 rows affected (0.00 sec) 
mysql> SELECT * FROM demo.mytrans; -- 销售流水没有插入
Empty set (0.00 sec)
mysql> SELECT * FROM demo.inventory; -- 库存也没有消减，说明事务回滚了
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
|      1     |   10.000    |
+------------+-------------+
1 row in set (0.00 sec)

“declare exit handler for sqlEeception rollback;”
这个语句，来监控 SQL 语句的执行结果，一旦发发生错误，就自动回滚并退出。

八、临时表

有点像是把复杂的方法里面的一部分抽出来成一个方法的思维。感觉入门这么理解就够了，后面深入了解应该要结合工作的实际情况。
八、锁