1、常见关系化数据库
（1）数据库的概念
（2）关系型数据库
（3）常见数据库
商业数据库
Oracle
SQLServer
DB2
Sybase
开源数据库
MySQL
SQLLite
2、MySQL安装与配置
（1）MySQL数据库的安装和配置
参照文档
（2）使用命令行窗口连接MYSQL数据库
mysql –u用户名 –p密码
3、MySQL数据库服务器、数据库和表的关系
（1）所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
（2）为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
（3）数据库服务器、数据库和表的关系如图所示
4、数据在数据库中的存储方式
5、SQL语言
（1）Structured Query Language, 结构化查询语言
（2）非过程性语言
（3）美国国家标准局(ANSI）与国际标准化组织（ISO）已经制定了SQL标准
（4）为加强SQL的语言能力，各厂商增强了过程性语言的特征
如Oracle的PL/SQL 过程性处理能力
SQL Server、Sybase的T-SQL
（5）SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能
6、创建数据库
CHARACTER SET：指定数据库采用的字符集
COLLATE：指定数据库字符集的比较方式
create database [if not exists] db_name [character set xxx] [collate xxx]
*创建一个名称为mydb1的数据库。
create database mydb1;
*创建一个使用utf8字符集的mydb2数据库。
create database mydb2 character set utf8;
*创建一个使用utf8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_bin ;
7、查看、删除数据库
Basic SELECT Statement
In its simplest form, a SELECT statement must include the following:
A SELECT clause, which specifies the columns to be displayed
A FROM clause, which specifies the table containing the columns listed in the SELECT clause
In the syntax:
SELECTis a list of one or more columns
* selects all columns
DISTINCTsuppresses duplicates
column|expressionselects the named column or the expression
aliasgives selected columns different headings
FROM table specifies the table containing the columns
Note: Throughout this course, the words keyword, clause, and statement are used as follows:
A keyword refers to an individual SQL element.
For example, SELECT and FROM are keywords.
A clause is a part of a SQL statement.
For example, SELECT employee_id, last_name, ... is a clause.
A statement is a combination of two or more clauses.
For example, SELECT * FROM employees is a SQL statement.
查看当前数据库服务器中的所有数据库 show databases;
查看前面创建的mydb2数据库的定义信息show create database mydb2;
删除前面创建的mydb1数据库 drop database mydb1;
8、修改数据库
切换数据库 use db_name;
查看当前使用的数据库 select database();
--default-character-set=charset 指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
练习
查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;
9、创建表(基本语句)
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)character set 字符集 collate 校对规则
field：指定列名 datatype：指定列类型
*创建一个员工表employee
create table employee(
id int primary key auto_increment,
name varchar(20) unique,
gender bit not null,
birthday date,
entry_date date,
job varchar(20),
salary double,
resume text
);
10、MySQL常用数据类型
（1）字符串型
VARCHAR、CHAR
（2）大数据类型
BLOB、TEXT
（3）数值型
TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE
（4）逻辑型
BIT
（5）日期型
DATE、TIME、DATETIME、TIMESTAMP
11、创建表练习
注意创建数据表时，只有字符串必须写长度，其他都有默认长度
12、定义单表字段的约束
（1）定义主键约束
primary key:不允许为空，不允许重复
删除主键：alter table tablename drop primary key ;
主键自动增长 ：auto_increment
创建表时指定约束：
create table tb(
id int primary key auto_increment,
name varchar(20) unique not null,
ref_id int,
foreign key(ref_id) references tb2(id)
);
create table tb2(
id int primary key auto_increment
);
增加主键（自动增长，只有主键可以自动增长）
Alter table tb add primary key(id) ;
删除主键
alter table 表名 drop primary key
增加自动增长
Alter table employee modify id int auto_increment;
删除自动增长
Alter table tb modify id int;
（2）定义唯一约束
unique
例如：name varchar(20) unique
（3）定义非空约束
not null
例如：salary double not null
（4）外键约束
增加外键：
可以明确指定外键的名称，如果不指定外键的名称,mysql会自动为你创建一个外键名称。
RESTRICT : 只要本表格里面有指向主表的数据， 在主表里面就无法删除相关记录。
CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。
alter table book add [constraint FK_BOOK] foreign key(pubid) references pub_com(id) [on delete restrict] [on update restrict];
删除外键
alter table 表名 drop foreign key 外键（区分大小写，外键名可以desc 表名查看）;
13、查看表信息
（1）查看表结构：
desc tabName
（2）查看当前所有表：
show tables
（3）查看当前数据库表建表语句
show create table tabName;
14、修改表
(1)增加一列——alter table tab_name add [column] 列名 类型;
(2)修改一列类型——alter table tab_name modify 列名 类型;
(3)修改列名——alter table tab_name change [column] 列名 新列名 类型;
(4)删除一列——alter table tab_name drop [column] 列名;
(5)修改表名——rename table 表名 to 新表名;
(6)修该表所用的字符集——alter table student character set utf8;
练习
在上面员工表的基本上增加一个image列。
alter table employee add image blob;
修改job列，使其长度为60。
alter table employee modify job varchar(60);
删除gender列。
alter table employee drop gender;
表名改为user。
rename table employee to user;
修改表的字符集为utf8
alter table user character set utf8;
列名name修改为username
alter table user change column name username varchar(20);
15、删除表
drop table tabName;
*删除员工信息表
drop table user;
16、数据库表记录CRUD语句
（1）Insert语句 (增加数据)
（2）Update语句 (更新数据)
（3）Delete语句 (删除数据)
（4）Select语句 (查找数据)
17、Insert语句
（1）插入的数据应与字段的数据类型相同。
（2）数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为 的列中。
（3）在values中列出的数据位置必须与被加入的列的排列位置相对应。
（4）字符和日期型数据应包含在单引号中。
（5）插入空值：不指定或insert into table value(null)
（6）如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表
18、Insert语句练习
insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values (null,'张飞',1,'1990-09-09','1991-10-10','打手',998.00,'真的很能打');
insert into employee values (null,'关羽',1,'1989-08-08','1990-09-09','财神',9000,'公司挣钱全指着他了');
insert into employee values (null,'刘备',1,'1988-01-01','1990-09-01','ceo',900000,'公司的boss'),(null,'赵云',1,'2000-01-01','2000-09-01','保安队队长',800.00,'老总的贴身保镖');
19、mysql中文乱码
（1）mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。
client是客户端使用的字符集。
connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
server是服务器安装时指定的默认字符集设定。
system是数据库系统使用的字符集设定。（utf-8不可修改）
（2）show variables like'character%';
（3）set names gbk;指定当前窗口所使用的编码集
（4）通过修改my.ini 修改字符集编码
mysql安装目录下的my.ini修改[mysql]中的defaultcharacter为gbk，并重启服务
20、Update语句
（1）UPDATE语法可以用新值更新原有表行中的各列。
（2）SET子句指示要修改哪些列和要给予哪些值。
（3）WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
21、Update语句练习
将所有员工薪水修改为5000元。
update employee set salary=5000;
*将姓名为’张飞’的员工薪水修改为3000元。
update employee set salary=3000 where name='张飞';
*将姓名为'关羽’的员工薪水修改为4000元,job改为ccc。
update employee set salary=4000,job='ccc' where name='关羽';
*将刘备的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='刘备';
22、Delete语句
（1）如果不使用where子句，将删除表中所有数据。
（2）Delete语句不能删除某一列的值（可使用update）
（3）使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
（4）同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
（5）删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档
23、Delete语句练习
（1）删除表中名称为’zs’的记录。
（2）删除表中所有记录。
（3）使用truncate删除表中记录。
delete from tbl_name [WHERE where_definition]
*删除表中名称为'张飞’的记录。
delete from employee where name='张飞';
*删除表中所有记录。
delete from employee;
*使用truncate删除表中记录。
truncate employee;
24、Select语句
（1）select 指定查询哪些列的数据。
（2）column指定列名。
（3）*号代表查询所有列。
（4）from指定查询哪张表。
（5）DISTINCT可选，指显示结果时，是否剔除重复数据
练习：
查询表中所有学生的信息。
select * from exam;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from exam;
过滤表中重复数据。
select DISTINCT english from exam;
练习
在所有学生分数上加10分特长分显示。
select name,english+10,chinese+10,math+10 from exam;
统计每个学生的总分。
select name,english+chinese+math from exam;
使用别名表示学生总分。
select name,math+chinese+english 总分 from exam
或select name,math+chinese+english as 总分 from exam
使用where子句，进行过滤查询。练习：
查询姓名为XXX的学生成绩
select * from exam where name='张三';
查询英语成绩大于80分的同学
select * from exam where english>80;
查询总分大于200分的所有同学
select name,math+english+chinese as 总成绩 from exam where math+english+chinese>200 ;
> < >= <= <>
between 10 and 20 值在10到20之间
in(10,20,3)值是10或20或30
like '张pattern' pattern可以是%或者_，如果是%则表示任意多字符，此例中张三丰 张飞 张abcd ，如果是_则表示一个字符张_，张飞符合。张
Is null
练习：
查询英语分数在 80－100之间的同学。
select name ,english from exam where english between 80 and 100;
查询数学分数为88,89,90的同学。
select name ,math from exam where math in (88,89,90);
查询所有姓张的学生成绩。
select *from exam where name LIKE '张%'
查询数学分>60，语文分>60的同学。
select name from exam where math>60 and chinese >60;
查找缺考数学的学生的姓名
select name from exam where math is null;
Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。
Asc 升序、Desc 降序
ORDER BY 子句应位于SELECT语句的结尾。
练习：
对数学成绩排序后输出。
select * from exam order by math;
对总分排序按从高到低的顺序输出
select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam order by 总成绩 desc;
对姓李的学生成绩排序输出
select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam where name like '李%' order by 总成绩 desc;
25、聚集函数－SUM
技巧,先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。
练习：
统计一个班级数学总成绩？
select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(english),sum(chinese) from exam;
统计一个班级语文、英语、数学的成绩总和
select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 总成绩 from exam;
统计一个班级语文成绩平均分
select sum(chinese)/count(*) from exam ;
注意：sum仅对数值起作用，否则会报错。
注意：对多列求和，“，”号不能少。
*实验，求一个人的总分时，如果他的某项成绩为null，则计算出的结果为null。此时需要用ifnull进行转换null为0计算
26、聚集函数－AVG
练习：
求一个班级数学平均分？
select avg(ifnull(math,0)) from exam;
求一个班级总分平均分？
select avg((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam ;
27、聚集函数－count
练习：
统计一个班级共有多少学生？
select count(*) from exam;
统计数学成绩大于70的学生有多少个？
select count(math) from exam where math>70;
统计总分大于250的人数有多少？
count(name) from exam where (ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))>220;
28、聚集函数－MAX/MIN
练习：
求班级最高分和最低分（数值范围在统计中特别有用）
select Max((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;
select Min((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;
29、Select语句——分组操作
GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。
注意，使用GROUP BY 时，SELECT 子句中只能由以下部分组成：
1. 汇总函数
2. GROUP BY 中出现的列名
3. 1和2 通过函数和表达式的结果
例如：
SELECT A_ID +COUNT(*) FROM C GROUP BY A_ID; =>合法
SELECT A_DATE,COUNT(*) FROM C GROUP BY A_ID; =>不合法，如图示中的按A_ID分组后，对于其他各列的查询只能是汇总查询，否则没有意义
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
练习：对订单表中商品归类后，显示每一类商品的总价
select product,sum(price) from orders group by product;
使用having 子句 对分组结果进行过滤
练习：查询购买了几类商品，并且每类总价大于100的商品
select product，sum(price) from orders group by product having sum(price)>100;
where和having区别：where在分组前进行条件过滤，having在分组后进行条件过滤。使用where的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用。
select from where groupby having orderby 执行顺序：from where select group by having order by
select math+english+chinese as 总成绩 from exam where 总成绩 >250; ---- 不成功
select math+english+chinese as 总成绩 from exam having 总成绩 >250; --- 成功
select math+english+chinese as 总成绩 from exam group by 总成绩 having 总成绩 >250; ----成功
select math+english+chinese as 总成绩 from exam order by 总成绩;----成功
select * from exam as 成绩 where 成绩.math>85; ---- 成功
30、备份、恢复数据库
（1）备份数据库表中的数据
cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql
（2）恢复数据库
source 文件名.sql // 在mysql内部使用
mysql –u 用户名 -p 数据库名 < 文件名.sql // 在cmd下使用
练习
备份test库中的数据，并恢复
31、多表设计——外键约束
（1）定义外键约束
foreign key
foreign key(ordersid) references orders(id)
（2）新建部门表department (id,name)
（3）通过外键约束建立与员工表employee关系
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values(null,'财务部'),(null,'人事部'),(null,'行政部'),(null,'科技部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);
insert into emp values(null,'奥巴马',1),(null,'张三丰',2),(null,'哈利波特',2),(null,'朴乾',4);
32、多表设计中三种实体关系
（1）M:N的联系引入关系表
（1）联系的属性及两个实体的主标识形成关系表。
（2）关系表的主键为两个实体主标识的组合
（2）1:N 的联系可不引入关系表
外键实际上是一种约束，表示这个属性是另一个实体集中的主标识属性，即另一个表中的主键。
外键用于建立和加强两个表数据之间的链接的一列或多列。
1:1的联系不必引入关系表
一对一（311教室和20130405班级，两方都是一）:在任意一方保存另一方的主键
一对多、多对一（班级和学生，其中班级为1，学生为多）：在多的一方保存一的一方的主键
多对多（教师和学生，两方都是多）：使用中间表，保存对应关系
33、多表设计实例
create table tb (id int primary key,name varchar(20) );
create table ta (
id int primary key,
name varchar(20),
tb_id int
);
insert into tb values(1,'财务部');
insert into tb values(2,'人事部');
insert into tb values(3,'科技部');
insert into ta values (1,'刘备',1);
insert into ta values (2,'关羽',2);
insert into ta values (3,'张飞',3);
mysql> select * from ta;
+----+------+-------+
| id | name | tb_id |
+----+------+-------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | bbb | 4 |
+----+------+-------+
mysql> select * from tb;
+----+------+
| id | name |
+----+------+
| 1 | xxx |
| 2 | yyy |
| 3 | yyy |
34、多表查询——笛卡尔积
笛卡尔积查询：两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录，其中很多错误数据
select * from ta ,tb;
mysql> select * from ta ,tb;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 1 | xxx |
| 3 | bbb | 4 | 1 | xxx |
| 1 | aaa | 1 | 2 | yyy |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | 2 | yyy |
| 1 | aaa | 1 | 3 | yyy |
| 2 | bbb | 2 | 3 | yyy |
| 3 | bbb | 4 | 3 | yyy |
+----+------+-------+----+------+
35、多表查询——内连接查询
查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
select * from ta ,tb where ta.tb_id = tb.id;
select * from ta inner join tb on ta.tb_id = tb.id;
mysql> select * from ta inner join tb on ta.tb_id = tb.id;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
+----+------+-------+----+------+
36、多表查询——外连接查询
（1）左外连接：在内连接的基础上增加左边有右边没有的结果
select * from ta left join tb on ta.tb_id = tb.id;
mysql> select * from ta left join tb on ta.tb_id = tb.id;
+----+------+-------+------+------+
| id | name | tb_id | id | name |
+----+------+-------+------+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | NULL | NULL |
+----+------+-------+------+------+
（2）右外连接：在内连接的基础上增加右边有左边没有的结果
select * from ta right join tb on ta.tb_id = tb.id;
mysql> select * from ta right join tb on ta.tb_id = tb.id;
+------+------+-------+----+------+
| id | name | tb_id | id | name |
+------+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| NULL | NULL | NULL | 3 | yyy |
+------+------+-------+----+------+
（3）全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
select * from ta full join tb on ta.tb_id = tb.id; --mysql不支持全外连接
select * from ta left join tb on ta.tb_id = tb.id
union
select * from ta right join tb on ta.tb_id = tb.id;
mysql> select * from ta left join tb on ta.tb_id = tb.id
-> union
-> select * from ta right join tb on ta.tb_id = tb.id; --mysql可以使用此种方式间接实现全外连接
+------+------+-------+------+------+
| id | name | tb_id | id | name |
+------+------+-------+------+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | NULL | NULL |
| NULL | NULL | NULL | 3 | yyy |
+------+------+-------+------+------+