MySQL数据库

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.14 sec)

2、设置编码命令

SET character_set_client = utf8;

SET character_set_connection = utf8;

SET character_set_database = utf8;

SET character_set_results = utf8;

SET character_set_server = utf8;

此种方式只在当前状态下有效，当重启数据库服务后失效。

用户操作

1、查看数据库中已有用户的用户名

mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| mysql.session |
| mysql.sys |
| root |
+---------------+
3 rows in set (0.00 sec)

2、添加 MySQL 用户，只需要在 mysql 数据库中的 user 表添加新用户即可。

（1）创建用户

mysql> create user '用户名'@'host' identified by '密码';
Query OK, 0 rows affected (0.11 sec)

注：host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;

密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

（2）授权

mysql> grant 用户的操作权限 on 数据库名.表名 to '用户名'@'host';
Query OK, 0 rows affected (0.18 sec)

注：如果要授予所有的权限则使用ALL 。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*.

以下为添加用户的的实例，用户名为guest，密码为guest123，并授权用户可对数据库student的表stu进行 SELECT, INSERT 和 UPDATE操作权限。

mysql> create user 'guest'@'host' identified by 'guest123';
Query OK, 0 rows affected (0.11 sec)

mysql> grant select,insert,update on student.stu to 'guest'@'host';
Query OK, 0 rows affected (0.18 sec)

注意：在注意需要执行 FLUSH PRIVILEGES 语句。这个命令执行后会重新载入授权表。

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。

你可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 'Y' 即可，用户权限列表如下：

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv

3、创建用户同时授权

以下命令会给指定数据库TUTORIALS添加用户 zara ，密码为 zara123 ，此用户的操作权限有SELECT,INSERT,UPDATE,DELETE,CREATE,DROP。

mysql> use mysql;
Database changed

mysql> grant select,insert,update,delete,create,drop
-> on tutorials.*
-> to 'zara'@'localhost'
-> IDENTIFIED BY 'zara123';

以上命令会在mysql数据库中的user表创建一条用户信息记录。

4、设置与更改用户密码

mysql> set password for '用户名'@'host'=password('密码');
Query OK, 0 rows affected, 1 warning (0.07 sec)

5、撤销用户权限

mysql> revoke 用户的操作权限 on 数据库名.表名 from '用户名'@'host';
Query OK, 0 rows affected (0.00 sec)

PS: 假如你在给用户'dog'@'localhost ''授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'dog'@'localhost ', 则在使用REVOKE SELECT ON *.* FROM 'dog'@'localhost ';命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON *.* TO 'dog'@'localhost ';则REVOKE SELECT ON test.user FROM 'dog'@'localhost ';命令也不能撤销该用户对test数据库中user表的Select 权限.

具体信息可以用命令SHOW GRANTS FOR 'dog'@'localhost '; 查看.

6、删除用户

mysql> drop user '用户名'@'host' ;
Query OK, 0 rows affected (0.00 sec)

7、查看用户的授权

mysql> show grants for '用户名'@'host';
+-------------------------------------------------------------------+
| Grants for guest@host |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'guest'@'host' |
| GRANT SELECT, INSERT, UPDATE ON `student`.`stu` TO 'guest'@'host' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

PS:GRANT USAGE:mysql usage权限就是

空权限，默认create user的权限，只能连库，啥也不能干

数据库操作

1、创建数据库

mysql> create database 数据库名 charset=utf8; Query OK, 1 row affected (0.01 sec)

2、选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

mysql> use 数据库名;
Database changed

注意:所有的数据库名，表名，表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。

3、查看所有数据库

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| demo |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.10 sec)

4、查看当前使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| demo |
+------------+
1 row in set (0.00 sec)

5、删除数据库

mysql> drop database 数据库名;
Query OK, 0 rows affected (0.10 sec)

模式操作

1、定义模式

mysql> create schema 模式名（authorization 用户名表定义子句|视图定义子句|授权定义子句）;
Query OK, 1 row affected (0.02 sec)

2、删除模式

mysql> drop schema 模式名 cascade|restrict;
Query OK, 0 rows affected (0.14 sec)

选择了cascade（级联），表示在删除模式的同时把该模式中所有的数据库对象全部一起删除。

选择了restrict（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行drop schema语句。

表存储引擎

1、看你的mysql现在已提供什么存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.08 sec)

2、看你的mysql当前默认的存储引擎

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.19 sec)

3、看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎)

mysql> show create table 表名;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL,
`class` char(10) NOT NULL,
`name` char(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表的操作

1、显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)

2、创建数据表

mysql> create table 表名(
-> 列名数据类型可选约束条件,
-> 列名数据类型可选约束条件,
-> 列名数据类型可选约束条件,
-> primary key(列名)，

-> foreign key(列名))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.33 sec)

注意：

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。id 字段设置了 auto_increment 属性后，必须设置为主键 PRIMARY KEY (`id`)。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
FOREIGN KEY关键字用于定义列为外键。
ENGINE 设置存储引擎，CHARSET 设置编码。
添加主（外）键方式如下：

mysql> alter table 表名 add primary|foreign key(列名，列名);
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除主（外）键方式如下：

mysql> alter table 表名 drop primary|foreign key;
Query OK, 5 rows affected (0.14 sec)
Records: 5 Duplicates: 0 Warnings: 0

创建数据表时用comment来添加字段或列的注释，为列设置默认值：

（注意：注释需要用单引号括起来）

mysql> create table 表名
-> (id int(11) not null auto_increment comment '用户主键',
-> pwd varchar(50) not null default 123456 comment '用户密码',
-> avater varchar(100) default null comment '用户头像图片地址',
-> primary key(id))engine=InnoDB default charset=utf8 comment='用户信息表';
Query OK, 0 rows affected (0.39 sec)

3、表定义的修改

（1）修改某一列的数据类型

mysql> alter table 表名 modify 列名数据类型可选约束条件;
Query OK, 3 rows affected (1.19 sec)
Records: 3 Duplicates: 0 Warnings: 0

（2）将某一列移动到指定列的后面

mysql> alter table 表名 modify 某一列数据类型 after 指定列;
Query OK, 0 rows affected (1.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

（3）修改某一列列名

mysql> alter table 表名 change 旧列名新列名数据类型可选约束条件;
Query OK, 0 rows affected (0.72 sec)
Records: 0 Duplicates: 0 Warnings: 0

（4）删除某一列

mysql> alter table 表名 drop 列名;
Query OK, 0 rows affected (0.63 sec)
Records: 0 Duplicates: 0 Warnings: 0

（5）添加某一列

mysql> alter table 表名 add 列名数据类型可选约束条件;
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0

（6）修改表名

mysql> alter table 旧表名 rename 新表名;
Query OK, 0 rows affected (0.14 sec)

（7）修改表的存储引擎

mysql> alter table 表名 engine=存储引擎类型;
Query OK, 3 rows affected (0.45 sec)
Records: 3 Duplicates: 0 Warnings: 0

（8）增加某一列作为唯一索引

（表示此列是唯一的，不能重复）

mysql> alter table 数据库名.表名
-> add unique index 事件名 (列名)
-> using btree comment '注释';
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0

4、查看表结构

方法一：

mysql> desc 表名;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(8) | NO | | NULL | |
| sclass | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

方法二：

mysql> show columns from 表名;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.09 sec)

5、查看某个数据表的所有记录。

mysql> select * from 数据表名;
+----------+----------+
| username | password |
+----------+----------+
| lenchu | lenchu |
+----------+----------+
1 row in set (0.01 sec)

6、删除数据表

mysql> drop table 表名;
Query OK, 0 rows affected (0.22 sec)

7、插入数据

mysql> insert into 表名(列名1,列名2,列名3)
-> values
-> (列值1,列值2,列值3),
-> (列值1,列值2,列值3);
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0

注意：（1）如果数据是字符型，必须使用单引号或者双引号，如："value"。

（2）添加数据的时候可以规定列进行添加。如果所有的列都要添加数据可以不规定列进行添加数据。

8、修改数据

mysql> update 表名
-> set 列名1=列值1,列名2=列值2
-> where 列名=列值;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

其功能是修改指定表中满足where子句条件的元组。其中set子句给出列值1，列值2用于取代相应的属性列值。如果省略where子句，则表示要修改表中的所有元组。

9、删除数据

mysql> delete from 表名
-> where 条件语句;
Query OK, 2 rows affected (0.12 sec)

delete语句的功能是从指定表中删除满足where子句条件的所有元组。如果省略where子句，表示删除表中全部元组，但表的定义仍在字典中。

10、新建一个和已有表结构一样的表，新建的表的内容为空

mysql> create table 新表名 like 已有表表名;
Query OK, 0 rows affected (0.11 sec)

11、将旧表中的数据插入到新表中，新旧表的结构必须一样

mysql> insert into 新表名
-> select * from 旧表名;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

索引操作

1、建立索引

mysql> create unique|cluster index 索引名
-> on 表名(列名1 asc|desc,列名2 asc|desc,列名3 asc|desc);
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0

每个列名后面可以用次序指定索引值的排列次序，可选asc（升序）或desc（降序），缺省值为asc。

unique表明此索引的每一个索引值只对应唯一的数据记录。

cluster表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新该索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。

2、删除索引

mysql> alter table 表名 drop index 索引名;
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0

查询数据

select [all|distinct] <目标列表达式> [,<目标列表达式>]...
from <表名或视图名> [,<表名或视图名>...]|(<select语句>)[as]<别名>
[where<条件表达式>]
[group by<列名1>[having<条件表达式>]]
[order by<列名2>[asc|desc]];

整个select语句的含义是：根据where子句的条件表达式，从from子句指定的基本表或视图中找出满足条件的元组，再按select子句中的目标列表达式，选出元组中的属性值形成结果表。

如果有group by子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果group by子句带having短语，则只有满足指定条件的组才予以输出。如果有order by子句，则结果表还要按<列名2>的值的升序或降序排序。

1、单表查询

（1）选择表中的若干列

查询指定列

select 列名1，列名2

from 表名;

查询全部列

select *|全部列名

from 表名;

查询经过计算的值

select 列名1,'列名4',列名2的计算表达式，lower(列名3)

from 表名;

注意：（1）加双引号或单引号的列名，在查询结果中此列的列值将显示为列名

（2）lower的作用是使此列的列值用小写字母显示

给列名起别名，使得能够见名知意

mysql> select 'sna' as '姓名','scl' as '班级';
+------+------+
| 姓名 | 班级 |
+------+------+
| sna | scl |
+------+------+
1 row in set (0.00 sec)

通过给列名指定别名来改变查询结果的列标题

mysql> select id 学号
-> from stu;
+------+
| 学号 |
+------+
| 111 |
| 121 |
| 123 |
| 124 |
| 125 |
+------+
5 rows in set (0.00 sec)

（2）选择表中的若干元组

取消取值重复的行

select distinct 列名1，列名2，列名3

from 表名;

查询满足条件的元组

select 列名

from 表名

where 查询条件;

（3）order by子句

用户可以用order by子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，缺省值为升序。

（4）聚集函数

SQL提供的聚集函数主要有：

如果指定distinct短语，则表示在计算时要取消指定列中的重复值。如果不指定distinct短语或指定all短语（all为缺省值），则表示不取消重复值。

（5）group by子句

group by子句将查询结果按某一列或多列的值分组，值相等的为一组。

对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

2、连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询。

（1）等值与非等值连接查询

（2）自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。

（3）外连接

（4）复合条件连接

上面各个连接查询中，where子句中只有一个条件，即连接谓词。where子句中可以有多个连接条件，称为复合条件连接。

3、嵌套查询

（1）带有in谓词的子查询

（2）带有比较运算符的子查询

（3）带有any(some)或all谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用any（有的系统用some）或all谓词修饰符。而使用any或all谓词时则必须同时使用比较运算符。其语义为：

（4）带有exists谓词的子查询

exists代表存在量词∃。带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。可以利用exists来判断非空等是否成立。

4、集合查询

select语句的查询结果是元组的集合，所以多个select语句的结果可进行集合操作。集合操作主要包括并操作union、交操作intersect和差操作except。注意，参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

5、分页查询

MySQL实现分页效果比较简单，只有一个limit关键字就可以解决。
示例：SELECT username,password FROM tb_user WHERE id = 1 LIMIT 100,10;
具体：select * from tableName where 条件 limit 当前页码*页面容量-1,页面容量

表的连接

1、内连接：只返回满足条件的数据

2、外连接

3、完全连接：返回左表和右表所有数据

mysql不支持全连接(full join)，可以使用 left join+ union+right join(SELECT * from a left JOIN b on a.name=b.id) UNION (SELECT * from a RIGHT JOIN b on a.name=b.id );

4、笛卡尔积：又称交叉连接，虽然没有实际意义，但某些场景可能应用到

总结：

两表直接笛卡尔积的结果数量是两表的数据量相乘
带where条件id相等的笛卡尔积和inner join结果相同，但是inner join效率快一点
left join：TEST_A表的ID为空时拼接TEST_B表的内容为空，right join则相反
full join：等于left join和right join的并

视图操作

1、建立视图

2、删除视图

mysql> drop view 视图名 cascade;
Query OK, 0 rows affected (0.00 sec)

视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用cascade级联删除语句，把该视图和由它导出的所有视图一起删除。

基本表删除后，由该基本表导出的所有视图（定义）没有被删除，但均已无法使用了。删除这些视图（定义）需要显式地使用drop view语句。

3、查询视图

视图定义后，用户就可以像对基本表一样对视图进行查询了。

4、更新视图

事务

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

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

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

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

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

1、事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。