Mysql随笔

## 数据库操作 ##
1.创建数据库和数据库中的表

create database [if not exists]数据库名 [default character set 字符编码(集)]加上中括号是代表不是必选

    create database if not exists shcool defadult charset utf8;
    
use 数据库名；  //选中所需的数据库

    use school;

建表

    create table student2(
        stuNo  varchar(6),
    stuName  varchar(50),
    address  varchar(50),
    emailvarchar(40)
        
    );
    
2.切换不同的数据库
 
use 数据库名; 

use yunsi2;  

3.获取自己目前所在的数据库  
   select  database( ); 

   show databases  like 'yu%';

4 . 删除表 
   drop table student2;  

5.查看数据库中有多少张表 
   show tables;  

6.查看表的创建语句 
   show create table student2;

7.表的复制
   
复制表结构
    CREATE TABLE 表名 LIKE 要复制的表名

插入数据
    insert into 表名 select * from 要复制的表名

复制表结构和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

8.查看表结构
    desc table  表名
## 数据类型 ##

  **整数** (int用的最多）

    tinyInt
    smallInt
    mediumInt
    int 
    bigInt 

范围从小到大  
这些整数分为带符号的  signed  负数-----正数    
     无符号   unsigned    0-----值  
     signed   tinyInt     -128----- 127
     unsigned tinyInt      0-----255 


      create table t_ceshi1(
      t_num1   tinyInt signed,
      t_num2   tinyInt unsigned,
      t_num3   tinyInt,
      t_createTime dateTime
     )engine=innoDB default charset utf8;
    
    注意点： t_num3没有标明是无符号还是带符号，默认成带符号的 signed

**小数类型**  （double用的多）   
        
     decimal(M,D)
     numeric(M,D)
     float(M,D); 
     double(M,D)  
   
     取值范围不同

 M:数字的总位数  ，D:小数位数     ，小数点不算   

    create table t_ceshi3(
      t_num1   decimal(5,2),
      t_num2    numeric(5,2),
      t_num3   float(5,2),
      t_num4  double(5,2),
      t_createTime dateTime
     )engine=innoDB default charset utf8;

    数字：总共5位，小数占2位，整数部分最多3位  

**位字段类型 **    bit   

     create table t_ceshi5(
      t_num1 bit(8),
      t_createTime dateTime
     )engine=innoDB default charset utf8; 

**字符串类型**

char(M)   M:0-255,默认值是255

varchar(M)   M:0-65535,如果mysql编码方式指定为utf-8时，实测 M:0---21844。  

----- 创建表的时候，char可以不指定长度，varchar必须指定长度

------实测char不指定长度时，默认长度1，  测试一下非严格模式时，char的默认长度、

      create table t_ceshi6(
      t_num1 char,
      t_num2 varchar(10),
      t_createTime dateTime
     )engine=innoDB default charset utf8;

**复合类型**

  存储一整篇文章,整本书,字符量比较大，（char,varchar不够用了） 

        text    文本类型   
        tinyText ,text, mediumText, longText
        
         tinyText----0-255个字符 
                 text---   0-65535个字符
                 mediumText- ----  0   - 16777215个字符
                 longText---   0  -- 4294967295个字符

  存储图片，音频，视频   blob  ，二进制大对象   
    tinyBlob  blob,  mediumBlob , longBlob  


//没有默认长度

     create table t_ceshi8(
      t_num1 text,
      t_num2 blob,
      t_createTime dateTime
     )engine=innoDB default charset utf8;

---- 选用文本类型，二进制大对象类型时，无需指定长度  ，只需要写类型  

      create table t_ceshi9(
      t_num1 text(5),
      t_num2 blob(5),
      t_createTime dateTime
     )engine=innoDB default charset utf8;

    text(5) ----->tinytext
    blob(5)----->tinyblob  


**enum枚举类型** 

固定的几个值   ,一旦列名是枚举类型，插入值的时候，只能在固定的几个值里选取。 

    create table t_ceshi10(
      t_num1 varchar(10),
      t_num2 enum('spring','summer','autumn','winter'),
      t_num3  enum('male','female'),
      t_num4   enum('101','102','103'),
      t_createTime dateTime
     )engine=innoDB default charset utf8;

    insert into t_ceshi10(t_num1,t_Num2,t_num3,t_Num4,t_createTime) 
     values('jack','spring','male','101',now());

**日期类型**  (date,dateTime)  

   1.  Date   yyyy-MM-dd   
        1000-01-01----9999-12-31

   2.  Time    HH:mm:ss 
            -838:59:59-----838:59:59  
  
   3.  DateTime    yyyy-MM-dd HH:mm:ss
        1000-01-01 00:00:00 -----9999-12-31 23:59:59 

   4. timestamp   yyyy-MM-dd HH:mm:ss
             1970-01-01 00:00:01-----2038-01-19 03:14:07  
                  经过测试得出范围为1970-01-01 08:00:01 到2038-01-19 11:14:07
        

   5. year     yyyy
        1901---2155

        create table t_ceshi11(
            t_num1  date, 
        t_num2  time,
        t_num3 dateTime,
        t_num4  timestamp, 
        t_num5   year
         )engine=innoDB default charset utf8;

## 数据操作 ##

-- 增

        INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
    -- 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。
    -- 可同时插入多条数据记录！
    REPLACE 与 INSERT 完全一样，可互换。
    INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]

-- 查

    SELECT 字段列表 FROM 表名[ 其他子句]
        -- 可来自多个表的多个字段
        -- 其他子句可以不使用
        -- 字段列表可以用*代替，表示所有字段

-- 删

    DELETE FROM 表名[ 删除条件子句]
        没有条件子句，则会删除全部

-- 改

    UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]
## INSERT ##

select语句获得的数据可以用insert插入。

可以省略对列的指定，要求 values () 括号内，提供给了按照列顺序出现的所有字段的值。

    或者使用set语法。
    INSERT INTO tbl_name SET field=value,...；

可以一次性使用多个值，采用(), (), ();的形式。

    INSERT INTO tbl_name VALUES (), (), ();

可以在列值指定时，使用表达式。

    INSERT INTO tbl_name VALUES (field_value, 10+10, now());

可以使用一个特殊值 DEFAULT，表示该列使用默认值。

    INSERT INTO tbl_name VALUES (field_value, DEFAULT);

可以通过一个查询的结果，作为需要插入的值。

    INSERT INTO tbl_name SELECT ...;

可以指定在插入的值出现主键（或唯一索引）冲突时，更新其他非主键列的信息。

    INSERT INTO tbl_name VALUES/SET/SELECT ON DUPLICATE KEY UPDATE 字段=值, …;

    
##DELETE##


    DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

按照条件删除。where

指定删除的最多记录数。limit

可以通过排序条件删除。order by + limit

支持多表删除，使用类似连接语法。

delete from 需要删除数据多表1，表2 using 表连接操作 条件。

## TRUNCATE##

TRUNCATE [TABLE] tbl_name

清空数据
删除重建表
区别：

1，truncate 是删除表再创建，delete 是逐条删除

2，truncate 删除表中记录，不支持事务，不能恢复 （本质上是删除表结构表数据，然后再重建表结构）

3，delete 删除表记录，支持事务，可以在事务中使用回滚恢复之前删除的数据。

## 备份与还原 ##

备份，将数据的结构与表内数据保存起来。
利用 mysqldump 指令完成。

-- 导出

mysqldump [options] db_name [tables]

mysqldump [options] ---database DB1 [DB2 DB3...]

mysqldump [options] --all--database

1. 导出一张表
2. 
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)

2. 导出多张表

　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)

3. 导出所有表

　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)

4. 导出一个库
 
　　mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql)

可以-w携带WHERE条件

-- 导入

1. 在登录mysql的情况下：

　　source  备份文件

2. 在不登录的情况下

　　mysql -u用户名 -p密码 库名 < 备份文件

##     用户操作 ##

**创建一个用户，指定密码。**

格式：

create user 用户名@IP地址 identified by '密码';

如：创建一个用户，用户名为ming，密码为123456

create user ming@'%' identified by '123456';

Use mysql; 

User 表，存储了所有的登录用户  
 
**为用户赋予操作权限**

格式：

grant create,alter, drop, insert, update, delete, select  on 数据库名字.表名  to  用户名@IP地址;
如：

赋予ming用户 create, alter, drop, insert, update, delete权限在njwangbo中的t_studnet表上

grant create,alter, drop, insert, update, delete on njwangbo.t_studnet to ming@'%';

给root授予在任意主机(%)访问任意数据库的所有权限 

grant all privileges  on  *.*  to root@’%’ identified by ‘123456’ with grant option;

给root授予在指定数据库上的权限
 
**回收权限**

格式：

revoke 权限 on 数据库.*|表 from 用户名@IP地址;

如

回收ming用户在t_studnet表上的select权限

revoke select on njwangbo.t_studnet from ming@'%';

回收ming在njwangbo数据库所有数据对象上的所有权限

revoke all on *.* from ming@'%';

**查看用户的权限**

show grants for 用户名@IP地址

**删除某个用户**

drop user 用户名@IP地址;

delete from user where user=’’; 

**修改密码**

使用create user创建用户时，相当于在mysql数据库的user表中插入一行数据。如果修改某个用户的信息（权限，密码），则可以直接修改user表中的列即可。
修改某用户的密码

    use mysql;
    update user set password=password('root')  where user = 'root';
    flush privileges;
    
    或者 set password for 用户名=password（'root');

**重命名用户**

RENAME USER old_user TO new_user


**数学函数**

向上取整ceil(8.1);

向下取整floor(8.9);

取余数mod(10,3);

四舍五入round(10.572,1);

截断 truncate(7.123456,2);

**查询**

    SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT

    a. select_expr
    -- 可以用 * 表示所有字段。
    select * from tb;
    -- 可以使用表达式（计算公式、函数调用、字段也是个表达式）
    select stu, 29+25, now() from tb;
    -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
    - 使用 as 关键字，也可省略 as.
    select stu+10 as add10 from tb;

    b. FROM 子句
    用于标识查询来源。
    -- 可以为表起别名。使用as关键字。
    SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- from子句后，可以同时出现多个表。
    -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。
    SELECT * FROM tb1, tb2;
    -- 向优化符提示如何选择索引
    USE INDEX、IGNORE INDEX、FORCE INDEX
    SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
    SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;

    c. WHERE 子句
    -- 从from获得的数据源中进行筛选。
    -- 整型1表示真，0表示假。
    -- 表达式由运算符和运算数组成。
    -- 运算数：变量（字段）、值、函数返回值
    -- 运算符：
    =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
    in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
    is/is not 加上ture/false/unknown，检验某个值的真假
    <=>与<>功能相同，<=>可用于null比较

    d. GROUP BY 子句, 分组子句
    GROUP BY 字段/别名 [排序方式]
    分组后会进行排序。升序：ASC，降序：DESC
    以下[合计函数]需配合 GROUP BY 使用：
    count 返回不同的非NULL值数目  count(*)、count(字段)
    sum 求和
    max 求最大值
    min 求最小值
    avg 求平均值
    group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。

    e. HAVING 子句，条件子句
    与 where 功能、用法相同，执行时机不同。
    where 在开始时执行检测数据，对原数据进行过滤。
    having 对筛选出的结果再次进行过滤。
    having 字段必须是查询出来的，where 字段必须是数据表存在的。
    where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
    where 不可以使用合计函数。一般需用合计函数才会用 having
    SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。

    f. ORDER BY 子句，排序子句
    order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...
    升序：ASC，降序：DESC
    支持多个字段的排序。

    g. LIMIT 子句，限制结果数量子句
    仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
    limit 起始位置, 获取条数
    省略第一个参数，表示从索引0开始。limit 获取条数

    h. DISTINCT, ALL 选项
    distinct 去除重复记录
    默认为 all, 全部记录

## 列约束 ##

1. PRIMARY 主键
    - 能唯一标识记录的字段，可以作为主键。
    - 一个表只能有一个主键。
    - 主键具有唯一性。
    - 声明字段时，用 primary key 标识。
        也可以在字段列表之后声明
            例：create table tab ( id int, stu varchar(10), primary key (id));
    - 主键字段的值不能为null。
    - 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。
        例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age));

2. UNIQUE 唯一索引（唯一约束）
    使得某字段的值也不能重复。

3. NULL 约束
    null不是数据类型，是列的一个属性。
    表示当前列是否可以为null，表示什么都没有。
    null, 允许为空。默认。
    not null, 不允许为空。
    insert into tab values (null, 'val');
        -- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null

4. DEFAULT 默认值属性
    当前字段的默认值。
    insert into tab values (default, 'val');    -- 此时表示强制使用默认值。
    create table tab ( add_time timestamp default current_timestamp );
        -- 表示将当前时间的时间戳设为默认值。
        current_date, current_time

5. AUTO_INCREMENT 自动增长约束
    自动增长必须为索引（主键或unique）
    只能存在一个字段为自动增长。
    默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;

6. COMMENT 注释
    例：create table tab ( id int ) comment '注释内容';

7. FOREIGN KEY 外键约束
    用于限制主表与从表数据完整性。
    alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
        -- 将表t1的t1_id外键关联到表t2的id字段。
        -- 每个外键都有一个名字，可以通过 constraint 指定
    存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。
    作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。
    MySQL中，可以对InnoDB引擎使用外键约束：
    语法：
    foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]

## 视图 ##

什么是视图：
    视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
    视图具有表结构文件，但不存在数据文件。
    对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
    视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

**-- 创建视图**

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
    - 视图名必须唯一，同时不能与表重名。
    - 视图可以使用select语句查询到的列名，也可以自己指定相应的列名。
    - 可以指定视图执行的算法，通过ALGORITHM指定。
    - column_list如果存在，则数目必须等于SELECT语句检索的列数
    - 
**-- 查看结构**
    SHOW CREATE VIEW view_name

**-- 删除视图**
    - 删除视图后，数据依然存在。
    - 可同时删除多个视图。
    DROP VIEW [IF EXISTS] view_name ...

**-- 修改视图结构**
    - 一般不修改视图，因为不是所有的更新视图都会映射到表上。
    ALTER VIEW view_name [(column_list)] AS select_statement

## 索引 ##

**概念**

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

**优点：**

1、所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引

2、大大加快数据的查询速度

**缺点：**

1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

**索引的分类**

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE (`column`)

INDEX(普通索引)      
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

组合索引  
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

**Mysql各种索引区别：**

普通索引(INDEX)：最基本的索引，没有任何限制

唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。 

全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。


**建立索引的原则：**

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

## MyISAM和InnoDB两者区别##



**事务支持**

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

**全文索引**

MyISAM：支持(FULLTEXT类型的)全文索引

InnoDB：不支持(FULLTEXT类型的)全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

**外键**

MyISAM：不支持
InnoDB：支持

**CURD操作**

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

**MyISAM和InnoDB两者的应用场景：**

1) MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

2) InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

转载于:https://www.cnblogs.com/wsscr/p/10712805.html