第四章 Mysql数据库和表的命令操作
1.数据库(DATABASE)操作:
(1)创建数据库
【例 4.1】查看MySQL服务器中已有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| day0222 |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
8 rows in set (0.00 sec)
【例4.2】创建名称为stusys的学生信息数据库
mysql> create database stusys;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| stusys |
| sys |
+--------------------+
5 rows in set (0.00 sec)
【例4.3】选择stusys为当前数据库
mysql> use stusys;
Database changed
【例4.4】修改数据库stusys的默认字符集和校对规则
mysql> alter database stusys
-> default character set gb2312
-> default collate gb2312_chinese_ci;
Query OK, 1 row affected (0.01 sec)
【例4.5】删除数据库stusys
mysql> drop database stusys;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
【例4.6】在学生信息数据库(stusys)中创建student表
mysql> use stusys;
Database changed
mysql> create table student
-> (
-> sno char(6) not null primary key,
-> sname char(8) not null,
-> ssex char(2) not null default '男',
-> sbrithday date not null,
-> speciality char(12) null,
-> tc tinyint null
-> );
Query OK, 0 rows affected (0.02 sec)
【例4.7】在数据库stusys中,使用复制方式创建student1表,表结构取自student表
mysql> create table student1 like student;
Query OK, 0 rows affected (0.01 sec)
【例4.8】查看数据库stusys中的所有表名
mysql> show tables;
+------------------+
| Tables_in_stusys |
+------------------+
| student |
| student1 |
+------------------+
2 rows in set (0.00 sec)
【例4.9】查看数据库stusys中student表的基本结构
mysql> show columns from student;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| sno | char(6) | NO | PRI | NULL | |
| sname | char(8) | NO | | NULL | |
| ssex | char(2) | NO | | 男 | |
| sbrithday | date | NO | | NULL | |
| speciality | char(12) | YES | | NULL | |
| tc | tinyint | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
【例4.10】查看数据库stusys中student表的详细结构
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`sno` char(6) NOT NULL,
`sname` char(8) NOT NULL,
`ssex` char(2) NOT NULL DEFAULT '男',
`sbrithday` date NOT NULL,
`speciality` char(12) DEFAULT NULL,
`tc` tinyint DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
【例4.11】在数据库stusys的student1表中增加一列sid,添加到表的第1列,不为空,取值唯一并且自动增加
mysql> alter table stusys.student1
-> add column sid int not null unique auto_increment first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#使用DESC语句查看student1表
mysql> desc stusys.student1;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| sid | int | NO | UNI | NULL | auto_increment |
| sno | char(6) | NO | PRI | NULL | |
| sname | char(8) | NO | | NULL | |
| ssex | char(2) | NO | | 男 | |
| sbrithday | date | NO | | NULL | |
| speciality | char(12) | YES | | NULL | |
| tc | tinyint | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
【例4.12】将数据库stusys的student1表的sbirthday列修改为sage,将数据类型改为tinyint,可为空,默认值为18
mysql> alter table stusys.student1
-> change column sbrithday sage tinyint default 18;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stusys.student1;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| sid | int | NO | UNI | NULL | auto_increment |
| sno | char(6) | NO | PRI | NULL | |
| sname | char(8) | NO | | NULL | |
| ssex | char(2) | NO | | 男 | |
| sage | tinyint | YES | | 18 | |
| speciality | char(12) | YES | | NULL | |
| tc | tinyint | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
【例4.13】删除数据库stusys的student1表中的sid列
mysql> alter table stusys.student1
-> drop column sid;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stusys.student1;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| sno | char(6) | NO | PRI | NULL | |
| sname | char(8) | NO | | NULL | |
| ssex | char(2) | NO | | 男 | |
| sage | tinyint | YES | | 18 | |
| speciality | char(12) | YES | | NULL | |
| tc | tinyint | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
【例4.14】在数据库stusys中,将student1表重命名为student2表
mysql> alter table stusys.student1
-> rename to stusys.student2 ;
Query OK, 0 rows affected (0.01 sec)
mysql> use stusys
Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_stusys |
+------------------+
| student |
| student2 |
+------------------+
2 rows in set (0.00 sec)
【例4.15】在数据库stusys中,将student2表重命名为student3表
mysql> rename table stusys.student2 to stusys.student3;
Query OK, 0 rows affected (0.01 sec)
mysql> use stusys;
Database changed
mysql> show tables;
+------------------+
| Tables_in_stusys |
+------------------+
| student |
| student3 |
+------------------+
2 rows in set (0.00 sec)
【例4.16】删除数据库stusys中的student3表
mysql> drop table stusys.student3;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_stusys |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)