MySQL第三课

database

首先创建一个数据库:

CREATE DATABASE ku;

Query OK, 1 row affected

查看一下是否有此数据库:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| ku                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set

使用此数据库:

 USE ku;
Database changed

创建数据表:

 CREATE TABLE biao(
    name VARCHAR(20),
    age INT(11),
    sex CHAR(11),
    love VARCHAR(11)
    );
Query OK, 0 rows affected

查看表结构:

DESC biao;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(11)    | YES  |     | NULL    |       |
| love  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

修改表名:

ALTER TABLE biao RENAME biao1;

Query OK, 0 rows affected

 

修改字段和类型:

ALTER TABLE biao1 CHANGE SEX SEX1 CHAR(20);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

用圆括号!!!!

 

再查看一边:

DESC biao1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| SEX1  | char(20)    | YES  |     | NULL    |       |
| love  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

修改字段的排列位置:

ALTER TABLE biao1 MODIFY NAME VARCHAR(20) AFTER SEX1;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看:

DESC biao1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | int(11)     | YES  |     | NULL    |       |
| SEX1  | char(20)    | YES  |     | NULL    |       |
| NAME  | varchar(20) | YES  |     | NULL    |       |
| love  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

注意改的是大写出现的也是大写

加一个think字段:

ALTER TABLE biao1 ADD think VARCHAR (10) AFTER LOVE;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看:

DESC biao1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | int(11)     | YES  |     | NULL    |       |
| SEX1  | char(20)    | YES  |     | NULL    |       |
| NAME  | varchar(20) | YES  |     | NULL    |       |
| love  | varchar(11) | YES  |     | NULL    |       |
| think | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set

删除think:

ALTER TABLE biao1 DROP think;
Query OK, 0 rows affected

Records: 0  Duplicates: 0  Warnings: 0

查看:

DESC biao1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | int(11)     | YES  |     | NULL    |       |
| SEX1  | char(20)    | YES  |     | NULL    |       |
| NAME  | varchar(20) | YES  |     | NULL    |       |
| love  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

删除此表:

DROP TABLE biao1;
Query OK, 0 rows affected

查看:

SHOW TABLES;
Empty set

删除数据库:

DROP DATABASE ku;
Query OK, 0 rows affected

查看:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set

 

 

 

以上是 MySQL第三课 的全部内容, 来源链接: utcz.com/z/531279.html

回到顶部