SQL语句
来源:互联网 发布:网络连接图标不见了 编辑:程序博客网 时间:2024/06/07 02:50
Order by
select company ,OrderNumber From Orders order by Company,OrderNumber
select Company ,OrderNumber From orders order by Company Desc
Alter
alter table table_name add column_name datatype
alter table table_name alter column_name datatype
alter table table_name modify column_name column_type not null;
Drop
删除索引 alter table table_name drop index index_name
删除列 alter table table_name drop column column_name
添加列 alter table table_name add column_name datatype
改变列 alter table table_name alter column column_name datatype
Index (索引)
创建索引 create index indexName on table_name ( username (length)) username位 char varchar 等
删除索引 drop index [indexName] on table_name
创建唯一索引 create unique indexname on table_name (username(length))
使用alter删除索引 alter table table_name drop index index_name
Left Join
select column_name from table_name left join table_name2 on table_name.column_name=table_name2.column_name 从左表那里返回所有的行,即使在右表 中没有匹配的行。
Group by
与聚合函数一起使用 select A,Count(B) as 数量 from table_name group by A
例如 tb_voucher_form 表: select user_id,COUNT(channel_id) as 那些 from tb_voucher_form GROUP BY user_id
与having 的使用: select A ,count(B) from table group by A having count(B) >2
select user_id,COUNT(channel_id) as 那些 from tb_voucher_form GROUP BY user_id HAVING COUNT(channel_id)>3
Count 函数 返回总数
select count (distinct column_name) from table_name
select count(*) from table_name
例题:
已知关系模式:
S(SNO,SNAME) --学生关系SNO为学号,SNAME为姓名
C(CNO,CNAME,CTEACHER)--课程关系CNO为课程号,CNAME为课程名,CTEACHER 为任课教
SC(SNO,CNO,SCGRADE) --选课关系SCGRADE 为成绩
S(SNO,SNAME) --学生关系SNO为学号,SNAME为姓名
C(CNO,CNAME,CTEACHER)--课程关系CNO为课程号,CNAME为课程名,CTEACHER 为任课教
SC(SNO,CNO,SCGRADE) --选课关系SCGRADE 为成绩
2.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
--实现代码:
SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
SELECT SNO
FROM SC
WHERE SCGRADE<60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2
)AWHERE S.SNO=A.SNOAND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
--实现代码:
SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
SELECT SNO
FROM SC
WHERE SCGRADE<60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2
)AWHERE S.SNO=A.SNOAND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
0 0
- SQL语句
- sql语句
- SQL语句
- SQL语句
- SQL语句
- SQL语句
- SQL语句
- SQL语句
- SQL 语句
- sql语句
- sql语句
- Sql语句
- SQL语句
- SQL语句
- sql语句
- SQL 语句
- sql语句
- sql语句
- mosquitto 使用时出现的一些问题及其解决办法
- SpringMVC集中传参方式
- mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication
- Merge Sorted Array
- OSI参考模型中各层的作用及协议
- SQL语句
- UITextField详解
- Climbing Stairs
- iOS:KVO的概述与使用
- Swift下的字符串查找
- Android开发之小知识记录-----gridview\listview选择固定位置无效解决方案
- Reverse Integer
- 内部类和外部类联系
- iOS开发笔记---objective-c宏定义