二.表操作

一.库操作

创建库:

                create  database    库名  charset  utf-8;

查看所有数据库:

　　　　 show  databases;

使用数据库:

  　　　  use  库名;

二.存储引擎

 

1.什么是存储引擎(存储方式):

　　　　同样的数据,对你来说也是同样的表,只不过指定不同的存储引擎,代表了数据在存储的时候的不同方式

2.mysql 有多种不同的存储引擎:

　　　　有的时候我们对数据的存取速度要求很高 但是对数据的稳定性要求没有那么高
             有的时候我们对数据的查询速度要求高,但是对修改和删除的效率要求没有那么高
             有的时候我们需要建立表与表之间的联系

3.最常用 InnoDB、(yin no db)  MyISAM (mai  ai  sa m)   *****

                 mysql5.5 - 5.7
               mysql 5.5 MyISAM 默认的存储引擎
               mysql 5.6 InnoDB 默认的存储引擎

4.mysql中存储引擎决定的事情:(特性)

1.行级锁 : 保证了一份数据的安全性                         

2.表级锁 : 把整个一张表锁起来

3.什么是并发性：

　　　　　　更好的支持了在并发情况中数据修改的安全性

4.什么是事务 :

　　　　　　让多条sql语句变成一个整体,要成功一起成功,如果其中有一条语句失败了,那么多有的状态都要回归到开始事务之前

5.什么是外键 : 

　　　　　　突出了表与表之间的联系

6.什么是索引 :

　　　　　　书的目录 帮助我们加快查询速

7.cache :(内存的高速缓冲)   把一部分数据放到内存中

存储引擎的区别:  ********

　　　　  innodb 支持事务\支持外键\行级锁\聚集索引  用于事务处理应用程序
                myisam 不支持事务\不支持外键\表级锁\非聚集索引  用于以读操作和插入操作为主，只有很少的更新和删除操作，并且对                                                                                                       事务的完整性、并发性要求不高
                memory 只能在内存中存储表数据\存取速度快\断电数据丢失
                blackhole 无论写入什么数据都不存储在表中\但是照常生成binlog日志\用于数据的分流  

 

 

存储引擎在mysql中的使用:　　

查看当前的默认存储引擎:

mysql> show variables like "default_storage_engine";

查询当前数据库支持的存储引擎:

mysql> show engines;

 create database db1;  创建一个db1库

 use db1;    切换到db1

 

1.create table innodb_t (id int);   创建一个innodb引擎的表

 show tables;  查看所有的表

create table myisam_t (id int);  创建一个表  没有指定引擎

 drop table myisam_t;  删除一个表

 

2. create table myisam_t (id int) engine = Myisam;  创建一个Myisam引擎的表

3.create table memory_t (id int) engine = memory;  创建一个memory引擎的表

4.create table blackhole_t (id int) engine = blackhole;  创建一个blackhole引擎的表

往表里插数据:

  insert  into  innodb_t     values (1);     能正常存储的

  insert  into  myisam_t    values (1);     能正常存储的

  insert  into  memory_t    valuse(1);     断电消失的

 insert   into    black_t       values(1);     根本存不进去

 

查看数据:

 select *from myisam_t;

  select *from innodb_t;

  select *from black_t;   没有数据

 

在配置文件中指定存储引擎:

#my.ini文件
[mysqld]
default-storage-engine=INNODB

　　mysql的工作流程

四.表介绍

 表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段           

     五.创建表结构

1. 操作文件（表）
   先切换到文件夹下：use db1
   增：create table t1(id int,name char);
   查：show tables;                      #查看所有表的名字
   改：alter table t1 modify name char(3);
      alter table t1 change name name1 char(2);
   删：drop table t1;
    

2. 操作文件中的内容（记录）
   增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
   查：select * from t1;
   改：update t1 set name='sb' where id=2;
   删：delete from t1 where id=1;

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

列 :create table staff_info(    #staff_info员工信息表
          id int unsigned,
          name varchar(11),
          age int,
          sex enum('male','female'),
          phone char(11),
          job char(10)
 );

2.查看表结构有两种方式:

1.查看表的字段信息

        　  describe 表名;   或   desc 表名;
2. 如果要查存储引擎/默认编码等其他信息
        　 show create table 表名;

 

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

1.mysql中的数据类型
　　　   1).数字类型
     　　　　　　int   4个字节
     　　　　　　bigint 8个字节
 　　　　2).字符串类型
   　　　　　　  char
   　　　　　　  varchar   不给长度,默认是1
　　　　3).集合和枚举类型= 
   　　　　　　 set('1','2','3'),多选
  　　　　　     enum('1','2'),多选1
　　　　4).时间类型　

插入数据

 1. insert into 表名字 (字段名,....) values (值1,值2,值3...);
                例子 : insert into staff_info (name,age) values ('yuan',25);

                 

2. insert into 表名字 values (值1,值2,值3...) ;
　　　 　例子 : insert into staff_info values (1,'heige',83,'male','13838383388','IT') ;

                

 

六.基础数据类型

1.数字类型
    　 1) tinyint 1个字节 8位
        　　　　print(2**8)
      　　　　  0 - 255 无符号的情况下
        　　　　2**(8-1) -128-127
    　  2) int   4个字节
       　　　　 int(5)
        　　　　create table t1 (id1 int,in2 int(5));
        　　　　 我们设置的int后面的长度只是 约束了 显示宽度
         　　　　所以我们输入的数字的长度可以是范围内任意的数字而不必担心宽度的设置
         　　　　但是如果我们放入了超出范围的数字,那么只能写入当前范围内能描述的最大值

             

# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)

# 像t1中插入数据1，1
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)

# 可以看出结果上并没有异常
mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

# 那么当我们插入了比宽度更大的值，会不会发生报错呢？
mysql> insert into t1 values (111111,111111);
Query OK, 1 row affected (0.00 sec)

# 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响
mysql> select * from t1;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000001 |  00001 |
| 0000111111 | 111111 |
+------------+--------+
2 rows in set (0.00 sec)

# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 当给id1添加的数据大于214748364时，可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)

# 当给id2添加的数据大于214748364时，会报错
mysql> insert into t1 values (2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

       3)float(一共m位,小数部分占n位)
       　　　　 保留n位小数,并且四舍五入保留

# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)

# 像t1中插入数据1，1
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)

# 可以看出结果上并没有异常
mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

# 那么当我们插入了比宽度更大的值，会不会发生报错呢？
mysql> insert into t1 values (111111,111111);
Query OK, 1 row affected (0.00 sec)

# 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响
mysql> select * from t1;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000001 |  00001 |
| 0000111111 | 111111 |
+------------+--------+
2 rows in set (0.00 sec)

# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 当给id1添加的数据大于214748364时，可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)

# 当给id2添加的数据大于214748364时，会报错
mysql> insert into t1 values (2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

 

# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入1.23，结果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)

# 向表中插入1.234，会发现4都被截断了
mysql> insert into t2 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)

# 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
3 rows in set (0.00 sec)

# 建新表去掉参数约束
mysql> create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02 sec)

# 分别插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

# 发现decimal默认值是(10,0)的整数
mysql> select * from t3;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
| 1.234 | 1.234 |    1 |
+-------+-------+------+
1 row in set (0.00 sec)

# 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别
mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t3;
+---------+--------------------+------+
| id1     | id2                | id3  |
+---------+--------------------+------+
|   1.234 |              1.234 |    1 |
| 1.23556 | 1.2355555555555555 |    1 |
+---------+--------------------+------+
2 rows in set (0.00 sec)

2.时间类型
    　　date     年月日      可以为空 没有默认值    表示时间范围长
    　    datetime 年月日时分秒 可以为空 没有默认值    表示时间范围长
   　     timestamp年月日时分秒 不能为空 默认插入当前时间 并且随update语句更新字段  表示时间范围短
           date支持的写入 insert into 表名 values('2018-12-17')
           datetime\ timestamp支持的写入
        　　　　　　 insert into 表名 values('2018-12-17 11:56:20')
       　　　　　　  insert into 表名 values('20181217115620')
       　　　　　　  insert into 表名 values(20181217115620)

data /time / datetime 示例

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

timestamp 示例

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

timestamp 示例2
mysql>createtable t6 (t1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql>desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type      |Null|Key|Default| Extra                       |+-------+-----------+------+-----+-------------------+-----------------------------+| t1    |timestamp| NO   ||CURRENT_TIMESTAMP|onupdateCURRENT_TIMESTAMP|+-------+-----------+------+-----+-------------------+-----------------------------+1 row inset (0.01 sec)

mysql>insertinto t6 values (19700101080001);
Query OK, 1 row affected (0.00 sec)

mysql>select*from t6;
+---------------------+| t1                  |+---------------------+|1970-01-0108:00:01|+---------------------+1 row inset (0.00 sec)
# timestamp时间的下限是19700101080001
mysql>insertinto t6 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000'forcolumn't1' at row 1

mysql>insertinto t6 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# timestamp时间的上限是2038-01-1911:14:07
mysql>insertinto t6 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08'forcolumn't1' at row 1
mysql>

year 示例

mysql> create table t7 (y year);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values (2018);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
+------+
| y    |
+------+
| 2018 |
+------+
1 row in set (0.00 sec)

datetime  示例

mysql> create table t8 (dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t8 values ('2018-9-26 12:20:10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t8 values ('2018/9/26 12+20+10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values ('20180926122010');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values (20180926122010);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+---------------------+
| dt                  |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+
4 rows in set (0.00 sec)

　　

3.字符串类型
           char 定长,存在空间浪费,速度快
                           char(12)     长度比较固定的内容 : 身份证号 手机号码 银行卡号 用户名 密码
                            'abc'   --> 'abc  '
                            'abcdef'--> 'abcde'
         varchar 不定长,不浪费空间,速度慢
                           varchar(255)   评论
                           'abc'   --> 'abc3'  占4个字节
                           'abcde' --> 'abcde5'占6个字节

mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t9 values ('ab  ','ab  ');
Query OK, 1 row affected (0.00 sec)

# 在检索的时候char数据类型会去掉空格
mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

# 来看看对查询结果计算的长度
mysql> select length(v),length(c) from t9;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

# 给结果拼上一个加号会更清楚
mysql> select concat(v,'+'),concat(c,'+') from t9;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
1 row in set (0.00 sec)

# 当存储的长度超出定义的长度，会截断
mysql> insert into t9 values ('abcd  ','abcd  ');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
| abcd | abcd |
+------+------+
2 rows in set (0.00 sec)

　　

 4.set和enum
     　　enum
        　　　　 单选
        　　　　 选错不生效
     　　set
       　　　　  多选
       　　　　  选错不写入
       　　　　  自动去重

mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)

# 选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t10 values ('nezha','male');
Query OK, 1 row affected (0.00 sec)

# 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值
mysql> insert into t10 values ('nezha','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));
Query OK, 0 rows affected (0.01 sec)

# 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能
mysql> insert into t11 values ('yuan','烫头,喝酒,烫头');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| yuan | 喝酒,烫头     |
+------+---------------+
1 row in set (0.00 sec)

# 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，
mysql> insert into t11 values ('alex','烫头,翻车,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

　　

修改表结构:

1. 修改表名
      alter table 表名 rename  新表名;
                      

2. 增加字段
      alter table 表名
                            add 字段名  数据类型 [完整性约束条件…],
                            add 字段名  数据类型 [完整性约束条件…];
     

     列:alter table t0 add age int ,add sex enum("male","female");   枚举类型

 

3. 删除字段

     alter table 表名 drop 字段名;
     

4. 修改字段
      1. alter table表名 modify 字段名 数据类型 [完整性约束条件…];

                           

       2.alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
                       

      3. alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…];
                             

5.修改字段排列顺序/在增加的时候指定字段位置

     alter table 表名 add  字段名  数据类型 [完整性约束条件…]  FIRST;

                         

    alter table 表名 add  字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

   

     alter table 表名 change  字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;

    alter table 表名 modify 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

    

 删除表结构

drop table 表名;