MySQL简单教程

SQL基础

关系型数据库管理系统（Robms）：每一行表示一个数据记录，每一列表示一个字段
非关系型数据库管理系统（Non-Robms）

• DDL 数据定义语言（create,drop,alter,truncate）
• DML 数据操作语言（insert,update,delete,call）
• DQL 数据查询语言（select,where）
• DCL 数据控制语言（grant,revoke）

创建数据库

# 查看数据库
SHOW databases;

# 创建数据库
CREATE database 'name';

# 删除数据库
DROP database 'name';

创建表

# 选中数据库
USE game;

# 建表
CREATE TABLE player (
  id INT,
  name VARCHAR(100),
  level INT,
  exp INT,
  gold DECIMAL(10,2)
);

# 查看表
DESC player;

# 修改表结构
ALTER TABLE player MODIFY COLUMN name VARCHAR(200); # 修改数据类型
ALTER TABLE player RENAME COLUMN name to nick_name; # 修改字段名
ALTER TABLE player ADD COLUMN last_login DATETIME; # 添加字段
ALTER TABLE player DROP COLUMN last_login; # 删除字段

# 删除表
DROP TABLE player;

MySQL数据类型

• 数值类型
  • INT 标准整数
  • TINYINT 非常小的整数
  • BIGINT 极大的整数
  • DECIMAL 定点数（精确小数）：例DECIMAL(10,2)表示长度为10，保留2位小数的十进制数值
  • FLOAT/DOUBLE 浮点数
• 字符串类型
  • VARCHAR 可变长度字符串
  • CHAR 固定长度字符串
  • TEXT 长文本
  • LONGTEXT 极长文本
• 日期和时间类型
  • DATETIME 日期+时间
  • DATE 仅日期
  • TIME 仅时间
  • TIMESTAMP 时间戳
• 二进制/其他
  • BLOB 二进制大对象
  • JSON JSON数据

数据的增删改查

# 插入数据（一齐修改所有数据且顺序一致可省略前括号）
INSERT INTO player (id, name, level, exp, gold) VALUES (1, '张三', 1, 1, 1);
#插入多条
INSERT INTO player (id, name) VALUES (2,'李四'),(3,'王五');

# 查看数据
SELECT */'column' FROM player;

# 添加默认数据
ALTER TABLE player MODIFY LEVEL INT DEFAULT 1;

# 修改数据
UPDATE player SET level = 1 where name = '李四';
UPDATE player SET level = 1 where name = '王五';
# 修改所有
UPDATE player SET exp = 0, gold = 0;

# 删除数据
DELETE FROM player where gold = 0;

数据的导入和导出

命令行中：

# 导出
mysqldump -u root -p game (player) > game.sql

# 导入
mysql -u root -p game < game.sql

常用语句

# WHERE子句
# 搭配SELECT,UPDATE,DELETE等
SELECT * FROM player WHERE level > 1 AND (level < 5 OR exp > 1) AND exp < 5;

# null的特判
SELECT * FROM player WHERE email is null;
SELECT * FROM player WHERE email is NOT null;
SELECT * FROM player WHERE email = ''; # 空字符串，并非空值NULL

# IN
SELECT * FROM player WHERE level IN (1,3,5);

# BETWEEN
SELECT * FROM player WHERE level BETWEEN 1 AND 10;

# NOT
SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;

# LIKE
# %任意个字符，_任意一个字符
SELECT * FROM player WHERE name LIKE '%王%'
SELECT * FROM player WHERE name LIKE '王_'

# REGEXP
# 正则表达式：· 任意一个字符 ^ 开头 $ 结尾 [abc] 其中任意一个字符 [a-z] 范围内任意一个字符 A|B A或B
SELECT * FROM player WHERE name REGEXP '^[王张].$' # 王或张姓二字
SELECT * FROM player WHERE email REGEXP '^zhangsan';
SELECT * FROM player WHERE email REGEXP '^[abc]';
SELECT * FROM player WHERE email REGEXP 'net$';

# ORDER BY
SELECT * FROM player ORDER BY level; # 默认升序
SELECT * FROM player ORDER BY level DESC; # 降序 
SELECT * FROM player ORDER BY level DESC, exp ASC; # 先level降序，然后exp升序

# 聚合函数
# AVG() COUNT() MAX() MIN() SUM()...
SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;

# GROUP BY
SELECT * FROM player GROUP BY sex;
SELECT level, COUNT(*) FROM player GROUP BY level; # 查看每个等级玩家数量

# HAVING 筛选分组后的数据
SELECT level, count(level) from player group by level having count(level) > 4 order by count(level) DESC; # 查看大于4每个等级玩家数量并按人数降序排序

# LIMIT
# 复杂些的例子：
SELECT substr(name,1,1),count(substr(name,1,1)) from player 
GROUP BY substr(name,1,1) 
HAVING count(substr(name,1,1)) >= 5 
ORDER BY count(substr(name,1,1)) DESC
LIMIT 4,3
# 第一个3表示偏移量，第二个3表示返回数量，此处返回5-7名

# DISTINCT 去重复
SELECT DISTINCT sex from player;

# UNION 两个查询结果并集（会去重） UNION ALL 不去重
SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION (ALL)
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

# INTERSECT 两个查询结果交集
SELECT * FROM player WHERE level BETWEEN 1 AND 3
INTERSECT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

# EXCEPT 两个查询结果差集
SELECT * FROM player WHERE level BETWEEN 1 AND 3
EXCEPT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

子查询

SELECT * from player where level > (SELECT avg(level) from player)

SELECT level, ROUND((SELECT avg(level) from player)) as average,
 level - ROUND((SELECT avg(level) from player)) as diff
 from player; # as取别名

# 创建新表（从子表）
CREATE table player_above_5 select * from player where level < 5;

# 在新表中插入
INSERT INTO player_above_5 select * from player where level between 6 and 10;

# EXISTS
# 返回 0 或 1
SELECT exists( select * from player where level > 10);

表关联

本质：笛卡尔积 + 条件过滤

#用 JOIN ON 关联

# 内连接 INNER JOIN
select * from player
inner join equip
on player.id = equip.player_id;
# 只显示匹配的字段

# 左连接 LEFT JOIN
select * from player
left join equip
on player.id = equip.player_id;
# 某玩家无装备则显示NULL

# 右连接 RIGHT JOIN
select * from player
right join equip
on player.id = equip.player_id;
# 某装备无主人则显示NULL

# 用 WHERE 关联

select * from player, equip
where player.id = equip.player_id;

# 加别名
select * from player p, equip e
where p.id = e.player_id;

索引

# CREATE INDEX
# 唯一索引/全文索引/空间索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    ON tbl_name (index_col_name,...)

CREATE INDEX email_index on fast_table(email);

# 查看索引
SHOW INDEX from fast_table;

# 删除索引
DROP INDEX email_index on fast_table;

# 用 alter 创建索引
alter table fast_table ADD INDEX email_index(email);

视图

视图中的数据是动态的，会随着表中数据的变化而变化

# 创建视图
CREATE VIEW top10
AS
select * from player order by level DESC limit 10;

# 查看视图
select * from top10;
update player set level = 10 where id = 64;

# 修改视图
ALTER VIEW top10
AS
select * from player order by level ASC limit 10;

# 删除视图
DROP VIEW top10;