SQL快速熟悉版

目录

数据库源数据：（两张表）

常用操作

常规查询语法

sql常见函数使用

---

数据库源数据：（两张表）

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `websites`
-- ----------------------------
DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `websites`
-- ----------------------------
BEGIN;
INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'), ('3', '菜鸟教程', 'http://www.runoob.com/', '4689', 'CN'), ('4', '微博', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `access_log`
-- ----------------------------
DROP TABLE IF EXISTS `access_log`;
CREATE TABLE `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 AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `access_log`
-- ----------------------------
BEGIN;
INSERT INTO `access_log` 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-14'), ('6', '4', '13', '2016-05-15'), ('7', '3', '220', '2016-05-15'), ('8', '5', '545', '2016-05-16'), ('9', '3', '201', '2016-05-17');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

常用操作

#查询全部数据
SELECT * FROM websites;

#查询部分数据
SELECT name,alexa FROM websites;

#去重
SELECT DISTINCT country FROM websites;

#条件查询
SELECT * FROM websites
WHERE country = 'CN';

SELECT * FROM websites
WHERE alexa > 10 AND country = 'CN';

SELECT * FROM websites
WHERE alexa BETWEEN 10 AND 25;

SELECT * FROM websites
WHERE alexa IN (13,20);

SELECT * FROM websites
WHERE url LIKE 'https%';

#排序
SELECT * FROM websites
ORDER BY alexa DESC;

SELECT * FROM websites
ORDER BY country DESC,alexa DESC;

#插入新数据
INSERT INTO websites VALUES('6','微信','https:www.weixin.com',666,'CN');
INSERT INTO websites (name,url,alexa,country) VALUES('英雄联盟','https:www.LOL.com',4396,'CN');

#更新数据
UPDATE websites SET name='微博呵呵',alexa=250
WHERE id = 4;

UPDATE websites SET alexa=911
WHERE country = 'USA';

#删除
DELETE FROM websites
WHERE alexa = '250';

常规查询语法

#分组查询
SELECT MAX(alexa),country
FROM websites
GROUP BY country;


#限制返回条数
SELECT * FROM websites
ORDER BY alexa DESC
LIMIT 3;

#通配符%:模糊匹配
SELECT * FROM websites
WHERE url LIKE '%OL%';

#通配符_
SELECT * FROM websites
WHERE name LIKE '_oo_le';

#操作正则表达式
#开头字符匹配
SELECT * FROM websites
WHERE name REGEXP '^[GF淘]';

#开头字符匹配A-H范围内
SELECT * FROM websites
WHERE name REGEXP '^[A-H]';

#开头匹配A-H范围外的
SELECT * FROM websites
WHERE name REGEXP '^[^A-H]';

#IN操作符
SELECT * FROM websites
WHERE alexa IN (911,4395,666);

#合并列
SELECT name,CONCAT(url,alexa,country) FROM websites;

#取表的别名
SELECT abc.name ,abc.alexa,abc.url FROM websites AS abc
WHERE abc.alexa ='911';

#多表联查INNER JOIN 取交集
SELECT website.name ,access_log.count,access_log.date
FROM websites
INNER JOIN access_log
ON websites.id =access_log.site_id
ORDER BY access_log.count;

#LEFT JOIN 左表有的都返回，右边匹配不上的返回null
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

#RIGHT JOIN与上面相对
SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id=websites.id
ORDER BY access_log.count DESC;

#合并结果
SELECT country, name FROM websites
WHERE country = 'CN'
UNION ALL
SELECT country,app_name FROM apps
WHERE country = 'CN'

sql常见函数使用

#AVG()
SELECT * FROM access_log
WHERE count >(SELECT AVG(count) FROM access_log);

#COUNT(),统计行数
SELECT COUNT(count) AS nums FROM access_log
WHERE site_id=3;

#MAX() MIN() 最大值最小值
SELECT MAX(alexa) AS max_alexa FROM Websites;
SELECT MIN(alexa) AS max_alexa FROM Websites;

#SUM函数统计数值
SELECT SUM(count) AS nums FROM access_log;

#GROUP BY 分组，配合聚合函数使用
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

#HAVING:where关键字无法与聚合函数一起使用，HAVING可以让我们筛选分组后的各组数据
SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

#EXISTS判断子句是否有记录，存在返回true，否则返回false
SELECT Websites.name, Websites.url 
FROM Websites 
WHERE 
EXISTS (SELECT count FROM access_log 
WHERE Websites.id = access_log.site_id AND count > 200);

#UCASE()转换内容为大写
SELECT UCASE(name) AS site_title, url
FROM Websites;

#LCASE()转换内容为小写
SELECT LCASE(name) AS site_title, url
FROM Websites;

#提取字符
SELECT MID(name,1,4) AS ShortTitle
FROM websites；

#返回文本长度
SELECT name,LENGTH(url) as LengthOfURL
FROM websites;