数据库学习笔记
数据库学习笔记
数据库基础
设计原型——》数据库建模——》编写后台程序
MySQL
创建数据库
进入数据库
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;
数据的导入导出
导出数据
将数据导入到game.sql文件中
mysqldump -u root -p password
数据库名
表名
game.sql
打开这个文件
cat game.sql
导入数据
mysqldump -u root -p password
数据库名
表名
< game.sql
常用语句
查找
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 合并查询结果的交集
如果MYSQL 不支持intersect关键字 ，可以使用连接查询来替换实现 ( INNER JOIN)
except 差集
子查询
一个查询的条件需要用到另一个查询的结果
比如：想查询等级大于平均等级的玩家
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)
表关联
INNER JOIN
LEFT JOIN 左连接
RIGHT JOIN 右连接
SELECT *FROM student
INNER JOIN course
ON student.Sno = course.Cno;
用
where
一样
表连接
=
笛卡尔积
条件过滤
索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name(index_col_name,……)
创建索引
CREATE INDEX email_index ON FAST(email);
alter table fast add index name_index(name);
查看索引
SHOW INDEX FROM fast
删除索引
DROP INDEX email_index ON fast;
视图
创建视图
CREATE VIEW top10
AS
SELECT * FROM player ORDER BY LEVEL DESC LIMIT 10;
查看视图
SELECT * FROM top10;