Mysql学习

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

术语代指

本文代指列表：

• 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

2. 查询当前已有数据库

show databases;

增删改查数据库

1. 创建数据库

create database DataBaseName;

2. 查看字符集

show create database DataBaseName;

3. 修改字符集

alter database DataBaseName default character set Language;

3.1. 创建时指定字符集

create database DataBaseName default character set Language;

4. 删除数据库

drop database DataBaseName;

5. 使用数据库

use DataBaseName;

增删改查表

1. 创建表

create table TableName (ListName DataType Restraint);

2. 查看表

desc TableName;

3. 删除表

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)