MySQL入门常用语句

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。

1. 数据库有三种类型：层次模型、网状模型和关系模型（市场主导）
2. 数据类型

名称	类型	说明
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

#BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

3. 主流的关系数据库有

• 商用数据库，例如：Oracle，SQL Server，DB2等；
• 开源数据库，例如：MySQL，PostgreSQL等；
• 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
• 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

4. 语法特点：SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。但一般SQL关键字总是大写，以示突出，表名和列名均使用小写。

5. 表的每一行称为记录（Record），记录是一个逻辑意义上的数据；表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

6. 安装管理

• linux安装mysql：apt-get install mysql-server
• 在命令提示符下输入mysql -u root -p 然后输入口令，如果一切正确，就会连接到MySQL服务器，同时提示符变为mysql>。输入exit退出MySQL命令行。注意，MySQL服务器仍在后台运行。
• 常用指令：SHOW DATABASES; USE MYSQL; SHOW TABLES;

7. 主键管理

• 对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。
• 主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。
• 可以使用多个列作为联合主键，但联合主键并不常用。

8. 外键管理

• 外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。
  增加外键

ALTER TABLE students  #修改student表
ADD CONSTRAINT fk_class_id  #外键约束的名称为fk_class_id
FOREIGN KEY (class_id)  #指定表格中的外键名称
REFERENCES class (id);  #这个外键关联class表的id

删除外键

ALTER TABLE students
DROP FOREIGN KEY fk_class_id #删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

9. 索引（索引是为了提高搜索速度）

• 创建多列索引

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

• 创建唯一索引

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

• 对某列添加唯一约束但不是创建唯一索引

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);  #name列没有索引，但依然有唯一性

10. 基本查询

SELECT * FROM table #*代表所有列,FROM选择查询的表

11. 条件查询

• where

SELECT * FROM students WHERE score >= 80; #SELECT * FROM <表名> WHERE <条件表达式>

• where + and

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

• where + or

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

• where + not

SELECT * FROM students WHERE NOT class_id = 2;

• NOT优先级最高，其次是AND，最后是OR，加上括号可以改变优先级

12. 投影查询（指定返回的列）

SELECT column1, column2, column3 FROM table

还可以对返回的列进行重命名

SELECT column1 alias1, column2, column3 alias FROM table  #format: 列 别名,

13. 排序

SELECT id, name, score 
FROM table 
WHERE class = 1
ORDER BY score, gender #DESC;
#默认是ASC升序排序，加了DESC可改为降序,ORDER BY应在WHERE之后

14. 分页查询

SELECT id, name, score 
FROM table 
ORDER BY score, gender #DESC
LIMIT 3 OFFSET 3;

#LIMIT限制的是每页显示的数据数目，OFFSET是指抵消/不看前面的几条

15. 聚合查询
    查询函数

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值
COUNT	计算某一列的总列数

SELECT AVG(score) average FROM table WHERE gender = 'M';
#筛选table里面gender为M的平均分，average为返回列的名称

SELECT class_id,gender, AVG(score) FROM students GROUP BY class_id, gender;
#GROUP BY先按class_id分组，再按gender分组

16. 多表查询

SELECT * FROM table1, table2
#返回的列数是table1和table2列数之和，行数是table1和table2行数之积
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;
#FROM <表名1> <别名1>, <表名2> <别名2>

17. 连接查询

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;

• 先确定主表，仍然使用FROM <表1>的语法；
• 再确定需要连接的表，使用INNER JOIN <表2>的语法；
• 然后确定连接条件，使用ON <条件…>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
• 可选：加上WHERE子句、ORDER BY等子句。

18. INSERT

INSERT INTO <table name> (字段1，字段2，...) VALUES (值1，值2...);
for instance
INSERT INTO students (class_id, name, gender, score) VALUES
(1, 'DABAO', 'M', 87),
(2, 'XIAOMING', 'M', 90);

19. UPDATE

UPDATE <table name> SET 字段1=值1，字段2=值2，... WHERE ...;
#注意如果没有WHERE,表的所有记录都会被更新。

20. DELETE

DELETE FROM <table name> WHERE...;
#注意如果没有WHERE，会导致把整个表删除

21. 命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。
22. 管理MySQL

SHOW DATABASES;
#其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们。其他的是用户创建的数据库。

• 创建新数据库

CREATE DATABASE test;

• 删除数据库

DROP DATABASE test;

• 对一个数据库进行操作时，要首先将其切换为当前数据库

USE test

• 列出当前数据库的所有表

SHOW TABLES;

• 查询一个表的结构

DESC students;

• 创建/删除表

CREATE TABLE student;
DROP TABLE student;

CREATE TABLE students 
(id bigint(20) NOT NULL AUTO_INCREMENT,
class_id bigint(20) NOT NULL,
name varchar(100) NOT NULL,
gender varchar(1) NOT NULL,
score int(11) NOT NULL,
PRIMARY KEY (id)
) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

• 给表格增加一列(ADD COLUMN)

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

• 修改表格某列的名称或类型(CHANGE COLUMN)

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

• 删除列(DROP COLUMN)

ALTER TABLE students DROP COLUMN birthday;

• 总结：列操作都要ALTER TABLE … (ADD/CHANGE/DROP) COLUMN …;

23. 实用的SQL语序(行操作)

• 插入或替换(REPLACE INTO)

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

• 插入或更新(INSERT INTO … ON DUPLICATE KEY UPDATE …)

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
#若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

• 插入或忽略(INSERT IGNORE INTO …)

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

• 快照

CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1; 

• 写入查询结果

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

24. 显式事务是begin开始，commit结束

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

25. 四种隔离级别以及可能会出现的状况

isolation level	脏读(dirty read)	不可重复读(non repeatable read)	幻读(phantom read)
read uncommitted	yes	yes	yes
read committed	-	yes	yes
repeatable read	-	-	yes
serializable	-	-	-