MySQL 02

操作数据表
操作表记录

操作数据表

先切换到当前数据库下
- 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 字段名列类型 [列约束], ...;
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)

posted @ 2019-10-29 19:46 MrBigB 阅读(116) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MrBigB

Easy Come Easy Go

MySQL 02

操作数据表

增加

列约束

列类型

删除

修改

修改表名

增加字段

修改字段

删除字段

查询

复制

操作表记录

增加

删除

delete

truncate

区别

修改

查询

公告