数据库的创建与删除
mysql> CREATE DATABASE test;
mysql> DROP DATABASE test;
选择要操作的数据库
对一个数据库进行操作时，要首先将其切换为当前数据库：
mysql> USE test;
创建表格
use test;
create table students
( id INT not NULL AUTO_INCREMENT, -- 自动添加的主键
name VARCHAR(40) NOT NULL,
班级 VARCHAR(40) NOT NULL,
成绩 SMALLINT,
primary key (id) -- 作为主键
);
-- id在插入时可以添加，自动添加
insert into students(name,班级,成绩) values ('小华','一班','99');
插入数据
基本语句INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
SELECT * FROM students;
更新数据
基本语句UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
UPDATE students SET score=score+10 WHERE score<80;
删除数据
基本语句DELETE FROM <表名> WHERE …;
DELETE FROM students WHERE id>=5 AND id<=7;
条件查询
SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
投影查询
SELECT id, score points, name FROM students WHERE gender = 'M';
排序
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;---由高到低 没有的DESC由低到高
分页
SELECT id, name, gender, score FROM students ORDER BY score ;
聚合查询
SELECT COUNT(*) boys FROM students WHERE gender = 'M';--- 计算个数
SELECT AVG(score) average FROM students WHERE gender = 'M';--- 计算平均数
分组
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;---多列分组
多表查询
SELECT * FROM students, classes;同时从students表和classes表的“乘积”
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
两表之间的连接
INNER JOIN是选出两张表都存在的记录
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
LEFT OUTER JOIN是选出左表存在的记录
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;
RIGHT OUTER JOIN是选出右表存在的记录
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 OUTER JOIN则是选出左右表都存在的记录
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;
脏读
Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
