Mysql

1.基础知识

1.数据类型

• 整数
  • int(n);默认11
  • zerofill:没有的位数用0补齐（约束）
• 浮点数
  • float(20,30) 20:总长度|12小数长度
  • double
  • decimal
• 字符串
  • char(n):不可变长度，执行效率高<255;
  • varchar(n)可变长度，占用空间小<65535;通常用于小字段(小于255);
  • text()可变长度<65535;通常用于文本(大于255);
• 日期
  • date:2019-2-20 用符号隔开(建议用-)
  • time:16:32:50 只能用“：”
  • datetime:默认位null;最大值:9999-12-31
  • timestamp:默认位系统时间;最大值:2019-1-19

2.关联关系

• 一对一

  没有外键，两表的主键和主键建立联系

• 一对多

  正常情况下

• 多对多

  创建一个额外的关联表，用于连接两个表

  多对多的意思是两个表中的个别记录都对应了另外一个表的某几个值;

3.约束条件

仅在当前字段内生效

1.主键约束

• primary key
• 主键不允许重复，不允许出现空值,可以使用唯一约束和非空约束替代主键约束

2.外键约束

• 两表的主键和外键建立关系以后，主键所在的表叫做父表，外键所在的表叫做子表，父表的数据不能被先删除，只有删除完所有的子表数据后才能被删除
• 外键可以重复，允许出现空值
• constraint 约束名称 foreign key(外键字段名) references 父表名(被依赖的父表名);
• 保证表和表之间数据一致性;
• 父表和子表之间必须使用相同的存储引擎,数据类型相同(字符长度可以不同)
• 数据表的存储引擎只能为InnoDB
• 外键列和参照列必须创建索引；

3.唯一约束

• unique
• 不允许出现重复值；

4.非空约束

• not null
• 太多会消耗数据库性能

5.默认约束

• default 默认值数据
• 当没有定义时生效，不包括null;

6.自定义约束

1. 约束 check
2. 自增 auto_increment,可以通过删除自增长字段然后新建自增长字段重置计数器
3. 描述 comment “描述”

2.基本语法

1.库操作

1. 创建库

   create database 库名;

2. 查看库

   show databases;

3. 查看创建库语法

   show create database 库名;

4. 切换库

   use 库名;

5. 删除库

   drop database 库名;

2.表操作

1. 创建表

   默认:ENGINE=InnoDB DEFAULT CHARSET=utf8

   character set = charset

   create table 表名(
       字段名1 类型 约束,
       字段名2 类型 约束,
       ...
   ) [character set 字符集,engine = 存储引擎名];

2. 查看所有表

   show tables;

3. 查看创建表的语句

   show create table 表名;

4. 查看表结构

   describe 表名;

5. 删除表

   drop table 表名;

6. 删除表的所有记录

   truncate table 表名;

7. 重命名表

   ALTER TABLE 原表名 RENAME TO 新表名;

   rename table 原表名 to  新表名;

3.记录操作

1. 添加记录

   • 标准写法

     insert into 表名 (字段1,字段2...)values(值1,值2...)[,(值1,值2...)...];

   • 简单写法

     insert into 表名 values (值1,值2...)[,(值1,值2...)];

2. 删除记录

   delete from 表名 where 字段名 = 值;

3. 更新记录

   update 表名set 新字段名= 新值[,新字段名= 新值] [where 字段名 = 值];

4. 查询记录

   select * from 表名[where];

4.字段操作

可以同时修改多个字段用(,)表示

1. 修改表信息

   alter table 表名 engine = myisam/innodb charset=utf8/gbk;

2. 增加字段

   alter table 表名 add 字段名 字段类型；

3. 更改字段名或类型

   alter table 表名 change 字段名 新字段名 新字段类型;

4. 更改类型

   alter table 表名 modify 原字段名 新字段类型;

5. 删除字段

   alter table 表名 drop 字段名;

   • 关键字

   after(字段1):在字段1后面

   first:在第一个字段处

   默认：添加在最后一个

3.查询操作

1.查询条件

• 别名：

  ​ as 字段名

• 去重：

  ​ distinct 字段名

• 横列显示

  ​ \ G

• 判断比较符号

  • and 和 &&
  • or 或 ||
  • < > = <= >= != <>

• 数值计算运算符(可用于*和where处)

  • + - * / %
  • a%b=mod(7,2);

• 连接语法

  • and/or

    or若前半段执行成功,后半段不执行

    select * from 表名 where 字段="值1" or(and) 字段="值2";

  • in

    select * from 表名 where 字段名 in (数值1,数值2);

  • between and

    select * from 表名 where 字段名 between X and Y;

    包含X\Y

    查询一个字段段满足多种情况时字段要多遍/not

• 模糊查询like/not

  • % 多个未知字符
  • _一个未知字符

• 排序 order by

  • desc 降序 从大到小

  • asc 升序 从小到大，默认可不写

  • 语法

    select * from 表名 order by 字段 desc;

  • 多排序

    select * from emp order by 字段1 ,字段2 desc;

• 分页查询 limit

  • limit a,n;

    显示从a+1开始的前n条;

    查询最大值使用limit 0,1,id之类的唯一值，需要在将no转换为相对应的其他值;;

  • llimit (a-1)*n,n;

    在第a页上显示n条记录；

• 分组查询 group by(写在排序的后面)

  • 单字段分组:group by 字段

    select [类别字段,]聚合函数(字段) from 表名 group by 类别字段;

  • 多字段分组:groub by 字段1,字段2

    select [类别字段,]聚合函数(字段) from 表名 group by 类别字段;

• having(写在group by后面)

  ​ 聚合函数在不分组使用where，分组后用having;

2.查询操作

1.普通查询

1. 普通查询

   select 字段1,字段2 from 表名 [查询条件];

2. 多表查询:

   类似无关联关系的内连接,只不过先依次比较库1的字段1

   select 字段1,字段2 from 表1,表2 [查询条件];

2.子查询

1. 写在where和having后面当作查询条件的值:

   • 查询最大值:

     select * from emp where job = (select job from emp order by sal limit 0,1);

   • 多层子查询

     select * from emp where job = (select job from emp where sal = (select min(sal) from emp));

2. 创建表:

   create table 表名 as [(]select * from 表名[)];

3. 虚拟表:必须加别名

   select ename from (select * from emp where deptno=10) newtable;

3.关联(连接)查询

​ 可以使用A.*=B.*代表一个表的所有信息；

1. 等值连接

   select * from A,B where A.x=B.x;

2. 内连接

   1. 无关联关系

      先书写A表数据,依次与B表字段交叉

      select * from A join B;

   2. 有关联关系

      select * from A join B on A.x=B.x;

3. 外连接

   select * from A [left/right] join B on A.x=B.x;

4. 自关联(表内字段1与字段2存在一对多的关系)

   select * from 表名X 别名a join 表名X 别名b on 别名a.字段1 = 别名b.字段2;

3.查询函数

1.聚合函数

计算多条函数中的一个字段的数据,不可以直接输出

函数	作用	注意事项
sum(字段)	总和
avg(字段)	平均值
max(字段)	最大值
min(字段)	最小值
count(字段)	数量	过滤null,建议使用*

2.字符函数

函数	作用	注意事项
upper(str)	转换为大写
lower(str)	转换为小写

• char_length(str)获取字符串的长度

  select char_length(字段名) from emp;

  select char_length("");

• instr(str,substr)获取substr在str中出现的位置，从1开始；

• insert(“字符串”,a,n,”字符串”);

• trim(str)去两端空白;trim与()之间不能加空格

• left(str,index);从左侧开始截取index个字符

• right(str,index);从右侧开始截取index个字符

• substring(str,index[,length]);substring与()之间不能加空格

• repeat(str,count);重复

• replace(str,old,new)替换

• reverse()反转

3.数字函数

函数	作用	注意事项
round(num,[m])	对num四舍五入保留m位小数	负数保留到整数位,不写代表0保存到个位
truncate(num,m)	向下取整至m位有效数字	负数保留到整数位,不写代表0保存到个位
floor(num)	将num小数点后的全部删除,负数再减一
rand()	随机小数0-1
flooor(rand()*5);	获取随机整数	结果0.1.2.3.4

4.时间函数

• now();系统当前（此处建议书写字段名）

• curdate();获取当前年月日

• curtime();获取当前时分秒

• date(now());从完整的内容中提取年月日

• time(now());从完整的内容中提取时分秒

• extract(year/month/day/hour/minute/second from now());提取单个分量

• date_format(时间,格式);日期格式化

  格式化	时间	注意事项
  %Y/%y	四位年/两位年
  %m/%c	两位月/一位月	记忆特殊化
  %d	日
  %H/%h	24小时/12小时
  %i	分
  %s	秒

• str_to_date(时间字符串,时间字符串格式);

  select str_to_date("14.08.2019 08:00:00","%d.%m.%Y %H:%i:%s");

5.其他函数

• concat()函数
  • 用于 * ,可以直接输出;
  • select concat(“字符串”，“字符串单位”) from 表名;
  • select concat("a","b");
• ifnull(字段，数值)
  • 用于where,不可以直接输出
  • 如果字段为null，输出数值；

4.其他功能

1.事务

• begin;开始事务
• commit;确认事务
• rollback;回滚到开始事务处
• saverollback 回滚点名称;
• roolback to 回滚点名称;
• 注意事项
  • 记录相关的修改可以用到事务
  • 执行同一业务的多条sql语句的工作单位，可以保证多条sql全部执行成功或者全部失败;
  • 在事务没有得到确认之前,操作都是在内存中进行的,只有当commit时才进入到磁盘中去

对表的操作适用

2.视图

​ 虚拟表/sql语句复用/隐藏敏感信息

1. 创建视图

   create view 视图名 as (子查询);

2. 创建或修改视图

   不存在时创建,存在时修改

   create or replace view 视图名 as (查询语句)

3. 删除视图

   drop view 视图名;

• 简单视图：增删查改

• 复杂视图：查

• 插入数据

  insert into 视图名 (字段名1,字段名2) values(数值1，数值2);.注意事项

  create view v_emp_10 as (select * from emp where deptno = 10);
  
  insert into v_emp_10 (empno,ename,deptno) values (10010,"tom",10);
  
  insert into v_emp_10 (empno,ename,deptno) values (10011,"jerry",20);//数据污染
  
  create view v_emp_20 as (select * from emp where deptno = 20) with check option;
  
  insert into v_emp_20 (empno,ename,deptno) values (10012,"book",20);
  
  insert into v_emp_20 (empno,ename,deptno) values (10013,"school",10);//数据添加失败

• 删除数据

  只能删除视图中存在的信息,删除失败时提示删除0条；

• 创建视图时如果子查询使用了别名则之后进行操作只能使用别名；

3.索引

相当于为字段创建索引值,增加查询速度,但是见笑了增加速度

• 详细信息:

  • 索引是用来快速查询大数据的,与目录的作用相似;
  • 创建索引相当于单独创建了索引表用来帮助查询,但是会消耗空间,提升了查询速度;
  • 索引都是按照字节添加的;

• 自动创建索引:

  • 创建主键时,会自动创建唯一索引
  • 创建外键时,会自动创建普通索引

• 索引分类:

  • 默认:从头依次查找
  • Btree:只用innodb引擎可以使用;从中间查找n/2—1/4n+3/4n二叉树的形式查找.
  • Hash:只有myisam引擎可以使用;查询速度快,一次查询即可出结果,是建立了hash值和字段值对应表

• 适用情况:

  • 频繁作为查询条件的字段创建索引
  • 排序/统计/分组的字段创建索引
  • 频繁更新的字段不适合创建索引
  • 重复信息过多的不适合创建索引
  • 表记录过少的不适合创建索引

1. 创建索引

   1. 单行索引

      此处的字段长度是指建立该字段前几个长度的索引，只用于特殊情况(例如价格的89.00只查询整数时)

      create index 索引名 on 表名(字段名[字段长度]);

   2. 复合索引

      create index 索引名 on 表名(字段名1[字段长度]),字段名2[字段长度][,...]);

2. 查看索引

   show index from 表名;

3. 删除索引

   drop index 索引名 on 表名;

4.存储引擎

1. 区别

   功 能	MYISAM	InnoDB	Memory	Archive
   存储限制	256TB	64TB	RAM	None
   支持事务	No	Yes	No	No
   支持全文索引	Yes	No	No	No
   支持数索引	Yes	Yes	Yes	No
   支持哈希索引	No	No	Yes	No
   支持数据缓存	No	Yes	N/A	No
   支持外键	No	Yes	No	No
   注意事项	适用于:增+查	适用于:业务处理	适用小表,类似redis	压缩,只支持增查操作;

2. 相关命令

   1. 查看支持的所有存储引擎

      show engines;

3. 选用存储引擎

   mysql 5.5以后默认采用innoDB存储引擎

   1. 在有外部全文索引插件的情况下建议采用innoDB;
   2. 主要用于增加记录和查询记录建议使用myisam,在处理逻辑问题建议使用innoDB;

5.注意事项

1. 若表中已存在记录,这时添加一个有默认值但不是非空字段时,不添加时为默认值,否则是null;

2. 表的名称不区分大小写,存储时默认小写，库区分大小写;

3. 字段名用``包含,可以省略不写;

4. 大小写:show full columns from t_user;

最后更新： 2019年11月06日 11:36

原始链接： https://airbash.cn/2019/10/31/%E6%95%B0%E6%8D%AE%E5%BA%93/Mysql/

ZSP

赏

× 请我吃糖~