首页

数据库基本操作

— 数据库原理及应用实验 —

Haifeng Xu

(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

References: 刘增杰、张少军 《MySQL 5.5 从零开始学》

目录

登录数据库

登录数据库

C:\>mysql -uroot -p
Enter password: 123456

登录成功后, 显示如下:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 576
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

查看 MySQL 的版本.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.17    |
+-----------+
1 row in set (0.04 sec)

使用 quit 或 exit 退出 mysql, 或者 \q.

mysql> quit
Bye

MySQL Shell

MySQL Shell 是 MySQL 新一代的高级客户端和代码编辑器，是 Oracle 公司提供的一个交互式命令行工具。对比自带的客户端工具 mysql ，MySQL Shell 不仅可以通过它执行传统的 SQL 语句，还可以使用包括 Python 和 JavaScript 在内的编程语言与服务器进行交互，为用户提供更多的选择和灵活性，而且为 MySQL 的不同产品（如 MySQL Server，MySQL Router，MySQL Innodb Cluster等）提供了一个统一接口。与此同时 MySQL Shell 还集成了很多功能，例如数据库查询和更新，数据库管理，集群管理，插件支持，备份恢复等。 MySQL Shell 8.0 可与 MySQL Server 8.0 和 5.7 一起使用。

首先使用 mysqlsh 命令进入客户端.

C:\Users\haife>mysqlsh
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS >

使用 \connect 或 \c 连接数据库.

 MySQL  JS > \connect root@localhost:3306

根据提示输入root 用户的密码, 比如 123456. 以及是否保存密码. 如果选择保存密码, 则下次输入上面的命令就可以直接连到数据库了. 如下:

 MySQL  JS > \connect root@localhost:3306
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 11
Server version: 5.7.23 MySQL Community Server (GPL)
No default schema selected; type \use  to set one.

使用 \sql 命令切换到 SQL 模式.

 MySQL  localhost:3306  JS > \sql
Switching to SQL mode... Commands end with ;

然后就可以查询数据库了.

也可以直接登录数据库.

C:\Users\haife>mysqlsh -uroot -p -hlocalhost -P3306
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 15
Server version: 5.7.23 MySQL Community Server (GPL)
No default schema selected; type \use  to set one.
 MySQL  localhost:3306  JS >
 

查看数据库

查看数据库

重新登录后, 使用下面的命令看一下已经存在有多少数据库.

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)

创建数据库

创建数据库

mysql> CREATE DATABASE test_bar;

确认是否已创建了test_bar 数据库.

mysql> SHOW DATABASES;

也可以使用下面的命令, 尝试使用 \G 参数.

mysql> SHOW CREATE DATABASE test_bar\G;

删除刚才的数据库

mysql> DROP DATABASE test_bar;

使用 DROP DATABASE 时要非常小心, 因为数据库中的所有表及数据将一同被删除, 而 MySQL 不会给出任何确认删除的提示.

为了继续实验, 我们重新创建一下数据库 test_bar.

mysql> CREATE DATABASE test_bar;

mysql> use test_bar;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test_bar   |
+------------+

上面的 select database(); 返回当前使用的数据库.

MySQL Shell

使用 \use 切换到某个数据库.

 MySQL  localhost:3306  SQL > \use chinamath
Default schema set to `chinamath`.
Fetching table and column names from `chinamath` for auto-completion... Press ^C to stop.

 MySQL  localhost:3306  chinamath  SQL > show tables;
+---------------------+
| Tables_in_chinamath |
+---------------------+
| students            |
+---------------------+
1 row in set (0.0015 sec)

数据库的备份与还原

数据库的备份与还原

mysqldump -uroot -p dbName > dbName-2014-5-10.sql

mysql -uroot -p < dbName-2014-5-10.sql

中文乱码的问题

中文乱码的问题

在创建数据库时指定编码, 如果是在 Windows 下, 我们使用 GBK 编码.

mysql> CREATE DATABASE test_bar
    -> CHARACTER SET gbk
    -> COLLATE gbk_chinese_ci;

mysql> USE test_bar;
mysql> SET NAMES 'gbk';

如果进入数据库发现数据有乱码, 首先查看该数据库或表的具体创建信息. 使用下面的命令

SHOW CREATE DATABASE test_bar;
SHOW CREATE TABLE Sells\G;

SET NAMES 'gbk';

查看错误信息

查看错误信息

如果刚才输入 select database(); 时误敲为 select databases(); 或者 select database; 等等, 则会返回错误信息.

要查看错误信息, 可以使用下面的语句.

mysql> SHOW WARNINGS;

取消命令的执行

取消命令的执行

如果当输入 select database(); 时误敲为 select databases() 或者 slect database() 等等, 需要取消命令的执行,

可以使用 \c.

mysql> slect
    -> database()
    -> \c
mysql>

MySQL 的提示符 mysql>

MySQL 的提示符 mysql>

prompt	meaning
mysql>	Ready for new command.
->	Waiting for next line of multiple-line command.
'>	Waiting for next line, waiting for completion of a string that began with a single quote (‘'’).
">	Waiting for next line, waiting for completion of a string that began with a double quote (‘"’).
`>	Waiting for next line, waiting for completion of an identifier that began with a backtick (‘`’).
/*>	Waiting for next line, waiting for completion of a comment that began with /*.

MySQL 的命令历史记录

MySQL 的命令历史记录

在 Unix 和 Linux 系统上, mysql 将执行的语句记录到一个文件中. 默认的, 这个历史文件被命名为 .mysql_history, 位于用户目录中.

在 Windows 下, 如果在MySQL的交互模式下需要记录所执行的命令. 则在启动 MySQL 时使用 --tee filename 这个选项.

实验

假设我们要将 mysql 的历史命令记录存储到 D:\tmp\mysql_history.txt 文件中. 首先切换到目录 D:\tmp, 然后执行下面的命令.

mysql -uroot -p --tee mysql_history.txt

test_bar 数据库的模式

test_bar 数据库的模式

-- Beers(name, manf)
-- Bars(name, addr, license)
-- Drinkers(name, addr, phone)
-- Likes(drinker, beer)
-- Sells(bar, beer, price)
-- Frequents(drinker, bar)

CREATE TABLE Beers (
	name	VARCHAR(20) UNIQUE,
	manf	VARCHAR(50)
);

CREATE TABLE Bars (
	name	VARCHAR(20) PRIMARY KEY,
	addr	VARCHAR(255),
	license	VARCHAR(255)
);

CREATE TABLE Drinkers (
	name	VARCHAR(20) PRIMARY KEY,
	addr	VARCHAR(255),
	phone	VARCHAR(15)
);

CREATE TABLE Likes (
	drinker	VARCHAR(20),
	beer	VARCHAR(255),
	PRIMARY KEY (drinker,beer)
);

CREATE TABLE Sells (
	bar	VARCHAR(20),
	beer	VARCHAR(20),
	price	REAL,
	PRIMARY KEY (bar, beer)
);

CREATE TABLE Frequents (
	drinker	CHAR(20),
	bar	VARCHAR(255),
	PRIMARY KEY (drinker,bar)
);

创建数据表

创建数据表

mysql> USE test_bar;

mysql> CREATE TABLE Beers (
    -> name	VARCHAR(20) UNIQUE,
    -> manf	VARCHAR(50)
    -> );

mysql> CREATE TABLE Bars (
    -> name	VARCHAR(20) PRIMARY KEY,
    -> addr	VARCHAR(255),
    -> license	VARCHAR(255)
    -> );

mysql> CREATE TABLE Drinkers (
    -> name	VARCHAR(20) PRIMARY KEY,
    -> addr	VARCHAR(255),
    -> phone	VARCHAR(15)
    -> );

mysql> CREATE TABLE Likes (
    -> drinker	VARCHAR(20),
    -> beer	VARCHAR(255),
    -> PRIMARY KEY (drinker,beer)
    -> );

mysql> CREATE TABLE Sells (
    -> bar	VARCHAR(20),
    -> beer	VARCHAR(20),
    -> price	REAL,
    -> PRIMARY KEY (bar, beer)
    -> );

mysql> CREATE TABLE Frequents (
    -> drinker	CHAR(20),
    -> bar	VARCHAR(255),
    -> PRIMARY KEY (drinker,bar)
    -> );

mysql> SHOW TABLES;
+--------------------+
| Tables_in_test_bar |
+--------------------+
| Bars               |
| Beers              |
| Drinkers           |
| Frequents          |
| Likes              |
| Sells              |
+--------------------+
6 rows in set (0.00 sec)

用文件的方式导入数据库

用文件的方式导入数据库

可以将上面的若干 CREATE TABLE 等语句存到一个文件(scheme.sql)中, 比如保存到 D:\tmp\schema.sql. (这里我们假设大家使用的是 Windows 系统. 如果使用的是 Linux 系统, 则如 ~/tmp/schema.sql.)

mysql> source D:/tmp/schema.sql;

注意这里不能写成 source D:\tmp\schema.sql; 因为 Windows 下 \ 表示转义. 必须是 source D:\\tmp\\schema.sql; 这里的 ; 可以省略.

插入数据

插入数据

mysql> INSERT INTO Beers (name, manf)
    -> VALUES ('百威', '百威英博啤酒集团');

不妨查看一下

mysql> SELECT * FROM Beers;
+--------+--------------------------+
| name   | manf                     |
+--------+--------------------------+
| 百威   | 百威英博啤酒集团         |
+--------+--------------------------+
1 row in set (0.00 sec)

将下面的内容保存到文件: D:\tmp\insert_data.sql,

INSERT INTO Beers (name, manf) VALUES ('贝克', '百威英博啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('喜力', '喜利得(中国)有限公司');
INSERT INTO Beers (name, manf) VALUES ('嘉士伯', '嘉士伯啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('安贝夫', '英博啤酒集团下巴西安贝夫公司');
INSERT INTO Beers (name, manf) VALUES ('南非啤酒', '南非啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('米勒', 'SAB 公司');
INSERT INTO Beers (name, manf) VALUES ('科罗娜', '墨西哥摩洛哥啤酒公司');
INSERT INTO Beers (name, manf) VALUES ('Stella Artois', '百威英博啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('朝日', '朝日啤酒株式会社');
INSERT INTO Beers (name, manf) VALUES ('麒麟', '麒麟(中国)投资有限公司');
INSERT INTO Beers (name, manf) VALUES ('生力', '香港生力啤酒廠有限公司');
INSERT INTO Beers (name, manf) VALUES ('健力士黑啤', '亚瑟健力士父子有限公司');
INSERT INTO Beers (name, manf) VALUES ('纯种苦啤酒', '马斯顿-汤普生-埃弗谢德公司');

INSERT INTO Bars (name, addr, license) VALUES ('3DArtBar', '上海虹梅路3338弄9－11号', 'BHXGZX');
INSERT INTO Bars (name, addr, license) VALUES ('HardRock', '上海政通路315号', 'XJ8QGF');
INSERT INTO Bars (name, addr, license) VALUES ('Westside', '上海衡山路237号', 'EDS9HF');
INSERT INTO Bars (name, addr, license) VALUES ('木板房啤酒吧', '上海子长路350号', 'YJVI9A');
INSERT INTO Bars (name, addr, license) VALUES ('宝莱纳餐厅', '上海徐汇区汾阳路150号', 'SKAPXJ');
INSERT INTO Bars (name, addr, license) VALUES ('苏荷酒吧', '扬州1912', 'LYMTD5');
INSERT INTO Bars (name, addr, license) VALUES ('扬州老啤酒厂酒吧', '广陵区南通东路128号', '8NZBSG');

mysql> source D:/tmp/insert_data.sql;

mysql> SELECT * FROM Beers;
+-----------------+--------------------------------------------+
| name            | manf                                       |
+-----------------+--------------------------------------------+
| 百威            | 百威英博啤酒集团                           |
| 贝克            | 百威英博啤酒集团                           |
| 喜力            | 喜利得(中国)有限公司                       |
| 嘉士伯          | 嘉士伯啤酒集团                             |
| 安贝夫          | 英博啤酒集团下巴西安贝夫公司               |
| 南非啤酒        | 南非啤酒集团                               |
| 米勒            | SAB 公司                                   |
| 科罗娜          | 墨西哥摩洛哥啤酒公司                       |
| Stella Artois   | 百威英博啤酒集团                           |
| 朝日            | 朝日啤酒株式会社                           |
| 麒麟            | 麒麟(中国)投资有限公司                     |
| 生力            | 香港生力啤酒廠有限公司                     |
| 健力士黑啤      | 亚瑟健力士父子有限公司                     |
| 纯种苦啤酒      | 马斯顿-汤普生-埃弗谢德公司                 |
+-----------------+--------------------------------------------+
14 rows in set (0.00 sec)

mysql> select * from Bars;
+--------------------------+---------------------------------+---------+
| name                     | addr                            | license |
+--------------------------+---------------------------------+---------+
| 3DArtBar                 | 上海虹梅路3338弄9－11号         | BHXGZX  |
| HardRock                 | 上海政通路315号                 | XJ8QGF  |
| Westside                 | 上海衡山路237号                 | EDS9HF  |
| 宝莱纳餐厅               | 上海徐汇区汾阳路150号           | SKAPXJ  |
| 扬州老啤酒厂酒吧         | 广陵区南通东路128号             | 8NZBSG  |
| 木板房啤酒吧             | 上海子长路350号                 | YJVI9A  |
| 苏荷酒吧                 | 扬州1912                        | LYMTD5  |
+--------------------------+---------------------------------+---------+
7 rows in set (0.00 sec)

insert_data.sql

insert_data.sql

下载数据文件 insert_data.sql

INSERT INTO Beers (name, manf) VALUES ('百威', '百威英博啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('贝克', '百威英博啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('喜力', '喜利得(中国)有限公司');
INSERT INTO Beers (name, manf) VALUES ('嘉士伯', '嘉士伯啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('安贝夫', '英博啤酒集团下巴西安贝夫公司');
INSERT INTO Beers (name, manf) VALUES ('南非啤酒', '南非啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('米勒', 'SAB 公司');
INSERT INTO Beers (name, manf) VALUES ('科罗娜', '墨西哥摩洛哥啤酒公司');
INSERT INTO Beers (name, manf) VALUES ('Stella Artois', '百威英博啤酒集团');
INSERT INTO Beers (name, manf) VALUES ('朝日', '朝日啤酒株式会社');
INSERT INTO Beers (name, manf) VALUES ('麒麟', '麒麟(中国)投资有限公司');
INSERT INTO Beers (name, manf) VALUES ('生力', '香港生力啤酒廠有限公司');
INSERT INTO Beers (name, manf) VALUES ('健力士黑啤', '亚瑟健力士父子有限公司');
INSERT INTO Beers (name, manf) VALUES ('纯种苦啤酒', '马斯顿-汤普生-埃弗谢德公司');


INSERT INTO Bars (name, addr, license) VALUES ('3DArtBar', '上海虹梅路3338弄9－11号', 'BHXGZX');
INSERT INTO Bars (name, addr, license) VALUES ('HardRock', '上海政通路315号', 'XJ8QGF');
INSERT INTO Bars (name, addr, license) VALUES ('Westside', '上海衡山路237号', 'EDS9HF');
INSERT INTO Bars (name, addr, license) VALUES ('木板房啤酒吧', '上海子长路350号', 'YJVI9A');
INSERT INTO Bars (name, addr, license) VALUES ('宝莱纳餐厅', '上海徐汇区汾阳路150号', 'SKAPXJ');
INSERT INTO Bars (name, addr, license) VALUES ('苏荷酒吧', '扬州1912', 'LYMTD5');
INSERT INTO Bars (name, addr, license) VALUES ('扬州老啤酒厂酒吧', '广陵区南通东路128号', '8NZBSG');


INSERT INTO Sells (bar, beer, price) VALUES ('3DArtBar', '百威', 30);
INSERT INTO Sells (bar, beer, price) VALUES ('3DArtBar', '贝克', 35);
INSERT INTO Sells (bar, beer, price) VALUES ('3DArtBar', '喜力', 25);
INSERT INTO Sells (bar, beer, price) VALUES ('3DArtBar', '嘉士伯', 32);
INSERT INTO Sells (bar, beer, price) VALUES ('3DArtBar', '安贝夫', 40);

INSERT INTO Sells (bar, beer, price) VALUES ('HardRock', '百威', 32);
INSERT INTO Sells (bar, beer, price) VALUES ('HardRock', '贝克', 33);
INSERT INTO Sells (bar, beer, price) VALUES ('HardRock', '喜力', 28);
INSERT INTO Sells (bar, beer, price) VALUES ('HardRock', '嘉士伯', 30);
INSERT INTO Sells (bar, beer, price) VALUES ('HardRock', '安贝夫', 39);
INSERT INTO Sells (bar, beer, price) VALUES ('HardRock', '科罗娜', 40);
INSERT INTO Sells (bar, beer, price) VALUES ('HardRock', '健力士黑啤', 39);

INSERT INTO Sells (bar, beer, price) VALUES ('Westside', '百威', 32);
INSERT INTO Sells (bar, beer, price) VALUES ('Westside', '纯种苦啤酒', 33);
INSERT INTO Sells (bar, beer, price) VALUES ('Westside', '贝克', 28);
INSERT INTO Sells (bar, beer, price) VALUES ('Westside', '南非啤酒', 36);
INSERT INTO Sells (bar, beer, price) VALUES ('Westside', '朝日', 26);
INSERT INTO Sells (bar, beer, price) VALUES ('Westside', '科罗娜', 40);
INSERT INTO Sells (bar, beer, price) VALUES ('Westside', '健力士黑啤', 39);

INSERT INTO Sells (bar, beer, price) VALUES ('木板房啤酒吧', '麒麟', 30);
INSERT INTO Sells (bar, beer, price) VALUES ('木板房啤酒吧', '贝克', 35);
INSERT INTO Sells (bar, beer, price) VALUES ('木板房啤酒吧', '生力', 25);
INSERT INTO Sells (bar, beer, price) VALUES ('木板房啤酒吧', '米勒', 32);
INSERT INTO Sells (bar, beer, price) VALUES ('木板房啤酒吧', '嘉士伯', 43);
INSERT INTO Sells (bar, beer, price) VALUES ('木板房啤酒吧', '安贝夫', 40);

INSERT INTO Sells (bar, beer, price) VALUES ('宝莱纳餐厅', '百威', 32);
INSERT INTO Sells (bar, beer, price) VALUES ('宝莱纳餐厅', '纯种苦啤酒', 33);
INSERT INTO Sells (bar, beer, price) VALUES ('宝莱纳餐厅', '喜力', 28);
INSERT INTO Sells (bar, beer, price) VALUES ('宝莱纳餐厅', '南非啤酒', 36);
INSERT INTO Sells (bar, beer, price) VALUES ('宝莱纳餐厅', '朝日', 26);
INSERT INTO Sells (bar, beer, price) VALUES ('宝莱纳餐厅', '科罗娜', 40);
INSERT INTO Sells (bar, beer, price) VALUES ('宝莱纳餐厅', '健力士黑啤', 39);

INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '百威', 32);
INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '纯种苦啤酒', 33);
INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '喜力', 28);
INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '南非啤酒', 36);
INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '朝日', 26);
INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '科罗娜', 40);
INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '健力士黑啤', 39);
INSERT INTO Sells (bar, beer, price) VALUES ('苏荷酒吧', '生力', 38);

INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '百威', 32);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '纯种苦啤酒', 33);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '喜力', 35);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', 'Stella Artois', 36);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '健力士黑啤', 26);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '科罗娜', 40);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '米勒', 50);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '安贝夫', 40);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '麒麟', 39);
INSERT INTO Sells (bar, beer, price) VALUES ('扬州老啤酒厂酒吧', '贝克', 42);

导入数据的另一种办法(LOAD DATA)

导入数据的另一种办法(LOAD DATA)

首先清空某个数据表, 比如 Bars. 注意这仅删除其中的数据. 表的结构还在.

TRUNCATE TABLE Bars;

将各个表的内容分别存储在文本文件中, 为方便, 下载 load_data.zip, 解压缩后有六个文件(它们是: Beers.txt, Bars.txt, Sells.txt, Drinkers.txt, Frequents.txt, Likes.txt) 其中文件中的每一行代表一个记录, 次序应与表中属性的次序一致. 注意: 这些文件的编码已经是 GBK 的了.

将它们保存在 D:\tmp 中. 然后执行下面的语句.

mysql> LOAD DATA LOCAL INFILE 'D:/tmp/Bars.txt'
    -> INTO TABLE Bars
    -> CHARACTER SET GBK
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES STARTING BY '[' TERMINATED BY ']'
    -> ;

• 注意如果需要 utf-8 编码的文件, 则下载 load_data_utf8.zip.
• 且将上面的 CHARACTER SET GBK 换成 CHARACTER SET UTF8

现在检查一下导入有没有成功, 其余类似.

• TERMINATED BY ',' 表示每个字段用逗号隔开. 如果没有该语句, 则默认用制表符 \t 隔开.
• ENCLOSED BY '"' 表示内容包含在双引号内
• ESCAPED BY '\\' 表示转义字符以 \开始.

---

如下面使用默认分隔符.

mysql> LOAD DATA LOCAL INFILE 'D:/tmp/Beers.txt'
    -> INTO TABLE Beers;

注意: 如果是在 Windows 中编辑的文本, 则要加上 LINES TERMINATED BY '\r\n', 表示每行数据用 Windows 的回车换行符分开.

mysql> LOAD DATA LOCAL INFILE 'D:/tmp/Beers.txt'
    -> INTO TABLE Beers
    -> LINES TERMINATED BY '\r\n';

• 注意: 如没有特别说明, 则这里提供的文件均是在 Linux 系统下编辑的, 采用 UTF-8 编码. 在 Windows 下使用时可能要转换编码.

Other data

其他数据也可以自行输入

这里提供了其他数据, 如 Drinkers, Likes, Frequents, 请使用 LOAD DATA命令导入. 或者不妨使用自己和周围同学的信息, 自行插入数据. 例如:

mysql> INSERT INTO Drinkers (name, addr, phone) 
    -> VALUES ('Mike', '上海市徐汇区', '15912345678');

mysql> INSERT INTO Likes (drinker, beer) 
    -> VALUES ('Mike', '贝克');

mysql> INSERT INTO Frequents (drinker, bar) 
    -> VALUES ('Mike', 'Westside');

练习 lecture_3.html 中的内容

练习 lecture_3.html 中的内容

如: 对于关系 Likes(drinker, beer) 和 Frequents(drinker, bar), 列出经常光顾 Westside酒吧的顾客所喜欢的啤酒.

mysql> SELECT beer
    -> FROM Likes, Frequents
    -> WHERE Frequents.drinker=Likes.drinker
    -> AND Frequents.bar='Westside';

查看数据表的结构

查看数据表的结构

mysql> describe Beers;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  | UNI | NULL    |       |
| manf  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

其中各字段的含义分别为:

• NULL: 表示该列是否可以存储 NULL 值.
• Key: 表示该列是否已编制索引.
      PRI 表示该属性是表主键的一部分;
      UNI 表示该属性是 UNIQUE 索引的一部分.
      MUL 表示在列中某个给定值允许出现多次.
• Default: 表示该列是否有默认值, 如果有是什么.
• Extra: 该列的附加信息, 如 AUTO_INCREMENT 等.

或者使用 DESC Beers;

mysql> desc Beers;

mysql> desc Beers\G;
*************************** 1. row ***************************
  Field: name
   Type: varchar(20)
   Null: YES
    Key: UNI
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: manf
   Type: varchar(50)
   Null: YES
    Key: 
Default: NULL
  Extra: 
2 rows in set (0.01 sec)

使用 SHOW CREATE TABLE Beers 查看表 Beers 的详细信息.

mysql> show create table Beers\G;
*************************** 1. row ***************************
       Table: Beers
Create Table: CREATE TABLE `Beers` (
  `name` varchar(20) DEFAULT NULL,
  `manf` varchar(50) DEFAULT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改数据表

修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

mysql> ALTER TABLE Beers RENAME TO Beer;

mysql> SHOW TABLES;
+--------------------+
| Tables_in_test_bar |
+--------------------+
| Bars               |
| Beer               |
| Drinkers           |
| Frequents          |
| Likes              |
| Sells              |
+--------------------+

mysql> ALTER TABLE Beer RENAME Beers;

修改数据表

修改属性(字段)的数据类型

ALTER TABLE <表名> MODIFY <属性名> <数据类型>;

mysql> alter table Beers modify name char(20);
Query OK, 14 rows affected (0.66 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc Beers;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | char(20)    | YES  | UNI | NULL    |       |
| manf  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

修改属性(字段)的默认值

ALTER TABLE <表名> MODIFY COLUMN <属性名> <数据类型> NOT NULL DEFAULT 'default_value';

或者

ALTER TABLE <表名> CHANGE COLUMN <属性名> <属性名> <数据类型> NOT NULL DEFAULT 'default_value';

修改数据表

修改属性名(字段名)

ALTER TABLE <表名> CHANGE <旧属性名> <新属性名> <新数据类型>;

mysql> alter table Beers change name beer char(20);
Query OK, 14 rows affected (0.23 sec)
Records: 14  Duplicates: 0  Warnings: 0

如果仅仅需要更改属性名, 数据类型也要加上, 不能为空. 即跟原来一致即可.

mysql> alter table Beers change beer name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

• CHANGE 也可以实现 MODIFY 的功能, 即仅仅修改数据类型. 这时只要将新属性名写成与旧属性名一样即可.
• 当数据表中已经有数据时, 不要轻易修改数据类型.

添加属性(字段)

添加属性(字段)

ALTER TABLE <表名> ADD <新属性名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];

• FIRST: 将新增属性放在表中第一列.
• AFTER 已存在字段名: 将新增属性放在表中指定字段的后面.
• 如果没有上面的选项, 则默认将新增属性放在表的最后一列.

---

添加无完整性约束条件的字段.

mysql> alter table Beers add Country varchar(20);
Query OK, 14 rows affected (0.23 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> alter table Beers add name_en varchar(20) after name;
Query OK, 14 rows affected (0.24 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc Beers;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  | UNI | NULL    |       |
| name_en | varchar(20) | YES  |     | NULL    |       |
| manf    | varchar(50) | YES  |     | NULL    |       |
| Country | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

---

添加有完整性约束条件的字段.

mysql> alter table Beers add column1 varchar(12) not null;
Query OK, 14 rows affected (0.30 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc Beers;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  | UNI | NULL    |       |
| name_en | varchar(20) | YES  |     | NULL    |       |
| manf    | varchar(50) | YES  |     | NULL    |       |
| Country | varchar(20) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

注意不能将 column1 改为 column, 因为 column 是 MySQL 中的保留字. 如果一定要创建名为 column 的属性, 则要加反引号`(位于 Esc 键和数字 1 键的旁边). 如 `column`

删除属性(字段)

删除属性(字段)

ALTER TABLE <表名> DROP <属性名>

删除刚才新加入的 column1 字段.

mysql> alter table Beers drop column1;
Query OK, 14 rows affected (0.56 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc Beers;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  | UNI | NULL    |       |
| name_en | varchar(20) | YES  |     | NULL    |       |
| manf    | varchar(50) | YES  |     | NULL    |       |
| Country | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改属性(字段)的排列位置

修改属性(字段)的排列位置

ALTER TABLE <表名> MODIFY <要修改的字段> <数据类型> FIRST | AFTER <某字段>

mysql> alter table Beers modify name_en varchar(20) after manf;
Query OK, 14 rows affected (0.25 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc Beers;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  | UNI | NULL    |       |
| manf    | varchar(50) | YES  |     | NULL    |       |
| name_en | varchar(20) | YES  |     | NULL    |       |
| Country | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

• FIRST | AFTER <某字段> 是可选参数, 如果不加上, 就是前面介绍的修改属性的数据类型.

MySQL 中可以使用的存储引擎

MySQL 中可以使用的存储引擎

mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+

mysql> show engines\G;
*************************** 1. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
6 rows in set (0.00 sec)

更改表的存储引擎

更改表的存储引擎

ALTER TABLE <表名> ENGINE=<新的存储引擎名>;

mysql> show create table Beers\G;
*************************** 1. row ***************************
       Table: Beers
Create Table: CREATE TABLE `Beers` (
  `name` varchar(20) DEFAULT NULL,
  `manf` varchar(50) DEFAULT NULL,
  `name_en` varchar(20) DEFAULT NULL,
  `Country` varchar(20) DEFAULT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table Beers engine=myisam;
Query OK, 14 rows affected (0.19 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> show create table Beers\G;
*************************** 1. row ***************************
       Table: Beers
Create Table: CREATE TABLE `Beers` (
  `name` varchar(20) DEFAULT NULL,
  `manf` varchar(50) DEFAULT NULL,
  `name_en` varchar(20) DEFAULT NULL,
  `Country` varchar(20) DEFAULT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

改回 InnoDB 存储引擎.

mysql> alter table Beers engine=innodb;

与讲义同步实验

与讲义同步实验

接下来, 按照讲义中的有关操作进行实验.

3. SQL 介绍(一)
4. SQL 介绍(二)
5. 约束(constraints)
6. 事务、视图、索引
7. 实际 SQL 编程
8. 用于连接数据库的函数库
9. XML
10. 关于 XML 的查询语言

End

Thanks very much!