mysql语句学习

最新推荐文章于 2024-07-24 08:55:56 发布

十七楠楠

最新推荐文章于 2024-07-24 08:55:56 发布

阅读量241

点赞数

文章标签： mysql 数据库学习

本文链接：https://blog.csdn.net/xznswd/article/details/127265966

版权

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];