mysql基本操作2

##DDL控制表结构，不支持事务
##DML控制表数据，支持事务
DQL专门做查询
##TCL 管理事务
##DCL 管理数据库权限

##ORDER BY 子句
-根据指定列对结果集进行排序
-默认升序
-asc升序，desc 降序
order by 语句必须写在where语句之后

select title,price,category_id from t_item where title like '%DELL%' order by category_id asc,price desc;
select price,category_id from t_item order by category_id asc,price desc;

##limit 子句--分页

-limit begin ,size 两个数字逗号分隔
-begin 本页数据的起始行，从0开始（从第几条数据开始）
-size 本页显示几行

select price from t_item order by price limit 0,5;

#数据库函数

很多函数功能与java相关的api类似，但是，除了几种特殊的需求外，我们开发时，尽量不要在数据库进行函数计算

##CONCAT()函数拼接字符串
concat(str1,str2....)
-如果任何一个str是null，返回值就是null

select concat('今天周五了','下周讲完数据库') as 诡异 from dual;
        dual  任何数据库都存在，是一张假表
 select 'helloword' from dual;
 select concat (ENAME,MGR) from EMP;
select concat (title,price,'元') from t_item;

##数值运算

-运算符 + - * /   %    mod()取模
-弱数据类型字符串与数字进行计算 ‘10’-5
-取余 7%2 等同于 mod(7,2)
select price 单价,num 库存,price*num aaa from t_item order by aaa;
##DATE_FORMAT() 日期函数
-now() 返回当前的日期和时间
-curdate() 返回当前的日期
-curtime() 返回当前的时间
-date(时间日期的表达式) 提取这个表达式日期的部分
-time(时间日期表达式)提取这个表达式时间的部分
-extract(时间日期表达式) 提取这个表达式单独的一部分   （年月日时分秒）

select now() from dual; 
select curdate();
select date(now());
select curdate();
mysql> select curdate() from dual;|
+------------+
| 2018-01-26 |
+------------+
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2018-01-26  |
+-------------+
select extract(year from now());
+--------------------------+
| extract(year from now()) |
+--------------------------+
|                     2018 |
+--------------------------+

##date_format()
-以不同的格式显示时间/日期数据
-语法 date_format(日期表达式,'format');

-format
--%c 月 1
--%d 月的天 (00-31)
--%H 小时 (00-23)
--%h 小时（01-12）
--%i 分钟
--%m 月 01
--%S 秒
--%Y 年
--%y 两位数的年

例子：select date_format(now(),'%c--%m');
select date_format(HIREdate,'%Y年%m月%d日') from EMP;
select date_format(now(),'%H时%i分%S秒');
select date_format(created_time,'%Y:%m:%d')from t_item;

##str_to_date 把字符串转换成日期格式
-把字符串格式转换成时间格式，第一个参数为字符串，第二个参数为与第一个参数一样的format字符。格式不同结果为null。

select str_to_date('08/09/2008','%m/%d/%Y');
select str_to_date('08/09/08','%m/%d/%y');
select str_to_date('08.09.08','%m.%d.%y');
select str_to_date("08.07.2008 08:00:00","%m.%d.%Y %h:%i:%S");

## IFNULL()的函数
-空值处理函数
-ifnull(expr1,expr2) 如果expr1不是null，ifnull返回expr1，如果是null，返回expr2

select ifnull(COMM,0) from EMP;
##聚合函数
-对多行数据进行合并并统计
-使用聚合函数要注意：聚合函数一般只有一行结果，如果其他要查询的列。有多行结果，那么只会显示一行，其他结果被舍弃，原因是数据库不支持行合并。
-不要把聚合函数和正常的列放在统一个dql语句中，除非普通列只有一条数据
###sum():返回列的总数或总额

select SAL,COMM,sum(SAL) from EMP;
--------+------+----------+
| SAL    | COMM | sum(SAL) |
+--------+------+----------+
| 800.00 | NULL | 24925.00 |
+--------+------+----------+
select sum(SAL) from EMP;
----------+
| sum(SAL) |
+----------+
| 24925.00 |
+----------+

###avg():返回数值的平均值，但是null不包含在计算中
select sum(SAL),avg(SAL) from EMP;
###count():返回指定列的总数目，null不计数
select count(COMM) from EMP;
--使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空
值( NULL )还是非空值。

###max():这一列的最大值 null不计算
####可以对时间比较
select max(HIREdate) from EMP ;

###min():这一列的最小值 null不计算

#字符串的函数

-char_length() 字符数
select char_length(ENAME) from EMP;
-instr('abcdef','bcd)  返回第二个字符串在第一个字符串的位置从1开始  不存在返回0
例如：select ENAME from EMP where instr(ENAME,"K")!=1;
select ENAME from EMP where instr(ENAME,"A")=0;
-locate('abc','abc---abc---abc---abc') 返回第一个字串在第二个字符串的位置，从1开始  不存在返回0
-insert('aifewfehfff',2,5,'---') 用子串取代第一个字符串的位置   ,从第一个字符串的第2个字符开始用---取代5个长度
-lower() 转换成小写
select lower(ENAME) from EMP;
-upper() 转换成大写
-left("safdfw",3) 返回左边3个字符
select left("safdfw",3);
-right('ihdwif',4) 返回右边4个字符
-trim(' dsf g gd ') 去除两边的空格
-substring('ewfuifhu',3) 从3开始截取
-substring('ewfuifgdghu',3,5) 从3开始截取5个字符
-repeat('ahsdff',3)  重复3遍
-replace('hello mysql','my','your');
-reverse()-反转字符串
例如：查询倒数的第2个字符是‘E’的员工的姓名：select ENAME from EMP where ENAME like ('%E_');
例如：：查询emp表中员工的倒数第3个字符是： select subString(right(ENAME,3),1,1) from EMP;
select substring(ENAME,-3,1) from EMP;

#数学相关函数
-floor() 向下取整
-round() 四舍五入
-round(24.324,2) 四舍五入取小数位2位
-round(232.3123,-2) 小数点往前走两位四舍五入 200
-truncate(234234.234,4) 保留小数点后几位不四舍五入

select truncate(234234.234,4);
+------------------------+
| truncate(234234.234,4) |
+------------------------+
|            234234.2340 |
+------------------------+

#分组
###分组查询通常和聚合函数一起使用,
--一般情况下，查询的字段中出现聚合函数和普通列，一起查询的时候，分组的条件就是普通列
--select语句中含有聚合函数时，凡是不在聚合函数中的其他单独字段，都必须出现在group by语句中
--group by 语句要写在order by之前，where之后
--group by可以根据多个字段进行分组

select avg(SAL),DEPTNO from EMP group by DEPTNO;
查询每个领导有多少个员工:select MGR,count(*) from EMP group by MGR;
 select DEPTNO,count(*),sum(sal) from EMP group by DEPTNO order by count(*),sum(SAL) desc;
  select DEPTNO,JOB,avg(SAL) from EMP group by DEPTNO,JOB;
 select * from EMP where EMPNO in(7369,7521);
 每个部门，每个主管手下的人数：select count(*),DEPTNO,MGR from EMP group by DEPTNO,MGR;
 select count(*),extract(year from HIREdate) as year from EMP group by year;

##有条件的分组统计
###HAVING 字句
select avg(SAL),DEPTNO
from EMP
group by DEPTNO
having avg(SAL)>2000;
##查询顺序
1.form找到数据源
2.where根据条件进行筛选，确定条目数量
3.group by将数据划分多个组
4.聚合函数运算
5.having字句筛选分组条件
6.计算所有表达式
7.确定select后的字段
8.使用 order by 排序
---group by使用having过滤条件
---在sql语句中添加having字句的原因，是因为where关键字无法与集合函数一起使用。
-where条件用于过滤行数，having条件用于过滤分组的数量
-执行顺序，首先执行where语句，然后执行group by，根据单个或多个列进行分组，之后执行having。对分组以后的数据再次过滤。最后执行order by。

select sum(num),category_id
from t_item 
group by category_id
having sum(num)>1000;
select avg(price),category_id
from t_item
group by category_id
having avg(price)<=100;
select JOB,count(*),avg(SAL),max(SAL)
from EMP
where DEPTNO in(10,30)
group by JOB
having max(SAL)<5000
order by count(*),max(SAL) desc;
select DEPTNO,count(*),sum(SAL),max(SAL),min(SAL)
from EMP
group by DEPTNO
having max(SAL)!=5000
order by count(*),max(SAL) desc;
select DEPTNO,sum(SAL),avg(SAL) 
from EMP
where SAL between 1000 and 3000
group by DEPTNO
having avg(SAL)>=2000
order by avg(SAL);
select JOB,count(*),sum(SAL),max(SAL)
from EMP
where JOB not like "S%" and SAL!=3000
group by JOB
order by count(*),sum(SAL) desc;
select JOB,count(*),avg(SAL),min(SAL)
from EMP
group by JOB
having avg(SAL)!=3000
order by count(*) desc,avg(SAL) asc;
 
select avg(price),category_id
from t_item
where category_id in(238,917)
group by category_id;
select avg(price),category_id
from t_item
group by category_id
having category_id in(238,917);

####，having字句经常和聚合函数一起使用，如果没有聚合函数，要注意是否可以写在where语句中，如果可以，优先使用where语句

select avg(SAL),DEPTNO,count(*)
from EMP
group by DEPTNO
having avg(SAL)>2000
order by avg(SAL);
select DEPTNO,sum(SAL),avg(SAL),min(SAL)
from EMP
where ENAME not like 'K%'
group by DEPTNO
having min(SAL)>1000
order by avg(SAL) asc;

#子查询
##MySql子查询
###子查询是指，在DML语句，嵌套了另外一个查询（DQL）语句
###某些DDL也可以使用子查询
###子查询语句称为内部语句，而包含子查询的查询语句称为外部查询
###常用的子查询会出现两种情况
-------外部查询的where字句使用子查询
-------子查询在from后面，用子查询的结果充当一张表
###子查询可以在表达式的任何地方使用，但必须在括号中关闭
###子查询可以嵌套在另外一个子查询中

select * from EMP where SAL=(select min(SAL) from EMP);
select * from EMP where SAL>(select avg(SAL) from EMP);
select * from EMP where HIREdate=(select max(HIREdate) from EMP);
select * from t_item_category where id in(select distinct category_id from t_item);
select SAL from EMP where SAL>(select max(SAL) from EMP where DEPTNO=20);

select DEPTNO,DNAME from Dept where DEPTNO=(select DEPTNO from EMP where ENAME='KING');
select EMPNO,ENAME,JOB,DEPTNO from EMP where DEPTNO=(select DEPTNO from Dept where DNAME='SALES');
##活用in
select * from EMP where DEPTNO in(select DEPTNO from Dept where LOC='DALLAS');

select * from EMP where JOB=(select JOB from EMP where ENAME='JONES');
select * from EMP where JOB=(select JOB from EMP where ENAME='JONES') and ENAME<>'JONES';

##关联查询数据
###从多张表中查询相应记录信息
###关联查询的重点在于这些表记录的对应关系，这个关系称为连接条件（关联条件）
select e.ENAME,d.DNAME from EMP e,Dept d where e.DEPTNO=d.DEPTNO;
### 避免笛卡尔积
####当多表关联时，没有关联条件，返回的结果集是这几张表条目数的乘积，这个乘积叫做笛卡尔积
--多数情况下笛卡尔积是无意义的，非常耗资源，要尽量避免

select e.ENAME,d.LOC from EMP e,Dept d where e.DEPTNO=d.DEPTNO and d.LOC='NEW YORK';

select ENAME,SAL,DNAME,LOC from EMP e,Dept d where SAL>3000 and e.DEPTNO=d.DEPTNO;

##等值连接，内连接
###语法select * from A,B where A.某字段=B.某字段
###语法select * from A join B on A.某字段=B.某字段
###完整语法select * from A inner join B on A.某字段=B.某字段
####不满足连接条件的记录是不会在关联查询中被查询出来对

select e.ENAME,e.SAL,d.DNAME,d.LOC 
from EMP e join Dept d 
on e.DEPTNO=d.DEPTNO
where e.SAL>3000; 
select e.ENAME,d.LOC
from EMP e join Dept d
on e.DEPTNO=d.DEPTNO
where d.LOC='NEW YORK';

##左外连接
--以join左侧表作为基准（驱动表--所有数据都会被显示出来，不管是否和连接条件），那么当该表中的某条记录不满足连接条件时，来自右表的字段全部为null

select e.ENAME,e.SAL,d.DNAME,d.LOC 
from Dept d left join EMP e
on e.DEPTNO=d.DEPTNO

##右外连接

select e.ENAME,e.SAL,d.DNAME,d.LOC 
from EMP e right join Dept d
on e.DEPTNO=d.DEPTNO

select *
from t_item t1 join t_item_category t2
on t1.category_id=t2.id;
select *
from t_item t1 right join t_item_category t2
on t1.category_id=t2.id;
select *
from t_item t1 left join t_item_category t2
on t1.category_id=t2.id;
select e.EMPNO,e.ENAME,e.JOB,e.SAL,s.GRADE,s.LOSAL
from EMP e left join SALGRADE s
on e.sal between s.LOSAL and s.HISAL
order by e.EMPNO;
select e.EMPNO,e.ENAME,e.JOB,d.DEPTNO,d.DNAME,d.LOC
from EMP e left join Dept d
on e.DEPTNO=d.DEPTNO
where e.ENAME not like '%K%';

####mysql oracle在外连接有区别
-oracle有全外连接关键字full 左右两边不满足连接条件的都可以显示

///##
##自连接，自关联连接
###自连接，当前表的一条记录可以对应当前表自己的多条记录，
自连接是为了解决同类型数据，但又存在上下级关系的树状结构时使用。
//
select e.ENAME 员工,e1.ENAME 领导,d.LOC from EMP e,EMP e1,Dept
d where e.MGR=e1.EMPNO and e1.DEPTNO=d.DEPTNO and e.ENAME='SMITH';

select e.ENAME,m.ENAME,d.LOC from EMP e join EMP m on e.MGR=m.EMPNO join Dept d on m.DEPTNO=d.DEPTNO where e.ENAME='SMITH';

select e.*,m.ENAME from EMP e join EMP m on e.MGR=m.EMPNO;

select e.*,m.ENAME,d.DNAME from EMP e join EMP m on e.MGR=m.EMPNO join Dept d on e.DEPTNO=d.DEPTNO;

select e.EMPNO,e.ENAME,e.JOB,e.SAL,m.EMPNO 领导,m.ENAME,m.JOB,m.SAL
from EMP e left join EMP m on e.MGR=m.EMPNO;

#设计表结构的一些基本原则
###一对多关联
通常一的这一方称之为主表，多的一方称之为从表
####通过关联关系理解：
--这张表一条数据对应另外一张表的一条数据 1
--这张表一条数据对应另外一张表的多条数据 n
--学生与班级学生是主表班级表是从表
--员工和部门员工是主表，部门是从表
--设计方案，在主表（1）中增加一个字段，保存从表(n)的主键，形成主外键关系
####直观理解：正好相反
--设计方案，在从（N）表中增加一个字段，保存主表（1）的主键，形成主外键关系
##多对多关系
--学生和课程
--角色和功能
--设计方案：增加中间表，保存双方的主键（中间表和另外两张表都是一对多的关联关系）

##连接方式和关联关系的区别
-连接方式是匹配数据的方式，是具体实现管理的语法，它能实现任何关联关系的数据查询---sql是语法
-关联关系是逻辑角度阐述两张表的关系，在这个关系的基础上，可以在任何连接方式查询出相关数据--是设计表的时候的逻辑

##什么是权限管理
--对用户访问软件的权限进行的限制手段
##如何实现权限管理
--1.设计3张表：用户表、角色表、功能表
create table user(
uid int auto_increment, //主键自增长
uname varchar(100),
create_time timestamp,
primary key(uid)
);
insert into user values(null,'张三',now());
insert into user values(null,'李四',now());
insert into user values(null,'王五',now());
create table role(
id int auto_increment,
name varchar(100),
create_time timestamp,
primary key(id)
);
insert into role values(null,'管理员',now());
insert into role values(null,'店小二',now());
insert into role values(null,'普通用户',now());

create table user_role(
id int auto_increment,
user_id int,
role_id int,
primary key (id)
);
insert into user_role values(null,1,3);
insert into user_role values(null,2,3);
insert into user_role values(null,3,3);
1.查询用户及其对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=role_id;
2.查询张三对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=role_id
where u.uname='张三';
###权限管理表实现（续2）
create table work(
id int primary key auto_increment,
rid int,
work_item varchar(100)
);
insert into work values (null,1,"对用户信息、商品信息、订单信息、角色信息等后台数据进行操作");
insert into work values (null,2,"对商品信息、订单信息等后台数据进行操作");
insert into work values (null,3,"浏览商品、购买商品");

3. user表中所有用户可以做什么操作
select u.uname,w.work_item from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id
join work w
on r.id=w.rid;
4.查看张三可以进行的操作
select u.uname,w.work_item
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id
join work w
on w.rid=r.id
where u.uname='张三';

#视图
##视图概括
--视图就是一张虚拟表，使用方法和使用表一样
--视图不是真实存在的表
--视图中对应的是一个select语句的查询结果集
--创建视图的语法
create view 视图名 as 子查询
create view v_emp_10 as select EMPNO,ENAME,DEPTNO from EMP where DEPTNO=10;
---查看视图 select * from 视图名
--使用 SHOW CREATE VIEW viewname ;来查看创建视图的语句。
####使用视图的目的：
--重用SQL语句。
--简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必
知道它的基本查询细节。
--使用表的组成部分而不是整个表。
--保护数据。可以给用户授予表的特定部分的访问权限而不是整个
表的访问权限。
--更改数据格式和表示。视图可返回与底层表的表示和格式不同的
数据。
在视图创建之后,可以用与表基本相同的方式利用它们。
####
--视图本身不包含数据
--视图只是映射到基表的查询语句，当基表数据发生变化时，视图显示的数据也随之发生变化
--如果创建是视图时，子查询起了别名，那么视图只认识别名。
###修改视图
--更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR
REPLACE VIEW。如果要更新的视图不存在,则第 2 条更新语句会创
建一个视图;如果要更新的视图存在,则第 2 条更新语句会替换原
有视图。
create or replace view v_emp_10_1 as select EMPNO id,ENAME name,DEPTNO from EMP where DEPTNO=10;
##视图的分类
--视图分为简单视图和复杂视图
简单视图：创建视图的子查询中，不含有关联查询，查询的字段不包含函数和表达式，没有分组，没有去重。
反之就是复杂视图
##对视图进行DML操作，只针对简单视图进行可以使用
insert into v_emp_10_1 values(2001,'lily',10);
####对视图进行DML操作要遵循基表的约束，不然会失败
--视图只能看到10号部门，但是通过视图插入了20号部门的员工，数据是偷渡过去的，对基表进行了污染。
insert into v_emp_10_1 values(2002,'lilei',20);
--对视图的操作就是对基表的操作，操作不当会对基表产生数据污染

###不算污染只改了视图自己可以看到的
update v_emp_10_1 set name='bbb' where DEPTNO=10;
update v_emp_10_1 set DEPTNO=20;
update v_emp_10_1 set name='bbb' where DEPTNO=30;
#####mysql 数据更新（update）不会视图污染，

####删除视图中的数据-在mysql中(影响行数0)不会产生数据污染
delete from v_emp_10_1 where DEPTNO=30;

#####mysql中，通过视图能产生数据污染的只有insert
--为视图增加了检查选项，可以保证对视图进行了DML操作的时候，视图必须对改变的部分可见（不可见的不许改）否则不允许进行DML操作，
###这样避免数据污染 with check option
create or replace view v_emp_10_1 as select EMPNO id,ENAME name,DEPTNO from EMP where DEPTNO=20 with check option;
insert into v_emp_10_1 values(2003,'lili',20);
insert into v_emp_10_1 values(2003,'haimeimei',30);

####视图简化复杂查询语句，重用子查询
1.简化复杂查询
2.如果需要经常执行某项复杂查询，可以基于这项复杂查询创建视图。
####限制数据访问
1.视图本质上就是一条select语句
2.当访问视图时，只能访问到select语句中涉及到的列
3.对基表中其他列起到安全和保密作用
####工作中对视图一般只进行DQL（查）操作，不进行DML（增、删、改）操作
//创建一个复杂视图
-包含一个含有公司部门工资情况的部门
-内容如下
-部门编号，部门名称，
-部门最高、最低、平均工资//函数表达式加别名
-工资总和
create view v_dept_sal as
select d.DEPTNO,d.DNAME,max(e.SAL) max_sal,min(e.SAL) min_sal,avg(e.SAL) avg_sal,sum(e.SAL) sum_sal from EMP e join Dept d on e.DEPTNO=d.DEPTNO group by DEPTNO;

//查询出比自己所在部门的平均工资高的员工

select e.ENAME,e.SAL,e.DEPTNO,v.avg_sal
from EMP e,v_dept_sal v where e.DEPTNO=v.DEPTNO and e.SAL>v.avg_sal;
###删除视图
--用 DROP 删除视图,其语法为 DROP VIEW viewname;。
drop view 视图名
drop view v_emp_10_1;
##索引
--用来加快查询的计数有很多，最重的就是索引
--通常索引可以大幅度提高查询速度
--如果不使用索引，mysql会从第一条数据开始，读完整个表，直到找到相关数据，表越大（条目数多），花费时间越多。
--索引可以用来改善性能，但是有时可以降低性能。
--索引的统计和应用是数据库自动完成的
--使用索引的位置
      where deptno=20
      order by
      distinct
      like 不会使用索引
create index 索引名 on 表名(字段);
create index idx_emp_ename on EMP(ENAME);
select * from EMP where ENAME='SMITH';
#####只要数据库认为可以使用某个已经创建的索引，索引就会自动应用
-我们只需要决定要不要给某张表的某个字段创建索引

####mysql innodb B+tree 3次磁盘io就可以找到
####复合索引
create index idx_emp_job_sal on EMP(JOB,SAL);
select * from EMP order by JOB,EMP;(索引多一个少一个位置调换都不行)
###创建表的时候加索引
create table mytable(
id int not null,
uname varchar(6) not null,
index idx_mytable_uname (uname)
);
####更改索引
alter table mytable add index idx_mytable_uname1(uname);
####删除索引
drop index idx_mytable_uname1 on mytable;
###索引总结
-经常出现在where子句中的列创建索引
-经常出现在order by子句中的列创建索引
-经常出现在distinct后面的列创建索引
-如果创建的是复合索引，索引的字段顺序和关键字顺序要一致
-为经常作为表连接条件的列创建索引

#####不要在经常做DML操作的表和列上建立索引
--不要在小表上创建索引
--索引不是越多越好
--删除很少使用，不合理的索引

###sql约束
####主键约束（primary key）
1.不允许重复,不允许空值
2.创建主键约束
--列级语法
create table student1(
id int primary key,
name varchar(20)
);
--表级语法 constraint 约束名约束类型(列名)
create table student(
id int,
name varchar(20),
constraint pk_stu_id primary key(id)
);
//简写
create table student2(
id int,
name varchar(20),
primary key(id)
);

####在表创建之后添加主键约束--相当于表级语法
alter table student add primary key(id);
alter table student modify id int primary key;
####删除主键约束
alter table student drop primary key;
/**mysql不行 oracle可以*/
alter table student modify id int; //不起作用

#####主键自增
create table t1(
id int primary key auto_increment,
name varchar(10)
);
insert into t1 values(null,'a');
insert into t1 values(10,'bbb');
delete from t1 where id=13;
insert into t1 values(null,'a');
#####主键自增长的细节
1.插入数据时，使用null，自动增长
2.插入数据时，如果插入一个比较大的数，那么下次增长从这个数累加
3.删除末尾的条目，这个条目的id不会再次从表中通过自增长出现。
##外键约束
1.工作中除非特殊情况，一般不使用外键，使用逻辑外键
2.外键约束是保证一个或两个表之间的参照完整性，保持数据一致
3.表的外键可以是另一张表的主键，也可以是唯一的索引
4.外键可以重复，可以是null

5.使用外键约束的条件
--父表和子表必须使用相同的存储引擎
--存储引擎必须是innodb
--外键列和参照列必须具有相似的数据类型，数字长度，若是有符号，必须相同----------字符类型长度可以不同
--外键列和参照列必须创建索引，如果外键列不存在索引，mysql自动为其创建。
1.注意，虽然mysql支持外键的列级语法创建外键，但是无效
2.表级语法创建
constraint 外键约束名 foreign key(本表列名) references 目标表(目标列)

create table teacher_table(
  teacher_id int auto_increment,
  teacher_name varchar(255),
  primary key(teacher_id)
);
insert into teacher_table values(null,'t1');
insert into teacher_table values(null,'t2');
insert into teacher_table values(null,'t3');
insert into teacher_table values(null,'t4');
create table student_table(
  student_id int auto_increment primary key,
  student_name varchar(255),
  java_teacher int,
  foreign key(java_teacher) references teacher_table(teacher_id)
);
insert into student_table values(null,'s1',1);
insert into student_table values(null,'s2',1);
insert into student_table values(null,'s3',2);
insert into student_table values(null,'s4',3);
delete from teacher_table where teacher_id=1;

###唯一约束（UNIQUE）
--指定表中某一列或者多列不能有重复的值
--唯一约束可以保证记录的唯一性
--唯一约束的字段可以为空值
--每张表可以存在多个唯一约束的列
####创建唯一约束
列级语法：
create table t2(
name varchar(20) unique
);
表级约束：
create table t3(
a int,
b int,
constraint uk_name_pass unique(a)
);
简写
create table t4(
a int,
b int,
unique(a)
);
###复合约束
create table temp(
id int not null,
name varchar(20),
password varchar(20),
constraint uk_name_password unique(name,password)
);
###删除约束
alter table temp drop index uk_name_password;
###创建表之后在添加唯一约束
alter table temp add unique uk_name_password(name,password);
alter table temp modify name varchar(25) unique;
###非空约束
create table t4(
id int not null,
name varchar(25) not null default 'abc'
);
insert into t4 (id) values(1);
###表建好之后修改非空约束
alter table t4 modify id int null;
alter table t4 modify id int not null;
###默认约束（DEFAULT）
1.用于设置列的默认值
2.要求：
--定义的默认值的常量必须与这个列的数据类型、精度等相匹配
--每个列只能定义一个默认约束
create table t5(
id int ,
name varchar(20),
sex char(10) default '男'
);
insert into t5(id,name) values(1,'aaa');
###check约束 mysql不支持检查约束，可以创建并且不报错，只不过没有任何效果
1.检查约束的作用，验证数据
create table t6(
id int,
name varchar(20),
age int,
check(age>20)
);
insert into t6 values(1,'aa',12);)
##事务
1.事务是一组原子性（最小事务单元）的sql查询，在事务内的语句，要么全都执行，要么全都不执行。

###事务的ACID 特性
1.原子性：最小的、不可分割的业务单元
2.一致性：都执行或者都不执行，保持同一个状态。
3.隔离性：多个事务并发，相互不影响。
4.持久性：commit（提交）之后，数据保存在数据库中
###使用事务的要求
-在mysql众多引擎中，innodb、NDB Cluster支持事务
-mysql默认自动提交事务，想手动提交，需要把默认提交关闭
####关闭默认提交
show variables like 'autocommit'; //展示
set autocommit=0; //关闭
start transaction;//开启
######案例
create table account(
id int,
name varchar(20),
money float
);
insert into account values(1,'aaa',1000);
insert into account values(2,'bbb',1000);
update account set money=money-100 where name='aaa';
update account set money=money+100 where name='bbb';
此时没有输入commit，直接关闭终端，再次打开终端，把自动提交关闭
查询account账户，之前的操作回滚了，
再次开启事务，完成两次update
输入commit--》数据真正保存在表中
rollback 回滚提交失败先关闭事务在开启在进行操作？

posted @ 2019-07-15 10:50 愚蠢的程序员阅读(295) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

愚蠢的程序员

mysql基本操作2

公告