mysql-3

连接

NATURAL JOIN :自然连接基于两个表中具有相同名称的所有列。它从2个表中选择在所有匹配中具有相等值的行，如果具有相同名称的列具有不同的数据类型一定会发生隐式转换。

select employee_id,first_name,department_id,
department_name
from employees natural join departments;



select employee_id,first_name,department_id,d
epartment_name
from employees natural join departments
using(department_id,manager_id);

使用表前缀来限定多个表中的列名。使用表前缀可以提高语句的解析速度；使用表别名代替完整的表名前缀，表别名为表提供了一个较短的名称，使sql代码更小，使用更少的内存，使用列别名来区分名称相同但位于不同表中的列。

内连接 join：

select * 
from emplyees e join departments d
on e.department_id = d.department_id; 
另外一种写法：
select * 
from emplyees e , departments d
where e.department_id = d.department_id;
--结果使2个表的交集：只输出匹配的内容

左外连接 left join：

select * 
from emplyees e left join departments d
on e.department_id = d.department_id; 
结果是 左表全部输出

右外连接 right join：

select * 
from emplyees e right join departments d
on e.department_id = d.department_id; 
结果是 右表全部输出

子查询

子查询在主查询之前执行，子查询的结果由主查询使用。

select * 
from employess 
where hire_date >
(select hire_date from employees where last_name='Davies');

多行子查询
返回多行，使用多行比较运算符
IN：等于列表中的任何成员
ANY：必须以=，!=,>,<,>=,<=开头。如果子查询的结果集中存在至少一个元素的关系为true，则返回true。
ALL：必须以=，!=,<,>,<=,>=开头。如果子查询结果集中的所有元素的关系均为true，则返回true。

select * 
from employees 
where salary < any(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';

union:消除重复后，union运算符从两个查询返回行。
union all：不消除重复，返回两个查询的总和

select employee_id,salary 
from employees 
union
select employee_id,salary 
from employees 
结果：去重，只是相当于 select employee_id,salary 
from employees 的查询

select employee_id,salary 
from employees 
union all
select employee_id,salary 
from employees 
结果：不去重

DML语句

insert 语句
语法：insert into table（列名1，列名2…） values (value1,value2…);

insert into departments(department_id,department_name,manager_id,location_id) values(280,'public',100,1700);

insert into emp values(207,'law','pop','517.124.4567',current_date,'ac_account',6900,null,205,110);


create table emp as select * from employees;
--复制表employees的表结构以及数据到emp表中，emp表创建成功。

create table dept as select * from departments where 1=2;
--创建dept表，它的表结构与departments相同，但是没有它的数据。

--时间
select currnt_date();
select current_date;
select current_time();

--从另一个表中复制行，将子查询返回的所有行插入表emp中。
insert into emp select * from employees;

update语句
语法：update table
set 列名1=value1，列名2=value2…
where 条件
SQL_SAFE_UPDATES:鉴于以前出现的数据大表误更新和全表误删除操作，影响服务使用和数据安全。为了防止线上业务出现以下3种情况影响线上服务的正常使用和不小心全表删除：
1.没有where条件的全表更新操作；
2.加了where条件，但是没有走索引
3.全表delete没有加where 条件，或者where条件没有走索引。
关闭方法： show variables like ‘SQL_SAFE_UPDATES’;
SET SQL_SAFE_UPDATE = 0;

update emp 
		set department_id=50
where employees_id=113;

update emp 
       set salary = (select salary from emp where employee_id=205)
       where employee_id=103;
--这个sql有问题，不能先select出同一个表中的某些值，再update这个表（在同一语句中），即不能依据某字段值左判断再来更新某字段的值。mysql会出问题，但是oracle不会。

update emp 
       set salary = (select salary from (select salary from emp where employee_id=205) as e)
       where employee_id=103;


update emp set salary = salary + 200 order by salary desc limit 4;		

DELETE语句
语法：delete from table [where 条件]
TRUNCATE 语句：从表中删除所有行，使表为空，并保持表结构完整，是数据定义语言（DDL）语句而不是DML语句，不能轻易撤销。
语法： truncate table table_name;

delete from dept where department_name = 'Finance';

delete from emp
where department_id in(
	select department_id from deptments where 			  department_name like '%Public%'
);

TRUNCATE TABLE emp;

事务

事务：一个数据库事务由一条或者多条可发生事务的sql语句构成，他们形成一个逻辑的工作单元。这些sql语句要么全部执行成功，要么全部执行失败。
事务时保证数据的完整型和一致性的重要手段。
数据库事务由以下之一组成：
DML事务：由一条或者多条DML语句构成。
DDL事务：总是由一条DDL语句构成。
DCL事务：总是由一条DCL语句构成。
对于DDL（create，alter，drop等开头的语句）和DCL（grant，revoke语句）事务，在执行每条语句之前和之后，mysql会自动执行一条commit语句，因此事务时自动开始和结束的，
DML事务的结束
1）commit语句：事务提交。事务所做的全部操作被永久的保存在磁盘上
2）rollback语句：事务回滚。事务所做的全部工作被撤销，表中的数据不受事务操作的的影响。
其他事务控制语句
1）SEVEPOINT identifier:保存点命令，用来在事务中做一个标记，专门提供rollback to 语句使用
2）ROLLBACK TO [SEVEPOINT] identifier:回滚到保存点。专门用来撤销事务所做的部分工作：保存点之后所做的工作全部撤销。该语句并不结束事务。
使用commit和rollback语句的优点，你可以：
1.确保数据一致性；
2.在永久更改之前预览数据更改
3.可以去控制分组逻辑相关的操作。

--查看事务是否开启自动提交
SHOW VARIABLES LIKE '%autocommit%';

SET @@autocommit = 0; --针对会话
SET @@autocommit = 1; --开启自动提交
SET @@global.autocommit=0;--针对全局
SET @@global.autocommit=1;--开启自动提交
--永久的启动关闭事务的自动提交
vi /etc/my.cnf
在里面添加 autocommit = 0;
保存
service mysql restart;
mysql -uroot -proot
show varibales like '%autocommit%';

前提是关闭自动提交：
CREATE TABLE trans_demo(
 id INT，
 name VARCHAR(20)
);
INSERT INTO trans_demo(id,name) values(1,'zs');
INSERT INTO trans_demo(id,name) values(1,'ls');
SEVEPOINT a;
UPDATE trans_demo SET name='ww' WHERE id=1;
SEVEPOINT b;
DELETE FROM trans_demo WHERE id=2;

-下面决定如何结束事务 
ROLLBACK TO b;  --有2条语句并且 数据 name=‘ww’
ROLLBACK to a;  ---有2条数据，数据是insert插入的语句
ROLLBACK;  --结束。 ---没有数据

事务在DDL语句中自动提交

START TRANSACTION;
CREATE TABLE trans_demo(
 id INT，
 name VARCHAR(20)
);
--默认事务提交了

INSERT INTO trans_demo(id,name) values(1,'zs');
INSERT INTO trans_demo(id,name) values(1,'ls');
ROLLBACK;
--我们发现数据表中仍然有2条数据，因为create建表语句已经让事务提交了。

约束

约束主要防止非法数据进入到表中，确保数据的正确性和一致性。约束也可以防止一个表被删除。
约束的类型：5种
NOT NULL:非空约束
UNIQUE:唯一性约束
PRIMARY KEY:主键约束
FOREIGN KEY:外键约束
CHECK:检查约束
约束何时被定义：
1）使用create table语句；
2）表已经创建完了，使用alter table语句。
唯一约束的列可以有多个null值，因为 null <> null.
具有主键约束的列不允许有null值，并且不允许有重复值。每个表最多只允许一个主键。

CREATE TABLE test_ck(
  id INTEGER CHECK(id > 0)
);
INSERT INTO test_ck values(100);
INSERT INTO test_ck values(-100); //在mysql 5.7中检查约束没有任何作用，在8.0中起作用，这条数据不能插入到表中。

删除和修改表

drop table dept;
drop table  if exists dept;

--重命名表
RENAME TABLE dept to deptnew

--添加列
alter table test_a add  name varchar(20);--有隐藏事务自动提交
alter table test_a add  age integer(11) after name;

--删除列
alter table test_a drop age;

--修改列
alter table test_a modify  name varchar(40);

alter table test_a change hire_date hire_of_date date;

--添加外键
alter table t1 add constraint fk2 foreign key(a) references		t2(a)

--删除主键
alter table test_a  drop primary key;
--删除外键
alter  table test_a drop foreign key  fk2;
--删除唯一性约束：删除对应的索引即可
alter table test_a drop index c1;

视图

视图一旦创建，就像普通表那样使用，视图主要用来查询。
因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中。

create  view emp_v as select * from emp;

create view  emp_v1(empid,f_name,l_name) as
select employee_id,first_name,last_name from emp;

索引

索引的作用就是提高select查询的效率。
innodb引擎支持3类索引：
b+树索引
哈希索引
全文索引
其中哈希索引是自适应的，即innodb引擎会根据表的使用情况自动为表生成哈希索引，不能认为干预是否对一张表生成哈希索引。
使用的最多索引是b+树索引。它对大表和小表来说，性能很平衡。
b+树索引分为：聚集索引和辅助索引
innodb引擎里的表默认是索引组织表，即表的数据按照主键的顺序存放。聚集索引就是按照每张表的主键构造一颗b+树，同时叶子结点中包含了表中完整的数据记录。也将聚集索引的叶子节点叫做数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。

由于实际的数据页只能按照一颗b+树进行排序，因此一张表只能有一个聚集索引。在多数情况下，查询优化器倾向于使用聚集索引，因为聚集索引能够在索引的叶子节点上直接找数据。

索引中键值的逻辑顺序决定了表中相应的物理顺序（索引中的数据物理存放地址和索引的顺序是一致的）
innodb引擎的数据文件本身就是索引文件，叶子节点中包含了完整的数据记录。因为innodb的数据文件本身要按照主键聚集，所以innodb要求表必须有主键，如果没有显示指定，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql自动为innodb表生成一个隐含字段作为主键。

innodb引擎会为每张表都加一个聚集索引，而聚集索引指向的数据又是以物理磁盘顺序来存储的，自增的主键会把数据自动向后插入，避免了插入过程中聚集索引排序问题。如果对聚集索引进行排序，这回带来磁盘io性能损耗是非常大的。
MYISAM引擎使用B+树作为索引结构，叶子点的data存放的是数据记录的地址，它的数据文件和索引文件是分开独立存储的，因此它的索引是非聚集索引。

辅助索引也称为非聚集索引。叶子节点不包含行的全部数据，叶子节点除了包含行的索引键值以外，每个索引行还包含了一个主键值，该主键值告诉innodb存储引擎，哪里可以找到与索引对应的数据。

辅助索引的存在并不影响数据在聚集索引中的组织，因此一个表可以有多个辅助索引。当通过辅助索引查找数据时，innodb会遍历辅助索引获得指向主键索引的主键，然后再通过主键索引找到一个完整的行数据。辅助索引的叶子节点中包含了列的值和主键值。

--添加索引
alter table t add index idx_c(c);
--显示所有的索引
show index from t;