MySql 基本概念以及常用操作

mysql 数据库语言分类

DDL 数据库定义语言  data definition language
DML 数据库操作语言  Data Manipulation Language
DQL 数据库查询语言  data query language
DCL 数据库控制语言  Data Control Language

// 常用数据类型
int // 整数
decimal(5,2) // 浮点数
date // 精确到年月日
datetime // 精确到年月日时分秒的时间
timestamp // 精确到年月日时分秒  默认填充当前时间
varchar(20) // 字符串
bolb // 二进制文件类型
text // 长文本

// where 和 having 的区别
    1、where是分组之前进行条件判断，having是在分组之后进行条件判断
    2、where只能写在group by的前面，having只能卸载group by 的后面
    3、where后面不能写聚合函数，having后面可以写聚合函数

// 其他语句
set names 编码格式; 临时设置编码格式

DDL:
    // 针对数据库
    show databases; //查看有哪些数据库；
    create database name; //创建名为name的数据库
    show create database name; //查看创建数据库的SQL语句
    drop database name; // 删除名为name数据库
    use name; // 选择使用名为name的数据库
    select database(); // 当前选择的数据库
    // 针对表
    show tables; // 查看当前数据库有哪些表；
    create table tablename (fieldname type,fieldname type,...); // 创建一张表
    show create table tablename; // 查看创建名为tablename的表的sql语句
    desc tablename; // 查看表结构
    alter table tablename add fieldname type; // 给表结构新增字段
    alter table tablename change fieldname newfieldname type; // 修改字段名
    alter table tablename change fieldname newfiledname newtype; // 更换字段类型  一般不这样用
    alter table tablename modify fieldname newtype; // 修改字段类型
    alter table tablename drop fieldname; // 删除某个字段名
    rename table tablename to newtablename; // 重命名表
    drop table tablename; // 删除表
    truncate tablename; // 删除表再创建一张新的表
DML:
    ##condition 应该为 fieldname=value 格式##
    // 添加数据
        insert into tablename (fieldname1,fieldname2,...) values (value1, value2, ...); // 添加数据
        insert into tablename values(fieldname1,fieldname2,...) // 添加数据， 有多少字段需要给多少字段
        insert into tablename (fieldname1,fieldname2,...) values (value1, value2, ...), (value1, value2, ...); // 一次添加多条记录
    // 修改操作
        update tablename set fieldname1=value1,fieldname2=value2; //更改字段值 修改所有记录
        update tablename set filedname=value1,filedname2=value2 where condition;// 根据 condition 符合的条件修改对应的字段数据
    // 删除操作
        delete from tablename; // 删除表中所有数据 逐条删除
        delete from tablename where condition; //根据condition条件删除对应的数据
        delete from tablename where condition and condition; // 根据多个condition匹配删除一条记录
DQL：
    // 单表查询
        select * from tablename; // 查询表的所有数据
        select fieldname1,fieldname2 from tablename; // 查询多个字段
    //条件查询
        = ： select * from tablename where fieldname=value;  // 条件等于
        < :  select * from tablename where fieldname<value;  // 条件小于
        > : select * from tablename where fieldname>value;   // 条件大于
        <= : select * from tablename where fieldname<=value; // 条件小于等于
        >= : select * from tablename where fieldname>=value; // 条件大于等于
        !=,<> : select * from tablename where fieldname!=value; 或者 select * from tablename where fieldname<>value; // 条件不等于
        between...and... : select * from tablename where fieldname between value1 and value2;（两个值之间)  // 条件在两个值中间
        in() : select * from tablename where fieldname in (value1,value2,value3); // 条件在多个值中
        is null ：select * from tablename where fieldname is null; // 条件为null
        is not null : select * from tablename where fieldname is not null; // 查询条件字段部位null 的所有数据
        and, && ：select * from tablename where fieldname1=value1 and fieldname2=value2; 或者 select * from tablename where fieldname1=value1 && fieldname2=value2; // 多个查询条件同时满足
        or, || ：select * from tablename where fieldname1=value1 or fieldname2=value2; 或者 select * from tablename where fieldname1=value1 || fieldname2=value2; // 单个查询条件满足即可
        distinct ： select distinct field from tablename; (去重)
        like：select * from tablename where fieldname like '_三%' //（模糊查询）_代表任意一个字符 % 代表0到过个字符
        as : select fieldname1 as alias, fieldname2 as alias  from tablename; // 简写 select fieldname1 alias, fieldname2 alias  from tablename;
           select * from tablename as alias;
           select u.fieldname1, u.fieldname2,u.fieldname3 from tablename as u;
    // 复杂的条件查询
        1、聚合函数
            count() 统计 select count(* || fieldname) from tablename;
            sum() 求和 select sum(fieldname) from  tablename;
            max() 最大值 select max(fieldname) from tablename;
            min() 最小值 select min(fieldname) from tablename;
            avg() 平均值 select avg(fieldname) from tablename;
        2、排序查询
            select * from tablename order by fieldname asc;  // 默认升序 升序asc  降序 desc
            select * from tablename order by fieldname1 desc,fieldname2 asc; // 第一个字段降序，如果第一个字段值相同根据第二个字段升序
        3、分组查询
            select count(*) from tablename group by fieldname;
            select fieldname,count(*) from tablename group by fieldname;
            select fieldname, count(*) from tablename group by fieldname having count(*) >= num  // 表示根据字段名分组，并且分组后每组的数量大于等于2
            select fieldname, count(*) from tablename where fieldname > num1 group by fieldname having count(*) > num2  // 筛选表中fieldname 大于 num1 的数据  进行分组 并且要求 分组后每组的数量大于num2
        4、分页查询
            select * from user limit startIndex,pageSize; // 查询第几页的几条数据
            select * from user limit (n - 1) *pageSize,pageSize; // 查询第几页的数据
    // 约束
        1、非空约束
            not null // 字段不能填充空值
            create table tablename(
                fieldname1 type not null,
                fieldname2 type
            );
            // 创建表后修改字段可以为null
            alter table tablename modify fieldname1 type;
            // 创建表后修改字段不能为bull(需首先清除当前表中为null的数据，或者修改)
            alter table tablename modify fieldname1 type not null;
        2、唯一约束
            unique // 值不能重复
            create table tablename(
                fieldname1 type unique,
                fieldname2 type
            );
            // 创建表后删除唯一约束
            alter table tablename drop index fieldname1;
            // 创建表后添加字段约束
            alter table tablename modify fieldname1 type;
        3、主键约束
            primary key // 表示唯一不能为null 并且不能重复
            create table tablename(
                id int primary key,
                name varchar(20)
            )
            // 创建表后删除主键
            alter table tablename drop primary key; // (执行后依然为非空  not null)
            alter table tablename modify id int;
            // 创建表后添加主键
            alter table tablename modify id int primary key;

            // 自动增长
               auto_increment
               create table tablename(
                   id int primary key aotu_increment,
                   name varchar(20)
               )
               // 创建表后删除自动增长
               alter table tablename modify id int;
               // 创建表后添加自动增长
               alter table tablename modify id int auto_increment;

        4、外键约束
            constraint 外键名字 foreign key (外键字段) references (字段);
            简写格式： foreign key (外键字段) references (字段);

            create table user(
                id int primary key,
                name varchar(20)
            );

            create table computer(
                id int primary key,
                name varchar(20),
                uid int,
                constraint c_u_key foreign key (uid) references user(id)
            );

            // 删除外键约束
                alter table computer drop foreign key c_u_key;
            // 创建表后添加外键约束
                alter table computer add constraint c_u_key foreign key (uid) references user(id);
    // 多表查询
        // 表与表之间的关系
        1、一对一
        2、一对多（多对一）
        3、多对多
        // 查询方式
        1、笛卡尔积查询(显示两张表所有字段互相的成积)
            select * from tablename1,tablename2;
            // 显示效果如下
            mysql> select * from user;
            +------+------------+
            | name | brithday   |
            +------+------------+
            | jack | 2020-02-01 |
            | rose | 2020-02-02 |
            | lisa | 2020-02-03 |
            +------+------------+

            mysql> select * from person;
            +--------+------+
            | name   | age  |
            +--------+------+
            | 张三   |   20 |
            | 李四   |   10 |
            | 王五   |   18 |
            +--------+------+

            mysql> select * from user,person;
            +------+------------+--------+------+
            | name | brithday   | name   | age  |
            +------+------------+--------+------+
            | lisa | 2020-02-03 | 张三   |   20 |
            | rose | 2020-02-02 | 张三   |   20 |
            | jack | 2020-02-01 | 张三   |   20 |
            | lisa | 2020-02-03 | 李四   |   10 |
            | rose | 2020-02-02 | 李四   |   10 |
            | jack | 2020-02-01 | 李四   |   10 |
            | lisa | 2020-02-03 | 王五   |   18 |
            | rose | 2020-02-02 | 王五   |   18 |
            | jack | 2020-02-01 | 王五   |   18 |
            +------+------------+--------+------+
        2、内连接查询
            ①、隐式内连接查询
                select * from tablename1, tablename2 where condition;
            ②、显示内连接查询
                select * from tablename1 inner join tablename2 on condition;
        3、外连接查询
            ①、左外连接查询
                select * from tablenameq left join tablename2 on condition;
            ②、右外连接查询
                select * from tablenameq right join tablename2 on condition;
        4、子查询(嵌套查询)
            ①、嵌套中的查询语句查询结果为一个数据
                select * from tablename1 where fieldname1=(select filed1 from tablename2 where filed2='value');
                exp::
                  select * from a_table where id=(select id from b_table where name='李四');
            ②、嵌套中查询语句查询结果为多个数据
                select * from tablename1 where fieldname in (select * from tablename2 where  filed1=value1 || field2=value2)
            ③、嵌套中查询语句查询结果为一张表
                select * from (select * from tablename) as new_tablename1;
    // 事务(处理事情的能力，多条sql语句完成一件事情，则使用事务，保证任务完成,如果事务没有执行commit 则会自动回滚到事务前的状态)
        // 事务的四大特性
            1、原子性：原子是化学反应中最小的粒子，不能再分割了
            2、持久性：永久存储
            3、一致性：在使用事务操作的时候，操作前和操作后的数据是一致的
            4、隔离性：事务与事务之间是没有关系，互相隔离，互不干扰的
                但是，有时候还是会打破隔离性， 当多个失误并发操作一张表的时候，就会打破隔离性
            // 打破隔离性所产生的一些问题
                1、脏读：事务A读到了事务B 的未提交的数据
                2、不可重复读：事务A读到了事务B 已经提交后的修改（update）的数据
                3、虚读(幻读): 事务A读到了事务B 已经提交后的增加（insert）或者减少（delete）的数据
            // 事务的隔离级别
                1、read uncommitted   设置这个隔离级别 以上三个问题都会发生
                2、read committed     设置这个隔离级别，能够解决脏读，但是会产生幻读和不可重复读 （Oracle 默认）
                3、repeatable read    设置这个隔离级别，能够解决脏读、不可重复读，但是会产生幻读 （Mysql 默认）
                4、serializable       设置这个隔离级别，都能够解决
                // 修改隔离级别
                    select @@tx_isolation; // 查询隔离级别
                    set grobal transaction isolation level (上面的四种模式);
        1、 start transaction;(开启事务)
            commit;(提交)
            rollback;(回滚)

            exp::(转账  假设张三之前有500,李四之前也有500,张三给李四转200)
                start transaction;
                update tablename1 set money=300 where name='张三';
                update tablename1 set money=700 where name='李四';
                commit;

        2、关闭单条语句的自动提交
            select @@autocommit; // 查询当前自动提交设置1为自动提交  0为手动提交
            set @@autocommit = 0; // 关闭自动提交 单次设置只对当前命令行窗口有效
            事务完成后  需要手动commit;