mysql基础回顾（二）

一、DQL：数据查询语言，针对单表的查询

查询表中所有学生的信息。
select * from STUDENT;
查询表中所有学生的姓名和对应的英语成绩。
select NAME,ENGLISH from STUDENT;
显示英语成绩，过滤表中重复数据
select distinct ENGLISH from STUDENT;
在所有学生数学分数上加10分特长分。
select NAME,MATH+10 from STUDENT;
统计每个学生的总分。
select NAME,MATH+CHINESE+ENGLISH from STUDENT;
使用别名表示学生分数。
select NAME as 姓名,MATH+CHINESE+ENGLISH 总分 from STUDENT;

查询姓名为wu的学生成绩
select * from STUDENT where NAME=’陈冠希’;
查询英语成绩大于90分的同学
select * from STUDENT where ENGLISH>90;
查询总分大于200分的所有同学
select * from STUDENT where (MATH+CHINESE+ENGLISH)>200;

查询英语分数在 80－90之间的同学。
select NAME,ENGLISH from STUDENT where ENGLISH between 80 and 90;
查询数学分数为89,90,91的同学。
select NAME,MATH from STUDENT where MATH in (89,90,91);
查询所有姓李的学生成绩。
select NAME,MATH from STUDENT where NAME like ‘李%’;
查询数学分>80，语文分>80的同学。
select NAME,MATH,CHINESE from STUDENT where MATH>80 and CHINESE>80;

注意：not and or 优先级依次降低。一元运算符比二元的要高。

对数学成绩排序后输出。
select NAME,MATH from STUDENT order by MATH;
对总分排序后输出，然后再按从高到低的顺序输出
select NAME,MATH+CHINESE+ENGLISH 总分 from STUDENT order by 总分 desc;
对姓李的学生语文成绩排序输出
select NAME,CHINESE from STUDENT where NAME like ‘李%’ order by CHINESE;

注意：订单，order。创建表时要注意不要和关键字冲突。
解决办法：
1、把用户定义的内容用反引号引起来。`(Esc按键的下方)
2、数据库命名习惯：表名ORDERS （建议）

二、完整性约束

数据完整性是为了保证插入到数据中的数据是正确的，它防止了用户可能的输入错误。

2.1实体（记录）完整性

规定表的一行（即每一条记录）在表中是唯一的实体(Entity)。实体完整性通过表的主键来实现。
主键：不能为null；唯一

创建一个表格T1，并声明一个主键
写法一：（只能指定一个字段作为主键）
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：
create table T4(
ID int primary key auto_increment,#自动增长，不建议使用。数据库迁移不方便，并不是所有的数据库都支持自动增长，比如Oracle就不支持。
NAME varchar(100)
);

2.2域（字段）完整性

指数据库表的列（即字段）必须符合某种特定的数据类型或约束
1、约束数据的类型：强类型
2、非空约束：not null
3、唯一约束：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
);

2.3参照完整性（多表的设计）：定义外键

注意：类和表结构的关系；对象和记录的关系（帮助学习ORM：Hibernate、MyBatis）
ORM:Object Relation Mapping（Java面向对象 DB关系型数据库 映射）

第一：（非常多）一对多

create table CUSTOMERS(
ID int primary key,
NAME varchar(100),
ADDRESS varchar(100)
);
create table ORDERS(
ID int primary key,
ORDER_NUM varchar(100),
AMOUNT float(10,2),
CUSTOMER_ID int,
constraint CUSTOMER_ID_FK foreign key (CUSTOMER_ID) references CUSTOMERS(id)
);

注意：
表结构：Java类定义
表中的记录：Java对象
表间的关系：Java对象间的关系

第二：多对多（比较常见）

create table TEACHERS(
ID int primary key,
NAME varchar(100),
SALARY float(10,2)
);
create table STUDENTS(
ID int primary key,
NAME varchar(100),
GRADE char(1)
);
create table TEACHERS_STUDENTS(
T_ID int,
S_ID int,
primary key(T_ID,S_ID),
constraint T_ID_FK foreign key (T_ID) references TEACHERS(id),
constraint S_ID_FK foreign key (S_ID) references STUDENTS(id)
);

第三种：一对一（很少用到。实际开发根本不用）

三、多表的查询（面试）

1、连接查询(面试几率很大)

a、交叉连接：（cross join）

返回多表的记录的笛卡尔积。（第一张表：5 第二章表：7，返回5*7=35）

select * from CUSTOMER,ORDERS;
select c.ID,c.NAME,o.ORDER_NUMBER,o.price from CUSTOMER c,ORDERS o;

b、内连接(inner join)

隐式内连接：select * from CUSTOMER c,ORDERS o where c.ID=o.CUSTOMER_id;
显式内连接：
select * from CUSTOMER c inner join ORDERS o on c.Id=o.CUSTOMER_id;

c、外连接(outer join)

查看所有客户信息，同时有订单的显示订单的详细信息。
左外：返回满足连接条件的记录，同时返回左表中剩余的其他记录。
select * from CUSTOMER c left outer join ORDERS o on c.ID=o.CUSTOMER_ID;

查看所有订单信息，同时返回下单的客户信息
右外：返回满足连接条件的记录，同时返回右表中剩余的其他记录。
select * from CUSTOMER c right outer join ORDERS o on c.ID=o.CUSTOMER_ID;

2、子查询

又称之为嵌套查询。查询中有查询。
执行顺序：先执行子查询（内查询），再执行外查询。
子查询：必须放在小括号之内。

查询陈冠希的所有订单信息。
形式一：多条查询语句
select ID from CUSTOMER where NAME=’陈冠希’;
select * from ORDERS where CUSTOMER_ID=1;
形式二：subselect（子查询）
select * from ORDERS where CUSTOMER_ID=(select ID from CUSTOMER where NAME=’陈冠希’);

查询订单价格大于100的有哪些客户
select * from CUSTOMER where ID in (select CUSTOMER_ID from ORDERS where PRICE>100);

3、MySQL常用的函数

SUM():求和
COUNT()：计数
AVG()：求平均
MAX()：求最大
MIN()：求最小
统计一个班级共有多少学生？
select count(*) from STUDENT;
统计数学成绩大于90的学生有多少个？
select count(*) from STUDENT where MATH>=90;
统计总分大于250的人数有多少？where可以使用表达式
select count(*) from STUDENT where (ENGLISH+CHINESE+MATH)>250;
统计一个班级数学总成绩？
select sum(MATH) from STUDENT;
统计一个班级语文、英语、数学各科的总成绩
select sum(CHINESE),sum(ENGLISH),sum(MATH) from STUDENT;
统计一个班级语文、英语、数学的成绩总和
select sum(ENGLISH+CHINESE+MATH) from STUDENT;
统计一个班级语文成绩平均分
select sum(CHINESE)/count(*) from STUDENT;
select avg(CHINESE) from STUDENT; #如果字段为null，不参与平均的计算。

4、分组（报表）查询：group by

对订单表中商品归类后，显示每一类商品的总价
select PRODUCT,sum(PRICE) from PRODUCTS group by PRODUCT;
查询购买了几类商品，并且每类总价大于100的商品
select PRODUCT,sum(PRICE) from PRODUCTS group by PRODUCT having sum(PRICE)>100;

四、MySQL的备份与数据恢复

1、 备份数据(表结构和数据)

c:\MySQL\bin\mysqldump -h localhost -p3306 -u root -p day14>d:/day14.sql

2、恢复数据库中的数据

前提：必须先创建数据库名

方式一：
mysql>user day14;
mysql>source d:/day14.sql;
方式二：
c:\MySQL\bin\mysql -u root -p day14