mysql基本操作2
##DDL控制表结构,不支持事务
##DML控制表数据,支持事务
DQL专门做查询
##TCL 管理事务
##DCL 管理数据库权限
##DML控制表数据,支持事务
DQL专门做查询
##TCL 管理事务
##DCL 管理数据库权限
##ORDER BY 子句
-根据指定列对结果集进行排序
-默认升序
-asc升序,desc 降序
order by 语句必须写在where语句之后
select title,price,category_id from t_item where title like '%DELL%' order by category_id asc,price desc; select price,category_id from t_item order by category_id asc,price desc;
##limit 子句--分页
-limit begin ,size 两个数字 逗号分隔
-begin 本页数据的起始行,从0开始(从第几条数据开始)
-size 本页显示几行
-begin 本页数据的起始行,从0开始(从第几条数据开始)
-size 本页显示几行
select price from t_item order by price limit 0,5;
#数据库函数
很多函数功能与java相关的api类似,但是,除了几种特殊的需求外,我们开发时,尽量不要在数据库进行函数计算
##CONCAT()函数 拼接字符串
concat(str1,str2....)
-如果任何一个str是null,返回值就是null
concat(str1,str2....)
-如果任何一个str是null,返回值就是null
select concat('今天周五了','下周讲完数据库') as 诡异 from dual; dual 任何数据库都存在,是一张假表 select 'helloword' from dual; select concat (ENAME,MGR) from EMP; select concat (title,price,'元') from t_item;
##数值运算
-运算符 + - * / % mod()取模
-弱数据类型 字符串与数字进行计算 ‘10’-5
-取余 7%2 等同于 mod(7,2)
select price 单价,num 库存,price*num aaa from t_item order by aaa;
##DATE_FORMAT() 日期函数
-now() 返回当前的日期和时间
-curdate() 返回当前的日期
-curtime() 返回当前的时间
-date(时间日期的表达式) 提取这个表达式日期的部分
-time(时间日期表达式)提取这个表达式时间的部分
-extract(时间日期表达式) 提取这个表达式单独的一部分 (年月日时分秒)
-弱数据类型 字符串与数字进行计算 ‘10’-5
-取余 7%2 等同于 mod(7,2)
select price 单价,num 库存,price*num aaa from t_item order by aaa;
##DATE_FORMAT() 日期函数
-now() 返回当前的日期和时间
-curdate() 返回当前的日期
-curtime() 返回当前的时间
-date(时间日期的表达式) 提取这个表达式日期的部分
-time(时间日期表达式)提取这个表达式时间的部分
-extract(时间日期表达式) 提取这个表达式单独的一部分 (年月日时分秒)
select now() from dual; select curdate(); select date(now()); select curdate(); mysql> select curdate() from dual;| +------------+ | 2018-01-26 | +------------+ mysql> select date(now()); +-------------+ | date(now()) | +-------------+ | 2018-01-26 | +-------------+ select extract(year from now()); +--------------------------+ | extract(year from now()) | +--------------------------+ | 2018 | +--------------------------+
##date_format()
-以不同的格式显示时间/日期数据
-语法 date_format(日期表达式,'format');
-以不同的格式显示时间/日期数据
-语法 date_format(日期表达式,'format');
-format
--%c 月 1
--%d 月的天 (00-31)
--%H 小时 (00-23)
--%h 小时(01-12)
--%i 分钟
--%m 月 01
--%S 秒
--%Y 年
--%y 两位数的年
-把字符串格式转换成时间格式,第一个参数为字符串,第二个参数为与第一个参数一样的format字符。格式不同结果为null。
-空值处理函数
-ifnull(expr1,expr2) 如果expr1不是null,ifnull返回expr1,如果是null,返回expr2
--%c 月 1
--%d 月的天 (00-31)
--%H 小时 (00-23)
--%h 小时(01-12)
--%i 分钟
--%m 月 01
--%S 秒
--%Y 年
--%y 两位数的年
例子:select date_format(now(),'%c--%m'); select date_format(HIREdate,'%Y年%m月%d日') from EMP; select date_format(now(),'%H时%i分%S秒'); select date_format(created_time,'%Y:%m:%d')from t_item;
##str_to_date 把字符串转换成日期格式
-把字符串格式转换成时间格式,第一个参数为字符串,第二个参数为与第一个参数一样的format字符。格式不同结果为null。
select str_to_date('08/09/2008','%m/%d/%Y'); select str_to_date('08/09/08','%m/%d/%y'); select str_to_date('08.09.08','%m.%d.%y'); select str_to_date("08.07.2008 08:00:00","%m.%d.%Y %h:%i:%S");
## IFNULL()的函数
-空值处理函数
-ifnull(expr1,expr2) 如果expr1不是null,ifnull返回expr1,如果是null,返回expr2
select ifnull(COMM,0) from EMP;
##聚合函数
-对多行数据进行合并并统计
-使用聚合函数要注意:聚合函数一般只有一行结果,如果其他要查询的列。有多行结果,那么只会显示一行,其他结果被舍弃,原因是数据库不支持行合并。
-不要把聚合函数和正常的列放在统一个dql语句中,除非普通列只有一条数据
###sum():返回列的总数或总额
select sum(SAL),avg(SAL) from EMP;
###count():返回指定列的总数目,null不计数
select count(COMM) from EMP;
--使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空
值( NULL )还是非空值。
##聚合函数
-对多行数据进行合并并统计
-使用聚合函数要注意:聚合函数一般只有一行结果,如果其他要查询的列。有多行结果,那么只会显示一行,其他结果被舍弃,原因是数据库不支持行合并。
-不要把聚合函数和正常的列放在统一个dql语句中,除非普通列只有一条数据
###sum():返回列的总数或总额
select SAL,COMM,sum(SAL) from EMP; --------+------+----------+ | SAL | COMM | sum(SAL) | +--------+------+----------+ | 800.00 | NULL | 24925.00 | +--------+------+----------+ select sum(SAL) from EMP; ----------+ | sum(SAL) | +----------+ | 24925.00 | +----------+
###avg():返回数值的平均值,但是null不包含在计算中
select sum(SAL),avg(SAL) from EMP;
###count():返回指定列的总数目,null不计数
select count(COMM) from EMP;
--使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空
值( NULL )还是非空值。
###max():这一列的最大值 null不计算
####可以对时间比较
select max(HIREdate) from EMP ;
####可以对时间比较
select max(HIREdate) from EMP ;
###min():这一列的最小值 null不计算
#字符串的函数
-floor() 向下取整
-round() 四舍五入
-round(24.324,2) 四舍五入 取小数位2位
-round(232.3123,-2) 小数点往前走两位 四舍五入 200
-truncate(234234.234,4) 保留小数点后几位 不四舍五入
-char_length() 字符数 select char_length(ENAME) from EMP; -instr('abcdef','bcd) 返回第二个字符串在第一个字符串的位置从1开始 不存在返回0 例如:select ENAME from EMP where instr(ENAME,"K")!=1; select ENAME from EMP where instr(ENAME,"A")=0; -locate('abc','abc---abc---abc---abc') 返回第一个字串在第二个字符串的位置,从1开始 不存在返回0 -insert('aifewfehfff',2,5,'---') 用子串取代第一个字符串的位置 ,从第一个字符串的第2个字符开始用---取代5个长度 -lower() 转换成小写 select lower(ENAME) from EMP; -upper() 转换成大写 -left("safdfw",3) 返回左边3个字符 select left("safdfw",3); -right('ihdwif',4) 返回右边4个字符 -trim(' dsf g gd ') 去除两边的空格 -substring('ewfuifhu',3) 从3开始截取 -substring('ewfuifgdghu',3,5) 从3开始截取5个字符 -repeat('ahsdff',3) 重复3遍 -replace('hello mysql','my','your'); -reverse()-反转字符串 例如:查询倒数的第2个字符是‘E’的员工的姓名:select ENAME from EMP where ENAME like ('%E_'); 例如::查询emp表中员工的倒数第3个字符是: select subString(right(ENAME,3),1,1) from EMP; select substring(ENAME,-3,1) from EMP;
#数学相关函数
-floor() 向下取整
-round() 四舍五入
-round(24.324,2) 四舍五入 取小数位2位
-round(232.3123,-2) 小数点往前走两位 四舍五入 200
-truncate(234234.234,4) 保留小数点后几位 不四舍五入
select truncate(234234.234,4); +------------------------+ | truncate(234234.234,4) | +------------------------+ | 234234.2340 | +------------------------+
#分组
###分组查询通常和聚合函数一起使用,
--一般情况下,查询的字段中出现聚合函数和普通列,一起查询的时候,分组的条件就是普通列
--select语句中含有聚合函数时,凡是不在聚合函数中的其他单独字段,都必须出现在group by语句中
--group by 语句要写在order by之前,where之后
--group by可以根据多个字段进行分组
###分组查询通常和聚合函数一起使用,
--一般情况下,查询的字段中出现聚合函数和普通列,一起查询的时候,分组的条件就是普通列
--select语句中含有聚合函数时,凡是不在聚合函数中的其他单独字段,都必须出现在group by语句中
--group by 语句要写在order by之前,where之后
--group by可以根据多个字段进行分组
select avg(SAL),DEPTNO from EMP group by DEPTNO; 查询每个领导有多少个员工:select MGR,count(*) from EMP group by MGR; select DEPTNO,count(*),sum(sal) from EMP group by DEPTNO order by count(*),sum(SAL) desc; select DEPTNO,JOB,avg(SAL) from EMP group by DEPTNO,JOB; select * from EMP where EMPNO in(7369,7521); 每个部门,每个主管手下的人数:select count(*),DEPTNO,MGR from EMP group by DEPTNO,MGR; select count(*),extract(year from HIREdate) as year from EMP group by year;
##有条件的分组统计
###HAVING 字句
select avg(SAL),DEPTNO
from EMP
group by DEPTNO
having avg(SAL)>2000;
##查询顺序
1.form找到数据源
2.where根据条件进行筛选,确定条目数量
3.group by将数据划分多个组
4.聚合函数运算
5.having字句筛选分组条件
6.计算所有表达式
7.确定select后的字段
8.使用 order by 排序
---group by使用having过滤条件
---在sql语句中添加having字句的原因,是因为where关键字无法与集合函数一起使用。
-where条件用于过滤行数,having条件用于过滤分组的数量
-执行顺序,首先执行where语句,然后执行group by,根据单个或多个列进行分组,之后执行having。对分组以后的数据再次过滤。最后执行order by。
###HAVING 字句
select avg(SAL),DEPTNO
from EMP
group by DEPTNO
having avg(SAL)>2000;
##查询顺序
1.form找到数据源
2.where根据条件进行筛选,确定条目数量
3.group by将数据划分多个组
4.聚合函数运算
5.having字句筛选分组条件
6.计算所有表达式
7.确定select后的字段
8.使用 order by 排序
---group by使用having过滤条件
---在sql语句中添加having字句的原因,是因为where关键字无法与集合函数一起使用。
-where条件用于过滤行数,having条件用于过滤分组的数量
-执行顺序,首先执行where语句,然后执行group by,根据单个或多个列进行分组,之后执行having。对分组以后的数据再次过滤。最后执行order by。
select sum(num),category_id from t_item group by category_id having sum(num)>1000; select avg(price),category_id from t_item group by category_id having avg(price)<=100; select JOB,count(*),avg(SAL),max(SAL) from EMP where DEPTNO in(10,30) group by JOB having max(SAL)<5000 order by count(*),max(SAL) desc; select DEPTNO,count(*),sum(SAL),max(SAL),min(SAL) from EMP group by DEPTNO having max(SAL)!=5000 order by count(*),max(SAL) desc; select DEPTNO,sum(SAL),avg(SAL) from EMP where SAL between 1000 and 3000 group by DEPTNO having avg(SAL)>=2000 order by avg(SAL); select JOB,count(*),sum(SAL),max(SAL) from EMP where JOB not like "S%" and SAL!=3000 group by JOB order by count(*),sum(SAL) desc; select JOB,count(*),avg(SAL),min(SAL) from EMP group by JOB having avg(SAL)!=3000 order by count(*) desc,avg(SAL) asc; select avg(price),category_id from t_item where category_id in(238,917) group by category_id; select avg(price),category_id from t_item group by category_id having category_id in(238,917);
####,having字句经常和聚合函数一起使用,如果没有聚合函数,要注意是否可以写在where语句中,如果可以,优先使用where语句
select avg(SAL),DEPTNO,count(*) from EMP group by DEPTNO having avg(SAL)>2000 order by avg(SAL); select DEPTNO,sum(SAL),avg(SAL),min(SAL) from EMP where ENAME not like 'K%' group by DEPTNO having min(SAL)>1000 order by avg(SAL) asc;
#子查询
##MySql子查询
###子查询是指,在DML语句,嵌套了另外一个查询(DQL)语句
###某些DDL也可以使用子查询
###子查询语句称为内部语句,而包含子查询的查询语句称为外部查询
###常用的子查询会出现两种情况
-------外部查询的where字句使用子查询
-------子查询在from后面,用子查询的结果充当一张表
###子查询可以在表达式的任何地方使用,但必须在括号中关闭
###子查询可以嵌套在另外一个子查询中
select * from EMP where SAL=(select min(SAL) from EMP); select * from EMP where SAL>(select avg(SAL) from EMP); select * from EMP where HIREdate=(select max(HIREdate) from EMP); select * from t_item_category where id in(select distinct category_id from t_item); select SAL from EMP where SAL>(select max(SAL) from EMP where DEPTNO=20); select DEPTNO,DNAME from Dept where DEPTNO=(select DEPTNO from EMP where ENAME='KING'); select EMPNO,ENAME,JOB,DEPTNO from EMP where DEPTNO=(select DEPTNO from Dept where DNAME='SALES'); ##活用in select * from EMP where DEPTNO in(select DEPTNO from Dept where LOC='DALLAS'); select * from EMP where JOB=(select JOB from EMP where ENAME='JONES'); select * from EMP where JOB=(select JOB from EMP where ENAME='JONES') and ENAME<>'JONES';
##关联查询数据
###从多张表中查询相应记录信息
###关联查询的重点在于这些表记录的对应关系,这个关系称为连接条件(关联条件)
select e.ENAME,d.DNAME from EMP e,Dept d where e.DEPTNO=d.DEPTNO;
### 避免 笛卡尔积
####当多表关联时,没有关联条件,返回的结果集是这几张表条目数的乘积,这个乘积叫做笛卡尔积
--多数情况下笛卡尔积是无意义的,非常耗资源,要尽量避免
###从多张表中查询相应记录信息
###关联查询的重点在于这些表记录的对应关系,这个关系称为连接条件(关联条件)
select e.ENAME,d.DNAME from EMP e,Dept d where e.DEPTNO=d.DEPTNO;
### 避免 笛卡尔积
####当多表关联时,没有关联条件,返回的结果集是这几张表条目数的乘积,这个乘积叫做笛卡尔积
--多数情况下笛卡尔积是无意义的,非常耗资源,要尽量避免
select e.ENAME,d.LOC from EMP e,Dept d where e.DEPTNO=d.DEPTNO and d.LOC='NEW YORK';
select ENAME,SAL,DNAME,LOC from EMP e,Dept d where SAL>3000 and e.DEPTNO=d.DEPTNO;
##等值连接,内连接
###语法select * from A,B where A.某字段=B.某字段
###语法select * from A join B on A.某字段=B.某字段
###完整语法select * from A inner join B on A.某字段=B.某字段
####不满足连接条件的记录是不会在关联查询中被查询出来对
###语法select * from A,B where A.某字段=B.某字段
###语法select * from A join B on A.某字段=B.某字段
###完整语法select * from A inner join B on A.某字段=B.某字段
####不满足连接条件的记录是不会在关联查询中被查询出来对
select e.ENAME,e.SAL,d.DNAME,d.LOC from EMP e join Dept d on e.DEPTNO=d.DEPTNO where e.SAL>3000; select e.ENAME,d.LOC from EMP e join Dept d on e.DEPTNO=d.DEPTNO where d.LOC='NEW YORK';
##左外连接
--以join左侧表作为基准(驱动表--所有数据都会被显示出来,不管是否和连接条件),那么当该表中的某条记录不满足连接条件时,来自右表的字段全部为null
--以join左侧表作为基准(驱动表--所有数据都会被显示出来,不管是否和连接条件),那么当该表中的某条记录不满足连接条件时,来自右表的字段全部为null
select e.ENAME,e.SAL,d.DNAME,d.LOC from Dept d left join EMP e on e.DEPTNO=d.DEPTNO
##右外连接
select e.ENAME,e.SAL,d.DNAME,d.LOC from EMP e right join Dept d on e.DEPTNO=d.DEPTNO
select * from t_item t1 join t_item_category t2 on t1.category_id=t2.id; select * from t_item t1 right join t_item_category t2 on t1.category_id=t2.id; select * from t_item t1 left join t_item_category t2 on t1.category_id=t2.id; select e.EMPNO,e.ENAME,e.JOB,e.SAL,s.GRADE,s.LOSAL from EMP e left join SALGRADE s on e.sal between s.LOSAL and s.HISAL order by e.EMPNO; select e.EMPNO,e.ENAME,e.JOB,d.DEPTNO,d.DNAME,d.LOC from EMP e left join Dept d on e.DEPTNO=d.DEPTNO where e.ENAME not like '%K%';
####mysql oracle在外连接有区别
-oracle有全外连接 关键字full 左右两边不满足连接条件的都可以显示
-oracle有全外连接 关键字full 左右两边不满足连接条件的都可以显示
///##
##自连接,自关联连接
###自连接,当前表的一条记录可以对应当前表自己的多条记录,
自连接是为了解决同类型数据,但又存在上下级关系的树状结构时使用。
//
select e.ENAME 员工,e1.ENAME 领导,d.LOC from EMP e,EMP e1,Dept
d where e.MGR=e1.EMPNO and e1.DEPTNO=d.DEPTNO and e.ENAME='SMITH';
select e.ENAME,m.ENAME,d.LOC from EMP e join EMP m on e.MGR=m.EMPNO join Dept d on m.DEPTNO=d.DEPTNO where e.ENAME='SMITH';
select e.*,m.ENAME from EMP e join EMP m on e.MGR=m.EMPNO;
select e.*,m.ENAME,d.DNAME from EMP e join EMP m on e.MGR=m.EMPNO join Dept d on e.DEPTNO=d.DEPTNO;
select e.EMPNO,e.ENAME,e.JOB,e.SAL,m.EMPNO 领导,m.ENAME,m.JOB,m.SAL
from EMP e left join EMP m on e.MGR=m.EMPNO;
#设计表结构的一些基本原则
###一对多关联
通常一的这一方称之为主表,多的一方称之为从表
####通过关联关系理解:
--这张表一条数据对应另外一张表的一条数据 1
--这张表一条数据对应另外一张表的多条数据 n
--学生与班级 学生是主表 班级表是从表
--员工和部门 员工是主表,部门是从表
--设计方案,在主表(1)中增加一个字段,保存从表(n)的主键,形成主外键关系
####直观理解:正好相反
--设计方案,在从(N)表中增加一个字段,保存主表(1)的主键,形成主外键关系
##多对多关系
--学生和课程
--角色和功能
--设计方案:增加中间表,保存双方的主键 (中间表和另外两张表都是一对多的关联关系)
from EMP e left join EMP m on e.MGR=m.EMPNO;
#设计表结构的一些基本原则
###一对多关联
通常一的这一方称之为主表,多的一方称之为从表
####通过关联关系理解:
--这张表一条数据对应另外一张表的一条数据 1
--这张表一条数据对应另外一张表的多条数据 n
--学生与班级 学生是主表 班级表是从表
--员工和部门 员工是主表,部门是从表
--设计方案,在主表(1)中增加一个字段,保存从表(n)的主键,形成主外键关系
####直观理解:正好相反
--设计方案,在从(N)表中增加一个字段,保存主表(1)的主键,形成主外键关系
##多对多关系
--学生和课程
--角色和功能
--设计方案:增加中间表,保存双方的主键 (中间表和另外两张表都是一对多的关联关系)
##连接方式和关联关系的区别
-连接方式是匹配数据的方式,是具体实现管理的语法,它能实现任何关联关系的数据查询---sql是语法
-关联关系是逻辑角度阐述两张表的关系,在这个关系的基础上,可以在任何连接方式查询出相关数据--是设计表的时候的逻辑
-连接方式是匹配数据的方式,是具体实现管理的语法,它能实现任何关联关系的数据查询---sql是语法
-关联关系是逻辑角度阐述两张表的关系,在这个关系的基础上,可以在任何连接方式查询出相关数据--是设计表的时候的逻辑
##什么是权限管理
--对用户访问软件的权限进行的限制手段
##如何实现权限管理
--1.设计3张表:用户表、角色表、功能表
create table user(
uid int auto_increment, //主键自增长
uname varchar(100),
create_time timestamp,
primary key(uid)
);
insert into user values(null,'张三',now());
insert into user values(null,'李四',now());
insert into user values(null,'王五',now());
create table role(
id int auto_increment,
name varchar(100),
create_time timestamp,
primary key(id)
);
insert into role values(null,'管理员',now());
insert into role values(null,'店小二',now());
insert into role values(null,'普通用户',now());
--对用户访问软件的权限进行的限制手段
##如何实现权限管理
--1.设计3张表:用户表、角色表、功能表
create table user(
uid int auto_increment, //主键自增长
uname varchar(100),
create_time timestamp,
primary key(uid)
);
insert into user values(null,'张三',now());
insert into user values(null,'李四',now());
insert into user values(null,'王五',now());
create table role(
id int auto_increment,
name varchar(100),
create_time timestamp,
primary key(id)
);
insert into role values(null,'管理员',now());
insert into role values(null,'店小二',now());
insert into role values(null,'普通用户',now());
create table user_role(
id int auto_increment,
user_id int,
role_id int,
primary key (id)
);
insert into user_role values(null,1,3);
insert into user_role values(null,2,3);
insert into user_role values(null,3,3);
1.查询用户及其对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=role_id;
2.查询张三对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=role_id
where u.uname='张三';
###权限管理表实现(续2)
create table work(
id int primary key auto_increment,
rid int,
work_item varchar(100)
);
insert into work values (null,1,"对用户信息、商品信息、订单信息、角色信息等后台数据进行操作");
insert into work values (null,2,"对商品信息、订单信息等后台数据进行操作");
insert into work values (null,3,"浏览商品、购买商品");
id int auto_increment,
user_id int,
role_id int,
primary key (id)
);
insert into user_role values(null,1,3);
insert into user_role values(null,2,3);
insert into user_role values(null,3,3);
1.查询用户及其对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=role_id;
2.查询张三对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=role_id
where u.uname='张三';
###权限管理表实现(续2)
create table work(
id int primary key auto_increment,
rid int,
work_item varchar(100)
);
insert into work values (null,1,"对用户信息、商品信息、订单信息、角色信息等后台数据进行操作");
insert into work values (null,2,"对商品信息、订单信息等后台数据进行操作");
insert into work values (null,3,"浏览商品、购买商品");
3. user表中所有用户可以做什么操作
select u.uname,w.work_item from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id
join work w
on r.id=w.rid;
4.查看张三可以进行的操作
select u.uname,w.work_item
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id
join work w
on w.rid=r.id
where u.uname='张三';
#视图
##视图概括
--视图就是一张虚拟表,使用方法和使用表一样
--视图不是真实存在的表
--视图中对应的是一个select语句的查询结果集
--创建视图的语法
create view 视图名 as 子查询
create view v_emp_10 as select EMPNO,ENAME,DEPTNO from EMP where DEPTNO=10;
---查看视图 select * from 视图名
--使用 SHOW CREATE VIEW viewname ;来查看创建视图的语句。
####使用视图的目的:
--重用SQL语句。
--简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必
知道它的基本查询细节。
--使用表的组成部分而不是整个表。
--保护数据。可以给用户授予表的特定部分的访问权限而不是整个
表的访问权限。
--更改数据格式和表示。视图可返回与底层表的表示和格式不同的
数据。
在视图创建之后,可以用与表基本相同的方式利用它们。
####
--视图本身不包含数据
--视图只是映射到基表的查询语句,当基表数据发生变化时,视图显示的数据也随之发生变化
--如果创建是视图时,子查询起了别名,那么视图只认识别名。
###修改视图
--更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR
REPLACE VIEW。如果要更新的视图不存在,则第 2 条更新语句会创
建一个视图;如果要更新的视图存在,则第 2 条更新语句会替换原
有视图。
create or replace view v_emp_10_1 as select EMPNO id,ENAME name,DEPTNO from EMP where DEPTNO=10;
##视图的分类
--视图分为简单视图和复杂视图
简单视图:创建视图的子查询中,不含有关联查询,查询的字段不包含函数和表达式,没有分组,没有去重。
反之就是复杂视图
##对视图进行DML操作,只针对简单视图进行可以使用
insert into v_emp_10_1 values(2001,'lily',10);
####对视图进行DML操作要遵循基表的约束,不然会失败
--视图只能看到10号部门,但是通过视图插入了20号部门的员工,数据是偷渡过去的,对基表进行了污染。
insert into v_emp_10_1 values(2002,'lilei',20);
--对视图的操作就是对基表的操作,操作不当会对基表产生数据污染
##视图概括
--视图就是一张虚拟表,使用方法和使用表一样
--视图不是真实存在的表
--视图中对应的是一个select语句的查询结果集
--创建视图的语法
create view 视图名 as 子查询
create view v_emp_10 as select EMPNO,ENAME,DEPTNO from EMP where DEPTNO=10;
---查看视图 select * from 视图名
--使用 SHOW CREATE VIEW viewname ;来查看创建视图的语句。
####使用视图的目的:
--重用SQL语句。
--简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必
知道它的基本查询细节。
--使用表的组成部分而不是整个表。
--保护数据。可以给用户授予表的特定部分的访问权限而不是整个
表的访问权限。
--更改数据格式和表示。视图可返回与底层表的表示和格式不同的
数据。
在视图创建之后,可以用与表基本相同的方式利用它们。
####
--视图本身不包含数据
--视图只是映射到基表的查询语句,当基表数据发生变化时,视图显示的数据也随之发生变化
--如果创建是视图时,子查询起了别名,那么视图只认识别名。
###修改视图
--更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR
REPLACE VIEW。如果要更新的视图不存在,则第 2 条更新语句会创
建一个视图;如果要更新的视图存在,则第 2 条更新语句会替换原
有视图。
create or replace view v_emp_10_1 as select EMPNO id,ENAME name,DEPTNO from EMP where DEPTNO=10;
##视图的分类
--视图分为简单视图和复杂视图
简单视图:创建视图的子查询中,不含有关联查询,查询的字段不包含函数和表达式,没有分组,没有去重。
反之就是复杂视图
##对视图进行DML操作,只针对简单视图进行可以使用
insert into v_emp_10_1 values(2001,'lily',10);
####对视图进行DML操作要遵循基表的约束,不然会失败
--视图只能看到10号部门,但是通过视图插入了20号部门的员工,数据是偷渡过去的,对基表进行了污染。
insert into v_emp_10_1 values(2002,'lilei',20);
--对视图的操作就是对基表的操作,操作不当会对基表产生数据污染
###不算污染 只改了视图自己可以看到的
update v_emp_10_1 set name='bbb' where DEPTNO=10;
update v_emp_10_1 set DEPTNO=20;
update v_emp_10_1 set name='bbb' where DEPTNO=30;
#####mysql 数据更新(update)不会视图污染,
####删除视图中的数据-在mysql中(影响行数0)不会产生数据污染
delete from v_emp_10_1 where DEPTNO=30;
delete from v_emp_10_1 where DEPTNO=30;
#####mysql中,通过视图能产生数据污染的只有insert
--为视图增加了检查选项,可以保证对视图进行了DML操作的时候,视图必须对改变的部分可见(不可见的不许改)否则不允许进行DML操作,
###这样避免数据污染 with check option
create or replace view v_emp_10_1 as select EMPNO id,ENAME name,DEPTNO from EMP where DEPTNO=20 with check option;
insert into v_emp_10_1 values(2003,'lili',20);
insert into v_emp_10_1 values(2003,'haimeimei',30);
--为视图增加了检查选项,可以保证对视图进行了DML操作的时候,视图必须对改变的部分可见(不可见的不许改)否则不允许进行DML操作,
###这样避免数据污染 with check option
create or replace view v_emp_10_1 as select EMPNO id,ENAME name,DEPTNO from EMP where DEPTNO=20 with check option;
insert into v_emp_10_1 values(2003,'lili',20);
insert into v_emp_10_1 values(2003,'haimeimei',30);
####视图简化复杂查询语句,重用子查询
1.简化复杂查询
2.如果需要经常执行某项复杂查询,可以基于这项复杂查询创建视图。
####限制数据访问
1.视图本质上就是一条select语句
2.当访问视图时,只能访问到select语句中涉及到的列
3.对基表中其他列起到安全和保密作用
####工作中对视图一般只进行DQL(查)操作,不进行DML(增、删、改)操作
//创建一个复杂视图
-包含一个含有公司部门工资情况的部门
-内容如下
-部门编号,部门名称,
-部门最高、最低、平均工资//函数 表达式加别名
-工资总和
create view v_dept_sal as
select d.DEPTNO,d.DNAME,max(e.SAL) max_sal,min(e.SAL) min_sal,avg(e.SAL) avg_sal,sum(e.SAL) sum_sal from EMP e join Dept d on e.DEPTNO=d.DEPTNO group by DEPTNO;
1.简化复杂查询
2.如果需要经常执行某项复杂查询,可以基于这项复杂查询创建视图。
####限制数据访问
1.视图本质上就是一条select语句
2.当访问视图时,只能访问到select语句中涉及到的列
3.对基表中其他列起到安全和保密作用
####工作中对视图一般只进行DQL(查)操作,不进行DML(增、删、改)操作
//创建一个复杂视图
-包含一个含有公司部门工资情况的部门
-内容如下
-部门编号,部门名称,
-部门最高、最低、平均工资//函数 表达式加别名
-工资总和
create view v_dept_sal as
select d.DEPTNO,d.DNAME,max(e.SAL) max_sal,min(e.SAL) min_sal,avg(e.SAL) avg_sal,sum(e.SAL) sum_sal from EMP e join Dept d on e.DEPTNO=d.DEPTNO group by DEPTNO;
//查询出比自己所在部门的平均工资高的员工
select e.ENAME,e.SAL,e.DEPTNO,v.avg_sal
from EMP e,v_dept_sal v where e.DEPTNO=v.DEPTNO and e.SAL>v.avg_sal;
###删除视图
--用 DROP 删除视图,其语法为 DROP VIEW viewname;。
drop view 视图名
drop view v_emp_10_1;
##索引
--用来加快查询的计数有很多,最重的就是索引
--通常索引可以大幅度提高查询速度
--如果不使用索引,mysql会从第一条数据开始,读完整个表,直到找到相关数据,表越大(条目数多),花费时间越多。
--索引可以用来改善性能,但是有时可以降低性能。
--索引的统计和应用是数据库自动完成的
--使用索引的位置
where deptno=20
order by
distinct
like 不会使用索引
create index 索引名 on 表名(字段);
create index idx_emp_ename on EMP(ENAME);
select * from EMP where ENAME='SMITH';
#####只要数据库认为可以使用某个已经创建的索引,索引就会自动应用
-我们只需要决定要不要给某张表的某个字段创建索引
from EMP e,v_dept_sal v where e.DEPTNO=v.DEPTNO and e.SAL>v.avg_sal;
###删除视图
--用 DROP 删除视图,其语法为 DROP VIEW viewname;。
drop view 视图名
drop view v_emp_10_1;
##索引
--用来加快查询的计数有很多,最重的就是索引
--通常索引可以大幅度提高查询速度
--如果不使用索引,mysql会从第一条数据开始,读完整个表,直到找到相关数据,表越大(条目数多),花费时间越多。
--索引可以用来改善性能,但是有时可以降低性能。
--索引的统计和应用是数据库自动完成的
--使用索引的位置
where deptno=20
order by
distinct
like 不会使用索引
create index 索引名 on 表名(字段);
create index idx_emp_ename on EMP(ENAME);
select * from EMP where ENAME='SMITH';
#####只要数据库认为可以使用某个已经创建的索引,索引就会自动应用
-我们只需要决定要不要给某张表的某个字段创建索引
####mysql innodb B+tree 3次磁盘io就可以找到
####复合索引
create index idx_emp_job_sal on EMP(JOB,SAL);
select * from EMP order by JOB,EMP;(索引多一个少一个位置调换都不行)
###创建表的时候加索引
create table mytable(
id int not null,
uname varchar(6) not null,
index idx_mytable_uname (uname)
);
####更改索引
alter table mytable add index idx_mytable_uname1(uname);
####删除索引
drop index idx_mytable_uname1 on mytable;
###索引总结
-经常出现在where子句中的列创建索引
-经常出现在order by子句中的列创建索引
-经常出现在distinct后面的列创建索引
-如果创建的是复合索引,索引的字段顺序和关键字顺序要一致
-为经常作为表连接条件的列创建索引
####复合索引
create index idx_emp_job_sal on EMP(JOB,SAL);
select * from EMP order by JOB,EMP;(索引多一个少一个位置调换都不行)
###创建表的时候加索引
create table mytable(
id int not null,
uname varchar(6) not null,
index idx_mytable_uname (uname)
);
####更改索引
alter table mytable add index idx_mytable_uname1(uname);
####删除索引
drop index idx_mytable_uname1 on mytable;
###索引总结
-经常出现在where子句中的列创建索引
-经常出现在order by子句中的列创建索引
-经常出现在distinct后面的列创建索引
-如果创建的是复合索引,索引的字段顺序和关键字顺序要一致
-为经常作为表连接条件的列创建索引
#####不要在经常做DML操作的表和列上建立索引
--不要在小表上创建索引
--索引不是越多越好
--删除很少使用,不合理的索引
--不要在小表上创建索引
--索引不是越多越好
--删除很少使用,不合理的索引
###sql约束
####主键约束(primary key)
1.不允许重复,不允许空值
2.创建主键约束
--列级语法
create table student1(
id int primary key,
name varchar(20)
);
--表级语法 constraint 约束名 约束类型(列名)
create table student(
id int,
name varchar(20),
constraint pk_stu_id primary key(id)
);
//简写
create table student2(
id int,
name varchar(20),
primary key(id)
);
####主键约束(primary key)
1.不允许重复,不允许空值
2.创建主键约束
--列级语法
create table student1(
id int primary key,
name varchar(20)
);
--表级语法 constraint 约束名 约束类型(列名)
create table student(
id int,
name varchar(20),
constraint pk_stu_id primary key(id)
);
//简写
create table student2(
id int,
name varchar(20),
primary key(id)
);
####在表创建之后添加主键约束--相当于表级语法
alter table student add primary key(id);
alter table student modify id int primary key;
####删除主键约束
alter table student drop primary key;
/**mysql不行 oracle可以*/
alter table student modify id int; //不起作用
alter table student add primary key(id);
alter table student modify id int primary key;
####删除主键约束
alter table student drop primary key;
/**mysql不行 oracle可以*/
alter table student modify id int; //不起作用
#####主键自增
create table t1(
id int primary key auto_increment,
name varchar(10)
);
insert into t1 values(null,'a');
insert into t1 values(10,'bbb');
delete from t1 where id=13;
insert into t1 values(null,'a');
#####主键自增长的细节
1.插入数据时,使用null,自动增长
2.插入数据时,如果插入一个比较大的数,那么下次增长从这个数累加
3.删除末尾的条目,这个条目的id不会再次从表中通过自增长出现。
##外键约束
1.工作中除非特殊情况,一般不使用外键,使用逻辑外键
2.外键约束是保证一个或两个表之间的参照完整性,保持数据一致
3.表的外键可以是另一张表的主键,也可以是唯一的索引
4.外键可以重复,可以是null
create table t1(
id int primary key auto_increment,
name varchar(10)
);
insert into t1 values(null,'a');
insert into t1 values(10,'bbb');
delete from t1 where id=13;
insert into t1 values(null,'a');
#####主键自增长的细节
1.插入数据时,使用null,自动增长
2.插入数据时,如果插入一个比较大的数,那么下次增长从这个数累加
3.删除末尾的条目,这个条目的id不会再次从表中通过自增长出现。
##外键约束
1.工作中除非特殊情况,一般不使用外键,使用逻辑外键
2.外键约束是保证一个或两个表之间的参照完整性,保持数据一致
3.表的外键可以是另一张表的主键,也可以是唯一的索引
4.外键可以重复,可以是null
5.使用外键约束的条件
--父表和子表必须使用相同的存储引擎
--存储引擎必须是innodb
--外键列和参照列必须具有相似的数据类型,数字长度,若是有符号,必须相同----------字符类型长度可以不同
--外键列和参照列必须创建索引,如果外键列不存在索引,mysql自动为其创建。
1.注意,虽然mysql支持外键的列级语法创建外键,但是无效
2.表级语法创建
constraint 外键约束名 foreign key(本表列名) references 目标表(目标列)
create table teacher_table(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
insert into teacher_table values(null,'t1');
insert into teacher_table values(null,'t2');
insert into teacher_table values(null,'t3');
insert into teacher_table values(null,'t4');
create table student_table(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
foreign key(java_teacher) references teacher_table(teacher_id)
);
insert into student_table values(null,'s1',1);
insert into student_table values(null,'s2',1);
insert into student_table values(null,'s3',2);
insert into student_table values(null,'s4',3);
delete from teacher_table where teacher_id=1;
--父表和子表必须使用相同的存储引擎
--存储引擎必须是innodb
--外键列和参照列必须具有相似的数据类型,数字长度,若是有符号,必须相同----------字符类型长度可以不同
--外键列和参照列必须创建索引,如果外键列不存在索引,mysql自动为其创建。
1.注意,虽然mysql支持外键的列级语法创建外键,但是无效
2.表级语法创建
constraint 外键约束名 foreign key(本表列名) references 目标表(目标列)
create table teacher_table(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
insert into teacher_table values(null,'t1');
insert into teacher_table values(null,'t2');
insert into teacher_table values(null,'t3');
insert into teacher_table values(null,'t4');
create table student_table(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
foreign key(java_teacher) references teacher_table(teacher_id)
);
insert into student_table values(null,'s1',1);
insert into student_table values(null,'s2',1);
insert into student_table values(null,'s3',2);
insert into student_table values(null,'s4',3);
delete from teacher_table where teacher_id=1;
###唯一约束(UNIQUE)
--指定表中某一列或者多列不能有重复的值
--唯一约束可以保证记录的唯一性
--唯一约束的字段可以为空值
--每张表可以存在多个唯一约束的列
####创建唯一约束
列级语法:
create table t2(
name varchar(20) unique
);
表级约束:
create table t3(
a int,
b int,
constraint uk_name_pass unique(a)
);
简写
create table t4(
a int,
b int,
unique(a)
);
###复合约束
create table temp(
id int not null,
name varchar(20),
password varchar(20),
constraint uk_name_password unique(name,password)
);
###删除约束
alter table temp drop index uk_name_password;
###创建表之后在添加唯一约束
alter table temp add unique uk_name_password(name,password);
alter table temp modify name varchar(25) unique;
###非空约束
create table t4(
id int not null,
name varchar(25) not null default 'abc'
);
insert into t4 (id) values(1);
###表建好之后修改非空约束
alter table t4 modify id int null;
alter table t4 modify id int not null;
###默认约束(DEFAULT)
1.用于设置列的默认值
2.要求:
--定义的默认值的常量必须与这个列的数据类型、精度等相匹配
--每个列只能定义一个默认约束
create table t5(
id int ,
name varchar(20),
sex char(10) default '男'
);
insert into t5(id,name) values(1,'aaa');
###check约束 mysql不支持检查约束,可以创建并且不报错,只不过没有任何效果
1.检查约束的作用,验证数据
create table t6(
id int,
name varchar(20),
age int,
check(age>20)
);
insert into t6 values(1,'aa',12);)
##事务
1.事务是一组原子性(最小事务单元)的sql查询,在事务内的语句,要么全都执行,要么全都不执行。
--指定表中某一列或者多列不能有重复的值
--唯一约束可以保证记录的唯一性
--唯一约束的字段可以为空值
--每张表可以存在多个唯一约束的列
####创建唯一约束
列级语法:
create table t2(
name varchar(20) unique
);
表级约束:
create table t3(
a int,
b int,
constraint uk_name_pass unique(a)
);
简写
create table t4(
a int,
b int,
unique(a)
);
###复合约束
create table temp(
id int not null,
name varchar(20),
password varchar(20),
constraint uk_name_password unique(name,password)
);
###删除约束
alter table temp drop index uk_name_password;
###创建表之后在添加唯一约束
alter table temp add unique uk_name_password(name,password);
alter table temp modify name varchar(25) unique;
###非空约束
create table t4(
id int not null,
name varchar(25) not null default 'abc'
);
insert into t4 (id) values(1);
###表建好之后修改非空约束
alter table t4 modify id int null;
alter table t4 modify id int not null;
###默认约束(DEFAULT)
1.用于设置列的默认值
2.要求:
--定义的默认值的常量必须与这个列的数据类型、精度等相匹配
--每个列只能定义一个默认约束
create table t5(
id int ,
name varchar(20),
sex char(10) default '男'
);
insert into t5(id,name) values(1,'aaa');
###check约束 mysql不支持检查约束,可以创建并且不报错,只不过没有任何效果
1.检查约束的作用,验证数据
create table t6(
id int,
name varchar(20),
age int,
check(age>20)
);
insert into t6 values(1,'aa',12);)
##事务
1.事务是一组原子性(最小事务单元)的sql查询,在事务内的语句,要么全都执行,要么全都不执行。
###事务的ACID 特性
1.原子性:最小的、不可分割的业务单元
2.一致性:都执行或者都不执行,保持同一个状态。
3.隔离性:多个事务并发,相互不影响。
4.持久性:commit(提交)之后,数据保存在数据库中
###使用事务的要求
-在mysql众多引擎中,innodb、NDB Cluster支持事务
-mysql默认自动提交事务,想手动提交,需要把默认提交关闭
####关闭默认提交
show variables like 'autocommit'; //展示
set autocommit=0; //关闭
start transaction;//开启
######案例
create table account(
id int,
name varchar(20),
money float
);
insert into account values(1,'aaa',1000);
insert into account values(2,'bbb',1000);
update account set money=money-100 where name='aaa';
update account set money=money+100 where name='bbb';
此时没有输入commit,直接关闭终端,再次打开终端,把自动提交关闭
查询account账户,之前的操作回滚了,
再次开启事务,完成两次update
输入commit--》数据真正保存在表中
rollback 回滚 提交失败 先关闭事务 在开启 在进行操作?
1.原子性:最小的、不可分割的业务单元
2.一致性:都执行或者都不执行,保持同一个状态。
3.隔离性:多个事务并发,相互不影响。
4.持久性:commit(提交)之后,数据保存在数据库中
###使用事务的要求
-在mysql众多引擎中,innodb、NDB Cluster支持事务
-mysql默认自动提交事务,想手动提交,需要把默认提交关闭
####关闭默认提交
show variables like 'autocommit'; //展示
set autocommit=0; //关闭
start transaction;//开启
######案例
create table account(
id int,
name varchar(20),
money float
);
insert into account values(1,'aaa',1000);
insert into account values(2,'bbb',1000);
update account set money=money-100 where name='aaa';
update account set money=money+100 where name='bbb';
此时没有输入commit,直接关闭终端,再次打开终端,把自动提交关闭
查询account账户,之前的操作回滚了,
再次开启事务,完成两次update
输入commit--》数据真正保存在表中
rollback 回滚 提交失败 先关闭事务 在开启 在进行操作?