MySQL学习宝典之最全的常用语法语句

MySQL引言

引言：为了学习方便，本文章搭配数据库文件学习，方便学习者结合例子进行深入的掌握，更加深刻印象
链接：https://pan.baidu.com/s/1dQfXgCiyC2fs2MV9iXutjg
提取码：20c5

1.登录MySQL数据库

mysql -u root -p
	1.1 exit;  //退出关闭数据库

一、数据库操作相关

2.查询所有数据库
show databases；

3.创建数据库
create database 数据库名；

4.查看数据库的字符集
show create database 数据库名；

5.创建数据库且给定字符集
create database 数据库名 character set utf8/gbk；

6.删除数据库
drop database 数据库名；

7.使用某一个数据库
use 数据库名；

8.数据库导入导出
	导入：source e:/MySQL/lianxi/tables.sql  (导入数据库前先创建个数据库，再use，进行导入这个数据库)
	导出：mysqldump -u root -p 数据库名 表名 > C:\path\path\dump.sql  ----路径		
	Windows 的斜杆是 \  ，Linux 的斜杆是 /

---

二、表相关

8.创建数据表（先use 数据库名，再添加数据表）
create table 数据表名（字段名 类型，字段名 类型）；

9.查询当前数据表
show create table 数据表名；

10.创建数据表并且给定表引擎和字符集
create table 数据表名（字段名 类型） engine=myisam/innodb charset=utf8/gbk;

11.查看数据表所有字段
desc 数据表名

12.删除数据表
drop table 数据表名;

13.查看当前数据库的所有数据表（先use 数据库名，再查看）
show tables；

	13.1清空数据表所有数据 (删除表并创新表 自增数值清零)
		truncate table 数据表名;

14.修改数据表名
rename table 数据表原名 to 新数据表名；
	
15.修改数据表的表引擎和字符集
alter table 数据表名 engine=myisam/innodb charset=utf8/gbk；
	修改当前数据库字符集（先use 当前数据库）
	alter database 数据库名 character set 'utf8';	
	
	
16.数据表添加字段
	16-1.alter table 表名 add 字段名 数据类型； //数据表字段末尾添加
	16-2.alter table 表名 add 字段名 数据类型 first； //数据表字符首位添加
	16-3.alter table 表名 add 字段名 数据类型 after 字段名； //数据表某字段后面添加

17.删除表字段
alter table 表名 drop 字段名；

18.修改字段名和类型
alter table 表名 change 原名 新名 新类型；

19.修改字段类型和位置
alter table 表名 modify 字段名 新类型 first/after xxx；

---

三、数据相关

20.全表插入数据
insert into 表名 values (值1，值2);

21.指定字段格式
insert into 表名 (字段1,字段2) values(值1,值2);
	
	批量插入
	21-1：insert into 表名 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
		  insert into 表名 (字段1,字段2) values(值1,值2),(值1,值2),(值1,值2);


22.查询数据表的数据（先use 数据库名，再查看）
select 字段 from 表名;
select 字段1,字段2 from 表名;
select * from 表名 where 条件;

	22_1.   is null 和 is not null, 查询为空(null)，和不为空的。
			
			select ename from emp where mgr is not null;//查询所有名字，且mgr不能为null的数据表emp
		   
		   
	22_2.	加别名
			select ename as '姓名',sal as '工资' from emp;
			select ename '姓名',sal '工资' from emp;
			select ename 姓名,sal 工资 from emp;
			
			
	22_3.	去重复 distinct
			select distinct job from emp;
			
			
	22_4.	比较运算符
			>  <  =  >=  <=   !=和<>	
			注：数据量大时，加\G就行了。
			
	
	22_5.	
			1.and，&&		需要同时满足多个条件时使用
	
			2.or，||		需要满足多个条件中某一个条件时使用
				多个or用in ，例子：select sal,comm from emp where ename in('james','king','ford');
				
				in ,not in
			
			3.between x and y   (包括xy)
				例子：select * from emp where sal>=1000 and  sal<=2000;  
					  select * from emp where sal between 1000 and 2000;
					  
			4.模糊语句	
			like , not like	 _  表示单个未知， % 表示0个或多个未知	
			SELECT 字段 FROM 表 WHERE 某字段 Like '条件'
			
				4.1 [ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个
				比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'
				将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

				如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
				SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
				将找出“老1”、“老2”、……、“老9”；

				4.2 [^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

				比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'
				将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

	
	22_6.	排序
			order by 字段名 asc/desc;	----asc升序，desc降序
				例子：查询所有员工信息按照部门编号升序排序，工资降序。
					  SELECT * FROM emp ORDER BY deptno,sal DESC;
	
	
	22_7.	分页查询
			limit 跳过的条数，请求的条数（每页的条数）
			
			例子：1.查询员工表工资最高的前五条数据
					select * from emp order by sal desc limit 0,5;
					第二页数据
					select * from emp order by sal desc limit 5,5;
					
			limit (页数-1)*每条的条数，每页的条数
			
			
	22_8.concat()函数 -----可以将字符串进行拼接
		1.1例子：
			查询商品表，显示商品名称，单价（价格：xx元）
			select title,concat('价格:',price,'元') from t_item;
			
			
	22_9.计算 +  -  *  /   %,mod()
		2.1例子：
			查询员工的姓名，工资，年终奖（年终奖=工资*5）
			select ename,sal,sal*5 年终奖 from emp;
			
			
	22_10.日期相关函数
		1.获取系统当前日期+时间
			select now();
			
		2.获取当前年月日  和  时分秒
			select curdate(),curtime();
	
		3.从完整的年月日时分秒中 提取年月日 和 提取时分秒
			select date(now());
			select time(now());
			
		4.从完整的年月日时分秒中提取时间分量 extract
			select extract(year/month/day/hour/minute/second from now());
			
		5.日期格式化 date_format()
			date_format(时间,格式);
			%Y 四位年	%y 两位年	%m 两位月	%c 一位月	%d 日
			%H 24小时	%h 12小时	%i 分	%s 秒
			
			例子：把默认的时间格式转为 年月日时分秒
			select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');
		
		6.把非标准时间格式转出标准格式 str_to_date();
			str_to_date(字符串时间，格式);
			select str_to_date('14.08.2019 08:00:00','%d.%m.%Y %H:%i:%s');
	
	22_11.分组查询
		1.group by 字段名,字段名	----（要对谁进行分组就写谁的字段名）			
		
		1-1例子：
			1.查询emp表中每个部门的编号，人数，工资总和，最后根据部门分组且根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
			    select deptno,count(*),sum(sal) from emp group by deptno order by count(*),sum(sal) desc;
				加别名:select deptno,count(*) c ,sum(sal) s from emp group by deptno order by c,s desc;
			  
			2.查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
				select deptno,avg(sal),min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by avg(sal);
			  
			3.查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排序，如果人数一致，根据平均工资进行升序排序。
				select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null group by job order by c desc,a;
			  
			4.查询每个部门每个主管的手下人数。
				select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;
			  
			5.查询每个部门的平均工资，要平均工资大于2000。
				select deptno,avg(sal) a from emp group by deptno having a>2000;
				(having后面可以写普通字符条件，但是不建议这么做，having一般要和分组查询结合使用，后面写聚合函数的条件，having写在分组查询的后面)
			  
			6.查询每个分类category_id的平均单价，要求平均单价低于100
				select category_id,avg(price) a from t_item group by category_id having a<100;
				
			7.查询分类category_id为238和917的平均单价
				select category_id,avg(price) a from t_item where category_id in(238,917) group by category_id;

			8.查询emp表中每个部门的平均工资高于2000的部门变你好，部门人数，平均工资，最后根据平均工资降序排序
				select deptno,avg(sal),count(*) a from emp group by deptno having a>2000 order by a desc; 
			
			9.查询emp表中工资1000~3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资进行升序排序
				select deptno,sum(sal),avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a;

			10.查询emp表中职位不是以s开头，每个职位的名字，人数，工资总和，最后工作，过滤掉平均工资3000的职位，根据人数升序排序，如果人数一致则工资总和降序排序。
				select ename,count(*) c,sum(sal) s,max(sal) a from emp where job not like 'S%' group by job having avg(sal)!=3000 order by c,s desc;
			
			11.查询emp表中每年入职的人数
				SELECT EXTRACT(YEAR FROM HIREdate) YEAR,COUNT(*) FROM emp GROUP BY YEAR;
			
			12.查询每个部门的最高平均工资
				select deptno,avg(sal) a from emp group by deptno order by a desc limit 0,1;
			
	22_12.子嵌套语句
			1.1例子：
				1.查询emp中工资最高的员工信息
					select * from emp where sal=(select max(sal) from emp);
				
				2.查询emp表中工资大于平均工资的所有员工的信息
					select * from emp where sal>(select avg(sal) from emp);


				3.查询工资高于20号部门的最高工资的员工信息。
					select * from emp where sal>(select max(sal) from emp where deptno=20);

				4.查询和Jones相同工作（job）的其他员工信息
					select * from emp where job=(select job from emp where ename='jones') and ename!='jones';

				5.查询工资最低的员工的同事们的信息（同事=相同job，即：相同工作的员工信息）
					select min(sal) from emp
					select job form emp where sal=(select min(sal) from emp)
				
					select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
				
				6.查询最后入职的员工信息
					select max(hiredate) from emp;
					
					select * from emp where hiredate=(select max(hiredate) from emp);

				7.查询名字为King的部门编号和部门名称
					select deptno,dname from dept where deptno=(select deptno from emp where ename='King');

				8.查询有员工的部门信息
					select distinct deptno from emp;
					
					select * from dept where deptno in(select distinct deptno from emp);
				
				9.查询平均工资最高的部门信息
					9.1先找到最高平均工资
					select avg(sal) a from emp group by deptno order by a desc limit 0,1;
					9.2通过最高平均工资得到部门编号
					select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
					9.3通过部门编号得到部门信息
					select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));
				
	22_13.子查询创建表
			1.例子：
				1.1：create table emp_10 as (select * from emp where deptno=10);	-----把查询到的数据存到一个新创建的数据表
				
	22_14.子查询虚拟表(当成虚拟包必须有别名)
			1.例子：
				1.1：select ename from (select * from emp where deptno=10) newtable;

	22_15.关联查询：等值连接和内连接，外连接(同时查询多张表的数据的查询方式)
		1.等值连接
			格式：select * from A,B where A.x=B.x and A.age=10;
			 1.1例子：
				1.查询每个员工的姓名和所属部门的名字
				select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
				
				2.查询部门地点在new york的 部门名称以及该部门下所有的员工姓名
				select d.dname,e.ename from emp e,dept d where e.deptno=d.deptno and d.loc='new york';
		2.内连接
			格式：select * from A join B on A.x=B.x where A.age=10;
			2.1例子：
					select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
		
		3.外连接(左外，右外)
			格式：select * from A left/right join B on A.x=B.x where A.age=18;
			3.1例子：查询所有的部门名称和对应的员工姓名（外连接）
					select d.dname,e.ename
					from emp e right join dept d on e.deptno=d.deptno;
					
		总结：如果查询的数据时两张表的交集数据
	
	22_16.组连接 group_concat
		1).例子一：查询每个部门的员工姓名和对应的工资，要求一个部门的信息显示到一行
			 select deptno,group_concat(ename,':',sal) from emp group by deptno;
			 
		2).例子二：
			create table student(id int primary key auto_increment, name varchar(10),subject varchar(10),score varchar(10));

		insert into student values(null,'张三','语文','66'),(null,'张三','数学','77'),(null,'张三','英语','55'),(null,'张三','计算机','81'),(null,'李四','语文','59'),(null,'李四','数学','88'),(null,'李四','英语','78'),(null,'张三','计算机','95'),(null,'王虎','语文','75'),(null,'王虎','数学','54'),(null,'王虎','英语','89'),(null,'王虎','计算机','75');

		-- 查询每个人的平均分，从大到小排序
		select name '姓名',avg(score)'平均分'  from student group by name order by '平均分';

		-- 每个人的名字，科目，成绩 一行显示出来
		select name,group_concat(subject,':',score) 各科成绩 from student group by `name`;

		-- 查询每个人的最高分和最低分
		select name,max(score)最高分,min(score)最低分 from student group by name;

		-- 查询每个人不及格的科目以及分数，不及格的科目数量
		select name 姓名,group_concat(subject,':',score)不及格科目,count(*)门数 from student where score<60 group by name; 
		
		
23.修改数据
	update 表名 set 字段名=值 where 条件;
	例子：update person set name='bbb' where name='xxx';

24.删除数据
	delete from 表名 where 条件;


25.解决中文字段出现乱码
	set 字段名(中文varchar) gbk;

---

四、约束

1.主键约束
主键：用于表示数据唯一性的字段称为主键。-----create table t1(id int primary key,name varchar(10));
约束：就是创建表的时候给字段添加的限制条件
2.主键约束：插入数据必须是唯一且非空的
	create table t1(id int primary key,name varchar(10));
	insert into t1 values(1,'刘备');
	insert into t1 values(1,'刘备’);	//报错 不能重复
	insert into t1 values(null,'刘备'); //报错 不能为null
  
  
3.删除主键(若有自增auto_increment,先删除自增auto_increment，再删除主键)
	alter table 表名 change 字段 字段 类型;
	alter table 表名 drop primary key;
	
	添加主键(在添加主键之前,必须先把重复的id删除掉。)
	alter table 表名 add primary key(字段);
	
	alter table 表名 modify 字段 类型 auto_increment primary key; ------修改字段为自增，并设置为主键  

	
4.非空约束：
	字段的值不能为null
	create table 表名 (id int, name varchar(10)not null);
	

5.唯一约束 unique
	字段的值不能重复
	create table 表名 (id int, age int unique);


6.默认约束 default
	给字段设置默认值
	create table 表名 (id int, age int default 20);
	insert into 表名 values(1,10);
	insert into 表名 (id) values(2); --------默认值生效

7.外键约束
	外键：用于建立关系的字段
	外键约束：为了保证两张表之间建立正确的关系，外键字段的值可以为null，可以重复，
	不能是另外一张表中不存在的数据，建立好关系后被依赖的数据不能先删除，
	被依赖的表不能先删除。
	
	格式：constraint 约束名称 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名);

---

五、主键约束+自增

自增数只增不减
从历史最大值基础上+1
1.create table t2(id int primary key auto_increment,name varchar(10));
  insert into t2 values(null,'猪八戒');//1
  insert into t2 values(null,'猪八戒');//2
  
  insert into t2 values(10,'猪八戒');//10
  insert into t2 values(null,'猪八戒');//11

---

六、注释

create table t3(id int primary key auto_increment comment '主键字段',name varchar(10) comment '这是姓名');

---

七、事务

事务是数据库中执行同一业务多条sql语句的工作单位，可以保证多条SQL语句全部执行成功或者全部执行失败，不会出现部分成功部分失败的执行。

事务相关指令：
1.开始事务 begin;
2.提交事务 commit;
3.回滚事务 rollback;
4.保存回滚点 savepoint;

---

八、数据库数据类型

	1.整数型
		int(m)   ----m表示显示长度，需要结合zerofill关键字使用。
		bigint(m) -----   create table t_int(id int(5) zerofill);
						  insert into t_int values(18);

	2.浮点数
		double(m,d) ---m代表总长度  ---d代表小数长度  25.321 m=5 d=3
		decimal超高度精度浮点数，当涉及超高精度运算时使用
		
	3.字符串
		char(m) 固定长度 执行效率高 最大长度255
		varchar(m)可变长度 节省资源 最大65535 超高255位建议使用text， text可变长度 最大65535
	
	4.日期
		date 只能保存年月日
		time只能保存时分秒
		datetime最大值9999-12-31  默认值为null
		timestamp 最大值2038-1-19 默认值 当前系统时间

---

九、数据库导入导出

	导入：source e:/MySQL/data/tables.sql     -----------（windows系统）

---

十、IFNULL() 函数

10.1. IFNULL() 函数用于判断第一个表达式是否为 NULL，
如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

ifnull();
	age = ifnull(x,y); 如果x值为null则age=y 如果x不为null则age=x
	例子：把emp数据表中的奖金为null的改为0
		  update emp set comm = ifnull(comm,0);

---

十一、聚合函数（不能写在where条件后面）

	求和 平均数 最大值 最小值 计数
	
	1.求和：sum
		sum(求和的字段)
		
	2.平均值：avg
		avg(字段)
		
	3.最大值：max
		max(字段)
	
	4.最小值：min
		min(字段)
	
	5.计数：count
		count(字段)
		一般写count(*)，只有涉及null值时才使用字段名

---

十二、字符串相关函数(从1开始)

	1.char_length(str)获取字符串的长度
		select ename,char_length(ename) from emp;
	
	2.instr(str,substr) 获取substr在str中出现的位置 从1开始
		select instr('abcdefg','d');
	
	3.insert(str,start,length,newstr); 插入
		select insert('abcdefg',3,2,'m');
	
	4.lower(str) upper(str)   转小写  转大写
		select lower('NBa'),upper('NBa');
	
	5.trim(str) 去两端空白
		select trim('   a   b    ');
		
	6.left(str,index) 从左边截取
	7.right(str,index) 从右边截取
	8.substring(str,index,?length) 从指定位置截取
		select left('abcdef',2);
		select right('abcdef',2);
		select substring('abcdef',3,2);
		select substring('abcdef',3);
		
	9.repeat(str,count) 重复
		select repeat('ab',3);	//ababab
		
	10.replace(str,old,new) 替换
		select replace('This is mysql','my','your');	//This is yoursql
		
	11.reverse() 反转
		select reverse('abc');	//cba

---

十三、数学相关的函数

	1.floor(num) 向下取整
		select floor(3.84);	//3
	
	2.round(num) 四舍五入
		select round(3.8);	//4
	
	3.round(num,m) 四舍五入并保留几位小数  （m代表保留几位小数）
		select round(3.8679,2);	//3.87
		
	4.truncate(num,m) 非四舍五入并保留几位小数	（m代表保留几位小数）
		select truncate(3.8679,2); //3.86

	5.rand() 随机数0~1
		例子1：获取0~5的随机整数
				select floor(rand()*6);
		例子2：获取0~10的随机整数
				select floor(rand()*11);

---

十四、分类

	1.DDL数据定义语言： create drop alter truncate 不支持事务
	2.DML数据操作语言： insert delete update select 支持事务
	3.DQL数据查询语言： select
	4.TCL事务控制语言： begin commit rollbakc savepoint
	5.DCL数据控制语言： 分配用户权限相关SQL

---

十五、视图

	一、试图和表都是数据库中的对象，视图可以理解为一张虚拟的表，视图本质就是取代了一段SQL查询语句。
			作用：可以起到SQL语句重用的作用，提高开发效率，还可以隐藏敏感信息。
		1.创建视图
			create view 视图名 as (子查询);
			
		2.删除视图
			drop view 视图名
		
		3.修改视图
			create or replace view 视图名 as (子查询);
		
		4.视图别名
			如果创建视图的子查询中使用了别名，那么对视图进行操作时只会使用别名
		
		
		3.例子：
			create view v_emp_30 as (select ename name,sal from emp where deptno=30);
		
	二、视图的分类：
		1.简单视图：创建视图时的子查询不包含：去重、函数、分组、关联查询创建的视图称为简单视图，可以对简单视图进行增删改查操作
		2.复杂视图：和简单视图相反，只能对复杂视图进行查询操作。

	三、数据污染：往视图中插入一条视图中不可见但是在原表中可见的数据称为数据污染
				  通过with check option 关键字避免出现数据污染现象。

---

十六、索引

一、索引是数据库中用于提高查询效率的技术，工作原理类似目录
	有利也有弊，如果数据量比较小，有索引反而会降低查询效率
	
		1.创建索引
			格式：create index 数据库名 on 表名(字段名(?字段长度));
				create index i_item_title on item2(title);
				select * from item2 where title='100';
					
			索引越多，不好，因为索引会占磁盘空间。
			
		2.查看索引
			格式：show index from 表名;
				show index from item2;
				
			3.删除索引
				格式：drop index 索引名 on 表名;
					drop index i_item_title on item2;
			
			4.复合索引
				通过多个字段创建的索引称为复合索引
				create index i_item_title_price on item2(title,price);

---

十七、事务

	一、数据库中执行同一业务多条sql语句的工作单元，可以保证多条sql全部执行成功或全部执行失败
	
	1）事务的ACID特性，此特性是保证事务正确执行的四大基本元素
		Atomicity：		原子性：最小不可拆分，保证全部成功或全部失败
		Consistency：	一致性：从一个一致性状态到另外一个一致性状态
		Isolation：		隔离性：多个事务之间互不影响
		Durability：	持久性：提交事务后数据持久保存在磁盘中
		
	2）事务相关指令：
			1.开始事务 begin;
			2.提交事务 commit;
			3.回滚事务 rollback;
			4.保存回滚点 savepoint;