对于数据库中库和表的操作,主要牵扯到创建、删除、查看、修改
对于库的操作
1、创建数据库school
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
2、查看数据库school
mysql> show create database school;
±---------±----------------------------------------------------------------+
| Database | Create Database |
±---------±----------------------------------------------------------------+
| school | CREATE DATABASE school
/*!40100 DEFAULT CHARACTER SET utf8 */ |
±---------±----------------------------------------------------------------+
1 row in set (0.00 sec)
3、使用数据库school
mysql> use school;
Database changed
4、删除数据库
mysql> drop database school;
Query OK, 0 rows affected (0.00 sec)
5、查看当前使用数据库
mysql> select database();
±-----------+
| database() |
±-----------+
| school |
±-----------+
1 row in set (0.00 sec)
对于表的操作 在数据库school中创建表、查看表、删除表、改变表
1、查看数据库中所有表
mysql> show tables;
Empty set (0.00 sec)
2、创建表student 表里面有两个字段,id和name
mysql> create table student(id int,name varchar(50));
Query OK, 0 rows affected (0.01 sec)
3、查看表
mysql> show tables; (查看所有表)
±-----------------+
| Tables_in_school |
±-----------------+
| student |
±-----------------+
1 row in set (0.00 sec)
mysql> show create table student; (查看表具体创建过程)
±--------±-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------±-----------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE student
(
id
int(11) DEFAULT NULL,
name
varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc student; (查看表的结构)
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)
4、修改表的名字
mysql> rename table student to s1; (修改student表名为s1)
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| s1 |
+------------------+
5、修改表结构约束条件或者类型
mysql> alter table s1 modify id int(11) not null; 将id字段修改为非空类型 s1表名,id字段,int类型,not null表示非空,modify表示修改关键字
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc s1; 再次查看表的结构,可以看到字段id在null这个地方写了一个no,表示非空
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | YES | | NULL | |
6、修改字段名称 (change关键字不仅仅可以修改字段名称 也可以修改字段类型 以及约束)
mysql> alter table s1 change name name1 char(30); 将字段name修改为name1,并且类型修改为char(30)
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc s1; 重新查看s1表结构,发现name字段名称变为name1,类型也改变为char(30)
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name1 | char(30) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)