一、创建表(没有索引或者约束)
创建一个表拥有三个字段,分别是id(自动增长,不为空,主键),name(不为空),sex(不为空,默认值为1,1为男,0为女)
mysql> CREATE TABLE users
-> (
-> id INT(10) NOT NULL AUTO_INCREMENT,
-> name VARCHAR(5) NOT NULL,
-> sex VARCHAR(1) NOT NULL DEFAULT '1',
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected
二、查询当前数据库的所有表
mysql> SHOW TABLES;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| users |
+----------------------+
1 row in set
三、重名名表
mysql> ALTER TABLE USERS
-> RENAME students;
Query OK, 0 rows affected
四、删除表
mysql> DROP TABLE IF EXISTS uses;
Query OK, 0 rows affected
五、复制表
复制students表到usersbiao
mysql> CREATE TABLE users
-> LIKE students;
Query OK, 0 rows affected
六、操作字段
1、添加字段
在users表中添加字段address,不为空,varchar类型,长度为20
mysql> ALTER TABLE users
-> ADD address VARCHAR(20) NOT NULL;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
2、删除字段
删除users表中的address字段
mysql> ALTER TABLE users
-> DROP address;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
3、更新users表中id字段名为sid,其他属性不变
mysql> ALTER TABLE users
-> CHANGE id sid INT(10) NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
4、修改user表中sex的字段数据属性为char,长度为2,不为空
mysql> ALTER TABLE users
-> MODIFY sex char(2) NOT NULL;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
5、修改sex字段的默认值为0
mysql> ALTER TABLE users
-> ALTER sex SET DEFAULT '0';
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
六、显示表结构
mysql> DESC users;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| sid | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(5) | NO | | NULL | |
| sex | char(2) | NO | | 0 | |
+-------+------------+------+-----+---------+----------------+
3 rows in set