MySQL 02

时间：2019-10-29 20:11:22 阅读：66 评论：0 收藏：0 [点我收藏+]

操作数据表
- 增加
  - 列约束
  - 列类型
- 修改
- 查询
- 复制
操作表记录
- 增加
- 删除
  - delete
  - truncate
  - 区别
- 修改
- 查询

操作数据表

先切换到当前数据库下
- use database 表名

增加

create table 文件名(字段名列类型 [约束条件], 字段名列类型 [约束条件]);

列约束

auto_increment 自增
primary key 主键索引, 加快索引速度, 列的值不能重复
not null 标识该字段不能为空
default 为该字段设置默认值

mysql> create table t3(
    -> id int unsigned auto_increment primary key,
    -> name char(10) not null default 'xxx',
    -> age int not null default 0);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t3;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     | xxx     |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

列类型

整形
- tinyint --->int8, 占用一个字节
- smallint --->int16
- mediumint ---> int24
- int ---> int32 (默认使用int就好)
- bigint ---> int64
浮点型
- float 单精度, float32
- double 双精度, float64
- decimal(m, d) m代表总个数, d代表小数个数
字符串
- char() 定长, 简单粗暴, 浪费空间, 存取速度快
- varchar() 变长, 节省空间, 存取速度慢
- 如果不能确定插入数据的大小, 一般建议使用varchar(255)

时间日期

year
date
time
datetime (推荐使用)
timestamp

mysql> create table t6(
    -> y year,
    -> d date,
    -> dt datetime,
    -> ts timestamp
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from t6;
+------+------------+---------------------+---------------------+
| y    | d          | dt                  | ts                  |
+------+------------+---------------------+---------------------+
| 2019 | 2019-10-29 | 2019-10-29 16:15:46 | 2019-10-29 16:15:46 |
+------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

枚举

列出所有的选项

mysql> create table t7(
    -> id int auto_increment primary key,
    -> gender enum('male', 'female')
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into t7 (gender) values ('unknown');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t7;
+----+--------+
| id | gender |
+----+--------+
|  1 | male   |
|  2 |        |
+----+--------+
2 rows in set (0.00 sec)

删除

drop table 表名

修改

修改表名

alter table 表名 rename 新表名;

mysql> alter table t7 rename t77;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
| t4            |
| t5            |
| t6            |
| t77           |
+---------------+
7 rows in set (0.00 sec)

增加字段

alter table 表名 add 字段名列类型 [列约束], add...;
alter table 表名 add 字段名列类型 [列约束] first;
alter table 表名 add 字段名列类型 [列约束] after 字段名;

# 添加字段到最后
mysql> desc t3;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     | xxx     |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table t3 add gender enum('male','female', 'unknown') not null defau
lt 'unknown';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+--------+---------------------------------+------+-----+---------+-------------
| Field  | Type                            | Null | Key | Default | Extra
+--------+---------------------------------+------+-----+---------+-------------
| id     | int(10) unsigned                | NO   | PRI | NULL    |auto_increment 
| name   | char(10)                        | NO   |     | xxx     |
| age    | int(11)                         | NO   |     | 0       |
| gender | enum('male','female','unknown') | NO   |     | unknown |
+--------+---------------------------------+------+-----+---------+-------------
4 rows in set (0.01 sec)

# 添加字段到首部
mysql> alter table t3 add province char(10) not null default "China" first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+----------+---------------------------------+------+-----+---------+-----------
| Field    | Type                            | Null | Key | Default | Extra
+----------+---------------------------------+------+-----+---------+-----------
| province | char(10)                        | NO   |     | China   |
| id       | int(10) unsigned                | NO   | PRI | NULL    |auto_increment
| name     | char(10)                        | NO   |     | xxx     |
| age      | int(11)                         | NO   |     | 0       |
| gender   | enum('male','female','unknown') | NO   |     | unknown |
+----------+---------------------------------+------+-----+---------+-----------
5 rows in set (0.00 sec)

# 添加字段到某个字段后面
mysql> alter table t3 add birthday date not null default "1900-1-1" after age;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+----------+---------------------------------+------+-----+------------+--------
| Field    | Type                            | Null | Key | Default    | Extra
+----------+---------------------------------+------+-----+------------+--------
| province | char(10)                        | NO   |     | China      |
| id       | int(10) unsigned                | NO   | PRI | NULL       |auto_increment 
| name     | char(10)                        | NO   |     | xxx        |
| age      | int(11)                         | NO   |     | 0          |
| birthday | date                            | NO   |     | 1900-01-01 |
| gender   | enum('male','female','unknown') | NO   |     | unknown    |
+----------+---------------------------------+------+-----+------------+--------
6 rows in set (0.00 sec)

修改字段

更新字段(类型, 约束) alter table 表名 modify 字段名列类型 [列约束];
替换字段(字段名, 类型, 约束) alter table 表名 change 旧字段名新字段名列类型 [列约束];

# 更新字段(类型, 约束)
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t1 modify name char(8) not null default '';
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(8) | NO   |     |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 替换字段(字段名, 类型, 约束)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(8) | NO   |     |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t1 change name familyname char(10) default 'zhao';
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t1;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id         | int(11)  | YES  |     | NULL    |       |
| familyname | char(10) | YES  |     | zhao    |       |
+------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除字段

alter table 表名 drop 字段名;

查询

show tables
desc 表名 查看表描述

复制

复制表结构 + 记录（key不会复制: 主键、外键和索引）
- create table 表名 select * from 表名;
复制表结构（key不会复制: 主键、外键和索引）
- create table 表名 select * from 表名 where 1=2; 条件不成立, 因此不会复制记录
- create table 表名 like 表名;

操作表记录

增加

insert into 表名 (字段1,字段2,字段3...) values (值1,值2,值3...);
insert into 表名 values (值1,值2,值3...);
insert into 表名 values (值1,值2,值3...), (值1,值2,值3...), (值1,值2,值3...);

mysql> insert into t1 (id, familyname) values (3,'chen');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------------+
| id   | familyname |
+------+------------+
|    1 | bigb       |
|    3 | chen       |
+------+------------+
2 rows in set (0.00 sec)

t1 values (5, 'gao'),(7,'pan'),(9,'tao')' at line 1
mysql> insert into t1 values (5, 'gao'),(7,'pan'),(9,'tao');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------------+
| id   | familyname |
+------+------------+
|    1 | bigb       |
|    3 | chen       |
|    5 | gao        |
|    7 | pan        |
|    9 | tao        |
+------+------------+
5 rows in set (0.00 sec)

删除

delete

delete from 表名 where 条件;

mysql> select * from t1;
+------+------------+
| id   | familyname |
+------+------------+
|    1 | zhao       |
|    3 | chen       |
|    5 | gao        |
|    7 | pan        |
|    9 | tao        |
+------+------------+
5 rows in set (0.00 sec)

mysql> delete from t1 where id=9;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------------+
| id   | familyname |
+------+------------+
|    1 | zhao       |
|    3 | chen       |
|    5 | gao        |
|    7 | pan        |
+------+------------+
4 rows in set (0.00 sec)

truncate

truncate 表名;

mysql> truncate t1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1;
Empty set (0.00 sec)

区别

delete之后，插入数据从上一次主键自增加1开始， truncate则是从1开始
delete删除，是一行一行的删除， truncate是全选删除, truncate删除的速度是高于delete的

修改

update 表名 set 字段1=值1, 字段2=值2, where 条件;

mysql> select * from t1;
+------+------------+
| id   | familyname |
+------+------------+
|    1 | bigb       |
|    3 | chen       |
|    5 | gao        |
|    7 | pan        |
|    9 | tao        |
+------+------------+
5 rows in set (0.00 sec)

mysql> update t1 set familyname='zhao' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------------+
| id   | familyname |
+------+------------+
|    1 | zhao       |
|    3 | chen       |
|    5 | gao        |
|    7 | pan        |
|    9 | tao        |
+------+------------+
5 rows in set (0.00 sec)

查询

select * from 表名;
select 字段名 from 表名;
select 字段1, 字段2... from 表名 where 条件;

mysql> select * from t1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | alpha |  18 |
|  2 | bigb  |  19 |
|  3 | blake |  20 |
|  4 | black |  20 |
+----+-------+-----+
4 rows in set (0.00 sec)

mysql> select id,name from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | alpha |
|  2 | bigb  |
|  3 | blake |
|  4 | black |
+----+-------+
4 rows in set (0.00 sec)

mysql> select * from t1 where id>2;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  3 | blake |  20 |
|  4 | black |  20 |
+----+-------+-----+
2 rows in set (0.00 sec)

mysql> select * from t1 where id>2 and id<4;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  3 | blake |  20 |
+----+-------+-----+
1 row in set (0.00 sec)

mysql> select * from t1 where id between 2 and 4;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  2 | bigb  |  19 |
|  3 | blake |  20 |
|  4 | black |  20 |
+----+-------+-----+
3 rows in set (0.00 sec)

去重select distinct 字段名 from t1;

mysql> select * from t1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | alpha |  18 |
|  2 | bigb  |  19 |
|  3 | blake |  20 |
|  4 | black |  20 |
|  5 | bigb  |  19 |
+----+-------+-----+
5 rows in set (0.00 sec)

mysql> select distinct name from t1;
+-------+
| name  |
+-------+
| alpha |
| bigb  |
| blake |
| black |
+-------+
4 rows in set (0.00 sec)

模糊查询

以x开头 select * from 表名 where 字段 like ‘x%‘
以x结尾 select * from 表名 where 字段 like ‘%x‘
包含x select * from 表名 where 字段 like ‘%x%‘

  mysql> select * from t1;
  +----+-------+-----+
  | id | name  | age |
  +----+-------+-----+
  |  1 | alpha |  18 |
  |  2 | bigb  |  19 |
  |  3 | blake |  20 |
  |  4 | black |  20 |
  +----+-------+-----+
  4 rows in set (0.00 sec)

  mysql> select * from t1 where name like 'b%';
  +----+-------+-----+
  | id | name  | age |
  +----+-------+-----+
  |  2 | bigb  |  19 |
  |  3 | blake |  20 |
  |  4 | black |  20 |
  +----+-------+-----+
  3 rows in set (0.00 sec)

  mysql> select * from t1 where name like '%b';
  +----+------+-----+
  | id | name | age |
  +----+------+-----+
  |  2 | bigb |  19 |
  +----+------+-----+
  1 row in set (0.00 sec)


  mysql> select * from t1 where name like '%a%';
  +----+-------+-----+
  | id | name  | age |
  +----+-------+-----+
  |  1 | alpha |  18 |
  |  3 | blake |  20 |
  |  4 | black |  20 |
  +----+-------+-----+
  3 rows in set (0.00 sec)

MySQL 02

原文：https://www.cnblogs.com/bigb/p/11761091.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)