mysql 廖雪峰_廖雪峰大佬SQL教程学习笔记

1，SQL(Structured Query Language)，就是访问和处理(操作)关系数据库的计算机标准语言。SQL语句既可以查询数据库中的数据，也可以添加，更新和删除数据库中的数据，还可以对数据库进行管理和维护。SQL定义几种操作数据库的能力。DDL:Data Definition Language(DDL允许用户定义数据，也就是创建表，修改表的结构)

DML:Data Manipulation Language(DML提供添加，删除，更新数据的能力)

DQL:Data Query Language(DQL允许用户查询数据)

2，NoSQL(非SQL数据库，不是关系数据库)。今天，SQL数据库仍然承担各种应用程序的核心数据存储，而NoSQL数据库作为SQL数据库的补充，两者不再是二选一的问题，而是主从关系。

3，为什么需要数据库？

假如将需要数据存放到CSV文件中，随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成为了大问题。读写文件并解析出数据需要大量重复的代码；

从成千上万的数据中快速查找出指定数据需要复杂的逻辑

4，关系模型把数据看作一个二维表格，任何数据都可以通过行号+列号来唯一确定。

5，数据类型：对于一个关系表，除了定义每一列名称外，还需要定义每一列的数据类型。

通常来讲，BIGINT就能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求。

6，语法特点：SQL语言关键字不区分大小写，但是针对不同的数据库，对于表名，列名，Linux上区分大小写，Windows不区分大小写。

7，关系数据库是建立在关系模型上的，关系模型本质上就是若干个存储数据的二维表。表的每一行称为记录，记录是一个逻辑意义上的数据。表的每一列称为字段，同一个表的每一行记录拥有相同的字段。在关系数据库中，关系是通过主键和外键来维护的。

8，在关系表中，有一个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指通过某个字段唯一区分出不同的记录。这个字段就是主键。

9，选取主键的一个基本原则是：不适用任何业务相关的字段作为主键。因此，身份证号，手机号，邮箱看上去可以作为主键，均不可用作主键。常见作为id(主键)字段的类型：

自增数据类型，全局唯一GUID类型。

10，把一个表的数据与另一个表的数据关联起来，这种列称之为外键。外键并不是通过列名来实现的，而是通过定义外键约束实现的。

ALTER TABLE students

ADD CONSTRAINT fk_class_id

FOREIGN KEY (class_id)

REFERENCE classes(id);

通过外键约束，关系数据库可以保证无法插入无效数据。

删除一个外键约束：

ALTER TABLE students

DROP FOREIGN KEY fk_class_id

11，多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系。

12，一对一，有些应用程序把一大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，获得更好的性能。

13，在关系数据库中，如果有上万，上亿条记录，想要获得非常快的速度，就需要使用索引。索引是关系数据库中对某一列或着多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，直接定位到符合条件的记录。

ALTER TABLE students

ADD INDEX index_score (score);

索引有多列：

ALTER TABLE students

ADD INDEX idx_name_score (name,score)

索引的效率取决于索引列是否散列。即该列的值如果越不相同，索引效率越高。反过来，如果记录的列里面存在大量相同的值，创建索引就没有意义。

创建索引的缺点就是在插入，更新，删除记录时候，需要同时修改索引，因此，索引越多，插入，更新，删除记录就很慢。对于主键，关系数据库会自动创建索引。使用主键索引的效率是最高的，因为主键会保证唯一性。

设计关系数据库的时候。看上去具有唯一值的列，但是有不是主键(具有业务含义)，应该添加唯一性索引：

ALTER TABLE students

ADD UNIQUE INDEX uni_identify_card (identify_card)

也可以只对某一列添加唯一约束而不是创建唯一索引：

ALTER TABLE students

ADD CONSTRAINT uni_name UNIQUE(name)

14，练习MySQL的(查询数据)SQL脚本(init-test-data.sql)​github.com

通过运行这个脚本，可以创建test数据库，和对应的students,classes表students表classes表基本查询

SELECT * FROM students;

*表示所有列，查询结果为一个二维表，也就是students表，

SELECT 100+300;

上述查询会直接计算表达式的结果，也就是SELECT可以用于计算，但是这不是它的强项，但是，不带FROM的SELECT语句有一个用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。条件查询

SELECT * FROM students WHERE score>=80;

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

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

SELECT * FROM students WHERE NOT class_id=2

SELECT * FROM students WHERE class_id <> 2 --同第四条等价，而且第四条不常用SELECT * FROM students WHERE (score<80 OR score>90) AND gender='M';

SELECT * FROM students WHERE score>80 AND score <90;

SELECT * FROM students WHERE score BETWEEN 80 AND 90;--于上一条等价

对于复杂的逻辑，如果不添加括号，条件运算符按照NOT>AND>OR优先级进行。投影查询

SELECT id,score,name FROM students;

SELECT id,score points,name FROM students;

SELECT id,score points,name FROM students WHERE gender='M';

这里我们只希望返回某些列，列的顺序可以跟原表不一样，可以为列重命名，可以添加条件语句排序

SELECT id,name,gender,score FROM students ORDER BY score; --从低到高SELECT id,name,gender,score FROM students ORDER BY score DESC; --从高到低SELECT id,name,gender,score FROM students ORDER BY score DESC,gender;

SELECT id,name,gender,score FROM students ORDER BY score DESC,gender ASC; --与上一条等价，ASCII由低到高SELECT id,name,gender,score FROM students WHERE class_id=1 ORDER BY score DESC:

使用SELECT查询的时候，查询结果按照id排序的，也就是根据主键排序的。ORDER BY可以按照其他条件排序分页查询

SELECT id,name,gender,score FROM students ORDER BY score DESC LIMIT M OFFSET N;

SELECT id name,gender,score FROM students ORDER BY score DESC LIMIT N M;--MySQL中等价于上一句SELECT id,name,gender,score FROM students ORDER BY score DESC LIMIT 3 OFFSET 9;

SELECT id,name,gender,score FROM students ORDER BY score DESC LIMIT 3 OFFSET 9;--返回空

M:pageSize

N:pageSize*(pageIndex-1)聚合查询

SELECT COUNT(*) FROM students;

SELECT COUNT(*) num FROM students;

SELECT AVG(score) average FROM students WHERE gender='M';

SELECT AVG(score) average FROM students WHERE gender='X';

SELECT CEILING(COUNT(*)/3) FROM students;

SELECT COUNT(*) num FROM students GROUP BY class_id;--分组聚合SELECT class_id,COUNT(*)num FROM students GROUP BY class_id;--效果同上，但是效果可以显示class_idSELECT class_id,gender,COUNT(*) num FROM students GROUP BY class_id,gender;--使用多个列进行分组

MAX,MIN函数不限于数值类型，如果是字符类型，MAX,MIN会返回排序后的最大，最小值。

要特别注意，如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0,SUM,AVG,MAX,MIN会返回NULL.多表查询

SELECT * FROM students,classes; --返回的是笛卡尔乘积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

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

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

连接查询是另一种的多表查询，连接查询对多个表进行JON运算--先确定一个主表为结果集，然后其他表的行选择性的连接到主表结果上。

15，修改数据INSERT

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

INSERT INTO students (class_id,name,gender,score) VALUES

(1,'小狗','M',89),

(2,'小yolo','F',90);UPDATE

UPDATE students SET name='小狗',score=89 WHERE id=1;

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

UPDATE students SET score=10;

UPDATE语句没有WHERE条件，整个表的记录都会更新，所以执行UPDATE要小心，最好先使用SELECT来测试WHERE条件是否筛选了期望的记录集合，然后再用UPDATE更新。

使用MySQL这类关系数据库时候，UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。DELETE

DELETE FROM students WHERE id=1

DELETE FROM students WHERE id=9899

DELETE FROM students;--删除整个表

16，MySQL

本地Mysql WorkBench远程连接云服务器中的MySQL

第一步填写连接信息：

第二步点击测试连接：

如果显示错误：host ... is not allowed to connect to this MySql server，说明远程服务器上的MySQL没有允许远程连接，可以通过以下方式修改服务器上的MySQL.让用户名为myuser使密码为mypassword从任何主机连接到mysql服务器

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

2. 允许用户myuser从ip为118.184.1.3的主机连接到mysql服务器，并使用mypassword作为密码

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'118.184.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

3. 允许用户myuser从ip为118.184.1.3的主机连接到mysql服务器的dk数据库，并使用mypassword作为密码

GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'118.184.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

第三步，再测试连接，连接成功

17，管理MySQL

SHOW DATABASES;

其中，informationschema, mysql, performance_schema, sys是系统数据库，不可以更改，其他数据库是自己创建的数据库。

CREATE DATABASE test;

DROP DATABASE test;

USE test; --切换为当前数据库SHOW tables;

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

DESC students; --查看一个表的结构SHOW CREATE TABLE students;--查看创建表的SQL语句DROP TABLE students;

--修改一个表的结构ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

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

ALTER TABLE students DROP COLUMN birthday;

EXIT

EXIT仅仅断开了客户端和服务器的连接，MySQL仍然在运行。

17，实用的SQL语句

--插入或替换REPLACE INTO students(id,class_id,name,gender,score) VALUES(1,1,'小明','F',88);

如果希望插入一条新纪录(INSERT),但是如果记录已经存在，就先删除原纪录，再插入新纪录。此时，就可以使用REPLACE语句，这里不必先查询，再决定是否先删除再插入。

--插入或更新INSERT INTO students(id,class_id,name,gender,score) VALUES(1,1,'小明','F',99) ON DUPLICATE KEY UPDATE name='小明',gender='F',score='99';

如果记录存在，就更新记录，不存在，插入记录。

--插入或更新INSERT IGONRE 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;

快照：复制一份当前表的数据到新表。

--写入查询结果集CREATE TABLE statistic(

id BIGINT NOT NULL AUTO_INCREMENT,

class_id BIGINT NOT NULL,

average DOUBLE NOT NULL,

PRIMARY KEY(id)

);

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

查询结果写入表中，可以结合INSERT和SELECT.

18，事务

定义：把多条语句作为一个整体进行操作的功能，被称为事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就没有执行这些SQL一样，不会对数据库数据有任何改动。

数据库事务具有ACID4个特性：A：Atomic，原子性，将所有SQL作为原子工作单位元执行，要么全部执行，要么全部不执行；

C：Consistent，一致性，事务完成后，所有数据的状态都是一致的；

I：Isolation，隔离性，如果多个事务并发执行，每个事务作出的修改必须与其他事务隔离；

D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐士事务。

BEGIN;

UPDATE accounts SET balance=balance-100 WHERE id=1;

UPDATE accounts SET balance=balance+100 WHERE id=2;

COMMIT;

手动把多条语句作为一个事务，使用BEGIN开启一个事务，COMMIT提交一个事务，这种事务为显示事务，COMMIT试图把事务内的SQL所作的修改永久保存，如果COMMIT失败，整个事务也会失败。

BEGIN;

UPDATE accounts SET balance=balance-100 WHERE id=1;

UPDATE accounts SET balance=balance+100 WHERE id=2;

ROLLBACK;

有些时候，我们希望主动让事务失败，这时可以用ROLLBACK回滚事务，整个事务会失败。

隔离级别：

对于两个并发的事务，如果涉及到操作同一条记录的时候，可能会发生问题，因为并发操作会带来数据的不一致性，包括脏读，不可重复读，幻读。数据库系统提供了隔离级别来让我们有针对性地选择事务地隔离级别，避免数据不一致问题。

Read Uncommitted

Read Uncommitted是隔离级别最低地一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交地数据，如果另一个事务回滚，那么当前事务读到地数据是脏数据。Read Committed

Read Committed级别隔离下，一个事务可能遇到不可重复读(Non Repeatable Read)的问题。

不可重复读指，在一个事务内，多次读取一个数据，在这个任务还没有结束，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一样Repeatable Read

Repeatable Read级别的隔离下，一个事务可能会遇到幻读(Phantom Read)问题

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时候，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。Serializable 是最严格地隔离级别，所有事务按照次序进行，脏读，不可重复读，幻读都不会出现。缺点是，效率会下降，应用程序性能会降低。

MySQL，如果使用InnoDB，默认隔离级别是Repeatable Read.