mysql table的增删查改。
来源：互联网 发布：淘宝孙俪的网店在哪里 编辑：程序博客网 时间：2024/05/23 02:03
mysql> alter database day01
-> character set utf8
-> collate utf8_generate_ci;
ERROR 1273 (HY000): Unknown collation: 'utf8_generate_ci'
mysql> alter database day01
-> character set utf8
-> collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use day01;
Database changed
mysql> create table users
-> (
-> id int ,
-> name varchar(20),
-> age int,
-> address varchar(50),
-> birthday date
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| users |
+-----------------+
1 row in set (0.00 sec)
mysql> show create table users;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> show columns from users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table users
-> add column sex char(1);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table users
-> change column sex gender char(2);
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table users
-> modify column gender varchar(10);
Query OK, 0 rows affected (0.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table users rename to staff;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| staff |
+-----------------+
1 row in set (0.00 sec)
mysql> desc staff;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table staff
-> drop column id;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> drop table staff;
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
Empty set (0.00 sec)
-> character set utf8
-> collate utf8_generate_ci;
ERROR 1273 (HY000): Unknown collation: 'utf8_generate_ci'
mysql> alter database day01
-> character set utf8
-> collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use day01;
Database changed
mysql> create table users
-> (
-> id int ,
-> name varchar(20),
-> age int,
-> address varchar(50),
-> birthday date
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| users |
+-----------------+
1 row in set (0.00 sec)
mysql> show create table users;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> show columns from users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table users
-> add column sex char(1);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table users
-> change column sex gender char(2);
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table users
-> modify column gender varchar(10);
Query OK, 0 rows affected (0.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table users rename to staff;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| staff |
+-----------------+
1 row in set (0.00 sec)
mysql> desc staff;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table staff
-> drop column id;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> drop table staff;
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
Empty set (0.00 sec)
0 0
- mysql table的增删查改。
- Mysql的增删改查
- MySQL的增删改查
- Mysql的增删改查
- MYSQL的查增删改
- MySQL的增删改查
- MySQL的增删改查
- MySQL的增删查改
- MYSQL的增删改查
- MySQL的增删改查
- MySql的增删改查
- mysql增删改查
- MySQL增删改查
- MYSQL增删改查
- MySQL增删改查
- mysql增删改查
- MySQL---增删改查
- MySQL增删改查
- 23种设计模式
- Ruby Warrior —— 用游戏学习Ruby （附全部关卡代码）
- PHP实现Web Service的四种方法
- 内外网同时访问设置
- Linux 账号与身份管理1
- mysql table的增删查改。
- DB2创建数据库
- 关于好程序和好产品的思考
- 最有效率的Android开发技巧
- 抽象工厂vs工厂方法的区别
- Linux 账号与身份管理2
- DB2导入导出数据脚本
- UVALive - 4123 Glenbow Museum
- JavaScript Global 对象
