SQL 基本语句学习(下)

一、DQL下
查询英语分数在 80－90之间的同学。
mysql>SELECT * FROM student WHERE english>=80 AND english<=90;
或者
mysql>SELECT * FROM student WHERE english BETWEEN 80 AND 90;  (含头也含尾)
查询数学分数为89,90,91的同学。
mysql>SELECT * FROM student WHERE math IN (89,90,91);
查询所有姓李的学生成绩。
mysql>SELECT * FROM student WHERE name LIKE '李%';
查询数学分>80，语文分>80的同学。
mysql>SELECT * FROM student WHERE math>80 AND chinese>80;
对数学成绩排序后输出。
mysql>SELECT name,math FROM student ORDER BY math;
对总分排序后输出，然后再按从高到低的顺序输出
mysql>SELECT name,chinese+english+math FROM student ORDER BY chinese+english+math DESC;
对姓李的学生语文成绩排序输出
mysql>SELECT name,chinese FROM student WHERE name LIKE '李%' ORDER BY chinese;

统计一个班级共有多少学生？
mysql>SELECT count(*) FROM student;
统计数学成绩大于90的学生有多少个？
mysql>SELECT count(*) FROM student WHERE math>=90;
统计总分大于250的人数有多少？
mysql>SELECT count(*) FROM student WHERE (chinese+english+math)>250;

统计一个班级数学总成绩？
mysql>SELECT sum(math) FROM student;
统计一个班级语文、英语、数学各科的总成绩
mysql>SELECT sum(math),sum(chinese),sum(english) FROM student;
统计一个班级语文、英语、数学的成绩总和
mysql>SELECT sum(math+chinese+english) FROM student;
统计一个班级语文成绩平均分
mysql>SELECT sum(chinese)/count(*) FROM student;
求一个班级数学平均分？
mysql>SELECT avg(math) FROM student;
求一个班级总分平均分
mysql>SELECT avg(math+chinese+english) FROM student;
求班级最高分和最低分（数值范围在统计中特别有用）
mysql>SELECT max(math+chinese+english) FROM student;
mysql>SELECT min(math+chinese+english) FROM student;

Tip:
如果要把关键字作为普通字符串对待，要使用``（Esc键盘下方的按键）包围起来

对订单表中商品归类后，显示每一类商品的总价
mysql>SELECT *,sum(price) FROM orders GROUP BY product;
查询购买了几类商品，并且每类总价大于100的商品
mysql>SELECT product,sum(price) FROM orders GROUP BY product HAVING sum(price)>100;
二、常用的函数
三、多表的设计和约束
1、主键约束(唯一定位一条记录)
PRIMARY KEY:唯一的，且不能为null。
逻辑主键：不参与业务运算，与业务无关   id
业务主键：
2、定义唯一约束
UNIQUE：可以不填。
3、不能为空
NOT NULL:必须填
定义主键自动增长：MYSQL  auto_increment

4、多表的设计
a、一对多
CREATE TABLE department(
 id int primary key,
 name varchar(100) not null unique
);
CREATE TABLE employee(
 id int primary key,
 name varchar(100) not null,
 salary float(8,2),
 dept_id int,
 CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);
b、多对多
CREATE TABLE teacher(
 id int primary key,
 name varchar(100),
 salary float(8,2)
);
CREATE TABLE student(
 id int primary key,
 name varchar(100),
 grade varchar(100)
);
CREATE TABLE teacher_student(
 t_id int,
 s_id int,
 primary key(t_id,s_id),
 CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
 CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id)
);
c、一对一
CREATE TABLE person(
 id int primary key,
 married boolean
);
CREATE TABLE idcard(
 id int primary key,
 num varchar(100),
 name varchar(100),
 CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES person(id),
);

四、JDBC简介
1、JDBC是规范。Java DataBase Conectivity的缩写
2、JDBC规范相关的类在JDK中，java.sql.*或javax.sql.*
3、编写步骤：
a、拷贝驱动
b、注册驱动
c、获取连接
d、建立发送SQL语句的对象
e、执行语句。如果是查询的话有返回结果
f、遍历结果进行处理
g、关闭资源

五、JDBC中常用接口详解
1、DriverManager
 注册驱动：
  DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  以上注册方式有2大缺点：
     a、对具体的数据库驱动严重依赖。
     b、会导致驱动类注册2遍。
  替代方案：
    Class.forName("com.mysql.jdbc.Driver");
 获取数据库的连接：
  //方式一：
//  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day12", "root", "sorry");
  //方式二：
  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day12?user=root&password=sorry&useUnicode=true&characterEncoding=utf8");
  //方式三：
//  Properties props = new Properties();
//  props.setProperty("user", "root");
//  props.setProperty("password", "sorry");
//  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day12",props);
2、Connection
 所有与数据库交互的代码都基于链接。
 createStatement()：创建向数据库发送sql的statement对象。
3、Statement
 向数据库发送SQL语句。
 ResultSet executeQuery(String sql):参数sql只能是DQL语句。返回的是封装了查询结果的ResultSet对象
 int executeUpdate(String sql):参数sql是DML语句，就返回的是语句影响到的行数。也可以执行没有返回结果的DDL语句，返回值不代表影响到的行数。
 boolean execute(String sql):参数sql可以是任意的SQL语句。返回的结果不代表成功与否。如果执行的SQL语句有结果集，返回true。其他返回false。
 
 
4、ResultSet
 封装了查询结果。内部有一个游标，默认指向第一行的前面。next()方法：游标下移，并且返回是否有记录。
 
5、PreparedStatement:是Statement的子接口（从此以后都用这个）
  作用：
   1、预编译SQL语句，执行效率高。
   2、防注入
   3、采用占位符?替换参数