在这篇文章中我会通过一些例子来介绍日常编程中常用的SQL语句
目录:
-
## 1.数据库的建立
-
## 2.常用查询
## 1.数据库的建立
实例将ER图的形式给出:
![](https://images2017.cnblogs.com/blog/1237656/201801/1237656-20180120205330693-1159710678.png)
由此转换的4个关系模式: ![]()
注:下划线为直线为主键,下划线为红色虚线为外键
![](https://images2017.cnblogs.com/blog/1237656/201801/1237656-20180120205341349-2117067483.png)
由此可以建立如下数据库:
C:
![](https://images2017.cnblogs.com/blog/1237656/201801/1237656-20180120205423506-2057338389.png)
S:
![](https://images2017.cnblogs.com/blog/1237656/201801/1237656-20180120205433303-654938660.png)
SC:
![](https://images2017.cnblogs.com/blog/1237656/201801/1237656-20180120205443662-859378259.png)
T:
![](https://images2017.cnblogs.com/blog/1237656/201801/1237656-20180120205452709-1248905755.png)
数据库的具体建立脚本请查看 我的github
## 2.常用查询
- 查询年龄最小的四个人(按年龄从小到大排序,如果年龄相同,按姓名顺序排序)
SELECT * FROM s
ORDER BY age ASC,CONVERT(sName USING gbk)
LIMIT 4
- 查询考试时间最早的两门课程(examDate为datetime类型数据)
SELECT * FROM c
ORDER BY examDate ASC,CONVERT(cName USING gbk)
LIMIT 2
- 随机获取三个学生数据
SELECT * FROM s
ORDER BY RAND()
LIMIT 3