通过mysql -uroot -p
进入数据库,创建数据库:create database databaseName;
。use databaseName
出现错误:Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A。上面卡住的原因:是由于数据库太大,即数据库中表非常多,所以如果预读数据库信息,将非常慢,所以就卡住了,如果数据库中表非常少,将不会出现问题。解决方式:进入数据库时输入:mysql -uroot -p -A
。
查看数据库、表:show databases;
、show tables
。使用某个数据库:use databaseName
,查看表具体内容:describe tableName;
。
退出:exit;
。
注释:-- 这是注释
; -*多行注释*-
。
1.操作数据库
(MySQL关键字不区分大小写)
1.1 创建数据库
CREATE DATABASE [IF NOT EXISTS] databaseName
1.2 删除数据库
DROP DATABASE [IF EXISTS] databaseName
1.3 使用数据库
USE `databaseName`
-- 如果表名或字段是一个特殊字符,需要加上``
1.4 查看所有的数据库
SHOW DATABASES
1.5 查看创建数据库/表的代码
SHOW CREATE DATABASE 数据库名
SHOW CREATE TABLE 表名
1.6 显示表的具体结构
DESC 表名
2.数据库引擎
-- 创建student表
CREATE TABLE `student` (
`ID` int NOT NULL COMMENT '学生ID',
`name` varchar(100) NOT NULL COMMENT '学生名字',
`age` int NOT NULL COMMENT '学生年龄',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INNODB(默认使用)
安全性高,支持事务处理,支持多表多用户使用
MYISAM(早期使用)
节约空间,速度较快
3. 数据库字符集编码
CHARSET=utf8
4. 修改和删除
修改表
修改表名:ALTER TABLE 旧表名 RENAME AS 新表名
增加表字段:ALTER TABLE 表名 ADD 字段名 字段列属性
修改表的字段:
· 修改约束:ALTER TABLE 表名 MODIFY 字段名 字段新的列属性
· 重命名:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段名的列属性
删除表的某个字段:ALTER TABLE 表名 DROP 字段名
删除表:DROP TABLE [IF EXISTS] 表名
5.外键
数据库级别的外键,不建议使用:
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(
作为外键的列REFERENCE 那个引用的表(
引用的那个字段))
5. DML数据操作语言(重要)
5.1 INSERT 插入
INSERT INTO `表名` (`字段名1`,`字段名2`,`字段名3`) VALUES (`值1`,`值2`,`值3`)
-- 或者:
INSERT INTO `表名` (`字段名`) VALUES (`值1`),(`值2`),(`值3`)
注:字段是可以省略的,但是后面的值必须一一对应。
5.2 UPDATE 修改
UPDATE `表名` SET `字段名1`='改成某个值',`字段名2`='改成某个值' WHERE 条件
5.3 DELETE删除
DELETE FROM `表名` [WHERE 条件]
完全清空一个数据库:
TRUNCATE TABLE `表名`
注:DELETE与TRUNCATE的区别
相同点:都能删除数据,都不会删除表结构
不同点:TRUNCATE重新设置自增列,计数器会归零;TRUNCATE不会影响事务。
drop(丢弃数据): drop table 表名
,直接将表都删除掉,在删除表的时候使用。
truncate (清空数据) : truncate table 表名
,只删除表中的数据,再插入数据的时候自增长 id 又从 1 开始,在清空表中数据的时候使用。
delete(删除数据) : delete from 表名 where 列名=值
,删除某一列的数据,如果不加 where 子句和truncate table 表名作用类似。
- truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据,但是 truncate 和 delete 只删除数据不删除表的结构(定义),执行 drop 语句,此表的结构也会删除,也就是执行 drop 之后对应的表不复存在。
- truncate 和 drop 属于 DDL(数据定义语言)语句,操作立即生效,原数据不放到 rollback segment 中,不能回滚,操作不触发 trigger。而 delete 语句是 DML (数据库操作语言)语句,这个操作会放到 rollback segement 中,事务提交之后才生效。
6.DQL数据查询语言(非常重要)
6.1指定查询字段
-- 查询所有信息
SELECT * FROM 表名
SELECT `字段` FROM 表名
-- 给字段或者表 取别名(有时候字段名不是那么的见名知意)
SELECT `字段` AS 字段别名 FROM 表名 AS 表的别名
-- 拼接
SELECT ('姓名:',`字段`) AS 别名 FROM 表名
-- 去重
SELECT DISTINCT `字段` FROM 表名
-- 查询版本号
SELECT VERSION()
-- 计算
SELECT 100*3-1 AS 计算结果
6.2 WHERE子句
搜索的条件由一个或多个表达式组成,结果为布尔值
逻辑运算符
与或非 and、or、not
模糊查询
IS NULL
IS NOT NULL
BETWEEN… AND…
LIKE
IN
6.3 联表查询
INNER JOIN、LEFT JOIN、RIGHT JOIN等,共7种
注:join…on…连接查询,where等值查询
6.4 常用函数
数学运算
-- 绝对值
SELECT ABS(-1) --返回1
-- 向上取整
SELECT CEILING(9.4) --返回10
-- 向下取整
SELECT FLOOR(9.4) --返回9
-- 返回0-1之间的随机值
SELECT RAND()
字符串函数
SELECT CHAR_LENGTH('某个字符串') --字符串长度
SELECT CONCAT('拼接','字符','串') --拼接字符串
SELECT INSERT() --插入替换
SELECT LOWER() --转小写
SELECT UPPER() --转大写
SELECT CURRENT_DATE() --获取当前日期
SELECT CURRENT_TIME() --获取当前时间
SELECT NOW()
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT LOCALTIME()
SELECT USER()
SELECT VERSION()
聚合函数
SELECT COUNT() --计数
SELECT SUM()
SELECT AVG()
SELECT MAX()
SELECT MIN()
(补充)测试MD5加密
创建表
CREATE TABLE `testmd5` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`pwd` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
加数据(明文密码)
INSERT INTO `testmd5` VALUES (1,'zhangsan','123456'),(2,'lisi','112233'),(3,'wangwu','556677')
加密
UPDATE testmd5 SET pwd = MD5(pwd)
插入的时候就加密
INSERT INTO `testmd5` VALUES (5,'dahong',MD5('123456'))
7.事务
7.1 事务的ACID特性:原子性,一致性,隔离性,持久性
原子性是指事务是一个不可分割的工作单位,事务中的操作要么全部成功,要么全部失败。比如在同一个事务中的SQL语句,要么全部执行成功,要么全部执行失败。
一致性指事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
并发事务带来哪些问题?
脏读(Dirty read): 当一个事务正在访问数据并且对数据进行了修改,而这种修改还没有提交到数据库中,这时另外一个事务也访问了这个数据,然后使用了这个数据。因为这个数据是还没有提交的数据,那么另外一个事务读到的这个数据是“脏数据”,依据“脏数据”所做的操作可能是不正确的。
丢失修改(Lost to modify): 指在一个事务读取一个数据时,另外一个事务也访问了该数据,那么在第一个事务中修改了这个数据后,第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失,因此称为丢失修改。 例如:事务 1 读取某表中的数据 A=20,事务 2 也读取 A=20,事务 1 修改 A=A-1,事务 2 也修改 A=A-1,最终结果 A=19,事务 1 的修改被丢失。
不可重复读(Unrepeatable read): 指在一个事务内多次读同一数据。在这个事务还没有结束时,另一个事务也访问该数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。
幻读(Phantom read): 幻读与不可重复读类似。它发生在一个事务(T1)读取了几行数据,接着另一个并发事务(T2)插入了一些数据时。在随后的查询中,第一个事务(T1)就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。
不可重复读和幻读区别:
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改,幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。
事务隔离级别有哪些?
SQL 标准定义了四个隔离级别:
READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
SERIALIZABLE(可串行化): 最高的隔离级别,完全服从 ACID 的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)。
因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ,但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ(可重读) 并不会有任何性能损失。
7.2 执行事务
-- mysql默认开启事务自动提交的
SET autocommit = 0 --关闭
SET autocommit = 1 --开启(默认的)
-- 手动处理事务,首先关闭自动条件
SET autocommit = 0;
-- 开启事务
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
SAVEPOINT 保存点名 -- 当事务过长,可以设置一个事务的保存点
COMMIT -- 成功,就提交。(持久化)
ROLLBACK TO 保存点名 -- 失败,就回滚。
RELEASE 保存点名 --撤销保存点
SET autocommit = 1 -- 事务结束,开启自动条件
-- 转账案例
-- 新建数据库shop
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
-- 创建表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO account (`name`,`money`) VALUES ('A',2000),('B',3000)
-- 模拟转账:事务 (注:每行单独运行)
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION
UPDATE account SET money=money-500 WHERE `name`='A'
UPDATE account SET money=money+500 WHERE `name`='B'
COMMIT; -- 提交
ROLLBACK; --回滚
SET autocommit=1; -- 恢复默认值
8.索引
索引(Index)是帮助MySQL高效获取数据的数据结构。在小数据量时用处不大,但是在大数据量时效果明显。
8.1 索引四大分类
主键索引 PRIMARY KEY
唯一的标识,主键不可重复,只能有一个列作为主键
唯一索引 UNIQUE KEY
避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
常规索引 KEY/INDEX
默认的,index、key关键字来设置
全文索引 FullText
在特定的数据库引擎下才有(MyISAM),可以快速定位数据。
8.2 索引的使用
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
-- 显示所有索引信息
SHOW INDEX FROM 表名
-- 增加全文索引
ALTER TABLE 数据库名.表名 ADD FULLTEXT INDEX `索引名`(`列名`)
-- EXPLAIN分析sql执行的状况
EXPLAIN SELECT * FROM 表名;
CREATE INDEX 索引名 ON 表名(`字段名`);
8.3 索引原则
·索引不是越多越好
·不要对进程变动数据加索引
·对小数据量的表不需要加索引
·索引应该加在常用来查询的字段上,提高查询速度
8.4 索引数据结构
Hash类型的索引
INNODB默认数据结构是 Btree
9. 权限管理和备份
9.1用户管理
系统用户表:mysql数据库下的user表
-- 创建用户
CREATE USER 用户名 IDENTIFIED BY ‘登陆密码’
-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456')
-- 修改指定用户密码
SET PASSWORD FOR 用户名 = PASSWORD('123456')
-- 重命名
RENAME USER 原用户名 TO 新用户名
-- 用户授权
-- GRANT ALL PRIVILEGES几乎拥有所有权限,但是没有GRANT权限
GRANT ALL PRIVILEGES ON 库名.表名 TO 用户
-- 查询指定用户权限
SHOW GRANTS FOR 用户名/rooot@localhost
-- 撤销权限
REVOKE ALL PRIVILEGES ON 库名.表名 FROM 用户
-- 删除用户
DROP USER 用户名
9.2MySQL备份
为什么要备份:保证数据不丢失;数据转移。
MySQL数据库备份的方式:
- 直接拷贝物理文件(data文件夹的内容);
- 在可视化工具中手动导出;
- 使用命令行导出(mysqldump)
mysqldump -hlocalhost -uroot -p123456 数据库名 表名 >D:/1.sql
出现如下结果:
4. 使用命令行导入
# 首先要登陆进数据库
mysql -uroot -p123456
use 数据库名
source D:/1.sql
10.规范数据库设计
10.1良好的数据库设计:
· 节省内存空间
· 保证数据库的完整性
· 方便我们开发系统
10.2三大范式(规范数据库设计)
- 第一范式(1NF):列不可再分
· 每一列属性都是不可再分的属性值,确保每一列的原子性;
· 两列的属性相近或相似或一样,尽量合并属性一样的列,确保不产生冗余数据。 - 第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
- 第三范式(3NF)需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范性和性能的问题![在这里插入图片描述](https://img-blog.csdnimg.cn/20210715162220527.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjAxNjU2Nw==,size_16,color_FFFFFF,t_70)
11.JDBC(重点)
11.1 JDBC简介
程序通过数据库驱动与数据库打交道,数据库驱动由数据库厂商提供。
JDBC:就是一套接口,JAVA操作数据库的规范,可以提高程序的扩展力,解耦合。具体实现由具体的厂商去做,开发人员只需要掌握JDBC接口的操作即可。
每个数据库的底层实现原理是不一样的(MyAQL、Oracle等),SUN公司制定了一套JDBC接口,Java程序员面向JCBC接口编程,各大数据库厂家对该接口进行了实现,负责编写JDBC接口的实现类。
所有的数据库驱动都是以jar包形式存在,jar包中有很多.class文件,这些.class文件就是对JDBC接口的实现,驱动不是SUN公司提供的,是个大数据库厂商提供的,下载驱动jar包需要去各大数据库官网下载。
导入jar包
这个jar包就是MySQL驱动,从官网下载对应的驱动jar包,然后将其配置到环境变量classpath中。
11.2JDBC编程六部曲(背会)
- 注册驱动。(作用:告诉Java程序,即将连接的是哪个品牌的数据库)
import java.sql.Driver;
import java.sql.DriverManager;
// 方法一
Driver driver = new com.mysql.jdbc.Driver; //多态,父类型引用指向子类型对象
DriverManager.registerDriver(driver);
// 记得try catch受检异常
//方法二(常用)
Class.forName("com.mysql.jdbc.Driver"); //固定写法,加载驱动,反射机制
- 获取链接。(表示JVM的进程和数据库进程之间的通道打开了,属于进程间通信,使用完一定要关闭)
import java.sql.Connection;
// 写在try catch内
// url:统一资源定位符(网络中某个资源的绝对路径)
// jdbc:mysql://表示协议,通信协议是通信之前就提前定好的数据传送格式
// localhost或者127.0.0.1表示IP地址
// 3306表示MySQL端口号
// useUnicode=true(支持中文编码)
// characterEncoding=utf8(设置中文字符集utf8)
// useSSL=true(使用安全的连接)
String url="jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=utf8&useSSL=true";
String user="root";
String password="123456";
Connection connection = DriverManager.getConnection(url,user,password); // 数据库连接对象
- 获取数据库操作对象。(专门执行sql语句的对象)
import java.sql.Statement;
// 写在try catch内
Statement statement = connection.createStatement();
- 执行sql语句。(主要执行DQL和DML等)
// 写在try catch内
String sql = "INSERT TNTO 表名(字段名1,字段名2) VALUES(值1,值2)";
// 专门执行DML语句的(INSERT、DELETE、UPDATE)
// 返回值count是影响数据库中的记录条数
int count = statement.executeUpdate(sql);
- 处理查询结果集。(只有当第4步执行的是SELECT语句时,才有这第5步)
- 释放资源。(使用完资源后一定要关闭,Java和数据库属于进程间通信,一定要关闭)
// 为了保证资源一定释放,在try...catch的finally中关闭资源,并且遵循从小到大依次关闭。
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
12.SQL注入
参考博文
用户名:1234
密码:1234’or’1’='1
登陆成功
12.1 为什么出现SQL注入?
因为程序要接受来自客户端用户输入的变量或URL传递的参数,并且这个变量或参数是组成SQL语句的一部分,正好将用户提供的“非法信息”编译进去了,导致SQL语句的原意被扭曲,进而达到SQL注入。
12.2 怎么解决SQL注入?
只要用户提供的信息不参与SQL语句的编译过程,问题就解决了。
即使用户提供的信息中含有SQL语句的关键字,但是没有参与编译,不起作用。
想要用户信息不参与SQL语句的编译,那么必须使用java.sql.PreparedStatement.
PreparedStatement接口继承了java.sql.Statement。
PreparedStatement是属于预编译的数据库操作对象。
PreparedStatement原理是预先对SQL语句框架进行编译,然后再给SQL语句传值。如SELECT * FROM 表名 WHERE 字段名 = ?
,一个?表示一个占位符,一个?将来接接收一个“值”。
13. 索引(重要哦)
MySQL索引使用的数据结构主要有BTree索引 和 哈希索引。
对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,如范围查找时,建议选择BTree索引。
B+树的叶子结点间也有指针,适合范围查找。
B树的叶子结点间无指针,不适合范围查找。
MySQL的BTree索引使用的是B树中的B+Tree,但对于主要的两种存储引擎的实现方式是不同的。
评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小,I/O次数越少。 那为什么是B+树而不是B树呢,因为它内节点不存储data,这样一个节点就可以存储更多的key。
MyISAM:
B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照 B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其 data 域的值,然后以 data 域的 值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB:
其数据文件本身就是索引文件。相比MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”。而其余的索引都作为助索引,助索引的data域存储相应记录主键的值而不是地址,这也是和MyISAM不同的地方。在根据主索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引。 因此,在设计表的时候,不建议使用过⻓的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。
联合索引
14.数据库设计通常分为哪几步?
需求分析 : 分析用户的需求,包括数据、功能和性能需求。
概念结构设计 : 主要采用 E-R 模型进行设计,包括画 E-R 图。
逻辑结构设计 : 通过将 E-R 图转换成表,实现从 E-R 模型到关系模型的转换。
物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
数据库实施 : 包括编程、测试和试运行
数据库的运行和维护 : 系统的运行与数据库的日常维护。
15. SQL优化方式
- 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
- 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
,可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0
。 - 应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
,可以这样查询:
select id from t where num=10
union all
select id from t where num=20
- in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)
,对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3
。 - 下面的查询也将导致全表扫描:
select id from t where name like '%abc%'
。 - 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
,应改为:select id from t where num=100*2
。 - 还有好多我这里就不写了,都在这个链接里,还有这个链接。
- 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。 一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
- 使用varchar代替char。【理由:1. varchar变长字段按数据内容实际长度存储,存储空间小,可以节省存储空间。char按声明大小存储,不足补空格;2. 其次对于查询来说,在一个相对较小的字段内搜索,效率更高。】
16.什么时候索引失效?
- 没有 WHERE 子句
众所周知,添加索引的字段必需要在where条件后适当使用才会生效,如果连查询条件都没有,那肯定不会用到索引的。 - 使用 IS NULL 和 IS NOT NULL
- WHERE 子句中使用函数
- 使用 LIKE ‘%T’ 进行模糊查询
- WHERE 子句中使用不等于操作