注：此仅为数据库原理及应用课程笔记。

术语代指

本文代指列表：

  • UserName：你的数据库管理账号
  • Password：你的数据库管理密码
  • DataBaseName：数据库名称
  • Language：数据库字符集
  • TableName：表名
  • ListName：列名
  • DataType：数据类型
  • Restraint：约束

数据类型

  • CHAR(n),CHARACTER(n)
  • VARCHAR(n),CHARACTERVARYING(n)
  • CLOB
  • BLOB
  • INT,INTEGER
  • SMALLINT
  • BIGINT
  • NUMERIC(p,d)

MySQL实战练习

  1. Mysql登陆
mysql -u UserName -pPassword
  1. 查询当前已有数据库
show databases;

增删改查数据库

  1. 创建数据库
create database DataBaseName;
  1. 查看字符集
show create database DataBaseName;
  1. 修改字符集
alter database DataBaseName default character set Language;

3.1. 创建时指定字符集

create database DataBaseName default character set Language;
  1. 删除数据库
drop database DataBaseName;
  1. 使用数据库
use DataBaseName;

增删改查表

  1. 创建表
create table TableName (ListName DataType Restraint);
  1. 查看表
desc TableName;
  1. 删除表
drop table TableName;
注意：若表存在外键引用关系，则需要先删除外键表，再删除要删除的表。

示例

  • 创建表
mysql> create table SC
    -> (Sno char(8),
    -> Cno char(5),
    -> Grade smallint,
    -> Semester char(5),
    -> Teachingclass char(8),
    -> primary key (Sno,Cno),
    -> foreign key (Sno) references Student (Sno),
    -> foreign key (Cno) references Course (Cno)
    -> );

mysql> create table Course
    -> (Cno char(5) primary key,
    -> Cname varchar(40) not null,
    -> Ccredit smallint,
    -> Cpon char(5),
    -> foreign key (Cpon) references Course (Cno)
    -> );

mysql> create table Student
    -> (Sno char(8) primary key,
    -> Sname varchar(20) unique,
    -> Ssex char(6),
    -> Sbirthdate Date,
    -> Smajor varchar(40)
    -> );
  • 显示
mysql> desc course
    -> ;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Cno     | char(5)     | NO   | PRI | NULL    |       |
| Cname   | varchar(40) | NO   |     | NULL    |       |
| Ccredit | smallint(6) | YES  |     | NULL    |       |
| Cpon    | char(5)     | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc sc;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Sno           | char(8)     | NO   | PRI |         |       |
| Cno           | char(5)     | NO   | PRI |         |       |
| Grade         | smallint(6) | YES  |     | NULL    |       |
| Semester      | char(5)     | YES  |     | NULL    |       |
| Teachingclass | char(8)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Sno        | char(8)     | NO   | PRI | NULL    |       |
| Sname      | varchar(20) | YES  | UNI | NULL    |       |
| Ssex       | char(6)     | YES  |     | NULL    |       |
| Sbirthdate | date        | YES  |     | NULL    |       |
| Smajor     | varchar(40) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)