古早MySQL笔记

MySQL

from liaoxuefeng

数据模型：

• 层次模型（类似 树）
• 网状模型（类似 无向图）
• 关系模型（类似 excel）

数据类型：

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

关系模型

主键->不可重复，只能唯一

主键最好与业务毫无关联（例如设成一个自增整数，或者用UUID）等，不然一旦业务发生变更，会影响整体的业务。

外键->与外部的表的column形成联系，有时可以避免无效输入

索引->提高查询速度

查询

-- 表示注释

-- query all the data in students
SELECT * FROM students;

-- SELECT can also be used to detect connectivity
SELECT 1;

-- 条件查询
-- WHERE for filter
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

-- <> equals to !=
SELECT * FROM student WHERE score <> 90;

-- LIKE is used for string similarity
-- % means string of any length,so baokker bbaokker is all feasible in the below code
SELECT * FROM student WHERE name LIKE %aokker ;

-- 投影查询
-- 只查询部分
-- 等于用= 而不是 == ！！
SELECT score, name FROM students WHERE score >= 85;
-- can also rename
-- 'score' will be renamed to 'point' in the output

-- 排序 sort
-- order by (some column),default as AEC(ascending)
SELECT id, name, gender, score FROM students ORDER BY score;
-- DESC(descending) will reverse the order
SELECT id, name, gender, score FROM students ORDER BY score DESC;
-- WHERE should be put before the ORDER BY
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

-- 分页查询
-- 从结果集中“截取”出第M~N条记录，这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

-- 聚合查询
-- for some calculating like average sum frequency
-- num of students,rename as num
SELECT COUNT(*) num FROM students;
-- average
SELECT AVG(score) average FROM students WHERE score >= 60
SELECT MAX(name) FROM students
SELECT MIN(name) FROM students
SELECT SUM(score) sum FROM students
-- 分组
-- 查出每个班级的男女平均分
SELECT class_id,gender,AVG(score) FROM students GROUP BY class_id,gender

-- 多表查询
-- 笛卡尔积
-- col(result) = col(stu) + col(class)
-- row(result) = row(stu) * row(class)
SELECT * FROM students, classes;
-- set alias
-- tableName.attribute
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;
-- table can also use alias
SELECT
	s.id id
	c.name cname
FROM students s,classes c;

-- 连接查询
-- INNER JOIN
SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>；
-- OUTER JOIN
-- RIGHT
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

-- LEFT
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;

-- FULL
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

修改数据

-- INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (class_id, name, gender, score) VALUES 
(2, '大牛', 'M' ,80),
(3, '菜鸡', 'M' ,30);

-- UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
UPDATE students SET name='大牛', score=66 WHERE id=1;
-- can use expression
UPDATE students SET score = score - 10 WHERE id=1;

-- DELETE
DELETE FROM students WHERE id>=5 and id<=7;

-- DELETE and UPDATE can both used without the restriction of WHERE,which could be dangerous
UPDATE students SET score=0;
DELETE FROM students;
-- test before use
SELECT id FROM students WHERE ..