数据库基础知识和SQL语言

-- 世界技能大赛培训讲稿

wsc database

第一部分 数据库的基本概念

一、什么是数据库？

数据库（Database）是存放和管理数据的软件系统。数据库软件又叫DBMS(Database Management System, 数据库管理系统)。数据库的类型有多种，我们在本文关注的是关系型数据库系统（Relational Database Management System, RDBMS）。

二、什么是数据（Data）

数据是信息片断，它可能是一个名字，一张图片，一个数字，一段视频……

在这里，不要把数据（Data）和数字（Number）等同起来，数字只是众多数据中的一种。

三、数据有类型(Type)

在生活中，数据有多种类型，如一个人的姓名（字符串），一个产品的生产日期（日期型），一件衣服的价格（带小数的数字），一个班级的学生人数（整数）……

在用计算机软件管理数据时，必须告诉计算机数据具体的类型，以便计算机能够正确处理数据。因为不同的数据类型，要以有的运算是不同的。比如可以把字符串连接起来，表示一个新的更长的字符串，但把数值类型连接起来表示什么结果？又如数值类型可以加减运算，但两个日期相加表示什么结果？

因此，除了要明确告诉计算机数据的类型外，各类型数据还应该有不同的表示方法（事实上有的编程语言就可以按表示方法来正确推断数据类型）。

1、字符串类型(string)

表示方法：用引号括起来，如："张三"，"Billy"，'13987576542';

可以有的运算：

（1）字符串相连（许多语言用 + 号，PHP 语言用 . 号）
如：“Hello, " + "world!" （结果："Hello, world!"）

（2）取从第几个字符开始的几个字符作为子字符串；
（3）判断某个子字符串是否在另一个字符串中，有的话，从哪个位置开始；
（4）判断是否以某个子串开头；
（5）判断是否以某个子串结尾；
（6）有几个字符
（7）去除头尾的空格、换行符等

2、整型(integer)

表示方法：直接以数字表示，如：10，8900L(长整型)

可以有的运算：
（1）加、减、乘、除、模除（取余数）

3、浮点型（带小数的数值,float）

表示方法：直接以数字表示，如：10.00，89.12E5 (89*10的5次方）

可以有的运算：同整型

4、日期型(date)

表示方法：可以以字符串方式表示，但年、月、日必须合法。

可以有的运算：
（1）取出一个日期的年、月、日；
（2）判断一个年份是否是润年；
……

5、日期时间型(datetime)

基本同上，但带有时间信息。

6、逻辑型(boolean)

表示方法：true(真),false(假)

可以有的运算：
（1）与（相当于乘法，相当于文字表述的并且）, and
（2）或（相当于加法), or
（3）非（相当于取反值）, not
（4）异或（不同为真，相同为假）,xor

从数值上说，0为假，非0值为真。那么我们可以这样认为，1为真，0为假。我们来看看以上运算的结果：

1 * 1 = 1 （真 与 真 = 真）
1 * 0 = 0 （真 与 假 = 假）

结论：与运算中，只要有一个为假，则结果都为假！

练习，根据下面运算，看看能得到什么结果：
a) 当（2大于3) 并且 （3等于3）时，给我一个惊喜；
b) 当（地球体积大于月球体积）并且 （珠穆朗玛峰高度大于8000米）时，请我喝一杯可乐！

1 + 1 = 1 （真 或 真 = 真）
1 + 0 = 1 （真 或 假 = 真）
0 + 0 = 0 （假 或 假 = 假）

结论：或运算中，只要有一项为真，结果就为真。

练习：根据下面运算，看看能得到什么结果：
a) 当（地球不再转动）或者（火星人来到了地球）或者（海水是咸的）时候，我才不会借钱给你！
b) 当（冬雷阵阵）或者（夏雨雪）时，我才会离开你！

非运算，一般用 ! 表示：
!0 = 1
!1 = 0

结论：非运算相当于取反值。

异或运算：

树 异或 草 = 真 （它们不同）
花 异或 花 = 假 （它们相同）

结论：异或运算相当于问：“它们不同吗？”

四、数据的组织

1、数据的组织方式——表格(table)

数据总是用来描述事物的，比如一个人的基本情况，用到的数据有：姓名（字符串），性别（字符串或者逻辑值），出生日期（日期型），家庭人口数（整型），家庭年收入（浮点型）……

在常规的信息管理工作中，人们为了规范化管理，往往用表格来存储某项事物的数据信息。以上例来讲，我们可以用以下表格来存储多个个人的信息：

2、数据库系统对表格的三个主要范式的定义

2.1 第一范式

不论是常规管理还是计算机进行信息管理，表格数据的规范是很重要的。指出下表的不合理之处：

不合理之处在于：每一列(column)的信息必须是单一的，即只能描述单一的一个数据。每一列的取值不能是多值！以上“产品”列就试图将某种分类的商品的名称、数量、价格都描述出来，这是不合理的。

表格中的每一列数据都应该只是单值的（在数据库系统中，这叫做第一范式）！

2.2 第二范式

再看一个例子：

以上表格记录了员工的基本个人信息和银行帐号信息。请问，这张表是否有不合理之处？

为了不出现重复的行，表中 员工编号 和 银行帐号 二者结合起来必须唯一，即不能有 员工编号 和 银行帐号 都分别相同的两行数据。我们把这两列的结合叫做“组合主关键字”。

现在，这个表的不合理之处在于：如果一个员工有n个银行帐号，它的员工编号和姓名等信息会出现n次！相同的信息数据被存储多次，一是浪费空间，二是可能导致数据不一致（比如同一员工编号的姓名有些是张三，有些是李三）。

出现这个问题的原因在于：“姓名”只依赖于“组合主关键字”中的“员工编号”，而开户行只依赖于“银行帐号”。

为了不出现数据完全相同的行，表格中必须有决定每一行都不同的列，这一列的值叫做“主关键字”。表格中的任何一列都必须完全依赖于“主关键字”，如果关键字是“组合主关键字（两列及以上组合而成）”，那么，它所有的列都必须完全依赖于它们的组合，而不是一部分。这在数据库系统中称为第二范式。

2.3 第三范式

请看以下表格：

为了使每一行唯一，只要员工编号唯一即可，其它列除 所在国首都 之外，都依赖于 员工编号。
现在，这张表有什么问题？

所在国首都 不直接依赖于员工编号，而是间接依赖（传递依赖），即：所在国首都 --> 国籍 --> 员工编号。

一张表中的列必须直接完全依赖于主关键字，而不能出现传递依赖。在数据库系统中，这一要求叫第三范式。

3、以上不规范表格的规范化过程

3.1 不满足第一范式的范化过程

将每一个出现重复值的列用一张新的表格代替：

3.2 不满足第二范式的范化过程

将表格按组合主关键字的数目（2列，员工编号和银行帐号）进行拆分，以上表可拆分为两张表：

3.3 不满足第三范式的范化过程

原则：消除所有传递依赖

由于 所在国首都 依赖于 国籍，国籍 依赖于 员工编号，我们将所在国首都和国籍移到一张新表中去。

4、制表练习

假如你要为一家学校设计表格，这些表格将被大量印制出来，可以供学校长期使用而不需要重新设计。表格要求能够记录学生的基本信息（学号、姓名、性别、出生年月）、课程基本信息（课程号、课程名）以及每一位学生的各科成绩（假定这家学校的所有学生都学习了所有的课程），表与表之间可用主关键字相互参照。

答案：

表1：学生基本信息表

表2：课程基本信息

表3：成绩表

五、关系数据库中的关系

在以上例子中，我们发现一些表之间需要相互参照，这些相互参照的表之间具有一定的关系。这些关系总体可划分为：

1、一对一关系（one to one, 1:1)

如：一个公民只有一个社会保险帐号，一个社会保险帐号只针对一个公民，如果两者都需要描述，就需要建立关联关系。

表1：公民基本信息
列：身份证号，姓名，性别，……

表2：社会保险信息
列：社会保险帐号，建立日期，建立社局，……，身份证号

一对一关系中，被参照的叫主表（parent,如以上的公民基本信息），参照主表的表叫子表（child,如以上的社会保险信息）。在定义这类关系时，可选择：
（1）主表中的记录删除时，子表中的对应记录也自动一并删除（叫级联删除，cascade delete);
（2）主表删除时，将子表中参照的主表主关键字设定为“空”（null）；
（3）有子表参照时，不允许删除主表中的记录。

2、一对多关系(one to many, 1:n)

如：一个部门有多位员工，多位员工属于一个部门，这就是一对多关系。

表1：部门基本信息
列：部门编号，部门名称，部门地址，电话，……

表2：员工基本信息
列：编号，姓名，性别，出生日期，……，部门编号

在一对多关系中，一方叫主表，多方叫子表，定义这类关系与一对一关系相同。

3、多对多关系(many to many, n:n)

如：一个小组有多位成员，而一位成员可参加多个小组。

表1：小组信息
列：小组编号，小组名称，小组办公室，……

表2：成员信息
列：成员编号，姓名，性别，出生日期，……

表3：小组-成员
列：小组编号，成员编号

表1 对 表3 为 一对多，
表2 对 表3 为 一对多，
表1 对 表2 为 多对多。

可见，多对多要建立一个中间表（以上表3），将多对多转换为两个一对多。

在多对多关系中，两个多方都是主表，中间表是子表。中间表中的两个参照列组合起来应当唯一。

在所有关系中，被参照的列（主表中的主关键字）叫主键（Primary Key），子表中存储主表主键的列叫外键(Foreign Key)。

第二部分 MySQL 数据库

一、MySQL数据库系统简介

请参阅：百度百科关于MySQL的介绍

二、MySQL数据库的安装

1、独立服务器的安装

2、Web站点开发套件的安装（包含 MySQL, PHP, Apache及一些调试工具）

三、MySQL客户端的使用

MySQL 客户端是一个基于 Windows 命令行的一个工具，它用来连接数据库服务器（本地或远程），并对数据库进行管理。

1、启动客户端

mysql -h 主机地址 -P 连接端口 -u 用户名 -p

最后一个参数要求提示输入密码。
如果连接本机上的数据库服务器，采用默认端口（3306），则：

mysql -u 用户名 -p

即可。

打入以上命令后，即进入 MySQL 控制台。提示符为：

mysql>_

2、查看可使用的控制台所有命令

mysql>help  
或者
mysql>\h

3、退出控制台命令

mysql>\q

4、查看系统中的所有数据库

mysql>show databases;

注意：命令后面要跟英文的分号表示命令结束。

5、使用其中一个数据库

mysql>use 数据库名;

6、查看当前使用的数据库中的表

mysql>show tables;

7、查看某张表的结构定义

mysql>describe 表名;

8、执行文件系统中的脚本文件

mysql>\. [路径/]脚本文件名

第三部分 SQL 语言基础

SQL语言可分为两大类，一是数据定义语言，二是数据操纵语言。

一、DDL (Data Definition Language) 数据定义语言

数据定义语言用于定义和管理数据库中的各种对象。

1、创建数据库

命令格式：

CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET 字符集名;

含义：如果 [数据库名] 不存在的话，就创建数据库 [数据库名]并将其编码解码字符的方案设定为 [字符集名]。

create: 创建，创造
database: 数据库
if not exists: 如果不存在
default: 缺省，默认
charset: 字符集（用于编码字符）

2、使用（打开）数据库

命令格式：

USE 数据库名;

使用数据库后，以后所有的操作默认均针对本数据库。

例如：

CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARSET utf8;
USE mydb;

在第1行中，我们需要 创建数据库（create database） mydb 如果它并不存在的话（if not exists），并且默认字符集（default charset）为 utf8。
第2行，使用了刚创建的数据库。

2、删除数据库

命令格式：

DROP DATABASE IF EXISTS 数据库名;

含义：如果[数据库名]存在的话，就删除它。

drop: 丢弃
if exists: 如果存在

2、创建表

基本命令格式：

CREATE TABLE 表名 (
    列名1 数据类型 能否为空 列约束 缺省值，
    列名2 ...,
    ...[,
    约束1，
    约束2，
    ...
    ]
);

常见数据类型：

能否为空：

列约束：

缺省值：

约束1，...

删除和更新联动定义：

ON DELETE        NO ACTION | RESTRICT |    CASCADE | SET NULL
删除主表记录时   有参照记录就不允许删除    级联删除  子表参照值设为空

ON UPDATE NO ACTION | RESTRICT | CASCADE | SET NULL
主表主键更新时

[举例]
一家商店需要建立一个数据库来管理商品。要求：
商品可以随意分类，分类级数不定；
同一商品可属于多种分类；
商品中包含“库存量”、“成本价”、“销售价”等信息；
可查询指定的分类的所有商品；
可查询某一商品的所有分类；
可查询当前所有商品、某类商品、某种商品、某个商品的库存总量、成本总额和预期的销售毛利润。

a)定义数据字典及约束字典(只以一张表为例)
表名：categories

数据字典

约束字典

b)撰写脚本

SET NAMES utf8;
DROP DATABASE IF EXISTS shop;
CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8;
USE shop;
CREATE TABLE categories(
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL DEFAULT '',
    parent_id INTEGER UNSIGNED,
    CONSTRAINT cate_pk PRIMARY KEY (id),
    CONSTRAINT cate_fk FOREIGN KEY (parent_id) REFERENCES categories(id)
        ON DELETE RESTRICT ON UPDATE CASCADE 
);
INSERT INTO categories(id,name,parent_id) 
VALUES 
(1,'电器产品',NULL),
(2,'家用电器',1),
(3,'空调制冷',2),
(4,'中央空调',3),
(5,'电冰箱',3),
(6,'工业设备',NULL),
(7,'空调制冷',6);
INSERT INTO products(id,name,spec,left_count,uni_cost,uni_price)
VALUES
(1,'电冰箱','兰花310',20,3000.00,3800.00),
(2,'电冰箱','海尔888',12,1800.00,2500.00),
(3,'彩色电视机','长虹666',50,2888.88,4000.00),
(4,'空调机','美的123',20,1600.00,2600.00),
(5,'洗衣机','海尔300',37,3000.00,4500.00),
(6,'洗衣机','海尔160',36,2800.00,4000.00),
(7,'彩色电视机','TCL250',21,2000.00,3200.00),
(8,'电磁炉','松下ABC',60,500.00,700.00),
(9,'中央空调','格力（工业用）',2,300000.00,400000.00),
(10,'数控机床','昆机345',4,120000.00,150000.00);
INSERT INTO cate_prod
VALUES
(2,3),
(2,5),
(2,6),
(2,7),
(2,8),
(3,1),
(3,2),
(3,4),
(7,9),
(6,10),
(1,9);
SET NAMES gb2312;

查询举例：
（1）列出所有的分类

select * from categories;

（2）找到所有的顶级分类

select * from categories
where parent_id is null;

（3）找出“家用电器”下的所有子分类

select * from categories
where parent_id in
    (select id from categories
    where name='家用电器');

括号中的查询为子查询，为主查询准备值。

（4）查看所有商品

select * from products;

（5）找出商品名包含"洗衣机"的所有商品

select * from products
where name like '%洗衣机%';

（6）找出以"电"开头的所有分类

select * from categories where name like '电%';

（7）找出属于"家用电器"的所有商品，列出其名称、品牌规格、分类名称，并按商品名称升序排序

select p.name, p.spec, c.name
from products p, categories c, cate_prod cp
where p.id=cp.prod_id and c.id=cp.cate_id and c.name='家用电器'
order by p.name asc;

（8）找出商品名称为"中央空调"的所有分类的名称，列出分类名、商品名，并按分类名降序排序

select c.name,p.name
from categories c, products p, cate_prod cp
where p.id=cp.prod_id and c.id=cp.cate_id and p.name='中央空调'
order by c.name desc;

（9）找出库存中售价大于等于10000元的商品

select * from products
where uni_price>=10000.00;

（10）显示当前库存的成本总价、销售总价、未来的销售毛利润

select sum(uni_cost) as 成本总价, sum(uni_price) as 销售总价, sum(uni_price)-sum(uni_cost) as 毛利润
from products;

（11）显示当前库存的成本总价、销售总价、未来的销售毛利润，按商品名称分组

select name as 商品名称, sum(uni_cost) as 成本总价, sum(uni_price) as 销售总价, sum(uni_price)-sum(uni_cost) as 毛利润
from products
group by name;

二、DML (Data Manipulation Language) 数据操纵语言

（一）

数据操纵语言用于插入、删除、修改、查询（增、删、改、查）数据表中的记录。

1、增加记录

insert into 表名(字段列表) values (值列表)[,(值列表),...];

注意：
（1）列（column）又叫做 字段（field）。在以上语句中，方括号里的内容是可选的，便于一次插入多条记录。同时，字段列表 与 值列表 应当一一对应，列表均以逗号分隔列表项；
（2）如果省略字段列表，表示要插入所有字段的值，值应当与表结构定义中的列的定义顺序一致；
（3）如果不省略字段列表，则那些 NOT NULL 的并且没有默认值（default）的列必须出现在列表中。AUauto_increment 的字段可以不出现在列表中（它将会自动被填入）。

如：

insert into category(name,description)
values('服装类','所有服装归于此类');

2、删除记录

delete from 表名 [where 条件];

说明：
（1）如果省略方括号里的内容，则会删除表中的所有记录；
（2）条件 可以是多个条件的组合（and, or）。

如：

delete from products where name like '%电%' or uni_price>=10000;

删除名称里包含"电"或者售价大于等于10000元的商品。

3、修改记录

update 表名 set 字段名=值[,字段名=值,...]
[where 条件];

说明：
（1）条件可以是各种条件的组合；条件省略时，将表中所有的行都做修改；
（2）可以同时修改多个列的值。

如：

update products set uni_price=uni_price*1.1;

将商品表中所有商品的售价提高了10%。

4、查询记录

select 字段列表 from 表1[,表2,...]
[where 条件
order by 字段名 升降序,[字段名 升降序,...]
[group by 字段名]]
[offset 整数]
[limit 整数]
;

说明：
（1）字段列表 可以来自多个表（前提是 from 了多个表），也可以用 * 表示所有字段；
（2）where 条件可以多个条件的组合；
（3）order by 用于记录排序，排序原则先按第一排序列排序，如果第一排序列相同，按第二排序列排序，依此类推。desc 表示降序，asc 表示升序（默认）。
（4）group by 用于统计查询时对统计结果按指定字段进行分组；
（5）offset 后面的数字表示从第几条记录开始读取；
（6）limit 后面的数字表示只要几条数据。

三、SQL 语言中的逻辑运算与比较运算

1、逻辑运算（用于组合条件）

（1）与(两条件同时满足)

条件1 and 条件2

（2）或（条件只要满足一个）

条件1 or 条件2

（3）非（取反）

not 条件1

条件1 为真则结果为假，为假则结果为真。

2、比较运算

四、数据的导入与导出

1、数据导入

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char' ]
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...)]

举例，将本地文件 f:\sql\mydata.txt 导入到表 departments 中。

load data local infile 'f:/sql/mydata.txt'
into table departments(name,address,phone,description)
fields terminated by '\t'
optionally enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines;

第 1 行，load data 表示载入数据，local 指明输入数据文件在本地主机，infile 后面指明要载入的数据文件。如果文件包含路径，路径的的分隔符必须用正斜线；
第 2 行 into table 后面写要导入数据的表名称；
第 3 行 '\t' 代表 tab 键，指明数据文件中的数据项是用制表符分隔的；
第 4 行 指明如果数据文件中的数据项由双引号括起来的话，不要把双引号当作值的内容；
第 5 行 说明数据文件的每一行的结束标志，windows 为 '\r\n'，其它操作系统为 '\n'；
第 6 行 要求忽略第一行数据（表头标题行）。

注意：
导入时，数据文件的值列应当与表名称后的字段列表顺序一致，如果不指明字段列表，则值列应当与列定义的顺序一致，可用 ‘describe 表名’ 来查看字段顺序。

2、导出数据

举例：

select * from products
into outfile 'f:/sql/products.txt'
fields terminated by '\t'
optionally enclosed by '"'
lines terminated by '\r\n';

第 2 行指明导出的文件在 f:\sql\products.txt。