SQL学习

外键

添加外键

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

fk_class_id外键约束的名称
REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）

删除外键

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

索引

添加索引

ALTER TABLE students
ADD INDEX idx_name_score (name,score);

• 可以对一张表创建多个索引。
• 在插入、更新和删除记录时，需要同时修改索引。

唯一索引

ALTER TABLE students
ADD UNIQUE INDEX idx_name (name);

唯一约束

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

查询数据

基本查询

SELECT * FROM students;

查询的结果是一个二维表。

条件查询

SELECT * FROM students WHERE score>80 AND gender = 'M';

• 优先级NOT、AND、OR

常用的条件表达式

条件	说明
使用<>判断不相等	score <> 80
使用LIKE判断相似	name LIKE ‘ab%’，%表示任意字符，例如’ab%‘将匹配’ab’，‘abc’，‘abcd’

投影查询

SELECT id,name FROM students;

返回的结果集就只包含了我们指定的列。

列名重命名：语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …。

SELECT id,name points FROM students;

name重命名为points，id列名不变

排序

SELECT id,name,score FROM students ORDER BY score;

倒序：

SELECT id,name,score FROM students ORDER BY score DESC;

如果score列有相同的数据，要进一步排序，可以继续添加列名:

SELECT id,name,score FROM students ORDER BY score,name;

• 如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。

SELECT id,name,score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

查询第一页：

SELECT id,name,score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

查询第二页：

SELECT id,name,score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：
LIMIT总是设定为pageSize；
OFFSET计算公式为pageSize * (pageIndex - 1)。

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

聚合查询

查询的结果仍然是一个二维表。

SELECT COUNT(*) FROM students;

给查询的列设置一个别名。

SELECT COUNT(*) num FROM students;

其他函数：SUM,AVG,MAX,MIN，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

• 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL。

分组

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

多表查询（笛卡尔查询）

给列设置别名：

SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM 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;

连接查询

内连接

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;

外连接

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返回右表都存在的行。
LEFT OUTER JOIN返回左表都存在的行。
FULL OUTER JOIN把两张表的所有记录全部选择出来。

修改数据

插入INSERT

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

INSERT INTO students (class_id, name, gender, score) VALUES 
(1, '大', 'M', 81),
(2, '牛', 'M', 80);

更新UPDATE

UPDATE students SET score=score+10 WHERE score<80;

• 如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错。

删除DELETE

DELETE from students WHERE score<80;

• 如果WHERE条件没有匹配到任何记录，DELETE语句不会报错。

MYSQL

管理

数据库
列出所有数据库

SHOW DATABASES;

切换为当前数据库

USE test;

创建一个新数据库

CREATE DATABASE test;

删除一个数据库

DROP DATABASE test;

表
查看一个表的结构

DESC students;

查看创建表的SQL语句

SHOW CREATE TABLE students;

创建表使用CREATE TABLE语句，删除表使用DROP TABLE语句

DROP TABLE students;

给students表新增一列birth

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

修改birth列，把列名改为birthday，类型改为VARCHAR(20)

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

删除列

ALTER TABLE students DROP COLUMN birthday;

事务

多条SQL语句要想作为一个事务执行，就必须使用显式事务，使用BEGIN开启一个事务，使用COMMIT提交一个事务。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;