目录
1.连接数据库
命令行连接
mysql -u root -p --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';--修改密码
show databases;--查看所有的数据库
show database;--查看数据库中所有的表
use school--切换数据库 use+数据库名
describe student;--显示数据库中student表的信息
create database abc;--创建一个abc数据库
数据库XX语言:
DDL 定义
DML 操作
DQL 查询
DCL 控制
2.操作数据库
2.1操作数据库
1.创建数据库:CREATE DATABASE 数据库名
2.删除数据库:DROP DATABASE 数据库名
3.使用数据库:USE 数据库
注意:如果表明或字段名是一个特殊字符,需要用 `` 包起来
4.查看数据库:SHOW DATABASE
5.查看创建表或数据库的语句:SHOW CREATE DATABASE/TABLE 数据库名/表名
6.显示表的结构:DESC 表名
2.2数据库的列类型
数值
tinyint 十分小的数据 1字节
smallint 较小的数据 2字节
mediumint 中等大小的数据 3字节
int 标准的整数 4字节
bigint 较大的数据 8字节
float 单精度浮点数 4字节
double 双精度浮点数 8个字节
decimal 字符串形式的浮点数,一般用于金融计算
字符串
char 字符串固定大小 0-255
varchar 可变字符串 0-65535
tinytest 微型文本 0-2^8-1
text 文本串 0-2^16-1
时间日期
date YYYY-MM-DD 日期格式
time HH: MM: SS 时间格式
datetime YYYY-MM-DD HH: mm: ss 常用格式
timestamp 时间戳,从1970.1.1到现在的毫秒数!
year 年份表示
null
空值
2.3数据库的字段属性
unsigned(无符号整数): 声明了该列不能为负数
zerofill(0填充): 不足的位数用0来填充
自增:自动在上一条的基础上+1(默认为1,可以更改)
非空:该属性必须填写
默认:设置默认的值
每一个表必需存在一下五个字段:
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
2.4创建数据库表([]里面的表示可有可无)
CREATE TABLE [IF NOT EXISTS] '表名'(
'列名' 列类型 [属性] [索引] [注释],
)[表类型] [字符集设置] [注释]
2.5数据表类型
2.6修改删除表
ALTER TABLE 表名 RENAME AS 新表名
ADD 列名 列类型
MODIFY 列名 新列类型 --修改列类型
CHANGE 原列名 新列名 列类型 --修改列名和列类型
DROP 列名
3.MySQL数据管理
3.1外键
3.2DML语言
3.3添加
3.4修改
3.5删除
delete和truncate的区别:
相同:都能删除数据,都不会删除表结构
不同:truncate 会重新设置自增列,计数器会归零
truncate 不会影响事物
3.6查询
SELECT *FROM stduent
SELECT `studentNo`,`StudentName` FROM student
SELECT `studentNo` AS 学号, `studentName` AS 学生姓名 FROM student AS 学生--给结果起名
SELECT CONCAT(’姓名:‘,studentName) AS 新名字 FROM student--函数
DISTINCT :可去重复数据
SELECT 表达式 FROM 表
3.7模糊查询
LIKE 结合 %代表0到任意个字符, _ 代表一个字符
LIKE ‘张%’:以张开头的名字;LIKE ‘张_’:以张开头的两个字的名字
LIKE ’%张%‘:名字里含有张的
3.8连表查询
LEFT JOIN INNER JOIN RIGHT JOIN
3.9分页(limit)和查询(order by)
升序:ASC 降序:DESC
ORDER BY 列名 ASC/DESC
LIMIT 0,5 LIMIT 起始值,页面大小
4、MySQL常用函数
CEILING(2.3) -- 结果为3 向上取整
FLOOR(2.3) -- 结果为2 向下取整
RAND() --返回一个0-1之间的随机数
SIGN(10) --判断一个数的符号 负数返回-1,正数返回1
字符串函数:
CHAR_LENGTH('XXX') --字符串长度
CONCAT('a','c','d',) --拼接字符串
INSERT('aadsfsfasddf',1,2,'cdaf') --某个位置开始替换n个长度
LOWER('ABC') --转为小写
UPPER('abc') --转为大写
5、事务
原子性:要么都发生,要么 都不发生
一致性:事务前后的数据完整性要保持一致,例如:A给B转账前后,A+B的钱的总数不变
隔离性:多个用户并发访问数据库时,各个事物要相互隔离
永久性:事物一旦提交则不可逆,被持久化到数据库中
MySQL默认开启事物自动提交
SET autocommit = 0--关闭
SET autocommit = 1--开启
START TRANSACTION --标记一个事务的开始,从这个sql之后都是在一个事务
COMMIT --提交
ROOLBACK --回滚
SET autocommit = 1 --开启自动提交
SAVEPOINT 保存点名 --设置一个保持点
ROOLBACK TO SAVEPOINT 保存点名 --回滚到保存点
RELEASE SAVEPOINT 保存点名 --撤销保存点
6.索引
索引是帮助MySQL高效获取数据的数据结构,索引就是数据结构
6.1索引的分类
主键索引 (PRIMARY KEY)
唯一索引 (UNIQUE KEY)
常规索引 (KEY/INDEX)
全文索引 (FullText) MyISAM引擎下才有,快速定位数据
EXPLAIN 分析sql执行的情况
6.2索引原则
1.索引不是越多越好
2.不要对进程变动数据加索引
3.小数据量的表不用加索引
4.索引一搬加在常用来查询的字段上
6.3索引的结构
Hash类型的索引
Btree:InnoDB的默认数据结构
7.权限管理和备份
7.1用户管理
CREATE USER 用户名 IDENTIFIED BY ‘密码’ --创建用户
SET PASSWORD FOR 用户名 = =PASSWORD('XXX') --改用户密码
RENAME USER 用户名 TO 新用户名 --改用户名
GRANT ALL PRIVILEGES ON 表名 TO 用户名 --给用户全部权限,除了授权权限
7.2MySQL备份
1.直接拷贝物理文件
2.在sql可视化软件中手动到出
3.使用命令行导出,mysqldump -hlocalhost -uroot -p123456 数据库名 表名 >磁盘位置
8、JDBC
8.1连接数据库
import java.sql.*;
public class JdbcTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.用户信息和url
String url = "jdbc:mysql://localhost:3306/school";
String username = "root", password = "123456";
//3.连接成功,数据库对象
Connection c = DriverManager.getConnection(url,username,password);
//4.执行SQL的对象
Statement statement = c.createStatement();
//5.执行SQL的对象去执行SQL
String sql = "SELECT * FROM student";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println(resultSet.getObject("loginpwd")+" "
+resultSet.getObject("studentname"));
}
//6.释放连接
resultSet.close();
statement.close();
c.close();
}
}
Connection 代表数据库
connection.rollback();
.commit();
.setAutoCommit();
Statement 执行SQL的对象 PrepareStatement执行SQL的对象
statement.executeQuery();//查询操作返回 resultSet
.execute();//执行任何SQL
.executeUpdate();//更新、差入、删除,返回一个受影响的行数
ResultSet:查询结果集,封装了所有的查询结果
resultSet.getObject();//在不知道列类型的情况下使用
.getString();//知道列类型的时候用指定类型
.getInt();.getFloat();getDate();
resultSet.beforeFirst();//移动到最前面
.afterLast();//移动到最后面
.next();//移动到下一个数据
.previous();//移动到前一行
.absolute(row);//移动到指定行
8.2SQL注入问题
sql存在漏洞,会被攻击导致数据泄露, SQL会被拼接 or
8.3Statement对象
8.4PreparedStatement对象
可防止SQL注入,效率更好
8.5JDBC操作事务
9、数据库连接池
池化技术:准备一些预先的资源,过来就连接预先准备好的
编写连接池,实现一个接口 DataSource
使用数据库连接池之后,我们在项目开发中就不需要缩写连接数据库的代码了