MySQL指令

SQL语言的分类
- DQL（数据查询语言）
DDL语句
- 表的创建
DML语句
- 插入表中数据
- 常用命令

SQL语言的分类

DQL（数据查询语言）

select关键字组合顺序
格式
select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...
PS：以上关键字对顺序不能颠倒！
执行关键字顺序

from

where

group by

having

select

order by

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为一页多少条记录。

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

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

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

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 二进制大对象（存储图片、声音、视频流媒体数据）

数据类型	解释
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 ;

posted on 2022-09-21 11:47 GottenZZP 阅读(46) 评论(0) 编辑收藏举报

搜索

常用链接

我的标签

随笔分类

随笔档案

阅读排行榜

MySQL指令

SQL语言的分类

DQL（数据查询语言）

select关键字组合顺序

select简单查询用法

select条件查询

select函数使用

select多表连接查询

select子查询

Union结果集合并

Limit分页显示

DDL语句

表的创建

DML语句

插入表中数据

常用命令