SQL结构化查询语言

SQL是在关系型数据库上执行的数据操作，检索及维护所使用的标准语言。
定义数据（DDL），操纵数据（DML），控制数据（DCL），查询数据（DQL），事务控制（TCL）

一、DDl数据定义语言

1.操作数据库的DDL

三个关键字：create，alter，drop

-- 创建数据库
create database mybase1;
-- 创建带字符集的数据库
create database mybase2 character set utf8;
-- 查看所有数据库（一定要加s）
show databases;
-- 查看当前正在使用数据库
select database();
-- 修改数据库编码
alter database mybase1 character set utf8;
-- 删除数据库
drop database [if exists] mybase1;
-- 切换数据库
use mybase2;

2.操作表的DDL

-- 创建表(创建表每一句都要加一个逗号,最后一个不带逗号)
create table [if not exists] exam(
	//列名  数据类型 主键    自动增长      不为空
	id int(11) primary key auto_increment not null,
	//default是默认值
	name varchar(20) default '小花',
	//tinyint为1位短整型null为允许为空
	sunscore tinyint(1) null,
	//blob为图片类型
	photo blob
)
-- 查看所有表
show tables;
-- 显示描述表里面的内容
desc exam;
-- 删除表
drop table [if exists] exam; 
-- 修改表的字符集
alter table exam character set gbk;
-- 修改表名
rename table exam to exam1;
-- 添加列(名为sex的字段)
alter table exam add [column] sex int not null;
-- 修改列类型
alter table exam1 modify sex varchar(2);
-- 修改列名
alter table exam1 change sex xingbie varchar(3);
-- 删除列
alter table exam1 drop xingbie;
-- 复制表（只复制表的缩影和结构）

二、DML数据操纵语言

小练习

-- 插入记录
	-- 插入一条记录的部分列
	insert into exam (id,name,english,chinese,math) value(2,'张三',90,90,90);
	insert into exam (id,name,english,chinese) value(null,'张三',90,90);
	-- 插入一条记录的全部列
	insert into exam values(null,'李四',90,90,100);
	-- 插入图片,图片最好放在mysql的目录下，以免发生权限问题
	create table photo(
		stuphoto bolb
	);
	insert into photo('E:\mysql\Install\1.PNG');
-- 修改记录
	-- 修改全部记录
	update exam set name='王五',math=80;
	--修改id=5的记录
	update exam set name='孙刘',math=89 where id=5;
-- 删除记录
	-- 删除全部记录
	delete from exam;
	-- 删除部分记录
	delete from exam where id=5;

drop,delete,truncate在表操作上的区别
①.delete from exam;-- 删除表的所有内容,但不清空auto_incremenet,下次再添会把原来的自增1
②.truncate table exam;-- 删除表的所有内容,并清空auto_incremenet
③.drop table exam;-- 删除整张表

三、DCL数据控制语言

1.使用HeidiSQL设置权限
可以使用管理用户认证和权限来添加删除用户,以及设置添加删除等权限.
2.使用命令行来设置权限
win+R打开cmd

小练习

登录:先进入到mysql的二级命令提示符下面
C:\Users\asus>mysql -uroot -proot123
退出登录:
mysql> exit;
创建用户:密码必须使用字符串，不可以不加双引号
mysql> create user apple@localhost identified by 'root123';
给用户授权:（只能在root用户下给其他用户授权）
//给apple@localhost授权mysql数据库下的所有的表的select与drop权限
mysql> grant select,drop on mysql.*to apple@localhost;
撤销权限://本用户不能撤销自己的权限,需要用其他用户来撤销
mysql> revoke drop on mysql.* from apple@localhost;
用apple用户登录
C:\Users\asus>mysql -uapple -proot123;
测试刚刚授权的select权限能不能使用
use mybase;
mysql> select * from exam;
查看表结构:
mysql> desc exam;
删除表全部记录:
mysql> delete from exam;//没有赋予此权限就不能使用
显示表的所有权限:
mysql> show grants for root@localhost;
删除用户:
mysql> drop user apple@localhost;

四、DQL数据查询语言

1.全表查询
– 全表查询: select * from表;
– 查询部分字段: select字段,字段,字段.from表;
– 过滤重复字段行: select [distinct] 列名,列名 from 表名;
– 查询字段起别名: select字段 as新字段名,字段 新字段名from表;
小练习

create table exam(
		id int(11) primary key auto_increment,
		name varchar(20),
		english int,
		chinese int
)
insert into exam values (null, '张三',85,74);
insert into exam values (null,'李四',95,90);
insert into exam values (null, '王五',89,84);
-- 查询所有记录:
	select * from exam;
-- 查询这个班级人的姓名和英语成绩:
	select name,english from exam;
-- 查询英语成绩,将重复英语成绩去掉:
	select distinct english from exam;
-- 显示这个人的名称和对应总成绩的分数:(使用as起别名,as可以省略)
	select id,name,english+chinese+math as Allscore from exam;

2.条件查询
– where语句后面可以加条件限制,条件关键字: =,>,>=, <, <=, <>
– 模糊查询like+通配符:_下划线(前面只能有一个字符),%匹配(前面可有任意多个字符)
– 使用and,or
– 使用in,not in
– between…and…(数值类型和日期类型)
– is null,is not null:空值NULL比较的时候不能使用" ="号,必须使用is null
– 查询或条件中使用表达式:当查询需要对选出的字段进行进一步计算,可以在数字列上使用算术表达式(+,-,*,/)
小练习

create table emp(
		empno int primary key auto_increment,
		ename varchar(20),
		job varchar(20),
		mgr int,
		hiredate date,
		sal double(7,2),
		comm double(5,2),
		deptno int not null
)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(1002, '白展堂','manger',1001, '1983-05-09', 7000.00 ,200.00,10),
(1003, '李大嘴','clerk' ,1002, '1980-07-08', 8000.00 ,100.00,10), 
(1004, '吕秀才','clerk' ,1002, '1985-11-12', 4000.00 ,NULL,  10), 
(1005, '郭美蓉','clerk' ,1002, '1985-03-04', 4000.00 ,NULL,  10), 
(2001, '胡一菲','leade' ,NULL, '1994-03-04', 15000.00,NULL,  20),
(2002, '陈美嘉','mange' ,2001, '1993-05-24', 10000.00,300.00,20),
(2003, '吕子乔','clerk' ,2002, '1995-05-19', 7300.00 ,100.00,20),
(2004, '张益达','clerk' ,2002, '1994-10-12', 8000.00 ,500.00,20),
(2005, '曾小贤','clerk' ,2002, '1993-05-10', 9000.08 ,700.00,20),
(3001, '刘晓梅','leade' ,NULL, '1968-08-08', 13000.00,NULL  ,30),
(3002, '夏冬梅','mange' ,3001, '1968-09-21', 10000.00,600.00,30),
(3003, '夏雪儿','clerk' ,3002, '1989-09-21', 8000.00 ,300.00,30);
-- 查询职员表中薪水低于10000元的职员信息
	select * from emp where sal<10000;
-- 查询职员表中不属于部门10的员工信息(!=等价于<>)
	select * from emp where deptno<>10;
-- 查询职员表中在1993年05月10号入职的职员信息,比较日期类型数据
	select * from emp where hiredate in('1993-05-10');
-- 查询薪水大于5000并且职位是clerk的职员信息
	select * from emp where sal>5000 and job='clerk';
-- 查询薪水大于5000或者职位是' K'结尾的职员信息
	select * from emp where sal>5000 or job like '%k';
-- 查询职位是leader或者manager的员工
	select * from emp where job in('leade','mange');
-- 查询不是部门10或20的员工
	select * from emp where deptno not in(10,20);
-- 查询薪水在5000-10000之间的职员信息
	select * from emp where sal between 5000 and 10000;
-- 查询哪些职员的奖金数据为NULL
	select * from emp where comm is null;
-- 查询年薪高于100000的职员信息
	-- sql语句执行,先是from,再是where,最后才是查询select
	select empno,ename,job,sal*12 as yearsal from emp where sal*12>100000;

3.排序查询
一列排序:select *from 表名 [where子句] order by字段[asc desc]
多列排序:select *from 表名 [where子句] order by字段[asc|desc] ,字段[asc]desc]…
注意
asc是升序(默认),desc是降序
order by必须出现在select中的最后一个子句,不能放在where之前
多列排序首先按照第一列进行排序,第一列数据相同,再以第二列排序，以此类推
多列排序时,不管正序还是倒序,每个列需要单独设置排序方式
小练习

-- 查询所有学生的信息,并且按语文成绩进行排序.
		select * from exam order by chinese desc;-- 降序
		select * from exam order by chinese asc; -- 升序
		select * from exam order by chinese;     -- 不写默认是升序
-- 查询学生的信息,按照英语成绩降序排序,如果英语成绩相同按照语文降序.
		select * from exam order by english desc,chinese desc;

4.聚合函数
查询时多行数据参与运算返回一行结果,也称作分组函数、多行函数、集合函数
– sum() :求和
– count() :做统计
– max() :求最大
– min() :求最小
– avg() :求平均
– 对空值的处理:
– IFNULL(exprl,expr2) :如果expr1不是null,则直接返回expr1的值,否则返回expr2
小练习

-- 查询每个学生总成绩:
-- 如果不加id的话和计算的是全部人员的总和
	select sum(chinese+english) from exam where id=2;
-- 统计学生的个数:
	select count(*) from exam;
-- 统计英语成绩的最高分:
	select max(english) from exam;
-- 统计语文成绩的最低分:
	select min(chinese) from exam;
-- 统计英语成绩平均分:
	select avg(english) from exam;

5.分组查询
– group by子句
– 对结果集按照给定字段值相同的记录进行分组
– 配合聚合函数使用
– by后面有什么字段,查询时就可以保留什么字段,这样就比较有意义
– having子句
– 用来对分组后的结果进一步限制
– 查询语句执行顺序
– 5select 1from 2where 3group by 4having 6order by
– where与having的区别
– where过滤记录,having过滤分组
– 过滤时机不同,先where后having
– where是在查询表时逐行过滤以选取满足条件的记录
– having是在数据查询后并且分完组后对分组进行过滤的,以决定分组的取舍
– having必须跟在group by后面
小练习

-- 查询每个职位的最高工资和最低工资?
	select job,max(sal),min(sal) from emp group by job;
-- 查询每个部门每种职位的最高工资?
	select deptno,job,max(sal) from emp group by deptno,job;
-- 查询每个部门的最高薪水,只有最高薪水大于15000的记录才被输出显示?
-- 过滤分组用having
	select deptno,max(sal) as maxsal from emp group by deptno having maxsal>=10000;
-- 查询每个部门平均工资,前提是该部门的平均工资高于9000 
	select deptno,avg(sal) as avgsal from emp group by deptno having avgsal>=9000;

6.分页查询
– 知识点
– 分页查询使用的是limit关键字进行查询。
– 第一个参数:起始位置,第二个参数:每页需要显示的条目数
– 按页查询取值为: (需要查看第几页-1)乘以第二个参数
小练习

-- 展示前三条职员信息:（从第0条开始，展示三条）
	select * from emp limit 0,3;
-- 展示3,4,5,6四条职员信息
	select * from emp limit 3,4;
-- 展示第三页,每一页都有5条,查询第三页,第一个参数为(3-1)*5
	select * from emp limit 10,5;
-- 展示第二页,每一页都有2条,查询第二页,第一个参数为(2-1)*2
	select * from emp limit 2,2;
-- 查看工资最高的前十个职员信息:
   select * from emp order by sal desc limit 0,10;

9.多表查询（要参照下面补充的约束内容）
知识点
– 笛卡尔积: select * from A,B;](将主表的记录都拼接到从表中)
– 等值连接: select * from A,B where A.主键=B.外键;
– 内连接: select * from A inner join B on A.主键=B.外键;(inner可以省略)
– 左外连接: select * from A left outer join B on条件;
(左边的表全存在,右边的如果匹配不上就用null来连接)
– 右外连接: select * from A right outer join B on条件
– 子查询结果若为单行单列: select * from 表名 where 字段 =,>,<… (子查询)
– 子查询结果若为多行单列: select * from 表名 where 字段 in/not in (子查询)
– 自连接:自己与自己连接
(on比where起作用更早,先根据on条件进行多表的连接操作，生成一个临时表再通过where来筛选)
小练习

-- 等值查询/内连接练习(等值连接和内连接的结果相同)
	-- 查看每个员工的名字以及其所在部门的名字?//先执行from,在执行select,再执行where
		select e.ename,d.dname,d.loc from emp e ,dept d where e.deptno=d.deptno;
	-- 查看工作地点在北京的员工有哪些?
		select * from emp inner join dept on emp.deptno=dept.deptno where dept.loc='北京';
-- 外连接练习
	-- 查看每个城市员工的平均工资?]//ifnull(avg(sal),0)代表如果结果是空,显示0,如果不是显示avg(sal)
		select dept.loc,ifnull(avg(sal),0) from emp right join dept on emp.deptno=dept.deptno group by dept.loc; 
	-- 查看工作地点在上海的员工有哪些?
		select emp.*,dept.dname,dept.loc from emp left outer join dept on emp.deptno=dept.deptno where dept.loc='上海';
-- 子查询练习
	-- 查找和曾小贤同职位的员工?
		select * from emp where job=(select job from emp where ename='曾小贤');
	-- 查找薪水比整个机构平均薪水高的员工?
		select * from emp where sal>(select avg(sal) from emp);
	-- 查询出部门中有CLERK但职位不是CLERK的员1的信息?
		select * from emp where deptno in(select distinct deptno from emp where job='clerk') and job<>'clerk';
	-- 查看每个城市员工的平均工资?
		select dept.loc,avg(sal) from emp inner join dept on emp.deptno=dept.deptno group by dept.loc;
	-- 查询列出最低薪水高于部门20的最低薪水的部门信息?(子查询在having子句中)
		select deptno,min(sal) as minsal from emp group by deptno having minsal>(select min(sal) from emp where deptno=20);
-- 自连接练习
	-- 列出所有员工的姓名及其直接上级的姓名?
		select e1.ename as emploee,e2.ename as boss from emp e1 left join emp e2 on e1.mgr=e2.empno;

五、约束

1.单表约束
– 主键约束: primary key (默认就是唯一非空的)
– 唯一约束: unique
– 非空约束:not null
– 设置方法
–1.直接在表里面创建
create table exam(
id int(11) primary key,
name varchar(20) unique,
chinese int not null
)
–2.表创建好了之后,unique not null,中间不用加逗号
alter table exam modify name varchar(21) unique not null;
2.外键约束
– 如果表A的主键是表B中的字段,则该字段称为表B的外键,表A称为主表,表B称为从表
– 主表设置主键:alter table 主表 modify 字段(长度) primary key;
– 建立外键约束:alter table 从表 add foreign key (从表外键字段) references 主表(主键字段);
注意
– 从表任何记录都可以随便删,主表只能删除没有从表依赖的项
– 添加外键约束:前提主表一定要设置主键
小练习

create table dept(
		deptno int primary key,
		dname varchar(21),
		loc varchar(21)
	)
	insert into dept values(10,'餐饮部','上海');
	insert into dept values(20,'销售部','浙江');
	insert into dept values(30,'财务部','北京');
	insert into dept values(40,'技术部','深圳');
	-- 修改列的约束类型时必须加上属性的数据类型
	alter table dept modify deptno int primary key;
	alter table emp add foreign key (deptno) references dept(deptno);