11.4 SQL代码

最新推荐文章于 2024-08-12 16:51:09 发布

Sshiyitong

最新推荐文章于 2024-08-12 16:51:09 发布

阅读量43

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/m0_68575886/article/details/127695020

版权

#11.4
SELECT * FROM websites WHERE alexa BETWEEN 1 AND 20;#选取 alexa 介于 1 和 20 之间的所有网站
SELECT * FROM websites WHERE alexa NOT BETWEEN 1 AND 20;#选取 alexa 不介于 1 和 20 之间的所有网站
SELECT * FROM websites WHERE (alexa BETWEEN 1 AND 20) AND (country NOT IN ('USA', 'IND'));#选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站
SELECT * FROM websites WHERE name BETWEEN 'A' AND 'H';#选取 name 介于 'A' 和 'H' 之间字母开始的所有网站
SELECT * FROM websites WHERE name NOT BETWEEN 'A' AND 'H';#选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站
#以access_log 表为例
SELECT * FROM access_log;
SELECT * FROM access_log WHERE date BETWEEN '2016-05-10' AND '2016-05-14';#选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录：
SELECT name AS n, country AS c FROM websites;#指定两个别名，一个是 name 列的别名为n，一个是 country 列的别名为c
SELECT *FROM websites;
SELECT name, CONCAT(url,',',alexa,',',country) AS site_info FROM websites;#把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名
SELECT w.name, w.url,a.count, a.date FROM websites AS w, access_log AS a WHERE w.id = a.site_id AND W.name = '菜鸟教程';#选取 "菜鸟教程" 的所有访问记录。我们使用 "Websites" 和 "access_log" 表，并分别为它们指定表别名 "w" 和 "a"
SELECT websites.name, websites.url,access_log.count,access_log.date FROM websites,access_log WHERE websites.id = access_log.site_id AND websites.name = '菜鸟教程'; #不带别名的相同的语句
SELECT websites.id, websites.name, access_log.count, access_log.date FROM websites,access_log WHERE websites.id = access_log.site_id;
SELECT websites.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;
SELECT websites.id, 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;
INSERT INTO access_log (aid, site_id, count, date) VALUES('10', '10','500','2016-05-16');
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 websites.id, websites.name, access_log.count, access_log.date 
FROM websites
LEFT JOIN access_log
ON websites.id = access_log.site_id
UNION
SELECT websites.id, websites.name, access_log.count, access_log.date 
FROM websites
RIGHT JOIN access_log
ON websites.id = access_log.site_id
ORDER BY count DESC;
SELECT * FROM apps;
SELECT country FROM websites
UNION
SELECT country FROM apps
ORDER BY country;
SELECT country FROM websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
SELECT name, country FROM websites
WHERE country = 'CN'
UNION ALL
SELECT app_name, country FROM apps
WHERE country = 'CN';