2017-06-28
数据库表:
一个数据库通常包含一个或者多个表。每个表有一个名字标识。
SQL语句:
注释:一定记住,SQL对大小写不敏感!
分号在数据库系统中分隔每条SQL语句的标准方法,这样就可以在对服务器的相同请求中执行一条
以上的语句。
SQL select语句:
select语句用于表中选取数据。结果被存储在一个结果表中(称为结果集)。
select语法:
select:列名称 from:表名称;
select*from 表名称
注释:SQL语句对大小写不敏感。SELECT 等效于 select;
SQL语句实例:
insert into user values(1,"小明",1,"abcdef",1);
insert into user values(2,"小黄",1,"aaaaaa",1);
insert into user values(3,"小花",0,"ssssss",1);
insert into user values(4,"小华",1,"dddddd",1);
insert into user values(5,"小蓝",0,"tttttt",1);
insert into user values(6,"小绿",0,"rrrrrr",1);
/*修改*/
update user set userName = "小胡" where userName = "小明"
/*修改表里的两个参数*/
update user set userName = "小胡",sex = 0 where userId = 1
/*删除表里的数据*/
delete from user where userId = 1
/*指定添加语句插入数据*/
insert into user (userId,userName,sex,password,flag)VALUES(1,"小明",1,"abcdef",1);
/*查询语句*/
/*给查询出来的字段取别名*/
select userName as "猪王" from user where userId=1
select userName as "猪王" from user where userId=6
select userName as "猪王" ,password as "密码" ,sex as "性别" from user where userId=1
select *from user;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
SQL DML 和 DDL:
可以把SQL分为两部分:数据操作语言(DML)和数据定义语言(DDL)。
SQL是用于执行查询的语法。但是SQL语言也包含用于更新、插入、删除的语法。
查询和更新指令构成了SQL的DML部分:
select:从数据库表现中获取数据;
update:更新数据库表中的数据;
delete:从数据库表中删除数据;
insert into:向数据库表中插入数据;
SQL的数据定义语言(DDL)部分使我们有能力创建或者删除表格。我们也可以定义索引键,
规定表之间的链接,一级施加表间的约束。
SQL中最重要的DDL语句:
create database:创建数据库;
alter database:修改数据库;
create table:创建新表;
alter table:变更(改变)数据库;
drop table:删除表;
create index:创建索引;
drop index:删除索引;
——————————————————————————————————————————————————————————————————————————————————————————————————————
SQL select distinct语句:
在表中,有可能会包含重复值。而distinct就是用来消除结果集中重复的信息,只显示一行;
语法:
select distinct :列名 from :表名;
例子:
——————————————————————————————————————————————————————————————————————————————————————————————————————
SQL where子句用于规定选择的标准:
where子句:
如需有条件地从表中选取数据,可将where子句添加到select语句。
语法:
select 列名 from 表名 where 条件 :
is null is not null 语句:
可用于where子句中的操作符:
=(等于);<>(不等于);>(大于);<(小于);>=(大于等于);<=(小于等于);any(任何一个);all(所有的);some(任何一个);
between:在某个范围内;
like:搜索某种模式;
not like %表示0或者多个字符;_ 表示一个字符
语法:
AND和OR运算符:
and和or可以再where子语句中把两个或者多个条件结合起来。
如果第一个条件和第二个条件都成立,则and运算显示一条记录。
如果第一个条件和第二个条件中只要有一个成立,则or运算符显示一条记录。
我们也可以把and和or结合起来(使用圆括号来组成复杂表达):
语法:
select * from 表名 where (列名 = 值 or 列名 = 值)and 列名 = 值;
语法:
注释:在某些版本的SQL中,操作符<>可以写成!=;
引号的使用:
SQL使用单引号来环绕文本值。如果是数值,请不要用引号;
文本值:
这是正确的: SELECT * FROM Persons WHERE数值:FirstName='Bush'
这是错误的: SELECT * FROM Persons WHEREFirstName=Bush
这是正确的: SELECT * FROM Persons WHEREYear>1965
这是错误的: SELECT * FROM Persons WHEREYear>'1965'
——————————————————————————————————————————————————————————————————————————————————————————————————————
SQL update语句:
update语句:
update语句用于修改表中德尔数据;
语法:
update 表名称 set 列名称 = 新值 where 列名称 = 值;
——————————————————————————————————————————————————————————————————————————————————————————————————————
SQL delete语句:
delete语句用于删除表中的行。
语法:
delete from 表名称 where 列名称 = 值;
删除某行:
fred Wilson 会被删除:
DELETE FROM Person WHERE LastName = 'Wilson'
删除所有行:
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的:
DELETE FROM table_name
或者:
DELETE * FROM table_name
——————————————————————————————————————————————————————————————————————————————————————————————————————
asc 升序 desc 降序;
order by 排序字段可以用查询字段的别名,先查后排;
where 条件中不能用查询字段的别名
常用函数:
可以再select后面,也可以在where后面
1.lower(字段或者字符串):把字符串转换成小写
2.upper(字段或者字符串):把字段转换成大写
3.concat(字段1或字符串1):链接两个字段(每次只能连接两个字段)||可以连接很多个
4.length(字段或者字符串):求字段的长度
5.substr(字段或者字符串,start,length):把字段转换成大写 start 从1开始 截取长度
6.round(number,n):n指定四舍五入到小数点后第几位,可以用负数指定小数点前几位
6.round(number,n):n指定四舍五入到小数点后第几位,可以用负数指定小数点前几位
7.to_number(字段或者字符串):那数字字符转换成数字
8.to_char(字段或者字符串,格式控制字符串):按指定的格式,格式化字符串
9.to_date:把字符串转换成数据库内部 日期
——————————————————————————————————————————————————————————————————————————————————————————————————————
分组函数:
计算的结果是一个查询的,不是某一行的
1.avg(字段):计算出查询结果中该字段的平均数
2.max(字段):计算出查询结果中该字段的最大值
3.min(字段):计算出查询结果中该字段的最小值
4.sum(字段):计算出查询字段中该字段的和
5.count(字段):计算出查询结果中该字段的个数
注释:函数不能写在where后面
——————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————
2017-06-29
SQL GROUP BY 语句:
GROUP BY :语句用于结合合计函数,根据一个或多个列对结果集进行分组
SQL INNER JOIN 语句:
inner join :对两个表进行连接!注释:只有两个表中有相同的属性时才能进行连接!
链表语句的其他两种方法:
LEFT JOIN (左连接)查询出包括左表中所有记录和右连接中连接字段相等的记录
right join (右连接)查询出包括右表中的所有记录和左表中联结字段相等的记录
SQL having 的用法:
having 过滤 在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。*/
having与where的区别:
having与where的区别:
having 后面可以跟函数 where后面不能跟函数
分页
MySQL:利用关键字:limit
MySQLserver:利用关键字top
Oracle:利用关键字rownum
格式:select*from(select rownum r,a.* from(?)a)where r between?and?
第一个问号:指的是要查询的SQL语句(包含排序)
第二个问号:指的是分页查询的起始行号;
第三个问号:指的是分页查询的结束行号 ;
MySQL:利用关键字:limit
MySQLserver:利用关键字top
Oracle:利用关键字rownum
格式:select*from(select rownum r,a.* from(?)a)where r between?and?
第一个问号:指的是要查询的SQL语句(包含排序)
第二个问号:指的是分页查询的起始行号;
第三个问号:指的是分页查询的结束行号 ;
/*分组函数 group by 后面字段相同的一段*/
select deptno ,avg(sal) from emp group by deptno
/*GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组*/
select deptno,avg(sal)from emp group by deptno
select job,sum(sal)from emp GROUP BY job
/*链表语句 两种:inner join(等值连接) 查询出两个表中联结字段相等的行 */
/*连表查询不能用点星,这样会把两个表中所有的数据都连在一起,会多出一组一样的数据*/
select * from emp e inner join dept d on e.deptno = d.deptno
/**/
select e.*,d.* from emp e,dept d where e.deptno = d.deptno
/*链表语句查询 想要查询什么就给出你想要查询的列名*/
select e.empno,e.ename,e.sal,e.mgr,d.loc from emp e inner join dept d on e.deptno = d.deptno
/*表连接是:通过两个表中相同的元素进行连接 emp.deptno = dept.deptno;如果两个表中没有相同的元素是,两个表就不能进行连接!*/
SELECT emp.ename,emp.job,emp.mgr,dept.dname,dept.loc from emp inner join dept on emp.deptno = dept.deptno
/*left join(左连接) 连表查询语句 查询出包括左表中所有记录和右连接中连接字段相等的记录 */
SELECT emp.ename,emp.job,emp.mgr,dept.dname,dept.loc from emp left join dept on emp.deptno = dept.deptno
/*right join (右连接) 连表查询语句 查询出包括右表中的所有记录和左表中联结字段相等的记录*/
SELECT emp.ename,emp.job,emp.mgr,dept.dname,dept.loc from emp right join dept on emp.deptno = dept.deptno
select max(sal) from emp
select min(sal) from emp
select sum(sal) from emp
select count(sal) from emp
select empno as "姓名" from emp where group by empno "姓名"
/*分组函数 group by 后面字段相同的一段*/
select deptno ,avg(sal) from emp group by deptno
/*GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组*/
select deptno,avg(sal)from emp group by deptno
select job,sum(sal)from emp GROUP BY job
/*我们一个月前来到优越国际,进入一个Java班级名称叫王萌的项目部!*/
/*链表语句 两种:inner join(等值连接) 查询出两个表中联结字段相等的行 */
/*连表查询不能用点星,这样会把两个表中所有的数据都连在一起,会多出一组一样的数据*/
select * from emp e inner join dept d on e.deptno = d.deptno
/**/
select e.*,d.* from emp e,dept d where e.deptno = d.deptno
/*链表语句查询 想要查询什么就给出你想要查询的列名*/
select e.empno,e.ename,e.sal,e.mgr,d.loc from emp e
inner join dept d on e.deptno = d.deptno
/*表连接是:通过两个表中相同的元素进行连接 emp.deptno = dept.deptno;如果两个表中没有相同的元素是,两个表就不能进行连接!*/
SELECT emp.ename,emp.job,emp.mgr,dept.dname,dept.loc from emp
inner join dept on emp.deptno = dept.deptno
/*left join(左连接) 连表查询语句 查询出包括左表中所有记录和右连接中连接字段相等的记录 */
SELECT emp.ename,emp.job,emp.mgr,dept.dname,dept.loc from emp
left join dept on emp.deptno = dept.deptno
/*right join (右连接) 连表查询语句 查询出包括右表中的所有记录和左表中联结字段相等的记录*/
SELECT emp.ename,emp.job,emp.mgr,dept.dname,dept.loc from emp
right join dept on emp.deptno = dept.deptno
/**/
select deptno 部门编号,sum(sal) 总工资,count(empno) 部门人数 from emp
GROUP BY deptno
SELECT COUNT(empno) 部门人数 from emp/**/
SELECT 新.*,d.dname,d.loc from (select deptno ,sum(sal) ,count(empno) from emp
GROUP BY deptno) 新 INNER JOIN dept d on 新.deptno = d.deptno
SELECT emp.empno,emp.ename,emp.job,dept.dname,dept.loc FROM emp
INNER JOIN dept on emp.deptno=dept.deptno
SELECT xin.deptno,dept.dname FROM (
SELECT deptno,ename,job FROM emp ) xin INNER JOIN dept
ON xin.deptno = dept.deptno
/**/
SELECT xin.dname,emp.deptno,SUM(emp.sal) from emp,dept
WHERE emp.deptno=dept.deptno GROUP BY dept.deptno
INNER JOIN dept on emp.deptno = dept.deptno
/**/
/* having 的用法*/
select * from emp;
SELECT * FROM dept;
SELECT deptno,ename,SUM(sal),MAX(sal),MIN(sal),COUNT(sal),AVG(sal) avg_sal from emp
group by deptno having avg_sal>1800 and avg_sal<3000
SELECT deptno,ename,SUM(sal),MAX(sal),MIN(sal),COUNT(sal),AVG(sal) avg_sal from emp
group by deptno having avg_sal>1800 or avg_sal<3000
/*having 后跟between智能用and来表示并且 加上or会报错*/
SELECT deptno,ename,SUM(sal),MAX(sal),MIN(sal),COUNT(sal),AVG(sal) avg_sal from emp
group by deptno having avg_sal BETWEEN 1800 and 3000
/**/
/*having 过滤 在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。*/
/*having与where的区别: having 后面可以跟函数 where后面不能跟函数*/
SELECT ename,sum(sal) from emp
GROUP BY ename having SUM(sal)<2000
/*分页
MySQL:利用关键字:limit
MySQLserver:利用关键字top
Oracle:利用关键字rownum
格式:select*from(select rownum r,a.* from(?)a)where r between?and?
第一个问号:指的是要查询的SQL语句(包含排序)
第二个问号:指的是分页查询的起始行号;
第三个问号:指的是分页查询的结束行号
*/
SELECT * FROM emp ORDER BY sal desc LIMIT 4,10
——————————————————————————————————————————————————————————————————————————————————————————————————————
- JDBC连接数据库:
- •创建一个以JDBC连接数据库的程序,包含7个步骤:
- 1、加载JDBC驱动程序:
- 在连接数据库之前,首先要加载想要连接的数据库的驱动到JVM(Java虚拟机),
- 这通过java.lang.Class类的静态方法forName(String className)实现。
- 例如:
- try{
- //加载MySql的驱动类
- Class.forName("com.mysql.jdbc.Driver") ;
- }catch(ClassNotFoundException e){
- System.out.println("找不到驱动程序类 ,加载驱动失败!");
- e.printStackTrace() ;
- }
- 成功加载后,会将Driver类的实例注册到DriverManager类中。
- 2、提供JDBC连接的URL
- •连接URL定义了连接数据库时的协议、子协议、数据源标识。
- •书写形式:协议:子协议:数据源标识
- 协议:在JDBC中总是以jdbc开始
- 子协议:是桥连接的驱动程序或是数据库管理系统名称。
- 数据源标识:标记找到数据库来源的地址与连接端口。
- 例如:(MySql的连接URL)
- jdbc:mysql:
- //localhost:3306/test?useUnicode=true&characterEncoding=gbk ;
- useUnicode=true:表示使用Unicode字符集。如果characterEncoding设置为
- gb2312或GBK,本参数必须设置为true 。characterEncoding=gbk:字符编码方式。
- 3、创建数据库的连接
- •要连接数据库,需要向java.sql.DriverManager请求并获得Connection对象,
- 该对象就代表一个数据库的连接。
- •使用DriverManager的getConnectin(String url , String username ,
- String password )方法传入指定的欲连接的数据库的路径、数据库的用户名和
- 密码来获得。
- 例如:
- //连接MySql数据库,用户名和密码都是root
- String url = "jdbc:mysql://localhost:3306/test" ;
- String username = "root" ;
- String password = "root" ;
- try{
- Connection con =
- DriverManager.getConnection(url , username , password ) ;
- }catch(SQLException se){
- System.out.println("数据库连接失败!");
- se.printStackTrace() ;
- }
- 4、创建一个Statement
- •要执行SQL语句,必须获得java.sql.Statement实例,Statement实例分为以下3
- 种类型:
- 1、执行静态SQL语句。通常通过Statement实例实现。
- 2、执行动态SQL语句。通常通过PreparedStatement实例实现。
- 3、执行数据库存储过程。通常通过CallableStatement实例实现。
- 具体的实现方式:
- Statement stmt = con.createStatement() ;
- PreparedStatement pstmt = con.prepareStatement(sql) ;
- CallableStatement cstmt =
- con.prepareCall("{CALL demoSp(? , ?)}") ;
- 5、执行SQL语句
- Statement接口提供了三种执行SQL语句的方法:executeQuery 、executeUpdate
- 和execute
- 1、ResultSet executeQuery(String sqlString):执行查询数据库的SQL语句
- ,返回一个结果集(ResultSet)对象。
- 2、int executeUpdate(String sqlString):用于执行INSERT、UPDATE或
- DELETE语句以及SQL DDL语句,如:CREATE TABLE和DROP TABLE等
- 3、execute(sqlString):用于执行返回多个结果集、多个更新计数或二者组合的
- 语句。
- 具体实现的代码:
- ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ;
- int rows = stmt.executeUpdate("INSERT INTO ...") ;
- boolean flag = stmt.execute(String sql) ;
- 6、处理结果
- 两种情况:
- 1、执行更新返回的是本次操作影响到的记录数。
- 2、执行查询返回的结果是一个ResultSet对象。
- • ResultSet包含符合SQL语句中条件的所有行,并且它通过一套get方法提供了对这些
- 行中数据的访问。
- • 使用结果集(ResultSet)对象的访问方法获取数据:
- while(rs.next()){
- String name = rs.getString("name") ;
- String pass = rs.getString(1) ; // 此方法比较高效
- }
- (列是从左到右编号的,并且从列1开始)
- 7、关闭JDBC对象
- 操作完成以后要把所有使用的JDBC对象全都关闭,以释放JDBC资源,关闭顺序和声
- 明顺序相反:
- 1、关闭记录集
- 2、关闭声明
- 3、关闭连接对象
- if(rs != null){ // 关闭记录集
- try{
- rs.close() ;
- }catch(SQLException e){
- e.printStackTrace() ;
- }
- }
- if(stmt != null){ // 关闭声明
- try{
- stmt.close() ;
- }catch(SQLException e){
- e.printStackTrace() ;
- }
- }
- if(conn != null){ // 关闭连接对象
- try{
- conn.close() ;
- }catch(SQLException e){
- e.printStackTrace() ;
- }
- }
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
package com.yy.jdbc.dao;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import com.yy.jdbc.po.User;
import com.yy.jdbc.uitl.DBConnectionUtil;
public class UserDao {
public UserDao(){
}
private static UserDao userDao = new UserDao();
public static UserDao getInstance(){
return userDao;
}
public boolean saveUser(User user){
boolean flag = false;
Connection conn = DBConnectionUtil.getConnection();
Statement st = null;
String sql = "insert into user(username,sex,password) values ('"
+ user.getUsername() + "',"
+ user.getSex() + ",'"
+ user.getPassword() + "')";
try {
st = conn.createStatement();
int rows = st.executeUpdate(sql);
if(rows != 0){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnectionUtil.closeAll(st, conn);
}
return flag;
}
public boolean updateUser(Map<String, Object> map){
boolean flag = false;
Connection conn = DBConnectionUtil.getConnection();
Statement st = null;
String sql = "update user set password = '"
+ (String)map.get("password") +"' where userId = " + (Integer)map.get("userId");
try {
st = conn.createStatement();
int rows = st.executeUpdate(sql);
if(rows != 0){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnectionUtil.closeAll(st, conn);
}
return flag;
}
//往SQL数据库里面添加数据语句
public static void main(String[] args) {
User user = new User();
user.setUsername("王萌2");
user.setSex(1);
user.setPassword("123456");
System.out.println(UserDao.getInstance().saveUser(user) ? "保存成功" : "保存失败");;
// Map<String, Object> map = new HashMap<String, Object>();
// map.put("password", "123qwe");
// map.put("userId", 24);
// System.out.println(UserDao.getInstance().updateUser(map) ? "修改成功" : "修改失败");;
}
}
package com.yy.jdbc.po;
public class User {
private int userId;
private String username;
private int sex;
private String password;
private int flag;
//把上面的变量进行封装:
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getFlag() {
return flag;
}
public void setFlag(int flag) {
this.flag = flag;
}
}
package com.yy.jdbc.uitl;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBConnectionUtil {
private static String jdbcDriver = "";
private static String jdbcURL = "";
private static String jdbcUser = "";
private static String jdbcPassword = "";
static {
InputStream is = null;
is = DBConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties p = new Properties();
try {
p.load(is);
jdbcDriver = p.getProperty("jdbcDriver");
jdbcURL = p.getProperty("jdbcURL");
jdbcUser = p.getProperty("jdbcUser");
jdbcPassword = p.getProperty("jdbcPassword");
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn = null;
try {
Class.forName(jdbcDriver);
conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(Statement st,Connection conn){
try {
if(st != null)
st.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(conn != null)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
System.out.println(DBConnectionUtil.getConnection());;
}
}