15-1 库和表相关操作

一 库相关操作:
0创建数据库:
语法(help create database)

CREATE DATABASE 数据库名 charset utf8;

1 查看数据库
show databases;
show create database db1;
select database();

2 选择数据库
USE 数据库名

3 删除数据库
DROP DATABASE 数据库名;

4 修改数据库
alter database db1 charset utf8;

二 表相关操作
创建表:
#语法:
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意:
1. 在同一张表中,字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

例子一:

1 创建表
create table t1(id int,name varchar(50),sex enum('male','female'),age int(3));
表中插入数据
insert into t1 values(1,'egon','male',18),(2,'alex','female',81); 顺序要和表结构一致

查看表结构
复制代码
 1 mysql> describe t1;
 2 +-------+-----------------------+------+-----+---------+-------+
 3 | Field | Type                  | Null | Key | Default | Extra |
 4 +-------+-----------------------+------+-----+---------+-------+
 5 | id    | int(11)               | YES  |     | NULL    |       |
 6 | name  | varchar(50)           | YES  |     | NULL    |       |
 7 | sex   | enum('male','female') | YES  |     | NULL    |       |
 8 | age   | int(3)                | YES  |     | NULL    |       |
 9 +-------+-----------------------+------+-----+---------+-------+
10 4 rows in set (0.00 sec)
复制代码

 



show create table t1\G; #查看表详细结构,可加\G

2 修改表ALTER TABLE

ALTER TABLE 表名 RENAME 新表名;


例子: alter table t1 rename t2;


3 增加表字段:(不能和原来的重复)

复制代码
 1 mysql> alter table student10 add name varchar(20) not null,add age int(3) not null default 22;
 2 Query OK, 0 rows affected (1.06 sec)
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> describe student10;
 6 +-------+-------------+------+-----+---------+-------+
 7 | Field | Type        | Null | Key | Default | Extra |
 8 +-------+-------------+------+-----+---------+-------+
 9 | id    | int(11)     | YES  |     | NULL    |       |
10 | name  | varchar(20) | NO   |     | NULL    |       |
11 | age   | int(3)      | NO   |     | 22      |       |
12 +-------+-------------+------+-----+---------+-------+
13 3 rows in set (0.00 sec)
复制代码
4 在name字段之后添加一个class字段
复制代码
 1 mysql> alter table student10 add class varchar(10) not null after name;
 2 Query OK, 0 rows affected (1.00 sec)
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> describe student10;
 6 +-------+-------------+------+-----+---------+-------+
 7 | Field | Type        | Null | Key | Default | Extra |
 8 +-------+-------------+------+-----+---------+-------+
 9 | id    | int(11)     | YES  |     | NULL    |       |
10 | name  | varchar(20) | NO   |     | NULL    |       |
11 | class | varchar(10) | NO   |     | NULL    |       |
12 | age   | int(3)      | NO   |     | 22      |       |
13 +-------+-------------+------+-----+---------+-------+
14 4 rows in set (0.00 sec)
复制代码

5 添加到字段最前面
复制代码
 1 mysql> alter table student10 add sex enum('male','female') default 'male' first;
 2 Query OK, 0 rows affected (1.05 sec)
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> describe student10;
 6 +-------+-----------------------+------+-----+---------+-------+
 7 | Field | Type                  | Null | Key | Default | Extra |
 8 +-------+-----------------------+------+-----+---------+-------+
 9 | sex   | enum('male','female') | YES  |     | male    |       |
10 | id    | int(11)               | YES  |     | NULL    |       |
11 | name  | varchar(20)           | NO   |     | NULL    |       |
12 | class | varchar(10)           | NO   |     | NULL    |       |
13 | age   | int(3)                | NO   |     | 22      |       |
14 +-------+-----------------------+------+-----+---------+-------+
15 5 rows in set (0.00 sec)
复制代码

6 删除字段
mysql> alter table student10 drop sex;
Query OK, 0 rows affected (0.97 sec)
Records: 0 Duplicates: 0 Warnings: 0

7 修改字段 modify
例子一:
复制代码
 1 mysql> alter table student10 modify age int(5);
 2 Query OK, 0 rows affected (1.21 sec)
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> describe student10;
 6 +-------+-------------+------+-----+---------+-------+
 7 | Field | Type        | Null | Key | Default | Extra |
 8 +-------+-------------+------+-----+---------+-------+
 9 | id    | int(11)     | YES  |     | NULL    |       |
10 | name  | varchar(20) | NO   |     | NULL    |       |
11 | class | varchar(10) | NO   |     | NULL    |       |
12 | age   | int(5)      | YES  |     | NULL    |       |
13 +-------+-------------+------+-----+---------+-------+
14 4 rows in set (0.00 sec)
复制代码

 


例子二:

复制代码
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
Query OK, 0 rows affected (1.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe student10;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| class | varchar(10) | NO   |     | NULL    |                |
| age   | int(5)      | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
复制代码

 


8. 对已经存在的表增加复合主键
mysql> alter table service2
-> add primary key(host_ip,port);

9. 增加主键
mysql> alter table student1
-> modify name varchar(10) not null primary key;

10. 增加主键和自动增长
mysql> alter table student1
-> modify id int not null primary key auto_increment;
11. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null;

b. 删除主键
mysql> alter table student10
-> drop primary key;

12 复制表
复制表结构+记录 (key不会复制: 主键、外键和索引

复制代码
mysql> create table new_hu select * from department;
Query OK, 2 rows affected (0.77 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from new_hu;
+-------+-----------------------------------+
| id    | name                              |
+-------+-----------------------------------+
|     1 | 欧德博爱技术有限事业部            |
| 22222 | 艾利克斯人力资源部                |
+-------+-----------------------------------+
2 rows in set (0.00 sec)
只复制表结构:用like
mysql> create table t10 like department;
Query OK, 0 rows affected (0.66 sec)

mysql> select * from t10;
Empty set (0.01 sec)
复制代码

13 删除表

DROP TABLE 表名;
posted @   huningfei  阅读(157)  评论(0编辑  收藏  举报
编辑推荐:
· .NET 依赖注入中的 Captive Dependency
· .NET Core 对象分配(Alloc)底层原理浅谈
· 聊一聊 C#异步 任务延续的三种底层玩法
· 敏捷开发:如何高效开每日站会
· 为什么 .NET8线程池 容易引发线程饥饿
阅读排行:
· 终于决定:把自己家的能源管理系统开源了!
· 外部H5唤起常用小程序链接规则整理
· C#实现 Winform 程序在系统托盘显示图标 & 开机自启动
· 了解 ASP.NET Core 中的中间件
· 详解:订单履约系统规划
levels of contents
点击右上角即可分享
微信分享提示