MySQL学习笔记（看的老杜做的一些笔记）

MySQL一些基本语法

小白做的一些自己学习的MySQL笔记，如有错误请大佬指正

每一句话最后都是以分号结尾，并且不区分大小写（可用来绕过）

show databases; //展示数据库
use sys;//use+数据库的名字，使用某个数据库
create database name；//创造一个叫name名字的数据库
exit//表示退出
show tables; //查看某个数据库里面的表，即当前使用的数据库
select * from tablename;//查看名字叫做tablename的里面的数据,*代表所有
desc tablename;//不看表中的数据只看表的结构
select version();//查看MySQL版本号
select database();//查看当前使用的哪个数据库

数据库中的基本单元是表：table

数据库中是以表格的形式表示数据的，因为表比较直观
任何的一张表都有行和列：
行（row）：被称为数据/记录
列（column）：被称为字段

SQL语句的分类

DQL	数据查询语句（凡是带有select关键字的都是查询语句）select…
DML	数据操作语言（凡是对表中的数据进行增删改的都是）insert delete update
DDL	数据定义语言（凡是带有create drop alert 的都是）操作的是表的结构不是表中数据
TCL	事务控制语言（事务提交commit 回滚 rollback)
数据控制语言	授权grant，撤销权限revoke

导入数据

source filename//加上文件的路径，路径里面不可以有中文，在最后不用加;

DQL查询语句

简单查询 （如果select 跟了一个常量 1 或者’abc‘ 表长是多少就会有多少个这个常量）

select 字段名 from 表名;//用来查询一个字段，select和from都是关键字，而字段名和表名都是标识符
select 字段名,字段名 from 表名;//用来查询多个字段
select * from 表名//查询所有表名
select 字段名 as 别名 from 表名//只是将显示的查询结构列名更改为别名，select的语句的别名不会变,别名可以用单引号括起来

条件查询
不将表中所有的数据查询出来，只查询复合条件的

select 字段名 from 表名 where 条件;
select 字段名 from 表名 where 字段 =;....//查询等于的
select 字段名 from 表名 where 字段 !=(<>);....//查询不等于...的,其他> >= <=等等类似
select 字段名 from 表名 where 字段 between ... and ...;//再两个值之间，等同于>=和<=
select 字段名 from 表名 where 字段 is null; //该字段的值为空（NULL）,is not null 是不为空
select 字段名 from 表名 where 字段条件 and 字段条件;//筛选出复合两个条件的,or是符合其中一个条件的,and的优先级比or要高，会先执行and 会优先执行()里面的
select 字段名 from 表名 where 字段 in (条件,条件); //找出找出符合两个条件的字段，in后面要跟着具体的值
//not 一般可以和in 和is一起使用取反，is not null 或者not in
select 字段名 from 表名 where 字段 like '%条件的字符%'; //%可以代表任意多个字符，而_可以代表任意一个字符例如有o的名字就'%o%'，以t结尾'%t',由于下划线有特殊含义如果要筛选的当中有下划线，就要用\转义

排序（排序总是在最后执行）

select 字段名,字段名from 表名 order by 字段名//前面是选择的字段名后面是要依靠什么字段来排序，例如工资等，默认是升序排序。
select 字段名 from 表名 order by 字段名 desc;//降序
select 字段名 from 表名 order by 字段名,字段名;//多个字段排序，先前面的，如果相同再后面的
select 字段名 from 表名 order by 一个数字;//这个数字代表按照查询结果的那一列来排序

函数

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

函数	功能
lower	转换小写 （select lower(字段名) from 表名）
upper	转换大写
substr	取子串（sbustr(被截取的字符串，起始下标，和截取的长度)），从1开始
length	取长度
trim	去空格 trim(字符串)将前后的空白去掉
str_to_date	将字符串转换成日期
data_format	格式化日期
fromat	设置千分位
round	四舍五入 round(数字,保留多少个小数)
rand()	生成随机数
ifnull	可以将一个null转换成一个具体值（ifnull(数据，被当作哪个值),专门用来处理空的，因为只要有空参与运算结果就是空
concat	进行字符串拼接（select concat(字符串，字符串) from 表名;)）这个在sql注入中会用到
case job when then	select ename,job,case job when ‘MANAGER’ then sal*1.1 else sal end

分组函数/多行处理函数

• 分组函数必须要先分组才能使用，分组函数会自动忽略null count(*)
• 和count(其他字段)的区别后者为统计不为null的总数，但是前者会统计全部 分组函数不能直接使用在where的子句中
• 所有分组函数可以组合起来一起使用

函数	功能
count	计数
sum	求和
avg	求平均值
max	最大值
min	最小值

分组查询（重要）
对数据进行分组，再对每一组的数据进行操作
写的顺序是

1. select
2. from
3. where
4. group by
5. order by

但是执行的顺序是

1. from
2. where
3. group by
4. select
5. order by

select 字段名from 表名 group by 要用来分组的字段名,字段名//按照最后一个字段来分组
//前面的字段只能写分组函数和用来分组的字段，其他的写上了没意义或者会报错
//也可以多个字段一起分组
select 字段名from 表名 group by 要用来分组的字段名 having 想要过滤的条件
//如果分完组之后不满意就可以通过having来进行过滤掉（having后面假的是想留下的条件）
//where和having之间优先选择where

去除重复记录
是把查询结果去除重复记录，原来的结果不会修改

select distinct 字段名 from 数据库名
//distinct只能出现在所有字段的最前方，会将前面两个字段联合起来去除重复记录

连接查询

（重要）
多个表联合起来查数据，被称为连接查询
根据表的连接方式分类

• 内连接：等值连接，非等值连接，自连接
• 外连接：左外连接，右外连接
• 全连接
  当两张表进行连接查询没有任何条件限制的时候，最终所得的查询条数是两张表条数的乘积，这种现象被称为笛卡尔积现象。

内连接

select 表名别名.列名, 表名别名.列名 from 表名 (as) 表别名 join 表名 (as) 表别名 on 连接条件;
//例子：
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
//后面还可以继续添加where过滤

如果条件是等值关系就是等值连接
如果是非等值条件就是非等值连接
如果是自己连接自己就是内连接，可以把一张表看成两张表来做

外连接
内连接是将符合条件的全部查询出来，ab两张表没有主次关系
外连接有分主次的表

select 表名别名.列名, 表名别名.列名 from 表名 (as) 表别名 (left/right)join 表名 (as) 表别名 on 连接条件;
//left 和 right表示的是join左边或者右边的那个表为主表要全部查询出来，捎带着关联查询另一边的表
//例子：
select e.ename,d.dname from emp e  right join dept d on e.deptno=d.deptno;
//表示要将右边的dept这个表中的全部内容都查出来

多表联查

select 字段名 from a join b on 连接条件 join c on a和b查出来后再和b的连接条件 join d on a b c三个联合查完之后和d的查询条件

子查询

在select 中嵌套select语句，被嵌套的语句称为子查询
where子查询

select 字段名，字段名 from 表名 where 查询的语句;
//例子：
select ename,sal from emp where sal >(select min(sal) from emp);
//where后面的条件还可以加上查询

from子查询
from后面的子查询可以把子查询的查询结果当作一张临时表来使用

select 字段名，字段名 from 查询的语句(查询的语句中如果有关键字要起别名后面才能调用的了）;
//例子：
select t.*,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
//中间的括号就是子查询查询出来的表，作为临时表被调用

select子查询

select 字段名,插叙语句 from 数据库
//例子：
select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

union

sql注入可以用到
合并查询的结果

select ... from ... where ... union select ... from... where...;
//例子：
select ename,job from emp where job='salesman' union select ename,job from emp where job='manager';
将两个查询的结果合并

union在进行结果集合并的时候要求两个结果集的列数相同，并且数据类型最好也要相同（否则可能报错）

limit

将查询结果集的一部分取出来，通常使用在分页查询中

select ... from ...  limit 数字,数字;//limit后为开始的位置和取的长度，如果只有一个数字就默认从0开始，和数组一样起始下表为0

在mysql中 limit会在order by 之后执行，也就是先排序在筛选前几

select … from … where… group by … having… order by… limit…
执行顺序

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit…
   以上是DQL查询语句

表的创建

建表属于DDL语句，DDL包括create drop alter

 create table 表名(字段名1 数据类型 default 默认值,字段名2 数据类型,字段名3,数据类型);
 //创造表，每个数据类型后面要给出长度,在数据类型后面加上default就是建表如果不传参的话就是默认的值
 drop table if exists 表名;//删除表,如果表存在删除
 //例子：
 create table t_student(no int,name varchar(32),sex char(1),age int(3),email varchar(255));
 drop table if exists t_student;

MySQL中的数据类型
varchar char int bigint float double date datetime clob blob

数据类型	用处
varchar	可变长的字符串，可以根据传入的数据长度动态分配空间，与char一样最长255
char	定长字符串，不管实际字符串长度多少，分配固定长度空间
int	整数型，最长11位
bigint	长整型相当于long
float	单精度浮点型
double	双精度浮点型
date	短日期，可以用now函数来获取系统当前时间
datetime	长日期和短日期的区别时他的默认格式有时分秒
clob	字符大对象，最多可存4G字符串
blob	二进制大对象，用来存储图片，视频等流媒体数据，要用io流插入

插入数据

insert into 表名(字段1,字段2，字段3...) values (值1,值2,值3...),(值1,值2,值3...),(值1,值2,值3...)//前面的字段和值要一一对应
//在values后面逗号就可以插入多条语句

如果不传值就是默认值
如果前面的字段省略就默认是全部字段都有，所以也要对应传所有的值

插入日期
运用前面的函数

函数	功能
str_to_date	将字符串varchar类型转换成date类型
date_format	将date类型转换成varchar类型

mysql的日期格式为 %Y 年 %m月 %d日 %h时 %i分 %s秒

insert into t_user values(1,'yier',str_to_date('01-10-1990','%d-%m-%Y'));
//str_to_date('日期' 'mysql的日期格式要一一对应)
//如果年月日时对应着年月日就不用str_to_date,可以自动转换

修改（DML）

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 where 条件;
//如果没有条件限制会导致所有数据全部更新
//例子：
update t_student set age=16 where no=1;
//后面的where是要更改的那一行的条件

删除（DML）

delete from 表名 where 条件;
//和update一样没有条件就会全部删除

快速操作表
快速创建表
原理：将一个查询结果当做一张表新建，这个可以快速的复制一张表

create table 表名 as select 字段 from 表名;
//后面就可以接select语句可以跟where group by order by等

也可以将查询的结果插入表中，与插入语句类似

insert into 表名 select 字段 from 表名;

快速删除表中数据（属于DDL操作，delete属于DML）
用delete删除比较慢,只会将数据删除，空间不会被释放掉，但是还可以回滚恢复
truncate删除的效率比较高，物理删除，表被依次截断，但是不能回滚恢复（只能删除全部）

truncate table 表名;

truncate 和delete都只是删除表中的数据， 但是表还在，drop是会把表直接删掉

对表结构的修改
要用alter这个语句，具体在使用的时候上网查

约束（重要）
在创建表的时候可以给表中的字段加上一些约束，保证这个表中的数据的完整和有效性

• 非空约束 not null 约束的字段不能为NULL
• 唯一性约束 unique 字段不能重复但是可以为NULL
• 主键约束 primary key（简称PK）
• 外键约束 foreign key （简称FK）
• 检查约束 check

create table 表名(id int not null)；//非空约束，只允许列级约束
create table 表名(id int unique);
create table 表名(id int,name varchar(255),unique(name,email)；
//名字和邮箱两个联合起来具有唯一性，只有两个一样的时候才能不能插入
//约束没有添加在列后面被称为表级约束，要给多个字段联合起来添加某一个约束的时候用表级约束
create table 表名(id int,name varchar(255) not null unique)；
//not null和unique可以联合一起使用,mysql中如果同时被not null 和unique同时约束的话，该字段变成主键字段

主键约束
主键可以是单个字段，也可以是多个字段的组合。对于单字段主键的添加可以使用表级 约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。相当于是一个身份证的唯一标识，其他字段可以相同但是这个字段不能相同。

create table table_name(column_name1 date_type primary key, column_name2 date_type,......);
//auto_increment在数字类型后面添加会自动增加。

外键约束
用另一个表中的某一个字段来约束该表的字段，这个表中的字段的值只能是来自引用表字段中的值
被引用的表被称为父表，被约束的表被称为子表
在创建和插入的时候要先创建父表在创建子表
在删除的时候要先删除子表再删除父表

create table t_student(no int primary key auto_increment,name varchar(255),cno int,foreign key(cno) references t_class(classno));
//foreign key(被约束的字段) references 用来约束的表名字(用来约束的字段)添加在被约束的字段后面

外键的值可以为空，引用的字段必须具有唯一性，不一定是主键
存储引擎
是MySQL中特有的术语
是一个表存储/组织数据的方式

show engines \G //可以查看mysql支持的存储引擎

常用的存储引擎
MyISAM存储引擎：
使用三个文件表示每个表：
格式文件 - 存储表结构的定义(mytable.frm)
数据文件 - 存储表行的内容(mytable.MYD)
索引文件 - 存储表上索引(mytable.MYI)，表上的主键或者unique的列都会自动创建索引
可以被转换压缩、只读表来节省空间
InnoDB存储引擎：
默认存储引擎，同时也是重量级的存储引擎，支持事物，支持数据库崩溃后自动恢复，非常安全
特点：
每个InnoDB表在数据库中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表内容
提供一组用来记录事务性活动的日志文件
用COMMIT、SAVEPOINT以及ROLLBACK回滚支持事物处理
提供全ACID兼容
在MySQL服务器崩溃后提供自动恢复
多版本和行级锁定
支持外键及引用的完整性，包括级联删除和更新
MEMORY存储引擎