DQL操作:查询
语法:
select
字段列表
from
表名列表
where
条件列表
group by
分组手段
having
分组后条件
order by
排序字段
limit
分页限定
1.基础查询
1.1:查询多个字段
select 列名1,列名2 ……from 表名;
select *from 表名;(查询所有数据,别用)
1.2:去除重复记录
select distinct 字段列表 from 表名;
1.3:起别名
as:(as 也可以省略)
例如:select math as 数学成绩 from student
2.分页查询
3.条件查询
3.1:条件查询语法
select 字段列表 from 表名 where 条件列表;
3.2:条件
符号 功能
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
<> 或 != 不等于
between...and... 在某个范围之内(都包含)
in(...) 多选一
like 占位符 模糊查询 _单个任意字符 %多个任意字符
is null 是null
is not null 不是null
and 或 && 并且
or 或 || 或者
not 或 ! 非,不是
4.分组查询
4.1聚合函数:将一列数据作为一个整体,进行纵向计算。
4.1.1聚合函数分类:
函数名 功能
count(列名) 统计数量(一般选用不为null的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值
4.1.2聚合函数语法:
select 聚合函数名(列名) from 表
!!null值不参与所有聚合函数运算!!
4.2分组查询语法:
select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名[having 分组后条件过滤];
!!分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义!!
4.3where和having 的区别:
4.3.1:执行时机不同:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
4.3.2可判断的条件不一样:where不能对聚合函数进行判断,having可以。
执行顺序:where>聚合函数>having
5.排序查询
5.1语法:
select 字段列表 from 表名 order by 排序字段名1 [排序方式1],排序字段名2[排序方式2]......;
5.2排序方式:
ASC:升序排列(默认)
DESC:降序排列
!!如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序!!
(比如想要优先按照年龄排序,如果年龄相同就按照成绩排列。)
6.分页查询
6.1语法
select 字段列表 from 表名 limit 起始索引 , 查询条目数;
起始索引=(页码-1)*每页显示的条数
6.2:
·分页查询limit 是MYSQL数据库的方言
·Oracle 分页查询使用rownumber
·SQL Server分页查询使用top
约束:
约束是作用于表中列上的规则,用于限制加入表的数据。
约束的存在保证了数据库中数据的正确性、有效性和完整性。
分类:
种类 描述 关键字
非空约束 保证列中所有数据不能有null值 NOT NULL
唯一约束 保证列中所有数据各不相同 UNIQUE
主键约束 主键是一行数据的唯一标识,要求非空且唯一 RIMARY KEY
检查约束 保证列中的值满足某一条件 CHECK
默认约束 保存数据时,未指定值则采用默认值 DEFAULT
外键约束 外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性 FOREIGN KEY
注意:MYSQL不支持检查约束。
1.语法:
1.1非空约束:
1.1.1:
/*创建表时添加非空约束*/
create table 表名(
列名 数据类型 NOT NULL,
……
);
1.1.2:/*建完表后添加非空约束*/
alter table 表名 modify 字段名 数据类型 NOT NULL;
1.2删除约束:
alter table 表名 modify 字段名 数据类型。
1.2唯一约束:
1.2.1:
/*创建表时添加唯一约束*/
create table 表名(
列名 数据类型 UNIQUE [AUTO_INCREMENT],
/*AUTO_INCREMENT:当不指定时自动增长*/
……
);
create table 表名(
列名 数据类型,
……
[CONSTRAINT] [约束名称] UNIQUE(列名)
);
1.2.2:
/*建完表后添加唯一约束*/
alter table 表名 modify 字段名 数据类型 UNIQUE;
1.2.3删除约束:
alter table 表名 drop index 字段名;
1.3主键约束:
1.3.1:
/*创建表时添加主键约束*/
create table 表名(
列名 数据类型 PRIMARY KEY [AUTO_INCRMENT],
……
);
create table 表名(
列名 数据类型;
[CONSTRAINT][约束名称] PRIMARY KEY(列名)
);
1.3.2:
/*建完表后添加主键约束*/
alter table 表明 ADD PRIMARY KEY(字段名);
1.3.3删除约束:
alter table drop primary key;
1.4默认约束:
1.4.1
/*创建表时添加默认约束*/
create table 表名(
列名 数据类型 DEFAULT 默认值,
……
);
1.4.2
/*建完表后添加默认约束*/
alter table 表名 alter 列名 set default 默认值;
1.4.3删除约束:
alter table 表名 alter 列名 drop DEFAULT;
1.5外键约束:
1.5.1:
/*创建表时添加外键约束*/
create table 表名(
列名 数据类型,
……
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES(主表列名称)
);
1.5.2:
/*建完表后添加外键约束*/
alter table 表名 add constraint 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
1.5.3:删除约束
alter table 表名 drop FOREIGN KEY 外检名称;
数据库设计:
1.概念:
1.1:数据库设计就是根据业务系统的具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的存储模型。
1.2:建立数据库中的表结构以及表与表之间的关联关系的过程。
1.3:有哪些表?表中的字段有哪些?表与表之间的关系?
2.设计步骤:
2.1:需求分析(数据是什么?数据有哪些属性?数据与属性的特点是什么?)
2.2:逻辑分析(通过ER图对数据进行逻辑建模,不需要考虑我们所选用的数据库管理系统)
2.3:物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
2.4:维护设计(①.对新的需求进行建表;②.表优化)
表关系:
1.一对一:
例如:一个人对应一张身份证。
2.一对多:
一般为从属关系,比如一个部门中有多个员工,多个员工组成一个部门。
最终有:一个部门对应多个员工,一个员工对应一个部门。
3.多对多:
例如:商品与订单:一个商品出现在多个订单里,一个订单里也包含多个商品。
实现方式:借助第三张中间表,中间表至少包含两个外键,分别关联两方主键。
事务:
1.数据库的事务(Transaction)是一种机制,一个操作序列,包含了一组数据库操作命令。
2.事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库要么同时成功,要么同时失败。
3.事务是一个不可分割的工作逻辑单元。
步骤:
开启事务->提交事务/回滚事务
1.开启事务:start transaction/begin(意味接下来的操作并不真正执行,只有提交才有效)
2.提交事务:commit(操作正常进行就提交生效)
3.回滚事务:rollback(中途出现异常就撤销回滚)。
事务特征:
1.原子性(Atomicity):事务是不可分割的最小单位,要么同时成功,要么同时失败。
2.一致性(Consistency):事务完成时,必须使所有的数据保持一致状态。
3.隔离性(Isolation):多个事务之间,操作的可见性。
4.持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
注意:MYSQL事务默认自动提交
-- 查看事务的默认提交方式
select @@autocommit;
-- 1 自动提交 0 手动提交
-- 修改事务提交方式
set@@autocommit = 0;
JDBC(Java DataBase Connectivity)java数据库连接:使用Java语言操作关系型数据库的一套API
1.本质:
1.1.官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口。
1.2.各个数据库厂商去实现这套接口,提供数据库驱动jar包。
1.3.我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类。
2.JDBC好处:
2.1:各数据库厂商使用相同的接口,java代码不需要针对不同的数据库分别开发。
2.2:可随时替换底层数据库,访问数据库的java代码基本不变。
3.步骤:
3.1.导入jar包。
3.2:注册驱动。
Class.forName("com.mysql.jdbc.Driver");
3.3:获取连接。
Connection conn = DriverManager.getConnection(url,username,password);
3.4:定义SQL语句。
String sql = "update...";
3.5:获取执行SQL对象。
Statement stmt = conn.createStatement();
3.6:执行SQL。
stmt.executeUpdate(sql);
3.7:处理返回结果。
3.8:释放资源。
DriverManager(驱动管理类):
作用:1.注册驱动。2.获取数据库连接。
1.1:Class.forName("com.mysql.jdbc.Driver");
Driver类源码:
static {
try {
DriverManager.registerDriver(new Driver());
}catch (SQLException var 1){
throw new RuntimeException("Can't register driver!");
}
}
(MYSQL5之后的驱动包,可以省略注册驱动的步骤;自动加载jar包中META-INF/services/java.sql.Driver文
件中的驱动类)
2.获取连接:
2.1:url:链接路径
语法:jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2...
示例:jdbc:mysql://127.0.0.1:3306/db1
细节:
如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以简写为:jdbc:mysql:///数据库名称?参数键值对
配置useSSL=false参数,禁用安全连接方式,解决警告提示。
2.2 user:用户名。
2.3:password:密码。
Connection(数据库连接对象)
作用:1.获取执行SQL对象。2.管理事务。
1.获取执行SQL的对象:
1.1普通执行SQl对象:
Statement createStatement()
1.2预编译SQL的执行SQL对象:防止SQL注入
PreparedStatement prepareStatement(sql)
1.3执行存储过程的对象(不常用)
CallableStatement prepareCall(sql)
2.事务管理
JDBC 事务管理: Connection接口中定义了3个对应的方法。
2.1开启事务:setAutoCommit(boolean autoCommit):true为自动提交事务;false为手动提交事务,即开启事务。
2.2提交事务:commit()
2.3回滚事务:rollback()自然而然用try/catch实现。
Statement
作用:执行SQL语句。
执行:int executeUpdate(sql):执行DML、DDL语句,
返回值:(1)DML语句影响的行数;(2)DDL语句执行后,执行成功也可能返回0;
ResulSet executeQuery(sql):执行DQL语句
返回值:ResulSet结果集对象。(封装了DQL查询语句的结果)
获取查询结果:
boolean next():(1)将光标从当前位置向前移动一行(2)判断当前行是否为有效行
返回值:
true:有效行,当前行有数据
false:无效行,当前行没有数据
xxx getXxx(参数):获取数据
xxx:数据类型;如int getInt(参数);String getString(参数)
参数:
int:列的编号,从1开始。
String:列的名称。
使用步骤:
1.游标向下移动一行,并判断该行是否有数据:next()
2.获取数据:getXxx(参数)
//循环判断游标是否是最后一张末尾
while(rs.next()){
//获取数据
rs.getXxx(参数);
}
PreparedStatement
作用:1.预编译SQL语句并执行:预防SQL注入问题。
SQL注入:是通过操作输入来修改实现定义好的sql语句,用以达到执行代码对服务器进行攻击的方法。
1.获取PreparedStatement对象
//SQL语句中的参数值,使用?占位符替代。
String sql =”select * from user where username = ? and password = ?“;
//通过Connextion对象获取,并传入对应的sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);
2.设置参数值
PrepareStatement对象:setXxx(参数1,参数2):给?赋值
Xxx:数据类型;如setInt(参数1,参数2)
参数:
参数1:?的位置编号,从1开始。
参数2:?的值。
执行SQL
executeUpdate();/executeQuery();:不需要再传递sql