【数据库模块】第39天——Mysql

MySQL

完成对MYSQL数据库中的数据库,表及数据的CRUD的操作
给定一套系统模型可以分析其中表关系并完成建表操作
完成对MYSQL数据库的多表查询及建表操作.

掌握MYSQL的基本数据库,表及表中记录的操作
掌握MYSQL中的多表创建及多表查询
掌握MYSQL中的表关系分析并能正确建表
了解MYSQL可视化界面的使用


1.1 完成对分类表的CRUD的操作
1.1.1需求：
使用cmd的方式创建一个数据库，创建数据库表，完成对数据库表中的记录的CRUD的操作.
1.1.2分析：
1.1.2.1技术分析：
【MYSQL的概述】
什么是数据库：
数据库就是一个文件系统，需要通过标准SQL语句才能访问.
常见的数据库：
MYSQL   ：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费.

Oracle  ：收费的大型数据库.Oracle公司的产品.Oracle收购SUN公司，收购MYSQL.

DB2     ：IBM公司的数据库产品,收费的.银行系统中.

SQLServer：MS公司.收费的中型的数据库.

SyBase  ：已经淡出历史舞台.提供了一个非常专业数据建模的工具PowerDesigner.

SQLite  : 嵌入式的小型数据库,应用在手机端.

Java相关的数据库：MYSQL，Oracle．
关系型数据库：
数据库中存的实体和实体之间的关系.
MYSQL数据库服务器

【MYSQL的安装和卸载】
安装和卸载的过程记录下服务器安装的路径和数据文件存放的路径.卸载的时候将这个两个路径下的文件一并都删除掉.
【MYSQL的SQL语句】
什么是SQL
SQL：结构化查询语言.
SQL的分类：
DDL:数据定义语言.
    create,alter,drop...
DML:数据操纵语言
    insert,delete,update
DCL:数据控制语言
    if,grant
DQL:数据查询语言.
    Select
1.1.3SQL的实现
1.1.3.1使用SQL完成对数据库的CRUD的操作：
【创建数据库】
语法:
    * create database 数据库名;
    * create database 数据库名 character set 字符集;
    * create database 数据库名 character set 字符集 collate 校对规则;

【查看数据库】
* 查看数据库服务器中的所有的数据库:show databases;
* 查看某个数据库的定义的信息:show create database 数据库名;
【删除数据库】
* drop database 数据库名称;
【修改数据库】
* alter database 数据库名 character set 字符集 collate 校对规则;
【其他的数据库操作命令】
* 切换数据库：use 数据库名;
* 查看正在使用的数据库:select database();
1.1.3.2使用SQL完成对数据库中表的CRUD的操作:
【创建表】
create table 表名(
   字段名 类型(长度) 约束,
   字段名 类型(长度) 约束
);

Java                MYSQL
int                 int
float               float
double              double
char/String         char/varchar(char固定长度字符串，varchar可变长度的字符串)
Date                date,time,datetime,timestamp
文件类型                BLOB、TEXT   TEXT指的是文本文件  BLOB二进制文件
     * Oracle的文件类型：BLOB  CLOB

约束：
单表约束：
    * 主键约束：primary key
    * 唯一约束：unique
    * 非空约束：not null

创建一个分类表：
    分类ID int类型主键 自动增长
    分类名称 字符串类型 长度20

create table category(
    cid int primary key auto_increment,
    cname varchar(20)
);

***** 注意：建表之前一定先选择数据库.
【查看表】
查看数据库中的所有表：show tables;
查看表结构：desc 表名;
【删除表】
drop table 表名;
【修改表】
alter table 表名 add 列名 类型(长度) 约束;        --修改表添加列. 
alter table 表名 modify 列名 类型(长度) 约束; --修改表修改列的类型长度及约束.
alter table 表名 change 旧列名 新列名 类型(长度) 约束;    --修改表修改列名.
alter table 表名 drop 列名;                 --修改表删除列.
rename table 表名 to 新表名;                 --修改表名
alter table 表名 character set 字符集;       --修改表的字符集
1.1.3.3使用SQL完成对表中记录的CRUD的操作：
【插入记录】
语法：
* insert into 表 (列名1,列名2,列名3..) values  (值1,值2,值3..); -- 向表中插入某些列
* insert into 表 values (值1,值2,值3..); --向表中插入所有列

注意：
* 1.列名数与values后面的值的个数相等
* 2.列的顺序与插入的值得顺序一致
* 3.列名的类型与插入的值要一致.
* 4.插入值得时候不能超过最大长度.
* 5.值如果是字符串或者日期需要加’’.

cmd下插入中文的乱码的解决：
* 修改mysql.ini文件.
    * 将[mysql]下面的字符集改为gbk
    * 重启mysql服务.services.msc
【修改记录】
语法：
* update 表名 set 字段名=值,字段名=值 [where 条件];

注意：
* 1.列名的类型与修改的值要一致.
* 2.修改值得时候不能超过最大长度.
* 3.值如果是字符串或者日期需要加’’.

【删除记录】
语法：
* delete from 表名 [where 条件];

面试：
删除表中所有记录使用delete from 表名; 还是用truncate table 表名;
* 删除方式：delete 一条一条删除. 而truncate 直接将表删除，重新建表.
* 事务控制DML,而delete属于DML.如果在一个事务中，delete数据，这些数据可以找回.truncate删除的数据找不回来.
【查询记录】
语法：
* select [distinct]*[列名,列名] from 表 [where 条件].

创建商品表：
创建商品表：
create table products(
    pid int primary key auto_increment,
    pname varchar(20),
    price double,
     pnum int,
     cno int,
    pdate timestamp
);

insert into products values (null,'泰国大榴莲',98,12,1,null);
insert into products values (null,'新疆大枣',38,123,1,null);
insert into products values (null,'新疆切糕',68,50,2,null);
insert into products values (null,'十三香',10,200,3,null);
insert into products values (null,'老干妈',20,180,3,null);
insert into products values (null,'豌豆黄',20,120,2,null);

简单查询:
     练习:
1.查询所有的商品
2.查询商品名和商品价格.   
3.查询所有商品都有那些价格.
4.将所有商品的价格+10元进行显示.(别名)


条件查询:
练习:
1.查询商品名称为十三香的商品所有信息：
2.查询商品价格>60元的所有的商品信息:
3.查询商品名称中包含”新”的商品
4.查询价格为38,68,98的商品

where后的条件写法：
    * > ,<,=,>=,<=,<>
    * like 使用占位符 _ 和 %  _代表一个字符 %代表任意个字符. 
        * select * from product where pname like '%新%';
    * in在某个范围中获得值.
        * select * from product where pid in (2,5,8);

排序查询:
1.查询所有的商品，按价格进行排序.(asc-升序,desc-降序)
2.查询名称有新的商品的信息并且按价格降序排序.


聚合函数:
* sum(),avg(),max(),min(),count();
1.获得所有商品的价格的总和：
2.获得商品表中价格的平均数：
3.获得商品表中有多少条记录：

分组：使用group by
1.根据cno字段分组，分组后统计商品的个数.
2.根据cno分组，分组统计每组商品的总数量，并且总数量> 200;
select cno,sum(pnum) t from products group by cno having t>200;   
1.2SQL会创建多表及多表的关系:
1.2.1需求:
分类表和商品表之间是不是有关系?如果有关系，在数据库中如何表示这种关系.
1.2.2分析：
1.2.2.1技术分析
【外键约束】
多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键约束.
alter table product add foreign key (cno) references category(cid);
【多表的关系】
一对多关系：
客户和订单，分类和商品，部门和员工.
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.
多对多关系：
学生和课程:
多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.
一对一关系：
在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.
主键对应：让一对一的双方的主键进行建立关系.

1.2.3网上商城案例实体抽取和表关系建立：


一对多的实现

###创建分类表（已存在）
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100)        #分类名称
);

# 商品表（已存在，删除，重新创建）
CREATE TABLE `products` (
  `pid` varchar(32) PRIMARY KEY  ,
  `name` VARCHAR(40) ,
  `price` DOUBLE 
);

#添加外键字段
alter table products add column category_id varchar(32);

#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);



多对多的实现
### 订单表
create table `orders`(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double   #总计
);

### 订单项表
create table orderitem(
  oid varchar(50),-- 订单id
  pid varchar(50)-- 商品id
);
### 联合主键（可省略）
alter table `orderitem` add primary key (oid,pid);

###---- 订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);

###---- 商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);


初始化数据
-- 给商品表初始化数据
insert into products(pid,name,price,category_id) values('p001','联想',5000,'c001');
insert into products(pid,name,price,category_id) values('p002','海尔',3000,'c001');
insert into products(pid,name,price,category_id) values('p003','雷神',5000,'c001');
insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');
insert into products(pid,name,price,category_id) values('p005','真维斯',200,'c002');
insert into products(pid,name,price,category_id) values('p006','花花公子',440,'c002');
insert into products(pid,name,price,category_id) values('p007','劲霸',2000,'c002');
insert into products(pid,name,price,category_id) values('p008','香奈儿',800,'c003');
insert into products(pid,name,price,category_id) values('p009','相宜本草',200,'c003');
insert into products(pid,name,price,category_id) values('p010','梅明子',200,null);


-- 给分类表初始化数据
insert into category values('c001','电器');
insert into category values('c002','服饰');
insert into category values('c003','化妆品');
insert into category values('c004','书籍');

1.3使用商城表关系完成多表的查询（*****）
1.3.1需求：
在商城的案例中很多涉及多表查询的功能，[我的订单]...
1.3.2分析：
1.3.2.1技术分析：
【多表查询】
交叉连接查询(基本不会使用-得到的是两个表的乘积)
语法：
* select * from A,B;
内连接查询(使用的关键字 inner join  -- inner可以省略)
* 隐式内连接：
    * select * from A,B where 条件;
* 显示内连接：
    * select * from A inner join B on 条件;
外连接查询(使用的关键字 outer join -- outer可以省略)
* 左外连接：left outer join
    * select * from A left outer join B on 条件;
* 右外连接：right outer join
    * select * from A right outer join B on 条件;

我们以一对多为例来练习一下
初始化数据:
-- 用户表(user) 
   create table `user` (                                  
          `id` int auto_increment primary key,                
          `username` varchar(50)  -- 用户姓名                                                
        );

-- 订单表(orders)
   create table `orders` (                                                  
          `id` int  auto_increment primary key,                                  
          `price` double,                                           
          `user_id` int                                       
        );
-- 给订单表添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id); 

-- 向user表中添加数据
        insert into user values(3,'张三');
        insert into user values(4,'李四');
        insert into user values(5,'王五');
        insert into user values(6,'赵六');

-- 向orders 表中插入数据
        insert into orders values(1,1314,3);
        insert into orders values(2,1314,3);
        insert into orders values(3,15,4);
        insert into orders values(4,315,5);
        insert into orders values(5,1014,null);


练习:
查询用户的订单,没有订单的用户不显示
查询所有用户的订单详情
查询所有订单的用户详情

【子查询】
在sql语言中，当一个查询是另一个查询的条件时，称之为子查询

练习:
查看用户为张三的订单详情
查询出订单的价格大于300的所有用户信息。
查询订单价格大于300的订单信息及相关用户的信息。

【分页查询】
分页查询每个数据库的语句是不通用的.
MYSQL:使用limit的关键字.
* select * from product limit a,b;  --a:从哪开始,b:查询多少条.
Oracle:使用SQL语句嵌套.
SQLServer:使用top的关键字.