文章目录
SQL
什么是SQL？SQL(Structured Query Language)是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。
总的来说，SQL语言定义了这么几种操作数据库的能力：
- DDL：Data Definition Language
DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
- DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
- DQL：Data Query Language语法特点
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。
SQL语言关键字不区分大小写！！！
但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。
\c 废弃本次语句
<> 表示非
若命令行输错出现
'>
'>
则加上',就可以正常了，原因是''是成对出现的
- 语句顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
1.数据库
- 查看所有数据库：
show databases;(注意复数s，和;) - 切换（选择要操作的）数据库
test：USE test - 创建数据库
test：CREATE DATABASE test - 删除数据库
test:DROP DATABASE test - 修改数据库编码：
ALTER DATABASE test CHARACTER SET utf8
2.数据类型
| 名称 | 类型 | 说明 |
|---|---|---|
| INT | 整型 | 4字节整数类型，范围约+/-21亿 |
| BIGINT | 长整型 | 8字节整数类型，范围约+/-922亿亿 |
| REAL | 浮点型 | 4字节浮点数，范围约+/-1038 |
| DOUBLE | 浮点型 | 8字节浮点数，范围约+/-10308，double(5,2)表示最多5位数字，其中2位小数，如999.99 |
| 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 |
3.具体操作
创建数据库
CREATE DATABASE test;
选择数据库
USE test;
创建数据表
mysql> CREATE TABLE stu(
-> id INT NOT NULL AUTO_INCREMENT,//主键自动增长
-> class_id int NOT NULL,
-> name varchar(10) NOT NULL,
-> gender char(1) NOT NULL,
-> PRIMARY KEY(id)
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
查看表结构
desc stu;
查看结果如下
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| class_id | int | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
查询数据
SELECT * FROM stu;
结果如下
+----+----------+----------+--------+
| id | class_id | name | gender |
+----+----------+----------+--------+
| 1 | 1 | Li Ming | M |
| 2 | 2 | Liu Tao | M |
| 3 | 2 | Luo Bing | F |
+----+----------+----------+--------+
查找数据表中的列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL
排序
SELECT id,name,gender,score FROM students ORDER BY score;//升序
SELECT id,name,gender,score FROM students ORDER BY score DESC;//降序
如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：
SELECT id,name,gender,score FROM students ORDER BY score DESC,gender;
分页查询
按照每页最多3条记录查询
确定LIMIT和OFFSET应该设定的值：
LIMIT总是设定为pageSize(每页显示的数量)；
OFFSET计算公式为pageSize * (pageIndex - 1)。
limit m, 1 = limit 1 offset m表示跳过m条取1条，序号从0开始，实际就是取第m+1个
SELECT id,name,gender,score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;//第一页
SELECT id,name,gender,score FROM students ORDER BY score DESC LIMIT 3 OFFSET 3;//第二页
SELECT id,name,gender,score FROM students ORDER BY score DESC LIMIT 3 OFFSET 6;//第三页
注意：
-
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。 -
在MySQL中，
LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。 -
使用
LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。
聚合查询
查询students有多少条性别为’M’的记录
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
除了COUNT()函数外，SQL还提供了如下聚合函数：
| 函数 | 说明 |
|---|---|
| SUM | 计算某一列的合计值，该列必须为数值类型 |
| AVG | 计算某一列的平均值，该列必须为数值类型 |
| MAX | 计算某一列的最大值 |
| MIN | 计算某一列的最小值 |
每页3条记录，如何获得总页数
SELECT CEILING(COUNT(*) / 3) FROM students;
分组
查询查出每个班级男生和女生的平均分,并按照平均分排列
SELECT class_id,gender,AVG(score) average FROM students GROUP BY class_id,gender ORDER BY average DESC;
多表查询
SELECT
s.id sid,
name,
gender,
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;
INNER JOIN选出两张表都存在的记录，也可写成JOINLEFT JOIN选出左表存在的记录RIGHT JOIN选出右表存在的记录FULL JOIN选出左右表都存在的记录
内连接是取左右两张表的交集形成一个新表，用FROM并列两张表后仍然还是两张表。如果还要对新表进行操作则要用内连接。从效率上看应该FROM并列查询比较快，因为不用形成新表。
插入数据
INSERT语句的基本语法是：
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
mysql> insert into stu(id,class_id,name,gender) VALUES(1,1,'Li Ming','M');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu(class_id,name,gender) VALUES(2,'Liu Tao','M');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu(class_id,name,gender) VALUES(2,'Luo Bing','F');
Query OK, 1 row affected (0.01 sec)
更新数据
UPDATE语句的基本语法是：
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
UPDATE stu SET name = 'Nancy' WHERE id = 1;
SELECT * FROM stu;
结果如下
+----+----------+----------+--------+
| id | class_id | name | gender |
+----+----------+----------+--------+
| 1 | 1 | Nancy | M |
| 2 | 2 | Liu Tao | M |
| 3 | 2 | Luo Bing | F |
+----+----------+----------+--------+
删除数据表数据
DELETE语句的基本语法是：
DELETE FROM <表名> WHERE ...;
DELETE FROM stu WHERE id = 1;
NOT IN
获取所有非manager的员工emp_no
SELECT emp_no FROM employees where emp_no NOT IN (SELECT emp_no FROM dept_manager);
DISTINCT
从employees中查找入职时间排倒数第3的员工的全部信息(注意同一天可能入职多人)
第一种
使用DISTINCT去重
SELECT * FROM employees WHERE hire_date = (SELECT DISTINCT hire_date FROM employees
ORDER BY hire_date DESC LIMIT 2,1)
第二种
SELECT * FROM employees WHERE hire_date = (SELECT hire_date FROM employees
GROUP BY hire_date ORDER BY hire_date DESC LIMIT 2,1)
UNION（并操作）
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
INTERSECT(交操作)
SELECT country FROM Websites
INTERSECT
SELECT country FROM apps
ORDER BY country;
MINUS(差操作)
属于集合A不属于集合B
SELECT country FROM Websites
MINUS
SELECT country FROM apps
ORDER BY country;
注意：并、交和差操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。
ALTER
向表中添加列
alter table stu add score int(3) not null;
修改列，例如把列score改为num
alter table stu change column score num int(3) not null;
删除列
alter table stu drop column num;
实用SQL语句
插入或替换
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。
插入或更新
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用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），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。
快照
如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：
对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
新创建的表结构和SELECT使用的表结构完全一致。
写入查询结果集
如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。
例如，创建一个统计成绩的表statistics，记录各班的平均成绩：
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);
然后，我们就可以用一条语句写入各班的平均成绩：
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score)
FROM students GROUP BY class_id;
4.事务
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
-
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
-
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢- 失。
隔离级别
READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE- 如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是
Repeatable Read
471
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
