SQL语言
SQL（Structure Query Language）分为四种语言
- 数据查询语言DQL
- 数据操纵语言DML
- 数据定义语言DDL
- 数据控制语言DCL
DDL（数据定义语言）
DDL全称：data defination language。主要是
MYSQL的建库，建表操作
- 创建数据库
create database <databaseName> charset utf8; - 显示所有数据库
show databases; - 使用数据库
use <databaseName> - 创建表
create table <tableName> (field type... , field type... , ...);例：create table teacher (id int primary key auto_increment, name varchar(10)); - 显示所有的表
show tables - 查表所含的字段
desc <tableName>; - 给表加字段
alter table <tableName> add 字段名 字段类型 - 修改字段的类型
alter table 表名 modify 字段名 字段类型; - 删除字段
alter table 表名 drop 字段名; - 修改表名
rename table 原名字 to 新名字; - 查看建表的细节
show create table 表名; - 修改表的字符集
alter table 表名 character set 字符集名称 - 修改列/字段名
alter table 表名 change 原始列/字段名 新列/字段名 字段类型; - 删除表
drop table 表名;
DML（数据操作语言）
DML全称：data manipulation language。主要是
MYSQL的增删改操作
- 插入数据
insert into 表名 (字段1,字段2) values (字段1的值,字段2的值); - 更新数据/修改数据
update 表名 set 列名=列值 where 列名=列值; - 删除记录
delete from 表名 where 列名 = 值;truncate table 表名;//删除整个表，再重新建立一个和原表结构一样的表
DQL（数据查询语言）
DQL全称：data query language。主要是
MYSQL的查询(select)操作
select的sql结构
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN
< right_tbale > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT
< limit_number >常用单表查询语句
- 去重查询
-- 去重查询。对查询结果中< select_list >的所有(重点)字段值一样的数据进行去重
select DISTINCT <select_list> from <tableName>;
-- WHERE条件查询
select * from <tableName> where <where_condition>;- 查询结果显示方式
-- 表的字段过多时，可使用"\G"修改数据显示方式
select * from <tableName>;
select * from <tableName> \G;- WHERE条件查询
>,<,>=,<=,!=,<>,is null,is not null,and,or,between ? and ?,in,not in,like,not like...
-
between ? and ?此为闭区间 -
like模糊查询。'%'表示多个字符，'_'表示 一个字符
- ORDER BY排序
asc升序，默认排序方式。从上到下，数据的值从小到大排序desc降序
-- 示例
select * from user order by age;
select * from user order by age desc;- GROUP BY分组
GROUP BY语句可结合一些聚合函数来使用，< select_list >中除了聚合函数部分外的其他查询字段必须和< group_list >一致
其作用和SELECT DISTINCT差不多，GROUP BY也会对查询结果去重SELECT DISTINCT和GROUP BY的区别
- 对于简单的去重，前者效率高于后者
- 前者可结合聚合函数使用，后者不能结合聚合函数。例如：
-- 查询user表的所有名字和每个名字同名的人数。此处使用了group by，但是distinct就做不到此效果，后者只会去重
select name count(name) as num from user group by name;
-- 查找不同年龄的人数
SELECT age, COUNT(age) FROM emp GROUP BY age- HAVING条件查询
还是条件查询。和WHERE的区别在于
- WHERE是对分组前的查询结果进行过滤，HAVING的作用是可以对分组后的查询结果再次过滤（即使没有使用分组也可以使用HAVING）
- WHERE中不能使用聚合函数，HAVING中能使用聚合函数
- LIMIT分页
limit <intBegin>, <intEnd>
- 上述区间为左闭右开区间
- 数据的下标从0开始计算
常用关键字
on，要区别于where。on只能用于多表查询，将不符合on中条件的数据中副表的字段全设为null并展示。where，不符合条件的数据将不被展示as，用于给字段起别名select name as username, password from member原本名为name现在改为username也可省略不写，如select name username, password from member
常用聚合函数
max(),min(),count()
now(),curdate(),curtime()
pi(),mod(45,7),sqrt(25),round() ...
多表查询
多表之间的关系
- 一对一。人和身份证。一个人只能有一张身份证
- 一对多。员工和部门。一个部门有多个员工，一个员工只能有一个部门
- 多对多。学生和课程表。一个学生选多门课程，一个课程也可以被多个学生选择
多对多关系中需要借助第三张中间表
多表查询分类
多表查询可分为
内连接：显示内连接，隐式内连接
外连接：左外链接，右外连接
全连接...
内连接查询
显式内连接
语法： select <select_list> from <table1Name> a, <table2Name> b on <on_condition>
隐式内连接
其效果和隐式内连接相同，只显示符合条件的语句
语法： select <select_list> from <table1Name> inner（inner可省略） join <table2Name> on <on_condition>
外连接查询
一般只用左外链接而不是用右外连接
左外链接
select 字段列表 from 表1 left outer(可省略) join 表2 on 条件 查询的是左表的所有数据以及其交交集。主体在左，右表字段为空的补null
右外连接
select 字段列表 from 表1 right outer(可省略) join 表2 on 条件 查询的是右表的所有数据以及其交交集，主体在右，左表字段为空的补null
==外连接除左外链接和右外连接外还有，自然连接和全连接==
全连接
mysql不支持全连接，oracle支持全连接
要想用mysql实现全连接，需要使用union关键字。
使用左外链接查询 + 'union' + 右外连接查询。
注意，这里的左外连接和右外连接select的字段必须一致，例如查A，B表时，两个连接的查询字段应该一致select a.*, b.*
union自带去重功能，union all不带去重功能
多表查询教程
常用的7中多表查询
mysql不支持FULL OUTER关键字，但又其他解决方案（union关键字）。具体方案见下文
select * from emp e left jion tmp t on e.tmp_id=t.id where e.id is null
union
select * from emp e right jion tmp t on e.tmp_id=t.id where t.id is null
子查询
概念：查询中嵌套查询，称嵌套查询为子查询
子查询的不同情况：
- 子查询的结果是单行单列的
查询员工工资小于平均工资的人select * from emp where emp.salary < (select avg(salary) from emp); - 子查询的结果是多行单列的
查询'财务部'和'市场部'所有员工信息select id from dept where name = '财务部' or name = '市场部';select * from emp where dept_id = 3 or dept_id = 2;子查询select * from emp where dept_id in (select id from dept where name = '财务部' or name = '市场部'); - 子查询的结果是多行多列的
子查询可以作为一张虚拟表
查询员工入职日期是2011-11-11日后的员工信息和部门信息select * from dept t1, (select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id;
DCL（数据库控制语言）
DCL全称：data control language。主要是对数据库事务的操作
MYSQL的事务：一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败
事务的操作
开启事务，回滚，提交事务
-- 开启事务
START TRANSACTION;
-- 提交事务。将事务的执行结果持久化，在提交之前数据都只是暂时改变而不是持久化地改变
COMMIT;
-- 回滚。当执行事务出现异常时需要回滚到开始事务之前的转台以保证数据安全
ROLLBACK;事务的四大特性
原子性，一致性，隔离性，持久性
原子性（Atomicity）
事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。
一致性（Consistency）
事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。
隔离性（Isolation）
隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。
下面的事务的隔离级别将详细介绍隔离性
持久性（Durability）
一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。
事务的隔离级别
事务隔离的概念
多个事务之间的隔离，是相互独立的。但是如果多个失事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就能解决这些问题
事务中存在的问题
- 脏读：一个事务读取到另一个事务中没有提交的数据
- 不可重复读（虚读）：同一个事务中多次执行同一个
select，读取到的数据发生了改变(被其它事务update并且提交)
事务A做更新数据的操作。事务B在事务A执行更新操作前后查询了两次数据，结果两次数据不一样
（因为事务B第一次读到了事务A更新数据前的数据和第二次读到了更新后的数据） - 幻读：同一个事务中多次执行同一个
select，读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)
虚读和幻读的区别：不可重复读的重点是事务执行的是修改，幻读的重点在于事务执行的是新增或者删除
隔离级别
READ-UNCOMMITED：读未提交的事务
可能出现的问题：脏读，不可重复读，幻读READ-COMMITED：读已提交（Oracle默认级别）
可能产生的问题：不可重复读，幻读（解决了脏读）REPEATABLE-READ：可重复读（MySql默认级别）
可能产生的问题：幻读（解决了脏读和不可重复读）SERIALIZABLE：串行化
可以解决所有问题。类似于加锁，一个事务使用一张表时另一个事务不能用这张表
注意：隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别
SELECT @@transaction_isolation;数据库设置隔离级别
-- 这里的<隔离级别的字符串>可用选项为上述隔离界别中的四个选项（read uncommitted，read committed等四个选项）
SET GLOBAL TRANSACTION SELECT isolation LEVEL <隔离级别的字符串>;导入导出sql文件
导出sql文件
$ mysqldump -u<username> -p <databaseName> > fileName.sql
$ mysqldump -u<username> -p -d <databaseName> > fileName.sql前者导出表结构和数据，后者只导出表结构
导出文件后，sql文件默认存放地在/usr/lib/mysql/目录下，根据情况不同，可使用find / -name fileName.sql命令查询文件所在地
导入sql文件
一般sql文件中没有建库命令，这样的话需自行建库后再执行sql文件。
执行sql文件有两种方式。1.进入mysql客户端中执行sql文件; 2.在shell终端中执行sql文件
- 进入mysql客户端
- 建库后，进入数据库
use databaseName - 在mysql客户端执行sql文件
mysql> source /home/abc/abc.sql;- 在shell终端中执行sql文件
$ mysql -u<username> -p <databaseName> < fileName.sql
