一、基本
数据库管理系统(DBMS)分为两类，一类是基于共享文件系统(Access)；另一类基于客户机-服务器(Mysql，Oracle)。客户机发起请求，服务器接收和处理请求。
整个MySQL包括服务器和及格客户程序。常用客户程序有mysql(交互式命令)、mysqldump(导入导出数据)、mysqladmin(检查服务器工作状态)。(MySQL指一个完整的MySQL RDBMS，mysql只是一个特定的客户程序。)
连接到服务器
C:>mysql -h 127.0.0.1 -p123456 -uuser00
或完整形式
C:>mysql –-host=127.0.0.1 –-password=123456 –-user=user00
mysql -h服务器名 -p密码 -u用户名
3306 是默认端口号。
进入 mysql 后，exit 或 \q 或 quit 可以断开连接。
二、用户管理
mysql 库中有一个 user 表，包含了所有用户帐号。
2.1 创建用户
mysql>CREATE USER ‘user01’@‘192.168.0.0’ [IDENTIFIED BY ‘123456’;]；
创建用户的语句要素如下：
用户名：user01
指定主机：‘192.168.0.0’。该用户只有在 ‘192.168.0.0’ 主机上才能访问数据库。不指定主机或用通配符指定为 ’%’ 时，该用户在任意主机上都能连接；通配符也能和域名或 IP 地址搭配来限定用户可连接的范围，如，‘192.168.0.%’，‘%.baidu.com’。范围还能用掩码来限定，‘192.168.122.0/255.255.255.0’ 的效果同 ‘192.168.0.%’。用户名相同但主机地址不同的用户是不同的两个用户。
设置密码：IDENTIFIED BY 后加用户连接时需要的密码。不设置时无需密码就能连接。
如果用户名和主机名内不含有须转义的符号，创建时不使用引号也可以。
创建完成后，用户 user01 只有一个内置数据库 information_schema 的权限；
使用 GRANT 或 INSERT GRANT 语句也能创建用户；直接插入行到 user 表中也能创建用户。
2.2 更改用户
2.2.1 重命名用户
RENAME USER user01 TO user02;
也能使用update直接更新user表。
2.2.2 删除用户账号
DROP USER ‘user01’@’192.168.0.0’;
2.3 权限设置
2.3.1 查看用户权限
查看某个用户权限：
mysql>SHOW GRANTS FOR user01；
查看当前用户权限：
mysql>SHOW GRANTS；
mysql>SHOW GRANTS FOR CURRENT_USER()；
2.3.2 授予权限
mysql>GRANT SELECT [(col01)] ON db01.table01 TO user01 [IDENTIFIED BY ‘123456’];
GRANT 语句能添加 (或更新) 用户对某个数据库或表的权限。语句要素有三个。
用户：user01。
级别：*.* 全局级，表示所有库下的所有表；db01.* 数据库级，表示库 db01 下的所有表；db01.table01 数据表级，表示库 db01 下的表 table01 的所有列；table01 表示当前数据库下表 table01 的所有列 。
权限：SELECT。能把多个权限用逗号隔开一次性添加。权限后可以跟上小括号包着的数据列，表示该权限可作用的数据列。
密码：如果账户已经存在并且有密码，就不需要写这一部分，写上这一部分会更改密码。若用户不存在，会创建新用户，写上这一部分更加安全。
其它能添加的用户权限如下表：
| 管理权限 | |||
|---|---|---|---|
| SHOW DATABASES | 使用SHOW DATABASES | PROCESS | 使用SHOW FULL PROCESSLIST |
| CREATE USER | 使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES | SUPER | 使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录 |
| FILE | 使用SELECT INTO OUTFILE和LOAD DATA INFILE | SHUTDOWN | 使用mysqladmin shutdown(用来关闭My SQL服务器) |
| RELOAD | 使用FLUSH | GRANT OPTION | 使用GRANT和REVOKE |
| REPLICATION CLIENT | 访问服务器位置 | REPLICATION SLAVE | 由复制从属使用 |
| ALL | 除GRANT OPTION外的所有权限 | USAGE | 无访问权限 |
| 数据对象权限 | |||
| SELECT | 使用SELECT | DELETE | 使用DELETE |
| ALTER | 使用ALTER TABLE | UPDATE | 使用UPDATE |
| CREATE | 使用CREATE TABLE | INSERT | 使用INSERT |
| DROP | 使用DROP TABLE | LOCK TABLES | 使用LOCK TABLES |
| ALTER ROUTINE | 使用ALTER PROCEDURE和DROP PROCEDURE | CREATE ROUTINE | 使用CREATE PROCEDURE |
| CREATE TEMPORARY TABLES | 使用CREATE TEMPORARY TABLE | SHOW VIEW | 使用SHOW CREATE VIEW |
| CREATE VIEW | 使用CREATE VIEW | EXECUTE | 使用CALL和存储过程 |
| INDEX | 使用CREATE INDEX和DROP INDEX | ||
| 除了 ALL 外的管理权限只有在全局级别下才能添加。ALL 能授予全局级、数据库级、数据表级、例程级权限。数据列级权限必须在每个权限后加括号包着的数据列。USAGE 常用于与权限无关的用户特性的控制。 |
2.3.3 撤销权限
和 GRANT 相反的操作，语句的要素和 GRANT 一样。
REVOKE SELECT ON db01.table01 FROM user01;
2.3.4 改密码
SET PASSWORD FOR user01 = Password('123456');
# mysql8的改密语法略有不同
SET PASSWORD FOR user01 = '123456'
不写 FOR user01 会更改当前用户的密码；修改其他用户的密码需要有 mysql 库的 UPSQTE 权限；Password() 函数会对密码进行加密。
三、索引
是帮助MySQL高效获取数据的数据结构。
3.1 索引分类
- 主键索引（primary key）
不能为空，不能重复。
一般和自增（auto_increment）连用。自增属性在一个表里只能有一个，必须属于类型为整型且带有索引的列。自增属性能自动生成值，插入数据时可以用null为该列占位。含有自增属性的列在删除索引时必须先删掉自增属性。 - 唯一索引（unique）
能为空，不能重复
为某个列添加唯一索引，该列不能存在重复值。 - 普通索引
没有固定的限制。 - 全文索引
- 组合索引
3.2 索引操作
- 查看索引
show index from t1;
- 添加索引
create table t1(c1 int, index 索引名(c1)); # 创建表时添加
alter table t1 add index 索引名(c1); # alter 添加
create index 索引名 on t1(c1); # create 添加
- 删除索引
drop index 索引名 on t1;
alter table t1 drop index 索引名;
3.3 索引的优缺点
- 优点
加快查询速度；加快分组和排序速度。 - 缺点
索引表会占用存储空间；
索引表建立和维护需要成本，建立时需要对表加锁；
降低数据更新的效率。
3.4 索引的原理
好多查询算法能优化查询效率，但是每种算法只能应用在特定的数据结构上，而数据本身的结构不可能完全符合这个要求。因此数据库系统中还存有满足特定算法的数据结构，这些数据结构通过某种方式指向数据，这时就能使用查询算法了。这种数据结构就是索引。
一般来说，索引本身也很大，不可能全部存储在内存中，而是以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。即索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
3.4.1 B-tree 和 B+tree
- 树
节点的度：一个节点含有的子树的个数称为该节点的度；
树的度：一棵树中，最大的节点的度称为树的度 d；
叶节点：度为0的节点称为叶节点；
分支节点：度不为0的节点； - B-tree
键值非降序排列；
每个节点都含有键、数据和指针；叶节点和内节点所需的内存大小一样；键和指针相邻；叶节点的指针都为null；
每个叶节点最多含有3个键；所有叶节点位于同一层； - B+tree 的不同
每个内节点含键和指针，每个叶节点含键和数据，内节点和叶节点一般大小不一样。
B+tree 的好处：
- 有较少的I/O。B-tree的一个节点有索引还有数据，磁盘的每一页能存的索引较少；B+tree的内节点只包含键，磁盘的每一页能有较多的索引。
- 随机I/O的次数更少。随机I/O是指连续的读写操作，访问的地址不连续，用时长。顺序I/O是指读写操作基于逻辑块逐个访问来自相邻地址的数据，用时极短。
- 查询速度更稳定。B+Tree内节点不存储数据，所有的数据都要到叶节点上找，而叶节点的高度都相同，因此所有数据的查询速度都是一样的。
3.4.2 聚簇索引和非聚簇索引
在索引的分类中，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。
- 非聚簇索引——MyISAM
非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值。非聚簇索引的主索引和辅助索引的叶节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系。 - 聚簇索引——InnoDB
聚簇索引的主索引的叶结点存储的是键值对应的数据本身，辅助索引的叶结点存储的是键值对应的数据的主键键值。主键的值长度越小、类型越简单就越好。索引的顺序和数据本身的顺序就是相同的；
3.5 使用索引的注意事项
不能使用函数或进行计算；
不用 ！=，<>，not in 和 or；
多个单列索引效果不如复合索引；
条件中使用了复合索引的第一个列时，该索引才会被使用；
条件中某个列使用范围查询，其后的列就不能用索引了；
复合索引中某一列含有 null 值，这一列的索引就无效了；
数据库中一般不含 null 值；
条件左右两侧类型不一致，通过隐式转换能查到结果，但这样可能让索引失效；
like 后格式为 ‘%XXX%’ 时，索引失效。
四、表连接
关系表、关系数据库出现的基础：避免重复数据过多。
关系表的效果：把信息分解为多个表，这些表通过某些关键信息关联起来。
优点：省内存；便于修改；不存在同一数据格式不一致的问题。
不连接返回笛卡尔乘积
select 表1的列,表2的列 from 表1,表2;
联结返回满足条件两表中的内容
select 表1的列,表2的列 from 表1,表2 where 表1.共同列=表2.共同列;
使用表别名(不同于列别名)能缩短sql语句；能在一个sql句子内多次使用相同的表。
4.1 普通连接
连接多个表
select 表1.列1,表2.列2,表3.列3 from 表1,表2,表3 where 表1.12共同列=表2.12共同列 and 表2.23共同列=表3.23共同列;
必须有足够的的关系避免笛卡尔乘积，但关系太多会影响性能。
select c1,c2 from 表1 where c3 in ( select c3 from t2 where c4 in ( select c4 from t3 where c5=v));
select的连续嵌套。最终显示 表1 的 c1 和 c2。若想显示 表2 或 表3 的内容或用 表2 或 表3 的内容排序、分组 ，就不能用这种形式了。
自连接
select 列1,列2 from 表 where 列3=(select 列3 from 表 where 列1=值1);
select t1.列1,t1.列2 from 表 as t1,表 as t2 where t1.列3=t2.列3 and t2.列1=值1;
这两句效果一样
4.2 用join连接
- 内连接/等值连接（inner join）
select 表1的列,表2的列 from 表1 inner join 表2 on 表1.共同列=表2.共同列;
返回符合条件的值。若表1=表2，效果同自连接。内连接是使用 join 时默认的连接方式。
- 左连接 （left join）
select 表1的列,表2的列 from 表1 left join 表2 on 表1.共同列=表2.共同列;
返回表1的列的所有项和对应的表2的列的符合条件的值，无对应值显示null。
- 右连接 （right join）
select 表1的列,表2的列 from 表1 right join 表2 on 表1.共同列=表2.共同列;
返回表2的列的所有项和对应的表2的列的符合条件的值，无对应值显示null。
用 join 的 sql 语句都能改写成同义的 where 语句。left join 和 right join 的 sql 语句有偏向显示的功能，不一定能换成where。
- （outter join）
包括 left outter join 和 right outter join（在 MySQL 即 left join 和 right join。） - （cross join）
？？？只做了简单的测试，结果和 join 一样。
4.3 组合查询 union
select 列1,列2,列3 from 表1
union [all]
select 列4,列5,列6 from 表2
group by 列1 order by 列1;
分组、排序要写在最后，且只能依据表1的列名。
union 默认去除检索到的重复行。union all 能保留重复。
这两个select语句查询的项目数必须相等，类型不兼容也行，会按查询顺序显示。
返回的是以列1,列2,列3的列名为列名的表格。
五、事务
对数据访问与更新的一系列操作组成的程序执行单元。
主要用于处理操作量大，复杂度高的数据；
MySQL中只有Innodb引擎才支持事务；
默认情况下，每执行一句sql就提交一次事务。
5.1 事务的特性(ACID)
原子性(Atomicity)：事务的所有步骤，要么全执行，要么全部不执行。执行中出错会回滚到执行之前的状态。
一致性(Consistency)：事务执行前后数据库都是完整和一致的。
隔离性(Isolation)：并发环境中，各个事务互相隔离。
持久性(Durability)：事务提交后，对数据的更改是永久性的。
5.2 事务并发会出现的问题
脏读：读取到另一事务未提交的数据。
不可重读：多次读取同一数据时，该数据被另一事务更新，导致返回不同的结果。
幻读：查询中数据量发生变化。
5.3 事务隔离级别
读未提交（READ_UNCOMMITTED）：允许脏读取，其隔离级别最低。
读已提交（READ_COMMITTED）：执行的时候只能获取到已经提交的数据。不会脏读。
可重复读（REPEATABLE_READ）：在事务处理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。可能出现幻读。
顺序读（SERIALIZABLE）：要求所有的事务排队顺序执行，即事务只能一个接一个地处理，不能并发。
查看当前事务的隔离级别
select @@tx_isolation;
设置当前会话的隔离级别
set session transaction isolation level read uncommitted;
六、备份和导入
6.1 备份
C:\>mysqldump -uroot -p123123 旧库名 > 目标路径
C:\>mysqldump -uroot -p123123 旧库名 表名 > 目标路径
命令行输入，于目标路径生成备份好的 sql 文件。第一句备份整个库，第二句只备份库中的表，多个表之间要用空格隔开。
6.2 导入
命令行输入：
C:>mysqldump -uroot -p123123 新库名 < 目标路径
进入mysql，使用该库，查看表，发现并没有表被导入。输入如下语句：
mysql>source 目标路径;
再查看表，导入成功。备份整个库会导入该库中所有表，备份表时，只会导入已备份的表。
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
