Mysql学习1

最新推荐文章于 2024-04-10 11:51:16 发布

ChengZi~

最新推荐文章于 2024-04-10 11:51:16 发布

阅读量271

点赞数

分类专栏： Mysql 文章标签： Mysql学习

本文链接：https://blog.csdn.net/qq_38339124/article/details/99705043

版权

Mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1 SQL基础

1.1 SQL的分类

语言种类	代表关键字	缩写
数据查询语言(Data Query Language)	select	DQL
数据操纵语言(Data Manipulation Language)	insert,delete,update	DML
数据定义语言(Data Definition Language)	代表关键字:create ,drop,alter,	DDL
事务控制语言(Transactional Control Language)	代表关键字:commit ,rollback;	TCL
数据控制语言(Data Control Language)	代表关键字:grant,revoke.	DCL

1.2 导入数据

mysql> use test
mysql>source  D:\test.sql

1.3 表的复制

create table dept2 as select * from dept;

1.4 查看建表语句

show create table <table name>;

2 条件查询

条件查询需要用到where语句，where必须放到from语句表的后面支持如下运算符

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and ….	（左小右大才行）两个值之间,等同于 >= and <=
is null	为null（is not null 不为空）null在sql中不是一个具体的值，不是值，所以不能使用等号衡量
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线匹配
%	匹配任意个字符
_	下划线，一个下划线只匹配一个字符

2.1 and

找出工作岗位SALESMAN中薪资高于1500的员工，要求显示员工名、工作岗位、薪资。

Select ename,job,sal  from emp  where  job = 'SALESMAN' and sal > 1500;

2.2 or

请找出工作岗位是SALESMAN和MANAGER的员工，要求显示员工名、工作岗位。

select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';

2.3 and和or的优先级

查询薪水大于1800，并且部门编号为20或30的员工,显示员工名、薪资、部门编号。
错误写法：

select ename,sal,deptno from emp where sal > 1800 and deptno = 20 or deptno = 30;

正确的写法

select ename,sal,deptno from emp where sal > 1800 and (deptno = 20 or deptno = 30);

结论：

1、and和or在一起的时候，and的优先级较高。
2、遇到优先级不确定的时候，可以加小括号。带小括号的优先级较高，会先执行。

2.4 in

请找出工作岗位是SALESMAN和MANAGER的员工，要求显示员工名、工作岗位。
or 的写法：

select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';

in 的写法：

select ename,job from emp where job in('SALESMAN' , 'MANAGER');

in 不代表区间：薪资等于1500或等于3000的员工信息

select ename,sal from emp where sal in(1500, 3000);

2.4 not in

找出除薪资是1500和3000之外的员工信息，显示员工名和薪资。
第一种写法：

select ename,sal from emp where sal <> 1500 and sal <> 3000;

第二种写法：

select ename,sal from emp where sal not in(1500, 3000);

in(a, b, c) : 在a b c三个值中
not in(a, b, c)：不在a b c三个值中

2.5 like

like
1、找出名字以’S’开始的员工信息，显示员工名和薪资

注意：在sql语句当中：% 代表任意0到多个字符， _ 代表任意1个字符。

select ename,sal from emp where ename like 'S%';

2、找出名字以’T’结尾的员工信息，显示员工名和薪资

select ename,sal from emp where ename like '%T';

3、找出名字中第二个字母是’A’的员工信息，显示员工名和薪资

select ename,sal from emp where ename like '_A%';

4、找出名字中含有’O’的员工信息，要求显示员工名和薪资

select ename,sal from emp where ename like '%O%';

5、找出商品名字中含有"_"的商品名称

select name from t_product where name like '%_%';

这条sql语句中的"_"有特殊含义，代表的是任意1个字符。

select name from t_product where name like '%\_%';

这里的’_'没有特殊含义了，代表一个普通的下划线字符。

MySQL在windows下是不区分大小写的，将script文件导入MySQL后表名也会自动转化为小写，结果再 想要将数据库导出放到linux服务器中使用时就出错了。因为在linux下表名区分大小写而找不到表，查了很多都是说在linux下更改MySQL的设置使其也不区分大小写，但是有没有办法反过来让windows 下大小写敏感呢。其实方法是一样的，相应的更改windows中MySQL的设置就行了。
        具体操作：
        在MySQL的配置文件my.ini中增加一行：
        lower_case_table_names = 0
        其中 0：区分大小写，1：不区分大小写
        MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
　　    1、数据库名与表名是严格区分大小写的；
　　    2、表的别名是严格区分大小写的；
　　    3、列名与列的别名在所有的情况下均是忽略大小写的；
　　    4、变量名也是严格区分大小写的；    MySQL在Windows下都不区分大小写

3 排序操作

3.1 asc

找出员工的姓名和薪资，要求以薪资升序排列

select ename,sal from emp order by sal;// 默认就是升序。
select ename,sal from emp order by sal asc; // 指定以升序的方式。

3.2 desc

找出员工的姓名和薪资，要求以薪资降序排列。

select ename,sal from emp order by sal desc;

执行顺序：先从from开始执行，再执行where，然后执行select，最后执行排序order by。

select
	...
from
	...
where
	...
order by
	... asc或desc;

3.3 多字段排序

查询员工信息，要求显示员工名和薪资，按照薪资升序排，薪资相同的按照员工名字降序排。

Select ename,sal from  emp  order by  sal  asc,  ename desc;

以上sql含义：
排序字段中越靠前的字段主导作用越大，其中先按照sal的升序排列，当sal相等的时候才会启用ename字段进行降序排列。当sal不相等的时候，则不启用ename字段。

4 单行处理函数/数据处理函数

4.1 lower/upper

mysql语法不严谨，比较松散。没有oracle严格。所以lower和upper函数在mysql中意义不大。重点在oracle中使用。例如：

select ename,sal from emp where lower(ename) = 'smith';
select ename,sal from emp where upper(ename) = 'SMITH';

4.2 substr函数

找出员工名字首字母是A的员工信息，要求显示员工名和薪资。
第一种：模糊查询

select ename,sal from emp where ename like 'A%';

第二种：substr

select ename,sal from emp where substr(ename,1,1) = 'A';

(ename,1,1)第一个1是下标，第二个1是长度。下标从1开始。

4.3 lenth

找出名字长度 > 5的员工信息，要求显示员工名和薪资

select ename,sal from emp where length(ename) > 5;

4.4 trim

找出工作岗位是manager的员工信息，要求显示员工的名字和岗位。

select ename,job from emp where job = ' MANAGER ';
		Empty set (0.00 sec)

 select ename,job from emp where job = trim(' MANAGER ');

4.5 select具体数据

select语句查询的“字段”位置提供的不是字段名而是一个具体数据的时候，会怎样？
首先，这个在sql语句中不会报错，最终查询结果显示的就是这个具体的数据，表当中有多少条记录，就显示多少个这样的数据。

4.6 round

1、 select round(123.45);
在这里插入图片描述
2、select round(123.59);

3、 select round(123.59, 1);

4、 select round(123.593, 2);

5、select round(123.593, 0);

6、select round(123.593, -1);

7、select round(125.593, -1);

4.7 rand

1、select rand();
在这里插入图片描述
2、0~100的随机数：
select round(rand() * 100);

4.8 case … when … then ……else …end

如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%

select ename,sal,job,
	(case job 
		when 'MANAGER' then sal*1.1 
		when 'SALESMAN' then sal*1.5 
		else sal
	 end) as newsal  from  emp;

4.9 ifnull

空值处理函数ifnull
查询员工年薪，显示员工名、月薪、月补助、以及年薪。

select ename,sal,comm,(sal+comm)*12 as yearsal from emp;

在这里插入图片描述

注意：	null在数据库当中不是一个值。表示真正意义的空，什么也没有，
		所以在数据库当中 但凡是有NULL参加的数学运算最终结果一定是NULL。
		为了避免这个现象，数据库当中有一个空值处理函数，叫做：ifnull

select ename,sal,comm,(sal + ifnull(comm,0))*12 as yearsal from emp;

在这里插入图片描述

4.10 date

关于数据库的日期类型date的处理。日期的处理具有数据库的特征，每个数据库处理日期的函数都不一样。所以在实际开发中，为了数据库能够平滑移植，能够灵活的切换数据库，一般在数据库当中不建议使用日期类型date，日期一般都是以字符串的形式保存，这样更加通用。
1、tr_to_date
将字符串类型(varchar)转换成日期类型(date)。
该函数的语法格式：str_to_date(‘字符串日期’, ‘日期格式’) —> 整体执行结束之后，字符串varchar就变成了date。主要使用在insert操作当中。
create table t_student(
sno int,
sname varchar(255),
birth date
);

insert into t_student(sno,sname,birth) values(1,'zs','10-01-1980'); // 执行失败
ERROR 1292 (22007): Incorrect date value: '10-01-1980' for column 'birth' at row 修改：

insert into t_student(sno,sname,birth) values(1,'zs',str_to_date('10-01-1980','%m-%d-%Y'));

mysql当中的日期格式：

格式	含义
%Y	年
%m	月
%d	日
%H	时
%i	分
%s	秒

insert into t_student(sno,sname,birth) values(2,'lisi','1980-11-12'); /
等同于：
insert into t_student(sno,sname,birth) values(3,'wangwu',str_to_date('1980-09-12','%Y-%m-%d'));

存在自动类型转换注意：写日期的时候，当字符串日期的格式是：‘1980-10-11’ ，会存在自动转换。
2、 date_format
date_format：将日期类型date转换成varchar。
date_format：主要使用在select语句方面，设置展示日期的格式。
date_format语法格式：date_format(日期对象, ‘日期格式’)
查询每个员工的雇佣日期，要求显示员工名和雇佣日期，日期采用’1980/11/25’格式展示

select ename,date_format(hiredate,'%Y/%m/%d') as hiredate from emp;

在这里插入图片描述

select ename,hiredate from emp; // 这个也存在自动类型转换。

在这里插入图片描述

综上所述，得出结论：mysql数据库的默认日期格式是 %Y-%m-%d

5 分组/多行处理函数

多行处理函数和单行处理函数有什么区别？
1、多行处理函数：多个输入，对应最后1个输出。
2、单行处理函数：一行一行处理，输入3行，最后会输出3行。
3、分组函数一共5个：
count 计数
sum 求和
avg 平均数
max 最大值
min 最小值

5.1 sum/avg/max/min

1、计算所有员工月工资总和。（不算补助）
select sum(sal) from emp;
2、计算员工的平均工资。
mysql> select avg(sal) from emp;
3、找出最高工资
select max(sal) from emp;
4、找出最低工资
select min(sal) from emp;
5、计算每个月“月补助comm”总和。
select sum(comm) from emp;

重点：所有的分组函数自动 NULL。
	mysql> select count(comm) from emp;

在这里插入图片描述

5.2 count

count(*) 和 count(具体某个字段)的区别？

select count(*) from emp;

在这里插入图片描述

select count(comm) from emp;

在这里插入图片描述

区别：
	count(具体某个字段)：该字段当中不为NULL的元素数量。
	count(*)：和具体的某个字段没有关系，就是要统计表当中的总数据条数。

分组函数组合也可以使用。

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

在这里插入图片描述

6 分组查询

6.1 group by 和 having

1、group by表示通过xxx分组。
2、having ： having是专门给group by准备的，当分组之后，对分组之后的数据不满意的时候，可以使用having子句进行过滤。类似于where，只不过和where执行时间不同。

找出每个部门的平均工资，要求显示部门的编号以及部门的平均工资。
思路：先按照部门编号分组，然后对每一组求工资的平均值。以上语句一定会使用group by + avg分组函数注意：分组函数，顾名思义，这些函数都是给group by准备的。多数情况下分组函数都是联合group by一起使用的。

select 
	deptno,avg(sal) as avgsal 
from 
	emp 
group by 
	deptno;

以上语句执行原理：先从emp中查询数据，将emp表中的数据根据deptno字段进行分组，然后计算每一组的平均工资。
在这里插入图片描述

找出每个部门的最高薪资，要求显示部门编号以及部门的最高薪资。

select
	deptno,max(sal) as maxsal
from
	emp
group by
	deptno;

在这里插入图片描述

select
	ename,deptno,max(sal) as maxsal
from
	emp
group by
	deptno;

在这里插入图片描述

这个查询结果没有意义。以上语句在Oracle数据库当中编译是报错的，在mysql中可以执行，但执行没有意义。所以：当一个DQL语句中有group by子句的话，select后面只能跟参加分组的字段，以及分组函数，其它字段都是没有意义的.以上的测试也足以说明了Oracle确实比mysql的语法严格# 3 测试表的创表语句

找出除最高薪资之外的员工信息，要求显示员工名，以及薪资。

mysql> select ename,sal from emp where sal < max(sal);
ERROR 1111 (HY000): Invalid use of group function

分组函数不能直接使用在where子句当中。（这个知识点是非常重要的）为什么？分组函数是配合“group by”使用的，只有分了组之后，才能轮到分组函数的执行。没有分组自然是使用不了分组函数的.

select sum(sal) from emp;

并没有使用group by，那么为什么还可以使用sum()分组函数呢？因为一个表没有使用group by进行分组的话，整个这张表的所有数据会自成一组。

以上程序报错的根本原因是:group by的执行一定是在where执行之后才会执行的。在where执行的时候，group by还没有执行，也就是说数据还没有分组，自然分组函数也是无法使用的。所以：where子句中不能直接使用分组函数。
总结一条完整的select语句：


			select			    5
				...
			from				 1     
				...                      -> join ...  on  ....
			where				 2   
				...
			group by			 3
				...
			having			     4
				...
			order by		      	6
				...

以上都是关键字，不能随便写，顺序编写的时候也不能颠倒。固定写法。先从表中查询数据，经过where过滤，然后分组，分组之后不满意再过滤，然后select查询，最后排序输出。

找出每个部门不同工作岗位的最高薪资，要求显示部门编号、岗位、最高薪资。

select
	deptno,job,max(sal) as maxsal
from
	emp
group by
	deptno,job;

在这里插入图片描述
多个字段联合起来分组的时候，字段之间使用逗号隔开。其实也可以把多个字段联合分组看做是一个字段进行的分组。

找出每个工作岗位的最高薪资，除MANAGER岗位之外。
第一种：先分组，分组之后不满意，再过滤。

			select
				job,max(sal) as maxsal
			from
				emp
			group by
				job
			having
				job <> 'MANAGER';

第二种：先进行过滤，然后分组。（优先选择的）

			select
				job,max(sal) as maxsal
			from
				emp
			where
				job <> 'MANAGER'
			group by
				job;

原则：where和having都能过滤数据，但是having是专门给group by准备的，能够使用where过滤，尽可能使用它，where要是实在解决不了这个问题，才会选择having。数据越早过滤掉效率越高。这是SQL优化重要准则。
在这里插入图片描述

找出每个部门的平均工资，要求显示出平均工资大于2000的数据。

select
	deptno,avg(sal) as avgsal
from
	emp
where
	avg(sal) > 2000
group by
	deptno;
	
ERROR 1111 (HY000): Invalid use of group function

select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno
having
	avg(sal) > 2000;

在这里插入图片描述

6.2 distinct

去除重复记录。distinct关键字。注意：这个去除重复记录只是去除查询结果的重复记录，原表数据不受影响。只是把显示结果中的重复记录去掉。

select distinct job from emp;
select count(distinct job) from emp;
select ename,distinct job from emp;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your near 'distinct job from emp' at line 1

结论：distinct关键字只能出现在所有字段的最前面。

select distinct deptno,job from emp;

// 表示deptno,job，这两个字段联合起来去除重复记录。

附录

在这里插入图片描述

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

ChengZi~

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql学习1

Mysql学习11 SQL基础1.1 SQL的分类1.2 导入数据1.3 表的复制1.4 查看建表语句2 条件查询2.1 and2.2 or2.3 and和or的优先级2.4 in3 测试表的创表语句 1 SQL基础1.1 SQL的分类语言种类代表关键字缩写数据查询语言(Data Query Language)selectDQL数据操纵语言(Data Ma...
复制链接

扫一扫

专栏目录