SQL语句学习

SQL 是用于访问和处理数据库的标准的计算机语言

一、可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。
DML：
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
DDL：
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
DQL：
（1）DISTINCT+列名称 用于返回唯一不同的值。
（2）where语句中 and比or的优先级更高
select * from tablename where condition1 or condition2 and condition3
其实相当于
select * from tablename where condition1 or (condition2 and condition3)
（3）ORDER BY 列名称 （asc/desc） 语句用于对结果集进行排序。
以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）：
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
（4）INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)
（5）UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
（6）DELETE FROM 表名称 WHERE 列名称 = 值
DELETE * FROM table_name 不删除表的情况下删除所有的行

二、高级
（1）limit a,b 取其中a-b
（2）like/not like ‘%a%’ 包含a字段
通配符
%一个或多个字符
_仅替代一个字符
[charlist] [!charlist] 在或不在字符列中的任何单一字符
SELECT * FROM Persons WHERE City LIKE ‘[ALN]%’
城市以 “A” 或 “L” 或 “N” 开头的人
（3）as 可以为列名称和表名称指定别名
SELECT po.ID, p.LName, p.Fname from Persons AS p, Product_Orders AS po WHERE p.LastName=‘Adams’ AND p.FirstName=‘John’
把两个表分别给予个别名。
SELECT LastName AS Family, FirstName AS Name FROM Persons
把两个列指定别名
（4）join
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
（5）UNION 内部的 SELECT 语句必须拥有相同数量的列
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
union只选取不同的值，union all可以选取重复的值。
（6）select into 创建表备份（MySQL不支持）
Select * Into new_table_name from old_table_name; 这是sql server中的用法
替代方法:
Create table new_table_name (Select * from old_table_name);
（7）索引
CREATE INDEX 语句用于在表中创建索引。
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。其中MySQL中的索引的存储类型有两种：BTREE、HASH。
三、其他

（1）count和sum的区别
count()函数里面的参数是列名的的时候,那么会计算有值项的次数。
Sum()函数里面的参数是列名的时候，是计算列名的值的相加，而不是有值项的总数。
1、查询“95031”班的学生人数
select count() from student where class=‘95031’
（2）子查询
2、查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select Sno,Cno from score where degree = (select max(degree) from Score)
select Sno,Cno from score order by degree limit 1;
（3）group by分组
3、查询每门课的平均成绩
select Cno,avg(degree) from Score group by Cno
4、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
外层为 查询课程的平均分 内层为 多于5名同学选的课程
select Cno,avg(Degree) from score where cno like ‘3%’ and Cno in
(select Cno from score group by Cno having count()>=5 ) (多于五名同学选的课)
group by Cno;
（4）其他多表查询
5、查询“95033”班学生的平均分
外层： 查询平均分，内层 学生是95033班的
select avg(degree) from score where sno in (select sno from student where class = 95033)
6、查询选修105课程的成绩高于109号同学成绩的所有同学的记录。
外层： 查询同学 中层：选修了105且成绩大于 内层：查询109选修105的成绩。
select * from student where sno in
(select sno from score where cno=105 and degree>
(select degree from score where Cno=105 and sno =109) )
7、查询和学号为108、101的同学同年出生的所有学生
year/month/day (date)取得日期中的年/月/日。now() 当前时间
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (101,108)) ;
8、查询选修某课程的同学人数多于5人的教师姓名
外层：查询老师姓名 ， 内层：选修的次数大于5
select tname from teacher,course where teacher.tno = course.tno and course.cno in (select cno from score group by cno having count(cno)>5);
9、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
外层：查询计算机系 teacher表的名字和职称 内层 查询电子工程系教师的职称
select tname,prof from teacher where depart = ‘计算机系’ and prof not in (select prof from teacher where depart = ‘电子工程系’)
10、查询那些选修了105和245课程的同学且105成绩大于245；
外层：按学号分组查询 内层：105成绩大于245成绩
select cno,sno i,degree from score group by sno desc having (select degree from score where cno = 105 and sno=i)>(select degree from score where cno=245 and sno=i);
11、联合查询 union 查询所有教师和同学的name、sex和birthday.
select tname,tsex,tbirthday from Teacher
union
select sname,ssex,sbirthday from Student；
12、 给表别名，查询成绩比该课程平均成绩低的同学的成绩表。
外层：查询成绩 内层：该课程的平均成绩。
select * from score a where degree < ( select avg(degree) from score b where a.cno = b.cno)
13、查询至少有2名男生的班号。
外层：查询班级号 内层：男生》=2
select class from student group by class having count(ssex=‘男’)>=2;
14、查询各科的最高分
select * from score a group by cno having degree in (select max(degree) from score);
select * from score group by cno order by degree limit 3;
15、查询所有选修“计算机导论”课程的“男”同学的成绩表
外层：查询成绩 内层：男同学+计算机导论

内层：
select sno from student where ssex = ‘男’;
select cno from course where cname = ‘计算机导论’ ;
外层：select * from score where cno = (select cno from course where cname = ‘计算机导论’ ) and sno in (select sno from student where ssex = ‘男’);

四、依照DateTime分组查询

SELECT DATE_FORMAT( visitTime, “%Y-%m-%d %H” ) , COUNT( * ) FROM gjsyoung.visitlog group BY DATE_FORMAT( visitTime, “%Y-%m-%d %H” );
对visitTime列查询分组依据为：年-月-日 时（查询其他分组依据以此类推）

SELECT count(*) FROM gjsyoung.visitlog WHERE TO_DAYS(visitTime) = TO_DAYS(NOW())-1;
计数昨天访问的人数，精确到某天

SELECT * FROM gjsyoung.visitlog where DATE_SUB(CURDATE(),INTERVAL 1 DAY) < date(visitTime);
计数某天至今的人数，INTERVAL 1 DAY意味着距近一天，即昨天和今天