MySql基础知识储备

发表于 | 分类于 | 热度 | 字数统计 5,392

最基本的增删改查SQL命令就略过了，记录下我认为有点难度又容易忘的东西
MySQL应该还是挺好用的，对于数据库的知识还是比较匮乏的

数据库引擎的知识没写，需要时再Google吧….

入门

稍微一提，啊哈哈

安装服务：mysqld -install
开启服务：net start mysql
停止服务：net stop mysql
登陆mysql：mysql -u用户名 -p密码 -P端口 -h服务器地址
例如：mysql -uroot -p<br>，然后输入密码即可
win下cls清屏
如果是对于Linux关于启动不太一样，其他倒是类似
启动：
1、使用 service 启动：service mysqld start
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start
3、使用 safe_mysqld 启动：safe_mysqld&
停止：
1、使用 service 启动：service mysqld stop
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop
3、mysqladmin shutdown
重启：
1、使用 service 启动：service mysqld restart
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld restart
默认是3306端口，可用netstat -ano 查看

显示所有数据库：
SHOW DATABASES;
显示某个数据库的细节：
show create database name;
打开/进入数据库：
USE Name;
查看当前数据库：
SELECT DATABASE();
清空数据表：
truncate table tabName;
过滤重复的数据（查询结果只显示不同的结果）：
select distinct cloName from tabName;
查看当前选择的数据库的所有表：
SHOW TABLES;
查看指定数据库中的所有表：
SHOW TABLES FROM TEST;
查看数据表结构：
SHOW COLUMNS FROM tbl_name
查看表结构的另一种：
DESC tabName;
显示创建表的语句：
SHOW CREATE TABLE table_name;
查看表是否有索引：
SHOW INDEXS FROM table_name;
以网格查看表是否有索引：
SHOW INDEXS FROM table_name\G
SQL语句中表名后面加as name，可以给表起别名
以网格查看的话就是 反斜线G 不要加分号
为了避免输入和数据库的关键字冲突，可以把值用 [` ] 包起来

下面的是一些我经常忘记的语法，是修改表相关的
增加一列：
alter table tabName add newName char(2);
修改表的列类型：
alter table tabName modify columnName varchar(20);
删除某列：
alter table tabName drop sex;
改表名：
alter table tabName to newName;
改列名：
alter table tabName change colum newName;

使用 \s 可以查看系统信息，可以用于查看当前在那个数据库
然后再说下备份数据库，其实就是生成数据的 SQL 语句
win: mysqldump -uname -p databaseName > file.sql
恢复数据库(恢复库中的数据，但是不能恢复库，如果库被删要手动创建)：
win：mysql -uroot -p databaseName < file.sql
mysql：Source file.sql; ；当然最好先 use 进入目标数据库

用户管理

创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
参数解释：

  • username：你将创建的用户名
  • host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
  • password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

授权：GRANT privileges ON databasename.tablename TO 'username'@'host'
显示授权信息可以直接执行 SHOW GRANTS;
参数解释：

  • privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
    比如：GRANT ALL ON *.* TO 'pig'@'%';
  • databasename：数据库名
  • tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

对应的，删除授权就是：REVOKE privilege ON databasename.tablename FROM 'username'@'host';
删除用户：DROP USER 'username'@'host';
然后还有一个就是查看用户，最简单的可以直接查 mysql 这个数据库中的 user 表.
引用：https://www.jianshu.com/p/d7b9c468f20d

MySQL5.7+ 后 mysql.user 表没有 password 字段改为 authentication_string；

分组和排序

前：花括号必须选择，中括号可以忽略，ASC升序，DESC降序
标准：[GROUP BY {col_name |position [ASC|DESC],...}
关于使用 GROUP 语句，在 select 指定的字段要么就要包含在 Group By 语句的后面，作为分组的依据；要么就要被包含在聚合函数中，如果 Select 后有字段，GROUP 中没有，那么就会报错。
having 语句分组，用在group by后面追加条件，判断式中的字段是必须出现在前面select中的 或者是可以包含没有出现在前面查询中的字段的一个聚合函数
举个例子：
SELECT sex, age FROM users GROUP BY 1 HAVING age > 35;
SELECT sex FROM users GROUP BY 1 HAVING count(id) >= 2;

关于排序，标准：[ORDER BY {col_name | expr | position} [ASC|DESC],...]
当然是可以按照多个字段排序的，如果第一个就已经达到了要求（不会出现相同的值）那么会忽略后面的，反正则在第一字段的前提下再对相同值按照第二个字段进行排序

可以使用LIMIT进行限制返回的数目，加在 sql 语句的最后，比如返回前两条 LIMIT 2；第2-4条：LIMIT 1,3

子查询与连接

子查询是指出现在 其他SQL语句内 的 SELECT 子句
子查询指嵌套在 查询内部，且必须始终 出现在圆括号内。
子查询可以包含多个关键字或者条件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等
子查询的外层查询可以是：SELECT,INSERT,UPDATE,SET或DO
子查询可以返回值：标量、一行、一列或者子查询

对于select中的子查询，通过上面的定义基本能猜到大部分的子查询是用在了where判断中，还有一个问题是，如果子查询返回的是多个值怎么办？所以有了下面的3个关键字，含义都在表中了

运算符\关键字 ANY SOME ALL
>、>= 最小值 最小值 最大值
<、<= 最大值 最大值 最小值
= 任意值 任意值
<>、!= 任意值

举个例子就是：SELECT * FROM test where age > ANY (select age from test2 where sex='女');
除了上表所说的，还有两个关键词：IN和NOT IN
其实也很简单，可以理解为简写：IN相当于=any；NOT IN 相当于 !=all；
可以运用到上面的那个例子上就是：SELECT * FROM test where age IN (select age from test2 where sex='女');
你也完全可以把IN换成=any；一样的

将查询结果写入数据表

看SQL语句就知道了，比如：
INSERT test（username） SELECT username FROM users WHERE age >=30;
以上就是把users表中的年龄大于30的姓名写入了test表；需要注意的是表名后面的列不能省略

多表更新

简单说就是A表参照B表的内容进行更新，比如：
update A inner join B on a_name=b_name set a_cate=b_id;

解释：
A：想要更改的表名
inner join： 内连接
B：关联的附表
a_name=b_name： 两个表对应列的关系
(要修改的列名) = (映射的列名)

INNER JOIN,内连接
​ 在MySQL中，JOIN, CROSS JOIN 和 INNER JOIN 是等价的。
LEFT [OUTER] JOIN ,左外连接
RIGHT [OUTER] JOIN,右外连接

连接

上面简单提到了3钟连接，这里进一步解释下
使用 ON 关键字来设定连接条件，也可以使用 WHERE 来代替。
但通常使用 ON 关键字来设定连接条件；使用 WHERE 关键字进行结果集记录的过滤
内连接：返回左表及右表符合连接条件的记录（即两表的交集部分）
例子：SELECT * FROM tabA JOIN tabB ON tabA.name = tabB.name;
左外连接(LEFT JOIN)：显示左表全部和左右符合连接条件的记录
右外连接(RIGHT JOIN)：显示左右符合连接条件的记录和右表全部记录
若某字段只存在某一表，则另一表的里字段返回null

当然是可以连接多个表的，直接在后面追加连接即可

还有一种比较特殊的情况，就是”自连接”，其实和上面的几种没多少区别，可以想象成有两张完全相同的表来进行连接，当然这就必须要起别名了，要不然分不清啊….

多表删除

假设一个表中有重复内容，我们利用多表删除去重复，其实是一个表，你可以看做两个表嘛，第二个表就是你用子查询查出来的重复内容的那个表了
delete t1 from test as t1 left join(select id,name from test group by name having count(name)>=2) as t2 on t1.name=t2.name where t1.id>t2.id;
将test看做t1与子查询所得到的表进行左连接，然后选出id较大的数据，进行删除
最简单的是：从数据表t1中把那些id值在数据表t2里有匹配的记录全删除掉：DELETE t1 FROM t1,t2 WHERE t1.id=t2.id
我主要是强调t1是不可忽略的…..在多表删除中
更多关于多表删除

其他

当然还有create….select等语句，可以变得更简洁

级联删除和更新

说的是当两个表用外键连接起来后，如果主表被删或者更新，从表应该如何处理
MySQL 支持外键的存储引擎只有 InnoDB
比如下面这条定义的外键约束，当主表被删或者更新，从表也跟着删除或者更新，换句话说就是：当外键指向的那个表删除或更新，保存外键的那个表的动作

1
2
FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE;
FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON UPDATE CASCADE;

常用的选项是：

  • CASCADE
    表示父表在更新或者删除时，更新或者删除子表对应记录；
  • SET NULL
    表示父表在更新或者删除的时候，子表的对应字段被 SET NULL。
  • RESTRICT 和 NO ACTION 相同
    是指在子表有关联记录的情况下父表不能更新；

自定义函数

以例子来解释：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> DELIMITER $$

mysql> CREATE FUNCTION hello(num1 VARCHAR(255),num2 VARCHAR(255))
    -> RETURNS VARCHAR(255)
    -> BEGIN
    -> select count(*) into total from test where num1 like num2;
    -> RETURN 'Hello  world,i am mysql';
    -> END $$
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;

mysql> SELECT hello('abc','a');
+-------------------------+
| hello()                 |
+-------------------------+
| Hello  world,i am mysql |
+-------------------------+
1 row in set (0.00 sec)

mysql> DROP FUNCTION hello;

DELIMITER 是用来修改分隔符的，因为当我们写的函数体有多条语句的时候要用;分割，但是它正好也是语句的结束标志，所以就会导致还没写完就执行了，所以我么先把它修改成别的，写完后再改回来
RETURNS 表明了返回值的类型
RETURN 是要返回的值
SELECT INTO 的用法就是说将查询出的内容插入到另一个表中，就上面而言，是把查询出的 count 技术保存到了 total 表中
当函数体内需要执行的是多条语句时，要使用BEGIN...END语句
执行函数使用 SELECT 语句，其实前面其实我们已经用到过了
删除函数使用DROP FUNCTION
使用SHOW CREATE FUNCTION name;可以查看函数的定义
上面我故意用了变量，这里补充下：

1.用户变量：以”@”开始，形式为”@变量名”用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者set @@global.变量名对所有客户端生效。只有具有super权限才可以设置全局变量

SELECT @nums; 这样就可以认为是定义了一个变量
也可以这样声明用户自定义变量：set @t1=1;

set语句可用于向系统变量或用户变量赋值; 也可使用select语句来定义
对于SET，可以使用=或:=来赋值，对于SELECT只能使用:=来赋值。

存储过程

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后交给存储引擎执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

存储过程通常有以下优点：

  1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
  2. 存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
  3. 存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
  4. 存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
  5. 存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

然后我们可以创建一个最简单的存储过程：
CREATE PROCEDURE sp1() SELECT VERSION();
这个存储过程就是sp1了，它不带参数，查询版本语句是它的函数体，调用我们用CALL sp_name()，关键字就是CALL啦，带参数的存储过程的调用必须有()，无参数的可以省略
下面我们看个比较一般的：

1
2
3
4
5
6
7
8
9
DELIMITER //
CREATE PROCEDURE test(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = showID;
SELECT count(ID) FROM user INTO showName;
END
//
CALL test(27, @nums);
SELECT @nums;

感觉和定义函数还是差不多的，注意到参数中有IN/OUT这些词，它们的作用也差不多可以猜出来

IN表示输入参数；表示该形参的值必须在调用存储过程时指定并传递给存储过程，在存储过程中修改该参数的值不能被返回，为原来值。
OUT表示输出参数； 该值可在存储过程内部被改变，并更新调用外面的变量。
INOUT表示既可以是输入，也可以是输出；

INTO就是把结果写入到后面跟的变量啦

事务相关

MySQL 是支持事务的，并且支持还非常好，简单说下在 MySQL 中使用事务，主要就是三条命令

  • 开启事务
    start transaction;
  • 回滚事务
    Rollback;
  • 提交事务
    Commit;

还有就是 MySQL 是支持 4 种隔离级别的，顺便说下这四种：

Serializable ：可避免上面的全部 （串行化）
Repeatable read ：可避免 脏读、不可重复读 （可重复读） [mysql 默认]
Read committed ：可避免脏读 （读已提交） [oracle 默认]
Read uncommitted : 最低级别，均无法保证 （读未提交）

  • 设置隔离级别（仅当前窗口有效）
    set transaction isolation level Read uncommitted;
  • 查询当前隔离级别
    select @@tx_isolation;

别管其他窗口（连接）的隔离级别，你设置的最低你就有所有的问题！

数据库中的事务

锁表

这里指的一般是手动操作，关于表锁行锁，可以移步 这里 进行查阅。

lock tables 命令是为当前线程锁定表。
这里有 2 种类型的锁定，一种是读锁定，用命令 lock tables tablename read；
另外一种是写锁定，用命令 lock tables tablename write；下边分别介绍

如果一个线程获得在一个表上的 read 锁，那么该线程和所有其他线程只能从表中读数据，不能进行任何写操作（等待）。
注意：表必须为 Myisam 表，如果表为 innodb 表，它是事务型的，–single-transaction 是一个更好的选项，因为它不根本需要锁定表。

如果一个线程在一个表上得到一个 WRITE 锁，那么只有拥有这个锁的线程可以从表中读取和写表，其它的线程被阻塞。

mysql 的 表锁 lock tables 感觉就像一个 封闭的空间；
mysql 发现 lock tables 命令的时候，会将带有锁标记的表带入封闭空间，直到出现 unlock tables 命令或线程结束，才关闭封闭空间。
进入封闭空间时 , 仅仅只有锁标记的表可以在里面使用，其他表无法使用。

原文参考：https://www.cnblogs.com/youxin/p/3584370.html

集群

从5.6+的版本开始吧，MySQL 官方就支持集群功能了，为的是解决读库压力过大，使用读写分离
也就是说只有一个主库负责写，其他的从库负责分担读的需求，这样的话就会有两种实现，一类是利用应用层的判断来确定是操作那个数据库（Spring 的 AOP 已经很好的支持），一类就是使用中间件，但是目前并没有太好的中间件。

主从复制

原理：
master 将数据改变记录到二进制日志(binary log)中,也即是配置文件 log-bin 指定的文件(这些记录叫做二进制日志事件，binary log events)；slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)；最后 slave 重做中继日志中的事件,将改变反映它自己的数据(数据重演)

需要注意的问题
1.、主DB server和从DB server数据库的版本一致
2、主DB server和从DB server数据库数据一致[ 这里就会可以把主的备份在从上还原，也可以直接将主的数据目录拷贝到从的相应数据目录]
3、主DB server开启二进制日志,主 DB server 和从 DB server 的 server_id 都必须唯一

下面就具体的操作，首先在主 MySQL 的 ini 配置文件中配置：

1
2
3
4
5
6
#开启主从复制，主库的配置
log-bin = mysql3306-bin
#指定主库serverid
server-id=101
#指定同步的数据库，如果不指定则同步全部数据库
binlog-do-db=mybatis

可以使用 SHOW MASTER STATUS 来查询状态，记录 Position 值，在从库里会用到
并且最好是单独创建一个同步的用户：

1
2
grant replication slave on *.* to 'slave01'@'127.0.0.1' identified by '123456';
flush privileges;

从库的 ini 没什么可配置的，如果一台机子有多台 MySQL 除了设置端口不同还要设置 server-id 不同，就是上面设置的那个
然后执行下面的 SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
CHANGE MASTER TO
 master_host='127.0.0.1',
 master_user='slave01',
 master_password='123456',
 master_port=3306,
 master_log_file='mysql3306-bin.000006',
 master_log_pos=1120;

#启动slave同步
START SLAVE;

#查看同步状态
SHOW SLAVE STATUS\G;

当看到两个 yes 就证明是配置成功了。
如果是在一台机器做测试，记得 UUID 也不能相同，在 data/data/auto.cnf 目录下

PS：这样的架构只能解决读库压力大的情况，如果是写库压力大可以尝试的方案有：

  • 使用缓存（不推荐，同步问题不好解决）
  • 多表、多库存储（查询效率会拖慢）
  • 使用队列（推荐）

补充

插入数据的时候可以一句插入多条：insert test(id) values('1'),('2');，类似这样，注意没有into了哦

所有的聚合函数都会忽略 Null 值，并且使用 count(1) 比使用 count(*) 效率高很多（HQL 就不支持）

有时会见到 SQL 语句中有 comment 之类的关键字，比如：name varchar(50) DEFAULT NULL COMMENT '资源名称', ；其中的 comment 后面跟的是注释说明

Key 是索引约束，对表中字段进行约束索引的（KEY cid ('cid')）

索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。
对需要排序的字段或者根据其进行搜索的字段创建索引是比较合适的

更多待补充

喜欢就请我吃包辣条吧！

评论框加载失败，无法访问 Disqus

你可能需要魔法上网~~