MySQL简明教程-上

本教程来自我的github nightttt7/MySQL-tutorial, 使用python脚本定时更新在此

本教程用于:
1. 入门学习
2. 快速查询
- 初稿完成于2018/3/13 by nightttt7 and lotus3333

todo

1. 完善 游标
2. 触发器
3. 函数
4. 引擎
5. 数据库设置与安全
6. 范式
7. 更多的实战

目录

windows下的MySQL安装

1. 下载MySQL:
   下载地址 -> Generally Available (GA) Releases -> Microsoft Windows -> 32-bit/64-bit(选择对应的32位/64位 windows) -> ZIP Archive -> Download
   

2. 解压MySQL:
   解压到指定文件夹,并将其中的/bin路径添加至环境变量:
   右键计算机 -> 高级系统设置 -> 环境变量 -> 系统变量 -> Path -> 在末尾添加完整的/bin路径(谨慎操作,不要删改已有值)
   

3. 安装MySQL:

   1. 打开命令提示符
   2. 路径变为完整的/bin路径
      
   3. 输入以下语句
      

      mysqld install 
      mysqld --initialize --user=mysql --console # 会给出初始密码
      net start mysql #启动服务

4. 初始化MySQL

   1. 命令提示符中输入以下语句

      mysql -uroot -p初始密码 #登录,使用刚才给出的初始密码
      set password=password('新密码'); #修改密码
      show variables like '%char%'; #查看默认编码
      set global character_set_database='utf8'; # 如果只是临时更改默认编码为utf-8
      exit #退出mysql 

   2. 更改默认编码,在mysql目录下添加my.ini文件,文件内容如下:

      [mysqld]
      character-set-server = utf8
      [client]
      default-character-set = utf8

   3. 命令提示符中输入以下语句

      net stop mysql #关闭服务
      net start mysql #启动服务

5. ==可以开始使用MySQL了==

linux下的MySQL安装

以CentOS7为例

1. 下载MYSQL源安装包,在这个页面里获取下载地址
   wget 下载地址

2. 安装源
   yum localinstall 安装包

3. 安装SQL
   yum install mysql-community-server

4. 如需安装其他SQL组件
   yum --disablerepo=\* --enablerepo='mysql*-community*' list available
yum install package-name

5. 第一次启用：

# 启用
systemctl start mysqld
# 查看状态
systemctl status mysqld
# 设置开机启动
systemctl enable mysqld
systemctl daemon-reload
# 获取默认密码
grep 'temporary password' /var/log/mysqld.log
# 登陆
mysql -urood -p初始密码 #登录,使用刚才给出的初始密码
# 修改root密码(已经进入mysql的界面)
set password for 'root'@'localhost'='password';('新密码包含大小写数字')

1. 配置默认编码为utf8,修改/etc/my.cnf配置文件，在[mysqld]下添加编码配置，如下所示：

[mysqld]
    character_set_server=utf8
    init_connect='SET NAMES utf8'

1. 文件路径

   • 配置文件：/etc/my.cnf
   • 日志文件：/var/log/mysqld.log
   • 服务启动脚本：/usr/lib/systemd/system/mysqld.service
   • datadir=/var/lib/mysql-用户名
   • socket=/var/lib/mysql-用户名/mysql.sock

2. 其他命令

# 启用
systemctl start mysqld
# 查看状态
systemctl status mysqld
#重启
systemctl restart mysqld
#　结束
systemctl stop mysqld
#　或者
service mysqld {start|stop|restart|status} 

• SQL语句的关键词不区分大小写
• 字符串需要用”包围, 数字则不用
• 查看版本信息和时间

SELECT VERSION(), CURRENT_DATE;

增删查改

创建

• 创建数据库
  CREATE DATABASE 数据库名称;
• 查看数据库
  SHOW DATABASES;

• 使用指定数据库
  USE 数据库名称;

• 创建表

CREATE TABLE 表名称 (
字段/列名 数据类型  一个或多个用空格分开的其他参数,
字段/列名 数据类型  一个或多个用空格分开的其他参数,
PRIMARY KEY (被设为主键的字段名)
);

• 创建表的其他参数
  
  1. NOT NULL 数据不能为NULL类型
  2. AUTO_INCREMENT 自动增加(整数)
     3, DEFAULT 设定默认值
• 常见数据类型
  
  1. 附加UNSIGNED 无符号,从0开始
  2. INT 整数
  3. BOOL True/False或1/0,实际是整数的一种
  4. FLOAT 浮点数
  5. DATE YYYY-MM-DD
  6. TIME HH:MM:SS
  7. YEAR YYYY
  8. DATE TIME YYYY-MM-DD HH:MM:SS
  9. TIMESTAMP YYYYMMDD HHMMSS
  10. CHAR() 定长字符串
  11. VARCHAR() 变长字符串
  12. BINARY 二进制字符
  13. TEXT 长文本数据
• 查看表
  SHOW TABLES;
• 表信息
  DESCRIBE 表名称;
• 表详细信息
  SHOW CREATE TABLE 表名称\G
• 复制出一个新表

CREATE TABLE 新表名 AS
SELECT * FROM 旧表名;

插入

• 插入新数据

INSERT INTO 表名称 ( 字段1, 字段2, 字段3) VALUES     #(括号内容)可省略
          ( 值1, 值2, 值3 ),
          ( 值1, 值2, 值3 );

• 加入新字段/列
  ALTER TABLE 表名称 ADD 字段 数据类型 AFTER/BEFORE 字段;
• 插入检索出的数据

INSERT INTO 表名称 ( 字段1, 字段2, 字段3) VALUES
SELECT语句;

更新

• 更新一行
  UPDATE 表名称 SET 字段1=值1 where 字段2 = 值2;
• 更改列名和数据类型
  ALERT TABLE 表名称 CHANGE COLUMN 字段/旧列名 字段/新列名 新数据类型
• 更改列类型(位置，数据类型等)
  ALERT TABLE 表名称 MODIFY COLUMN 字段/列名 新数据类型

简单检索

• 检索整个表
  SELECT * FROM 表名称;
• 选择行

SELECT * FROM 表名称 WHERE 字段/列名 = 值';
SELECT * FROM 表名称 WHERE 字段/列名 is not null;

• 选择指定行,注意,计数从0开始

SELECT * FROM 表名称 LIMIT 开始选择的行,需要选择的行数;

• 选择列,多个字段可以用逗号连接
  SELECT 字段/列名 FROM 表名称;
• 选择行列
  SELECT 字段/列名 FROM 表名称 WHERE 字段/列名 = 值;
• 选择不重复项,注意,如果是多个字段,会选择出组合不重复的项
  SELECT DISTINCT 字段/列名 FROM 表名称;
• 排序
  select 字段/列名 from 表名称 order by 字段/列名;
• 逆序排序,注意,如有多个字段,需要分别添加DESC
  select 字段/列名 from 表名称 order by 字段/列名 DESC;
• 计数
  SELECT 字段/列名, COUNT(*) FROM 表名称 GROUP BY 字段/列名;

删除

• 删除一行
  DELETE FROM 表名称 WHERE 字段=值;
• 删除一列
  ALTER TABLE 表名称 DROP 字段;
• 清空table
  DELETE FROM 表名称；
• 删除table
  DROP TABLE 表名称;

示例代码

mysql> create database sqltest;
Query OK, 1 row affected (0.08 sec)

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| performance_schema      |
| sqltest                 |
| sys                     |
+-------------------------+
11 rows in set (0.42 sec)


mysql> use sqltest;
Database changed

mysql> create table test (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(10) NOT NULL,
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.69 sec)

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

mysql> describe test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.08 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> delete from test;
Query OK, 2 rows affected (0.08 sec)

mysql> insert into test values
    -> (0, 'ann'),
    -> (1, 'bob');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table test add sex varchar(10) after name;
Query OK, 0 rows affected (0.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update test set name = 'bobby' where id = 2;
Query OK, 0 rows affected (0.09 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> alter table test change column sex gender varchar(2);
Query OK, 2 rows affected (0.81 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table test modify column gender varchar(3);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | bob  | NULL   |
|  3 | ann  | NULL   |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> select * from test where id = 3;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  3 | ann  | NULL   |
+----+------+--------+
1 row in set (0.01 sec)

mysql> select * from test where id is not null;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | bob  | NULL   |
|  3 | ann  | NULL   |
+----+------+--------+
2 rows in set (0.06 sec)

mysql> select name from test;
+------+
| name |
+------+
| bob  |
| ann  |
+------+
2 rows in set (0.00 sec)

mysql> select name from test where id = 3;
+------+
| name |
+------+
| ann  |
+------+
1 row in set (0.00 sec)

mysql> select distinct name from test;
+------+
| name |
+------+
| bob  |
| ann  |
+------+
2 rows in set (0.00 sec)

mysql> select name from test order by id;
+------+
| name |
+------+
| bob  |
| ann  |
+------+
2 rows in set (0.00 sec)

mysql> select name from test order by id desc;
+------+
| name |
+------+
| ann  |
| bob  |
+------+
2 rows in set (0.00 sec)

mysql> select name,count(*) from test group by id;
+------+----------+
| name | count(*) |
+------+----------+
| bob  |        1 |
| ann  |        1 |
+------+----------+
2 rows in set (0.37 sec)

mysql> delete from test where id = 2;
Query OK, 0 rows affected (0.00 sec)

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

mysql> delete from test;
Query OK, 2 rows affected (0.08 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.25 sec)

过滤

WHERE 单一条件过滤

1. where 与 select结合，数据根据where的限制条件搜索结果，位于from之后，oder by/group by之前例如：

select a,b,c from table_test where a=3;

1. where子句操作符：

   • =
   • <>(不等于)
   • !=(不等于)
   • <
   • <=
   • !<(不小于)
   • >
   • >=
   • !>(不大于)
   • between and(在两个值之间,包含这两个值)

   • is null(为空值)

   • 示例：

select a,b,c from table_test where a<3; 
select a,b,c from table_test where a<>3;
select a,b,c from table_test where a between 3 and 10;
select a,b,c from table_test where c is null; #检查空值不用=null，null的意思是无值，与0/空字符串/空格不同

WHERE 多子句过滤

• 组合where子句的操作符：
• AND
• OR
• IN
• NOT
• 示例：

select a,b,c from table_test where a=3 and b<=4 and c!=0; #返回满足以上三个条件的行
select a,b,c from table_test where a=3 or a=9; #一般来讲第一个条件满足时，数据库不再计算第二个条件
select a,b,c from table_test where a in (3,6,9);#此过滤条件相当于三个or，与or相比，in执行效率一般高些，语法更简洁
select a,b,c from table_test where not a=3; #此过滤条件还可写成 select a,b,c from table_test where a<>3; not的优势在于能与其他操作符搭配，如in/between/exists

• 注意事项：AND 与 OR 一起使用时，SQL 优先处理AND操作符，以下两个查询代码执行的结果不一样

select a,b,c from table_test where a=3 or a=6 and b<=4; #SQL先搜索符合b<=4的行，再搜索a=3或6
select a,b,c from table_test where (a=3 or a=6) and b<=4; #SQL先搜索满足圆括号内条件的行，再搜索符合b<=4的行

通配符过滤/正则表达式

• like 是 sql模式 比如 WHERE name LIKE ‘b%’
• 使用like操作符搭配通配符搜索模糊值：
• 常用通配符：
• % 可匹配多个字符
• _ 匹配一个字符
• [] 指定一个字符集，类似数学中的集合，集合中元素是或的关系
• 示例：

select a,b,c,d from table_test where d like 'sun%'; #搜索d以sun开头的行，根据数据库配置，搜索可区分大小写
select a,b,c,d from table_test where d like '%sun%'; #搜索d中间包含sun的行，根据数据库配置，搜索可区分大小写，注意sun   这个字符在sun后面有三个空格，模糊搜索时需要使用'sun%'
select a,b,c,d from table_test where d like 's%un'; #搜索d开头是s中间包含un行，根据数据库配置，搜索可区分大小写
select a,b,c,d from table_test where d like '__cat'; #搜索d开头两个字符后是cat的行
select a,b,c,d from table_test where d like '[sx]%'; #搜索d开头是s或者x的行
select a,b,c,d from table_test where d like '[^sx]%'; #搜索d开头不是s或者x的行，^表示否定，此过滤条件还可写成select a,b,c,d from table_test where not d like '[sx]%';

• 注意事项：
• %这个通配符无法匹配NULL，例如where d like ‘%’ 不会匹配d为NULL的行;
• 通配符放在开头搜索执行效率很低，能使用其他操作符能达到相同目的情况下，避免使用通配符操作
• 正则表达式
• rlike 是 正则表达式模式 比如 WHERE name REGEXP ‘^b’

数据处理,汇总,分组

创建计算字段

1. 使用别名

select a from as a1 from table_test;

1. 拼接字段，MYSQL中使用concat函数

select concat(a,b) from table_test; #该检索将得到a与b结合的的数据，字段名字为plus

1. 执行算术计算，例如：

select a,b,c*d as e from table_test;

函数处理数据

• 函数分类
• 文本处理函数
  
  • left(返回字符串左边字符)
  • length(计算字符长度)
  • lower(字符串转为小写)
  • ltrim(去掉字符串左边的空格)
  • rtrim(去掉字符串右边的空格)
  • right(返回字符串左边字符)
  • soundex(搜索发音相似的字符)
  • upper(字符串转为大写)
  • substring(提取部分字符串)
• 日期和时间处理函数
  
  • year()
  • month()
  • curdate()
• 数值处理函数
  
  • ABS()
  • COS()
  • EXP()
  • PI()
  • SIN()
  • SQRT()
  • TAN()

汇总数据

• 聚集函数及组合（适用于单列数据）
• AVG():只用于单个列，忽略列值为null的行,例如

select avg(a) as avg_a from table_test

• COUNT():对特定列具有值的行计数，忽略null值，count(*)不会忽略null值

select count(a) as aa from table_test

• MAX(),MIN():忽略列值为null的行，用于文本数据时，分别返回该列排序后的最后一行和第一行

select max(a) as A from table_test

• SUM():忽略列值为null的行,例如：

select sum(a*b) as X from table_test

• DISTINCT:不能用于count(*)，必须使用列名，不能用于计算或表达式，例如：

select avg(distinct a) as Y from table_test

• 组合多个函数，例如：

select count(*) as M,MIN(a) as aaa,MAX(a) as AA,avg(a) as avg_a from table_test

数据分组（适用于多列数据）

• 创建分组：使用group by实现，group by 可以多层嵌套实现详细分组；group by子句的每一列必须是检索列或有效的表达式（不能是聚类函数），==select中使用表达式则group by中要指定相同的表达式，不能使用别名（此处存疑）==；group by同时对null值汇总单列；位于where子句之后，order by之前；group by可根据select中字段顺序，指定group by 的列，比如group by 2，1是指先按第二个字段分组，再按第一列分组，例如

select a,count(*) from table_test group by a order by a;
select a,b,count(*) from table_test group by 2,1

• 过滤分组:使用having实现,和WHERE类似

select a,count(*) from table_test
group by a
having count(*)>10;

select子句顺序

1. select
2. from
3. where
4. group by
5. having

6. order by

   • 可用 表名.列名 指定特定表的特定列,用于区分不同表的同名列

子查询,组合查询,联结

子查询

• 把查询结果用于另一个查询中,可多重嵌套,但是不建议嵌套太多
• 可将计算字段放入子查询中

联结

• 简单联结,指定不同表的数据即可
• 使用where指定联结条件,或者inner join

FROM 表一,表二 WHERE 联结条件;
FROM 表一 INNER JOIN 表二 ON 联结条件;

• 如果没有指定联结条件,将返回笛卡尔积(行数*行数)
• 使用表别名,同一个表能多次出现(自联结)

FROM 表名 AS 表别名

• 外联结, left join/right join

FROM 表一 LEFT JOIN 表二 ON 联结条件;
FROM 表一 RIGHT JOIN 表二 ON 联结条件;

• 示例代码(where,inner join,left join,right join)

mysql> select * from a;
+------+-------+
| id   | name  |
+------+-------+
|    1 | night |
|    2 | kik   |
|    3 | tes   |
|    4 | haha  |
|    5 | just  |
+------+-------+
5 rows in set (0.09 sec)

mysql> select * from b;
+------+------+
| id   | age  |
+------+------+
|    1 |   10 |
|    3 |   17 |
|    5 |   14 |
+------+------+
3 rows in set (0.07 sec)

mysql> select a.id,a.name,b.age from a,b where a.id=b.id;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | night |   10 |
|    3 | tes   |   17 |
|    5 | just  |   14 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> select a.id,a.name,b.age from a inner join b on a.id = b.id;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | night |   10 |
|    3 | tes   |   17 |
|    5 | just  |   14 |
+------+-------+------+
3 rows in set (0.01 sec)

mysql> select a.id,a.name,b.age from a left join b on a.id = b.id;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | night |   10 |
|    3 | tes   |   17 |
|    5 | just  |   14 |
|    2 | kik   | NULL |
|    4 | haha  | NULL |
+------+-------+------+
5 rows in set (0.00 sec)

mysql> select a.id,a.name,b.age from a right join b on a.id = b.id;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | night |   10 |
|    3 | tes   |   17 |
|    5 | just  |   14 |
+------+-------+------+
3 rows in set (0.00 sec)

组合查询

• 用UNION操作符来组合多条查询
• 从性能上看使用多条WHERE子句条件或UNION没有实际的差别,但建议测试一下
• UNION中的每个查询必须包含相同的列、表达式或聚集函数
• UNION去除了重复值,UNION ALL包含重复值
• 只能使用一条ORDER BY子句,位于最后一条SELECT语句之后
• 示例代码

SELECT语句
UNION
SELECT语句

约束,外键,索引

约束 CONSTRAINT

• 管理如何插入或处理数据库数据的规则
• 检查约束

# 创建表时设置参数
# CHECK (列名与条件)
score INT CHECK ( score>0 ),

外键

• 作为外键的列只能接受指定表的键

# 创建表时设置参数
CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 表名(列名)

索引

• PRIMARY KEY (主键,值必须唯一,不允许有空值)

ALTER TABLE 表名 ADD PRIMARY KEY (列名)

• UNIQUE (唯一索引,值必须唯一,允许有空值)

ALTER TABLE 表名 ADD UNIQUE (列名)

• INDEX(普通索引)

ALTER TABLE 表名 ADD INDEX index_name (列名)

-FULLTEXT(全文索引,仅可用于 MyISAM 表,针对较大的数据)

ALTER TABLE 表名 ADD FULLTEXT (列名)

• 组合索引,最左字段in可以用到索引

ALTER TABLE 表名 ADD INDEX index_name (列名1,列名2) 

• 也可以在创建表时添加到最后 索引类型 (列名)
• 索引方法
  
  1. B-Tree 适合用来查找某一范围内的数据，而且可以直接支持数据排序
  2. Hash 仅支持”=”,”IN”和”<=>”精确查询,不支持排序
  3. R-Tree

视图,存储过程,游标

视图 VIEW

• MySQL从版本5开始支持视图查询
• 视图是包含查询出的数据的虚拟表
• 创建视图

CREATE VIEW 视图名 AS 查询语句;

• 查看视图结构

SHOW CREATE VIEW 视图名;

• 删除视图

DROP VIEW 视图名;

存储过程 PROCEDURE

• MySQL从版本5开始支持存储过程,可看作保存的多条SQL语句
• 查询当前数据库的存储过程

show procedure status where db='数据库';

• 把语句结束符号改为$$

delimiter $$

• 创建存储过程

CREATE PROCEDURE 存储过程名([IN/OUT/INOUT] 参数名 数据类型[,下一个参数])
BEGIN
SQL语句
END;

- IN(传入)/OUT(输出)/INOUT(传入并输出),默认为IN
- 定义变量
```
declare 变量名 数据类型;
```
- 赋值
```
set 变量名 = 值;
```
- IF条件语句
```
IF 条件 THEN
语句块
ELSEIF 条件 THEN
语句块
ELSE
语句块
END IF;
```
- CASE条件语句
```
CASE 值 
WHEN 比较值 THEN 语句块
WHEN 比较值 THEN 结语句块
ELSE 语句块
END CASE;
```
- 循环语句
```
WHILE 条件 DO
语句块
END WHILE;
```
- todo: 完善REPEAT和LOOP
- REPEAT 语句块 INTIL END REPEAT;
- LOOP_LABLE:LOOP 语句块 LEAVE LOOP_LABLE; END LOOP;

- 调用存储过程

CALL 存储过程名(参数)

• 删除存储过程

DROP PROCEDURE 存储过程名

• 注释

-- 这是注释
# 也是注释

• 省略参数可以用null

• 示例代码

mysql> delimiter $$

mysql> CREATE PROCEDURE A(A INT,B INT)
    -> BEGIN
    -> declare C int;
    -> SET C = 1;
    -> SELECT A+B+C;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL A(2,3);
+-------+
| A+B+C |
+-------+
|     6 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

• todo: 完善游标

游标 CURSOR

• 主要用在循环处理、存储过程、函数中使用
• 创建游标

DECLARE 游标名 CURSOR FOR SELECT语句

• 打开游标

OPEN CURSOR 游标名

• 使用游标

FETCH [NEXT | PRIOR | FIRST | LAST] FROM 游标名 INTO 变量;

• 关闭游标

CLOSE 游标名;

• 释放游标

deallocate 游标名;

自定义变量

• Using User-Defined Variables

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;