一、SQL简介

SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统应充分利用SQL语言提高计算机应用系统的工作质量与效率。SQL语言不仅能独立应用于终端，还可以作为子语言为其他程序设计提供有效助力，该程序应用中，SQL可与其他程序语言一起优化程序功能，进而为用户提供更多更全面的信息。

二、sql基础语法

2.1、增删改查基本语法

功能名称	关键字	语法
插入数据	INSERT	INSERT INTO 表名（字段1，字段2） VALUES(内容1,内容2)
删除数据	DELETE	DELETE FROM 表名 WHERE 字段=内容
修改数据	UPDATE	UPDATE 表名 SET 字段1=新内容 WHERE 字段2=内容
查询数据	SELECT	SELECT * FROM 表名 WHERE 字段1=内容

以动漫主角表usertables为例

uname	sex	job
罗峰	男	精神念师
牧尘	男	域主

插入数据：INSERT|INSERT INTO usertables(uname,sex,job) VALUES("美杜莎","女","蛇人女王")
删除数据：DELETE|DELETE FROM usertables WHERE uname="牧尘"
修改数据：UPDATE|UPDATE usertables SET sex="女" WHERE uname="罗峰"
查询数据：SELECT * FROM usertables WHERE uname="罗峰"

2.2、新增数据库及表

2.2.1、新增数据库

语法： CREATE DATABASE 库名 CHARSET=UTF8;USE 新增库名;
解释：CHARSET=UTF8表示指定存储数据所用的字符编码,USE表示启用新增数据库。

2.2.2、新增表

CREATE TABLE 学生信息
( 学号 varchar(14) IDENTITY(1,1) PRIMARY KEY,
           姓名 varchar(8) UNIQUE NOT NULL,
           班级编号 varchar(14) REFERENCES '班级信息',
           年级 int null,
           性别 varchar(2) CHECK(性别in ('男’','女’)),
           民族 varchar(20) DEFAULT '未知该生民族',
           籍贯 varchar(50) )

三、sql联表语法

3.1、联表删除

DELETE 表1,表2 FROM 表1

LEFT JOIN 表2 on 表2.id=表1.id

WHERE 表1.id='条件内容';

注意：表名不能使用别名

3.2、联表更新

update cp_member m

inner join cp_card_bind cb on cb.user_id=m.user_id

inner join cp_merchant  mc on mc.user_id=m.user_id

set m.mobile_no=concat(m.mobile_no,'ZhuXiao'), /*表示：原内容后追加内容*/

m.id_no='513028',  /*表示：替换新内容*/ 

m.wx_id=concat('ZhuXiao',m.wx_id),/*表示：原内容前追加内容*/

mc.merch_name=concat(mc.merch_name,'ZhuXiao')

WHERE m.user_id='100000240';

3.3、联表查询

表壹：usertable

id	uname	jobid
1	蔡文姬	101
2	卓文君	102
3	高渐离	103

表贰：jobteble

id	jobname
101	乐师
102	诗人
104	射手

3.3.1、内连接

SELECT     
ut.id AS '用户ID',
ut.uname AS '用户姓名',
jt.jobname '职业名称'   

FROM usertable ut   
INNER JOIN jobteble jt on jt.id = ut.id

查询结果

用户ID	用户姓名	职业名称
1	蔡文姬	乐师
2	卓文君	诗人

3.3.2、左连接

SELECT     
ut.id AS '用户ID',
ut.uname AS '用户姓名',
jt.jobname '职业名称'   

FROM  usertable ut 
LEFT JOIN jobteble jt on jt.id = ut.id

查询结果

用户ID	用户姓名	职业名称
1	蔡文姬	乐师
2	卓文君	诗人
3	高渐离	null

3.3.3、右连接

SELECT     
ut.id AS '用户ID',
ut.uname AS '用户姓名',
jt.jobname '职业名称'   

FROM  usertable ut
RIGHT JOIN jobteble jt on jt.id = ut.id

查询结果

用户ID	用户姓名	职业名称
1	蔡文姬	乐师
2	卓文君	诗人
null	null	射手

3.3.4、完全连接

SELECT     
ut.id AS '用户ID',
ut.uname AS '用户姓名',
jt.jobname '职业名称'   

FROM  usertable ut   

FULL JOIN jobteble jt on jt.id = ut.id

查询结果

用户ID	用户姓名	职业名称
1	蔡文姬	乐师
2	卓文君	诗人
3	高渐离	null
null	null	射手

注意：有些数据库中要用FULL OUTER JOIN

四、存储过程及备份恢复

4.1、存储语法

名称	语法
创建存储过程	CREATE PROCEDURE 存储名() BEGIN SQL语句; END;
调用存储过程	CALL 存储名();
删除存储过程	DROP PROCEDURE IF EXISTS 存储名;

4.2、数据备份

名称	备份类型	语法
备份数据及结构	数据库备份	mysqldump -h数据地址 -P端口 -u用户名 -p密码数据库名> 备份文件.sql
仅备份结构	数据库备份	mysqldump -h数据地址 -P端口 -u用户名 -p密码 --opt -d 数据库名> 备份文件.sql
仅备份数据	数据库备份	mysqldump -h数据地址 -P端口 -u用户名 -p密码 -t 数据库名> 备份文件.sql
备份数据及结构	数据表备份	mysqldump -h数据地址 -P端口 -u用户名 -p密码数据库名表名> 备份文件.sql

4.3、数据恢复

名称	语法
数据恢复	mysql -h数据地址 -P端口 -u用户名 -p密码数据库名< 备份文件
SQL执行	Log into the database -e "use 数据库名;SQL语句;"

4.4、表解锁

查看是否锁表：show OPEN TABLES where In_use > 0;
查看锁表的进程ID：show processlist;
查看当前锁表的情况：SELECT * FROM information_schema.INNODB_TRX;
杀掉被锁表的进程：trx_mysql_thread_id kill 锁表进程ID;

五、语法拓展

5.1、同表多级查询以及 CASE WHEN 用法

编号	姓名	级别	上级编号
NO.1	李氏	1	null
NO.2	张氏	2	NO.1
NO.3	王氏	3	NO.2

select 别名.编号,别名.姓名,
CASE 别名.级别 
WHEN 1 THEN '一级'
WHEN 2 THEN '二级'
WHEN 3 THEN '三级'
ELSE 别名.级别 END AS '级别',
(select 别名1.姓名  FROM 表名 别名1 WHERE 别名1.编号=别名.上级编号 )AS '上级姓名',
(select 别名2.姓名  FROM 表名 别名2 WHERE 别名2.编号=
(select 别名3.上级编号  FROM 表名 别名3 WHERE 别名3.编号  =别名.上级编号 )) AS '上上级姓名'
FROM 表名 别名

拓展：CASE WHEN 的另一种用法
 CASE WHEN 别名.级别=1 THEN '一级'
WHEN 别名.级别=2 THEN '二级'
WHEN 别名.级别>3 THEN '无此级别'
ELSE 别名.级别 END AS '级别',

5.2、查询处理

5.2.1、截取值的部分字符串SUBSTRING

SUBSTRING（字段名称,数字1，数字2）查询某个字段左边第数字1开始截取数字2个字符
SUBSTRING（字段名称,数字）查询某个字段左边某个数字开始截取后面所有字符

5.2.2、时间戳与时间互相转换

时间戳转时间： SELECT DATEADD(S,时间戳所在列名 + 8 * 3600,'1970-01-01 00:00:00') FROM 表名
时间转时间戳：SELECT DATEDIFF(S,'1970-01-01 00:00:00',时间所在列名) - 8 * 3600 FROM 表名
注意：时间戳代表的是当前时间和1970-01-01 00:00:00相差的毫秒数， GMT是中央时区,北京在东8区,相差8个小时，所以北京时间 = GMT时间 + 8小时

SQL从菜鸟到王者篇

SQL从菜鸟到王者篇

一、SQL简介

二、sql基础语法

2.1、增删改查基本语法

2.2、新增数据库及表

2.2.1、新增数据库

2.2.2、新增表

三、sql联表语法

3.1、联表删除

3.2、联表更新

3.3、联表查询

3.3.1、内连接

3.3.2、左连接

3.3.3、右连接

3.3.4、完全连接

四、存储过程及备份恢复

4.1、存储语法

4.2、数据备份

4.3、数据恢复

4.4、表解锁

五、语法拓展

5.1、同表多级查询以及 CASE WHEN 用法

5.2、查询处理

5.2.1、截取值的部分字符串SUBSTRING

5.2.2、时间戳与时间互相转换

推荐阅读更多精彩内容