MySql 进阶

1.SQL 分类

DDL:数据定义
create，alter，drop。。。
DML：数据操纵
update，insert，delete
DCL：数据控制
grant，if
DQL：数据查询
select

1.1SQL 特征

非过程性；一条语句就会有一个运行的结果。

2.对数据库的CRUD操作

【创建数据库】
语法：
create database数据库的名称（character Set 字符集 collate 字符集的校对规则）；
eg。create database testdb2 character set utf8 collate utf8_bin;
【查看数据库】
查看所有数据库：

show databases；
查看数据库的定义信息：
show create database testdb1；

mysql> show create database testdb1;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| testdb1  | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------------+

查看当前正在使用的数据库信息
>select databases();

【删除数据库】
drop database 数据库名称；
【修改数据库】：修改数据库修改的是数据库的字符集和校对规则。
alter database 数据库名 character Set新字符集 collate 校对规则；
【切换数据库】
use 数据库名称；

2.1对数据库的表的CRUD操作

【创建表】
create table 表名{
字段名 类型（长度） 约束，
字段名 类型（长度） 约束，
}；

数据类型：
JAVA类型       Mysql类型
int           int
String        char(固定)/varchar（不固定长度）
float         float
double        double
Date          date/time/datetime/timeatamp
    *datetime和timeStamp都有既有日期又有时间的日期类型
    *区别？datetime需要使用外部引入的日期，如果没传这个值就是null，timestamp会使用系统的当前时间的作为这个值得默认值。
    MySql中字符串类型需要设置长度其他的都是默认长度。
二进制文件 BLOB
***Oracle使用CLOB/BLOB

约束：
*主键约束：primary key
*唯一约束：unique
*非空约束：not nullc

创建一个表
create table employee(
eid int primary key auto_increment,
ename varchar(20) not null,
Email varchar(30) unique,
birthday date,
job varchar(20)
);
表的查看
查看数据库中有哪些表：show tables；
查看表的结构： des 表名；
表的删除：drop table 表名；
表的修改
修改表添加列：alter table 表名 add 列名 类型（长度） 约束；
修改表删除列：alter table 表名 drop 列名
修改表的列的类型长度及约束：alter table 表名 modify 列名 类型（长度） 约束；
修改表的列名：alter table 表名 change 旧列名 新列名 类型（长度） 约束；
修改表名：rename table 旧表名 to 新表名；
修改表的字符集：alter table 表名 character Set 字符集；

2.2对表的记录的CRUD的操作

【插入记录】
*insert into 表名（列名，列名，。。。） values () ;
*insert into 表名 values（）；
【注意事项】
*列名的个数与值得个数对应。
*列的类型与值得类型对应，位置也要对应。
*列的类型如果是字符串或者日期，写值得时候使用单引号将值引起来。
*插入的值得最大长度不能超过列的最大长度。
【插入记录】
*插入某几列的值：
*INSERT INTO emplyee (eid,ename,email) VALUES(NULL,’aaa’,’aaa@itcast.cn’);
*插入所有列的值:
【插入中文】
*解决方法：
*show variables like “%character%”
*解决插入乱码的问题，找到Mysql的安装路径，my.ini文件（mysql的client和server的字符集不同，导致了乱码）
[client] port = 3306;
[mysq] default-character-set=gbk
*重新加载mysql的设置文件
service.msc
停止mysql的服务，重新启动mysql服务
执行之前的mysql语句
【修改记录】
*Update 表 Set 列名=值，列名=值 [where 条件]；
注意事项：
列名和值类型要一致
值不能超过列的最大长度
值是字符串或字符串，需要用单引号
【删除记录】
delete from 表 【where 条件】
**删除表中的所有记录，truncate table 表 和delete table 表 的区别
**truncate 删除表的记录：将整个表删除，重新创建一个新的表，truncate属于DDL语言。
**delete 删除表的记录：一条一条的删除，事务管理只能作用在DML语句上，如果在一个事务中使用delete删除记录，可以通过事务回滚。

【查询记录】
select * form 表 where 条件
*distinct ，去重
* 使用别名as，as可以省略
*

CREATE TABLE exam(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    english INT,
    chinese INT,
    math INT
);

INSERT INTO exam(id,NAME,english,chinese,math) VALUES(NULL,'tommy',76,87,99);
INSERT INTO exam(id,NAME,english,chinese,math) VALUES(NULL,'hans',67,77,69);
INSERT INTO exam(id,NAME,english,chinese,math) VALUES(NULL,'allen',56,67,79);
INSERT INTO exam(id,NAME,english,chinese,math) VALUES(NULL,'keyu',65,77,99);
INSERT INTO exam(id,NAME,english,chinese,math) VALUES(NULL,'lily',36,57,69);
INSERT INTO exam(id,NAME,english,chinese,math) VALUES(NULL,'shelly',86,84,65);

SELECT * FROM exam;

SELECT DISTINCT english FROM exam;//去重
SELECT NAME, english+chinese+math AS SUM FROM exam;//起别名

where语句后面可以加：
=，>,>=,<,<=,<>
like中可以使用占位符“_”and “%” 下划线匹配一个字符，%可以匹配任意多个。
in后跟着一组值：id in {1,2,3}
And
or
条件查询
ordered by :对数据进行排序，默认为升序 -》SELECT * FROM exam ORDER BY chinese desc/ASC;
查询学生信息，按照英语成绩降序，如果英语成绩相同，按照语文降序。
SELECT * FROM exam ORDER BY english DESC;
SELECT * FROM exam ORDER BY english DESC, chinese DESC;
【聚合函数】
sum()
count()
max()
min()
avg()
*查询每个学生总成绩；
【分组】group by
创建一个订单详细的表：
*统计订单的每类商品的所购买的个数：
SELECT product,COUNT(*) FROM orderItem GROUP BY product;
*统计订单中每类商品所花的金额
SELECT product,SUM（price） FROM orderItem GROUP BY product;
SELECT product,SUM(price) FROM orderItem WHERE product LIKE ‘W%’ GROUP BY product HAVING SUM(price) > 2000 ORDER BY SUM(price) DESC;
【SQL查询语言总结】
顺序：select from where group having order。。。

3.案例一，多表案例

3.1 多表的创建和关系设置

3.2 分析

【数据库的多表设计】
实体的关系总结：
一对一：

唯一外键对应：假设一对一的双方是一对多的关系，在多的一方创建指向一对一的方的主键，需要在外键上添加一个unique的约束
主键对应：将一对一的双方主键建立对应。
一对多：建立表的原则
->在多的一方建立一个字段，这个字段作为外键指向一方的主键。
多对多：
创建一个第三种表，中间表至少需要两个字段，分别作为外键指向多对多双方的表。
【创建一对多的关系】
创建客户表
create table customer(
cid int primary key auto_increment,
cname varchar(20)
);

 create table orders(
    oid int primary key auto_increment,
    addr varchar(50),
    cid int
);

*约束：保证数据的完整性
**多表的约束：外键约束！
**给Orders中的cid添加外键。

建表的时候添加。
修改表的语句。
alter table orders add foreign key (cid) references customer (cid).

3.3使用SQL完成多表查询

【多表查询的SQL】
*交叉连接：
select from A,B — 获得两个表的笛卡尔积
*内连接 Inner join： — inner 可以省略
显示内连接：select from A inner join B on 条件
隐式内连接：select from A，B where 条件
SELECT * FROM customers c,orders o WHERE c.cid = o.cid;
*外连接 Outer join: — outer 可以省略
左外连接：left outer join – select from A left outer join B on 条件；
select from customer c left outer join orders o on c.cid = o.cid;
右外连接： right outer join – select from A right outer join B on 调教；
SELECT FROM customers c RIGHT OUTER JOIN orders o ON c.cid = o.oid;
【多表查询的子查询】
一个SQL语句查询的过程中需要依赖另一个查询语句
SELECT * FROM customers c,orders o WHERE c.cid IN(SELECT cid FROM orders WHERE addr LIKE ‘%海%’);
【SQL】ANY和All关键字
按照客户名统计订单的个数
select c.cname，count（*） from customer c, orders o where c.cid = o.cid group by c.cname;