SQL基础知识整理
SQL基础知识整理
0. 查看当前数据库的配置
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.21, for macos10.13 (x86_64) using EditLine wrapper
Connection id:4
Current database:mysql
Current user:root@localhost
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.7.21 MySQL Community Server (GPL)
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:latin1
Db characterset:latin1
Client characterset:utf8
Conn. characterset:utf8
UNIX socket:/tmp/mysql.sock
Uptime:10 min 17 sec
Threads: 1 Questions: 42 Slow queries: 0 Opens: 136 Flush tables: 1 Open tables: 129 Queries per second avg: 0.068
--------------
1.操作数据库
(0) 选择数据库
USE db_name;
(1) 创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
创建一个默认环境的数据库:
CREATE DATABASE IF NOT EXISTS db_test01;
指定字符集:
CREATE DATABASE IF NOT EXISTS db_test02 CHARACTER SET utf8;
指定字符集和校对规则:
create database mydb3 character set utf-8 collate utf8_bin;
(2) 查看数据库
SHOW DATABASES; # 查看所有数据库
SHOW CREATE DATABASE db_test03; # 查看某一个数据库的建表语句
(3) 修改数据库
ALTER DATABASE [IF NOT EXISTS]db_name [alter_specification [, alter_specification] ...]
alter_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
ALTER DATABASE db_test03 CHARACTER SET gbk;
(4) 删除数据库
DROP DATABASE [IF EXISTS] db_name
DROP DATABASE IF EXISTS db_test03;
2. 操作表
(1) 创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)[character set 字符集] [collate 校对规则]
CREATE TABLE IF NOT EXISTS t_test01(
id int primary key auto_increment,
name varchar(20) not null,
gender bit,
job varchar(40),
sallary double
) CHARACTER SET utf8;
(2) 查看表
查看表结构：desc tabName
查看当前数据库中所有表：show tables
查看当前数据库表建表语句 show create table tabName;
(3) 修改表
ALTER TABLE table ADD/MODIFY/DROP/CHARACTER SET/CHANGE (column datatype [DEFAULT expr][, column datatype]...);
*修改表的名称：rename table 表名 to 新表名;
ALTER TABLE t_test01 ADD image blob;
ALTER TABLE t_test01 MODIFY job varchar(30) NOT NULL;
ALTER TABLE t_test01 DROP gender;
RENAME TABLE t_test01 to t_user; # 修改表名
ALTER TABLE t_user CHARACTER SET gbk; # 修改表的字符集编码
ALTER TABLE t_user CHANGE name userName varchar(15); # 修改列名
(4) 删除表
DROP TABLE t_user;
3. 操作表的记录CRUD —— Create Read Update Delete
show variables like "character%”; # 查看当前数据库的字符集
mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。
client是客户端使用的字符集。
connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
server是服务器安装时指定的默认字符集设定。
system是数据库系统使用的字符集设定。（utf-8不可修改）
修改字符集:
set names gbk;指定当前窗口所使用的编码集
通过修改my.ini 修改字符集编码
1) 插入数据 — insert
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
插入的数据应与字段的数据类型相同。
数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
插入空值：不指定或insert into table value(null)
如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表
1> 按列插入一条数据
INSERT INTO t_user(id, name, gender, job, sallary)
VALUES (null, '张飞', 1, '先锋', 1000) # 插入一条数据时可以用value，也可以用values
;
2> 插入多条数据
INSERT INTO t_user
VALUES (null, '关羽', 1, '元帅', 4000),
(null, '刘备', 1, '皇帝', 8000);
2) 更新数据 — Update
UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行
~将所有员工薪水修改为5000元。
UPDATE t_user SET salary = 5000;
~将姓名为’张飞’的员工薪水修改为3000元。
UPDATE t_user SET salary=3000 WHERE name=‘张飞’;
~将姓名为’关羽’的员工薪水修改为4000元,job改为ccc。
UPDATE t_user SET salary=4000, job='ccc' WHERE name='关羽’;
~将刘备的薪水在原有基础上增加1000元。
UPDATE t_user SET salary=salary + 1000 WHERE name='刘备';
3) 删除数据 — Delete
delete from tbl_name
[WHERE where_definition]
如果不使用where子句，将删除表中所有数据。
Delete语句不能删除某一列的值（可使用update）
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。-- 外键约束
删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。
Note：
TRUNCATE TABLE 是先直接删除表，然后根据建表语句重新创建一个一摸一样的表
Delete from table; 删除所有记录会逐条判断是否符合删除条件， 然后逐条删除， 因此删除整张表的效率要低于TRUNCATE TABLE；
~删除表中名称为’张飞’的记录。
DELETE FROM t_user WHERE name='张飞';
~删除表中所有记录。
DELETE FROM t_user;
~使用truncate删除表中记录。
TRUNCATE TABLE t_user;
4) 查询数据 — Select
create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);
insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);
1> 基本查询
SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;
# 使用别名表示学生总分。
select name as 姓名, chinese + math + english as 总成绩 from exam;
select name 姓名, chinese + math + english 总成绩 from exam;
2> 使用 where 子句进行查询
# 查询姓名为张飞的学生成绩
SELECT * FROM exam WHERE name='张飞';
# 查询英语成绩大于90分的同学
SELECT * FROM exam WHERE english > 90;
# 查询总分大于230分的所有同学 # 别名不能在 WHERE 子句中使用
SELECT *, chinese + math + english 总成绩 FROM exam WHERE chinese + math + english > 230;
# 查询英语分数在 80－100之间的同学。
SELECT * FROM exam WHERE english BETWEEN 80 AND 100;
# 查询数学分数为75,76,77的同学。
SELECT * FROM exam WHERE math IN (75, 76, 77);
# 查询所有姓张的学生成绩。
SELECT * FROM exam WHERE name LIKE '张%'; # %代表一个或多个任意字符
SELECT * FROM exam WHERE name LIKE '张_'; # _代表一个任意字符
# 查询数学分>70，语文分>80的同学。
SELECT * FROM exam WHERE math > 70 AND chinese > 80;
3> 使用 order by 语句对结果集进行排序操作
SELECT column1, column2. column3.. FROM table where... order by column asc|desc;
asc 升序 -- 默认就是升序
desc 降序
# 对语文成绩排序后输出。
SELECT * FROM exam order by chinese desc;
# 对总分排序按从高到低的顺序输出 # 别名可以在 order by 语句中使用
SELECT *, chinese + math + english 总成绩 FROM exam ORDER BY 总成绩;
# 对姓张的学生成绩排序输出
SELECT *, chinese + math + english 总成绩 FROM exam WHERE name like '张%' ORDER BY 总成绩;
4> 聚合函数
a. Count — 用来统计符合条件的行的个数
# 统计一个班级共有多少学生？
SELECT COUNT(*) FROM exam;
# 统计数学成绩大于90的学生有多少个？
SELECT COUNT(*) FROM exam WHERE math > 90;
# 统计总分大于230的人数有多少？
SELECT COUNT(*) FROM exam WHERE chinese + math + english > 230;
b. SUM — 用来将符合条件的指定列求和
# 统计一个班级数学总成绩？
SELECT SUM(math) FROM exam;
# 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(chinese) 语文总成绩, SUM(math) 数学总成绩, SUM(english) 英语总成绩 FROM exam;
# 统计一个班级语文、英语、数学的成绩总和
INSERT INTO exam VALUE(null, '张三丰', null, 90, 80);
SELECT SUM(ifnull(chinese, 0) + math + english) FROM exam;
在执行计算时,只要有null参与计算,整个计算的结构都是null
此时可以用ifnull函数进行处理
# 统计一个班级语文成绩平均分
SELECT SUM(ifnull(chinese, 0)) / COUNT(*) 语文平均分 FROM exam;
c. AVG — 计算符合条件记录的指定列的平均值
# 求一个班级数学平均分？
SELECT AVG(math) FROM exam;
# 求一个班级总分平均分？
SELECT AVG(ifnull(chinese, 0) + math + english) 平均分 FROM exam;
d. MAX/MIN — 计算符合条件记录的指定列的最大/最小值
# 求班级总分的最高分和最低分
SELECT MAX(ifnull(chinese, 0) + math + english) 最高分 FROM exam;
SELECT MIN(ifnull(chinese, 0) + math + english) 最低分 FROM exam;
5> 分组查询
SELECTcolumn1,column2.column3.. FROMtable;
group by column having ...
# 对订单表中商品归类后，显示每一类商品的总价
SELECT product, sum(price) 总价 FROM orders GROUP BY product;
# 查询购买了几类商品，并且每类总价大于100的商品
SELECT product, sum(price) FROM orders GROUP BY product HAVING sum(price) > 100;
where子句和having子句的区别:
where子句在分组之前进行过滤having子句在分组之后进行过滤
having子句中可以使用聚合函数,where子句中不能使用
很多情况下使用where子句的地方可以使用having子句进行替代
# 查询单价小于100而总价大于150的商品的名称
SELECT product, SUM(price) FROM orders WHERE price < 100 GROUP BY product HAVING sum(price) > 150;
~~sql语句书写顺序:
select from where groupby having orderby
~~sql语句执行顺序:
from where select group by having order by
5) 备份和恢复数据库
备份数据库：
DreamLideMacBook-Pro:~dreamli$ mysqldump -u root -p db_test01 > /Users/dreamli/Workspace/Temp/db_test01.sql
恢复数据库:
方法一:
DreamLideMacBook-Pro:~dreamli$ mysql -u root -p db_test01 < /Users/dreamli/Workspace/Temp/db_test01.sql
方法二:
mysql> create database db_test01;
Query OK, 1 row affected (0.00 sec)
mysql> use db_test01;
Database changed
mysql>source/Users/dreamli/Workspace/Temp/db_test01.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...
4. 多表设计与多表查询
1) 外键约束
表是用来保存显示生活中的数据的，而现实生活中数据和数据之间往往具有一定的关系，我们在使用表来存储数据时，可以明确的声明表和表之前的依赖关系，命令数据库来帮我们维护这种关系，向这种约束就叫做外键约束.
如果一个字段X在一张表（表一）中是主关键字，而在另外一张表（表二）中不是主关键字，则字段X称为表二的外键；换句话说如果关系模式R1中的某属性集不是自己的主键，而是关系模式R2的主键，则该属性集称为是关系模式R1的外键。
create table dept(
id int primary key auto_increment,
name varchar(20)
) charset=utf8;
insert into dept values(null, '财务部'), (null, '人事部'), (null, '销售部'), (null, '行政部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
) charset=utf8;
insert into emp values(null,'奥巴马',1),(null,'哈利波特',2),(null,'本拉登',3),(null,'朴乾',3);
2) 多表设计
一对多:在多的一方保存一的一方的主键做为外键
一对一:在任意一方保存另一方的主键作为外键
多对多:创建第三方关系表保存两张表的主键作为外键,保存他们对应关系
3) 多表查询
# 笛卡尔积查询:
将两张表的记录进行一个相乘的操作查询出来的结果就是笛卡尔积查询,如果左表有n条记录,右表有m条记录,笛卡尔积查询出有n*m条记录,其中往往包含了很多错误的数据,所以这种查询方式并不常用
SELECT * FROM dept, emp;
# 内连接查询:查询的是左边表和右边表都能找到对应记录的记录
SELECT * FROM dept, emp WHERE dept.id = emp.dept_id;
SELECT * FROM dept INNER JOIN emp ON dept.id = emp.dept_id;
# 外连接查询:
# 左外连接查询:在内连接的基础上增加左边表有而右边表没有的记录
SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;
# 右外连接查询:在内连接的基础上增加右边表有而左边表没有的记录
SELECT * FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;
# 全外连接查询:在内连接的基础上增加左边表有而右边表没有的记录和右边表有而左表表没有的记录
SELECT * FROM dept FULL JOIN emp ON dept.id = emp.dept_id; # MySql 数据库不支持全外连接查询
# 但是可以通过 union 关键字模拟全外连接
SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id
UNION
SELECT * FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;
5.事务管理
start transaction； # 开启事务
rollback; # 回滚事务
commit; # 提交事务
6. 事务的四大特性:
原子性
一致性
隔离性
持久性
隔离性:
将数据库设计成单线程的数据库,可以防止所有的线程安全问题,自然就保证了隔离性.但是如果数据库设计成这样,那么效率就会极其低下.
数据库中的锁机制:
共享锁:在非Serializable隔离级别做查询不加任何锁,而在Serializable隔离级别下做的查询加共享锁,
共享锁的特点:共享锁和共享锁可以共存,但是共享锁和排他锁不能共存
排他锁:在所有隔离级别下进行增删改的操作都会加排他锁,
排他锁的特点:和任意其他锁都不能共存
如果是两个线程并发修改,一定会互相捣乱,这时必须利用锁机制防止多个线程的并发修改
如果两个线程并发查询,没有线程安全问题
如果两个线程一个修改,一个查询......
四大隔离级别:
Read uncommitted -- 不防止任何隔离性问题,具有脏读/不可重复度/虚读(幻读)问题
Read committed -- 可以防止脏读问题,但是不能防止不可重复度/虚读(幻读)问题
Repeatable read -- 可以防止脏读/不可重复读问题,但是不能防止虚读(幻读)问题
Serializable -- 数据库被设计为单线程数据库,可以防止上述所有问题
从安全性上考虑: Serializable>Repeatable read>read committed>read uncommitted
从效率上考虑: read uncommitted>read committed>Repeatable read>Serializable
真正使用数据的时候,根据自己使用数据库的需求,综合分析对安全性和对效率的要求,选择一个隔离级别使数据库运行在这个隔离级别上.
mysql 默认下就是Repeatable read隔离级别
oracle 默认下就是read committed个隔离级别
查询当前数据库的隔离级别:select @@tx_isolation;
设置隔离级别:set [global/session] transaction isolation level xxxx;其中如果不写默认是session指的是修改当前客户端和数据库交互时的隔离级别,而如果使用golbal,则修改的是数据库的默认隔离级别
脏读:一个事务读取到另一个事务未提交的数据
a 1000
b 1000
----------
a:
start transaction;
update account set money=money-100 where name=a;
update account set money=money+100 where name=b;
----------
b:
start transaction;
select * from account;
a : 900
b : 1100
----------
a:
rollback;
----------
b:
start transaction;
select* from account;
a: 1000
b: 1000
不可重复读:在一个事务内读取表中的某一行数据，多次读取结果不同 --- 行级别的问题
a: 1000 1000 1000
b: 银行职员
---------
b:start transaction;
select 活期存款 from account where name='a'; ---- 活期存款:1000
select 定期存款 from account where name='a'; ---- 定期存款:1000
select 固定资产 from account where name='a'; ---- 固定资产:1000
-------
a:
start transaction;
update accounset set 活期=活期-1000 where name='a';
commit;
-------
select 活期+定期+固定 from account where name='a'; --- 总资产:2000
commit;
----------
虚读(幻读):是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致 --- 表级别的问题
a: 1000
b: 1000
d: 银行业务人员
-----------
d:
start transaction;
select sum(money) from account; --- 2000 元
select count(name) from account; --- 2 个
------
c:
start transaction;
insert into account values(c,4000);
commit;
------
select sum(money)/count(name) from account; --- 平均:2000元/个
commit;
------------
7. 更新丢失问题:
两个线程基于同一个查询结果进行修改,后修改的人会将先修改人的修改覆盖掉.
悲观锁:悲观锁悲观的认为每一次操作都会造成更新丢失问题,在每次查询时就加上排他锁
通过在查询语句之后加一个 for update 实现；
乐观锁:乐观锁会乐观的认为每次查询都不会造成更新丢失.利用一个版本字段进行控制
通过在数据库表中加一个版本字段来实现。
查询非常多,修改非常少,使用乐观锁
修改非常多,查询非常少,使用悲观锁
