MySQL
概述
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。我这边用的就是Oracle下的环境加上sqlyog来连接。
命令行操作
mysql -u root -p -- 连接数据库
flush privileges; -- 刷新权限
mysql> use name -- 使用数据库
show tables; -- 查看所有的表
describe student; -- 显示数据库表的所有信息
create database name;-- 创建表
exit; -- 退出连接
-- 单行注释
操作数据库
CREATE DATABASE [IF NOT EXISTS] name -- 创建数据库
DROP DATABASE [IF EXISTS] name -- 删除数据库
use name -- 使用数据库
show databases; -- 查看所有数据库
列的类型
tinyint 十分小的数据 1字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
* int 标准的整数 4个字节 常用的int
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数 金融计算时候一般使用这个
*char 字符串固定大小 0~255
*varchar 可变字符串 0~65535 常用的变量 String
*tinytext 微型文本 2^8 -1
*text 文本串 2^16-1 保存大文本
*date YYYY-MM-DD 日期格式
*time HH:mm:ss 时间格式
*datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
*timestamp 时间戳 1970.1.1到现在的毫秒数 也较为常用
*year 年份表示
创建数据库表
注意``符号和””。
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
也可用反查询:
SHOW CREATE TABLE `info`
eg:
CREATE TABLE IF NOT EXISTS `info`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT "学号",
`names` VARCHAR(10) NOT NULL DEFAULT "匿名" COMMENT "姓名",
`passwd` VARCHAR(20) NOT NULL DEFAULT "123456" COMMENT "密码",
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8`info`
数据库引擎
MYISAM | innoDB | |
---|---|---|
事物支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,2倍 |
修改表
-- 修改表名: alter table 旧表名 rename as 新表名
ALTER TABLE `info` RENAME AS `info1`
-- 增加表的字段: alter table 旧表名 add 字段名 列属性
ALTER TABLE `info` ADD age INT(11)
-- 修改表的字段: alter table 表名 modify 字段名 列属性
ALTER TABLE `info` MODIFY age VARCHAR(11)
-- 修改表的字段: alter table 表名 change 字段名 列属性
ALTER TABLE `info` change age age1 INT(11)
-- 删除字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE `info` DROP `age`
-- 删除数据库
DROP DATABASE [IF EXISTS] name
外键
MySQL可以设置物理外键但是因为关联不好直接删除table,因此不常用。
ALTER TABLE 主表名 ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(主表字段) REFERENCES 从表(字段)
DML语句
insert
-- insert into 表名([字段名1,字段名2,字段名3]) values('值1'),('值2'),('值3')
INSERT INTO `info`(`id`,`names`,`passwd`)
VALUES('1','11','123'),
('2','22','456'),
('3','33','789')
update
-- update 表名 set colnum_name=value,[colnum_name=value...] where [条件]
UPDATE `info` SET `names`='22',`passwd`='aaa' WHERE `id`=1;
where
where between num1 and num2
where name='1' and str='2'
where name='1' or str='2'
where name like 'a%' -- 查询名字中a开头后面任意
where name like 'a_' -- 查询名字中a开头后面一个字符_
where name like '%a%' -- 查询名字中有a后面任意
where name in (value1,value2,value3...) -- 查询名字在value范围中的具体值
where name is null -- 查询名字为null的
where name is not null -- 查询名字不为null的
delete
-- delete from 表名 [where 条件]
DELETE FROM `info` WHERE id=3;
Delete删除的问题:
innodb 自增列会重1开始(存在内存中,断电即失)
myisam 继续从上一个自增量开始(存在文件中,不会丢失)
truncate
truncate table name -- 删除自增
DQL语句
select
-- select 字段 (算数) [as rename] from 表 [as rename] [where 条件]
SELECT `gradeid` AS 序号 FROM `grade`
-- 去重查询 select distinct [字段名] from 表
SELECT DISTINCT `gradeid` FROM `grade`
-- select version
select version()
JoinOn
连表查询可以由下面的图来解释,但是总结起来还是可以归为3类,left,right,inner查询,可以理解为以交点为中心,向指定方向查询。
-- 选择字段名注意有些要加前缀
-- 进行左右表排序,选择左右或内join
-- on选择关联条件
-- 可以加where语句结尾
SELECT a.studentno,loginpwd,sex,digit
FROM `student` AS a
LEFT JOIN `score` AS b
ON a.studentno = b.studentno
排序和分页
order by name desc -- 降序
order by name asc -- 升序
limit num1,num2 -- limit(起始下标,页面大小)
子查询
在两个以上的连表时我们可以使用where子查询去,有一种递归的感觉。
select distinct 字段名
from 表名
where 字段名 = (
select distinct 字段名 from 表名 where 字段名 = (
...
)
)
总和:
select [all|distinct] 字段名
from 表名 [as name]
[left|right|inner join 表名 as name]
[where ...]
[group by ...]
[having ...]
[order by ...]
[limit num1 num2]
函数
-- ===========================聚合函数============================
select count(name) from table_name -- 查询个数,不算null
select count(*) from table_name -- 查询个数,算null
select count(1) from table_name -- 查询个数,算null
select sum(name) from table_name -- 总和
select avg(name) from table_name -- 平均值
select max(name) from table_name -- 最大值
select min(name) from table_name -- 最小值
事务
ACID:
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致。
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
贴一个链接:事务ACID理解_dengjili的专栏-CSDN博客_acid
流程:关闭自动提交—–>开启一个事物—–>(提交||回滚)—–>开启自动提交
-- SET autocommit=0 关闭自动提交
-- START TRANSACTION 开启交易
-- 执行语句
-- COMMIT 提交事物
-- ROLLBACK 回滚
-- SET autocommit=1 开启自动提交
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
索引
索引是帮助MySQL高效获取数据的数据结构。
- 主键索引(primary key)
- 唯一索引 (unique key)
- 常规索引 (key|index)
- 全文索引 (FullText)
-- 显示全部索引
SHOW INDEX FROM account
-- 添加索引
ALTER TABLE `test`.`account` ADD FULLTEXT INDEX `name`(`name`)
CREATE INDEX id_app_user_name ON app_user(`name`);
-- explain执行sql情况(非全文)
EXPLAIN SELECT * FROM `account`
-- explain执行sql情况
EXPLAIN SELECT * FROM `account` WHERE MATCH(`name`) AGAINST('A')
用户权限
以下为8.0以下的版本:
CREATE USER user_name IDENTIFIED BY 'passwd' -- 创建用户
SET PASSWORD = PASSWORD('passwd') -- 设置密码
SET PASSWORD FOR user_name = PASSWORD(passwd) -- 设置用户密码
RENAME USER user_name1 TO user_name2 -- 重命名
GRANT ALL PRIVILEGES ON *.* to user_name -- 赋予权限
show grants for user_name -- 显示权限
revoke ALL PRIVILEGES ON *.* from user_name -- 删除权限
drop user user_name -- 删除用户
数据备份
1.可以直接在sqlyog中去导出
2.选择命令行导出db
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 [表名] > 位置
mysqldump -hlocalhost -uroot -p123456 school md5 > D:/a.sql
# 导入数据库
source dir.sql
三大范式
第一范式
原子性:保证每一列不可分。
第二范式
在满足第一范式前提下,每张表只描述一件事情。
第三范式
满足一二范式的前提下,与主键要直接相关。
JDBC
一般是作为中间件去连接驱动和数据库,方便操作。
jar包下载地址:http://mvnrepository.com/artifact/mysql/mysql-connector-java
import java.sql.*;
public class mysql {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//用户信息和url
String url = "jdbc:mysql://127.0.0.1:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true";
String username = "root";
String password = "123456";
//连接数据库,connection为数据库对象
Connection connection = DriverManager.getConnection(url,username,password);
//执行sql对象
Statement statement = connection.createStatement();
//操作并接受对象,result只有查询才有
String sql = "SELECT * FROM users;";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next())
{
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("pwd="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birth="+resultSet.getObject("birthday"));
}
//关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
很多时候为了避免使用的代码重复我们可以这样写:
import java.sql.*;
public class JdbcUtils {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true";
private static String username= "root";
private static String password = "123456";
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
public static void release(Connection conn, Statement sta,ResultSet res){
if(res!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(sta!=null)
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void release(Connection conn, Statement sta){
if(sta!=null)
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class test {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
try {
conn=JdbcUtils.getConnection();
sta= conn.createStatement();
String sql = "INSERT INTO `users` (`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
"VALUES (11,\"dyd\",\"321321\",\"213@qq.com\",\"2022-02-22\")";
sta.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,sta);
}
}
}
防止sql注入的语句
public class test1 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement st = null;
try {
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO `users` (`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
st = connection.prepareStatement(sql);
st.setInt(1,7);
st.setString(2,"hahahaha");
st.setString(3,"13321");
st.setString(4,"133@123.com");
st.setString(5,"1999-03-03");
int i = st.executeUpdate();
if(i>0) System.out.println("success");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,null);
}
}
}