mysql中常用SQL语句

chenjiali

DDL相关：　　
#创建数据库
　　
MariaDB [(none)]> CREATE DATABASE testdb;
　　
Query OK, 1 row affected (0.00 sec)
　　
#查看数据库
　　
MariaDB [(none)]> SHOW DATABASES;
　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
| performance_schema |
　　
| test               |
　　
| testdb             |
　　
+--------------------+
　　
5 rows in set (0.02 sec)
　　

　　
#使用testdb数据库，
　　
MariaDB [(none)]> use testdb;
　　
Database changed
　　

　　
SHOW CHARACTER SET;查看字符集
　　
SHOW COLLATIONS;查看排序规则
　　
创建表的三种方式：
　　
(1) 直接创建；
　　
CREATE TABLE [IF NOT EXISTS] tb_name (col1 datatype 修饰符, col2 datatype 修饰符) ENGINE=''
　　
数据类型：
　　
    字符：
　　
    变长字符：VARCHAR(#), VARBINARY(#)
　　
    定长字符: CHAR(#), BINARY(#)
　　
    内建类型：ENUM, SET
　　
    对象：TEXT, BLOB
　　
    数值：
　　
    精确数值：
　　
      整型：INT
　　
      TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT
　　
      十进制：DECIMAL
　　
    近似数值：
　　
      单精度: FLOAT
　　
      双精度: DOUBLE
　　
    日期时间型：
　　
      日期：DATE
　　
      时间：TIME
　　
      日期时间：DATETIME
　　
      时间戳：TIMESTAMP
　　
      年份：YEAR(2), YEAR(4)
　　
修饰符：
　　
    所有类型适用：
　　
    NOT NULL
　　
    DEFAULT
　　
    PRIMARY KEY
　　
    UNIQUE KEY
　　
    数值型适用：
　　
    UNSIGNED
　　
    AUTO_INCREMENT
　　
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1, col2 type2, ...)
　　
可选：col type1
　　
    PRIMARY KEY(col1,...)#可以定义关联主键
　　
    INDEX(col1, ...)  #以此字段为基础创建索引
　　
    UNIQUE KEY(col1, ...)
　　
    表选项：
　　
    ENGINE [=] engine_name
　　
    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
　　
(2) 通过查询现存的表创建；新表会被直接插入查询而来的数据；
　　
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
　　
    [(create_definition,...)]
　　
    [table_options]
　　
    [partition_options]
　　
    select_statement
　　
(3) 通过复制现存的表的表结构创建；不复制数据；
　　
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
　　
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
　　
#创建表students，定义id为无符号非空int型，并且id是主键，name是VARCHAR非空型，最多容纳20字符，age是无符号tinyint型
　　
MariaDB [testdb]> CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED);
　　
Query OK, 0 rows affected (0.04 sec)
　　

　　
#查看表详细信息
　　
MariaDB [testdb]> DESC students;
　　
+-------+---------------------+------+-----+---------+-------+
　　
| Field | Type                | Null | Key | Default | Extra |
　　
+-------+---------------------+------+-----+---------+-------+
　　
| id    | int(10) unsigned    | NO   | PRI | NULL    |       |
　　
| name  | varchar(20)         | NO   |     | NULL    |       |
　　
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
　　
+-------+---------------------+------+-----+---------+-------+
　　
3 rows in set (0.01 sec)
　　
#定义联合主键---此次创建id和name组合起来为主键，这样只要求id+name和其他行不同即可
　　
MariaDB [testdb]> CREATE TABLE tbl2 (id int UNSIGNED NOT NULL, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, PRIMARY KEY(id,name));
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
MariaDB [testdb]> DESC tbl2;
　　
+-------+---------------------+------+-----+---------+-------+
　　
| Field | Type                | Null | Key | Default | Extra |
　　
+-------+---------------------+------+-----+---------+-------+
　　
| id    | int(10) unsigned    | NO   | PRI | NULL    |       |
　　
| name  | varchar(20)         | NO   | PRI | NULL    |       |
　　
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
　　
+-------+---------------------+------+-----+---------+-------+
　　
3 rows in set (0.00 sec)
　　

　　
#查看正规创建命令：\G使用竖向格式
　　
MariaDB [testdb]> SHOW CREATE TABLE students\G;
　　
*************************** 1. row ***************************
　　
       Table: students
　　
Create Table: CREATE TABLE `students` (
　　
  `id` int(10) unsigned NOT NULL,
　　
  `name` varchar(20) NOT NULL,
　　
  `age` tinyint(3) unsigned DEFAULT NULL,
　　
  PRIMARY KEY (`id`)
　　
) ENGINE=InnoDB DEFAULT CHARSET=latin1
　　
1 row in set (0.00 sec)
　　

　　
#查看表状态：
　　
MariaDB [testdb]> SHOW TABLE STATUS LIKE 'students'\G;
　　
*************************** 1. row ***************************
　　
           Name: students
　　
         Engine: InnoDB
　　
        Version: 10
　　
     Row_format: Compact    #compact表示使用紧致型存储
　　
           Rows: 0
　　
Avg_row_length: 0
　　
    Data_length: 16384    #表中数据大小
　　
Max_data_length: 0        #表的最大容量，此处0表示无上限
　　
   Index_length: 0        #索引大小（字节为单位）
　　
      Data_free: 10485760    #目前已分配但未被使用的空间
　　
Auto_increment: NULL        #下一次该增长到数字几
　　
    Create_time: 2015-10-27 21:57:09
　　
    Update_time: NULL
　　
     Check_time: NULL        #最近一次执行checktable命令或myisamchk根据检查表的时间
　　
      Collation: latin1_swedish_ci    #排序规则
　　
       Checksum: NULL    #校验和
　　
Create_options:         #创建表时额外指明的选项
　　
        Comment:
　　
1 row in set (0.00 sec)
　　

　　
#删除表：
　　
DROP TABLE [IF EXISTS] 'tbl_name';
　　

　　
修改表：
　　
ALTER TABLE 'tbl_name'
　　
    字段：
　　
        添加字段：add
　　
        ADD col1 data_type [FIRST|AFTER col_name]
　　
        删除字段：drop
　　
        修改字段：alter, change, modify
　　
    索引:
　　
        添加索引：add
　　
        删除索引: drop
　　
    表选项
　　
        修改:
　　
查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
　　

　　
#使用ADD在name段之后添加gender段，类型为枚举类型
　　
MariaDB [testdb]> ALTER TABLE students ADD gender ENUM('m','f') AFTER name;
　　
Query OK, 0 rows affected (0.02 sec)
　　
Records: 0  Duplicates: 0  Warnings: 0
　　

　　
MariaDB [testdb]> DESC students;
　　
+--------+---------------------+------+-----+---------+-------+
　　
| Field  | Type                | Null | Key | Default | Extra |
　　
+--------+---------------------+------+-----+---------+-------+
　　
| id     | int(10) unsigned    | NO   | PRI | NULL    |       |
　　
| name   | varchar(20)         | NO   |     | NULL    |       |
　　
| gender | enum('m','f')       | YES  |     | NULL    |       |
　　
| age    | tinyint(3) unsigned | YES  |     | NULL    |       |
　　
+--------+---------------------+------+-----+---------+-------+
　　
4 rows in set (0.00 sec)
　　

　　
使用CHANGE修改表，注意之前是主键修改时不需要指明主键，UNSIGNED用于修饰int类型，所以必须放在int后
　　
MariaDB [testdb]> ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
　　
ERROR 1068 (42000): Multiple primary key defined
　　
MariaDB [testdb]> ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL;
　　
Query OK, 0 rows affected (0.02 sec)
　　
Records: 0  Duplicates: 0  Warnings: 0
　　

　　
MariaDB [testdb]> DESC students;
　　
+--------+---------------------+------+-----+---------+-------+
　　
| Field  | Type                | Null | Key | Default | Extra |
　　
+--------+---------------------+------+-----+---------+-------+
　　
| sid    | int(10) unsigned    | NO   | PRI | NULL    |       |
　　
| name   | varchar(20)         | NO   |     | NULL    |       |
　　
| gender | enum('m','f')       | YES  |     | NULL    |       |
　　
| age    | tinyint(3) unsigned | YES  |     | NULL    |       |
　　
+--------+---------------------+------+-----+---------+-------+
　　
4 rows in set (0.00 sec)
　　

　　
#增加唯一键：唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。
　　
MariaDB [testdb]> ALTER TABLE students ADD UNIQUE KEY(name);
　　
Query OK, 0 rows affected (0.22 sec)
　　
Records: 0  Duplicates: 0  Warnings: 0
　　

　　
MariaDB [testdb]> DESC students;
　　
+--------+---------------------+------+-----+---------+-------+
　　
| Field  | Type                | Null | Key | Default | Extra |
　　
+--------+---------------------+------+-----+---------+-------+
　　
| sid    | int(10) unsigned    | NO   | PRI | NULL    |       |
　　
| name   | varchar(20)         | NO   | UNI | NULL    |       |
　　
| gender | enum('m','f')       | YES  |     | NULL    |       |
　　
| age    | tinyint(3) unsigned | YES  |     | NULL    |       |
　　
+--------+---------------------+------+-----+---------+-------+
　　
4 rows in set (0.00 sec)
　　
#添加索引：如果创建索引时没给名字，则索引名为字段名。
　　
索引是特殊数据结构；定义在查找时作为查找条件的字段；
　　
创建索引：
　　
CREATE INDEX index_name ON tbl_name (index_col_name,...);
　　
删除索引：
　　
DROP INDEX index_name ON tbl_name;
　　
在age字段添加一个索引
　　
MariaDB [testdb]> ALTER TABLE students ADD INDEX(age);
　　
Query OK, 0 rows affected (0.01 sec)
　　
Records: 0  Duplicates: 0  Warnings: 0
　　
#查看索引
　　
MariaDB [testdb]> SHOW INDEXES FROM students\G;
　　
*************************** 1. row ***************************
　　
        Table: students
　　
   Non_unique: 0
　　
     Key_name: PRIMARY
　　
Seq_in_index: 1
　　
  Column_name: sid
　　
    Collation: A
　　
  Cardinality: 0
　　
     Sub_part: NULL
　　
       Packed: NULL
　　
         Null:
　　
   Index_type: BTREE
　　
      Comment:
　　
Index_comment:
　　
*************************** 2. row ***************************
　　
        Table: students
　　
   Non_unique: 0
　　
     Key_name: name
　　
Seq_in_index: 1
　　
  Column_name: name
　　
    Collation: A
　　
  Cardinality: 0
　　
     Sub_part: NULL
　　
       Packed: NULL
　　
         Null:
　　
   Index_type: BTREE
　　
      Comment:
　　
Index_comment:
　　
*************************** 3. row ***************************
　　
        Table: students
　　
   Non_unique: 1
　　
     Key_name: age
　　
Seq_in_index: 1
　　
  Column_name: age
　　
    Collation: A
　　
  Cardinality: 0
　　
     Sub_part: NULL
　　
       Packed: NULL
　　
         Null: YES
　　
   Index_type: BTREE
　　
      Comment:
　　
Index_comment:
　　
3 rows in set (0.00 sec)
　　
#删除索引：(索引一旦不使用应该即刻删除，否则会影响数据库性能)
　　
DROP INDEX name ON students;
　　
查看是否删除成功：
　　
MariaDB [testdb]> SHOW INDEX FROM students\G;
　　
*************************** 1. row ***************************
　　
        Table: students
　　
   Non_unique: 0
　　
     Key_name: PRIMARY
　　
Seq_in_index: 1
　　
  Column_name: sid
　　
    Collation: A
　　
  Cardinality: 0
　　
     Sub_part: NULL
　　
       Packed: NULL
　　
         Null:
　　
   Index_type: BTREE
　　
      Comment:
　　
Index_comment:
　　
1 row in set (0.00 sec)
　　

　　
#删除字段：
　　
MariaDB [testdb]> ALTER TABLE students DROP age;