SQL基础语法学习记录（速成版）

选择安装MySql

拖拉好久 又是磨洋工赶ddl 受不了了 能不能再摆一点、、

常用类型

int 整型

double 浮点

datatime 日期

char 定长

varchar 不定长

建表操作

-- 删除表
DROP TABLE IF EXISTS `表名`;
-- 新建表
create table `表名`(
	字段名 类型 约束（主键，非空，唯一，默认值）,
	字段名 类型 约束（主键，非空，唯一，默认值）,
)编码，存储引擎

约束：

DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
	id INT(11) NOT NULL AUTO_INCREMENT,
	name1 CHAR(20) NOT NULL DEFAULT '' COMMENT websites'站点名称',
	url VARCHAR(255) NOT NULL DEFAULT '',
	alexa INT(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
	sal DOUBLE COMMENT '广告收入',
	contry CHAR(10) NOT NULL DEFAULT '' COMMENT '国家',
	PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

查询

INSERT INTO websites(name1,url,alexa,sal,contry) VALUES('腾讯','https://www.qq.com',18,1000,'CN');

删除

DELETE FROM websites WHERE id = 5;

更新

UPDATE websites SET sal = NULL WHERE id = 1;

DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
	id INT(11) NOT NULL AUTO_INCREMENT,
	name1 CHAR(20) NOT NULL DEFAULT '' COMMENT '站点名称',
	url VARCHAR(255) NOT NULL DEFAULT '',
	alexa INT(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
	sal DOUBLE COMMENT '广告收入',
	contry CHAR(10) NOT NULL DEFAULT '' COMMENT '国家',
	PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `websites` VALUES
	(1,'google','https://www.google.cn/','1',2000,'USA'),
	(2,'淘宝','https://www.taobao.cn/','13',2050,'CN'),
	(3,'菜鸟教程','https://www.runnoob.cn/','4689',0.0001,'CN'),
	(4,'微博','https://www.weibo.cn/','20',50,'CN'),
	(5,'facebook','https://www.facebook.cn/','3',500,'USA');
	
CREATE TABLE if NOT EXISTS `access_log`(
	`aid` INT(11) NOT NULL AUTO_INCREMENT,
	`site_id` INT(11) NOT NULL DEFAULT '0' COMMENT '网站id',
	`count` INT(11) NOT NULL DEFAULT '0' COMMENT '访问次数',
	`date` date NOT NULL,
	PRIMARY KEY(`aid`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
INSERT INTO `access_log`(`aid`, `site_id`,`count`,`date`) VALUES
	(1, 1, 45, '2016-05-10'),
	(2, 3, 100, '2016-05-13'),
	(3, 1, 230, '2016-05-14'),
	(4, 2, 10, '2016-05-14'),
	(5, 5, 205, '2016-05-15'),
	(6, 4, 13, '2016-05-15'),
	(7, 3, 220, '2016-05-15'),
	(8, 5, 545, '2016-05-16'),
	(9, 3, 201, '2016-05-17'),
	(10, 88, 9999, '2016-09-09');

查询select

-- 实际开发中少使用*作为查询
-- SELECT * FROM websites
SELECT id, name1, url, alexa, sal, contry FROM websites

分页查询

SELECT id, name1, url, alexa, sal, contry FROM websites LIMIT 2,3
-- 分页查询

从第二条开始查三条

去重distinct

SELECT distinct contry FROM websites;

条件查询where

SELECT * FROM websites WHERE sal >500;

运算符：> < <= >= <> != =

​ is null,is not null,like,in(null和任何东西比较都是假的，包括他本身)

逻辑判断and、or

SELECT * FROM websites WHERE sal >= 0 AND sal <= 2000;
SELECT * FROM websites WHERE sal BETWEEN 0 AND 2000;
SELECT * FROM websites WHERE sal <5 OR sal IS NULL;

排序 order by

SELECT * FROM websites ORDER BY sal ASC,alexa DESC;

asc升序，desc降序

like 和 通配符

SELECT * FROM websites WHERE NAME LIKE '%o';

like ：模糊查询

通配符： % 0个 或多个字符，_ 1个字符

in

SELECT * FROM websites WHERE contry IN ('USA','CN');

in

别名

SELECT a.name1 '网站名称' FROM websites a

分组Group by

SELECT avg(sal) 'avg' ,contry FROM websites GROUP BY contry;

附加条件

contry FROM websites GROUP BY contry HAVING AVG > 1200;

子查询

把子查询当作一个新的表

连接查询

查询网站的访问记录（site_id对应网站）以及他本身

过时的：

-- 查询每个网站每天的访问量，显示出 ‘名称 访问量 日期’
SELECT NAME1,a.count,a.date FROM websites w, access_log a WHERE w.id = a.site_id;

实际开发推荐：

内连接

SELECT * FROM websites w JOIN access_log a
ON w.id = a.site_id  -- 关联条件
WHERE sal > 2000  -- 筛选条件

左关联：

SELECT * FROM websites w left JOIN access_log a
ON w.id = a.site_id  -- 关联条件

右关联：

SELECT * FROM websites w right JOIN access_log a
ON w.id = a.site_id  -- 关联条件

全关联：

SELECT * FROM websites w right JOIN access_log a
ON w.id = a.site_id  -- 关联条件
union
SELECT * FROM websites w left JOIN access_log a
ON w.id = a.site_id  -- 关联条件

NULL处理

SELECT NAME1,count,a.date FROM websites w left JOIN access_log a
ON w.id = a.site_id  -- 关联条件

当count为null时改为空值0

SELECT NAME1,ifnull(COUNT,0),ifnull(a.date,' ') FROM websites w left JOIN access_log a
ON w.id = a.site_id  -- 关联条件