MySQL语句总结

MySQL中表、列可以改名，database不能改名

 

show databases：显示所有数据库

use database_name：使用database_name这个数据库

show tables：显示当前库下的所有表

create database database_name [charset 字符集]：创建一个数据库，数据集一般是utf8

drop database database_name：删除一个数据库

drop table table_name：删除表

rename table old_table_name to new_table_name：修改表名

truncate table_name：清空表数据（不是删除表中数据，与delete不一样，truncate相当于删表在重建一张同样结构的表，操作后得到一张全新表，而delete是从删除所有层面来操作的，truncate相当于把旧的表扔了重新画一张，delete相当于用橡皮把表的数据库擦掉，truncate比delete执行速度快）

set names 编码方式：如果出现乱码，设置正确的编码方式

desc table_name：查看table_name所有字段信息

insert into table_name (字段1，字段2，...) values (字段1的值，字段2的值，...)：往table_name表中插入数据，如果插入的数据是所有字段的值，则“(字段1，字段2，...)”可以省略，如果想一次插入多列，则用逗号把“(字段1的值，字段2的值，...)”分开

update table_name set 字段名=值 where expression：修改操作，expression表达式为true即可修改成功，修改多个字段，用逗号分开（where 1：取出所有行，因为1恒为真）
delete from table_name where expression：删除满足expression的行，如果不写where语句，代表删除表中所有数据
select 字段... from table_name where expression：查询操作

建表语句：
MySQL三大列类型：
    数值型：
        整形：（默认是有符号类型）,三种符号类型参数:(M)/unsigned/zerofill。M表示补零的宽度，与zerofill一起使用，单独使用                         没有意思。比如：tinyint(5)与tinyint(5) zerofill，如果值为5，则前者代表5，后者代表00005
            tinyInt:1字节,存储范围：-128~127,0~255
            samllint:2字节
            mediumint:3字节
            int:4字节
            bigint:8字节
        小数:M表示精度，代表小数的总位数。D表示标度，代表小数位(小数点右边的位数)。M<24占用4个字节，否则占用8个字节
            float(M,D)：浮点
            decimal(M,D)：定点，整数部分和小数分开存储，精度较高
        字符：
            char:定长类型，效率高，指针直接移动到指定位置，比如;char(N),不够N长度，在尾部补够空格
            varchar:变长类型，有标志位标志字段占有多少字符，不用空格补齐
            text:文本来兴，可以存储比较大的文本段，搜索速度较慢。声明时，不用声明默认值
            blob:二进制信息。意义：二进制0-255都有可能出现，blob在于防止因为字符集的问题，导致信息丢失。比如一张图片中                      有0xFF字节，这个在ASCII字符集认为非法，在入库的时候，被过滤了
            注意：char和varchar区别，如果本身char右边有空格将会被删除，varchar则不会。
                       char(M)和varchar(M)限制的是字符，不是字节。
                       blob防止字符集问题导致数据丢失
    日期/时间类型：
        date：3字节，存储年月日类型，范围：1000-01-01~9999-12-31
        time：3字节，范围：-838:59:59~838:59:59
        datetime：8字节，日期时间类型
        timestamp：4字节，和datetime一样，current_timestamp取当前时间
        year：1字节,范围：1901-2155，如果范围错误则是0000年，可以简写成两位，但不推荐这样写
        
    语法：
        建表其实就是声明列的过程
        create table table_name(
        列1声明 列类型1 列1参数，
        列2声明 列类型2 列2参数，
        ...
        列n声明 列类型n 列n参数
        )engine myisam/innodb/dbd charset utf8/gbk/;//设置搜索引擎和编码方式
        例如：
        create table member(
        id int unsigned auto_increment primary key,//主键自增
        username char(20) not null default "",
        gender char(1) not null default "",
        weight tinyint unsigned not null default 0,
        birth date not null default "2019-01-01",
        salary decimal(8,2) not null default 0.00,
        lastlogin int unsigned not null default 0
        )engine myisam charset utf8;
修改表的语法：
    添加列：
        alter table table_name add 列名称 列类型 列参数
        alter table table_name add 列名称 列类型 列参数 after 某列:把要加的列加载某列之后，after可以省略，默认追加在最后一行
        alter table table_name add 列名称 列类型 列参数 first：如果想把一列添加到表的最前面，使用first
    删除列：
        alter table table_name drop 列名
    修改列类型：
        alter table table_name modify 列名 新的列类型 新的列参数
    修改列名及列类型：
        alter table table_name change 旧列名 新列名 新类型 新参数

show create table table_name:查看建表语句

查询语句：
    select 5种子句之where常用运算符：
        比较运算符：

运算符	说明	运算符	说明
<	小于	!或<>	不等于
<=	小于等于	>=	大于等于
=	等于	>	大于
in	在某集合内	between…and…	在某范围内

        in是散点的集合(某几个点)比如：in (1,5)代表1和5，between..and..是区间
        
        逻辑运算符：    

运算符	说明
NOT或！	逻辑非
OR或||	逻辑或
AND或&&	逻辑与

        and优先级比or高
        like "abc%"：模糊查询,%表示任意字符，_匹配单个字符，例如：like "abc__",表示abc后面接两个字符

例子：
            ①select id,name,a-b from table_name where 1：表里原来没有a-b列，这一列其实是一个运算结果，术语叫“广义投影”，也可以作为列打印出来
            ②select id,name,(a-b) from table_name where a-b：把a-b的列的值进行显示
              select id,name,(a-b) as c from table_name having c：把a-b的列的值用c作为别名进行显示，结果同上
            ③把num=[3,12,15,25,3,29,34,37,32,45,48,52]中[20,29]的值设置为20，[30-39]的值设置为30
              select table_name set num=floor(num/10)*10 where num>=20 and num <=30; -->floor(x)表示取不大于x的整数值
        where：对表中的数据发挥作用
        having：对于结果中的列，进行筛选，表达式中可以使用中间结果值(不是在列中的值)
select 5种子句之group统计函数：(下面五个关键字单独使用意义不大，要和分组配合使用)
        max：求最大
        min：求最小
        sum：求总和
        avg：求平均
        count：求总行数
        
        group by：分组查询，把group by的关键字就行合并显示，取每个关键字第一行
        order by：放在where/group by/having后面，当最终结果集出来后，可以进行排序。
                  语法：order by 结果集中的列名 desc/asc：对“结果集中的列名”进行降序/升序排序，如果一个字段有冲突不能排序的话，在选用另一个字段进行排序，使用方法;order by 字段1 asc/desc,字段2 asc/desc...
        limit：在语句的最后，起到限制条目的作用。
               语法：limit offest，N；offset(偏移量，跳过的行数，如果为0，可以省略)，N(实际取出的行数)
        
        顺序：where->group by->having->order by->limit(必须按照这个顺序，否则会报错)
        
        例子：
            ①select max(a) from table_name:查询a列中a最大的行，其他使用方法相同，而count的使用方法是count(*),如果使用具体字段名称作为参数，如果参数为null，则不计算在内；count(*)和count(1)查询结果相同，都是取出所有行，包括为null的行，这两种对于myisam引擎的表，没有区别，这种引擎内部有一个计数器在维护着行数，Innodb引擎的表，用count(*)直接读行数，效率低，因为Innodb要去数一遍
            ②select sum(a) from table_name group by b：分组查询，在table_name表中，按b分组就行统计a的和
            ③把下表中挂科数大于两科的人讲求平均值

name	subject	score
张三	数学	90
张三	语文	50
张三	地理	40
李四	语文	55
李四	政治	45
王五	政治	30
赵六	语文	100
赵六	数学	99
赵六	品德	98

              select name,avg(score),sum(score<60) as gks from result group by name having gks>=2;
select之子查询：
        where型子查询(指把内层查询的结果作为外层查询的比较条件)：例：select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);//查询goods_id最大的商品
        from型子查询(指把内层的查询结果当成临时表，提供外层sql再次查询)：
        exists子查询(指把外层的查询结果拿到内层，看内层的查询是否成立)：
        注意：where型子查询：如果where列=(内层sql)，则内层sql返回的是单行单列，单个值
                             如果where列in(内层sql)，则内层sql只返回单列，可以多行

左连接、右连接和内连接：
    左连接语法：假设A表在左不动，B表在A表的右边滑动，A表与B表通过一个关系来筛选B表的行
                A left join B on 条件：当条件为真，则B表对应的行取出，形成一个结果集，可以看成一张表，可以在对此表进行查询(where/group by/having/order by/limit照常使用)，可以查询的列为AB表的列
                                      例：select * from A left join B on 条件 where...
    右连接语法：A right join B on 条件
    内连接语法：A inner join B on 条件
    注意：既然左右连接可以互换，尽量用左连接，处于移植时兼容性方面的考虑
              左右连接是把两张表条件相同的行放在一行，没有满足的条件补NULL；内连接中不满足的条件删除。
              内连接是左右连接的交集          
              外连接是左右连接的并集，在MySQL中不支持，MySQL中使用union实现外连接的效果
union：合并2条或多条语句的结果
             语法：sql1 union sql2
             注意：如果两个表的列名不一样，可以先用as取别名让两个表列名相同后在使用union；也可以直接用，不用起别名，查询结果的列名使用第一个sql语句的列名；列的类型不一样也可以合并，此时合并没有意思