进入数据库
mysql -u root -p
检察数据库
show databases;
创建game数据库
create database game
删除数据库
drop database game；
退出数据库
quit/exit
创建表
关键字常用大写
创建表
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(100);
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;
数据的增删改查
插入一行数据
INSERT INTO player (id,NAME,LEVEL,exp,gold)VALUE(1,'zhangsan',1,1,1);
检察数据
SELECT * FROM player;
约束：默认值，非空，唯一约束，主键约束（包管数据唯一性，不为空，只有一个主键），外键约束（包管数据的一致性，一个表的外键必须是一个表的主键）
修改数据
UPDATE player SET LEVEL = 2 WHERE NAME = 'zhangsan';
UPDATE player SET exp=0,gold=0;
删除数据
DELETE FROM player WHERE goal=1;
数据的导入导出
SELECT *FROM player WHERE level>1 AND level <6
SELECT *FROM player WHERE level>1 OR level <6
SELECT *FROM player WHERE LEVEL NOT IN (1,3,5)
SELECT *FROM player WHERE LEVEL BETWEEN 4 AND 8
SELECT *FROM player WHERE NAME LIKE '张%'
%任意多个字符，_任意一个字符
正则表达式
查找第一个字符是王且只有两个字的人
SELECT *FROM player WHERE NAME REGEXP '^王.$'
查找level为空的，没有填写
SELECT *FROM player WHERE LEVEL IS NULL
SELECT *FROM player WHERE LEVEL IS NOT NULL
填写了为空的值，空字符串
SELECT *FROM player WHERE email =’’;
排序 order by
SELECT *FROM player ORDER BY LEVEL; 根据level升序排序
SELECT *FROM player ORDER BY LEVEL DESC; 降序
SELECT *FROM player ORDER BY LEVEL DESC ,exp ASC; 按level降序，level雷同按exp升序
聚合函数
SELECT COUNT(*) FROM player;
SELECT AVG(LEVEL) FROM player;
分组 group by
SELECT LEVEL ,COUNT(LEVEL) FROM player GROUP BY LEVEL
having：筛选分组后的数据
SELECT LEVEL ,COUNT(LEVEL) FROM player GROUP BY LEVEL HAVING COUNT(LEVEL)>1
【
SELECT SUBSTR(name,1,1),COUNT(SUBSTR(NAME,1,1)) FROM player substr是从name的第一个字符开始截取一个字符，即姓氏
GROUP BY SUBSTR(NAME,1,1) 根据姓氏进行分组
HAVING COUNT(SUBSTR(NAME,1,1))>=2 筛选姓氏数量大于等于2的
LIMIT 1 只显示第一名
】
limit 3，4 显示第3名到第7名
distinct 去重（去除重复的记载）
sex列中不重复的
SELECT DISTINCT sex FROM player;
union 归并查询结果集（并集）
把两条查询结果归并起来
SELECT * FROM player WHERE LEVEL BETWEEN 1 AND 5;
UNION
SELECT * FROM player WHERE exp BETWEEN 30 AND 40;
用union all来归并，不会去除两个结果中重复的记载
intersect 归并查询结果的交集
一个查询的条件需要用到另一个查询的结果
比如：想查询等级大于平均等级的玩家
SELECT *FROM player WHERE LEVEL > (SELECT AVG(LEVEL)FROM player);
SELECT LEVEL, ROUND((SELECT AVG(LEVEL) FROM player)) AS average, round是四舍五入
LEVEL - ROUND((SELECT AVG(LEVEL) FROM player)) AS diff AS xx是重定名列名
FROM player
子查询要用括号包裹
ROUND(SELECT AVG(LEVEL) FROM player) -- 错误
ROUND((SELECT AVG(LEVEL) FROM player)) -- 正确
使用子查询创建一个新的表
CREATE TABLE new_player SELECT * FROM player WHERE LEVEL<5
使用子查询插入数据
insert into
INSERT INTO new_player SELECT * FROM player WHERE LEVEL BETWEEN 6 AND 10;
判断是否存在
SELECT EXISTS (SELECT * FROM player WHERE LEVEL > 10)
表关联
