sql 书写case

来源：互联网发布：面板数据编辑：程序博客网时间：2024/05/01 15:21

查看数据库编码 SHOW CREATE DATABASE db_name;
查看表编码 SHOW CREATE TABLE tbl_name;
查看字段 SHOW FULL COLUMNS FROM tbl_name;
创建库指定utf8
CREATE DATABASE test2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
修改数据库字符集：
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE …];

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE …]
如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默认字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE…];
如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集：
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

having 的典型使用
Websites 样本
+—-+————–+—————————+——-+———+
| id | name | url | alexa | country |
+—-+————–+—————————+——-+———+
| 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 |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+—-+—————+—————————+——-+———+
access_log 样本
+—–+———+——-+————+
| aid | site_id | count | date |
+—–+———+——-+————+
| 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 |
+—–+———+——-+————+
现在我们想要查找总访问量大于 200 的网站。
select w.name, sum(a.count) as nums from Websites as w inner join access_log as a on w.id = a.site_id group by a.site_id having sum(a.count) > 200
现在我们想要查找总访问量大于 200 的网站，并且 alexa 排名小于 200
select w.id, w.name, sum(a.count) as nums from Websites as w inner join access_log as a on w.id = a.site_id where w.alexa < 200 group by a.site_id having sum(a.count) > 200;
avg 使用（access_log 样本）
SQL AVG() 语法
SELECT AVG(column_name) FROM table_name
选择访问量高于平均访问量的 “site_id” 和 “count”：
SELECT site_id, count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);

count() 用来计算行数
count(*) 所有行数常用
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目常用
count(column_name) 这一列行数不常用

练习：http://2301703.blog.51cto.com/2291703/1048174

总结：按照
select
from
where
group by
having
order by
limit 顺序拆分来

关于 in not exists not exists 练习
http://blog.csdn.net/wolfofsiberian/article/details/39346781

待练习
http://zhaodedong.leanote.com/post/mysql-sql%E7%BB%83%E4%B9%A0%E9%A2%98
http://rrylee.github.io/2015/11/12/mysql-practice/

0 0