mysql多表查询，内外连接，自连接，交叉连接，子查询，索引，视图，个人学习总结的

MYSQL外键约束多表查询外连接内连接自连接子查询索引

-------------缪林自2014-10月总结，转载请尊重原创

 

字段值类型：date:日期，time,时间，datetime:日期时间，timestamp时间戳，自动刷新，用于记录修改时间

如果cmd中数据乱码：set names utf8/gbk/gbk2312;进行设置数据库编码。

聚合函数count(),min/max(),sum(),avg();其中count(*)选中所有行，count(字段)选择非空的行。

控制流程语句：case :select case 1 when 1 then '男' when 2 '女' else '煞笔' end asresult;

if: select if(1>2,'男','女')  as result;相当于三目运算

ifnull:select ifnull(age,0) from teacher askk;如果第一个不为空则返回第一个本身值，第一个为空则返回参数2的值

nullif:select nullif(1,1);两个参数相等返回Null,不相等返回第一个参数值

 

字符串函数：

SELECT char_length(name) from teacher;求子符的长度，多少个字符；

select format(age,3) from teacher;格式化数据

select right/left('cuit daxue',4);(结果axue/cuit)从左边/右边截取4个字符

select length(name) from teacher;返回字符串的字节数

SELECT trim(name) from teacher;

select trim(leading/both/trailing 'x' from'xxxmiaolinxxx');去除头部两边尾部的x

trim（）ltrim/rtrim去除空格。

字符串拼接：select concat('my','s','ql');mysql。变量中有二进制则最终转化为二进制字符串

日期函数：

select dayofweek（‘1992-06-2’）；返回星期几(1-7)星期日-星期六等等。。。

 

//多表查询外键，关键部分开始

主键外键，就像部门表与员工表之间的关系，外键约束

添加外键语法：

要求：两张表都必须是innodb引擎表，且没有临时表，建立外间关系的对应列必须具有相似的innnodb内部数据类型，比如都是int类型，建立外间关系的对应列必须建立索引，所有要建立外键的字段必须建立索引

之前从表里面已经增加了外键字段，然后alter table emp add foreign key(dept_id) references dept(id);来添加多表查询外键。默认约束为restrict

外键之间相互的约束关系，delete/update时常用restrict静止删除更新,cascade级联删除更新，show create table emp;查看外键等各种表信息，

alter table emp drop foreign keyemp_ibfk_1(外键名);

alter table child add constraint fk_1foreign key (parent_id) references

parent(id) on update restrict on delete setnull;

ALTER TABLE yourtablename

ADD [CONSTRAINT外键名] FOREIGNKEY [id] (index_col_name, ...)

REFERENCES tbl_name (index_col_name, ...)

[ON DELETE {CASCADE | SET NULL | NO ACTION| RESTRICT}]

[ON UPDATE {CASCADE | SET NULL | NO ACTION| RESTRICT}]

对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT的方式.

内链接外连接交叉连接自连接（重点）

内连接：

select e.name as员工姓名 ,d.nameas 部门名称 from emp e inner join dept d on e.dept_id=d.id;on后面跟连接条件

select * from emp e,dept d wheree.dept_id=d.id;内链接的另一种简洁写法。

外连接：

不仅列出与链接条件相符合的数据行，还列出左表（坐外连接），右表（右外链接）中所有符合where过滤条件的数据行。

1,select e.name,d.name from emp e left joindept d on e.dept_id = d.id;

列出左表emp全部，右表dept匹配连接条件的。

2,select e.name,d.name from emp e rightjoin dept d on e.dept_id = d.id;

右边的表dept全部显示，左边的表emp显示匹配链接条件的

3,select e.name,d.name from dept d rightjoin emp e on e.dept_id = d.id;

和第1条sql语句作用相同。a左联d等于d右连a。

交叉连接：cross join或者from table1,table2;

select e.name,d.name from dept d,emp e;

或者：select e.name,d.name from emp e cross join dept d;返回两张表的笛卡尔乘积；

比如(1,2),(a,b).笛卡尔乘积1a,1b,2a,2b。作用：供别人的匹配选择。

 

自连接：做目录，子目录父目录有用。相当常用。参与连接的表都是同一张表（通过给表取别名虚拟出）

select e1.name as员工,e2.name as经理 from emp e1 left join emp e2 on e2.id=e1.gmr;查询出员工姓名和对应的经理姓名；join on的链接条件顺序不能乱

取别名时as可以要可以不要

 

子查询：当进行查询的时候，需要的条件是另外一个select语句的结果，就要用到子查询。

为了给主查询提供数据而首先执行的查询被叫做子查询。

用于子查询的关键字包括in,notin,exist,not exist,=,<>（不等于），大于小于等。一般情况子查询的效率低于连接查询，表连接都可以用子查询代替，但是反过来却不一定。子查询嵌套不可过多。

select name ,salary from emp where salary=(=,>,<等，后面必须要是一个字段值，如果是exist等后面可以是多个字段) (select max(salary) from emp);查询薪水最高的员工的名字和薪水

select avg(salary) as平均月薪,dept_idfrom emp where dept_id is not null group by dept_id;

查询每个部门员工的平均薪资，当dept_id不为空的部门，并且查询结果以dept_id进行分组。

select name,salary from emp where salary> (select avg(salary) from emp（此处还可以嵌套子查询）);

查询月薪高于平均月薪的员工的姓名月薪。

 

索引：重点！索引是数据库中用来提高查询效率的常用工具，所有mysql索引都以b-树形式保存。

在使用< > >= <= <> in between like "patten"，patten不能以通配符%_开始 ，在条件语句上才会都会用到索引。selectname where age > 30；在age上面用到索引，要为age创建索引。

创建普通索引：

create index indexname onemp(name(10));name里面的10，表示只索引前10个字符，后面的不管了，节省索引时间ALTER mytable ADDUNIQUE [indexName]  (username(length))

altertable emp add index newname  (name(6));

drop index ageindex on emp;删除索引

组合索引：ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

alter table foryou add index name_check(username(10),isCheck);

组合索引加速索引效率。

唯一索引：索引列的所有值都只能出现一次，必须唯一。

createuniqueindex indexname on tablename(name(10));

alter table emp addunique indexname (name(10));

主件索引：建立主键的时候就已经创建好了主键。alter table emp add [constraint主键名]primary key(id);

全文索引：mysql从3.2后支持全文索引，索引类型为fulltext.mysql5.0只有MYISAM支持fulltext,并且仅限于char,varchar,text几种类型。

创建全文索引：create fulltext index索引名 on 表名(列(length));

当存储引擎为myisam，不支持外键约束，当大批量导入数据时，它会边键表边建立索引，效率低，应该先禁用索引，在打开。而Innodb建立索引都是单独处理的，不需要禁用，innodb还支持事务处理，myisam不支持。

禁用索引：alter table emp disable keys;打开索引：alter table empenable keys;

最适合索引的列时出现在where句子中的列，或者是连接句子中的列，而不是select中的列。

利用最左前缀，利用搜索的关键字建立多列索引，不要过度的索引。

select * from users whereYEAR(adddate)<2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users whereadddate<‘2007-01-01’;

mysql视图：视图也是为了提高查询效率！视图是虚表，存储时只存储视图的定义，并没有存储对应的数据。视图只有在用户需要使用时才从基表中搜集数据，再展现给用户。

特点：简单，安全：防止未经许可的用户访问敏感数据，只能操作视图。视图数据的更新就是表数据的更新。

视图属于数据库级别，不是表级别。

创建视图：create view viewname as select语句;

create view myview as selectemp.id,emp.name ename,dept.name dname from emp,dept where emp.dept_id = dept.id;

使用视图：select * from myview;myview视图就相当于一张虚表；

不可更新试图：

如果视图包含下述结构中的任何一种，那么它是不可更新的：

聚合函数sum,min/max,avg,count等。

distinct,group by,having,union,union all，

仅引用文字值（没有基本表来源）；

位于选择列表中的子查询；

join,from,where子查询，引用from自居的表，临时表不可更新。

查看视图：show tables;显示视图结构：desc视图名(表明，看表的结构)；显示视图的详细信息：show create view myview;

修改视图：alter view myview as select语句;

alter view myview as selecta.username,emp.name from tp_admin a, emp where a.id<=2;

create or replace view myview as select …..;修改视图的内容。

删除视图：drop view view1,view2,......;

Union记录联结：UNION操作符用于合并两个或多个 SELECT语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。默认地，UNION操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。另外，UNION结果集中的列名总是等于 UNION 中第一个 SELECT语句中的列名

selecte.name,e.info from emp e union select d.name,d.miaoshu from dept d;

sql插入语句：insert into dept(name,miaoshu) selectname,info from emp where mgr is not null;

插入的内容可以是查询出来的结果，插入的列的字段类型必须要和查询的字段类型一致并且顺序保持一致。select distinct name from dept;查询name字段，但是过滤掉重复的值。

Distinct在select之后，可以过滤点select查询的关键字后面的重复值。

格式数据：例如：select concat(name,'的年薪是：',salary*12) as年薪 from emp;// 缪林的年薪是：60000

关系符号：and &&, or ||,xor异或,not !。(not)between…and …;

select name,age from emp where age in(22,24,29,40);

select name,age from emp where age between20 and 40;