MySQL数据库的基本操作

01-创建数据库
 
1、创建数据库
    查看数据库
    show databases;    
    
    创建数据库test
    create database test;
    
    查看test数据库所有信息
    show create database test\G
    
2、删除数据库
    删除数据库
    drop database test;        删除数据库时连带数据一起删除
    delete database test;    删除时不删除数据
    delete * from test1;
    
3、数据库存储引擎
    mysql数据目录:/home/mysql/data
    mysql的核心是存储引擎
    常见存储引擎InnoDB  MyISAM  Memory  CSV
 
查看mysql支持的存储引擎
 show engines\G
*************************** 9. row ***************************
       Engine: InnoDB        存储引擎名称
      Support: DEFAULT        是否支持存储引擎
      Comment: Supports transactions, row-level locking, and foreign keys    存储引擎功能
 Transactions: YES
           XA: YES
   Savepoints: YES
 
1)InnoDB(事务型)
        提供了提交、回滚、系统崩溃前恢复的安全性操作。
        支持行级索,专为处理大数据量数据库而设计
         
2)MyISAM(web数据库存储不支持事务)
        混合使用命令,减少碎片的产生,最大的索引数64,最大键长度1000B
        使用MyISAM引擎创建的数据库,产生3个文件
        frm文件存储表定义(.MYD)  索引(.MYI)
        
4、数据表的基本操作
1)创建数据表
     按行和列的方式存储,每一行唯一一条记录,每一列代表记录中的某个字段或者是域。
    create table <table_name>
          (
          字段1,数据类型 [约束条件],
          字段1,数据类型 [约束条件],
          ..............
          [表级别约束条件]
          );
        mysql> create table test     创建数据表test
            -> (id int(11),
            -> name varchar(30));
            
        mysql> show tables;        查看数据表
        +------------------+
        | Tables_in_test01 |
        +------------------+
        | test             |
        +------------------+
        
        mysql> select * from test;        查看数据表内容
        
        mysql> desc test;        查看数据表结构
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int(11)     | YES  |     | NULL    |       |
        | name  | varchar(30) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
 
主键约束(PRIMARY KEY),数据表中一个列或者多个列,它能唯一标识表中的某一条记录
      单字段主键,由一个字段组成
列级别主键约束
        mysql> create table test01
            -> (id int(11) PRIMARY KEY,
            -> name varchar(30));
        
        mysql> desc test01;
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int(11)     | NO   | PRI | NULL    |       |
        | name  | varchar(30) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        
        表级别约束条件
        mysql> create table test02 (id int(11), name varchar(30), PRIMARY KEY(id));
        mysql> desc test02;
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int(11)     | NO   | PRI | 0       |       |
        | name  | varchar(30) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
 
 
        
        多字段联合主键,由多个字段组成
        mysql> create table test03 (id int(11), name varchar(30), PRIMARY KEY(id,name));
        mysql> desc test03;
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int(11)     | NO   | PRI | 0       |       |
        | name  | varchar(30) | NO   | PRI |         |       |
        +-------+-------------+------+-----+---------+-------+
 
外键约束(FOREIGN KEY),一个表可以有一个或多个外键
        1.能够在两个表之间建立数据连接,数据引用时保证数据的一致性完整性
        2.定义外键后,不允许删除另一个表中具有关联关系的记录
        
      主表:对于两个表具有关联关系的,且具有主键的表
      从表:对于两个表具有关联关系的,且具有外键的表
格式
  CONSTARAINT <外键名> FOREIGN KEY <字段名> REFERENCES <主表名> 主键列
 
mysql> CREATE TABLE test05 (id int(11) PRIMARY KEY,name varchar(30));  
mysql> CREATE TABLE test06 (id int(11) PRIMARY KEY,name varchar(30),CONSTRAINT test0506 FOREIGN KEY(id) REFERENCES test05(id));
 
非空约束 (NOT NULL)
    mysql> CREATE TABLE test04 (id int(11),name varchar(30) NOT NULL);
    mysql> DESC test04;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(30) | NO   |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    
 
 
    唯一性约束 (UNIQUE)
      该列唯一 但只能出现一个空值(可以出现空值)
    mysql> CREATE TABLE test07 (id int(11) UNIQUE,name varchar(30));
    mysql> DESC test07;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  | UNI | NULL    |       |
    | name  | varchar(30) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    
    
    默认约束(DEFAULT)某列的默认值
    mysql> CREATE TABLE test08 (id int(11) DEFAULT 1 PRIMARY KEY,name varchar(30));
    mysql> DESC test08;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | 1       |       |
    | name  | varchar(30) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    
    
    属性值自动增加(AUTO_INCREMENT)
    mysql> CREATE TABLE test09 (id int(11) PRIMARY KEY AUTO_INCREMENT,name varchar(30));
    mysql> desc test09;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(30) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    
      验证结果
    mysql> INSERT INTO test09 (name) VALUES('chenyun');
    mysql> select * from test09;
    +----+---------+
    | id | name    |
    +----+---------+
    |  1 | chenyun |
    |  2 | chenyun |
    +----+---------+
 
 
02-数据表的结构
 
查看数据表结构
    mysql> describe test09;
    mysql> desc test09;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(30) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    Field        字段名称
    Type         数据类型
    Null         是否为空
    Key          PRI 主键    UNI  UNIQUE(唯一性约束)
    Default      默认值
    Extra        扩展的值(设置增加的属性)
    
    
    mysql> show create table test09\G
    *************************** 1. row ***************************
           Table: test09
    Create Table: CREATE TABLE `test09` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
 
 
03-修改数据表
修改数据表(ALTER TABLE)
 
3.1 修改表名称(RENAME)
     mysql> alter table test09 rename ceshi09;
     mysql> show tables;
     +------------------+
      | Tables_in_test01 |
     +------------------+
     | ceshi09          |
     +------------------+
 
3.2 修改字段名(CHANGE)     
     mysql> alter table test09 change id newid int(11);
     mysql> describe test09;
     +-------+-------------+------+-----+---------+-------+
     | Field | Type        | Null | Key | Default | Extra |
     +-------+-------------+------+-----+---------+-------+
     | newid | int(11)     | NO   | PRI | 0       |       |
     | name  | varchar(40) | YES  |     | NULL    |       |
     +-------+-------------+------+-----+---------+-------+
 
3.3 修改字段数据类型(MODIFY)
     mysql> alter table ceshi09 modify name varchar(40);
     mysql> desc ceshi09;
     +-------+-------------+------+-----+---------+----------------+
     | Field | Type        | Null | Key | Default | Extra          |
     +-------+-------------+------+-----+---------+----------------+
     | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
     | name  | varchar(40) | YES  |     | NULL    |                |
     +-------+-------------+------+-----+---------+----------------+
 
3.4 添加字段(ADD)
     mysql> ALTER TABLE test09 ADD user int(10);    (默认)
     mysql> describe test09;
     +-------+-------------+------+-----+---------+-------+
     | Field | Type        | Null | Key | Default | Extra |
     +-------+-------------+------+-----+---------+-------+
     | newid | int(11)     | NO   | PRI | 0       |       |
     | name  | varchar(40) | YES  |     | NULL    |       |
     | user  | int(10)     | YES  |     | NULL    |       |
     +-------+-------------+------+-----+---------+-------+
     
     mysql> ALTER TABLE test09 ADD users int(10) first;        (在第一列添加)
     mysql> desc test09;
     +-------+-------------+------+-----+---------+-------+
     | Field | Type        | Null | Key | Default | Extra |
     +-------+-------------+------+-----+---------+-------+
     | users | int(10)     | YES  |     | NULL    |       |
     | name  | varchar(40) | YES  |     | NULL    |       |
     | user  | int(10)     | YES  |     | NULL    |       |
     +-------+-------------+------+-----+---------+-------+
     
     mysql> ALTER TABLE test09 ADD passwd int(20) AFTER newid;    (在某列后添加)
     mysql> desc test09;
     +--------+-------------+------+-----+---------+-------+
     | Field  | Type        | Null | Key | Default | Extra |
     +--------+-------------+------+-----+---------+-------+
     | users  | int(10)     | YES  |     | NULL    |       |
     | newid  | int(11)     | NO   | PRI | 0       |       |
     | passwd | int(20)     | YES  |     | NULL    |       |
     | name   | varchar(40) | YES  |     | NULL    |       |
     | user   | int(10)     | YES  |     | NULL    |       |
     +--------+-------------+------+-----+---------+-------+
 
3.5 删除字段
     mysql> ALTER TABLE test09 DROP users;
     mysql> describe test09;
     +--------+-------------+------+-----+---------+-------+
     | Field  | Type        | Null | Key | Default | Extra |
     +--------+-------------+------+-----+---------+-------+
     | newid  | int(11)     | NO   | PRI | 0       |       |
     | passwd | int(20)     | YES  |     | NULL    |       |
     | name   | varchar(40) | YES  |     | NULL    |       |
     | user   | int(10)     | YES  |     | NULL    |       |
     +--------+-------------+------+-----+---------+-------+
 
3.6 移动字段位置
     mysql> ALTER TABLE test09 MODIFY name varchar(40) first|after;
     ysql> desc test09;
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | name   | varchar(40) | YES  |     | NULL    |       |
    | newid  | int(11)     | NO   | PRI | 0       |       |
    | passwd | int(20)     | YES  |     | NULL    |       |
    | user   | int(10)     | YES  |     | NULL    |       |
    +--------+-------------+------+-----+---------+-------+
 
3.7 更改存储引擎
    mysql> show create table test09\G    修改前
    *************************** 1. row ***************************
           Table: test09
    Create Table: CREATE TABLE `test09` (
      `id` int(10) NOT NULL DEFAULT '0',
      `name` varchar(40) DEFAULT NULL,
      `passwd` int(20) DEFAULT NULL,
      `user` int(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    
    mysql> ALTER TABLE test09 ENGINE=MyISAM;
    mysql> show create table test09\G    修改后
    *************************** 1. row ***************************
           Table: test09
    Create Table: CREATE TABLE `test09` (
      `id` int(10) NOT NULL DEFAULT '0',
      `name` varchar(40) DEFAULT NULL,
      `passwd` int(20) DEFAULT NULL,
      `user` int(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
 
04-删除数据表
 
4.1 删除表的外键
      格式
 ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
添加外键
mysql> CREATE TABLE wj1 (id int(11) PRIMARY KEY,name varchar(20),CONSTRAINT test0101 FOREIGN KEY (id) REFERENCES test08(id));
 
删除外键        
mysql> ALTER TABLE wj1 DROP FOREIGN KEY test0101;    
 
 4.2 删除数据表
    4.2.1 删除未被关联的表
  
  mysql> DROP TABLE test01;
  mysql> DROP TABLE IF EXISTS test02;
  mysql> SHOW TABLES;
  +------------------+
  | Tables_in_test01 |
  +------------------+
  | test             |
  | test03           |  
  | wj1              |
  +------------------+
4.2.2 删除被关联的表
    
mysql> show create table test06\G
*************************** 1. row ***************************
       Table: test06
Create Table: CREATE TABLE `test06` (
              `id` int(11) NOT NULL,
             `name` varchar(30) DEFAULT NULL,
              PRIMARY KEY (`id`),
              CONSTRAINT `test0506` FOREIGN KEY (`id`) REFERENCES `test05` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    
首先删除关联的外键
mysql> ALTER TABLE test06 DROP FOREIGN KEY test0506;
    
再删除数据表
mysql> drop table if exists test05;
mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| test             |
| test04           |
| test06           |
+------------------+
 
 05-MySQL数据类型
 
5、MySQL数据类型
 
1)整数类型
  TINYINT       很小的整数,占用1字节
  SMALLINT      小的整数,占用2字节
  MEDIUMINT     中等整数,占用3字节
  INT(INTEGER)普通整数,占用4字节
  BIGINT        大的整数,占用8字节
  TINYINT: 1字节=8位(bits)    无符号: 2的8次方减1    255    有符号: 2的7次方减1    127
  SMALLINT:2字节=16位(bits)  无符号: 2的16次方减1  65535   有符号: 2的15次方减1  32767
  +---------+--------------------------------------------+----------------------------+
  |类型     |                    有符号                   |             无符号         |
  +---------+--------------------------------------------+----------------------------+
  |TINYINT  |                 -128~127                   |             0~255          |
  |SMALLINT |             -32768 ~ 32767                 |           0 ~ 65535        |
  |MEDIUMINT|           -8388608 ~ 8388607               |          0 ~ 16777215      |
  | INT     |         -2147483648 ~ 2147483647           |         0 ~ 4294967295     |
  |BIGINT   | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 184467440703709551615  |
  +---------+--------------------------------------------+----------------------------+
    
    
2)浮点数类型
   FLOAT    单精度浮点数    4字节
   DOUBLE    双精度浮点数    8字节
       
   格式
 create table type3 (a FLOAT(m,n), b DOUBLE(m,n));    m:精度 总位数        n:标度    小数的位数
       
    mysql> create table type3 (a FLOAT(5,1), b DOUBLE(5,1));
    mysql> INSERT INTO type3 values(5.12,5.15);
    mysql> select * from type3;
    +------+------+
    | a    | b    |
    +------+------+
    |  5.1 |  5.2 |
    +------+------+
    
3)定点数类型
       DECIMAL
    mysql> CREATE TABLE type4 ( c DECIMAL(5,1));
    mysql> INSERT INTO type4 VALUES(5.124);
    
    mysql> show warnings;
    +-------+------+--------------------------------------------------------------------------------------------------------+
    | Level | Code | Message |
    +-------+------+--------------------------------------------------------------------------------------------------------+
    | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for       the right syntax to use near 'warinings' at line 1 |
    +-------+------+---------------------------------------------------------------------------------------------------------
    
    mysql> SELECT * FROM type4;
    +------+
    | c    |
    +------+
    |  5.1 |
    +------+
    
4)日期与时间类型
   DATETIME        YYYY-MM-DD HH:MM:SS        8字节
   DATE             YYYY-MM-DD                3字节
   TIMESTAMP       YYYY-MM-DD HH:MM:SS        4字节
   TIME               HH:MM:SS                3字节        
   YEAR               YYYY                    1字节        表示年        1901-2155
    
      YEAR
    mysql> CREATE TABLE type5 (y YEAR);
    mysql> INSERT INTO type5 values(2010),('2010');   不带''的是整数类型,带''的是字符串类型
    mysql> SELECT * FROM type5;
    +------+
    | y    |
    +------+
    | 2010 |
    | 2010 |
    +------+
    
      TIME
    mysql> CREATE TABLE type6 (t TIME);
    mysql> INSERT INTO type6 values ('12:12:12'),('22:25:22');
    mysql> select * from type6;
    +----------+
    | t        |
    +----------+
    | 12:12:12 |
    | 22:25:22 |
    +----------+
    
5)文本字符串类型
   CHAR            固定,不可变长的
   VARCHAR         可变长字符串
   TINYTEXT        
   TEXT
   MEDIUMTEXT
   LONGTEXT
   ENUM
   SET
     
     CHAR  VARCHAR
    mysql> CREATE TABLE type7 (user CHAR(4),name VARCHAR(4));
    mysql> INSERT INTO type7 values ('ch  ','cy  ');
    mysql> SELECT * FROM type7;
    +------+------+
    | user | name |
    +------+------+
    | ch   | cy   |
    +------+------+
 
 
    mysql> SELECT CONCAT('(', user, ')'), concat('(', name,')') from type7;
    +------------------------+-----------------------+
    | CONCAT('(', user, ')') | concat('(', name,')') |
    +------------------------+-----------------------+
    | (ch)                   | (cy  )                |
    +------------------------+-----------------------+
 
 
06-插入数据
     INSERT插入数据
 
1、向表中所有的字段插入数据
 
  格式:INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...);
 
mysql> CREATE TABLE insert2 (id INT(10) AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, user VARCHAR(50) NOT NULL, passwd VARCHAR(50) DEFAULT '123456' NOT NULL, data FLOAT(5,2), time DATE);
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(10)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50) | NO   |     | NULL    |                |
| user   | varchar(50) | NO   |     | NULL    |                |
| passwd | varchar(50) | NO   |     | 123456  |                |
| data   | float(5,2)  | YES  |     | NULL    |                |
| time   | date        | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
 
mysql> INSERT INTO insert2 (id,name,user,passwd,data,time) VALUES (1,'chenyun','cy','qq123',3.1314,20190828);
mysql> SELECT * FROM insert2;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | qq123  | 3.13 | 2019-08-28 |
+----+---------+------+--------+------+------------+
 
 
2、向表中某字段插入数据
 
mysql> INSERT INTO insert2 (name,user,data,time) VALUES ('yanglin','yl',2.37,19981027);
mysql> INSERT INTO insert2 VALUES (3,'xumao','xm','xm123',2.299,19800807);
mysql> SELECT * FROM insert2;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | qq123  | 3.13 | 2019-08-28 |
|  2 | yanglin | yl   | 123456 | 2.37 | 1998-10-27 |
|  3 | xumao   | xm   | xm123  | 2.30 | 1980-08-07 |
+----+---------+------+--------+------+------------+
 
mysql> SELECT id,name,user,time FROM insert2 WHERE name = 'chenyun';
+----+---------+------+------------+
| id | name    | user | time       |
+----+---------+------+------------+
|  1 | chenyun | cy   | 2019-08-28 |
+----+---------+------+------------+
 
3、 向表中某字段同时插入多条数据
 
mysql> INSERT INTO insert2 (name,user,time) VALUES ('zhaya','zy',19931111),('luojun','lj',19911214);
mysql> select * from insert2;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | qq123  | 3.13 | 2019-08-28 |
|  2 | yanglin | yl   | 123456 | 2.37 | 1998-10-27 |
|  3 | xumao   | xm   | xm123  | 2.30 | 1980-08-07 |
|  4 | zhaya   | zy   | 123456 | NULL | 1993-11-11 |
|  5 | luojun  | lj   | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
 
 
4、 将查询结果插入至数据表
 
  格式:INSERT INTO 表名 (字段1,字段2) SELECT (字段1) FROM 表2 WHERE 查询条件
 
查询insert2数据插入insert1
mysql> INSERT INTO insert1 (id,name,user,passwd,data,time) SELECT * FROM insert2 WHERE name = 'chenyun';
mysql> select * from insert1;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | qq123  | 3.13 | 2019-08-28 |
+----+---------+------+--------+------+------------+
 
 
 07-数据表中更新与删除数据
 
  1、UPDATE更新数据
 
  格式:UPDATE 表名 SET 字段1 = 值1, 字段2=值2 WHERE (条件);
mysql> UPDATE update1 SET name = 'yangjun',passwd = 159369 WHERE id = 2;
mysql> SELECT * FROM update1 WHERE id = 2;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  2 | yangjun | yl   | 159369 | 2.37 | 1998-10-27 |
+----+---------+------+--------+------+------------+
 
mysql> INSERT INTO insert1 select * from update1 WHERE id > 1;
mysql> SELECT * FROM insert1;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | qq123  | 3.13 | 2019-08-28 |
|  2 | yangjun | yl   | 159369 | 2.37 | 1998-10-27 |
|  3 | xumao   | xm   | xm123  | 2.30 | 1980-08-07 |
|  4 | zhaya   | zy   | 123456 | NULL | 1993-11-11 |
|  5 | luojun  | lj   | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
 
 
mysql> UPDATE insert1 set passwd = '123456';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 5  Changed: 3  Warnings: 0
 
 
mysql> SELECT * FROM insert1;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | 123456 | 3.13 | 2019-08-28 |
|  2 | yangjun | yl   | 123456 | 2.37 | 1998-10-27 |
|  3 | xumao   | xm   | 123456 | 2.30 | 1980-08-07 |
|  4 | zhaya   | zy   | 123456 | NULL | 1993-11-11 |
|  5 | luojun  | lj   | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
 
注:使用UPDATE更新数据时,如果后面没有加WHERE条件语句,则指定更改的该列数据则全部更改,慎重使用
 
 
2、如何删除数据表中的数据
 
 格式: DELETE FROM 表名 WHERE 字段 = 值
 
mysql> SELECT * FROM update1;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | qq123  | 3.13 | 2019-08-28 |
|  2 | yangjun | yl   | 159369 | 2.37 | 1998-10-27 |
|  3 | xumao   | xm   | xm123  | 2.30 | 1980-08-07 |
|  4 | zhaya   | zy   | 123456 | NULL | 1993-11-11 |
|  5 | luojun  | lj   | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
 
mysql> DELETE FROM update1 WHERE id = 5; 
mysql> SELECT * FROM update1;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | qq123  | 3.13 | 2019-08-28 |
|  2 | yangjun | yl   | 159369 | 2.37 | 1998-10-27 |
|  3 | xumao   | xm   | xm123  | 2.30 | 1980-08-07 |
|  4 | zhaya   | zy   | 123456 | NULL | 1993-11-11 |
+----+---------+------+--------+------+------------+
 
mysql> DELETE FROM update1 WHERE id BETWEEN 1 AND 3;
mysql> SELECT * FROM update1;
+----+-------+------+--------+------+------------+
| id | name  | user | passwd | data | time       |
+----+-------+------+--------+------+------------+
|  4 | zhaya | zy   | 123456 | NULL | 1993-11-11 |
+----+-------+------+--------+------+------------+
 
 
3、清空数据表内容
 
  清空前
mysql> select * from update1;
+----+---------+------+--------+------+------------+
| id | name    | user | passwd | data | time       |
+----+---------+------+--------+------+------------+
|  1 | chenyun | cy   | 123456 | 3.13 | 2019-08-28 |
|  2 | yangjun | yl   | 123456 | 2.37 | 1998-10-27 |
|  3 | xumao   | xm   | 123456 | 2.30 | 1980-08-07 |
|  4 | zhaya   | zy   | 123456 | NULL | 1993-11-11 |
+----+---------+------+--------+------+------------+
 
 
mysql> truncate table update1;
 
  清空后
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| insert1        |
| test           |
| test1          |
| update1        |
+----------------+
 
mysql> desc update1;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(10)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50) | NO   |     | NULL    |                |
| user   | varchar(50) | NO   |     | NULL    |                |
| passwd | varchar(50) | NO   |     | 123456  |                |
| data   | float(5,2)  | YES  |     | NULL    |                |
| time   | date        | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
 
mysql> select * from update1;
Empty set (0.00 sec)
 
mysql> delete from update1;
Query OK, 5 rows affected (0.29 sec)
 
mysql> select * from update1;
 查询列数据,清空成功
 
 
4、truncate和delete
       
truncate 语句
  格式 :truncate table 表名
mysql> truncate table update1;
 
    不能与where一起使用
    truncate删除数据后是不可以rollback的
    truncate删除数据后会重置Identity(标识列、自增字段),相当于自增列会被置为初始值,又重新从1开始记录,而不是接着原来的ID数
    truncate删除数据后不写服务器log,整体删除速度快
    truncate删除数据后不激活trigger(触发器)
 
 
delete 语句
     格式:delete from 表名 (where 条件= 值)
mysql> delete from update1;
 
    可以where子句一起使用
    不会重置Identity字段,主键id继续增加
    会写入数据log文件(主从、主备需要)
    可以激活trigger(触发器)
 
 
 08-MySQL索引的创建与删除
 
1、索引的含义
    索引即存储在磁盘空间中的数据库结构,包含数据表中所有创建索引的记录。所有的存储引擎支持大于或等于16索引,索引的长度256字节以上。
 
BTREE:MyISAM InnoDB
HASH:MEMORY HEAP
 
2、索引和特点    
    1)创建唯一索引,可以保证数据表中的每一行数据唯一
    2)加快数据库的查询速度
    3)加快表与之间的连接
    4)耗费时间
    5)占用磁盘空间
 
3、索引的分类
    1)普通索引:允许定义索引的列中出现重复值和空值
    2)唯一索引:索引列的值可以为空,但必须唯一
                组合索引:组合的字段值必须唯一
                主键索引:不允许有空值
    3)单列索引:一个索引只包含一个列,一个表可以有多个单列索引
    4)组合索引:组合多个字段创建索引,(id,name,age)
    4)全文索引:FULLTEXT全文查找,允许定义索引的列中出现插入重复值和空值。仅支持CHAR    VARCHAR    TEXT 类型的字段中创建
 
4、索引的设计要求
    1)索引并非越多越好:(占用磁盘突然间、降低管理语句的性能)
    2)避免对经常发生变动的数据表建立索引
    3)数据量比较小无需创建索
 
 ********    实践    *********
 
CREATE TABLE     #在创建表的同时指定索引列
ALTER TABLE        #在已存在的表创建索引
CREATE INDEX     #在已存在的表添加索引
 
 1、创建表的同时创建索引
1.1 创建普通索引
  
  格式:CREATE TABLE 表名 (字段1,字段2) [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] (索引名) (字段名);
mysql> CREATE TABLE test1 ( id INT(11), name VARCHAR(40), year YEAR NOT NULL, INDEX (year));
mysql> SHOW CREATE TABLE test1 \G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(40) DEFAULT NULL,
  `year` year(4) NOT NULL,
  KEY `year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
mysql> explain select * from test1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
 
mysql> INSERT INTO test1 VALUES (1,'chenyun',2016);
mysql> explain select * from test1 where year = 2016 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: year
          key: year
      key_len: 1
          ref: const
         rows: 1
        Extra: Using index condition
 
mysql> explain select * from test1 where name = 'chenyun' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
 
1.2 创建唯一索引
mysql> create table test2 (id int(11) NOT NULL, name VARCHAR(40), time DATE,age int(11),unique index name_index (id));
mysql> insert into test2 values (1,'chenyun','20190115',28),(2,'chenxuan','20110802',22),(3,'wangxu','20111111',27);
mysql> explain select * from test2 where id = 2;
+----+-------------+-------+-------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | test2 | const | name_index    | name_index | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+------------+---------+-------+------+-------+
 
mysql> explain select * from test2 where name = 'chenyun';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
1.3 单列索引
mysql> create table test3 (id int(11) AUTO_INCREMENT PRIMARY KEY,name VARCHAR(40) NOT NULL,time DATE,INDEX one_index(name(40)));
mysql> INSERT INTO test3 VALUES (1,'chenyun',20110101),(2,'chenxuan',20170404),(3,'wangxu',20181010),(4,'yangjun',19920202);
mysql> EXPLAIN SELECT * FROM test3 WHERE name = 'chenyun' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test3
         type: ref
possible_keys: one_index
          key: one_index
      key_len: 122
          ref: const
         rows: 1
        Extra: Using index condition
 
1.4 组合索引
mysql> create table test4 ( id int(10) auto_increment primary key, name varchar(40) not null, age int(11) not null, time YEAR, INDEX more_index(id,name,age));
mysql> insert into test4 values (1,'chenyun',26,1993),(2,'chenxuan',22,1997),(3,'wangxu',33,1986);
mysql> explain select * from test4 where id = 2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test4
         type: const
possible_keys: PRIMARY,more_index
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
 
1.5 全文索引
mysql> create table test4 ( id int(10) auto_increment primary key, name varchar(40) not null, age int(11) not null, time YEAR, FULLTEXT INDEX fulltext_1(name(40))) ENGINE=MyISAM;
mysql> SHOW CREATE TABLE test4 \G
*************************** 1. row ***************************
       Table: test4
Create Table: CREATE TABLE `test4` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `age` int(11) NOT NULL,
  `time` year(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `fulltext_1` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
 
mysql> EXPLAIN SELECT * FROM test4 WHERE name='chenyun';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test4 | ALL  | fulltext_1    | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
 
posted @ 2020-03-25 00:15  向云而生(陈云)  阅读(187)  评论(0编辑  收藏  举报