mysql 常见语句

创建数据库

create database practice;

显示数据库

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| dearabao           |
| hello              |
| link_draw          |
| link_group         |
| link_user          |
| mysql              |
| performance_schema |
| person             |
| practice           |
| tempdb             |
| test               |
+--------------------+

使用数据库

use practice;

创建数据表

create table `practice_2`(
    `id` int(11) unsigned not null auto_increment comment '主键id',
    `name` varchar(16) NOT NULL COMMENT '姓名',
    `age` tinyint(4) NOT NULL COMMENT '年龄',
    `CID` bigint(20) NOT NULL COMMENT '身份证ID',  
    PRIMARY KEY(`id`), 
    UNIQUE KEY `uk_cid` (`CID`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8 COMMENT='测试表';

显示数据表

show tables;

+--------------------+
| Tables_in_practice |
+--------------------+
| practice_1         |
+--------------------+

显示数据表结构

show create table practice_1;

+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                  |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| practice_1 | CREATE TABLE `practice_1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(16) NOT NULL COMMENT '姓名',
  `age` tinyint(4) NOT NULL COMMENT '年龄',
  `CID` bigint(20) NOT NULL COMMENT '身份证ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cid` (`CID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='测试表'             |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

插入数据

• 插入单条数据

insert practice_1 (`name`, `age`, `CID`) values ('jack', 20, 20072222);

• 插入多条数据

mysql> insert practice_1 (`name`, `age`, `CID`) values ('jack', 20, 20072222), ('jhon', 21, 20072003) on duplicate key update `name` = values(name), `age` = values(age), `CID` = values(CID);
Query OK, 1 row affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

• 插入命中唯一索引的数据

mysql> insert practice_1 (`name`, `age`, `CID`) values ('jack', 20, 20072222);
ERROR 1062 (23000): Duplicate entry '20072222' for key 'uk_cid'

mysql> insert practice_1 (`name`, `age`, `CID`) values ('jack', 20, 20072222) on duplicate key update `name` = values(name), `age` = values(age), `CID` = values(CID);
Query OK, 0 rows affected (0.00 sec)

删除数据

mysql> delete from practice_1 where CID = 20072103;
Query OK, 1 row affected (0.01 sec)

删除表数据

## 创建practice_2表
mysql> create table `practice_2`(
    -> `id` int(11) unsigned not null auto_increment comment '主键id',
    ->     `name` varchar(16) NOT NULL COMMENT '姓名',
    ->     `age` tinyint(4) NOT NULL COMMENT '年龄',
    ->     `CID` bigint(20) NOT NULL COMMENT '身份证ID',  
    ->     PRIMARY KEY(`id`), 
    ->     UNIQUE KEY `uk_cid` (`CID`)
    -> ) ENGINE=InnoDb DEFAULT CHARSET=utf8 COMMENT='测试表';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

## 
mysql> show tables;
+--------------------+
| Tables_in_practice |
+--------------------+
| practice_1         |
| practice_2         |
+--------------------+
2 rows in set (0.00 sec)

## 复制practice_1表数据到practice_2
mysql> insert into practice_2 select * from practice_1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from practice_2;
+----+------+-----+----------+
| id | name | age | CID      |
+----+------+-----+----------+
|  1 | jack |  20 | 20072222 |
|  2 | jhon |  21 | 20072003 |
|  3 | Jim  |  20 | 20072253 |
+----+------+-----+----------+
3 rows in set (0.00 sec)

mysql> truncate practice_2; // 表数据被删, 表结构保存

删除表

mysql> drop table practice_2;
Query OK, 0 rows affected (0.00 sec)

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

删除数据库

mysql> drop database practise;
Query OK, 0 rows affected (0.01 sec)

联表去重查询

mysql> select distinct(`name`) from (select * from practice_1 union all select * from practice_2) as t where 1 = 1;
+-------+
| name  |
+-------+
| jack  |
| jhon  |
| Jim   |
| henry |
+-------+