MySQL
- 简介
什么是数据库(DB)
存储数据的仓库,实际上就是一堆文件,这些文件存储了具有特定格式的数据
什么是数据库管理系统(DBMS)
是专门管理数据库中的数据的,数据库管理系统可以对数据库当中的数据进行增删改查
SQL 是什么
程序员需要学习sql语句,程序员通过编写sql语句,然后DBMS负责执行sql语句,最终来完成数据库中数据的怎删改查操作
sql是一套标准,程序员主要学习的就是sql语句这个不止在mysql中使用在oracle也可以使用
他们之间的关系是什么
DMBS--执行-->SQL--操作-->DB
常见的数据库管理系统
MySQL 、Oracle、ms sqlserver 、DB2、sybase等
- sql语句分类
DQL数据查询语言
凡是带有select关键词的都是,如select.......
DML数据操作语言
凡是对表中的数据内容进行增删改查,inserct增加 delect 删除 update 修改
DDL数据定义语言
也是对表进行增删改查但操作的是结构比如:列是结构,create 增加表,alter 修改表,drop 删除表,这个主要是对表的结构进行修改
TCL事物控制语言
包括对事物的提交commit 事物回滚back
DCL数据控制语言
如授权grant ,撤销权限 revoke
- 常用语句
show databases;查看有哪些数据库
show table ;查看数据库下有那些表
select * from 表名;查看数据表里的内容,*代表从这张表里查所有的数据
例如:select * from temp; 查看temp里的所有内容
create table /databases ;创建表/数据库
source +路径 导入数据文件表,路径不能有中文
drop table <表名>; 删除表
drop database <数据库名>; 删除数据库
例如:drop database txt;
desc +表名;只看数据表结构不看内容,全写为:describe
INSERT INTO table_name VALUES()数据表插入数据
将grade表的id为3的english 字段行数据更新为7
update.....set......where
INSERT INTO grade VALUES (2,'陈',17,100,100,'女');
UPDATE grade SET english=7 WHERE id=3;
查询mysql数据库中所有用户
SELECT DISTINCT CONCAT('User: ''',USER,'''@''',HOST,''';') AS QUERY FROM mysql.user;
查看某个用户的权限
show grants for 'shiqi'@'%';
修改用户密码
UPDATE user SET password=PASSWORD('新密码') WHERE user='用户';
DQL查询语句
- 条件查询
查询一个字段
select 字段名 from 表名 ; select和from是关键字,其余两个是标识符,内容可以改变
例如:
select dname form dept 查询dept数据表中dname的内容
查询两个或两个以上的字段数据
select 字段名,字段名 from dept; 查询两个字段或两个以上的用,隔开
例如:
select dname,deptno from dept; 查询dept数据表中dname和deptno的数据
查询所有字段(两种方法)
select A,B,C,D.... from 表名 ;查询所有字段 (第一种)
select * from 表名;第二种的方式效率低,可读性差,开发中不建议采用
给查询的列起别名as
select deptno,dname as deptname from dept; 使用as关键字起别名,将查询结果的列名(dname)显示为deptname,但原表名还是dname只是当前显示为deptname,
注意:select语句只负责查询,永远不会进行修改操作的,as关键词也可以用空格代替,但中间不能有逗号,如果起的别名有空格可以用单引号或双引号括起来
例如:select deptno ,dname “detname” from dept;
= 等于
select empon,dname from emp where sal=800; 查询emp表中工资等于800的员工的姓名与编号
select empon,sal from emp where dname = 'shimisi'; 查询史密斯的编号与工资,字符串要加单引号
大于等于>=
select empon,danme from emp where sal>=3000; 查询emp表中工资大于等于3000的员工姓名和编号
!= 不等于
select empon,dname from emp where sal !=800 查询emp表中工资不等于800的员工
<=小于等于
select empon,dname from emp where sal <=3000 查询emp表中工资小于等于3000的员工信息
大于>
select empon,dname from emp where sal >3000 查询emp表中工资大于3000的员工信息
< 小于
select empon,dname from emp where sal <3000 查询emp表中工资小于3000的员工信息
between…and…两个值之间,等同于>=and<=
select empno,dname,sal from emp where sal>=2450 and <=3500; 第一种方法
查询工资在2450和3500之间的员工信息,(包括2450和3500)的两种方法,查区间。
select empno,dname,sal from emp where sal between 2450 and 3500; 第二种方法
is null 为空(is not null 不为空)
查询那些员工的津贴为空的
select dname,sal,empno,comm from emp where comm is null;
查询那些员工的津贴不为空的
select dname,sal,empno,comm from emp where comm is not null;
and 并且(同时满足两个条件)
查询职位为经理并且工资大于2500的员工信息
select danme,empno,job,sal from emp where jbo = 'manger' and sal > 2500;
or 或者(和)
查询英语成绩为90分以上和60分一下的学生
select name,english,number from grade english >60 or english <90;
当and和or同时出现时,and优先级较高,如果想让or先执行可以加小括号
查询工资大于2500,且部门编号为10和20 的员工信息
select empno,danme,sal,deptno from emp where sal > 2500 and deptno=10 or deptno=20 这句话的意思是找出工资大于2500,且部门编号为10,或者全部编号为20的员工
select empno,danme,sal,deptno from emp where sal > 2500 and (deptno=10 or deptno=20);
加个括号可以题目想要查找的意思。意思是找出部门编号为10和20的且工资大于2500的员工
in 包含,相当于多个or,in后面跟的是具体的值
查询薪资是800和5000的员工(两种方式,in和or)
select name,enpno,sal from emp where sal = 800 or sal = 5000;
select name,empno,sal from emp where sal in (800,5000);可以查找多个值,不是查区间,
select name,empno,sal from emp where sal in not(800,5000);查询除了这两个值以外的其他值
like 模糊查询
支持%或下划线匹配
% 匹配任意个字符
下划线,一个下划线只匹配一个字符
%和下划线都是特殊的符号
例如找出表中名字里含有O的
select enmae from emp where enmae like '%o%';
找出名字以T结尾的
select enmae from emp where enmae like '%t';
找出名字以K开始的
select enmae from emp where ename like 'K%‘;
找出第二个字母是A的
select enmae from emp where ename like '_a%';
找出第三个字母是R的
select enmae from emp where ename like '__r%';前面加两个下划线
找出名字中有下划线的
select enmae from emp where ename like '%\_%'; 用斜杠来转译下划线的意思
- 排序查询
正常排序(默认升序)
select name,sal from emp order by sal ;查询所有员工的薪资排序
指定降序desc
select name,sal from emp order by sal desc;从大到小排序
指定升序esc
select name,sal from emp order by sal esc;从小到大排序
按多个字段排序
select name,sal from emp order by sal esc ,name esc ;查询员工薪资和名字,要求按照薪资升序,如果薪资一样在按照名字升序
了解即可
select name,sal from emp order by 2;
//2表示第二列sal排序,第一列不排序,开发中不建议这样做
综合练习
找出工资在1250到3500之间的员工信息并按薪资降序排列
select name,sal from emp where sal between 1250 and 3500 order by sal desc;
- 数据处理函数
简介:单行函数:一行一行处理的,一个输入对应一个输出,多行函数:多个输入对应一个输出,例如将全部员工的工资相加得出总是,则是多行函数
lower 转小写
select lower(ename) from emp ; 那ename所有显示结果转换为小写
upper 转大写
select upper(enmae)from emp; 将enmae所有显示结果转换为大写
substr 取子串 (格式:substr(被截取的字符串,起始下标,截取长度))
截取ename字符串每列的第一个字母
select subst (ename,1,1) as substenamefrom emp;
找出员工字母第一个是A的员工信息(两种方式)
select name from emp where ename like 'a%' 模糊查询
select name from emp where subst(name,1,1)='A' 找出首字母是大写的
concat函数进行字符串拼接
select concat(ename,demo) from emp;
将两个字符段的数据拼接在一起
lenght取长度
select lenght(ename) as lenght from emp;
显示字符串的长度,不显示内容数据
trim 去空格
select * from emp where ename = trim ( "A");
round 四舍五入
select "abc" from emp; 英文一定要加双引号,不然会默认成是字段在表里面查找
select 1000 as null from emp; //select后面直接跟“字面量/字面值”
意思是,根据你这个表结构生成多少行的ABC,有多少行生成多少行,1000也是被当作字面量
结论:select后面可以跟某个表的字段名,可以等同看作变量名,也可以跟字面量/字面值(数据)
select round(1236.567 ,0) as result from emp;四舍五入,最后字面量显示为1237,0表示为保留整数
select round(1236.567 ,-1) as result from emp; 四舍五入,最后字面量显示为1240,保留到十位
select round(1236.567 ,-2) as result from emp; 四舍五入,最后字面量显示为1240,保留到百位
case 条件语句
格式
case....when....then...when...then...else...end
例如,当员工工作岗位是M时,工资上调10%,当岗位时S时,工资上调50%,注:此命令不修改数据库,只修改显示数据
select ename,job,(case job when 'M' the sal*1.1 when 'S' the sal*1.5 else sal end)as dname from emp;
当学生ID为1时,英语成绩上调10%,当ID为2时,工资上调50%
SELECT NAME,id,(CASE id WHEN 1 THEN english*1.1 WHEN 2 THEN english*1.5 ELSE english END) AS emp FROM grade;
- 多行处理函数
count 计数
计算行数
select count(sal) from emp;
sum 求和
计算全部工资总金额 select avg(sal) from emp;
计算每个岗位的工资和 select job,sum(sal) from emp group by job;
avg 平均
计算平均工资
select avg(sal)from emp;
max最大
找出最高工资 : select max(sal)from emp;
找出每个部门最高薪资: select deptno max(sal) from emp group by deptno;
找出每个部门最高薪资,并显示最高薪资大于3000的(两种方法)
select deptno, max(sal)from emp group by having max(sal) >3000; 这种方法效率低
select deptno ,max(sal) from emp where sal>3000 group by deptno;
where 和having ,优先选择having,where实在完成不了的选择having,group by函数,不能在where语句后面执行。
例如,where完成不了的例题:找出每个部门平均薪资,并显示平均薪资高于2500的
select deptno,avg(sal)from emp group by deptno having avg(sal) > 2500;
找出每个部门不同岗位的最高薪资
select deptno,job ,max(sal) from emp group by deptno,job;
min 最小值
找出最低工资
select min(sal) from emp;
分组函数使用时注意事项:
分组函数自动忽略null
分组函数中,count()和count(具体字段)的区别
count()统计表中的总行数
count(具体字段)统计该字段下所有不为null的元素的总数
分组函数不能直接使用在where子句中
例如:找出比最低工资高的员工信息
select name,sal from emp where sal > min(sal) 不可以使用这样的语法,会显示语法错误
分组函数可以一起使用
select sum(sal),avg(sal),count(*),max(sal),min(sal),from emp;
distinct去重
把查询结果去除重复记录(不会对原数据进行更改,distinct)注意distinct只能出现在所有字段的最前面
select distinct job from emp;
查询有那些工作岗位并去除重复的数据
SELECT DISTINCT DEPTNO ,job AS emm FROM emp;
(在两个字段前面,表示两个字段连接起来去除重复选项)查询部门编号和工作岗位,并改别名去重复
SELECT COUNT(DISTINCT JOB) FROM emp;
统计工作岗位的数量,单行处理函数可以在前
连接查询(重要)
简介:连接查询是什么
从一张表中的查询叫单表查询
两张表联合起来查询数据,从emp表中取员工名字,从dpt表中取部门名字,这种跨表查询,多表联合起来查询书数据叫连接查询
- 内连接
等值连接
查询每个员工所在的部门名称,显示员工名和部门名。
SELECT a.`ENAME`,d.`DNAME` FROM emp a,dept d WHERE a.deptno=d.deptno; 若还要查询在后面加and +条件 sql92的语法
sql92缺点:语法结构不清晰,表的连接条件和后期的筛选条件连接都放在了where后面
SELECT a.`ENAME`,d.`DNAME` FROM emp a inner join dept d on a.deptno=d.deptno; sql99的语法 语法:select 查询字段 from a表 join连接b表 on表的连接条件a和b where 筛选条件 (inner代表内连接,也可以省略,带着可读性更好)
sql99优点:语法结构清晰,连接条件是独立的,将连接条件和where分离,如果连接完以后要在进一步筛选,在后面继续加where条件继续筛选
非等值连接(条件不是一个等量关系)
找出每个员工的薪资等级,并显示员工名,薪资,员工等级
select a.ename,a.sal,d.grade from emp a join salgrade b on sal between d.losal and d.hisal;
自连接(技巧:将一张表看成两张表)
[查询员工的上级领导,要求显示员工名和员工领导
SELECT a.ename AS '员工', b.ename AS '领导' FROM emp a JOIN emp b ON a.mgr = b.empno;//员工的领导编号=领导的员工编号]()
员工 领导
SMITH FORD
ALLEN BLAKE
WARD BLAKE
- 外连接
左外连接(左连接)
select a.ename,d.dname from emp a left (outer) join dept d on a.deptno=d.deptno
右外连接(右连接)
select a.ename,d.dname from emp a right join dept d on a.deptno=d.deptno
在外连接中,两张表连接产生了主次关系,内连接没有主次关系
查询员工的上级领导,要求显示全部员工名(主表)和员工领导(次表)思路:用非等值连接+左连接
SELECT a.ename AS '员工', b.ename AS '领导' FROM emp a left JOIN emp b ON a.mgr = b.empno;//员工的领导编号=领导的员工编号
- 全连接
将两张表都作为主表,将数据都查出来
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
mysql不支持全连接,但可以通过左外连接+ union+右外连接实现
SELECT a.ename,d.dname FROM emp a LEFT JOIN dept d ON a.deptno=d.deptno UNION SELECT a.ename,d.dname FROM emp a RIGHT JOIN dept d ON a.deptno=d.deptno;
- 多表连接(三张和三张以上)可以内外连接混合
找出每个员工的部门名称和工资等级,要求显示员工名,部门名和薪资,薪资等级
SELECT a.ename,a.sal, b.dname,e.grade FROM emp a JOIN dept b ON a.`DEPTNO` = b.`DEPTNO` JOIN salgrade e ON a.`SAL` BETWEEN e.`LOSAL` AND e.`HISAL`
找出每个员工的部门名称和工资等级,还有上级领导,要求显示员工名,领导名,部门名和薪资,薪资等级
SELECT a.ename,a.sal, b.dname,e.grade ,d.ename
FROM emp a JOIN dept b ON a.`DEPTNO` = b.`DEPTNO` JOIN salgrade e ON a.`SAL` BETWEEN e.`LOSAL` AND e.`HISAL` left join emp d on a.sal between d.'条件 and d.'条件'
语法
语法:select ......
from
a join b
on a和b的连接条件
join c
on a和c的连接条件
join d
on a和d的连接条件
子查询(嵌套查询)
什么是子查询语句
select语句中嵌套select语句,被嵌套的select语句叫子查询语句
select可以嵌套的位置
select可以出现的位置
select
.....(select)
from
.....(select)
where
.....((select)
where中的子查询
例题:找出比最低工资高的员工姓名和工资
思路:
1.查询出最低工资是多少
2.找出大于最低工资的数据
3.合并语句
1.select min(sal) from emp ;
2.select ename,sal from emp where sal>800;
3.sleect ename,sal from emp where sal>(select min(sal)from emp);
from中的子查询
查询出平均工资高于2000的职位名称
having方法
select job,avg(sal)from emp group by job having avg(sal)>2000
from中子查询
select job,avgsal from (select job avg(sal)AS avgsal from emp group by job )temp where temp.avgsal>2000
1.FROM 子查询的数据来源是另外一个查询的结果,这个子查询的结果相当于一张临时数据表
2.必须为子查询定义别名
3.如果要使用子查询中的字段,则应该使用"别名.字段名"的方式进行引用
查询出所有在部门SALES(销售部)工作的员工编号,姓名,基本工资,奖金,职位,入职日期,部门最高和最低工资
SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,e.deptno,temp.maxsal,temp.minsal
FROM emp e,(
SELECT deptno dno,MAX(sal) maxsal,MIN(sal) minsal
FROM emp
GROUP BY deptno
) temp
WHERE e.deptno=(SELECT deptno
FROM dept
WHERE dname='SALES')
AND e.deptno=temp.dno;
FROM字句之后跟上的数据来源不只是数据表,还可以是一个查询结果,这就是FROM子查询
一般FROM子查询的返回类型的多行多列的结果集
select后的子查询
找出每个员工的部门名称,要求显示员工名和部门名称
select e.name (select d,name from dept d where d.deptno=e.deptno)as dname from emp e;
FROM字句之后跟上的数据来源不只是数据表,还可以是一个查询结果,这就是FROM子查询
一般FROM子查询的返回类型的多行多列的结果集
集合查询
union并操作
查询工作岗位是manger和sakesbane的员工
select name job from emp where job='M' or job ='S'
select name job from emp where job in ('M','S')
union查询语法:select name job from emp where job=‘m' union select name job from emp where job='s';
查询计算机科学系的学生及年龄小于 19 岁的学生
select * from student where sdept='cs' or age <'19'
select * from student where sdept='cs' union select * from student where age <'19';
UNION
自动取出重复元素
UNION ALL
保留重复元素
union的效率要高一些,相对于表连接来说,每连接一次新表,匹配次数满足笛卡尔效应,成倍的翻,但是union可以减少匹配的次数,在减少匹配次数的情况下,可以完成两个结果的集拼接
表连接匹配方式
a连接b连接c
a=10,b=10,c=10
匹配次数是10*10*10=1000
union匹配方式
a连接b一个结果 10*10=100
a连接c一个结果 10*10=100
最终匹配次数=100+100=200
union把乘法变成了加法来运算,匹配次数减少
INTERSECT交操作
查询计算机科学系的学生与年龄不大于19岁的学生的交集
select * from student where sdept="CS" INTERSECT select * from student where sage < '19';
实际可以用WHERE语句中AND条件实现
select * from student WHERE Sdept='CS' AND Sage<=19;
查询选修课程 1 的学生集合与选修课程 2 的学生集合的交集、
SELECT Sno FROM sc WHERE Cno='1'INTERSECT
SELECT Sno FROM sc WHERE Cno='2';
except 差操作
查询计算机科学系的学生与年龄不大于 19 岁的 学生的差集
select * from student where sdept='cs' except select * from student where sage <='19'
select * from student where sdept='cs' and sage >'19';
实际时查询计算机系中年龄大于19的
数据定义语言DDL
数据库的增删改查
新增
create database 库名; 创建库
use 库名; 指定库
show databases; 查看库下面的所有表
改
ALTER TABLE<旧表名> rename [TO] <新表名>;在一个数据库中,表名是唯一的。可以通过SQL语句对已创建的表修改表名
TO为可选参数,使用与否不影响结果。仅修改表名,表结构不变
ALTER TABLE 表名 ADD 字段名 数据类型 [属性] 向已存在的表中添加新的字段,添加字段的语法如下
ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性] 数据表中一个字段中包含字段名和数据类型,因此如果实现修改字段,可以包括修改字段名和修改数据存储的类型
ALTER TABLE 表名 DROP 字段名;删除字段是将数据表中的某个字段从表中移除
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段); 添加主键约束 不能重复也不能为空
alter table student add ID int not null first; 添加ID字段限制不能为空,且为第一
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 关联表名(关联字段)
创建表的语法
CREATE TABLE [IF NOT EXISTS] 表名(
字段1 数据类型 [字段属性|约束] [索引] [注释],
字段2 数据类型 [字段属性|约束] [索引] [注释],
···
字段n 数据类型 [字段属性|约束] [索引] [注释],
)[表类型][表字符集][注释];
单字段主键:
CREATE TABLE [IF NOT EXISTS] 表名(
字段1 数据类型 PRIMARY KEY,
···
);
在定义完所有字段之后指定主键:
CREATE TABLE student(
`studentNo` INT(4),
···
PRIMARY KEY(`id`)
);
多字段联合主键:
CREATE TABLE [IF NOT EXISTS] 表名(
···
PRIMARY KEY [字段1,字段2···]
);
CREATE TABLE tb_temp(
`id` INT(4),
`name` VARCHAR(11),
···
PRIMARY KEY (`id`,`name`)
);
注释:在创建表的同时可以为表或字段添加说明性文字,即注释。注释是使用COMMENT关键字来添加的
CREATE TABLE test(
`id` int(11) UNSIGNED COMMENT '编号'
)COMMENT='测算表';
DML数据操作语言
表的增删改
update
update student set id=1 where name="张三"; 将张三的ID改为1
UPDATE website SET age = 20, country = 'CN'; 修改表中age和country的所有记录,则不用加where
delete
delete from student where name="李四"; 删除李四字段的记录
delete from student; 删除所有记录
insert
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('百度', 'https://www.baidu.com/', 21, 4, 5010.5, 'CN');
INSERT INTO website VALUES(8, 'VK', 'https://vk.com/', 23, 23, 1206, 'RU');
###使用一个表的数据填充另一个表
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];