<commit_before>---
layout: post
title:  "MySQL基础语法"
date:   2017-02-23 20:09:00
categories: RDBMS
tags: MySQL
author: miaoqi
---

* content
{:toc}

## MySQL基础

### 数据库操作

* 创建数据库

        create database web08; --默认字符集
        create database web08_01 character set gbk; --指定字符集


* 查看数据库

        show databases;

* 查看数据库结构
 
        show create database web08_01;
        
* 查看正在使用的库

        select database();


* 删除一个库

        drop database web08_01;

* 使用库
    
        use web08;

* 导出整个库

        mysqldump -u 用户名 -p 数据库名 > 导出的文件名


### 表操作

* 创建表
        
        create table 表名{
           字段名 类型(长度) [约束],
           字段名 类型(长度) [约束]
        };
            
        create table user(
           uid int(32) primary key auto_increment,
           uname varchar(32),
           upassword varchar(32)
        );
            
        主键约束: primary key 要求被修饰的字段: 唯一和非空
        唯一约束: unique 要求被修饰的字段: 唯一
        非空约束: not null 要求被修饰的字段: 非空

* 查看表

        show tables;

* 查看表结构

        desc user;

* 删除表

        drop table user;

* 修改表

        alter table user add column uinfo varchar(32) not null; --增加一列

        alter table user modify uinfo varchar(50) not null; -- 修改列的长度, 类型, 约束
    
        alter table user change 旧列名 新列名 类型(长度) 约束; -- 修改列名, 类型, 约束
    
        alter table user drop 列名; -- 删除列
    
        rename table 表名 to 新表名; -- 修改表名
    
        alter table 表名 character set 字符集 -- 修改表的字符集

* 查看当前表的编码

        show create table tbl_user;

* 导出一个表
    
        mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 
    
* 导入

        在mysql命令行界面 source 目标sql文件

* 插入记录

        insert into 表(列名1, 列名2, 列名3, ...) values(值1, 值2, 值3);
    
        insert into 表 values(值1, 值2, 值3...)

* 更新记录

        update 表名 set 字段名=值, 字段名=值 where = 条件

* 删除记录

        delete from 表名 where = 条件

* 从现有表新建表

        create table new_table_name from select [*|column1,column2] from table_name [where]

* 查询
    
    * 准备工作
                
            create table product(    
                pid int primary key auto_increment,   
                pname varchar(20),   
                price double,   
                pdate timestamp  
            )


    * 自动增长列：auto_increment,要求：1,必须整型(int) 2.必须是主键
    
            insert into product values (null,'谭妮平',0.01,null);
            insert into product values (null,'李士雪',38,null);
            insert into product values (null,'左慈',-998,null);
            insert into product values (null,'黄迎',99999,null);
            insert into product values (null,'南国强',99998,null);
            insert into product values (null,'士兵',1,null);
            insert into product values (null,'李士兵',698,null);
            insert into product values (null,'士兵',1,null);

    * 简单查询

            1.查询所有商品
                select * from product;
            2.查询商品名和商品价格
                select pname, price from product;
            3.查询所有商品信息使用表别名
                select * from product as p; --as可以省略
            4.查询商品名，使用列别名
                select pname as name from product; --as可以省略
            5.去掉重复值(按照价格)
                select distinct price from product;
            6.将所有的商品的价格+10进行显示
                select price + 10 from product;

            条件查询
            
            1.查询商品名称为"左慈"的商品信息
                select * from product where pname = '左慈';
            2.查询价格>60元的所有商品信息
                select * from product where price > 60;
            3.查询商品名称含有"士"字的商品信息
                select * from product where pname like '%士%';
            4.查询商品id在(3,6,9)范围内的所有商品信息
                select * from product where pid in (3, 6, 9);
            5.查询商品名称含有"士"字并且id=6的商品
                select * from product where pname like '%士%' and pid = 6;
	
    * 排序(asc/desc)
    
            1.查询所有的商品，按价格进行排序(升序、降序)
                select * from product order by price asc;
            2.查询名称有"士"的商品信息并且按照价格降序排序
                select * from product where pname like '%士%' order by price desc, pid desc;
	
    * 聚合
    
            1.获得所有商品的价格的总和
                select sum(price) from product;
            2.获得所有商品的平均价格
                select avg(price) from product;
            3.获得所有商品的个数
                select count(*) from product;
	
    * 分组
    
            1.添加分类id 
                alter table product add cid varchar(32);
            2.初始化数据
                update product set cid='1';
                update product set cid='2' where  pid in (5,6,7);
        
    * 查询
    
            1.根据cid字段分组，分组后统计商品的个数。
                select count(*) from product group by cid;
            2.根据cid分组，分组统计每组商品的平均价格，并且平均价格大于20000元。
                select avg(price) from product group by cid having avg(price) > 20000;

    * 外键

            添加
                alter table product add foreign key [constraint] [constraint_name] (从表外键字段名) references 主表(主表的主键);
            
    * 多表查询

            1.交叉连接查询(基本不会使用, 得到的是两个表的乘积)
                select * from A,B;
            2.内连接查询(使用 inner join --inner可以省略)
                隐士内连接: select * from A,B where 条件
                显示内连接: select * from A inner join B on 条件
            3.外连接查询(使用 outer join --outer可以省略)
                左外连接: select * from A left outer join B on 条件
                右外连接: select * from A right outer join B on 条件
            4.全连接 full outer join(oracle中)
            5.联合查询
                union:去重,排序,效率低
                union all:不去重,不排序,效率高
            
    * 复制现有表

            create table new_table select * from old_table;

### 完整性约束

* 主键约束  
    
    主键是表里一个或多个用于实现记录唯一性的字段

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

* 唯一性约束

    唯一性要求表里的某个字段的值在每条记录里是唯一的
    
        create table table_name(
            id int primary key,
            name varchar(20),
            cellphone varchar(11) unique
        );
        
* 外键约束
    
    外键是从表里的一个字段, 引用主表里的主键
    
        create table tb_emp(
            id int primary key,
            dept_id int,
            constraint fk_emp_dept_id foreign_key(dept_id) references tb_dept(id)
        );


### 服务操作

* 启动服务端(mac)

        mysql.server start
        
* 停止服务端(mac)

        mysql.server stop

* 启动客户端(mac)

        mysql -uroot -p
        
* 启动服务端(Linux)

        service mysqld start
        
* 停止服务端(Linux)

        service mysqld stop
        
### 权限操作

* 授权操作

        grant all privileges on databasename.tablename to 'username'@'addr' identified by 'password';

        将所有库的所有表的增删改查权限授权给miaomiaoqi用户在localhost登录时, 并且密码是<PASSWORD>
        grant select,delete,update,INSERT on *.* to 'miaomiaoqi'@'localhost' identified by 'miaoqi';
        
        

        grant all PRIVILEGES on *.* to 'miaomiaoqi'@'localhost' identified by 'miaoqi';
        
* 查看权限

        show grants;
        
        show grants for username;
        
        select * from mysql.user;

### 主从一致

* 原理: 主库负责写, 从库负责读, 主库会启动一个线程, 将写操作写到日志文件中, 从库会启动一个IO线程负责从日志文件中读取操作记录, SQL线程负责执行操作记录进行同步

* 如何保持一致性: 减少数据库压力

### 悲观锁

* 数据库层面加个排它锁, 性能低

        select * from account for update;
        try {
            // process business logic
            
            commit();
        } catch (Exception e) {
            rollback();
        }

### 乐观锁

* 程序员手动控制(CAS机制)

        int version = select version from tb_xxx where id = 1;
        boolean success = update tb_xxx set name = 'bbb' where id = 1 and version = 1.0
        if (!success) {
            try...again
        }

### 事物

* 一套操作, 要么全执行, 要么全不执行, 通过写日志的方式实现日志

### 事物特性

* 原子性(A)

    事物中的逻辑, 不可分割, 要么全成功, 要么全失败

* 一致性(C)

    事物执行前后, 数据完整性

* 隔离性(I)

    事物在执行期间, 不应该受到其他事物的影响

* 持久性(D)

    事物执行成功, 那么数据应该持久保存到磁盘上

### 事物的安全隐患

select @@tx_isolation;

set session transaction isolation level read uncommitted; 读未提交

set session transaction isolation level read committed; 读已提交

set session transaction isolation level repeatable read; 可重复读

set session transaction isolation level serializable; 可串行化

不考虑隔离级别设置, 会出现以下问题

* 读

    * 脏读
        
        > 一个事物读到了另一个事物未提交的数据

    * 不可重复读

        > 一个事物读到了另一个事物更新的数据

    * 幻读

        > 一个事物读到了另一个事物插入的数据

* 写

    > 丢失更新
    
* 长事物

* 短事物

### 特殊sql

* 补充自增主键

        alter table `表名` add column `列名` int not null auto_increment primary key comment '主键' first;
    
* 中文字段排序

        ORDER BY CONVERT(column USING gbk) ASC<commit_msg>update: 更新博客<commit_after>---
layout: post
title:  "MySQL基础语法"
date:   2017-02-23 20:09:00
categories: RDBMS
tags: MySQL
author: miaoqi
---

* content
{:toc}

## 数据库操作

* 创建数据库

        create database web08; --默认字符集
        create database web08_01 character set gbk; --指定字符集


* 查看数据库

        show databases;

* 查看数据库结构
 
        show create database web08_01;
        
* 查看正在使用的库

        select database();


* 删除一个库

        drop database web08_01;

* 使用库
    
        use web08;

* 导出整个库

        mysqldump -u 用户名 -p 数据库名 > 导出的文件名


## 表操作

* 创建表
        
        create table 表名{
           字段名 类型(长度) [约束],
           字段名 类型(长度) [约束]
        };
            
        create table user(
           uid int(32) primary key auto_increment,
           uname varchar(32),
           upassword varchar(32)
        );
            
        主键约束: primary key 要求被修饰的字段: 唯一和非空
        唯一约束: unique 要求被修饰的字段: 唯一
        非空约束: not null 要求被修饰的字段: 非空

* 查看表

        show tables;

* 查看表结构

        desc user;

* 删除表

        drop table user;

* 修改表

        alter table user add column uinfo varchar(32) not null; --增加一列

        alter table user modify uinfo varchar(50) not null; -- 修改列的长度, 类型, 约束
    
        alter table user change 旧列名 新列名 类型(长度) 约束; -- 修改列名, 类型, 约束
    
        alter table user drop 列名; -- 删除列
    
        rename table 表名 to 新表名; -- 修改表名
    
        alter table 表名 character set 字符集 -- 修改表的字符集

* 查看当前表的编码

        show create table tbl_user;

* 导出一个表
    
        mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 
    
* 导入

        在mysql命令行界面 source 目标sql文件

* 插入记录

        insert into 表(列名1, 列名2, 列名3, ...) values(值1, 值2, 值3);
    
        insert into 表 values(值1, 值2, 值3...)

* 更新记录

        update 表名 set 字段名=值, 字段名=值 where = 条件

* 删除记录

        delete from 表名 where = 条件

* 从现有表新建表

        create table new_table_name from select [*|column1,column2] from table_name [where]

* 查询
    
    * 准备工作
                
            create table product(    
                pid int primary key auto_increment,   
                pname varchar(20),   
                price double,   
                pdate timestamp  
            )


    * 自动增长列：auto_increment,要求：1,必须整型(int) 2.必须是主键
    
            insert into product values (null,'谭妮平',0.01,null);
            insert into product values (null,'李士雪',38,null);
            insert into product values (null,'左慈',-998,null);
            insert into product values (null,'黄迎',99999,null);
            insert into product values (null,'南国强',99998,null);
            insert into product values (null,'士兵',1,null);
            insert into product values (null,'李士兵',698,null);
            insert into product values (null,'士兵',1,null);

    * 简单查询

            1.查询所有商品
                select * from product;
            2.查询商品名和商品价格
                select pname, price from product;
            3.查询所有商品信息使用表别名
                select * from product as p; --as可以省略
            4.查询商品名，使用列别名
                select pname as name from product; --as可以省略
            5.去掉重复值(按照价格)
                select distinct price from product;
            6.将所有的商品的价格+10进行显示
                select price + 10 from product;

            条件查询
            
            1.查询商品名称为"左慈"的商品信息
                select * from product where pname = '左慈';
            2.查询价格>60元的所有商品信息
                select * from product where price > 60;
            3.查询商品名称含有"士"字的商品信息
                select * from product where pname like '%士%';
            4.查询商品id在(3,6,9)范围内的所有商品信息
                select * from product where pid in (3, 6, 9);
            5.查询商品名称含有"士"字并且id=6的商品
                select * from product where pname like '%士%' and pid = 6;
	
    * 排序(asc/desc)
    
            1.查询所有的商品，按价格进行排序(升序、降序)
                select * from product order by price asc;
            2.查询名称有"士"的商品信息并且按照价格降序排序
                select * from product where pname like '%士%' order by price desc, pid desc;
	
    * 聚合
    
            1.获得所有商品的价格的总和
                select sum(price) from product;
            2.获得所有商品的平均价格
                select avg(price) from product;
            3.获得所有商品的个数
                select count(*) from product;
	
    * 分组
    
            1.添加分类id 
                alter table product add cid varchar(32);
            2.初始化数据
                update product set cid='1';
                update product set cid='2' where  pid in (5,6,7);
        
    * 查询
    
            1.根据cid字段分组，分组后统计商品的个数。
                select count(*) from product group by cid;
            2.根据cid分组，分组统计每组商品的平均价格，并且平均价格大于20000元。
                select avg(price) from product group by cid having avg(price) > 20000;

    * 外键

            添加
                alter table product add foreign key [constraint] [constraint_name] (从表外键字段名) references 主表(主表的主键);
            
    * 多表查询

            1.交叉连接查询(基本不会使用, 得到的是两个表的乘积)
                select * from A,B;
            2.内连接查询(使用 inner join --inner可以省略)
                隐士内连接: select * from A,B where 条件
                显示内连接: select * from A inner join B on 条件
            3.外连接查询(使用 outer join --outer可以省略)
                左外连接: select * from A left outer join B on 条件
                右外连接: select * from A right outer join B on 条件
            4.全连接 full outer join(oracle中)
            5.联合查询
                union:去重,排序,效率低
                union all:不去重,不排序,效率高
            
    * 复制现有表

            create table new_table select * from old_table;

## 完整性约束

* 主键约束  
    
    主键是表里一个或多个用于实现记录唯一性的字段

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

* 唯一性约束

    唯一性要求表里的某个字段的值在每条记录里是唯一的
    
        create table table_name(
            id int primary key,
            name varchar(20),
            cellphone varchar(11) unique
        );
        
* 外键约束
    
    外键是从表里的一个字段, 引用主表里的主键
    
        create table tb_emp(
            id int primary key,
            dept_id int,
            constraint fk_emp_dept_id foreign_key(dept_id) references tb_dept(id)
        );


## 服务操作

* 启动服务端(mac)

        mysql.server start
        
* 停止服务端(mac)

        mysql.server stop

* 启动客户端(mac)

        mysql -uroot -p
        
* 启动服务端(Linux)

        service mysqld start
        
* 停止服务端(Linux)

        service mysqld stop
        
## 权限操作

* 授权操作

        grant all privileges on databasename.tablename to 'username'@'addr' identified by 'password';

        将所有库的所有表的增删改查权限授权给miaomiaoqi用户在localhost登录时, 并且密码是<PASSWORD>
        grant select,delete,update,INSERT on *.* to 'miaomiaoqi'@'localhost' identified by 'miaoqi';
        
        

        grant all PRIVILEGES on *.* to 'miaomiaoqi'@'localhost' identified by 'miaoqi';
        
* 查看权限

        show grants;
        
        show grants for username;
        
        select * from mysql.user;

### 主从一致

* 原理: 主库负责写, 从库负责读, 主库会启动一个线程, 将写操作写到日志文件中, 从库会启动一个IO线程负责从日志文件中读取操作记录, SQL线程负责执行操作记录进行同步

* 如何保持一致性: 减少数据库压力

## 悲观锁

* 数据库层面加个排它锁, 性能低

        select * from account for update;
        try {
            // process business logic
            
            commit();
        } catch (Exception e) {
            rollback();
        }

## 乐观锁

* 程序员手动控制(CAS机制)

        int version = select version from tb_xxx where id = 1;
        boolean success = update tb_xxx set name = 'bbb' where id = 1 and version = 1.0
        if (!success) {
            try...again
        }

## 事物

* 一套操作, 要么全执行, 要么全不执行, 通过写日志的方式实现日志

### 事物特性

* 原子性(A)

    事物中的逻辑, 不可分割, 要么全成功, 要么全失败

* 一致性(C)

    事物执行前后, 数据完整性

* 隔离性(I)

    事物在执行期间, 不应该受到其他事物的影响

* 持久性(D)

    事物执行成功, 那么数据应该持久保存到磁盘上

### 事物的安全隐患

select @@tx_isolation;

set session transaction isolation level read uncommitted; 读未提交

set session transaction isolation level read committed; 读已提交

set session transaction isolation level repeatable read; 可重复读

set session transaction isolation level serializable; 可串行化

不考虑隔离级别设置, 会出现以下问题

* 读

    * 脏读
        
        一个事物读到了另一个事物未提交的数据

    * 不可重复读

        一个事物读到了另一个事物更新的数据

    * 幻读

        一个事物读到了另一个事物插入的数据

* 写

    丢失更新
    
* 长事物

* 短事物

## 特殊sql

* 补充自增主键

        alter table `表名` add column `列名` int not null auto_increment primary key comment '主键' first;
    
* 中文字段排序

        ORDER BY CONVERT(column USING gbk) ASC

## 函数

* DATE_FORMAT

        SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') FROM DUAL;

* INSTR

        SELECT INSTR('foobarbar', 'fo') FROM DUAL;
{'fraction_non_alphanumeric': 0.07999050557797294, 'fraction_numerical': 0.01756468075005934, 'mean_word_length': 1.569446561975911, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2256322', 'n_tokens_mistral': 7178, 'n_tokens_neox': 7652, 'n_words': 1541}