MYSQL总结

MySQL简介

MySQL属于小型关系型数据库。大型有Oracle、DB2更稳定。 MySQL包含很多库、库中包含表，库可以被多个不同用户访问。
Oracle包含库、库中包含很多对象，有表，序列，索引，函数，过程。用户创建表自己访问。

拥有由于其体积小、速度快、总体拥有成本低，开放源码的特点

MySQL和Oracle 使用上区别
1.主键自增方式不同、

Oracle：表有自动递增使用序列sequence
MySQL：表有自动递增支持auto_increment

2.单引号的处理
3.创建时间函数不同，

Oracle: Sysdate
Mysql: now()

5.长字符串的处理不同
6.分页函数不同

Oracle：采用子查询+ROWNUM列实现
MySQL：采用limit ?,?

Mysql和oracle相同之处

建表语句相同、创建约束语句相同、增删改除相同

建表
建表语句相同
create table xxx( 字段名 类型, 字段名 类型 )

Oracle常用类型：NUMBER、VARCHAR2、DATE、CLOB、BLOB、TIMESTAMP

MySQL常用类型：int、long、double、float、varchar、date、time、datetime、timestamp、clob、blob

创建约束语句相同 alter table xxx add constraint primary key(xx)

主键值自增方式不同

Oracle:创建序列sequence，然后利用sequence.nextval做主键ID值，实现递增效果
create sequence user_seq;//默认从1开始递增加1
insert into USER(ID,NAME) values (user_seq.nextval,?)
MySQL：表有自动递增支持auto_increment create table xx(
ID int primary key auto_increment, NAME varchar(20)
)
insert into USER(NAME) values (?)

DML操作（增删改）

insert into 表 (字段1,字段2...) values (?,?)
update 表 set 字段1=?,字段2=? where 字段=?
delete from 表 where 字段=?

上述标准语句相同。

INSERT INTO users(name, age) VALUES(‘姚明’, 25), (‘比尔.盖茨’, 50), (‘火星人’, 600);
Oracle: SYSDATE、ROWNUM
MySQL： now()

DQL查询操作

select 字段… from 表 where 条件 group by 分组 having 组函数条件（将查询的结果当做条件） order by 排序

上述标准语句相同

join…on…相同
组函数max，count，min，avg，sum相同

注意：分页查询不同

Oracle：采用子查询+ROWNUM列实现
MySQL：采用limit ?,?

select * from dept limit 0,5

事务控制 提交不同

Oracle:默认关闭自动提交。执行一个DML后，需要commit一下
MySQL:自动提交，执行一个DML就修改数据库。

Mysql优点：

1.可移植性
2.支持多种操作系统
3.为多种编程语言提供了 API
4.支持多线程
5.优化sql查询算法
6.可单独、也可嵌入式的使用
7.提供TCP/IP、ODBC和JDBC等多种数据库连接途径
8.MySQL是开源的
9.支持大型的数据库

JDBC：java数据库连接
MySQL操作

mysql环境变量配置cmd命令进入
在计算机属性下配置环境变量path到D：mysql\bin下
进入cmd 输入

 如：mysql  -uroot -p101010
-u-----用户名
-p------密码

1. 选择库

show databases // 查看所有库
use 库名  //进入（使用）某个库
create database 库名//创建库
drop database 库名//删除库 

进入use 库后才可以进行表操作

对字段的修改

增加字段名 alter table 表名 add ename varchar();
删除字段名 alter table 表名 drop column ename
修改字段名 alter table 表名 rename column 旧字段名 to 新字段名;
修改字段类型 alter table 表名 modify 字段名 数据类型；
alter table 表名 modify ename varchar(20);

查看表

show tables;//查看库中所有表名
desc 表名;//查看表结构

删除表 drop table 表名;
创建表

create table x_user(
id int primary key auto_increment,//自动提交
name varchar(20),
age int
);

修改表

alter table x_user add (birth date);

操作表数据

insert into x_user (name,age,birth) values ('james',25,'2013-06-05');
update x_user set age=22 where id=1;
delete from x_user where id=6;
select * from x_user where name like '%o%';

分页查询

select * from x_user limit 抓取记录起点,抓取最大记录数；
select * from x_user limit 3;//查询前3条，等价limit 0,3
select * from x_user limit 0,3；//查询前3条
select * from x_user limit 3,3；//从第4条抓，最多取3个

now() 创建当前时间

insert into x_user (name,age,birth) values ('xdl',10,now());
select count(*) from x_user; -----统计数量
select max(age) from x_user;-------查找指定字段最大值

Java访问MySQL
修改连接参数改变即可

url 改为 jdbc:mysql://localhost:3306/库名
driverClassName 改为 com.mysql.jdbc.Driver
username 改为 root
password 改为 自己密码

---

查询SQL语句编写

select ... from ... where ... group by ... having ... order by ...

1. from字句

两个表直接关联 from A join B on(A.xx=B.xx)
两个表间接关联 from A join C on (A.xx=C.xx) join B on (C.xx=B.xx)

内连接

join… on…、inner join… on… (查询出两个表对等记录，最终记录是由双方表决定)

外连接

left outer join... on...、right outer join... on...、 full outer join... on...
（查询某个表记录，另一个表没有对等，补null值。最终记录是由某一方表决定）
//以A表记录为准全部取出，B没有对等补null
from A left outer join B on(A.xx==B.xx)
from B right outer join A on(A.xx==B.xx)
//以B表记录为准全部取出，A没有对等补null
from A right outer join B on(A.xx=B.xx)

2. where 字句

编写检索条件，条件字段一定出现在from数据表中，如果没有，from部分还需要加表。(注意不要出现分组函数,例如max,min等)
条件：>、<、>=、<=、!=、is null、is not null等 （参考搜索界面的输入条件）

3. group by字句
分组统计，可以所有记录一组、可以按某个字段或多个字段相同的做一组

group by deptno
group by sex,city

有时可以先将group by放过，等select确定后再决定是否追加group by

select c1,c2,max(c3) //必须追加 group by c1,c2
select max(c3)//可以不追加group by字句

4. having字句
使用分组函数做检索条件，过滤记录
5. select字句
根据页面显示项目决定。
6. order by字句
根据想要的顺序排序。
SQL语句级联删除
修改一下外键，下面是个例子：

alter table 表名
add constraint 外键名（emp_字段名_fk）
foreign key(字段名) references 主表名(主表id)
on delete cascade

例:
前加级联删除

create table(
id number,
dept_id number constraint emp_dept_id_fk foreign key(dept_id) reference dept(id)on delete casecode
);

后加级联删除

alter table add emp  constraint emp_dept_id_fk foreign key(dept_id) reference dept(id) on delete casecode

注释：

column:列名
referenced_table_name:外键参考的主键表名称
ref_name:外键要参考的表的主键列 on
delete:删除级联

数据库优化：

1：数据库设计—-表结构设计、字段规范设计、表引擎设计、索引规范(id，外键、查询比较频繁的字段、时间字段)
2：sql语句层次的优化—4张表、select * from XXXXX、select username from user left join role on role.roleid = user.roleid

避免写循环、(批量操作功能—-id用逗号隔开—1,2,3,4
select userid from user where userid in(1,2,3,4)，is not null \ nvl())
分页查询(10条数据，count(0))
查数据不为空is not null
count（0） 0或1 不写*

mysql：
分库：
场景：

1：第三方平台数据库
2：涉及到分布式
3：数据量特别特别大
4：业务，按照访问曝光度（常用和不常用的访问）

解决：

1：跨库查询—多数据源—id

分表：
场景：

1：常用的字段和不常用字段，进行分表—主子表
2：根据数据量—-日志表—分表（除了表名外，其它表结构一模一样）

解决：

1：主外键
2：根据数据量—-分表
a：根据表的容量来分表—配置mysql的触发器
b：根据时间来分表(常用) —

按照时间来分表

1：一个月一分表
2：tabname_201709
tablname_201708
3：监听器—-监听的是当前服务器的时间—每个月20-24号之间，监听到符合条件的时间后，自动复制一张表，目的是提前创建
4：查询：
1：给定限制条件—-日期时间段
2：后台根据时间段进行截取，创建动态sql
3：分页，并且按照时间排序