浅谈SQL语句与QA的情节

一、关系型数据库概念

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

二、常见的数据库系统

Oracle 甲骨文
DB2 IBM出品的一些列关系型数据库
Mysql 开源数据库软件
SQL Server 微软
Redis 最好用的缓存数据库

三、MySQL

3.1、数据库服务器、数据库和表的关系

所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
数据库服务器、数据库和表的关系如图所示：

3.2、数据在数据库中的存储方式

3.3、SQL语言

Structured Query Language, 结构化查询语言
SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能

3.4、SQL分类

3.4.1、DDL（数据定义语句）

数据定义语言 - Data Definition Language
用来定义数据库对象：库、表、列等；创建、删除、修改：库、表结构

3.4.1.1、数据库创建和操作

create database  数据库名;  创建数据库       
show databases;  展示数据库
show create database; 展示数据库的创建细节    
select database(); 查看当前数据库  
alter database character set 字符集  修改数据库
drop database 数据库名;    删除数据库    

3.4.1.2、表的创建

CREATE TABLE table_name  
(  
    field1  datatype,  
    field2  datatype,  
    field3  datatype  
); 
field：指定列名　datatype：指定列类型  

3.4.1.3、数据类型

int：整型  
float/double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99最小值是0.01；  
decimal：浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题；对十进制运算比较精确的类型  
char：固定长度字符串类型； char(255)，数据的长度不足指定长度，补足到指定长度！  
sex Char(2)       男   
varchar：可变长度字符串类型；    varchar(65535), zhangSan  
name varchar(10)    张三  
text(clob)：字符串类型；  
blob：字节类型；  
date：日期类型，格式为：yyyy-MM-dd；  
time：时间类型，格式为：hh:mm:ss  
timestamp：时间戳类型；若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

3.4.1.4、表的修改删除查看操作

alter table 表名 add 列名 列的类型 列的约束  
alter table 表名 modify 列名 列的类型 列的约束   
show tables ; 查看当前数据库中所有的表名  
show create table 表名: 查看表的定义结构/创建语句  
desc 表名 : 查看表的结构
drop table 表名  

3.4.1.4.1、创建表案例

1、创建一个员工表employee ---- 查看表结构: desc 表名;

CREATE TABLE employee (
	id INT ,
	NAME VARCHAR (20),
	gender VARCHAR (20),
	birthday date,
	entry_date date,
	job VARCHAR (20),
	salary FLOAT (10, 2),
	resume text
);

2、创建一个商品表 product 有pid,price价格,shelves_date 上架日期 shelves_time上架时间，tamp时间戳

CREATE TABLE product (
	pid INT,
	price FLOAT (9, 2),
	shelves_date date,
	shelves_time time,
	tamp TIMESTAMP
);

3.4.1.5、单表字段约束

定义主键约束
primary key:不允许为空，不允许重复
删除主键：alter table tablename drop primary key ;
主键自动增长 ：auto_increment
定义唯一约束
unique
例如：name varchar(20) unique
定义非空约束
not null
例如：salary double not null

3.4.2、DML（数据操纵语句）

数据处理语言 - Data Manipulation Language
在数据库表中更新，增加和删除记录
如 update， insert， delete — 增删改

3.4.2.1、INSERT语句

插入的数据应与字段的数据类型相同。
数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
插入空值：不指定或insert into table value(null)

1.方式一:INSERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);  
2.方式二:INSERT INTO 表名(列名1,列名3) VALUES(列值1, 列值3);  
3.方式三:INTERT INTO 表名 VALUES(列值1, 列值2)

3.4.2.1.1、使用insert语句向表中插入三个员工的信息

方式一:
insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values(1,"张三","男","1999-10-10","2020-12-20","文员",4000.01,"来自山东");
方式二:
insert into employee  values(2,"李四","男","1998-10-10","2020-12-30","会计",4500.01,"来自北京");
方式三:-多条插入
insert into employee  values(3,"王五","男","1998-10-10","2020-12-30","会计",4500.01,"来自北京"),
(4,"赵六","男","1998-10-10","2020-12-30","会计",4500.01,"来自北京"),
(5,"小花","女","1998-10-10","2020-12-30","出纳",4500.01,"来自上海");

3.4.2.2、mysql中文乱码

到my.ini文件中或者控制台修改一下信息即可
查看变量语句:

show variables like ‘char%’;

设置变量的语句：

> set character_set_client=gbk;    客户端
> set character_set_results=gbk;   结果【复数】

3.4.2.3、UPDATE语句

UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件] 

UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

3.4.2.3.1、案例

-- 将所有员工薪水修改为5000元。
update employee set salary=5000;
-- 将姓名为张三的员工薪水修改为3000元。
update employee set salary=3000 where name="张三";
-- 将姓名为李四的员工薪水修改为4000元,job改为ccc。
update employee set salary=4000,job="ccc" where name="李四";
-- 将王五的薪水在原有基础上增加1000元。	
update employee set salary=salary+1000 where name="王五";

3.4.2.4、DELETE语句

DELETE FROM 表名 [WHERE 条件];  

如果不使用where子句，将删除表中所有数据。
delete语句不能删除某一列的值（可使用update）
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。

• delete可以加条件 truncate是删除整张表
• delete支持回滚 truncate不支持
• delete清理速度慢 trucate快

3.4.2.4.1、案例

删除表中名称为张三的记录。
delete from employee where name="张三";
删除表中所有记录。
delete from employee;
使用truncate删除表中记录。
truncate employee;

3.4.3、DQL（数据查询语句）

数据查询语言 – Data Query Language
select

3.4.3、SELECT语句

3.4.3.1、简单查询

SELECT * FROM 表名;    {其中“*”表示查询所有列}  

3.4.3.2、去重查询

关键字distinct

select distinct job from employee;

3.4.3.3、指定列查询

SELECT 列1 [, 列2, ... 列N] FROM 表名;
select name,job,salary from employee;

3.4.3.4、列运算

数据类型的列可以做加、减、乘、除运算  
给员工涨工资【倍数1.5】：
select salary*1.5 from employee;
工资加奖金 ：select *,salary+bonus from employee;  
当数字碰到null的时候整个就会变成null  
select *,salary*12+ifnull(bonus,0) from employee;
给列起别名  
查询出的结果集中的列名称不好看，可以给列名起个别名，       
select *,salary*12+ifnull(bonus,0) 年薪 from employee;

3.4.3.5、模糊查询

关键字：like
_ 表示的单个字符
% 表示的是多个字符

select * from employee where name like "小_";
select * from employee where name like "小%";
select * from employee where name like "%花%";

3.4.3.6、关系运算符

<> 不等于
!= 不等于

select * from employee where job <> 3000;
select * from employee where job != 3000;

3.4.3.7、逻辑运算符

and or 

select * from employee where job="会计" and salary>3000;
select * from employee where gender="女" or salary>9000;

3.4.3.8、limit分页查询

Limit 开始索引，长度
limit m,n 第一页:limit 0,10 (下标从0开始,10表示加载10条数据) 第二页:limit 10,10 第三页: limit 20,10

分页加载 每页展示4条数据
* 第一页 select * from employee limit 0,4;
* 第二页 select * from employee limit 4,4;
* 第三页 select * from employee limit 8,4;

select * from employee limit 0,3;   从第一个开始，取三个
select * from employee limit 3,2;   从第4个开始，取两个

3.4.3.9、between … and …

查询薪资是6000到9000之间的员工

select * from employee where salary between 6000 and 9000;

3.4.3.10、in 在范围中

查询名字是小王/张/赵的员工

select * from employee where name in ("小张","小王","小赵");

3.4.3.11、order by 排序

1.ASC 【升序排序】
2.DESC 【降序排序】

select * from employee order by birthday asc;  按照出生时间排序(数字从小到大)
select * from employee order by birthday desc;按照出生时间倒序(数字从大到小)
select * from employee order by salary desc,birthday desc;(多条件排序)
select * from employee order by salary desc limit 0,3; 查询salary 最大的三名

3.4.4、DCL（数据控制语句）

数据控制语言 – Data Control Language
指用于设置用户权限和控制事务语句
如grant，revoke，if…else，while，begin transaction

3.4.5、聚合函数

3.4.5.1、count()

count(列名)返回某一列，行的总数

select count(*)|count(列名) from 表名 [WHERE 条件]   

3.4.5.1.1、练习

-- 统计一个班级共有多少学生？
select count(*) from student;
-- 统计数学成绩大于90的学生有多少个？
select count(*) from student where math>90;
-- 统计总分大于250的人数有多少？
select count(*) from student where (math+english+chinese)>250;

3.4.5.2、sum()

select sum(列名)｛,sum(列名)…｝ from 表名 [WHERE 条件]   

3.4.5.2.1、练习

注意：sum仅对数值起作用，否则会报错。
注意：对多列求和，“，”号不能少。

-- 统计一个班级数学总成绩？
select sum(math) 数学总成绩 from student;
-- 统计一个班级语文、英语、数学各科的总成绩
select sum(chinese),sum(english),sum(math) from student;
-- 统计一个班级语文、英语、数学的成绩总和
select sum(chinese+english+math) from student;

3.4.5.3、avg()

AVG函数返回满足where条件的一列的平均值

select avg(列名)｛,avg(列名)…｝ from 表名 [WHERE where_definition]

3.4.5.3.1、练习：

-- 统计一个班级语文成绩平均分
select avg(chinese) from student;
-- 求一个班级数学平均分？
select avg(math) from student;
-- 求一个班级总分平均分
select avg(chinese+math+english) from student;

3.4.5.4、max()、min()

max/min函数返回满足where条件的一列的最大/最小值

Select max(列名)　from tablename [WHERE 条件]   

3.4.5.4.1、练习：

– 求班级数学最高分和最低分

select max(math),min(math) from student;
-- 求班级总分最高分和最低分
select max(math+english+chinese) from student;
-- 求数学最高分的同学
select * from student where math=(select max(math) from student);
select * from student order by math desc limit 0,1;(本条语句结果不准确 最高分相同 只能查出一条数据)
-- 求班级总分最高的同学
select * from student where (chinese+english+math)=(select max(chinese+math+english) from student );
select * from student order by (chinese+english+math) desc limit 0,1;(本条语句结果不准确 最高分相同 只能查出一条数据)

3.4.6、分组查询

分组原理：按照分组的列名，将该列中数据相同的部分分到一组,然后对每一组中的数据进行计算。 【分组查询不是用来查询个人信息的】

select  列名 from 表名 where  限定条件 group by 分组的列 [having  分组之后的筛选条件]  

having和where均可实现过滤，但在having可以使用聚集函数,having通常跟在group by后，它作用于分组

create table orders(
	id int,
	product varchar(20),
	price float
);

insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);

练习：

-- 对订单表中商品归类后，显示每一类商品的总价
select product, sum(price) from orders group by product;
-- 查询购买的商品类别，并且每类总价大于100的商品
select product, sum(price) from orders group by product having sum(price)>100;
-- 查询每一类商品买的个数
select product,count(*) from orders group by product;
-- 查询出购买个数大于1的商品
select product,count(*) from orders group by product having count(*)>1;

3.4.7、多表设计（关系）

3.4.7.1、一对一

create table user (uid int primary key auto_increment,uname varchar(20),idcard varchar(30));
create table user_detail(idcard varchar(20) primary key,address varchar(20),age int,salary float,gender varchar(20));
insert into user values(0,"张三","37119199010081010");
insert into user values(0,"李四","37119199010081011");
insert into user values(0,"小红","37119199010081012");
insert into user values(0,"小花","37119199010081013");

insert into user_detail values("37119199010081010","北京",28,12000.01,"男");
insert into user_detail values("37119199010081011","上海",40,13000.01,"男");
insert into user_detail values("37119199010081012","北京",40,13000.01,"女");
insert into user_detail values("37119199010081013","上海",40,20000.01,"女");

#查询每个用户的所有信息  
select uid,uname,u.idcard,address,age,salary,gender from user u,user_detail ud where u.idcard=ud.idcard;
#查询张三的所有信息  
select uid,uname,u.idcard,address,age,salary,gender from user u,user_detail ud where u.idcard=ud.idcard and uname="张三"; 
#查询北京的用户  
select uid,uname,u.idcard,address,age,salary,gender from user u,user_detail ud where u.idcard=ud.idcard and ud.address="北京";

3.4.7.2、一对多

一对多和多对一是一回事，所以就不再提多对一这个词。一对多的概念是一个对象A会对应多个对象B，而从B的角度看，一个对象B只会对应一个对象A。比如说班级和学生就是一对多关系。一个班级对应多个学生，一个学生只会对于一个班级。

部门表和员工表

定义外键约束
constraint foreign key (did) references department(did)
新建部门表department (id,name)
通过外键约束建立与员工表employee关系

create table department(did int primary key,name varchar(100));
create table employee(eid int primary key auto_increment,name varchar(20),salary double(6,2),did int,constraint fk_id foreign key (did) references department(did));

insert into department values(1,'人事部');
insert into department values(2,'教学部');
insert into department values(3,'会计部');
insert into employee values(1,'张三',1000.01,1);
insert into employee values(2,'李四',1000.03,1);
insert into employee values(3,'王五',1000.02,2);
insert into employee values(4,'赵六',1000.03,3);
insert into employee values(5,'周七',1.01,3);

-- 查询出人事部所有的员工信息
-- 写法一:子查询
select * from emp where did=(select did from dept where dname="人事部");‘
-- 写法二:内连接
select * from dept d,emp e where d.did=e.did and d.dname="人事部";
-- 查询出张三所在的部门
select * from dept where did=(select did from emp where ename="张三");
select * from dept d,emp e where d.did=e.did and e.ename="张三";
-- 查询出每个部门的总人数
select d.dname,count(*) from dept d,emp e where d.did=e.did group by e.did;
-- 查询出人事部的总人数
select d.dname,count(*) from dept d,emp e where d.did=e.did group by e.did having d.dname="人事部";
-- 查询出每个部门的总工资
select d.dname,sum(salary) from dept d,emp e where d.did=e.did group by e.did;
-- 查询出每个部门的平均工资
select d.dname,avg(salary) from dept d,emp e where d.did=e.did group by e.did;

3.4.7.3、多对多

create table student(sid int primary key auto_increment,sname varchar(20));
create table teacher(tid int primary key auto_increment,tname varchar(20),tsubject varchar(20));
create table st(sid int,tid int,constraint fk_sid foreign key (sid) references student(sid),constraint fk_tid foreign key (tid) references teacher(tid));

insert into student values(1,"张三");
insert into student values(2,"李四");
insert into student values(3,"王五");
insert into student values(4,"赵六");

insert into teacher values(1,"王老师","数学");
insert into teacher values(2,"李老师","英语");
insert into teacher values(3,"赵老师","语文");
insert into teacher values(4,"刘老师","物理");

insert into st values(1,1);
insert into st values(1,2);
insert into st values(2,3);
insert into st values(3,1);
insert into st values(3,2);
insert into st values(3,4);

#查询出王老师教过的所有学生  
select * from student s,st,teacher t where s.sid=st.sid and t.tid=st.tid and t.tname="王老师";
-- 查询出教过张三的所有老师  
select * from student s,st,teacher t where s.sid=st.sid and t.tid=st.tid and s.sname="张三";
-- 查询出每个老师所教学生数量  
select tname,count(*) from st,teacher t where st.tid=t.tid group by st.tid;
-- 查询出王老师所教学生数量
select tname,count(*) from st,teacher t where st.tid=t.tid group by st.tid having t.tname="王老师";

3.4.8、连接查询

3.4.8.1、笛卡尔积查询

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y。对于数据库的笛卡尔积查询，指的是两张表查询的时候，每一行数据进行连接查询。 关键词是cross join，也可以将两张表串连起来省略不写。

select * from customer cross join orders;
select * from customer,orders;

3.4.8.2、内连接

内连接是A表的所有行交上B表的所有行得出的结果集,返回两张表满足条件的所有数据

select * from customer c inner join orders o on c.cid=o.cid; 
select * from customer c,orders o where c.cid=o.cid;

3.4.8.3、左外连接

左外连接是A表的所有行匹配上B表得出的结果集,返回满足条件的所有记录，同时返回左表中剩余的其他记录

 select * from customer c left join orders o on c.cid=o.cid;

3.4.8.4、右外连接

右外连接是B表的所有行匹配上A表得出的结果集,返回满足条件的所有记录，同时返回右表中剩余的其他记录

select * from customer c right join orders o on c.cid=o.cid;

3.4.8.5、内外连接的区别

内连接返回两张表满足条件的所有数据
外连接除了返回两张表满足条件的所有数据,还会返回左表或者右表不满足条件的数据

3.4.8.6、左外连接和右外连接的区别

左外连:返回满足条件的所有记录，同时返回左表中剩余的其他记录
右外连:返回满足条件的所有记录，同时返回右表中剩余的其他记录

3.4.9、存储过程

带有业务逻辑的sql语句。里面有流程控制语句 if条件判断，while循环等等
特点
1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！
2）移植性很差！不同数据库的存储过程是不能移植。
创建存储过程 :create procedure 存储过程名字 (参数)

create PROCEDURE delete_emp(IN empno INTEGER)
begin 
delete from emp where empno = empno;
end

调用存储过程 call 存储过程名字(参数)
删除存储过程 drop procedure 存储过程名字
定义存储过程的时候，参数定义：
IN：表示输入参数，可以携带数据到存储过程中

delimiter告诉mysql解释器,介绍符号是什么????默认是;
delimiter $$ 
create procedure select_student()
begin
select * from student;
end$$
将结束符号修改为;
delimiter ;


delimiter $$ 
create procedure select_student(IN i INTEGER)
begin
select * from student where id=i;
end$$
将结束符号修改为;
delimiter ;

调用：
Call select_student();   

3.4.10、事务

作为单个逻辑工作单元执行的一系列操作,由多条语句组成的集合,要么全部成功,要么全部失败,数据库通过事务来保证数据的一致性
事物广泛的运用于订单系统，银行系统等多种场景中。
举例：银行转账操作
执行演示：
Begin； 【开启事务】
sql语句
commit； 【都成功 手动提交】
rollback； 【任何一部分失败了 回滚 返回最初状态】

create table account(id int primary key auto_increment,name varchar(20),money float);
insert into account values(0,"zhangsan",10000);
Insert into account values(0,"lisi",20000);

正常提交演示

begin;
update account set money=money-1000 where name="zhangsan";
update account set money=money+1000 where name="lisi";
commit;

回滚场景

begin;
update account set money=money-1000 where name="zhangsan";
update account set money=money+1000 where name="lisi";
rollback;

3.4.11、备份、恢复数据库

备份数据库表中的数据  
cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql  
例如: mysqldump -u root -p day12 > c:\day12.sql  
  
恢复数据库  
source 文件名.sql   // 在mysql内部使用  
mysql –u 用户名 p 数据库名 < 文件名.sql  // 在cmd下使用