mariadb基本操作之表的操作

在example中创建两个表student和grade表，内容如下：
student表

字段名	字符描述	数据类型	主键	外键	非空	唯一	自增
num	学号	INT(10)	是	否	是	是	否
name	姓名	VARCHAR(20)	否	否	是	否	否
sex	性别	VARCHAR(4)	否	否	是	否	否
birthday	出生日期	DATETIME	否	否	否	否	否
address	家庭住址	VARCHAR(50)	否	否	否	否	否

grade表

字段名	字符描述	数据类型	主键	外键	非空	唯一	自增
id	编号	INT(10)	是	否	是	是	是
course	课程名	VARCHAR(10)	否	否	是	否	否
s_num	学号	INT(10)	否	是	是	否	否
grade	成绩	VARCHAR(4)	否	否	否	否	否

需求：
1、将grade表的course字段的数据类型改为VARCHAR(20)
2、将s_num字段的位置更改到course字段前面
3、将grade字段名改为score
4、删除grade表的外键约束
5、将grade表的存储类型改为MyISAM类型
6、将student表中的address字段删除
7、在student表中增加名为phone的字段，数据类型为INT(10)
8、将grade表改名为gradeInfo
9、删除student表

前提：
先建立example数据库，

create database example;

进入example数据库中

use example;

根据表格建立student表和grade表
建立student表：

create table student(num INT(10) PRIMARY KEY NOT NULL UNIQUE,name VARCHAR(20) NOT NULL,sex VARCHAR(4) NOT NULL,birthday DATETIME,address VARCHAR(50));

创建grade表

create table grade(id INT(10) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,course VARCHAR(10) NOT NULL,s_num INT(10) NOT NULL,grade VARCHAR(4),CONSTRAINT grade_fk FOREIGN KEY (s_num) REFERENCES student(num));

查看表

MariaDB [example]> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| grade             |
| student           |
+-------------------+

MariaDB [example]> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `num` int(10) NOT NULL,
  `name` varchar(20) COLLATE utf8_bin NOT NULL,
  `sex` varchar(4) COLLATE utf8_bin NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `address` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`num`),
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [example]> show create table grade \G;
*************************** 1. row ***************************
       Table: grade
Create Table: CREATE TABLE `grade` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `course` varchar(10) COLLATE utf8_bin NOT NULL,
  `s_num` int(10) NOT NULL,
  `grade` varchar(4) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `grade_fk` (`s_num`),
  CONSTRAINT `grade_fk` FOREIGN KEY (`s_num`) REFERENCES `student` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1、将grade表的course字段的数据类型改为VARCHAR(20)

MariaDB [example]> alter table grade modify course VARCHAR(20);
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [example]> desc grade;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(10)     | NO   | PRI | NULL    | auto_increment |
| course | varchar(20) | YES  |     | NULL    |                |
| s_num  | int(10)     | NO   | MUL | NULL    |                |
| grade  | varchar(4)  | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

2、将s_num字段的位置更改到course字段前面

alter table grade modify course varchar(20) NOT NULL AFTER s_num;
MariaDB [example]> desc grade;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(10)     | NO   | PRI | NULL    | auto_increment |
| s_num  | int(10)     | NO   | MUL | NULL    |                |
| course | varchar(20) | YES   |     | NULL    |                |
| grade  | varchar(4)  | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

3、将grade字段名改为score

alter table grade change grade score varchar(4) ;
MariaDB [example]> desc grade;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(10)     | NO   | PRI | NULL    | auto_increment |
| s_num  | int(10)     | NO   | MUL | NULL    |                |
| course | varchar(20) |  YES   |     | NULL    |                |
| score  | varchar(4)  |  YES   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

4、删除grade表的外键约束

alter table grade drop  FOREIGN KEY `grade_fk`;
MariaDB [example]> show create table grade \G;
*************************** 1. row ***************************
       Table: grade
Create Table: CREATE TABLE `grade` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `s_num` int(10) NOT NULL,
  `course` varchar(20) COLLATE utf8_bin NOT NULL,
  `score` varchar(4) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `grade_fk` (`s_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

5、将grade表的存储类型改为MyISAM类型

MariaDB [example]> alter table grade engine=MyISAM;
MariaDB [example]> show create table grade \G;
*************************** 1. row ***************************
       Table: grade
Create Table: CREATE TABLE `grade` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `s_num` int(10) NOT NULL,
  `course` varchar(20) COLLATE utf8_bin NOT NULL,
  `score` varchar(4) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `grade_fk` (`s_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

6、将student表中的address字段删除

MariaDB [example]> alter table student drop address;
Query OK, 0 rows affected (0.06 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [example]> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| num      | int(10)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   |     | NULL    |       |
| sex      | varchar(4)  | NO   |     | NULL    |       |
| birthday | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

7、在student表中增加名为phone的字段，数据类型为INT(10)

MariaDB [example]> alter table student add phone INT(10);
Query OK, 0 rows affected (0.05 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [example]> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| num      | int(10)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   |     | NULL    |       |
| sex      | varchar(4)  | NO   |     | NULL    |       |
| birthday | datetime    | YES  |     | NULL    |       |
| phone    | int(10)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

8、将grade表改名为gradeInfo

alter table grade rename gradeInfo;
MariaDB [example]> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| gradeInfo         |
| student           |
+-------------------+

9、删除student表

drop table student;
Query OK, 0 rows affected (0.02 sec)
MariaDB [example]> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| gradeInfo         |
+-------------------+