mysql数据库

最新推荐文章于 2021-06-09 19:39:41 发布

一笑知语

最新推荐文章于 2021-06-09 19:39:41 发布

阅读量558

点赞数 1

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_44005434/article/details/117401485

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库基础

无所待而游无穷也。

目录

数据库基础
1. 分类：
2. MySQL
2.1 注意
2.2 数据库语言（CRUD【增删改查】）
2.3 操作用户语句
2.4 权限语句（主要DCL）
2.5 操作数据库语句（主要DDL）
2.6 操作表语句
2.7 操作表的字段语句
2.8 操作表中的数据(主要DML，DQL)
2.9 常用函数
2.10 操作表中的约束
2.11 MD5密码加密[不支持索引，cpu消耗大，自己能够加密最好]
2.12 事务
2.13 索引（本质是数据结构，帮助数据库建立高效获取数据的数据结构）
2.14 备份
2.15 JDBC连接池

3. 总结

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

mysql索引内部实现

-- 索引的使用：创建时加，创建后加

-- 创建索引
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的基础，中间还有许多没有讲到的基础，并不是全部内容，这是自己学习时的感悟与总结，如果有错误或者建议，欢迎大家评论区指出，谢谢大家。

一笑知语

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
mysql数据库

数据库基础1. 分类：关系型数据库（通过表与表之间的关系，行与列之间关系进行存储【SQL】）：Mysql,Oracle,Sql Server，SqlLite非关系型数据库（【NOSQL（not only sql）】通过对象存储，自身属性来决定）：Redis，MongDB数据库引擎数据库引擎MYSIAMINNODB事务支持不支持支持数据行锁定不支持支持外键约束不支持支持全文索引支持不支持表空间的大小较小较大，约两倍
复制链接

扫一扫