个人java学习路线——mysql

sql语言分类

数据库操作语言大体有五类：
DQL(数据查询语言):查询语句，凡是select语句都是DQL。
DML(数据操作语言)：insert delete updata 对表中数据进行增删改
DDL(数据定义语言)：create drop alter 对表结构的增删改
TCL(事务控制语言)：commit提交事务 rollback 回滚事务
DCL(数据控制语言):grant 授权 ,revoke 撤销权限等

先说说数据库的建立基本操作

show databases;	--查看有哪些数据库
create database xxx;	--创建数据库
use xxx			--切换数据库
show tables;	--查询当前数据库有哪些表;
source (表地址);     --初始化表，导入表
drop database xxx;	--删除数据库
drop tables xxx;	--删除表
desc xxx;			--查看表结构
select * from xxx;	--查看表中数据
select database();	--查询当前数据库
select version();	--查看版本
\c    //cmd中写错了，用来结束一条语句

show create table xxx;	--查看xxx表的建立语句
distinct  			--去除重复记录

看看最重要的查询(DQL)

查询条件where

select 字段名1,字段名2,...form 表名;
select ename,sal*12 as yearsal from emp;
//select 字段名,月薪资*12 ，sal重命名为yearsal(中文加''     :'年薪')(as 可省)
//sql中字符串使用' '括起来
select ename from emp where sal=5000;
//从emp表中 当sal=5000 查询ename
//<>或!=不等于
//sal between.(a)..and.(b).在...和...之间 *a<b
//sal<> 2000 and sal<5000;sal不等于2000以及sal小于5000
// is null (where is null 为空)不能用=null;
where sal>1000 and deptno=20 or deptno=30;
//and 优先级大于or 所以是sal>1000 and deptno=20 然后才or   deptno=30
//所以where sal>1000 and (deptno=20 or deptno=30);
where job='SALESMAN' or job='MANAGER';//约等于下面
where job in('SALESMAN','MANAGER');
//in 后跟的是具体的值
//not in 不在
//  where xxx like '%O%'   //%任意多个字符      找到含O的字符串
//   where xxx like '_A% '   // _任意一个字符   	找到第二个字母为A的
//           "   \  "  转义字符依旧可以用

分组函数相关：

分组函数操作：
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
select sum(sal) from emp;
*分组 函数自动忽略null
*sql 中 有null参与的数学函数无论这么计算结果都是null
ifnull(可能为null的数据,处理方法);
select ename,ifnull(comm,0) from emp; 
//从emp搜索ename和comm数据，如果comm为null算成0
//分组函数不能直接出现在where后面
***因为group by在where之后执行，而分组函数又在group by之后执行
//group by:  按照某个字段或者某些字段进行分组
//having  : 对分组之后的数据进行再次过滤
select max(xx) from emp group by job;
//把emp按job先分组在筛选max(xx)
//分组函数一定在group by 之后执行 


--数字表示执行顺序
select	5
  ..
from 	1
  ..
where	2
  .. 
group by	3
  ..	
having	4
  ..
order by	6
  ..
;

语句联合

select max(xx),job from emp group by job;
select deptno,job,max(sal) from emp group by deptno,job;
//select max(sal),deptno from emp group by deptno having max(sal)>2900;	
select max(sal),deptno from emp where sal>2900 group by deptno;

说说表的连接方式-内连接

表链接方式：
内连接：
	等值链接:
	非等值链接：
	自链接：
外连接：
	左外连接：
	右外连接：
全连接：（略）


等值链接:
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;   	//sql 92语法  (现在不用)
// emp  e             emp表取别名e              
//e.ename            e表的ename列
sql 99:(常用)
  select
    e.ename,d.dname
  from
    emp e
  inner join
    dept d
  on
    e.deptno=d.deptno;
//后面仍可以加where
// inner可以省略，表示内连接

非等值链接：
mysql> select
    ->  e.ename,e.sal,s.grade
    -> from
    ->  emp e
    -> join
    ->  salgrade s
    -> on
    ->  e.sal between s.losal and s.hisal;

自链接：
mysql> select
    ->  e.empno,e.ename,m.ename
    -> from
    ->  emp e
    -> join
    ->  emp m
    -> on
    ->  e.mgr=m.empno;

说说表的连接方式-外连接

内连接：(有inner,可省)
select
  a.ename '员工', b.ename '领导'
from
  emp a
inner join
  emp b
on 
  a.mgr=b.empno;	

左外连接：（左连接）(有outer,可省)
select
  a.ename '员工', b.ename '领导'
from
  emp a
left outer join
  emp b
on 
  a.mgr=b.empno;
// a表为主，a中一定要查出来

右外连接：（右连接）
select
  a.ename '员工', b.ename '领导'
from
  emp b
right outer join
  emp a
on 
  a.mgr=b.empno;
// a表为主，a中一定要查出来

//三表连接
select
  e.ename,d.dname,s.grade,e2.ename
from
  emp e
join 
  dept d
on
  e.deptno=d.deptno 
join
  salgrade s
on
  e.sal between s.losal and s.hisal
left join 
  emp e2
on
  e.mgr=e2.empno;

子查询(套娃)

select 
  ..(select).
from
  ..(select).
where
  ..(select).

select avg(sal) from emp;   //加上下面的    +
select * from emp where sal >??; //等于下面的  =

select * from emp where sal >(select avg(sal) from emp);

limit

select ename,sal from emp order by sal desc limit 0,5;
//从第0条数据开始读取5条数据
select ename,sal from emp order by sal desc limit 5;
//读取前5条数据

建表语句(DDL)

建表

create table 表名(
  字段名1 数据类型,
  字段名1 数据类型,
  ...
);

drop table if exists t_student;  //如果存在t_student表，删除
create table t_student(
  no bigint,				//列属性   列名称 类型，
  name varchar(255),
  sex char(1) default 1,
  classno varchar(255),
  birth char(10)
);

//default  默认值

create table emp1 as select * from emp;   //复制表
create table emp1 as select ename,sal from emp; //复制部分表

insert into dept1 select * from dept;  //插入部分表
truncate table dept1;   //删除大数据量表 --（delete 删除数据表格还在，truncate 删除只剩表头）

插入值

insert into 表名(字段名1,字段名2,字段名3,...)values(值1,值2,值3,...);

insert into t_student(no,name,sex,classno,birth)values(1,'tom','1','1001','0001-01-01');
insert into t_student(no,name,sex,classno,birth)values(2,'jerry','2','1002','0001-01-01');

insert into t_student values(3,'jack','1','2001','0100-01-01');

insert into 表名(字段名1,字段名2,字段名3,...)values(值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);

修改和删除

updata 表名 set 字段值1=值1,字段值2=值2......where 条件
//没有条件表示整张表数据全部更新

update dept1 set loc='SHANGHAI',dname='RENSHIBU' where deptno=10;

delete from 表名 where 条件;  //无条件全部删除

约束

值的约束：

非空约束(not null)
唯一约束(unique)
主键约束(primary key)
外键约束(foreign key)
检查约束(check) //mysql 不支持

----------------unique ：

drop table if exists t_user;
create table t_user(
  id int,
  usercode varchar(255),
  username varchar(255),
  password varchar(255),
  unique(usercode,username)
);
insert into t_user(id,usercode,username,password) values(1,1,'yanyi','123');
insert into t_user(id,usercode,username,password) values(2,2,'yanyi','123');
//unique   不能重复可以为null【列级约束】
//unique(usercode,username)   联合不能重复【表级约束】

-------------------primary key ：
建完表后加约束–alter table newstudent add constraint pk_stuno primary key(studentno);

//primary 主键约束 不能重复，不能为null
//列级约束
drop table if exists t_user;
create table t_user(
  id int primary key, 
  username varchar(255),
  email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'lisi','lisi@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');

//insert into t_user(id,username,email) values(1,'tom','tom@123.com');  错误
//insert into t_user(username,email) values('tom','tom@123.com');  错误


根据主键字段的字段数量划分：
	单一主键
	复合主键
根据主键的性质划分：
	自然主键
	业务主键（不推荐，最好不要拿跟业务挂钩的作主键）
一张表的主键只能有一个

表级约束：
drop table if exists t_user;
create table t_user(
  id int, 
  username varchar(255),
  primary key(id)
);
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'lisi');
insert into t_user(id,username) values(3,'ww');


主键值自增：（mysql）*****
//Oracle 中也提供了一个自增机制叫:序列（sequence）
//auto_increment    自增
drop table if exists t_user;
create table t_user(
  id int primary key auto_increment,
  username varchar(255)
);
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
select * from t_user;

-------------------foreign key：
建完表后加约束-- alter table 外键表表名 add constraint fk_外键约束的约束名 foreign key(外键表外键列) references 主表(主键列)

外键约束：foreign key 
外键字段：添加啊有外键约束的字段
外键值：外键字段中的每一个值

举例介绍
t_class班级表
cno(pk)       cname
------------------------------------------
101	    xx高三(1)班
102	    xx高三(2)班

t_student 学生表
sno(pk)	sname	classno(外键约束pk)
-----------------------------------------------------
1	zs1	101
2	zs2	101
3	zs3	102
4	zs4	102
5	zs5	102
// 外键约束pk值不能为超过cno的值（只能为101，102），但可以为null
//外键约束的引用：一般引用主键，必须要有唯一性
t_student 为t_class 的子表 ， t_class 为父表
删表先删子表


drop table if exists t_student;
drop table if exists t_class;
create table t_class(
  cno int,
  cname varchar(255),
  primary key(cno)
);
create table t_student(
  sno int,
  sname varchar(255),
  classno int,
  primary key(sno),
  foreign key(classno) references t_class(cno)
);

引擎

创建表时，没有指定引擎默认：
ENGINE=InnoDB DEFAULT CHARSET=utf8

查看当前mysql支持的存储引擎：
show engines \G

myism是mysql最常用的引擎

事务

介绍

transaction 事务----完整的业务逻辑单元

commit 提交事务
rollback 回滚事务

业务的特性：
四大特性：ACID
A: 原子性
C: 一致性
I : 隔离性
D: 持久性

事务隔离性的隔离级别：
第一级别：读未提交(read uncommitted)
A事务未提交，B当前事务已经可以读取
脏读（Dirty Read）:读到脏的数据
第二级别：读已提交(read committed)
A事务提交，B才可以读取
但数据不可重复读
第三级别：可重复读(repeatable read)
可以重复读取数据，但读到的是幻象
第四级别：序列化读/串行化读
效率低，需要事务排队
oracle 默认读已提交
mysql 默认可重复读

演示

关闭自动提交   ：start transaction;
drop table if exists t_user;
create table t_user(
  id int primary key auto_increment,
  username varchar(255)
);

insert into t_user(username) values('zs');
select * from t_user;
rollback;
select * from t_user;
//mysql 中事务自动提交

start transaction;
//关闭自动提交

insert into t_user(username) values('ls');
select * from t_user;
rollback;
select * from t_user;
//关闭回滚后事务不会自动结束，不能读取'ls'数据

insert into t_user(username) values('ww');
commit;
select * from t_user;
rollback;
select * from t_user;
//commit;  提交事务

查看事务隔离级别

set global transaction isolation level read uncommitted;
//设置全局事务隔离级别为read uncommitted

select @@global.tx_isolation;
//查看全局事务隔级别

索引

索引底层数据结构采用 ： B + tree
用法：

explain select ename,sal from emp where sal=5000;
//查看搜索情况

create index emp_sal_index on emp(sal);
//创建索引

drop index emp_sal_index on emp;
//删除索引

视图view

视图不是数据库中的真实表 而是一张虚拟表，其结构和数据是建立在对真实表的查询基础上的

create view myview as select empno,ename from emp;
drop view myview;
//创建/删除视图

****对视图进行增删改查会改变原表数据
select * from myview;
//查看视图

----------------------------------------------------
create view myview1 as select empno,ename,sal from emp2;

update myview1 set ename='hehe',sal=1 where empno=7369;

delete from myview1 where empno=7369;

-------------------------------------------------------

create view myview2 as select empno a,ename b,sal c from emp2;
select * from myview2;

设计表的三范式

按照三范式设计的表不会出现数据冗余

一对一两种方法：主键共享和外键唯一
多对多三张表对应二范式
一对多两张表对应三范式

三范式依次递进：
第一范式：任何一张表都应该有主键，并且每一个字段原则性不可再分。
第二范式：所有非主键字段必须完全依赖主键，不能出现部分依赖。（只要没有复合主键就基本没问题）
第三范式：所有非主键字段直接依赖主键字段，不能产生传递依赖。 

一范式举例：
第一种方法：主键共享
t_user_login
id(pk)	username		password
--------------------------------------------------
1	zs		123
2	ls		456

t_user_detail
id(pk+fk)*****	realname		tel .....
----------------------------------------------------
1		张三		121312
2		李四		213153

第二种方法：外键唯一
t_user_login
id(pk)	username		password
--------------------------------------------------
1	zs		123
2	ls		456

t_user_detail
id(pk)	realname	   tel 	    userid(fk+unique)****.....
----------------------------------------------------
1	张三	   121312	     1
2	李四	   213153	     2


二范式举例：
t_student
sno(pk)		sname
--------------------------------------
1		张三
2		李四
3		王五

t_teacher
tno(pk)		tname
---------------------------------------
1		王老师
2		李老师
3		刘老师


t_student_teacher_relation
id(pk)	sno(fk)	tno(fk)
---------------------------------
1	1	1
2	2	1
3	3	2
4	4	3


三范式举例：
classno和cname不能在一张表中
t_class
cno(pk)		cname
----------------------------------
1		a
2		b
3		c
4		d

t_student
sno(pk)		sname		classno(fk)
--------------------------------------------------------
1		张三		1
2		李四		2
3		王五		3
4		赵六		4

其它(尤其是关于cmd的)

查看mysql编码：
SHOW VARIABLES LIKE 'character%';

建表建库指定字符集：
1、建库（注意标明utf8）：
create database test charset utf8;          
2、建表（注意标明utf8）：
    create table C
         ( CNO char(5) not null primary key, 
          CN varchar(20), 
          CT int check(CT>=1) 
          )charset utf8;           

重新导入ini文件,(经常出错)
mysqld install MySQL --defaults-file="xxx/mysql.ini"


导入导出数据：
数据库内数据导出：
	导入指定库：
mysqldump mydatabase1>桌面\mydatabse1.sql -uroot -p????
	导入指定表：
mysqldump mydatabase1 emp>桌面\mydatabse1.sql -uroot -p???

导入：
create databse mydatabase1;
use mydatabase1
source ??
//??托文件以输入路径