Mysql

启动命令

系统偏好设置启动mysql

PATH="$PATH":/usr/local/mysql/bin # 链接
mysql -u root -p # 登录

库的管理

创建

create database xxx;
mysqladmin -u root -p create xxx;

删除

drop database xxx;
mysqladmin -u root -p drop xxx;

选择

use xxx;
# xxx changed

数据类型

数值

数值类型表

日期和时间类型

日期和时间类型表

字符串类型

字符串类型图表

表的创建、删除、数据插入、查询

表的创建

CREATE TABLE table_name (column_name, column_type, ... , PRIMARY KEY (key_name))
	ENGINE = InnoDB DEFAULT CHARSET = utf8;

注意： 字段属性如果要空值，尽量NOT NULL， 否则容易报错

数据库中创建表实例

命令行创建

表的删除

DROP TABLE table_name;

表中插入数据

INSERT INTO table_name(field 1, field 2,... field n)
					   VALUES
					  (value 1, value 2,... value n);

⚠️ 数据为字符型需要加入单引号或双引号

表中查询数据

SELECT * FROM table_name; # 读取表中所有数据

WHERE子句

语法

SELECT field1, field2, ... , fieldn FROM table1, table2, ... , tablen
[WHERE condition1 [AND [OR]] condition2

默认没有大小写区分, 使用BINARY WHERE可以实现大小写区分

SELECT * from table_name BINARY WHERE filed1 = value1;

=表示等于， <> or !=表示不等于

UPDATE 子句

UPDATE table_name SET field1=value1, field2=value2;
[WHERE Clause]

• 可以同时更新多个字段
• 可以在WHERE语句中指定多个条件
• 一个单独表中同时更新数据

更改指定id的语句

UPDATE table_name SET field1=value1 WHERE id=id_value;

DELETE 子句

DELETE FROM table_name [WHERE Clause];

• 没有制定WHERE子句，那么表中所有记录将被删除

LIKE 子句

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

• LIKE子句中可以用%来代替任何字符
• 对于DELETE、UPDATE命令也可以使用LIKE子句

LIKE子句实例

UNION 操作符

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT experssion1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• expression 是要检索的列
• tables 要检索的表
• WHERE conditions 可选，要检索的条件
• DISTINCT 删除重复数据，但是UNION已经默认删除重复
• ALL 返回所有数据，包含重复的数据

UNION实例p1

UNION实例p2

带有WHERE子句实例

ORDER BY 子句

SELECT field1, field2,...fieldN FROM tbl1, tbl2,... tblN
ORDER BY field1 [ASC / DESC[默认 ASC]]

• 默认升序排序
• 也可以加上WHERE或者LIKE来设置条件

GROUP BY 子句

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

SELECT col_name, function(col_name)
FROM tbl_name
WHERE col_name operator value
GROUP BY col_name;

GROUP BY 实例1

GROUP BY 实例2

连接的使用

• INNER JOIN(内连接、等值连接)：获取两个表中匹配的记录
• LEFT JOIN(左连接)：获取左表所有的记录，即使右表没有对应的记录
• RIGHT JOIN(右连接)：与LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

连接实例数据

INNER JOIN实例

LEFT JOIN实例

RIGHT JOIN实例

NULL值的处理

• MySQL中不使用=和!=来对NULL进行比较运算
• MySQL提供了3个运算符来对NULL进行处理：
  • IS NULL: 列的值为NULL, 返回true
  • IS NOT NULL: 列的值不为NULL，返回true
  • <=>: 比较操作符，当比较的两个值都相等或者都为NULL，返回true

NULL实例

正则表达式

• 使用REGXGP操作符

正则表达式符号表

正则表达式实例

事务

MySQL事务用于处理操作量大、复杂性高的数据，以一个例子来说明就是在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

注意：

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务可以维护数据库的完整性，保证批量的SQL语句要么执行，要么不执行
• 事务用来管理insert, update, delete 语句（涉及数据库信息改动）

重点⚠️

一个事务一般来说要满足的4个条件（ACID）：原子性（Atomicity，或不可分割性)，
一致性(Consistency)、隔离性（Isolation，又称独立性)、持久性(Durability)。

1. 原子性：一个事务(transaction)中所有的操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从未执行过一样（类似于撤销的感觉。

2. 一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发地完成预定的工作。

3. 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时
   由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括：

   • 未提交(Read uncommitted)
   • 读提交(read committed)
   • 可重复读(repeatable read)
   • 串行化(Serializable)

4. 持久性：事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失。

   在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句

基本操作步骤

• BEGIN 开始一个事务
• ROLLBACK 回滚事务
• COMMIT 提交事务

# 操作1
begin;
    # 若干SQL语句
commit; # 提交事务
# 操作2
begin;
    # 若干SQL语句
rollback; # 回滚事务

使用保存点 SAVEPOINT

• savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
• ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT，执行多条操作时，回滚到想要的那条语句之前。

使用SAVEPOINT

SAVEPOINT savepoint_name;   # 声明一个savepoint
ROLLBACK TO savepoint_name; # 回滚到savepoint

删除SAVEPOINT

SAVEPOINT在事务处理完成后(执行一条ROLLBACK或者COMMIT)，自动RELEASE（释放）。

RELEASE SAVEPOINT savepoint_name;   # 删除指定savepoint

ALTER 命令

用于修改数据库表名或者修改数据库表字段时，就需要用到ALTER命令

删除、添加字段

DROP 删除，如果数据表中只有一个字段是无法用DROP删除的

ALTER TABLE tbl_name DROP key_name;

ADD 添加

ALTER TABLE tbl_name ADD key_name TYPE;
ALTER TABLE tbl_name ADD key_name TYPE FIRST;   # 添加在第一列
ALTER TABLE tbl_name ADD key_name TYPE AFTER key2_name; # 添加在某字段之后

FIRST和AFTER可以用于ADD和MODIFY子句，要重置字段位置就先DROP再ADD即可

修改字段类型及名称

使用MODIFY或者CHANGE子句

MODIFY使用举例

ALTER TABLE tbl_name MODIFY key_name TYPE; # 将字段类型改为你想要的类型

CHANGE使用举例

ALTER TABLE tbl_name CHANGE key_name1 key_name2 TYPE # 将key1修改，名字为key_name2，类型为TYPE

ALTER TABLE 默认值

不设置默认值，字段默认为NULL，否则

ALTER TABLE tbl_name MODIFY key_name TYPE NOT NULL DEFAULT VALUE; # 字段默认为value不为null

修改默认值

使用ALTER修改字段默认值

ALTER TABLE tbl_name ALTER key_name SET DEFAULT VALUE; # 默认值修改为VALUE

ALTER和DROP删除字段默认值

ALTER TABLE tbl_name ALTER key_name DROP DEFAULT;

ALTER和TYPE修改数据表类型

ALTER TABLE tbl_name ENGINE = MYISAM; # 修改数据库引擎为MYISAM

可以使用SHOW TABLE STATUS语句(搭配LIKE)查看数据库状态

SHOW TABLE STATUS LIKE 'tbl_name'\G

修改表名

通过RENAME TO子句实现

ALTER TABLE tbl_name RENAME TO new_name;

ALTER命令同样可以用于创建和删除表的索引,详见下节

索引