SQL开发技巧

SQL分类

DDL Data Ddfinition Language　　　　数据定义语言  create ,drop,

DML Data　Manipulation Language　 数据操作语言  select insert delete update

TPL Transaction Control Language　事务控制语言  rollback commit 

DCL Data Control Language    　　   数据控制语言 grant revoke 

join语句

CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT 'yonghuming',
  `over` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `u` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

BEGIN;
INSERT INTO `user1` VALUES (1, '唐僧', 't菩萨');
INSERT INTO `user1` VALUES (2, '猪八戒', 'z菩萨');
INSERT INTO `user1` VALUES (3, '孙悟空', '齐天大圣');
INSERT INTO `user1` VALUES (4, '沙僧', 's菩萨');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL DEFAULT '',
  `over` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `b` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

BEGIN;
INSERT INTO `user2` VALUES (1, '孙悟空', '斗战胜佛');
INSERT INTO `user2` VALUES (2, '牛魔王', '牛妖');
INSERT INTO `user2` VALUES (3, '鹏魔王', '鹏鸟');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

 

inner join 

选取两张表公共的部分

select column from table a a inner join table b on a.key = b.key

SELECT
    * 
FROM
    user1 a
    INNER JOIN user2 b ON a.user_name = b.user_name

 left join

select * from tablea a left join tableb b on a.key = b.key

select * from tablea a left join tableb b on a.key = b.key where b.key is null