mysql的操作语句

一、DDL：数据定义语言

1.创建数据库（create）

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_db              |
| mysql              |
| performance_schema |
| sys                |
| tracing_process    |
| tracing_system     |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_db              |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
| tracing_process    |
| tracing_system     |
+--------------------+
8 rows in set (0.00 sec)

2.创建表（create）

切换数据库
mysql> use mydb;

Database changed
mysql> show tables;
Empty set (0.00 sec)

创建表
mysql> create table mytable(
    -> id int not null primary key,
    -> name varchar(32),
    -> age int,
    -> salary double
    -> );
Query OK, 0 rows affected (0.03 sec)

查看表是否创建成功
mysql> show tables;

+----------------+
| Tables_in_mydb |
+----------------+
| mytable        |
+----------------+
1 row in set (0.00 sec)

查看创建后的表结构
mysql> desc mytable;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(32) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

更详细的表结构(可以看到存储索引ENGINE和字符编码CHARSET)
mysql> show create table mytable \G;

*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

3.复制表结构

mysql> create table mytable2 like mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mytable        |
| mytable2       |
+----------------+
2 rows in set (0.00 sec)

mysql> desc mytable2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(32) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4.删除（drop）

mysql> show tables;

+----------------+
| Tables_in_mydb |
+----------------+
| mytable        |
| mytable2       |
+----------------+
2 rows in set (0.00 sec)

删除表
mysql> drop table mytable2;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

+----------------+
| Tables_in_mydb |
+----------------+
| mytable        |
+----------------+
1 row in set (0.00 sec)

删除数据库
mysql> create database myddb;
Query OK, 1 row affected (0.00 sec)

mysql> drop database myddb;
Query OK, 0 rows affected (0.02 sec)

5.修改操作（alter）

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mytable        |
+----------------+
1 row in set (0.00 sec)

修改表名（rename）
mysql> alter table mytable rename mytb;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mytb           |
+----------------+
1 row in set (0.00 sec)

修改字段属性（modify）
mysql> alter table mytb modify column name varchar(255);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytb;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | varchar(255) | YES  |     | NULL    |       |
| age    | int(11)      | YES  |     | NULL    |       |
| salary | double       | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改字段属性和名称（change）
mysql> alter table mytb change name name1 varchar(32);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytb;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name1  | varchar(32) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

添加字段
mysql> alter table mytb add column address varchar(255);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytb;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name1   | varchar(32)  | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | NULL    |       |
| salary  | double       | YES  |     | NULL    |       |
| address | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除字段

mysql> alter table mytb drop column address;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytb;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name1  | varchar(32) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

二、DML：数据操纵语言

1.添加(insert)

未指定字段，全部添加
mysql> insert into mytb values(1,'123',12,20.0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from mytb;
+----+-------+------+--------+
| id | name1 | age  | salary |
+----+-------+------+--------+
|  1 | 123   |   12 |     20 |
+----+-------+------+--------+
1 row in set (0.00 sec)

指定字段，部分添加
mysql> insert into mytb(name1,age) values ("zhi",20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytb;
+----+-------+------+--------+
| id | name1 | age  | salary |
+----+-------+------+--------+
|  1 | 123   |   12 |     20 |
|  2 | zhi   |   20 |   NULL |
+----+-------+------+--------+
2 rows in set (0.00 sec)

添加多条信息
mysql> insert into mytb(name,age,salary) values ("bing",20,2000),("ze",25,3000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mytb;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  2 | zhi  |   20 |   NULL |
|  3 | bing |   20 |   2000 |
|  4 | ze   |   25 |   3000 |
+----+------+------+--------+
4 rows in set (0.00 sec)

2.修改（update）

mysql> select * from mytb;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  2 | zhi  |   20 |   NULL |
|  3 | bing |   20 |   2000 |
|  4 | ze   |   25 |   3000 |
+----+------+------+--------+
4 rows in set (0.00 sec)


mysql> update mytb set salary=500 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from mytb;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  2 | zhi  |   20 |    500 |
|  3 | bing |   20 |   2000 |
|  4 | ze   |   25 |   3000 |
+----+------+------+--------+
4 rows in set (0.00 sec)

3.删除（delete）

mysql> select * from mytb;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  2 | zhi  |   20 |    500 |
|  3 | bing |   20 |   2000 |
|  4 | ze   |   25 |   3000 |
+----+------+------+--------+
4 rows in set (0.00 sec)

删除一条语句
mysql> delete from mytb where id =4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from mytb;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  2 | zhi  |   20 |    500 |
|  3 | bing |   20 |   2000 |
+----+------+------+--------+
3 rows in set (0.00 sec)

删除多条语句
mysql> delete from mytb where id in(2,3);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from mytb;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
+----+------+------+--------+
1 row in set (0.00 sec)

4.查询（select）

查询所有字段
mysql> select * from mytb;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  5 | bing |   20 |   2000 |
|  6 | ze   |   25 |   3000 |
|  7 | bing |   20 |   2000 |
|  8 | ze   |   25 |   3000 |
+----+------+------+--------+
5 rows in set (0.00 sec)

查询单个字段
mysql> select name from mytb;
+------+
| name |
+------+
| 123  |
| bing |
| ze   |
| bing |
| ze   |
+------+
5 rows in set (0.00 sec)

去除重复的查询(distinct必须在查询字段的首位）
mysql> select distinct name from mytb;
+------+
| name |
+------+
| 123  |
| bing |
| ze   |
+------+
3 rows in set (0.00 sec)

查询指定范围的数据（limit：默认从下标0开始，也就是第一条）
mysql> select * from mytb limit 3;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  5 | bing |   20 |   2000 |
|  6 | ze   |   25 |   3000 |
+----+------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from mytb limit 0,3;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 123  |   12 |     20 |
|  5 | bing |   20 |   2000 |
|  6 | ze   |   25 |   3000 |
+----+------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from mytb limit 1,3;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  5 | bing |   20 |   2000 |
|  6 | ze   |   25 |   3000 |
|  7 | bing |   20 |   2000 |
+----+------+------+--------+
3 rows in set (0.00 sec)

5.升序和降序（order by column_name desc/asc,默认asc)

默认升序排列（asc）
mysql> select * from mytb;
+----+--------+------+--------+
| id | name   | age  | salary |
+----+--------+------+--------+
|  1 | 123    |   12 |     20 |
|  5 | bing   |   20 |   2000 |
|  6 | ze     |   25 |   3000 |
|  7 | bing   |   20 |   2000 |
|  8 | ze     |   25 |   3000 |
|  9 | bingze |   20 | 300000 |
+----+--------+------+--------+
6 rows in set (0.00 sec)

指定按照某个字段升序排列（order by xxx asc【可不写，默认asc】）

mysql> select * from mytb order by salary;
+----+--------+------+--------+
| id | name   | age  | salary |
+----+--------+------+--------+
|  1 | 123    |   12 |     20 |
|  5 | bing   |   20 |   2000 |
|  7 | bing   |   20 |   2000 |
|  6 | ze     |   25 |   3000 |
|  8 | ze     |   25 |   3000 |
|  9 | bingze |   20 | 300000 |
+----+--------+------+--------+
6 rows in set (0.01 sec)

mysql> select * from mytb order by salary asc;
+----+--------+------+--------+
| id | name   | age  | salary |
+----+--------+------+--------+
|  1 | 123    |   12 |     20 |
|  5 | bing   |   20 |   2000 |
|  7 | bing   |   20 |   2000 |
|  6 | ze     |   25 |   3000 |
|  8 | ze     |   25 |   3000 |
|  9 | bingze |   20 | 300000 |
+----+--------+------+--------+
6 rows in set (0.00 sec)


降序排列（order by xxx desc）
mysql> select * from mytb order by salary desc;
+----+--------+------+--------+
| id | name   | age  | salary |
+----+--------+------+--------+
|  9 | bingze |   20 | 300000 |
|  6 | ze     |   25 |   3000 |
|  8 | ze     |   25 |   3000 |
|  5 | bing   |   20 |   2000 |
|  7 | bing   |   20 |   2000 |
|  1 | 123    |   12 |     20 |
+----+--------+------+--------+
6 rows in set (0.00 sec)