MySql数据库基础知识详解

1.数据库相关

a.查询所有数据库
show databases;
b.创建数据库
create database 库名/(db1);
c.查看创建的数据库详情
show create database db1;
d.创建数据库时指定字符集
create database db2 character set gbk/utf8;
e.删除数据库
drop database db2;
f.使用数据库
use db1;

2.表相关

a.查询所有表
show tables;
b.创建表
create table 表名(字段1名 字段1的类型,字段2名 字段2的类型,.....); 
create table person(name varchar(10),age int); 
创建一个学生表（student） 保存学号id,姓名name,年龄age,语文chinese,数学math,英语english 
create table student(id int,name varchar(10),age int,chinese int,math int,english int);
c.查看创建的表详情
show create table 表名; show create table person;
d.创建表时指定表的引擎和字符集
create table 表名(name varchar(10)) engine=myisam charset=gbk;
表的引擎
innodb：支持数据库的高级操作如：外键、事务等，默认引擎
myisam：只支持基础的增删改查操作

创建表 create table t1(name varchar(10),age int) engine=myisam/innodb charset=gbk/utf8;
查询所有表 show tables;
查看表详情 show create table t1;
查看表字段 desc t1;
删除表 drop table t1;

修改表：

a.修改表名
rename table 原名 to 新名; rename table student to stu;
b.修改表的引擎和字符集
alter table 表名 engine=myisam/innodb charset=utf8/gbk; 
alter table stu engine=myisam charset=gbk;
c.添加表字段
最后面： alter table 表名 add 字段名 字段类型;
最前面： alter table 表名 add 字段名 字段类型 first;
xxx的后面: alter table 表名 add 字段名 字段类型 after xxx; 
create table hero(name varchar(10)); 
alter table hero add age int; 
alter table hero add id int first; 
alter table hero add sal int after name;
d.删除表字段
alter table 表名 drop 字段名; 
alter table hero drop sal;
e.修改表字段的名字和类型
alter table 表名 change 原字段名 新字段名 新字段类型; 
alter table hero change name heroname varchar(5);
f.修改表字段的类型和位置
alter table 表名 modify 字段名 类型 位置; 
alter table hero modify age int first/(after xxx);

3.数据相关

插入数据 insert into 表名 (name,age) values(值1,值2),(值1,值2),(值1,值2);
查询数据 select name,age from 表名 where id<5;
修改数据 update 表名 set age=18 where id=5;
删除数据 delete from 表名 where id=3;

a.插入数据

create table 表名(id int,name varchar(10),age int,sal int);	//创建表
全表插入数据：
insert into 表名 values(1,'Tom',18,3000);
指定字段插入数据：
insert into 表名 (name,age) values('Jerry',19);
insert into emp (name) values('李白');
批量插入数据：
insert into emp values(3,'刘备',28,6000),(4,'张飞',20,5000),(5,'关羽',25,9000);
指定字段批量插入数据： 
insert into emp (name,age) values('悟空',500),('八戒',400),('沙僧',200);

b.查询数据

查询全部数据的全部字段信息 
select * from 表名;
查询所有员工的姓名和年龄 
select name,age from emp;
查询年龄在25岁以下的员工信息 
select * from emp where age<25;
查询工资3000块钱的员工姓名、年龄、工资 
select name,age,sal from emp where sal=3000;

c.修改数据

修改Tom的工资为3333 
update 表名 set sal=3333 where name='Tom';
修改30岁以下的工资为666 
update emp set sal=666 where age<30;
修改id等于3的名字为吕布 年龄为55 工资为20000 
update emp set name='吕布', age=55, sal=20000 where id=3;
修改工资为null的工资为800 
update emp set sal=800 where sal is null;

d.删除数据

删除全部数据 
delete from 表名;
删除id=1的员工 
delete from emp where id=1;
删除年龄在25岁以下的员工 
delete from emp where age<25;

4.主键：primary key,自增：auto_increment

create table t2(id int primary key auto_increment,name varchar(10));

5.注释 comment

create table t3(
	id int primary key auto_increment comment '这是一个主键',
	name varchar(10) comment '这是员工的姓名'
	);

6.`和’

`:用于修饰表名和字段名 可以省略。create table `t4`(`id` int,`name` varchar(10));
':用于修饰字符串

7.事务

查看数据库自动提交的状态 show variables like '%autocommit%';
关掉自动提交 0关闭 1开启 set autocommit=0;
执行提交 commit;
回滚 rollback; -回滚到某点：	rollback to s1;
将内存中的修改回滚到上次提交（commit）的点 update person set money=100 where id=1; rollback;
保存回滚点 :	savepoint s1; update person set money=100 where id=1; savepoint s1; update person set money=200 where id=1; savepoint s2; rollback to s1;

8.删除表

truncate table 表名;
删除表并且创建一个新表
truncate、drop和delete的区别：
delete用于删除数据，使用delete清空表时自增数值不清零 执行效率最低
drop 用于删除表 执行效率最高
truncate 用于删除表并创建新的空表，执行效率比delete要高，而且自增数值会清零

9.数据库的数据类型

a.整数
常用整数有 int(m)和bigint(m)，m代表显示长度必须和zerofill结合使用 
create table t_int(num int(10) zerofill); 
insert into t_int values(123); 
select * from t_int;
b.浮点数
常用浮点数double(m,d) m代表总长度 d代表小数长度 23.346 m=5 d=3
decimal超高精度浮点数，应用场景：涉及超高精度运算时使用 
create table t_double(num double(5,3)); 
insert into t_double values(23.5678); 值为23.568 
insert into t_double values(23.5); 值为23.500
c.字符串
char(m): 固定长度 m=10 abc 占10，效率高，最大255
varchar(m):可变长度 m=10 abc 占3，节省空间，最大65535，
如果超过255建议使用text
text(m):可变长度 最大65535
d.日期	
date:只能保存年月日
time:只能保存时分秒
datetime:保存年月日时分秒，默认值为null，最大值9999-12-31
timestamp(时间戳距离19700101 08：00：00):保存年月日时分秒，默认值为当前系统时间,最大值2038-01-19 
create table t_time(t1 date,t2 time,t3 datetime,t4 timestamp); 
insert into t_time values('2018-10-16',null,null,null); 
insert into t_time values(null,'20:06:32','2008-11-22 18:22:11',null);

10.is null 和 is not null

查询emp表中没有上级领导mgr的员工编号empno，姓名ename，工资sal 
select empno,ename,sal from emp where mgr is null;
查询emp表中没有奖金comm的员工姓名，工资，奖金 
select ename,sal,comm from emp where comm is null;
查询有奖金的所有员工信息 
select * from emp where comm is not null;

11.别名

将查询到的员工姓名ename改成‘姓名’
 select ename from emp; 
 select ename as '姓名' from emp; 
 select ename '姓名' from emp; 
 select ename 姓名,sal 工资 from emp;

12.去重 distinct

查询emp表中出现的所有职位job 
select distinct job from emp;

13比较运算符 >,<,>=,<=,=,!=和<>

查询工资小于等于1600的所有员工的姓名和工资 
select ename,sal from emp where sal<=1600;
查询部门编号是20的所有员工姓名、职位和部门编号deptno 
select ename,job,deptno from emp where deptno=20;
查询职位是manager的所有员工姓名和职位 
select ename,job from emp where job='manager';
查询部门不是10号部门的所有员工姓名和部门编号(两种写法) 
select ename,deptno from emp where deptno!=10; 
select ename,deptno from emp where deptno<>10;
查询商品表t_item中单价price等于23的商品信息 
select title,price from t_item where price=23;
查询商品表中单价不等于8443的商品信息 
select * from t_item where price!=8443;

14and 和 or

and等效java中的&&
or等效java中的||
查询工资大于2000并且是10号部门的员工信息	
select * from emp where sal>2000 and deptno=10;
查询不是10号部门并且工资大于等于1600的员工姓名，工资，部门编号 
select ename,sal,deptno from emp where deptno!=10 and sal>=1600;
查询部门是30号部门或者上级领导mgr为7698的员工姓名,职位，上级领导和部门编号 
select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;
查询有上级领导并且工资低于2000并且是20号部门的员工信息 
select * from emp where mgr is not null and sal<2000 and deptno=20;

15.in not in()

查询emp表中工资是5000，1500，3000的员工信息 
select * from emp where sal=5000 or sal=1500 or sal=3000; 
select * from emp where sal in(5000,1500,3000);

16.between x and y 包含x和y（之间） not between x and y

查询工资在2000到3000之间的员工姓名和工资	
select ename,sal from emp where sal>=2000 and sal<=3000; 
select ename,sal from emp where sal between 2000 and 3000;

17.模糊查询 like not like

_:代表单个未知字符
%：代表0或多个未知字符
举例：
以a开头的字符串 a%
以m结尾 %m
包含x %x%
第二个字符是a _a%
倒数第三个字母是m %m__
以a开头并且倒数第二个字母是b a%b_

18.排序 order by

order by 关键字， by后面写排序的字段名称 默认是升序，
asc升序 desc降序
查询所有员工的姓名和工资按照工资升序排序 
select ename,sal from emp order by sal;
查询10号部门的所有员工信息，按照工资降序排序 
select * from emp where deptno=10 order by sal desc;

*19.分页查询 limit （页数-1）条数

limit 跳过的条数,请求的数量（每页的数量）
查询员工表中工资降序的前5条数据 
select * from emp order by sal desc limit 0,5;

20.concat()拼接函数

把concat内部的参数拼接到一起
查询员工姓名和工资，要求工资单位是元
select ename,concat(sal,'元') 工资 from emp;

21.数值计算 + - * / %（mod(7,2)等效 7%2）

查询商品表中每个商品的单价，库存及总价值（单价库存） 
select price,num,pricenum from t_item;
查询员工表中每个员工的姓名，工资，及年终奖(五个月的工资) 
select ename,sal,sal*5 年终奖 from emp;

22.日期相关函数

获取当前的年月日时分秒 select now();
获取当前的日期 current select curdate();
获取当前的时间 select curtime();
从年月日时分秒中提取年月日 select date(now());
从年月日时分秒提取时分秒 select time(now());
从年月日时分秒中提取时间分量 年 月 日 时 分 秒
extract(year from now())
extract(month from now())
extract(day from now())
extract(hour from now())
extract(minute from now())
extract(second from now()) select extract(year from now());
查询员工表中的所有员工姓名和入职的年份 
select ename,extract(year from hiredate) from emp;
日期格式化 date_format(时间,格式)
%Y:四位年 2018
%y:两位年 18
%m:两位月 05
%c:一位月 5
%d:日
%H: 24小时
%h: 12小时
%i: 分
%s: 秒 
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');
查询商品名称和商品的上传日期(格式：x年x月x日) 
select title,date_format(created_time,'%Y年%m月%d日') from t_item;
把非标准的日期字符串转成标准的时间格式 str_to_date(时间字符串,格式) 14.08.2018 08:00:00 
select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s');

23.ifnull(x,y)函数

age=ifnull(x,18) 如果x的值为null 则age=18 如果不为null则 age=x
修改员工表中奖金为null的值为0 
update emp set comm=ifnull(comm,0);

24.聚合函数

用于对多条数据进行统计
求和 sum(字段名)
查询emp中10号部门的工资总和 
select sum(sal) from emp where deptno=10;
平均值 avg(字段名)
查询emp表中所有员工的工资平均值 
select avg(sal) from emp;
最大值 max(字段名)
查询30号部门的员工的最高奖金 
select max(comm) from emp where deptno=30;
最小值 min(字段名)
查询商品表中价格最便宜的商品的单价 
select min(price) from t_item;
统计数量 count(字段名) 一般使用count(*)
统计30号部门有多少人 
select count(*) from emp where deptno=30;

25.和字符串相关函数

获取字符串的长度 char_length(str); 获取所有员工的姓名和姓名的字符长度 
select ename,char_length(ename) from emp;
获取字符串在另外一个字符串中出现的位置 instr(str,substr) 
select instr('abcdefg','d');//4
插入字符串 
insert(str,start,length,newStr) select insert('abcdefg',3,2,'m'); //abmefg
转大写 转小写 select upper('abc'),lower('NBA');
左边截取和右边截取 select left('abcdefg',2) , right('abcdefg',2);//ab@fg
去两端空白 select trim(' a b ');
截取字符串 select substring('abcdefg',3,2);//cd
重复 repeat(str,count) select repeat('ab',2); //abab
替换 replace(str,old,new) select replace('This is mysql','my','your');//This is yoursql
反转 reverse(str) select reverse('abc');//cba

26.数学相关函数

向下取整 floor(num) 
select floor(3.84); //3
四舍五入 round(num); 
select round(3.84); // 4 -round(num,m) m代表小数位数 select round(3.84567,3); //3.846
非四舍五入 truncate(num,m) select truncate(3.84567,3);//3.845
随机数 rand() 0-1 5-10 0-5 select floor(rand()*6) + 5;//5-10的随机数 3-8 0-5 select floor(rand()*6) +3;//3-8的随机数

27.分组查询 group by 字段名,名段字

分组查询通常和聚合函数结合使用，以组为单位进行统计
一般情况下，题目中每个xxx 就在group by后面写xxx
如果需要使用多个字段进行分组 直接在group by 后面写多个字段名通过逗号分隔
查询每个部门的最高工资 
select deptno,max(sal) from emp group by deptno;

28.having

where后面只能写普通字段的条件不能写聚合函数

having后面可以写普通字段条件也可以写聚合函数，但是推荐在having后面只写聚合函数

having写在group by 的后面

select * from 表名 where ..... group by xxx having .... order by ..... limit ...;

查询每个部门的平均工资，要求平均工资大于2000 
select deptno,avg(sal) a from emp group by deptno having a>2000;
查询每个分类的平均单价，过滤掉平均单价低于100的。 
select category_id,avg(price) a from t_item group by category_id having a>=100;

29.子查询

子查询可以写在什么位置

写在where或having后面 当做查询条件的值
写在创建表的时候 把查询结果保存成一张新的表 
create table emp_20 as (select * from emp where deptno=20);
写在from后面 当成一个虚拟表 必须有别名 
select * from emp where deptno=20; 
select ename,sal from (select * from emp where deptno=20) newtable;

30.关联查询

同时查询多张表的数据称为关联查询

a.等值连接和内连接

等值连接和内连接查询到的内容一样，都为两张表中有关联关系的数据(交集部分)

等值连接： select * from A,B where A.x=B.x and A.age=18;

内连接： select * from A [inner] join B on A.x=B.x where A.age=18;

查询每一个员工的姓名和对应的部门名称 
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
外连接

内连接和等值连接查询到的是交集部分的数据，外连接查询到的是某一张表的全部数据+另外一张表的交集数据
左/右外连接： 
select * from A left/right join B on A.x=B.x where A.age=18; 
insert into emp (empno,ename) values(10010,'Tom');
查询所有员工姓名和对应的部门名称 
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;

关联查询总结

关联查询的查询方式： 等值连接 内连接和外连接
如果想查询的数据为两张表的交集数据使用等值连接或内连接(推荐)
如果查询的数据是一张表的全部数据和另外一张表的交集数据则使用外连接

31.表设计之关联关系

a.一对一

什么是一对一关系： 有AB两张表，其中A表的一条数据对应B表的一条数据，同时B表的一条数据也对应A表中的一条数据
应用场景： 用户表和用户信息扩展表、商品表和商品信息扩展表
外键： 表中用于建立关系的字段称为外键，一张表有可能有多个外键，但只会有一个主键
如何建立关系：在从表中添加外键指向主表的主键

b.一对多

什么是一对多：有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条
应用场景： 用户表和部门表，商品表和分类表
如何建立关系：在多的一端添加外键指向另外一张表的主键

c.多对多

什么是多对多：有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的多条
应用场景： 老师表和学生表
如何建立关系：通过第三张关系表保存两张主表的关系

32.自关联

在当前表中添加外键，外键的值指向当前表的主键，这种关联方式称为自关联 
create table person(id int primary key auto_increment,name varchar(10),mgr int); 
保存以下数据： 如来->唐僧->悟空->猴崽子 
insert into person values(null,'如来',null),(null,'唐僧',1),(null,'悟空',2),(null,'猴崽子',3);
查询每个人的名字和上级的名字 
select p.name,m.name 上级 from person p left join person m on p.mgr=m.id;

33.视图

数据库中包含多种对象，表和视图都是数据库中的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一段sql查询语句

为什么使用视图：因为有些数据的查询需要写大量的SQL语句，每次书写比较麻烦，通过使用视图相当于把当了的SQL查询语句进行保存，下次从视图中查询就不用再次书写大量SQL语句，从而提高开发效率。隐藏敏感字段

视图格式： 
create view 视图名 as (子查询); 
create view v_emp_10 as (select * from emp where deptno=10); 
delete from emp where sal=1300; 
select * from v_emp_10;

视图的分类：
简单视图：创建视图的时候不包含：去重、分组、函数、关联查询的视图称为简单视图，可以对视图中的数据进行增删改查
复杂视图：和简单视图相反，只能进行查询操作

简单视图的增删改操作 操作方式和操作table一样
插入数据 
create view v_emp_10 as (select * from emp where deptno=10); 
insert into v_emp_10 (empno,ename,deptno) values (10011,'悟空',10); insert into v_emp_10 (empno,ename,deptno) values (10012,'八戒',20);(数据污染)
往视图中插入一条在视图中不显示但是在原表中显示的数据，称为数据污染
通过 with check option 关键字解决数据污染问题 
create view v_emp_20 as (select * from emp where deptno=20) with check option; 
-测试： 
insert into v_emp_20 (empno,ename,deptno) values (10013,'刘备',20); //成功 
insert into v_emp_20 (empno,ename,deptno) values (10014,'张飞',30); //失败
删除和修改：只能操作视图中存在的数据
别名：如果创建视图时使用别名 则 操作视图时只能使用别名 
create view v_emp_30 as (select ename name from emp where deptno=30); 
select * from v_emp_30 where ename='james';

视图总结：

视图时数据库中的对象，可以理解成一张虚拟的表，本质就是一段SQL语句
作用： 重用SQL，隐藏敏感字段
分类：简单视图(不包含去重 分组 函数 关联查询，可以增删改查)和复杂视图(反之，查询)
通过with check option 解决数据污染
删除和修改时 只能操作视图中存在的数据
起了别名 只能用别名、

34.约束

约束： 约束是创建表时给字段添加的限制条件

a.非空约束 not null

字段值不能为null 
create table t1(id int,age int not null); 
insert into t1 values(1,18);//成功 
insert into t1 values(2,null);//失败

b.唯一约束 unique

字段的值不能重复 
create table t2(id int,age int unique); 
insert into t2 values(1,20);//成功 
insert into t2 values(2,20);//失败

c.主键约束

字段的值唯一且非空
创建表时添加主键约束: 
create table t3(id int primary key,age int);
创建表之后添加主键约束： 
create table t3(id int,name varchar(10)); 
alter table t3 add primary key(id);
删除主键约束 
alter table t3 drop primary key;

d.自增

数值只增不减
从历史最大值的基础上+1
字段值赋值为null的时候自动+1
使用delete 删除全表数据 自增数值不变
使用truncate 自增清零

e.默认约束 default

给字段添加默认值,当插入数据不给该字段赋值时 默认值生效 
create table t4(id int,age int default 10); 
insert into t4 values(1,20); 
insert into t4 values(2,null); 
insert into t4 (id) values(3);//默认值生效

f.检查约束 check

语法支持,但是没有效果. 
create table t5(id int,age int check(age>10)); 
insert into t5 values(1,5);

g.外键约束

外键约束作用：为了保证两个表之间的关系正确建立
插入数据时外键值可以为null，可以重复，但是不能是另外一张表不存在的数据
被依赖的表不能被先删除
被依赖的数据不能先删除

如何使用外键：
创建部门表 
create table dept(id int primary key auto_increment,name varchar(10));
创建员工表 
create table emp(id int primary key auto_increment,name varchar(10),deptid int, constraint fk_dept foreign key(deptid)references dept(id));
介绍： constraint 约束名称 foreign key(外键字段名)references 主表名(字段名)
测试： 
insert into dept values(null,'神仙'),(null,'妖怪'); 
insert into emp values(null,'悟空',1);//成功 
insert into emp values(null,'八戒',1);//成功 
insert into emp values(null,'超人',3);//失败 
drop table dept;//失败	
delete from dept where id=2;//成功 
delete from dept where id=1;//失败

35.索引

什么是索引： 索引是数据库中提高查询效率的技术，类似于字典的目录
为什么使用索引：如果不使用索引数据会零散的保存在每一个磁盘块当中，查询数据时需要挨个的遍历每一个磁盘块查找数据，如果数据量超级大，遍历每一个磁盘块是件非常耗时的事情，添加索引后，会将磁盘块以树桩结构进行保存，查询数据时会有目的性的访问部分磁盘块，因为访问的磁盘块数量降低所以能起到提高查询效率的作用
索引是越多越好吗？ 不是，因为索引会占磁盘空间，通过某个字段创建的索引可能永远用不上，则这个索引完全没有存在的意义，只需要对查询时频繁使用的字段创建索引
有索引就一定好吗？ 不一定，如果数据量小使用索引反而会降低查询效率
索引的分类(了解)
聚集索引(聚簇索引)： 通过主键创建的索引为聚集索引，添加了主键约束的表会自动添加聚集索引，聚集索引的树桩结构中保存了数据
非聚集索引：通过非主键字段创建的索引叫做非聚集索引，树桩结构中只保存了数据所在磁盘块的地址并没有数据。

导入数据：

windows电脑 把文件放到D盘下 source d:/item_backup.sql;
Linux系统 把文件放到桌面 source /home/soft01/桌面/item_backup.sql;

笔记链接地址

链接：https://pan.baidu.com/s/1ZEefptvv4wtn-FAXdbT8FA 
提取码：3m56

后记：

创建数据表
CREATE TABLE t_user (
		uid INT AUTO_INCREMENT COMMENT '用户id',
		username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
		password CHAR(32) NOT NULL COMMENT '密码',
		salt CHAR(36) COMMENT '盐值',
		phone VARCHAR(20) COMMENT '电话号码',
		email VARCHAR(30) COMMENT '电子邮箱',
		gender INT COMMENT '性别:0-女，1-男',
		avatar VARCHAR(50) COMMENT '头像',
		is_delete INT COMMENT '是否删除：0-未删除，1-已删除',
		created_user VARCHAR(20) COMMENT '创建人',
		created_time DATETIME COMMENT '创建时间',
		modified_user VARCHAR(20) COMMENT '修改人',
		modified_time DATETIME COMMENT '修改时间',
		PRIMARY KEY (uid)
	) DEFAULT CHARSET=utf8mb4;
	
	插入数据
	INSERT INTO t_user (
		username, password,
		salt, phone,
		email, gender,
		avatar, is_delete,
		created_user, created_time,
		modified_user, modified_time
	) VALUES (
		#{username}, #{password},
		#{salt}, #{phone},
		#{email}, #{gender},
		#{avatar}, #{isDelete},
		#{createdUser}, #{createdTime},
		#{modifiedUser}, #{modifiedTime}
	)
	
	查询数据
	SELECT
		*
	FROM
		t_user
	WHERE
		uid=#{uid}
		
	修改数据
	UPDATE
		t_user
	SET
		avatar=#{avatar},
		modified_user=#{modifiedUser},
		modified_time=#{modifiedTime}
	WHERE
		uid=#{uid}
		
	删除数据
		DELETE FROM 
		t_address 
	WHERE 
		aid=#{aid}