SQL通用语法:
1.SQL语句口可以单行或多行书写,以分号结尾
2.SQL语句可以使用空格/缩进来增强语句的可读性
3.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
4.注释:单行注释: --数值内容或者#注释内容(MySQL特有的)
SQL分类:
DDL Date Definition Language
数据定义语言,用来定义数据库对象(数据库,表,字段)
查询所有数 据库: show databases;
查询当前数据库: select database();
创建: creat database [ if not exists ]数据库名default charset 字符集
删除: drop database[if exists]数据库名
修改数据库的字符集: alter database 数据库名称 character set 字符集名称
使用: use 数据库名
查询当前数据库所有表: show tables;
查询表结构: desc 表名;
查询指定表的建表语句: show create table 表名;
创建表: create table 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
字段n 字段n类型[COMMENT 字段n注释]
)[comment 表注释];
修改表:
修改数据类型:
alter table 表名 modify 字段名 新数据类型(长度);
复制表:
create table 表名 like 被复制的表名
修改字段名和字段类型:
alter table 表名 change 旧字段名 新字段名 类型 (长度)comment 注释
删除字段: alter table 表名 drop 字段名;
修改表的字符集: alter table 表名 character set 字符集名称
添加一列: alter table 表名 add 列名 数据类型
删除列: alter table 表名 drop 列名
DML Date Manipulation Language
数据操作语言,用来对数据库表中的数据进行增删改
给指定字段添加数据:
insert into 表名(字段名1,字段名2,...)values(值1,值2,...);
给全部字段添加数据:
insert into 表名values(值1,值2...);
批量添加数据:
insert into 表名(字段名1,字段名2,...)values(值1,值2,...), (值1,值2,...)
insert into 表名values(值1,值2,...), (值1,值2,...), (值1,值2,...);
修改数据:
update表名set字段名1=值1,字段名2=值2,...[where条件]
删除数据:
delete from 表名[where 条件] 没有条件就删除整张表的数据
修改表名:
alter table 表明 rename to 新表名;
删除表:
drop table [if exists] 表名;
删除指定表,并重新创建该表: truncate table 表名;
DQL Date Query Language
数据查询语言,用来查询数据库中表的记录
分组查询:
select 字段列表from表名[where 条件]group by分组字段名[having 分组后过滤条件];
where和having区别:
执行时机不同:
where是分组之前进行过滤,不满足where条件,不参与分组;having是分组后的结果进行过滤
判断条件不同:
where不能对聚合函数进行判断,而having可以
排序查询:
select 字段
列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
排序方式只有两种:asc(升序) desc(降序)
如果是多字段排序,当第一个字段值相同的时候,才会根据第二个字段来进行排序
分页查询:
select 字段列表 from 表名 limit 起始索引,查询记录数;
起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数.
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
如果查询的是第一页数据,起始索引可以省略,直接简写成limit 查询记录数
聚合函数:将一列的数据作为一个整体,进行纵向的计算 计算的时候自动排除null
count:计算个数 一般选择主键
max:计算最大值
min:计算最小值
sum:计算和
avg:计算平均值
like : 模糊查询 _ 单个任意字符 % 多个任意字符
编写顺序: select from where gruop by having order by limit
执行顺序: from where group by having se lect order by limit
DCL Daye Control Language
数据控制语言,用来创建数据库用户,控制数据库的访问权限
查询用户:
use mysql;
select *from user;
创建用户:
create user ‘用户名’@’主机名’identified by’密码';
修改用户密码:
alter user ‘用户名’@’主机名’identified with mysql_native_password by’新密码’;
删除用户:
drop user ‘用户名’@’主机名’;
查询权限:
show grants for ‘用户名’@’主机名’;
授予权限:
grant 权限列表 on 数据库名.表名to ‘用户名’@’主机名’;
撤销权限:
revoke 权限列表 on 数据库名.表名from ‘用户名’@’主机名’;
函数: 使用方法:select 函数名();
字符串函数
concat(s1,s2,...sn) 字符串拼接,将s1,s2....sn拼接成一个字符串
lower(str) 将字符串str全部转换为小写
upper(str) 将字符串str全部转换为大写
trim(str) 去掉字符串头部和尾部的空格
lpad(str,n,pad) 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
rpad(str,n,pad) 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度
substring(str,start,len) 返回从字符串str从strat位置起的len个长度的字符串
数值函数
ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模
rand() 返回0-1内的随机数
round(x,y) 求参数x的四舍五入的值,保留y位小数
日期函数
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前时间和日期
year(date) 获取指定date的年份
month(date) 获取指定date的月份
day(date) 获取指定date的日期
date_add(date,interval expr type)
返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数
流程函数
if(value,t,f) 如果value为true,则返回t,否则返回f
ifnull(value 1, value 2) 如果value1不为空,返回value,否则返回value2
case when[ val1 ] then[ res1 ] ... else[ default ] end
如果val1为true,返回res1,...否则返回defalut默认值
case [ expr ] when[ val1 ] then[ res1 ] ... else[ default ] end
如果expr的值等于val1,返回res1,...否则返回default默认值
约束
非空约束 限制该字段的数据不能为null not null
唯一约束 保证该字段的所有数据都是唯一,不重复的 unique
主键约束 主键是一行数据中的唯一标识,要求非空且唯一 primary key
默认约束 保存数据的时候,如果未指定该字段的值,则采用默认值 default
检查约束 保证字段值满足某一个条件 check
外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 foreign key添加外键语法:
create table 表名(
字段名 数据类型,
.....
constraint外键名称 foreign key(外键字段名) references 主表 (主表列名)
);
alter table 表名add constraint外键名foreign key (外键字段名) reference主表(主表列名)
删除外键:
alter table 表名 drop foreign key 外键名称;
删除/更新行为
no action /restrict:
当在父表中删除或者更新对应的记录的时候,首先检查加记录是否有对应的外键,如果有则不允许删除或者更新
cascad:
当在父表中删除或者更新对应的记录的时候,首先检查该记录是否有对应的外键,如果有,
则也删除或者更新外键在子表中的记录
set null:
当在父表中删除对应的记录的时候,首先检查该记录是否有对应的外键,如果有则设置
子表中该外键值为null(这就要求该外键允许取null)
set default:
父表有变更的时候,子表将外键列设置成一个默认值(innodb不支持)
语法:
alter table 表名add constraint 外键名称foreign key(外键字段) references
主表名(主表名字段) on update 行为 on delete 行为
外键创建规则:
必须有主表才可以设置从表。
主表必须实际存在。
必须为主表定义主键。
外键列的数据类型必须和主键列的数据类型相同。
外键列的数量必须和主键列的数量相同。
外键可以不是外表中的主键，但必须和主表关联字段相对应。
主从表创建时，存储引擎必须是InnoDB。
多表查询
内连接:(交集)
隐式内连接:
select 字段列表 from 表1,表2 where 条件 ...;
显示内连接:
select 字段列表 from 表1 inner join 表2 on 连接条件....;
外连接:
左外连接:
select 字段列表 from 表1 left outer join 表2 on 条件....;
右外连接:
select 字段列表 from 表1 right outer join 表2 on 条件....;
自连接:
select ... from 表1 别名1 ,表1 别名2 where 条件 ...
联合查询: union, union all
select 字段列表 from 表a...
union (all)
select 字段列表 from 表b....;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
union all会将全部的数据直接合并到一起,union会对合并之后的数据进行去重
子查询:
sql语句中嵌套select语句,称为嵌套查询,又称为子查询
select * from t1 ehrer column1 =( select column1 from t2);
根据子查询结果不同分为:
标量子查询(子查询的结果为单个值)
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询(子查询结果为多行多列)
根据子查询位置分为:where之后 from之后 select之后
事务操作:
查看/设置事务提交方式
select @@autocommit;
set @@autocommit=0;
提交事务
commit;
回滚事务
rollback;
开启事务
start transaction 或 begin;
事务的四大特性:ACID
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
一致性(Consistency):事务完成的时候.必须使所有的数据都保持一致状态
隔离性(Isolation):数据库系统提供的隔离机制,保证事务不在受外部并发操作影响的
独立环境下运行
持久性(Durability):事务一旦提交或者回滚,他对数据库的数据的改变是永久的
事务的并发问题:
脏读:一个事务读到另外一个事务还没有提交的数据
不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在
事务隔离级别
Read uncommited 无法解决 脏读 不可重复读 幻读 (性能最好)
Read commited 无法解决 不可重复读 幻读
Repeatable Read(默认) 无法解决 幻读
Serializable 都可以解决 (性能最差)
查看事务隔离级别
select @@transaction_isolation;
设置事务隔离级别
set [session | global] transaction isolation level { 事务隔离等级 }
JDBC
具体例子请去jdbc项目中查看
jdbc是java连接数据库技术的统称
jdbc是由两部分组成:一是java提供的jdbc规范(接口)
二是各个数据库厂商的实现驱动jar包!
jdbc技术是一种典型的面向接口编程
jdbc的优势:
我们只需要学习jdbc接口规定方法,即可操作所有数据库软件,项目中期需要切换数据库我们只需要更新第三方的驱动jar包,不需要更改代码
DriverManager:驱动管理对象
注册驱动: com.mysql.jdbc.Driver 类中有注册驱动的代码 forName( );
获取数据库连接:
static Connection getConnection( String url,String user,String password)
Connection:数据库连接对象
获取执行sql的对象
Statement createstatement( )
PreparedStatemnet prepareStatemnet( String sql )
管理事务:
开启事务: setAutoCommit(boolean autoCommit):调用该方法设置参数为false
提交事务: commit( )
回滚事务: rollback( )
Statement:执行sql的对象
执行sql
boolean execute(String sql):可以执行任意的sql
int executeUpdate(String sql):执行DML,DDL
ResultSet executeQuery(String sql):执行DQL
ResultSet:结果集对象,封装查询结果
next( ):游标向下移动一行
getxxx(参数):获取数据
xxx代表数据类型 如: int getInt( ) , String getString( )
参数: int : 代表列的编号,从1开始, getString(1)
String:代表列的名称 getDouble(“*”)
PreparedStatement:执行sql的对象
Spring JDBC
Spring 框架对JDBC的简单封装,提供了一个JDBCTemplate对象简化JDBC的开发
步骤:
1.导入jar包
2.创建JdbcTemplate对象,依赖于数据源DataSource
JdbcTemplate template = new JdbcTemplate(ds);
3.调用JdbcTemplate的方法来完成CRUD的操作
update(): 执行DML语句.增删改语句
queryForMap(): 查询结果将结果集封装为map集合
queryForList(): 查询结果将结果集封装为list集合
query() : 查询结果,将结果封装为JavaBean对象
query的参数:RowMapper
一般我们使用BeanPropertyRowMapper实现类,可以完成数据到javabean的自动封装 new BeanPropertyRowMapper<类型>(类型.class)