dustland

dustball in dustland

MYSQL命令

Posted on Edited on

MySQL命令

登录

在linux终端上登录命令为:

1
2
3
>mysql -u<username> -p<password>
>mysql -u<username> -p
>Enter password:<password>

例如:

1
>mysql -uroot -psjh123456

登录成功之后:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@deutschball-virtual-machine:/home/deutschball/桌
面# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

或者:

1
2
>mysql -uroot -p
>Enter password:

后面这种方法密码需要另起一行输入,并且输入的时候不会显示在屏幕上,类似于linux终端登录其他用户账号时的情形

退出登录:

1
mysql>exit;

==注意==

1.登录MySQL数据库之后,命令行自动带上mysql>标志,表示此后执行的所有命令均是对MySQL数据库的操作.

2.MySQL命令也是末行模式,并且以分号";"结束命令

查看用户名下所有数据库

1
mysql>show databases;

例如:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| earth              |
| empire             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

创建数据库

1
>mysql create database <dbname>;

例如:

1
2
mysql> create DATABASE newdb;
Query OK, 1 row affected (0.00 sec)

如果已经存在同名数据库则会报告错误

1
2
mysql> create database newdb;
ERROR 1007 (HY000): Can't create database 'newdb'; database exists

删除数据库

1
mysql>drop database <dbname>;

例如:

1
2
mysql> drop database newdb;
Query OK, 0 rows affected (0.02 sec)

对一个不存在的数据库名使用drop命令会报错:

1
2
mysql> drop database newdb;
ERROR 1008 (HY000): Can't drop database 'newdb'; database doesn't exist

选择数据库

使用show databases命令之后可以看到一系列数据库列表,现在要对其中某个数据库进行操作,应当选中该数据库,类似于cd进入某个子目录进行操作

1
mysql>use <dbname>

例如:

1
2
mysql> use empire;
Database changed

企图使用不存在的数据库将会报错:

1
2
mysql> use noneexist;
ERROR 1049 (42000): Unknown database 'noneexist'

注意此后对数据表的操作需要首先选中数据库

对于单个数据的操作需要首先选中数据表

查看数据表状态

1
SHOW TABLE STATUS LIKE '<tablename>';
status

创建数据表

如果没有事先选中数据库就创建数据表会报错:

1
2
mysql> create TABLE troop;
ERROR 1046 (3D000): No database selected

需要事先使用use命令确定数据库之后才能建立数据表

1
2
3
4
5
6
mysql>CREATE TABLE <tablename>(
	column_name1 data_type(size),
	column_name2 data_type(size),
	...
	column_namen data_type(size)
);

其中后面的圆括号中包含若干对数据,分别是列名称和其数据类型(最大长度)

1
2
3
4
5
6
7
8
mysql> use empire;
Database changed
mysql> CREATE TABLE troop(
    -> id int,
    -> name varchar(255),
    -> age int
    -> );
Query OK, 0 rows affected (0.02 sec)

在数据类型后面可以声明not null,则新增记录时如果该项的值缺省则报错(不声明not null的缺省值自动设为NULL)

1
2
3
4
5
6
7
8
9
mysql> CREATE TABLE fleets(
    -> id INT NOT NULL AUTO_INCREMENT,
    -> commander VARCHAR(100) NOT NULL,
    -> PRIMARY KEY(id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> insert into fleets(id)values(2);
ERROR 1364 (HY000): Field 'commander' doesn't have a default value

属性约束

主键约束

外键约束

空约束

去重约束

默认值约束

断言约束

检查约束

删除数据表

1
drop table <tablename>

例如:

1
2
mysql> drop table troop;
Query OK, 0 rows affected (0.01 sec)

插入数据

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

前面括号中是列栏目名称,插入不是按照数据表创建时的列顺序插入的,而是value与field一一对应

例如:

1
2
3
4
5
6
7
8
9
10
11
mysql> insert into fleets(commander)values('vader');
Query OK, 1 row affected (0.00 sec)

mysql> select * from fleets;
+----+-----------+
| id | commander |
+----+-----------+
|  1 | vader     |
+----+-----------+
1 row in set (0.00 sec)

id已设置自增

查询数据表

1
2
3
4
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

select语句列明要查找的列栏目名称

column_name规定保留查询记录的列目,如果只写星号*,则保留完整记录

from语句指明从哪个数据表查询

where语句给出数据筛选条件

limit语句限定查询数据最大条数

offeset设置开始查询的数据偏移量

例如现有数据库如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select *from fleets;
+----+-----------+
| id | commander |
+----+-----------+
|  1 | vader     |
|  2 | soldier1  |
|  3 | soldier2  |
|  4 | soldier10 |
|  5 | vader     |
|  6 | vader     |
|  7 | vader     |
|  8 | vader     |
|  9 | vader     |
| 10 | vader     |
| 11 | vader     |
| 12 | vader     |
+----+-----------+
12 rows in set (0.00 sec)

要查询所有commander=vader的飞船的id记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select id from fleets where commander='vader';
+----+
| id |
+----+
|  1 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
+----+
9 rows in set (0.00 sec)

打印数据表前五个完整记录:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from fleets limit 5;
+----+-----------+
| id | commander |
+----+-----------+
|  1 | vader     |
|  2 | soldier1  |
|  3 | soldier2  |
|  4 | soldier10 |
|  5 | vader     |
+----+-----------+
5 rows in set (0.00 sec)

WHERE

类似于if语句,用于限定查询,删除等操作的范围.

可以理解为:在满足某某条件的记录上进行某种操作

比如:

1
mysql> select id from fleets where commander='vader';

这句就可以翻译为:

从fleets数据表中查询所有commander列的值为'vader'的记录,返回满足条件的记录的id

where条件为bool表达式

注意判断是否相等只需要使用单等号=,不需要使用双等号==

1
mysql> select * from fleets where id%2=0;

这句可以翻译为:

从fleets数据表中查询所有id为偶数的记录

结果:

1
2
3
4
5
6
7
8
9
10
11
+----+-----------+
| id | commander |
+----+-----------+
|  2 | soldier1  |
|  4 | soldier10 |
|  6 | vader     |
|  8 | vader     |
| 10 | vader     |
| 12 | vader     |
+----+-----------+
6 rows in set (0.00 sec)

where中使用and指定多个条件

1
2
3
4
5
6
7
8
9
mysql> select *from fleets where id%2=0 and id%3=0;
+----+-----------+
| id | commander |
+----+-----------+
|  6 | vader     |
| 12 | vader     |
+----+-----------+
2 rows in set (0.00 sec)

从fleets数据表中查询所有id为2和3的公倍数的记录

where使用binary开启大小写敏感

现有数据表如下(注意第13条记录Vader有大写),要查询所有commander='vader'的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select *from fleets;
+----+-----------+
| id | commander |
+----+-----------+
|  1 | vader     |
|  2 | soldier1  |
|  3 | soldier2  |
|  4 | soldier10 |
|  5 | vader     |
|  6 | vader     |
|  7 | vader     |
|  8 | vader     |
|  9 | vader     |
| 10 | vader     |
| 11 | vader     |
| 12 | vader     |
| 13 | Vader     |
+----+-----------+
13 rows in set (0.00 sec)

使用binary关键字:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select *from fleets where binary commander='vader';
+----+-----------+
| id | commander |
+----+-----------+
|  1 | vader     |
|  5 | vader     |
|  6 | vader     |
|  7 | vader     |
|  8 | vader     |
|  9 | vader     |
| 10 | vader     |
| 11 | vader     |
| 12 | vader     |
+----+-----------+
9 rows in set, 1 warning (0.00 sec)

如果不用binary:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select *from fleets where commander='vader';
+----+-----------+
| id | commander |
+----+-----------+
|  1 | vader     |
|  5 | vader     |
|  6 | vader     |
|  7 | vader     |
|  8 | vader     |
|  9 | vader     |
| 10 | vader     |
| 11 | vader     |
| 12 | vader     |
| 13 | Vader     |
+----+-----------+
10 rows in set (0.00 sec)

数据表更新记录

1
2
UPDATE table_name SET field1=new-value1, field2=new-value2,...
[WHERE Clause]

将fleets数据表中id=5的记录的指挥官重新指派为anakin

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> update fleets set commander='anakin' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from fleets;
+----+-----------+
| id | commander |
+----+-----------+
|  1 | vader     |
|  2 | soldier1  |
|  3 | soldier2  |
|  4 | soldier10 |
|  5 | anakin    |
|  6 | vader     |
|  7 | vader     |
|  8 | vader     |
|  9 | vader     |
| 10 | vader     |
| 11 | vader     |
| 12 | vader     |
| 13 | Vader     |
+----+-----------+
13 rows in set (0.00 sec)

数据表删除记录

1
DELETE FROM table_name [WHERE Clause]

从数据表fleets中删除编号id=1的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> delete from fleets where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from fleets;
+----+-----------+
| id | commander |
+----+-----------+
|  2 | soldier1  |
|  3 | soldier2  |
|  4 | soldier10 |
|  5 | anakin    |
|  6 | vader     |
|  7 | vader     |
|  8 | vader     |
|  9 | vader     |
| 10 | vader     |
| 11 | vader     |
| 12 | vader     |
+----+-----------+
11 rows in set (0.00 sec)

删除后打印数据表发现第一条记录的编号为2,表明id虽然自动增加,但是删除中间的某些记录后,后面的记录的id不会自动减小

如果不使用where子句则删除指定数据表中的所有记录

1
2
3
4
5
6
mysql> delete from fleets;
Query OK, 11 rows affected (0.00 sec)

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

数据表模糊匹配操作

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

关键由LIKE子句实现,首先容易观察得到的是LIKE子句完全包含等号的作用

可以理解为:

从某数据表查询某键值==像==某个样子的记录

比如从动物园数据表中查询==狗样==的记录和从动物园查询所有==狗==的记录,前者为LIKE语句,后者为等号

比如现有数据表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from fleets;
+----+------------+
| id | commander  |
+----+------------+
| 14 | soldier1   |
| 15 | soldier2   |
| 16 | soldier3   |
| 17 | soldier20  |
| 18 | soldier200 |
| 19 | general    |
| 20 | admiral    |
| 21 | major      |
+----+------------+
8 rows in set (0.00 sec)

现在要查询所有commander为soldier==#==(这里#表示任意字符或者字符串)的记录,显然用等号的话只能用where和or进行枚举,

1
mysql> select * from fleets where commander='soldier1' or commander='soldier2' or commander='soldier3'...

可以翻译为:

从数据表fleets中查询所有指挥官为soldier1或者soldier2或者soldier3...的记录

但是用==like配合占位符%==进行模糊搜索

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from fleets where commander like 'soldier%';
+----+------------+
| id | commander  |
+----+------------+
| 14 | soldier1   |
| 15 | soldier2   |
| 16 | soldier3   |
| 17 | soldier20  |
| 18 | soldier200 |
+----+------------+
5 rows in set (0.01 sec)

可以翻译为:

从数据表fleets中查询所有指挥官为soldier后面加上一些东西(管他什么东西,甚至是没有东西)的记录

数据表查找合并

1
2
3
4
5
6
7
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

tables为数据表名

expression为要检索的列

distinct为去重合并,all为不去重合并

例如在empire数据库下有两个数据表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from officers;
+----+----------------+------------+
| id | name           | title      |
+----+----------------+------------+
|  1 | Darth Vader    | executor   |
|  2 | Darth Sidious  | emperor    |
|  3 | Wilhuff Tarkin | Grand Moff |
+----+----------------+------------+
3 rows in set (0.00 sec)

mysql> select *from commanders;
+----+---------------+---------+
| id | name          | title   |
+----+---------------+---------+
|  1 | Rex           | captain |
|  2 | Darth Vader   | general |
|  3 | Darth Sidious | marshal |
+----+---------------+---------+
3 rows in set (0.00 sec)

1.现在统计所有政府官员和军队指挥官一共有多少人(考虑有些人可以集军政大权于一身,需要去重)

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select name from officers
    -> union 
    -> select name from commanders;
+----------------+
| name           |
+----------------+
| Darth Vader    |
| Darth Sidious  |
| Wilhuff Tarkin |
| Rex            |
+----------------+
4 rows in set (0.01 sec)

2.统计名叫Darth Vader的是否身兼数职

1
2
3
4
5
6
7
8
9
10
11
mysql> select *from officers where name='Darth Vader'
    -> union all
    -> select *from commanders where name='Darth Vader';
+----+-------------+----------+
| id | name        | title    |
+----+-------------+----------+
|  1 | Darth Vader | executor |
|  2 | Darth Vader | general  |
+----+-------------+----------+
2 rows in set (0.00 sec)

3.统计==肉眼可见的西斯==担任的职务:

肉眼可见的西斯即Darth开头

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from officers where name like 'Darth%' 
    -> union all
    -> select *from commanders where name like 'Darth%';
+----+---------------+----------+
| id | name          | title    |
+----+---------------+----------+
|  1 | Darth Vader   | executor |
|  2 | Darth Sidious | emperor  |
|  2 | Darth Vader   | general  |
|  3 | Darth Sidious | marshal  |
+----+---------------+----------+
4 rows in set (0.00 sec)

排序

1
2
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

order by语句中从前到后为关键字优先级,首先按照field1关键字的规则进行排序,然后field1关键字相同项再按照field2关键字进行排序,以此类推

ASC(ascend)升序,默认模式

DESC(descent)降序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select *from commanders order by id ASC;
+----+---------------+---------+
| id | name          | title   |
+----+---------------+---------+
|  1 | Rex           | captain |
|  2 | Darth Vader   | general |
|  3 | Darth Sidious | marshal |
+----+---------------+---------+
3 rows in set (0.00 sec)

mysql> select *from commanders order by id DESC;
+----+---------------+---------+
| id | name          | title   |
+----+---------------+---------+
|  3 | Darth Sidious | marshal |
|  2 | Darth Vader   | general |
|  1 | Rex           | captain |
+----+---------------+---------+
3 rows in set (0.00 sec)

分组

1
2
3
4
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

从某个表中按照某些规则选取某些列,并且按照某列进行同名分组

现有数据库如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from 
    -> popularity;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | Tom  | male   |
|  2 | Tom  | male   |
|  3 | Tom  | male   |
|  4 | Jack | male   |
|  5 | Jon  | famal  |
|  6 | Mike | male   |
+----+------+--------+
6 rows in set (0.00 sec)

要调查人口统计表中人重名的情况

1
2
3
4
5
6
7
8
9
10
11
mysql> select name,count(*) from popularity group by name;
+------+----------+
| name | count(*) |
+------+----------+
| Tom  |        3 |
| Jack |        1 |
| Jon  |        1 |
| Mike |        1 |
+------+----------+
4 rows in set (0.01 sec)

WITH ROLLUP

现有彩票获奖名单如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from Winners;
+----+------+-------+
| id | name | prize |
+----+------+-------+
|  1 | Mike |    20 |
|  2 | Mike |    30 |
|  3 | Mike |    10 |
|  4 | Mike |    15 |
|  5 | Jack |    15 |
|  6 | Jack |    18 |
|  7 | John |    18 |
|  8 | Jack |    19 |
|  9 | Mike |     5 |
+----+------+-------+
9 rows in set (0.00 sec)

1.查看每个人一共获奖多少次

1
2
3
4
5
6
7
8
9
mysql> select name,count(*) from Winners group by name;
+------+----------+
| name | count(*) |
+------+----------+
| Mike |        5 |
| Jack |        3 |
| John |        1 |
+------+----------+
3 rows in set (0.00 sec)

2.查看每个人一共获奖多少钱

1
2
3
4
5
6
7
8
9
10
mysql> select name,SUM(prize) as tot_prize from Winners group by name with rollup;
+------+-----------+
| name | tot_prize |
+------+-----------+
| Jack |        52 |
| John |        18 |
| Mike |        80 |
| NULL |       150 |
+------+-----------+
4 rows in set (0.00 sec)

其中SUM()为累加函数,类似的有AVG()平均数函数,COUNT()等

NULL为总计,即三个获奖者奖金总和

如果想让结果显示"prize_sum"字样而不是NULL则可以用select coalesce(...)函数

1
select coalesce(a,b,c);

如果a=NULL则选择b,如果a,b=NULL则选择c,全空则为NULL

1
2
3
4
5
6
7
8
9
10
mysql> select coalesce(name,'sum_prize'),SUM(prize) as tot_prize from Winners group by name with rollup;
+----------------------------+-----------+
| coalesce(name,'sum_prize') | tot_prize |
+----------------------------+-----------+
| Jack                       |        52 |
| John                       |        18 |
| Mike                       |        80 |
| sum_prize                  |       150 |
+----------------------------+-----------+
4 rows in set (0.00 sec)

连接

INNER JOIN

img

==等值连接==,获取两张表中匹配的记录

现有帝国政府官员和军队指挥官的数据表如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select * from officers;
+----+---------+-----------+
| id | name    | title     |
+----+---------+-----------+
|  1 | Mike    | Mayor     |
|  2 | Jack    | executor  |
|  3 | Jackson | candidate |
|  4 | John    | emperor   |
|  5 | Tom     | minister  |
+----+---------+-----------+
5 rows in set (0.00 sec)

mysql> select * from commanders;
+----+-------+---------------+
| id | name  | military_rank |
+----+-------+---------------+
|  1 | Vader | general       |
|  2 | Rex   | captain       |
|  3 | John  | marshal       |
+----+-------+---------------+
3 rows in set (0.00 sec)

现在John皇帝不希望军队和政治耦合,希望调查有没有在军政上同时身居要职的大官,

即统计其中身兼数职(比如John既是帝国皇帝又是军队元帅)的人

1
2
3
4
5
6
7
mysql> select a.name,a.title,b.military_rank from officers a inner join commanders b on a.name=b.name;
+------+---------+---------------+
| name | title   | military_rank |
+------+---------+---------------+
| John | emperor | marshal       |
+------+---------+---------------+
1 row in set (0.00 sec)

其中

1
mysql> select a.name,a.title,b.military_rank from officers a inner join commanders b on a.name=b.name;

可以翻译为:

保留a表的name和title列,保留b表的military_rank列,

a表即officers表,b表即commanders表,

两表根据name列等值连接

调查完后John皇帝很开心

LEFT JOIN

左合并会保留左侧表的全部数据,不管右侧表有无匹配数据

img 
1
2
3
4
5
6
7
8
9
10
11
12
mysql> select a.name,a.title,b.military_rank from officers a left join commanders b on a.name=b.name;
+---------+-----------+---------------+
| name    | title     | military_rank |
+---------+-----------+---------------+
| Mike    | Mayor     | NULL          |
| Jack    | executor  | NULL          |
| Jackson | candidate | NULL          |
| John    | emperor   | marshel       |
| Tom     | minister  | NULL          |
+---------+-----------+---------------+
5 rows in set (0.00 sec)

可以翻译为:

政府官员先都列在表里,然后军队指挥官如果有人也是政府官员则把其军衔也写在表里,否则写NULL

RIGHT JOIN

类比左合并,保留右侧表的全部数据,不管左侧表有无匹配数据

NULL值处理

null值与任何值的任何比较都是null

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from officers;
+----+---------+-----------+
| id | name    | title     |
+----+---------+-----------+
|  1 | Mike    | Mayor     |
|  2 | Jack    | executor  |
|  3 | Jackson | candidate |
|  4 | John    | emperor   |
|  5 | Tom     | minister  |
+----+---------+-----------+
5 rows in set (0.00 sec)

mysql> select * from officers where null=null or null >null or null<null;
Empty set (0.00 sec)

判断NULL

IS NULL,IS NOT NULL,<=>三种方法

现有数据表

1
2
3
4
5
6
7
8
9
10
mysql> select * from officers;
+----+------+---------+
| id | name | post    |
+----+------+---------+
|  1 | John | Mayor   |
|  2 | Mike | NULL    |
|  3 | Jack | NULL    |
|  4 | Tom  | emperor |
+----+------+---------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select * from officers where post is not null;
+----+------+---------+
| id | name | post    |
+----+------+---------+
|  1 | John | Mayor   |
|  4 | Tom  | emperor |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> select * from officers where post is null;
+----+------+------+
| id | name | post |
+----+------+------+
|  2 | Mike | NULL |
|  3 | Jack | NULL |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from officers where post <=> null;
+----+------+------+
| id | name | post |
+----+------+------+
|  2 | Mike | NULL |
|  3 | Jack | NULL |
+----+------+------+
2 rows in set (0.00 sec)

替换NULL

1
ifnull(a,b)

如果a为NULL则返回b的值

1
2
3
4
5
6
7
8
mysql> select * from officers where ifnull(post,'')='';
+----+------+------+
| id | name | post |
+----+------+------+
|  2 | Mike | NULL |
|  3 | Jack | NULL |
+----+------+------+
2 rows in set (0.00 sec)

一般用于int值替换成0参与计算

正则

在where子句中使用正则表达式,类似于等号和LIKE语句:

1
where 键值 REGEXP <pattern>

现有数据表如下:

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from officers;
+----+---------------+----------+
| id | name          | post     |
+----+---------------+----------+
|  1 | John          | Mayor    |
|  2 | Mike          | NULL     |
|  3 | Jack          | NULL     |
|  5 | Darth Vader   | executor |
|  6 | Darth Sidious | emperor  |
+----+---------------+----------+
5 rows in set (0.00 sec)

要查询政府官员中肉眼可见的西斯

1
2
3
4
5
6
7
8
mysql> select * from officers where name regexp '^Darth';
+----+---------------+----------+
| id | name          | post     |
+----+---------------+----------+
|  5 | Darth Vader   | executor |
|  6 | Darth Sidious | emperor  |
+----+---------------+----------+
2 rows in set (0.00 sec)

MySQL正则表达式规则与javaScript等脚本语言中的正则表达式规则相同

事务

意义

事务这种东西的存在我的理解是:

使用命令行操作数据库,很难保证输入没有拼写错误或者语法错误.

比如某个人口统计数据库里有一张基本统计表,一张学历统计表,一张收入统计表.

现在张三寿终正寝over了,需要在这三张表中都删除张三的记录.

如果没有事务,我们需要分别在三张表上各执行一次删除操作,如果在第二张表上删除时写成了张四那么张四就可能无缘无故地在某表上去世了,但是该走的张三没走.

更进一步,如果一口气要删除近一个月的死亡人口,需要输入多个名字,很难保证在三张表上准确地删除这些人名

这时引入事务,即规定一个事务开始,然后写入想要执行的命令,然后规定事务结束,此时确认事务内输入无误后再命令事务执行,可以有效减少错误.

如果肉眼没有检查出事务输入的错误,事务自动报错,并且从事务开始到错误的命令都会当作没有执行过

  • 事务的好处用科学的语言表达为:
  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个==并发==事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

控制语句

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

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

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

    写着写着发现前面已经有语法错误或者达不到目的甚至偏离目的的语句时,使用回滚,如果运气好设置了savepoint就不至于回滚到从头开始

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

    可以理解为游戏的复活点(undertale里复活点好像就叫savepoint),从该复活点之后死亡可以回到该复活点,在MySQL中从某个savepoint之后出现错误可以回到该savepoint避免错误

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

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

  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

例如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> select * from officers;
+----+---------------+----------+
| id | name          | post     |
+----+---------------+----------+
|  1 | John          | Mayor    |
|  3 | Jack          | NULL     |
|  5 | Darth Vader   | executor |
|  6 | Darth Sidious | emperor  |
+----+---------------+----------+
4 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from officers where name='Jack';
Query OK, 1 row affected (0.00 sec)

mysql> savepoint point1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from officers where name='John';
Query OK, 1 row affected (0.00 sec)

mysql> rollback to point1;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from officers;
+----+---------------+----------+
| id | name          | post     |
+----+---------------+----------+
|  1 | John          | Mayor    |
|  5 | Darth Vader   | executor |
|  6 | Darth Sidious | emperor  |
+----+---------------+----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
第12行事务开始
	第15行删除,删除名叫Jack的记录
	第18行存档,存档名叫point1
	第21行删除,删除名叫John的记录
	第24行回滚,回滚到point1,刚才从point1到第24行之间输入的东西都不算数
第27行提交事务

实际执行了只有

1
2
mysql> delete from officers where name='Jack';
Query OK, 1 row affected (0.00 sec)

ALTER修改表字段

在学到这里之前,建表都是使用create命令,规定好列的各种属性以及有多少列,此后就只能对记录进行增删改查的操作了,但是如果想增删字段或者设置字段属性,除了drop了这个表然后新建表,没有其他方法.

现在有了ALTER方法修改字段

修改表名

1
ALTER TABLE <old tablename> RENAME TO <new tablename>;
1
2
mysql> alter table officers rename to government_officers;
Query OK, 0 rows affected (0.01 sec)

查看表字段SHOW COLUMNS

数据表字段及属性由show columns方法可以查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> use empire;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show columns from officers;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| post  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

即officers数据表有三个字段,分别为id,name,post,其类型,是否可以为空,是否为主键,默认值,额外属性业已给出

新增ADD

现在希望增加一个军衔字段.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> alter table officers add military_rank varchar(10) not null default 'soldier';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from officers;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int         | NO   | PRI | NULL    | auto_increment |
| name          | varchar(20) | NO   |     | NULL    |                |
| post          | varchar(20) | YES  |     | NULL    |                |
| military_rank | varchar(10) | NO   |     | soldier |                |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

其中

1
mysql> alter table officers add military_rank varchar(10) not null default 'soldier';

可以翻译为:

改变officers这张表的字段值,新增一个military_rank字段,其类型为varchar,最长10个字符,不能为空,缺省值为'soldier';

现在希望在name后面,post前面新增一个字段salary,表示官员薪水,可以使用AFTER命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> alter table officers add salary int(10) not null default 3000 after name;
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show columns from officers;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int         | NO   | PRI | NULL    | auto_increment |
| name          | varchar(20) | NO   |     | NULL    |                |
| salary        | int         | NO   |     | 3000    |                |
| post          | varchar(20) | YES  |     | NULL    |                |
| military_rank | varchar(10) | NO   |     | soldier |                |
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

删除字段DROP

1
ALTER TABLE <tablename> DROP <key>;

现在希望删除salary字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> alter table officers drop salary;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from officers;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int         | NO   | PRI | NULL    | auto_increment |
| name          | varchar(20) | NO   |     | NULL    |                |
| post          | varchar(20) | YES  |     | NULL    |                |
| military_rank | varchar(10) | NO   |     | soldier |                |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

只修改字段属性MODIFY

1
2
3
mysql> alter table officers modify post varchar(10);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

修改字段名及属性CHANGE

1
ALTER TABLE <tablename> MODIFY <old key> <new key> <type> ...

现在希望修改post字段为position

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> alter table officers change post position varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from officers;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int         | NO   | PRI | NULL    | auto_increment |
| name          | varchar(20) | NO   |     | NULL    |                |
| position      | varchar(10) | YES  |     | NULL    |                |
| military_rank | varchar(10) | NO   |     | soldier |                |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

需要注意的是,即使只想修改字段名,不改变字段类型等属性,也需要在change语句的新字段后面重写一遍属性

change子句只写旧新字段名会报错:

1
2
mysql> alter table officers change post position;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

修改字段默认值

1
ALTER TABLE <tablename> ALTER <key> SET <属性> <新属性值>

比如希望修改官员的默认职位为'大臣'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> alter table officers alter position set default 'minister';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from officers;
+---------------+-------------+------+-----+----------+----------------+
| Field         | Type        | Null | Key | Default  | Extra          |
+---------------+-------------+------+-----+----------+----------------+
| id            | int         | NO   | PRI | NULL     | auto_increment |
| name          | varchar(20) | NO   |     | NULL     |                |
| position      | varchar(10) | YES  |     | minister |                |
| military_rank | varchar(10) | NO   |     | soldier  |                |
+---------------+-------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)

修改约束

1
2
alter table UserId
　　add constraint PK_UserId primary key (UserId)

索引

普通索引没有任何限制,唯一索引要求==建立索引的列==没有重复数据

显示索引

1
SHOW INDEX FROM <tablename>;
index

普通索引

建立普通索引的方法:

1
2
3
4
5
6
7
8
9
1.CREATE INDEX <indexname> ON <tablename>(columnname);
2.ALTER TABLE <tablename> ADD INDEX <indexname>(columnname);
3.建表时指定
mysql> create table testIndex(
    -> id int not null,
    -> name varchar(20)not null,
    -> index myindex(name)
    -> );
Query OK, 0 rows affected (0.02 sec)

删除索引

1
DROP INDEX <indexname> ON <tablename>
1
2
3
mysql> drop index myindex on testIndex;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

唯一索引

建立唯一索引的列不允许有重复数据

建立索引的方法

1
2
3
4
5
6
7
8
9
1.CREATE UNIQUE INDEX <indexname> ON <tablename>(columnname);
2.ALTER TABLEL <tablename> ADD UNIQUE <indexname>(columnname);
3.建表时指定
mysql> create table mytable(
    -> id int(10)not null,
    -> name varchar(20)not null,
    -> unique myindex(name)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8
9
10
mysql> create unique index myindex on mytable(id);
ERROR 1146 (42S02): Table 'empire.mytable' doesn't exist
mysql> create unique index myindex on testIndex(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table testIndex add unique myindex2(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意指定length长度

表克隆

"假"克隆

1
SHOW CREATE TABLE <tablename>;

该条指令的作用是显示创建当前表时的创建语句.那么使用相同的语句,稍微改动一下表名称就可以"克隆"一个新表了

但是命令行对与复制粘贴不友好,只能是对着给出的建表语句敲代码,显然这种方法不可取

"真"克隆

1
2
3
4
CREATE TABLE <target_table_name> LIKE <source_table_name>;//克隆表结构
INSERT INTO <target_table_name> SELECT * FROM <source_table_name>;//克隆表数据
CREATE TABLE <target_table_name> SELECT * FROM <source_table_name>;//一步到位
CREATE TABLE <target_table_name> SELECT * FROM <source_table_name> where 1=2;//克隆表结构

现有数据表officers:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show columns from officers;
+---------------+-------------+------+-----+----------+----------------+
| Field         | Type        | Null | Key | Default  | Extra          |
+---------------+-------------+------+-----+----------+----------------+
| id            | int         | NO   | PRI | NULL     | auto_increment |
| name          | varchar(20) | NO   |     | NULL     |                |
| position      | varchar(10) | YES  | MUL | minister |                |
| military_rank | varchar(10) | NO   | MUL | soldier  |                |
+---------------+-------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)

mysql> select * from officers;
+----+---------------+----------+---------------+
| id | name          | position | military_rank |
+----+---------------+----------+---------------+
|  5 | Darth Vader   | executor | soldier       |
|  6 | Darth Sidious | emperor  | soldier       |
+----+---------------+----------+---------------+
2 rows in set (0.00 sec)


希望克隆一张新表newOfficers,其结构和内容与officers完全相同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create table newOfficers like officers;
Query OK, 0 rows affected (0.02 sec)

mysql> show columns from newOfficers;
+---------------+-------------+------+-----+----------+----------------+
| Field         | Type        | Null | Key | Default  | Extra          |
+---------------+-------------+------+-----+----------+----------------+
| id            | int         | NO   | PRI | NULL     | auto_increment |
| name          | varchar(20) | NO   |     | NULL     |                |
| position      | varchar(10) | YES  | MUL | minister |                |
| military_rank | varchar(10) | NO   | MUL | soldier  |                |
+---------------+-------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)

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

通过show columns和select *两个命令可以观察得到,新表目前只是克隆了结构,但是内容没有克隆

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> insert into newOfficers select * from officers;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from officers;
+----+---------------+----------+---------------+
| id | name          | position | military_rank |
+----+---------------+----------+---------------+
|  5 | Darth Vader   | executor | soldier       |
|  6 | Darth Sidious | emperor  | soldier       |
+----+---------------+----------+---------------+
2 rows in set (0.00 sec)

执行了mysql> insert into newOfficers select * from officers;之后才完成了内容的复制

一步到位:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> create table newtable select * from officers;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from newtable;
+----+---------------+----------+---------------+
| id | name          | position | military_rank |
+----+---------------+----------+---------------+
|  5 | Darth Vader   | executor | soldier       |
|  6 | Darth Sidious | emperor  | soldier       |
+----+---------------+----------+---------------+
2 rows in set (0.00 sec)