首页 > Java > MySQL(SQL入门)
2014
08-21

MySQL(SQL入门)

5、数据完整性约束

数据完整性是为了保证插入到数据表中的数据是正确的,它防止了用户可能的输入错误。数据的完整性分为以下三类:

1)实体完整性

规定表的一行(即每一条记录)在表中是唯一的实体,实体完整性通过表的主键来实现。

创建表格时声明主键的三种写法:

① 只能指定一个字段作为主键:

create table t1(

id int primary key,

name varchar(100)

);

② 可指定联合主键:

create table t2(

id int,

name varchar(100),

primary key(id)

);

③ 建议写法:先创建表格,再修改约束

create table t3(

id int,

name varchar(100)

);

alter table t3 add primary key(id);

数据表中存在如下两种主键:

逻辑主键(建议):除了唯一标识一条记录外,没有别的意义,一般取名ID。

业务主键:有一定的业务意义。

在MySQL中,AUTO_INCREMENT通常和主键一起用,如:

create table t4(

id int primary key auto_increment, #不建议使用,会导致数据库迁移不方便,因为并不是所有的数据库都支持自动增长,如Oracle。

name varchar(100)

);

2)域(字段)完整性

指数据表的列(即字段)必须符合某种特定的数据类型或约束,有如下几个:

① 数据类型约束

② 非空约束:not null

③ 唯一约束:unique

create table t5(

id int primary key auto_increment,

username varchar(100) not null unique,

phone_num varchar(11) unique,

gender varchar(10) not null

);

3)参照完整性(定义外键):多表的设计

参照完整性约束通过定义外键实现,又称外键约束。外键约束反映的是实体与实体之间的关系,通常分为一对多、多对多、一对一的关系。在设计多表的时候,应注意以下对应关系:

  • 表结构、Java类定义
  • 表中的记录、Java对象:表中一条记录对应一个Java对象的数据
  • 表间的关系、Java对象间的关系

① 一对多(非常常见)

如客户(Customer)与订单(Order)的关系,可以设计如下的数据表:

01. MySQL(SQL入门)6190

此种关系对应的JAVA类和数据表定义如下:

Java class and data table1

附数据表、列の命名问题:命名不要和关键子冲突(如本例中的orders表),解决办法:

a. 创建表时,将表名或列名用反引号(Esc按键的下方)引起来,如create table `order`(…);

b. 用复数形式如本例中的表名ORDERS(建议)

 ② 多对多(比较常见)

如教师(Teacher)与学生(Student)的关系,可以设计如下的数据表:

01. MySQL(SQL入门)7026

此种关系对应的JAVA类和数据表定义如下:Java class and data table2

③ 一对一(几乎不用)

如用户(USERS)和身份证(ID_CARD)的对应关系,实际开发中就直接将身份证作为用户表的一个字段。

6、DQL(下):多表查询

本节练习多表查询,练习前请在MySQL中导入下面两张数据表(CUSTOMERS、ORDERS)。百度网盘

1)连接查询

连接查询的from子句的连接语法格式为:

from 表1 join_type 表2 [on (join_condition)] [where (query_condition)]

① 交叉连接(cross join):不带on子句,返回多表的所有数据行的笛卡尔积。

select * from customers cross join orders;

通常不写cross join:select * from customers,orders; 返回customers表和orders表的笛卡尔积,共35行记录(表1行数 * 表2行数)

② 内连接(inner join):

显式内连接:select * from customers c inner join orders o on c.id=o.customer_id;

隐式内连接:select * from customer c,orders o where c.id=o.customer_id;

内连接结果如下图,它返回符合连接条件(on子句)及查询条件(where子句)的数据行

01. MySQL(SQL入门)8375

③ 外连接(outter join):有方向性,分左、右连接

左外连接(不带where子句):select * from customers c left join orders o on c.id=o.customer_id;

左外连接结果如下图,除了包含内连接返回的结果,还包含左表中剩余的数据行。

01. MySQL(SQL入门)8529

右外连接(不带where子句):select * from customers c right join orders o on c.id=o.customer_id;

右外连接结果如下图,除了包含内连接返回的结果,还包含右表中剩余的数据行:

01. MySQL(SQL入门)8653

带上where子句对结果过滤:select * from customers c right join orders o on c.id = o.customer_id where o.price > 150;

查询结果如下图,会在上面的右外连接查询的基础上过滤掉价格在150元以下的商品:

01. MySQL(SQL入门)8801

提示:必须仔细体会连接条件(on子句)和查询条件(where)的用法区别!

2)子查询:又称为嵌套查询,即在where子句中又嵌入select语句,子查询语句放在小括号之内。执行顺序:先执行内查询(子查询),再执行外查询。

查询“陈冠希”的所有订单信息:

select * from orders where customer_id=(select id from customers where name=’陈冠希’);

查询订单价格大于100的有哪些客户:

select * from customers where id in (select customer_id from orders where price>100);

3)报表查询:使用group by子句对数据进行分组统计

语法格式:select … from … [where…] group by … [having… ]

对订单表中订单按用户分组后,显示每个用户购买的商品总额:

select customer_id ‘用户编号’,sum(price) ‘订单总额’ from orders group by customer_id;

在上述查询的基础上,只显示订单总额大于300的用户:

select customer_id ‘用户编号’,sum(price) ‘订单总额’ from orders group by customer_id having sum(price)>300;

这种写法是错误的:select customer_id ‘用户编号’,sum(price) ‘订单总额’ from orders group by customer_id where sum(price)>300;

错误原因解析:报表查询语句各部分执行顺序(☆):

① 执行where子句查找符合条件的数据;

② 使用group by 子句对数据进行分组;对各个分组运行合计函数计算每一组的值

③ 最后用having 子句去掉不符合条件的组。

由此可知,在where子句中不能出现合计函数,having子句中可以出现合计函数,并且一般总会出现合计函数。

7、其他问题

1)SQL合计函数(又叫聚集函数、聚合函数)

本节练习使用第4节单表查询中的Student表。

① count(*):返回结果集中行的总数。

统计数学成绩大于等于90的学生有多少个?

select count(*) from student where math>=90;

统计总分大于250的人数有多少?

select count(*) from student where (english+chinese+math)>250;

count()在报表查询中也可计算每个分组的行数:select english,count(*) from student group by english;

② sum(列名):返回结果集中指定列的和

统计一个班级语文、英语、数学各科的总成绩

select sum(chinese),sum(english),sum(math) from student;

统计一个班级语文、英语、数学的成绩总和

select sum(english+chinese+math) from student;

③ avg(列名):返回结果集中指定列的平局值

统计一个班级语文成绩平均分

select avg(chinese) from student; 或者 select sum(chinese)/count(*) from student;

④ max/min:……

2)SQL书写规范

可以将SQL中的关键字、保留字大写,自己命名的表名、字段名等小写。仅仅是建议书写规范,具体还要看开发时的规定。

3)MySQL的数据备份与恢复

本节只介绍命令行下的备份与恢复。

备份数据(表结构、数据):C:\Users\flyne>mysqldump -u root -p mydb1>d:/mydb1.sql

注意备份文件路径的写法,用“/”。

恢复数据:使用mysqldump命令导出的sql脚本中只有定义表结构(create table)和导入数据(insert into)的语句,因此在恢复数据时必须先选择一个数据库。有如下两种方式导入:

方式一:

mysql> use mydb1;

mysql> source d:/mydb1.sql

方式二:

C:\Users\flyne>mysql -u root -p mydb2<d:/mydb.sql


留下一个回复

你的email不会被公开。