数据库基础
无所待而游无穷也。
1. 分类:
-
关系型数据库(通过表与表之间的关系,行与列之间关系进行存储【SQL】):Mysql,Oracle,Sql Server,SqlLite
-
非关系型数据库(【NOSQL(not only sql)】通过对象存储,自身属性来决定):Redis,MongDB
-
数据库引擎
数据库引擎 | MYSIAM | INNODB |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约两倍 |
使用时期 | 默认使用 | 早些年使用 |
使用操作 | 节约空间,速度较快 | 安全性高,支持事务处理 |
在物理文件的位置 | 有 .frm(表结构的定义文件)和**.MYD(数据文件【data】)和*.MYI文件(索引文件【index】) | 在data数据目录下中只有一个*.frm文件,以及上级目录下的ibdata1文件 |
2. MySQL
2.1 注意
-
尽量不要 .exe,会有注册表;用压缩包安装,下载地址mysql压缩包安装下载
-
教程:解压,配置环境变量,新建 my.ini配置文件,mysql压缩包安装教程见其他博主
-
所有语句使用分号;结尾
2.2 数据库语言(CRUD【增删改查】)
- DDL【数据库定义语言】
- DML【数据库操作语言】
- DQL【数据库查询语言】
- DCL【数据库控制语言】
2.3 操作用户语句
-- 创建用户
create user xx IDENTIFIED BY 'root';
-- 删除用户
drop user xxy;
-- 修改当前账户密码
set PASSWORD = PASSWORD('123');
set PASSWORD = PASSWORD('root');
-- 修改指定用户密码
set PASSWORD for xx = PASSWORD('123');
-- 修改用户名
RENAME user xx to xxy;
-- 查找所有用户
SELECT *from mysql.`user`;
2.4 权限语句(主要DCL)
-- 用户授权
-- All PRIVILEGES 全部权限,除了给别人授权
GRANT all PRIVILEGES on *.* to xxy;
-- 撤销权限【删除权限】
REVOKE ALL PRIVILEGES on *.* from xxy;
-- 修改权限'通过撤销和重新授权进行更改'
-- 查询权限
show GRANTS;
SHOW GRANTS for xxy;
show GRANTS for root@localhost;
-- 刷新权限,每个语句执行后都要刷新权限,防止没有更新
flush PRIVILEGES;
2.5 操作数据库语句(主要DDL)
-- mysql不区分大小写
-- 操作数据库
-- #注释的本质是-- 单行注释
/*
多行注释
*/
-- 展示一个表的所有信息
describe tempdb;
-- 退出连接
exit;
-- 创建数据库
create database if not exists temp1;
-- 如果你的数据库名字和字段名是一个特殊字符,就需要带``(Tab键的上面)
-- 使用数据库
use `temp1`;
-- 删除数据库
drop database if exists temp1;
-- 修改数据库名字 最安全的做法是进入到mysql安装目录,修改库名;还可以导出改名再导入
-- 展示所有数据库
show databases;
-- 查看创建数据库的语句
show create database temp1;
-- 查询系统版本
SELECT VERSION();
-- 数据库属性类型
--无符号的整数,声明了该列不能为负数
Unsign
-- 不足的位置用0填充
zerofill
-- 自增,必须为整数
AUTO_INCREMENT
-- 空、非空
null、not null
-- 默认,不指定该列的值就有默认的值
default
-- 拓展
/*
每一个表都必须存在的五个字段,表示一个记录存在;
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.6 操作表语句
-- 创建简单的表
create table if not exists `tb_student`(
-- int(4)代表四位数字
`student_id` int(4) not null AUTO_INCREMENT COMMENT '学号',
`student_name` varchar(10) not null default '匿名' COMMENT '姓名',
`student_password` varchar(20) not null DEFAULT '12345' COMMENT '密码',
`student_gender` varchar(2) not null DEFAULT '男' comment '性别',
`student_brithday` datetime default null comment '出生日期',
`student_address` varchar(50) default null comment '地址',
primary key(`student_id`)
)AUTO_INCREMENT=0001 default charset=utf8;
-- 删除表
drop table if exists tb_user;
-- 修改表名
alter table tb_user rename as tb_user2;
-- 展示所有的表
show tables;
-- 查看创建表的语句
show create table tb_student;
2.7 操作表的字段语句
-- 字段数据类型
create table if not exists tb_user(
-- tinyint 非常小的数据 1个字节(Byte)=8位(bit)在MySQL中布尔类型实际上是TinyInt(1)
user_id1 tinyint null ,
-- smallint 比较小的数据 1个字节
user_id2 smallint null,
-- mediumint 中等大小的的数据 3个字节
user_id3 mediumint null,
-- int 标准的整数,4个字节,常用
user_id4 int null AUTO_INCREMENT comment ‘用户id’,
-- bigint 较大的整数 8个字节
user_id5 bigint,
-- integer 比较大的整数,12个字节
user_id6 integer null,
-- bit 最小的位 0和1,除0以外都是1
user_id7 bit(1) null,
-- real 是不精确的双精度浮点型 8字节
user_weight1 real null,
-- double 双精度浮点型 8字节(精度问题)
user_weight2 double null,
-- float 单精度浮点型 4字节 精度不高
user_weight3 float null,
/*字符串形式的浮点数 金融计算 16个字节
3代表有效位数,2代表小数点后的有效位数
*/
user_money decimal(3,2) null,
-- numeric 总的位数为2位数字,小数点后的位数为0位。
user_numeric numeric(2,0) null,
-- char 字符串固定大小 0-255
user_gender char null,
-- varchar 可变字符串,0-65535 常用对接java实体的String
user_name varchar(10) null,
-- tinytext 微型文本 2^8 - 1
user_descrpt1 tinytext null,
-- text 文本串 2^16-1
user_descrpt2 text null,
-- mediumtext 文本串 2^24-1
user_descrpt3 mediumtext null,
-- longtext 文本串 2^32-1
user_descrpt3 longtext null,
-- date YYYY-MM-DD日期
user_brithday1 data,
-- tiem HH:mm:ss 时间格式
user_brithday2 time ,
-- datatime 最常用的格式 YYYY-MM-DD HH:mm:ss
user_brithday3 datatime null,
-- timestamp 时间戳 1970.1.1到现在的毫秒数,也较为常用
user_brithday4 timestamp null,
-- year 年份
user_brithday5 year null,
-- null 没有值,未知,不要使用NULL进行运算,结果为NULL
user_brithday null,
-- tinyblob 用于存储二进制文件 255字节
user_1 tinyblob NULL ,
-- blob 用于存储二进制文件 最大65k
user_2 blob NULL ,
-- mediumblob 用于存储二进制文件 最大16M
user_3 mediumblob NULL ,
-- longblob 用于存储二进制文件 最大4G
user_4 longblob NULL ,
-- enum('') 枚举类型是字符串类型
/*
主要好处为MySQL在存储此类数据时,
直接转化成数字存储而不是字符串,
可以节省空间,
并且在表的.frm文件中存储“数字-字符串”之间的对应关系
*/
user_5 enum('') NULL ,
-- set('') SET是一个字符串对象,可以有零或多个值 64个成员
user_6 set('') NULL DEFAULT '' ,
-- binary 二进制类型 字节数为2
user_7 binary(2) NULL ,
--varbinary(2) 二进制类型 0-2字节
user_8 varbinary(2) NULL ,
-- point 空间数据格式 用于表示GIS中的地理坐标,在GIS中广泛使用 在表后设置ENGINE=MyISAM
user_9 point NULL ,
-- LineString 空间数据格式 由Point个值组成
user_10 linestring NULL ,
-- polygon 空间数据格式几何类型
user_11 polygon NULL ,
-- geometry 空间位置信息
user_12 geometry NULL ,
-- multipoint 几何集合
user_13 multipoint NULL ,
-- multilinestring 两个LineString值 返回一个双精度数字
user_14 multilinestring NULL ,
-- multipolygon 有两个Polygon值
user_15 multipolygon NULL ,
-- geometrycollection 包含两个Point值和一个LineString值
user_16 geometrycollection NULL ,
)ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- 增加表中的字段
alter table tb_user2 add user_name varchar(10) not null;
-- 删除表中的字段
alter table tb_user2 drop user_age;
-- 修改表中的字段名和约束,**change**不管要不要改表名,表名都要写两次
alter table tb_user2 change user_name user_name1 varchar(2);
-- 修改表中的约束
alter table tb_user2 modify user_name int(2);
-- 展示表中的字段和约束
desc tb_user2;
describe tb_user2;
show full columns from tb_user2;
2.8 操作表中的数据(主要DML,DQL)
-- 增加插入数据
insert into tb_user2(user_id,user_name1) values('11','li');
-- 删除表中的指定的数据
delete from `tb_user2` where `user_name1`='ke';
-- 清空表的内容;避免使用delete from `tb_user2`,因为不清空自增;
truncate tb_user2;
-- 修改表中的所有数据
update `tb_user2` set `user_name1` = 'kk',`user_name` = 'hh';
-- 修改表中指定的数据,where后面可以包括布尔值 >、 <、 =、 !=、between...and...、and、or、not
update `tb_user2` set `user_name1` = 'ke' where `user_id` = '11';
-- 查询表中的所有数据,尽量不要用*
SELECT `user_id` as 用户Id,`user_name1` as 用户姓名1,`user_name` as 用户姓名 from tb_user2 as a;
-- 查询表中的指定数据的数量 通过函数count() where后面同样可以包括表达式,子查询,like%_,in(),is not null,
SELECT count(*) as 总数 from tb_user2 where user_id = '12';
-- 去重查询某项
SELECT DISTINCT user_name from tb_user2;
--联表查询【SQL JOINS 】
-- innner join
select u1.student_id,student_name,user_age from tb_user1 as u1
inner join tb_student s
on u1.student_id = s.student_id
where u1.student_id=1000;
-- left join 即使右边没有匹配的值
select u1.student_id,student_name,user_age from tb_user1 as u1
left join tb_student s
on u1.student_id = s.student_id
where u1.student_id=1000;
-- RIGHT JOIN 即使左边没有匹配的值,用Null代替
select u1.student_id,student_name,user_age from tb_user1 as u1
right join tb_student s
on u1.student_id = s.student_id
where u1.student_id=1000;
-- 自连接
select u1.user_id as '父ID',u2.user_id as '子ID'
from `tb_user1` as u1,`tb_user1` as u2
where u1.user_id = u2.user_id;
-- 分页limit和排序order by(一起必须在前面)
select DISTINCT u1.user_id as '父ID',u2.user_id as '子ID'
from `tb_user1` as u1,`tb_user1` as u2
where u1.user_id = u2.user_id
order by u1.user_id desc/asc
limit 1,2;
2.9 常用函数
mysql常用函数官网:mysql常用函数官网
-- 数学运算
-- 绝对值
select ABS(-12);
-- 向上(大)取整
select CEILING(9.4);
-- 向下(小)取整
SELECT FLOOR(9.7);
-- 返回0-1之间随机数
SELECT RAND();
-- 判断一个数的符号,负数返回-1
SELECT SIGN(-10);
-- 字符串函数
-- 字符串长度
SELECT CHARACTER_LENGTH("jdhgh");
-- 拼接字符串
SELECT CONCAT('h','d','h');
-- 查询位置并插入
SELECT INSERT('我爱shhsshh',1,2,'ch');
-- 转换为小写
select LOWER("UUd");
-- 转换为大写
SELECT UPPER("jj");
-- 返回第一个字母出现的子串的索引
SELECT INSTR('dshsjhhhdbch','d');
-- 替换出现的字符
SELECT REPLACE('hhhhfghsdvgfs','vg','kl');
-- 返回指定的字符串
SELECT SUBSTR('dhggdjsd',2,6);
-- 反转
SELECT REVERSE('ghhkdhkl');
-- 时间日期函数
-- 获取当前日期
SELECT CURRENT_DATE();
SELECT CURDATE();
SELECT NOW();
-- 获取本地时间
SELECT LOCALTIME();
-- 获取系统时间
SELECT SYSDATE();
-- 只得到日期的一个值
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 系统函数
SELECT SYSTEM_USER();
SELECT USER();
SELECT SYSDATE();
SELECT VERSION();
-- 聚合函数:最大值,最小值,平均值,求和,总数 GROUP BY ...HAVING
2.10 操作表中的约束
-- 主键和物理外键,创建表的时候就完成了
-- 学生表
create table if not exists `tb_student`(
-- int(4)代表四位数字
`student_id` int(4) not null AUTO_INCREMENT COMMENT '学号',
`student_name` varchar(10) not null default '匿名' COMMENT '姓名',
`student_password` varchar(20) not null DEFAULT '12345' COMMENT '密码',
`student_gender` varchar(2) not null DEFAULT '男' comment '性别',
`student_brithday` datetime default null comment '出生日期',
`student_address` varchar(50) default null comment '地址',
primary key(`student_id`)
)ENGINE = INNODB AUTO_INCREMENT=0001 default charset=utf8;
-- 用户表
create table `tb_user`(
`user_id` varchar(3) not null,
`student_id` int(4) not null,
`user_age` int not null,
primary key(`user_id`),
key `FK_student_id` (`student_id`),
constraint `FK_student_id` FOREIGN KEY(`student_id`) REFERENCES `tb_student`(`student_id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;
-- 主键、物理外键创建表的时候没有
-- 用户表1
create table `tb_user1`(
`user_id` varchar(3) not null COMMENT '用户1ID',
`student_id` int(4) not null comment '学生Id',
`user_age` int not null comment '用户年龄'
)ENGINE = INNODB DEFAULT CHARSET=utf8;
-- 表外创建主键
alter table `tb_user1`
add constraint `tb_user1_PK_user_id`
primary key(`user_id`);
-- 表外修改主键
alter table myself modify id int primary key;
-- 表外删除主键(针对主键对),必须有且只有一个主键
alter table myself drop primary key;
-- 表外创建外键
alter table `tb_user1`
add constraint `tb_user1_FK_tb_student_student_id`
foreign key(`student_id`) references `tb_student`(`student_id`)
-- 表外删除外键
alter table myself drop foreign key myself_ibfk_1;
-- 表外检查约束,在MYSQL中,CHECK子句会被分析,但是会被忽略,域(Domain)与断言(Assertion)也是无法使用的,原因类似于CHECK
alter table tb_account
add constraint tb_account_CK_money
CHECK(money >= -100.00 and money <= 10000.00);
-- 定义默认约束 字段名 数据类型 DEFAULT 默认值
alter table myself add id int not null default 'abc';
2.11 MD5密码加密[不支持索引,cpu消耗大,自己能够加密最好]
-- 插入的时候MD5
INSERT into tb_student(student_id,student_name,student_password,student_gender,student_brithday,student_address) values(44,'gh',MD5('khdjgf'),'男','1999-11-15','dbhfhjdh');
-- 修改密码为MD5
update tb_student set student_password=MD5(student_password) where student_id = 2;
-- 查询
SELECT *from tb_student where student_name = 'gh' and student_password=MD5('khdjgf');
2.12 事务
特点:ACID(原一隔持)
隔离性可能出现的问题
脏读:一个事务读取了另一个没有提交的数据
不可重复读:在一个事务内读取表中某一行数据,多次读取结果不同,【不一定是错误,只是场合有可能不对】
虚读(幻读):指一个 事务内读取到了别的事务插入的事务,导致前后读取不一致;
-- 事务关键词:
-- mysql是默认事务自动提交的
-- 关闭自动提交事务
set autocommit = 0;
-- 事务开启
START TRANSACTION;
-- 提交
COMMIT;
-- 回滚
ROLLBACK;
-- 事务结束,恢复
set autocommit = 1;
-- 设置事务的保存点
SAVEPOINT 保存点名;
-- 回滚到保存点
ROLLBACK to SAVEPOINT 保存点名;
-- 撤销保存点
RELEASE SAVEPOINT 保存点名
-- 从这里开始
use temp1;
create table if not exists `tb_account`(
`account_id` int(3) not null auto_increment,
`username` varchar(30) not null,
`money` DECIMAL(9,2) not NULL,
primary key(`account_id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;
select *from `tb_account`;
alter table account rename as tb_account;
INSERT into tb_account(`username`,`money`) VALUES ('A',400.88),('B',5000.87);
-- 1.关闭事务自动提交
set autoCOMMIT = 0;
-- 2. 手动开启一个事务
START TRANSACTION;
UPDATE tb_account set money=money-500 where `username`='A';
UPDATE tb_account set money=money+500 where `username`='B';
-- 提交事务
COMMIT;
-- 回滚
ROLLBACK;
-- 恢复默认值
set autoCOMMIT = 1;
2.13 索引(本质是数据结构,帮助数据库建立高效获取数据的数据结构)
分类:
主键索引(primary key):一个表只能一个主键索引
唯一索引(Unique key):可以多个
常规索引(key、index)
全文索引(fullText):快速定位,一般用于数据引擎MyISAM
-- 索引的使用:创建时加,创建后加
-- 创建索引
create index id_t_student_stu_name on t_student(`stu_name`);
EXPLAIN SELECT *from t_student where stu_name='胡伟亮';
-- 创建后增加全文索引
alter table temp1.tb_student add FULLTEXT INDEX `student_name`(`student_name`);
-- 删除索引
alter table t_student drop index id_t_student_stu_name1;
-- 修改索引名字
alter table t_student rename index id_t_student_stu_name to id_t_student_stu_name1;
-- 展示索引
show index from t_student;
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT *from tb_student;
-- 分析非全文索引情况
EXPLAIN SELECT *from tb_student where match(student_name) AGAINST ('匿名');
/*
索引并不越多越好
*/
2.14 备份
# 1.利用mysql可视化工具
# 2.直接拷贝data数据物理文件
# 3.cmd指令
# 导出
mysqldump -hlocalhost -uroot -proot temp1 tb_account tb_user1 > D:/a.sql
# 导入,最好先登录,然后创建使用数据库,
source D:/a.sql;
# 不登录导入,不建议
mysql -uroot -proot 库名 < 文件位置
2.15 JDBC连接池
数据库连接(connection)-数据库sql操作(preparedStatement【可以防止SQL注入】)-结果操作(整数或resultSet)–释放
- c3p0(需要导入包,工具类)
package cn.itcast.itcaststore.utils;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* 数据源工具
*/
public class DataSourceUtils {
private static DataSource dataSource = new ComboPooledDataSource();
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
public static DataSource getDataSource() {
return dataSource;
}
/**
* 当DBUtils需要手动控制事务时,调用该方法获得一个连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection con = tl.get();
if (con == null) {
con = dataSource.getConnection();
tl.set(con);
}
return con;
}
/**
* 开启事务
* @throws SQLException
*/
public static void startTransaction() throws SQLException {
Connection con = getConnection();
if (con != null)
con.setAutoCommit(false);
}
/**
* 从ThreadLocal中释放并且关闭Connection,并结束事务
* @throws SQLException
*/
public static void releaseAndCloseConnection() throws SQLException {
Connection con = getConnection();
if (con != null) {
con.commit();
tl.remove();
con.close();
}
}
/**
* 事务回滚
* @throws SQLException
*/
public static void rollback() throws SQLException {
Connection con = getConnection();
if (con != null) {
con.rollback();
}
}
}
-
dbcp(需要导入包)
-
druid:阿里巴巴
3. 总结
总结了mysql的基础,中间还有许多没有讲到的基础,并不是全部内容,这是自己学习时的感悟与总结,如果有错误或者建议,欢迎大家评论区指出,谢谢大家。