• SQL语言的分类
  • DQL（数据查询语言）
    • select关键字组合顺序
    • select简单查询用法
    • select条件查询
    • select函数使用
    • select多表连接查询
    • select子查询
    • Union结果集合并
    • Limit分页显示
• DDL语句
  • 表的创建
• DML语句
  • 插入表中数据
  • 常用命令

SQL语言的分类

DQL（数据查询语言）

select关键字组合顺序

• 格式

  select
  	...
  from
  	...
  where
  	...
  group by
  	...
  having
  	...
  order by
  	...
  limit
  	...
  

  PS：以上关键字对顺序不能颠倒！

• 执行关键字顺序

  1. from
  2. where
  3. group by
  4. having
  5. select
  6. order by
  7. limit

select简单查询用法

• 语法格式

  select 字段1，字段2、、、 from 表名

• 用 as（可省略） 字符给查询中的字段起别名：

  • select 字段 as 别名 from 表名

  • select 字段 别名 from 表名

• 若别名中有空格还想省略 as 字符来起别名：

  select 字段 '别名' from 表名

• select中的字段可以使用数学表达式，其显示结果为运算表达式后的值：

  select 字段 +、-、*、/ 数字 from 表名

select条件查询

• 语法格式

  select
  字段1、字段2、字段3、、、
  from
  表名
  where
  条件运算符;
  

• 条件运算符：

  查询条件	谓词
  比较	=, >, <, >=, <=, !=, <>, !>, !<
  确定范围	between and, not between and
  确定集合	in, not in
  字符匹配	like, not like
  空值	is null, is not null
  多重条件（逻辑运算）	and, or, not

  • and优先级比or高，and和or同时出现时，先执行and

  • like为模糊查询

    • % ： 代表多个字符

    • _ ： 代表一个字符

    • 例子：

      select
      	ename
      from
      	emp
      where
      	ename like '_A%'；
      

      意思为在emp表中寻找ename第二个字母为A的元素

    • 若查询的元素中带有 % 或 _ ，则在该字符前方使用转译字符 \

      • 例子：

        select ename from emp where ename like '%\_%'

• select排序

  • 语法格式

    select
    	字段1、字段2、、、
    from
    	表名
    order by
    	字段1 排序参数；
    

    若排序参数为空时默认升序排序！

  • 降序排序在排序参数后增加 desc 字符

    • 例 select * from emp order by sal desc;

  • 手动升序排序在排序参数后增加 asc 字符

    • 例 select * from emp order by sal asc;

  • 同时排序多个字段格式

    • 格式1

      select
      	字段1、字段2、、、
      from
      	表名
      order by
      	字段1 排序参数1，子段2 排序参数2、、、、；
      

      先判读 字段1 排序，若 字段1 相同则按照 字段2 排序！

    • 格式2

      select 字段1、字段2 from 表名 order by 2;

      最后的 2 是指代前面的 字段2

select函数使用

• 单行处理函数

  函数名称	函数意义
  lower(字段)	转换小写
  upper(字段)	转换大写
  substr(字段,起始下标,截取长度)	取子串
  concat(字段1,字段2)	字段拼接
  length(字段)	字段长度
  trim(字符串/字段)	去除字符串/字段前后空格
  round(字段/字面值,保留位数)	四舍五入
  rand()	生成0到1之间的随机数
  ifnull(字段数据,被指定的值)	将字段中的NULL数据转为被指定的值（NULL数据经过任何数据运算都只会是NULL）

  • 特殊单行处理函数

    case 字段 when 事件1 then 处理1 when 事件2 then 处理2 else 剩余处理 end

    • 解释：在case如果 when，则 then，当所有 when then 执行完成的其余情况则 else，最后以 end 结尾

    • 例如：

      select
      	ename, job,
      	(case job when 'MANAGER' then sal * 1.1 when 'SALESMAN' then sal * 1.5 else sal end)
      from
      	emp;
      

      • 解释：当job是MANAGER时sal提升1.1倍，当job是SALESMAN时sal提升1.5倍，其余sal不变

• 多行处理函数（分组函数）

  函数名称	函数意义
  count(字段)	计数
  sum(字段)	求和
  avg(字段)	求平均值
  max(字段)	求最大值
  min(字段)	求最小值

  PS：多行处理函数需要先对数据进行分组，若无分组，则默认整表为一组！

  PSS：多行处理函数会自动忽略NULL！

• 分组查询

  • 格式

    select
    	...
    from
    	...
    group by
    	...
    having //可省略
    	...
    

    PS：在一条select语句当中，若出现group by，则select后面只能跟参加分组的字段或分组函数，其余字段一律不能跟。

    例如：从 emp(员工) 表中找出各个 job(部门) 的分别总 sal(薪资)？

    select
    	job, sum(sal)
    from
    	emp
    group by
    	job;
    

  • having 可用于分组后的进一步筛选，但必须跟在 group by 后！

  • 优化策略：能用where完成的，先用where，否则再用having。

• 去除重复记录使用 distinct

  • 格式

    select distinct 字段1、字段2、、 from 表;

    PS：distinct只能出现在所有字段前方，若身后跟着多个字段，则联合去重！

select多表连接查询

• 格式

  select
  	...
  from
  	表1
  join
  	表2
  on
  	表1和表2的连接条件	
  join
  	表3
  on
  	表1和表3的连接条件
  ...
  

  PS：可以给表起别名！

  select
  	e.ename, d.dname	//用表别名进行操作
  from
  	emp e
  join
  	dept d	//给表起别名
  on
  	e.deptno = d.deptno;	//用表别名进行操作
  

• 多表连接之左外连接

  • 格式

    select
    	字段1、字段2、、
    from
    	表1
    left join
    	表2
    on
    	条件
    

    • 左连接会保留 left join 左端表（表1）的所有数据

• 多表连接之右外连接

  • 格式

    select
    	字段1、字段2、、
    from
    	表1
    right join
    	表2
    on
    	条件
    

    • 右连接会保留 right join 右端表（表2）的所有数据

select子查询

• 格式

select
	//可嵌套select子查询
from
	//可嵌套select子查询
where
	//可嵌套select子查询

子查询在select内的以上三处位置均可嵌入

Union结果集合并

• 格式

select 字段1、字段2、、 from 表1
union
select 字段1、字段2、、 from 表2

PS：可将两表查询出来的结果合并成一个表，但是两表所合并的字段数要一致（select和from之间的字段数量）

Limit分页显示

• 格式

limit startIndex, length;

PS：startIndex是起始下标，默认从0开始，length为步长！

limit在order by后使用！

• 例如：

• 显示排名薪资在第3到5名的员工姓名和薪资

  select
  	ename, sal
  from
  	emp
  order by
  	sal desc
  limit
  	2, 3;
  

• 分页公式

• limit (pageNo - 1) * pageSize, pageSize;

  解释：pageNo为页码值，pageSize为一页多少条记录。

DDL语句

表的创建

• 语法格式

  create table 表名(
  	字段名1 数据类型 default 默认值,
    字段名2 数据类型 default 默认值,
    字段名3 数据类型 default 默认值
  );
  

  PS：建议以 t_ 或者 tb_ 开始，可读性强。default省略默认值为NULL

• 数据类型

  数据类型	解释
  varchar	可变长度字符串（最长255）
  char	定长字符串（最长255）
  int	数字整数型（最长11）
  bigint	数字长整型
  float	单精度浮点型
  double	双精度浮点型
  date	短日期类型
  datetime	长日期类型
  clob	字符大对象（超过255字符，小于4G）
  blob	二进制大对象（存储图片、声音、视频流媒体数据）

DML语句

插入表中数据

• 语法格式

  insert into 表名(字段1，字段2，字段3····)
  values(值1，值2，值3···);
  

  PS：字段和值得一一对应（位置对应，数据类型对应，长度对应），且表名后的字段若全都省略了，则表示包含所有字段

常用命令

• 查看MySQL数据库系统的版本号

  select version();

• 在终端连接数据库：

  • 显示密码方式

    mysql -uroot -p密码

  • 隐藏密码方式

    mysql -uroot -p
    Enter password: 密码
    

• 退出数据库

  exit

• 显示所有数据库

  show databases;

• 使用数据库

  use 数据库名称

• 查询当前数据库

  select database();

• 终止正在输入的指令

  • control + c
  • 输入 \c

• 创建数据库

  create database 数据库名称

use mbook;
 
CREATE TABLE `TReader`
(
	`借书证号` char(6) NOT NULL PRIMARY KEY,
	`密码` varchar(20) NOT NULL,
	`姓名` char(8) NOT NULL,
	`性别` bit NOT NULL,
	`出生时间` date NOT NULL,
	`专业` char(12) NOT NULL,
	`借书量` int NOT NULL DEFAULT '0',
	`照片` varbinary(200) NULL,
    `备注` varchar(200) NULL
	
);


use MBOOK;
create view RBL
	as
	select TLend.借书证号,TReader.姓名,TReader.借书量,TLend.ISBN,
		   TBook.书名,TBook.出版社,TBook.价格,TLend.图书ID,TLend.借书时间
    from TReader inner join TLend on TReader.借书证号=TLend.借书证号
	             inner join TBook on TLend.ISBN=TBook.ISBN

use mbook;
-- drop procedure if exists call `BookID_Generate`;
delimiter $ 
create procedure `BookID_Generate` (in_ISBN char(18),_count int,firstID char(10))
begin
	declare cnt int ;
    set cnt=_count; 
	while cnt>0 
    do
		insert into `TBLend` values(firstID,in_ISBN,0);
		set firstID=firstID+1;
		set cnt=cnt-1;
	end while;
end $
delimiter ;


use mbook;
delimiter $$
create procedure Book_Borrow (in_ReaderID char(6),in_ISBN char(18),in_BookID char(10),out_str char(30) )
begin
	if not exists(select * from TReader where 借书证号=in_ReaderID)  then
		set out_str='该读者不存在';
	end if;
	if not exists(select * from TBook where ISBN=in_ISBN) then
		set out_str='该图书不存在';
	end if;
	if (select 借书量 from TReader where 借书证号=in_ReaderID)=5  then
		set out_str='读者借书量不能大于5';
	end if;
	if (select 库存量 from TBook where ISBN=in_ISBN)=0 then 
		set out_str='图书库存量为0';
	end if;
	if (in_ISBN in (select ISBN from TLend where 借书证号=in_ReaderID) ) then 
		set out_str='读者已经借过该书';
	end if;
	if exists (select * from TLend where 图书ID=in_BookID) then
		set out_str='该图书已经被借出';
	end if;
	set autocommit=0;    
	start transaction; 
	insert into TLend(借书证号,ISBN,图书ID,借书时间)values(in_ReaderID,in_ISBN,in_BookID,GETDATE());
	update TReader set 借书量=借书量+1 where 借书证号=in_ReaderID;
	update TBook set 库存量=库存量-1 where ISBNin_ISBN;
	update TBLend set 是否借出=1 where 图书ID=in_BookID;
	commit;
    
end $$
delimiter ;
 
 

use  mbook;
delimiter $ 
create procedure DB_backup (path varchar(100))
begin
	backup database MBOOK to disk=path with init;
end $
delimiter ;