mysql

mysql语法

库操作

​ create database 库名[character set utf8,engine set myisam/innodb];

​ show databases;

​ show create database 库名;

​ use 库名;

​ drop database 库名;

表操作

​ create table 表名(字段名 类型 约束);[character = utf8,engine = myisam/innodb];

​ show tables;

​ show create table 表名;

​ describe 表名;

​ drop table 表名;

​ rename table 原表名 to 新表名;

​ truncate table 表名;

字段操作

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

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

​ alter table 表名 change 字段名 新字段名 新字段类型; 改名或类型[名和类型]

​ alter table 表名 modify 字段名 新字段类型; 改类型

​ alter table 表名 drop 字段名;

• 关键字

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

  first:在第一个字段处

  默认：添加在最后一个

记录操作

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

​ delete from 表名 where 字段名 = 值;

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

​ select * from 表名[where];

事务

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

对表的操作适用

数据类型

• 整数
  • 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

查询条件

• 别名：

  ​ 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;

查询操作

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 emp_10 as (select * from emp where deptno=10);
   

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;
   

查询函数

• concat()函数

  • 用于 * ,可以直接输出;
  • select concat(“字符串”，“字符串单位”) from 表名;
  • select concat("a","b");

• ifnull(字段，数值)

  • 用于where,不可以直接输出
  • 如果字段为null，输出数值；

• 聚合函数

  • 不可以直接输出
  • sum(字段):总和
  • avg(字段):平均值
  • max(字段):最大值
  • min(字段):最小值
  • count(字段)数量(过滤null,建议使用*?

• 时间函数

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

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

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

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

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

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

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

    • %Y四位年 %y两位年
    • %m两位月 %c一位月
    • %d日
    • %H24小时 %h12小时
    • %i分
    • %s秒

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

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

• 字符串相关函数：

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

    select char_length(字段名) from emp;

    select char_length("");

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

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

  • lower(str)转换为小写

  • upper(str)转换为大写

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

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

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

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

  • repeat(str,count);重复

  • replace(str,old,new)替换

  • reverse()反转

• 数学相关的函数

  • floor(num)向下取整/舍去小数点后的

  • truncate(num,m)向下取整

  • round(num,[m])四舍五入,[m代表保留几位小数]

  • rand() 随机小数0-1

    ​ flooor(rand()*5);0.1.2.3.4

关联关系

• 一对一

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

• 一对多

  正常情况下

• 多对多

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

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

视图

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

• 创建视图：

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

• 简单视图：

  增删查改

• 复杂视图：

  查

• 插入数据

  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条；

• 创建或修改视图

  create or replace v视图名 as (select * from emp where 信息);

  视图已存在时进行修改，视图不存在时创建视图；

• 删除视图

  drop view 视图名;

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

约束

仅在当前字段内生效

• 主键
  • primary key
  • 主键不允许重复，不允许出现空值
  • constraint pk_stud_id primary key(id);
• 外键
  • 两表的主键和外键建立关系以后，主键所在的表叫做父表，外键所在的表叫做子表，父表和父表的数据不能被先删除，值有删除完所有的子表数据后才能被删除
  • 保证表和表之间数据一致性;
  • constraint 约束名称 foreign key(外键字段名) references 父表名(被依赖的父表名);
  • 外键可以重复，允许出现空值
  • 父表和子表之间必须使用相同的存储引擎,数据类型相同(字符长度可以不同)
  • 数据表的存储引擎只能为InnoDB
  • 外键列和参照列必须创建索引；
• 唯一约束
  • unique
  • 不允许出现重复值；
• 非空约束
  • not null
  • 太多会消耗数据库性能
• 默认约束
  • default 默认值数据
  • 当没有定义时生效，不包括null;
• 约束 check
• 自增 auto_increment
• 描述 comment “描述”

索引

相当于为字段创建索引值

• 详细信息:

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

• 自动创建索引:

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

• 索引分类:

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

• 适用情况:

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

• 创建索引:

  create index 索引名 on 表名(字段名[字段长度]);
  此处的字段长度是指建立该字段前几个长度的索引，只用于特殊情况(例如价格的89.00只查询整数时)
  create index 索引名 on 表名(字段名1[字段长度]),字段名2[字段长度]);
  此处时复合索引，几个字段名代表创建几个同名索引
  
  

• 查看索引

  show index from 表名;
  
  

• 删除索引

  drop index 索引名 on 表名;
  
  

大小写

show full columns from t_user;

注意事项

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

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

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