欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL第三课

程序员文章站 2022-08-20 09:15:45
首先创建一个数据库: CREATE DATABASE ku; Query OK, 1 row affected 查看一下是否有此数据库: SHOW DATABASES; + +| Database |+ +| information_schema || ku || mysql || performa ......

首先创建一个数据库:

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