一、数据库的操作
创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
mysql> create database if not exists test charset = utf8;
Query OK, 1 row affected, 1 warning ( 0.00 sec)
查看所有的数据库
mysql> show databases ;
+
| Database |
+
| information_schema |
| test |
+
2 rows in set ( 0.01 sec)
切换当前数据库
mysql> use test;
Database changed
查看当前选中的数据库
mysql> select database ( ) ;
+
| database ( ) |
+
| test |
+
1 row in set ( 0.00 sec)
查看数据库创建信息
mysql> show create database test;
+
| Database | Create Database |
+
| test | CREATE DATABASE ` test` |
+
1 row in set ( 0.00 sec)
删除数据库
mysql> drop database if exists test;
Query OK, 7 rows affected ( 0.14 sec)
mysql> show databases ;
+
| Database |
+
| information_schema |
+
1 row in set ( 0.00 sec)
二、表的操作
表的创建、查看、删除
mysql> select database ( ) ;
+
| database ( ) |
+
| test |
+
1 row in set ( 0.00 sec)
mysql> show tables ;
Empty set ( 0.00 sec)
mysql> CREATE TABLE student_info (
- > number INT PRIMARY KEY ,
- > name VARCHAR ( 5 ) ,
- > sex ENUM ( '男' , '女' ) ,
- > id_number CHAR ( 18 ) UNIQUE ,
- > department VARCHAR ( 30 ) ,
- > major VARCHAR ( 30 ) ,
- > enrollment_time DATE
- > ) ;
Query OK, 0 rows affected ( 3.15 sec)
mysql> CREATE TABLE student_info (
- > number INT ,
- > name VARCHAR ( 5 ) ,
- > sex ENUM ( '男' , '女' ) ,
- > id_number CHAR ( 18 ) ,
- > department VARCHAR ( 30 ) ,
- > major VARCHAR ( 30 ) ,
- > enrollment_time DATE ,
- > PRIMARY KEY ( number) ,
- > UNIQUE KEY uk_id_number ( id_number)
- > ) ;
Query OK, 0 rows affected ( 2.41 sec)
mysql> show tables ;
+
| Tables_in_test |
+
| student_info |
+
1 row in set ( 0.00 sec)
mysql> drop table if exists student_info;
Query OK, 0 rows affected ( 0.01 sec)
mysql> show tables ;
Empty set ( 0.00 sec)
删除多个表
DROP TABLE 表1 , 表2 , . . . , 表n;
查看表结构
mysql> desc student_info;
+
| Field | Type | Null | Key | Default | Extra |
+
| number | int ( 11 ) | YES | | NULL | |
| name | varchar ( 5 ) | YES | | NULL | |
| sex | enum ( '男' , '女' ) | YES | | NULL | |
| id_number | char ( 18 ) | YES | | NULL | |
| department | varchar ( 30 ) | YES | | NULL | |
| major | varchar ( 30 ) | YES | | NULL | |
| enrollment_time | date | YES | | NULL | |
+
7 rows in set ( 0.00 sec)
mysql> show create table student_info\G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : student_info
Create Table : CREATE TABLE ` student_info` (
` number` int ( 11 ) DEFAULT NULL ,
` name` varchar ( 5 ) DEFAULT NULL ,
` sex` enum ( '男' , '女' ) DEFAULT NULL ,
` id_number` char ( 18 ) DEFAULT NULL ,
` department` varchar ( 30 ) DEFAULT NULL ,
` major` varchar ( 30 ) DEFAULT NULL ,
` enrollment_time` date DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = '学生基本信息表'
1 row in set ( 0.00 sec)
修改表名
mysql> ALTER TABLE student_info RENAME TO student_info_new;
Query OK, 0 rows affected ( 3.24 sec)
mysql> show tables ;
+
| Tables_in_test |
+
| student_info_new |
+
1 row in set ( 0.00 sec)
mysql> RENAME TABLE student_info_new to student_info;
Query OK, 0 rows affected ( 0.01 sec)
mysql> show tables ;
+
| Tables_in_test |
+
| student_info |
+
1 row in set ( 0.00 sec)
移动表到其他数据库
mysql> show tables from test2;
Empty set ( 0.00 sec)
mysql> ALTER TABLE student_info RENAME TO test2. student_info2;
Query OK, 0 rows affected ( 0.91 sec)
mysql> show tables from test2;
+
| Tables_in_test2 |
+
| student_info2 |
+
1 row in set ( 0.00 sec)
mysql> show tables from test;
Empty set ( 0.00 sec)
增加列
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [ 列的属性] ;
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [ 列的属性] FIRST ;
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [ 列的属性] AFTER 指定列名;
mysql> desc student_info;
+
| Field | Type | Null | Key | Default | Extra |
+
| number | int ( 11 ) | YES | | NULL | |
| name | varchar ( 5 ) | YES | | NULL | |
| sex | enum ( '男' , '女' ) | YES | | NULL | |
| id_number | char ( 18 ) | YES | | NULL | |
| department | varchar ( 30 ) | YES | | NULL | |
| major | varchar ( 30 ) | YES | | NULL | |
| enrollment_time | date | YES | | NULL | |
+
7 rows in set ( 0.01 sec)
mysql> ALTER TABLE student_info ADD COLUMN new_col INT NOT NULL ;
Query OK, 0 rows affected ( 0.05 sec)
Records: 0 Duplicates: 0 Warnings : 0
mysql> desc student_info;
+
| Field | Type | Null | Key | Default | Extra |
+
| number | int ( 11 ) | YES | | NULL | |
| name | varchar ( 5 ) | YES | | NULL | |
| sex | enum ( '男' , '女' ) | YES | | NULL | |
| id_number | char ( 18 ) | YES | | NULL | |
| department | varchar ( 30 ) | YES | | NULL | |
| major | varchar ( 30 ) | YES | | NULL | |
| enrollment_time | date | YES | | NULL | |
| new_col | int ( 11 ) | NO | | NULL | |
+
删除列
mysql> ALTER TABLE student_info DROP COLUMN new_col;
Query OK, 0 rows affected ( 3.29 sec)
Records: 0 Duplicates: 0 Warnings : 0
修改列信息
ALTER TABLE 表名 MODIFY 列名 新数据类型 [ 新属性] ;
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [ 新属性] ;
插入数据
INSERT INTO 表名( 列1 , 列2 , . . . ) VALUES ( 列1 的值,列2 的值, . . . ) ;
INSERT INTO 表名( 列1 , 列2 , . . . ) VAULES( 列1 的值,列2 的值, . . . ) , ( 列1 的值,列2 的值, . . . ) , ( 列1 的值,列2 的值, . . . ) , . . . ;
外键
CONSTRAINT [ 外键名称] FOREIGN KEY ( 列1 , 列2 , . . . ) REFERENCES 父表名( 父列1 , 父列2 , . . . ) ;
mysql> CREATE TABLE student_score (
- > number INT ,
- > subject VARCHAR ( 30 ) ,
- > score TINYINT ,
- > PRIMARY KEY ( number, subject) ,
- > CONSTRAINT FOREIGN KEY ( number) REFERENCES student_info( number)
- > ) ;
Query OK, 0 rows affected ( 0.03 sec)
mysql> show create table student_score\G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : student_score
Create Table : CREATE TABLE ` student_score` (
` number` int ( 11 ) NOT NULL ,
` subject` varchar ( 30 ) NOT NULL ,
` score` tinyint ( 4 ) DEFAULT NULL ,
PRIMARY KEY ( ` number` , ` subject` ) ,
CONSTRAINT ` student_score_ibfk_1` FOREIGN KEY ( ` number` ) REFERENCES ` student_info` ( ` number` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8
1 row in set ( 0.00 sec)
三、查询语句(待补充)