MySQL开发技巧一

DDL:数据定义语言

TPL:事务处理语言

DCL:数据控制语言

DML:数据操作语言

---

 

SQL语句的作用：

1 增加数据库的处理效率，减少应用的响应时间

2 减少服务器的负载，增加服务器的稳定性

3 减少服务器间通讯的网络流量

---

 

Join从句的查询

创建测试表

create table `user1` (
`user_name` varchar (105),
`id` int (11),
`number` int (11)
); 
insert into `user1` (`user_name`, `id`, `number`) values('张三','1','8');
insert into `user1` (`user_name`, `id`, `number`) values('李四','2','12');
insert into `user1` (`user_name`, `id`, `number`) values('王五','3','6');


create table `user2` (
`user_name` varchar (105),
`id` int (11),
`number` int (11)
); 
insert into `user2` (`user_name`, `id`, `number`) values('张三','1','9');
insert into `user2` (`user_name`, `id`, `number`) values('李逵','2','12');
insert into `user2` (`user_name`, `id`, `number`) values('张飞','3','6');
insert into `user2` (`user_name`, `id`, `number`) values('张三','4','3');


create table `user3` (
`id` int (11),
`user_name` varchar (450),
`age` int (11),
`classes` varchar (450)
); 
insert into `user3` (`id`, `user_name`, `age`, `classes`) values('1','like','22','2');
insert into `user3` (`id`, `user_name`, `age`, `classes`) values('2','mawu','26','3');
insert into `user3` (`id`, `user_name`, `age`, `classes`) values('3','fei','28','4');
insert into `user3` (`id`, `user_name`, `age`, `classes`) values('4','sdfd','23','7');
insert into `user3` (`id`, `user_name`, `age`, `classes`) values('5','mawu','23','3');

 

inner join　　　　　　内连接

　　　　

<1> SELECT a.'user_name',a.'password',b.'password' FROM user1 a INNER JOIN user2 b ON a.'user_name'=b.'user_name';
<2> SELECT a.user_name,a.password,b.password FROM user1 a,user2 b WHERE a.user_name=b.user_name;

 

full (outer) join　　　　　　　　 全(外)连接

SELECT a.'user_name',a.'password',b.'password' FROM user1 a FULL JOIN user2 b ON a.'user_name'=b.'user_name';

　　在MySQL查询中报错：ERROR 1064

如何在MySQL中使用 full join(如上图)
1 查询A,B表的所有数据：UNION ALL 建立左连接和右连接的合集操作

mysql> SELECT a.user_name,a.password,b.password FROM user1 a LEFT JOIN user2 b ON a.user_name=b.user_name 
UNION ALL 
SELECT b.user_name,b.password,a.password FROM user1 a RIGHT JOIN user2 b ON a.user_name=b.user_name;

2 查询除去A,B共有，其它所有数据：

mysql> SELECT user_name,password FROM user1 WHERE user_name NOT IN (SELECT a.use
r_name FROM user1 a,user2 b WHERE a.user_name=b.user_name) UNION SELECT user_nam
e,password FROM user2 WHERE user_name NOT IN (SELECT b.user_name FROM user1 a,us
er2 b WHERE a.user_name=b.user_name);

 

left (outer) join　　　　 　左(外)连接

　　　　

查询：左表user1的全部及两表的交集

SELECT a.'user_name',a.'password',b.'password' FROM user1 a LEFT JOIN user2 b ON a.'user_name'=b.'user_name';

查询：除去右表user2后所有的user1

SELECT a.'user_name',a.'password',b.'password' FROM user1 a LEFT JOIN user2 b ON a.'user_name'=b.'user_name' WHERE b.'user_name' IS NULL;

 

right (outer) join　　　  右(外)连接

查询：右表user2的所有

SELECT b.'user_name',b.'password',a.'user_name' FROM user1 a RIGHT JOIN user2 b ON a.'user_name'=b.'user_name';

查询：除去user1后所有的user2

SELECT b.'user_name',b.'password',a.'user_name' FROM user1 a RIGHT JOIN user2 b ON a.'user_name'=b.'user_name' WHERE a.'user_name' IS NULL;

 

cross join　　　　　　交叉连接

CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来。笛卡尔连接不需要 'ON' 关键词:

---