MySQL学习

创建数据库

注意每个语句后面都要加;

SHOW DATABASES; #显示数据库 
CREATE DATABASE game; #创建数据库
DROP DATABASE game; #创建数据库

数据类型：数值类型（整数类型：TINYINT、SMALLINT、INT、BIGINT ；浮点数类型：FLOAT DOUBLE）、日期和时间类型(日期 DATE、时间 TIME、日期时间 DATATIME、时间戳 TIMESTAMP)、字符串类型（定长字符串 CHAR、变长字符串 VARCHAR、文本 TEXT、二进制数据 BLOB）等

创建表

USE game; #使用数据库
CREATE TABLE player (
  ID INT,
  NAME VARCHAR(100),
  LEVER INT,
  EXP INT,
  GOLD DECIMAL(10,2)
) #创建表
DESC player; 展示
ALTER TABLE player MODIFY COLUMN name VARCHAR(200);#修改table中的数据格式
ALTER TABLE player RENAME COLUMN name TO nick_name; #修改table中数据的名字
ALTER TABLE player ADD COLUMN last_login DATETIME; #添加新数据列
ALTER TABLE player DROP COLUMN last_login; #删除数据列
DROP TABLE player; #删除整个表

数据增删改查

INSERT INTO player (id,name,lever,exp,gold) VALUES(1,'张三',1,1,1); #添加数据 可添加多条，一个括号代表一条数据，不同数据用逗号分开
SELECT * FROM player;#查询数据，*代表所有
ALTER TABLE player MODIFY LEVEL INT DEFAULT 1;#修改table中level的默认值为1
UPDATE player set level = 1 where name = '李四'; #修改数据，level是要修改的列（多个字段可用逗号分开），where后面添加条件
DELETE FROM player where gold=0; #删除数据

常用语句

#查询
SELECT * FROM player WHERE level = 1; #查询语句
SELECT * FROM player WHERE level > 1 AND level < 5; #多条件查询语句可用AND连接
SELECT * FROM player WHERE level > 1 AND level < 5 OR exp > 1 AND exp < 5; #连接优先级：NOT>AND>OR
SELECT * FROM player WHERE level IN (1,3,5); #多条件查询语句也可用IN
SELECT * FROM player WHERE level BETWEEN 1 AND 10; # BETWEEN和AND来指定一个连续范围（包含边界）
SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10; # NOT 可用来取反，即查询不在1-10之间的数据
SELECT * FROM player WHERE name LIKE '王%'; # 查询姓王的玩家，LIKE可用于模糊查询，%表示任意个字符，_表示任意一个字符，也可使用正则表达式REGEXP表示：.表示任意一个字符，^表示开头，$表示结尾，[abc]其中任意一个字符，[a-z]范围内任意一个字符，A|B表示A或者B
SELECT * FROM player WHERE name REGEXP '^王.$';#查找姓王的两字玩家
SELECT * FROM player WHERE email IS NULL;#查找值为NULL不可用等号（可用<=>），NULL不等于NULL，要用IS NULL判断；非空则用 IS NOT NULL
SELECT * FROM player WHERE email = ''; # 空字符串并非是NULL，注意区分
# 排序
SELECT * FROM player ORDER BY LEVEL; #排序（默认升序，ASC） 使用ORDER BY
SELECT * FROM player ORDER BY LEVEL DESC; #排序（降序） ，在末尾添加DSC表示降序
SELECT * FROM player ORDER BY LEVEL DESC, exp ASC; #多字段排序 ，在字段后添加排列顺序
SELECT * FROM player ORDER BY 5 DESC; #排序（降序） ，可用数字代表字段（第五个字段即是level）
#聚合函数
SELECT COUNT(*) FROM player;#返回集合中的项目数
SELECT AVG(LEVEL) FROM player; #返回集合的平均值
SELECT MAX(LEVEL) FROM player; #返回最大值
SELECT MIN(LEVEL) FROM player; #返回最小值
SELECT SUM(LEVEL) FROM player; #求和
# 分组
SELECT LEVEL, COUNT(LEVEL) FROM player GROUP BY LEVEL; #GROUP BY 按值分组，显示每类的各内容 
SELECT LEVEL, COUNT(LEVEL) FROM player GROUP BY LEVEL HAVING COUNT(LEVEL) > 4; #HAVING 筛选
SELECT LEVEL, COUNT(LEVEL) FROM player GROUP BY LEVEL HAVING COUNT(LEVEL) > 4 ORDER BY COUNT(LEVEL) DESC; #筛选数据后再进行排序

SELECT SUBSTR(NAME,1,1), COUNT(SUBSTR(NAME,1,1)) FROM player #SUBSTR(NAME,1,1)截取字符串，name为要截取的字符串，第一个1代表起始位置，第二个1代表截取长度
GROUP BY SUBSTR(NAME,1,1)
HAVING COUNT(SUBSTR(NAME,1,1)) >= 5
ORDER BY COUNT(SUBSTR(NAME,1,1)) DESC
LIMIT 3, 2; #选取第四和第五限制，3为偏移量，2为选取的个数（可用做分页）

SELECT DISTINCT sex FROM player; #DISTINCT去重
SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION
SELECT * FROM player WHERE exp BETWEEN 1 AND 3; #UNION可将两次查询结果合并显示（自动去除重复的数据，若要不去重可在UNION后加 ALL），INTERSECT求两次查询结果的交集，EXCEPT求两次查询结果的差集（前肯后否）
SELECT * FROM player WHERE level BETWEEN 1 AND 3
INTERSECT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3; #INTERSECT求两次查询结果的交集

子查询

SELECT * FROM player WHERE LEVEL > (SELECT AVG(LEVEL) FROM player); #将查询结果作为另一个查询的条件
SELECT level, ROUND((SELECT AVG(LEVEL) FROM player)) AS averge, #ROUND 取整，AS重命名
level - ROUND((SELECT AVG(LEVEL) FROM player)) AS diff 
FROM player;
CREATE TABLE new_player1 SELECT * FROM player WHERE level < 5; #利用子查询创建TABLE
INSERT INTO new_player1 SELECT * FROM player WHERE level BETWEEN 6 AND 10; #利用子查询插入数据
SELECT EXISTS (SELECT * FROM player WHERE level >10); #EXISTS判断是否存在

表关联

SELECT * FROM player
INNER JOIN equip
ON player.id = equip.player_id; #INNER JOIN链接两个表，显示两表匹配数据

SELECT * FROM player
LEFT JOIN equip
ON player.id = equip.player_id; #左连接 LEFT JOIN链接两个表，显示左表数据和右表匹配数据

SELECT * FROM player
RIGHT JOIN equip
ON player.id = equip.player_id; #右链接 RIGHT JOIN链接两个表，显示右表数据和左表匹配数据

SELECT * FROM player, equip
WHERE player.id = equip.player_id; #使用WHERE可代替内连接

SELECT * FROM player p, equip e
WHERE p.id = e.player_id; #还可使用别名来代替（原理是笛卡尔积加过滤）

索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name, ...); #创建索引
SHOW INDEX FROM tbl_name; #查看索引
DROP INDEX index_name ON tbl_name; #删除索引
ALTER TABLE tbl_name ADD INDEX index_name (name); #更改表结构创建索引

视图

CREATE VIEW top10
AS
SELECT * FROM player ORDER BY level DESC LIMIT 10; #创建视图（数据是动态的） 等级前十名

ALTER VIEW top10
AS
SELECT * FROM player ORDER BY level LIMIT 10; #更改视图

DROP VIEW top10; #删除视图