SQL数据库基础知识
一、数据库的基础
1.数据库的英文单词 : DataBase 简称: DB
2.数据库的特点:
1.持久化存储数据的, 其实数据库就是一个文件系统
2.方便存储和管理数据
3.使用统一的方式操作数据库
3.MySQL数据库软件
配置:
MySQL软件启动
1.手动
2.cmd
3.使用管理员打开cmd:
1.net start mysql:启动mysql的服务
2.net stop mysql:关闭mysql服务
MySQL登录
1.mysql -uroot -p密码
2.mysql -hip -uroot -p连接目标的密码
3.mysql
MySQL退出
1.exit
2.quit
MySQL目录结构
1. MySQL安装目录:basedir="D:/develop/MySQL/"
配置文件 my.ini
2. 数据目录:datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
几个概念
1.数据库:文件夹
2.表:文件
3.数据:数据
二、SQL
1.SQL(Structured Query Language):结构化查询语言
2.SQL通用语法
1. SQL语句可以单行或多行书写,以分号结尾.
2. 可使用空格和缩进来增强语句的可读性
3. MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写
4. 3种注释:
单行注释:
多行注释:
3.SQL分类
1)DDL(Data Definition Language)数据定义语言
(对数据库和表可以进行创建、删除和修改)
用来定义数据库对象:数据库、表、列等。关键字:create,drop,alter等
2DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改.关键字:insert,delet,update等
3)DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据).关键字:select,where等
4)DCL(Data Control Language)数据控制语言(了解)
用来定义数据库的访问权限和安全级别,及创建用户.关键字:GRANT,REVOKE等
1、DDL:操作数据库、表
1.操作数据库:CRUD
1.c(Create):创建
创建数据库: create database 数据库名称;
创建数据库,判断不存在,再创建: create database if not exists 数据库名称;
创建数据指定字符集: create database 数据库名称 character set 字符集名;
2.R(Retrieve):查询
查询所有的数据库的名称: show databases;
查询某个数据库的字符集:查询某个数据库的创建语句show create database 数据库名称;
3.U(Update):修改
修改数据库的字符集: alter database 数据库名称 character set 字符集名称;
4.D(Delete):删除
删除数据库: drop database 数据库名称;
判断数据库存在,存在在删除: drop database if exists 数据库名称;
5.使用数据库
查询当前正在使用的数据库名称: select database();
使用数据库: use 数据库名称;
2.操作表
1.c(Create):创建
语法:
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
0.
....
列名n 数据类型n
);
*注意:最后一列,不需要加逗号*
数据库类型:
1.int:整数类型: age int;
2.double:小数类型: score double(5,2)
3.date:日期,只包含年月日,yyyy-MM-dd
4.datetime:日期,只包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5.timetamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值
6.varchar:字符串name vachar(20):姓名最大20个字符
复制表: create table 表名 like 被复制的表名;
2.R(Retrieve):查询
查询某个数据库中的所有的表名称: show tables;
查询表结构: desc 表名;
3.U(Update):修改
1.修改表名: alter table 表名 rename to 新的表名;
2.修改表的字符集: alter table 表名 character set 字符集名称;
3.添加一列: alter table 表名 add 列名 数据类型;
4.修改列名 类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;
5.删除列: alter table 表名 drop 列名;
4.D(Delete):删除
删除表: drop table 表名;
判断表存在,存在再删除: drop table if exists 表名;
2、DML:增删改表中数据
1.添加数据:
语法: insert into 表名(列名1,列名2,...列名n) values(值1,值2...值n);
注意:
1.列名和值要一一对应
2.如果表名后,不定义列名,则默认给所有列添加值.
3.除了数字类型,其他类型需要使用引号(单双都可以)引起来
2.删除数据:
语法: delete from 表名[where条件];
注意:
1. 如果不加条件,则删除表中所有记录。
2. 如果要删除所有记录
1. delete from 表名;不推荐使用。有多少条记录就会执行多少次删除操作
2. TRUNCATE TABLE表名;推荐使用,效率更高先删除表,然后再创建一张一样的表
3.修改数据:
语法: update 表名 set 列名1=值1,列名2=值2,...[where 条件]
3、DQL:查询表中的记录
1.语法:
select:字段列表;
from:表名列表;
where:条件列表;
group by:分组字段
having:分组之后的条件
order by:排序
limit:分页限定
2.基础查询:
1.多个字段的查询:select 字段名1,字段名2... from 表名;
查询所有字段:select * from 表名;
2.去除重复:distinct
3.计算列
一般可以使用四则运算计算一些列的值.
ifnull(表达式1,表达式2):null参与的运算,计算结果都为null
表达式1:哪个字段需要判断是否为null。
表达式2:如果该字段为null后的替换值。
4.起别名
as:as也可以省略
3.条件查询(where):
1.where子句后跟的条件
2.运算符
1. > 、< 、<= 、>= 、= 、<>(不等于)
2.BETWEEN...AND :在一个范围之内
3.IN( 集合):集合表示多个值,使用逗号分隔
4.LIKE:模糊查询
占位符:
1._:单个任意字符
2.%:多个任意字符
5.IS NULL(判断某值为null值)
6.IS not NULL(判断某值不为null值)
7and (推荐) 或 &&(不推荐)
8.or (推荐) 或 || (不推荐)
9.not(推荐) 或 ! (不推荐)
4.排序语句(order by):
1.语法:order by 子句;
order by 排序字段1 排序方式1,....
2.排序方式:
1.ASC:升序,默认
2.Desc:降序
5.聚合函数:将一列数据作为一个整体,进行纵向计算(写在SELECT和FROM之间)
1.count:计算个数,选择非空的列:主键;count()
2.max:计算最大值
3.min:计算最小值
4.sum:计算和
5.avg:计算平均值
注意:聚合函数计算会排除null的值;
解决方案:
1.选择不包含null的
2.ifnull函数
6.分组查询(group by,having):
语法:group by 分组字段
注意:
1.分组之后查询的字段:分组字段,聚合函数
2.where和having的区别:
1.where在分组之间进行了限定,不满足条件,不参与分组
2.having在分组之后进行限定,不满足条件,则不会被查询
3.where后不可以跟聚合函数进行判断
4.having后可以跟聚合函数进行判断
7.分页查询(limit):
1.语法:limit 开始的索引,每页查询的条数;
2.公式:开始的索引 = (当前页面-1) * 每页显示的条数;
3.limit是一个mysql的"方言";
4、DCL:管理用户,授权
1. 管理用户
1) 添加用户:
语法:CREATE USER '用户名'@ '主机名' IDENTIFIED BY '密码';
2) 删除用户:
语法:DROP USER '用户名'@ '主机名';
3) 修改用户密码:
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR '用户名'@ '主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@ 'localhost' = PASSWORD('123');
mysql中忘记了root用户的密码?
1. cmd
需要管理员运行该cmd
2. 使用无验证方式启动mysql服务: mysqld
3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器,手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。
4.)查询用户:
1. 切换到mysql数据库
USE mysql;
2. 查询user表
SELECT * FROM USER;
通配符: % 表示可以在任意主机使用用户登录数据库
2. 权限管理:
1. 查询权限:
查询权限
SHOW GRANTS FOR '用户名'@ '主机名';
SHOW GRANTS FOR 'lisi'@ '%';
2. 授予权限:
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@ '主机名';
例子:GRANT ALL ON *.* TO 'zhangsan'@ 'localhost';
3. 撤销权限:
撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@ '主机名';
REVOKE UPDATE ON db3.account FROM 'lisi'@ '%';
三、约束
概念: 对表中的数据进行限定,保证数据的正确性,有效性和完整性.
分类:
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key
1、非空约束(not null)
概念:某一列的值不能为空
1.创建表时添加约束:
列名 数据类型 not null
2.创建表完后,添加非空元素:
Alter table 表名 modify 列名 数据类型 not null;
3.删除已添加的非空约束:
Alter table 表名 modify 列名 数据类型;
2、唯一约束(unique)
概念: 某一列的值不能重复
1.创建表时,添加唯一约束:
列名 数据类型 unique;
2.删除唯一约束:
alter table 表名 drop index 列名;
3.在表创建完后,在添加唯一约束
alter table 表名 Modify 列名 数据类型 unique;
3、主键约束(primary key)
1.注意:
1.含义:非空且唯一
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识
2.在创建表时,添加主键约束:
列名 数据类型 primary key,
3.删除主键:
alter table 表名 drop primary key;
4.创建完表后,添加主键约束:
alter table 表名 modify 列名 数据类型 Primary key;
5.自动增长:
1.概念:如果某一列是数值类型,使用 auto_increment可以来完成值的自动增长
2.在创建表时,添加主键约束,并完成主键自增长:
列名 数据类型 primary key auto_increment,
3.删除自动增长:
alter table 表名 modify 列名 数据类型;
4.创建完表后,添加自动增长:
alter table 表名 modify 列名 数据类型 auto_increment;
4、外键约束(foreign key)
概念:让表于表产生关系,从而保证数据的正确性
1.在创建表时,可以添加外键约束:
语法:外键列: constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称),
2.删除外键约束:
alter table 表名 drop foreign key 外键名称
3.创建表之后,添加外键约束:
alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称);
4.级联操作:(谨慎操作)
1.添加级联操作:
在设置外键时添加级联操作
alter table 表名 add constraint 外键名称 foreign key (外键列名称) on upDate cascade on delete cascade
2.分类:
1.级联更新: on upDate cascade
2.级联删除: on delete cascade
四、数据库的设计
1. 多表之间的关系
1. 一对多(多对一):
实现方式:在多的一方建立外键,指向一的一方的主键。
2. 多对多:
实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表
的主键
3. 一对一(了解):
实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
2. 数据库设计的范式
1.概念:设计数据库时,需要遵循的一些规范.要遵循后面的范式要求,必须先遵循前面的所有范式要求
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规
范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)
和第五范式(5NF,又称完美范式)。
2.分类:
1.第一范式(1NF):每一列都是不可分割的原子数据项
2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主元素对主码的部分函数依赖)
几个概念:
1. 函数依赖:A
例如:学号
2. 完全函数依赖:A
例如:(学号,课程名称)
3. 部分函数依赖:A
例如:(学号,课程名称)
4. 传递函数依赖:A
值可以确定唯一C属性的值,则称C传递函数依赖于A
例如:学号
5. 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)
1.主属性:码属性组中的所有属性
2.非主属性:除过码属性组的属性
3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上传递依赖)
五、数据库的备份和还原
1. 命令行:
备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
还原:
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径
2. 图形化工具:
六、多表查询
查询语句:
select
列名列表
from
表名列表
where....
笛卡尔积:
1.有两个集合A,B 取这两个集合的所有组成情况
2.要完成多表查询,需要消除无用的数据
多表查询的分类:
1.内连接查询
1.隐式内连接:使用where条件消除无用数据
2.显式内连接: inner可以省略
语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件;
3.内连接查询:
1.从哪个表中查询数据
2.条件是什么
3.查询那些字段
2.外连接查询 outer可以省略
1.左外连接:
语法: select 字段列表 from 表1 left [outer] join 表2 on 条件;
查询的是左表所有数据以及其交集部分.
2.右外连接:
语法: select 字段列表 from 表1 right [outer] join 表2 on 条件;
查询的是右表所有数据以及其交集部分.
3.子查询
概念:查询中嵌套查询,称嵌套查询为子查询.
子查询的不同情况:
1.子查询的结果是单列的:
子查询可以作为条件,使用运算符去判断.运算符:> >= < <= =
2.子查询的结果是多行单列的:
子查询可以作为条件,使用运算符in来判断
3.子查询的结果是多行多列的:
子查询可以作为一个虚拟表参与查询
七、事务
1. 事务的基本介绍
1.概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作同时成功或失败。
2. 操作:
1. 开启事务: start transaction;
2. 回滚:rollback;
3. 提交:commit;
3. MySQL数据库中事务默认自动提交
事务提交的两种方式:
自动提交:
mysql就是自动提交的,一条DML(增删改)语句会自动提交一次事务。
手动提交:
Oracle 数据库默认是手动提交事务
需要先开启事务,再提交
修改事务的默认提交方式:
查看事务的默认提交方式:SELECT @@autocommit;
修改默认提交方式: set @@autocommit = 0;
2. 事务的四大特征:
1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
3. 隔离性:多个事务之间。相互独立。
4. 一致性:事务操作前后,数据总量不变
3. 事务的隔离级别(了解)
概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题
存在问题:
1. 脏读:一个事务,读取到另一个事务中没有提交的数据
2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
隔离级别:
1. read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
2. read committed:读已提交 (Oracle)
产生的问题:不可重复读、幻读
3. repeatable read:可重复读 (MySQL默认)
产生的问题:幻读
4. serializable:串行化
可以解决所有的问题
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
数据库查询隔离级别:select @@tx_isolation;
数据库设置隔离级别:set global transaction isolation level 级别字符串;
JDBC
一、基本概念
1.概念:Java DataBase Connectivity Java数据库连接, Java语言操作数据库
2.本质:其实就是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口.各个数据库厂商实现这套接口,提供数据库驱动jar包.
我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类.
3.快速入门:
步骤:
1.导入驱动jar包mysql-connector-java-5.1.37-bin.jar
1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
2.右键-->Add As Library
2.注册驱动
3.获取数据库连接对象 Connection
4.定义sql
5.获取执行sql语句的对象Statement
6.执行sql,接受返回结果
7.处理资源
8.释放资源
1) DriverManager:驱动管理对象
功能:
1.注册驱动:告诉程序该使用哪一个数据库驱动jar
static void registerDriver(Driver driver):注册驱动程序DriverManager
写代码使用: Class.forName("com.mysql.jdbc.Driver");
通过查看源码发现:在 com.mysql.jdbc.Driver 类中存在静态代码块
注意:mysql5之后的驱动jar包可以省略注册驱动的步骤。
2.获取数据库连接:
方法: static Connection getConnection(String url,String user,String password);
参数:
url:指定连接的路径: jdbc:mysql://ip地址(域名):端口号/数据库名称
如果是本机,并且端口号是3306,则可以简写为:jdbc:mysql:/
user:用户名
password:密码
2) Connection:数据库连接对象
功能:
1.获取执行sql的对象:
Statement createStatement();
PrepareStatement prepareStatement(String sql);
2.管理事务:
开始事务: setAutoCommit(boolean autoCommit):调用方法设置参数为false
提交事务: commit();
回滚事务: rollback();
3) Statement:执行sql的对象
执行sql:
1. boolean execute(String sql):可以执行任意的sql (了解)
2. int executeUpdate(String sql):(常用DML语句)
执行DML(insert、update、delete)语句、DDl(create,alter、drop)语句
返回值是影响的行数,返回值>0则执行成功反之失败.
3. ResultSet executeQuery(String sql):执行DQL(select)语句
4) ResultSet:结果集对象,封装查询结果
boolean next(): 游标向下移动一行,判断当前是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true,
getXxx(参数):获取数据
Xxx代表数据类型
参数:
1.int 代表列编号,从1开始
2.String :代表列名称.
5) PreparedStatement:执行sql的对象
1.SQL注入问题:在拼接sql时,有一些sql的特殊关键字参与字符串的拼接.会造成问题
2.解决sql注入的问题:使用PreparedStatement对象来解决
3.预编译的SQL:参数使用?作为占位符
4.步骤:
1.导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
2.注册驱动
3.获取数据库连接对象 Connection
4.定义sql
注意:sql的参数使用?作为占位符。
如:select * from user where username = ? and password = ?;
5.获取执行sql语句的对象 PreparedStatement
Connection.prepareStatement(String sql)
6.给?赋值:
方法: setXxx(参数1,参数2)
参数1:?的位置编号 从1 开始
参数2:?的值
7.执行sql,接受返回结果,不需要传递sql语句
8.处理结果
9.释放资源
5. 注意:后期都会使用PreparedStatement来完成增删改查的所有操作
1. 可以防止SQL注入
2. 效率更高
二、抽取JDBC工具类 : JDBCUtils
目的:简化书写
分析:
1. 注册驱动也抽取
2. 抽取一个方法获取连接对象
需求:不想传递参数(麻烦),还得保证工具类的通用性。
解决:配置文件
jdbc.properties
url=
user=
password=
3. 抽取一个方法释放资源
三、JDBC控制事务:
1.事务:一个包含多个步骤的业务操作。如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败。
2.操作:
1.开启事务
2.提交事务
3.回滚事务
3.使用Connection对象来管理事务
java
开启事务: setAutoCommit(boolean autoCommit) :调用该方法设置参数为false,即开启事务
在执行sql之前开启事务
提交事务: commit()
所有sql都执行完提交事务
回滚事务: rollback()
在catch中回滚事务
四、JDBC连接池
1.概念:是一个容器,存放数据库连接的容器,是sun公司提供了连接吃的规范(DataSource:接口)
2.好处:
1.节约资源
2.用户访问高效
3.实现:
1.标准接口:DataSource Java小.sql包下的
1.方法:
1.获取连接:getConnection();
2.归还连接:Connection.close();如果连接对象Connection是从连接池获取,则close()不是关闭连接,而是归还连接
2.一般程序员不实现,由数据库厂商来说实现
1.C3P0:数据库连接池技术
2.Druid:数据库连接池实现技术,由阿里巴巴提供
1、C3P0:数据库连接池技术
步骤:
1.导入jar包 (两个) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar
2.定义配置文件
名称:c3p0.properties 或者 c3p0-config.xml
路径:直接将文件放在src目录下
3.创建核心对象:数据库连接池对象 ComboPooledDataSource
DataSource 对象名 = new ComboPooledDataSource();
4.获取连接: getConnection
2、Druid:数据库连接池实现技术
1.步骤:
1.导入jar包 druid-1.0.9.jar
2.定义配置文件:
是properties形式的
可以叫任意名称,可以放在任意目录下
3.加载配置文件。Properties
Properties 对象名称 = new Properties();
InputStream 对象名 = 当前类名.class.getClassLoader().getResourceAsStream("配置文件全称");
pro.load(对象名);
4.获取数据库连接池对象:通过工厂来获取 DruidDataSourceFactory
DataSource 对象名 = DruidDataSourceFactory.createDataSource(properties对象名称);
5.获取连接:getConnection
2.定义工具类:
1.定义一个类 JDBCUtils
2.提供静态代码块加载配置文件,初始化连接池对象
3.提供方法
1.获取连接方法:通过数据库连接池获取连接
2.释放资源
3.获取连接池方法
五、SpringJDBC(JdbcTemplate工具)
Spring 框架对JDBC的简单封装。提供了一个JDBCTemolate对象简化JDBC的开发
步骤:
1.导入5个包
2.创建JdbcTemplate对象,依赖于数据源DataSource
JdbcTemplate 对象名 = new JdbcTemplate(ds);
3. 调用JdbcTemplate的方法来完成CRUD的操作
1. update("sql语句");执行DML语句。增、删、改、语句
2. queryForMap("sql语句"):查询结果将结果封装为map集合,将列名作为key,将值作为value 将这条记录封装为一个map集合
注意:这个方法查询结果长度只是1
3. queryForlist("sql语句"):查询结果将结果集封装为list集合
注意:将每一条记录封装为Map集合,再将Map集合装载到list集合中
4. query("sql语句",new BeanPropertyRowMapper<类型>(类型.class)):查询结果将其封装为JavaBean对象
参数:RowMapper;
一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
new BeanPropertyRowMapper<类型>(类型.class)
5. queryForObject("sql语句",数据类型.class):查询结果,将结果封装为对象
一般用于聚合函数的查询