数据库表的增、删、改、查:
1、创建表:CREATE TABLE [IF NOT EXISTS] tab_name (col_name1 col_type1,col_name2 col_type2...)
备注:语法内容很复杂,使用并不复杂,以常用格式即可,难点在于创建表结构,明确哪些列、哪些类型、哪些列上创建索引等。
mysql> create table tb_admin(
-> id int auto_increment primary key,
-> user varchar(30) not null,
-> password varchar(30) not null,
-> createtime datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> create table tb_backup_admin like tb_admin;根据表结构创建新表
mysql> desc tb_backup_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
mysql> create table tb_admin_data as select * from tb_admin;连同数据一起复制
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
4 rows in set (0.00 sec)
2、查询表内容:select * from tab_name;
mysql> show create table tb_admin;查看创建表语法
+----------+-------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------+
| tb_admin | CREATE TABLE `tb_admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show columns from tb_admin;查看表结构
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe tb_admin;查看表结构
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from tb_admin;查看表索引信息
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_admin | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
3、更改表:修改表结构,加列、删列、修改列属性、修改表明
mysql> alter table tb_admin add email varchar(50) not null,
-> modify user varchar(40);增加列、修改列属性
Query OK, 0 rows affected (0.05 sec)
mysql> describe tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(40) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
| email | varchar(50) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> alter table tb_admin drop email;删除列
Query OK, 0 rows affected (0.03 sec)
mysql> describe tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(40) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table tb_admin change column user username varchar(30) null default null;更改列名
Query OK, 0 rows affected (0.07 sec)
mysql> describe tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> rename table tb_admin to tb_new_admin;重命名表名
mysql> describe tb_new_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
4、删除表:DROP TABLE [IF EXISTS] tb_name;
删除操作是危险举动,一定要慎重!
1、创建表:CREATE TABLE [IF NOT EXISTS] tab_name (col_name1 col_type1,col_name2 col_type2...)
备注:语法内容很复杂,使用并不复杂,以常用格式即可,难点在于创建表结构,明确哪些列、哪些类型、哪些列上创建索引等。
mysql> create table tb_admin(
-> id int auto_increment primary key,
-> user varchar(30) not null,
-> password varchar(30) not null,
-> createtime datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> create table tb_backup_admin like tb_admin;根据表结构创建新表
mysql> desc tb_backup_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
mysql> create table tb_admin_data as select * from tb_admin;连同数据一起复制
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
4 rows in set (0.00 sec)
2、查询表内容:select * from tab_name;
mysql> show create table tb_admin;查看创建表语法
+----------+-------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------+
| tb_admin | CREATE TABLE `tb_admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show columns from tb_admin;查看表结构
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe tb_admin;查看表结构
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from tb_admin;查看表索引信息
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_admin | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
3、更改表:修改表结构,加列、删列、修改列属性、修改表明
mysql> alter table tb_admin add email varchar(50) not null,
-> modify user varchar(40);增加列、修改列属性
Query OK, 0 rows affected (0.05 sec)
mysql> describe tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(40) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
| email | varchar(50) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> alter table tb_admin drop email;删除列
Query OK, 0 rows affected (0.03 sec)
mysql> describe tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(40) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table tb_admin change column user username varchar(30) null default null;更改列名
Query OK, 0 rows affected (0.07 sec)
mysql> describe tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> rename table tb_admin to tb_new_admin;重命名表名
mysql> describe tb_new_admin;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
4、删除表:DROP TABLE [IF EXISTS] tb_name;
删除操作是危险举动,一定要慎重!