JAVA-第七部分-MySQL数据库

99永远差一分
388 阅读10分钟

Mysql

  • 登陆 mysql -uroot -p
  • 退出 exit quit
  • 链接远程 mysql -h127.0.0.1 -uroot -p
  • 启动mysql服务 sudo ./support-files/mysql.server start

DDL

  • 操作数据库、表、列
  • 要大写

创建

  • 创建 Create
  • 数据库
create database dbtest;
create database if not exists dbtest2;
//指定字符集
create database if not exists dbtest3 character set gbk;
  • 创建表
create table student( 
    id int, 
    name varchar(32), 
    age int, 
    score double(4,1), 
    birthday date,
    insert_time timestamp 
);
  • 复制表
create table student1 like student;

查询

  • 查询Retrieve
  • 查询所有数据库
SHOW DATABASES;
  • 查询某个数据库的字符集，查询某个数据库的创建语句
show create databases mysql;
  • 查询某个数据库所有的表
show tables;
  • 查询表结构
desc user;
  • 查询表的字符集
show create table newStudent;

修改

  • Update
  • 修改数据库的字符集
alter database dbtest3 character set utf8;
  • 修改表名
alter table student rename to newStudent;
  • 修改表字符集
alter table newStudent character set gbk;
  • 添加一列
alter table newStudent add gender varchar(10);
  • 修改列名称、类型
//修改名称和类型
alter table newStudent change gender sex varchar(20);
//只修改类型
alter table newStudent modify sex varchar(5);
  • 删除列
alter table newStudent drop sex;

删除

  • Delete
  • 删除数据库
drop database dbtest;
drop database dbtest2 if exists;
  • 删除表
drop table if exists student1;

使用

  • 查询当前正在使用的数据库名称
select database();
  • 使用数据库
use dbtest3;

DML

  • 对数据增删改

增加数据

  • 列名和值一一对应
INSERT INTO student(id, `name`, age) VALUES (1, 'zhangsan', 20);
  • 给所有列添加数据
INSERT INTO student VALUES (2, 'lisi', 20, 132.3, NULL, NULL);
INSERT INTO student VALUES (3, 'huangliu', 20, 132.3, "1892-11-12", NULL);
INSERT INTO student VALUES (3, 'huangliu', 20, 132.3, "1892-11-12", CURRENT_TIMESTAMP);

删除数据

  • 如果不加条件，删除表中所有记录
DELETE FROM student WHERE id=1;
  • 删除数据后新增数据导致主键不连续的解决方法，删除后执行，或者添加前执行
ALTER TABLE sys_user AUTO_INCREMENT = 1;
  • 删除表，然后创建一个一摸一样的空表
TRUNCATE TABLE student;

修改数据

  • 如果不加任何条件，会将表里的数据都改变
UPDATE student set age = 22, score = 233.4 WHERE id = 4;

DQL

基本查询

  • 查询所有数据
SELECT * FROM student;
  • 查询指定字段
SELECT name, age FROM student;
  • 去除重复查询
SELECT DISTINCT score FROM student;
  • 计算某两列
SELECT name, age, score, age + score FROM student;
//有null的归零
SELECT name, age, score, age + IFNULL(score,0) FROM student;
  • 起别名，省略as，用空格也可以
SELECT name, age, score, age + IFNULL(score,0) AS 总分 FROM student;
SELECT name, age math, score, age + IFNULL(score,0) 总分 FROM student;

条件查询

  • 大于小于等于
//大于
SELECT * FROM student WHERE age > 20;
//不等于 != 也可以
SELECT * FROM student WHERE age <> 31;
//并列
SELECT * FROM student WHERE age > 20 AND age < 40;
//包括20和30
SELECT * FROM student WHERE age BETWEEN 20 and 40;
//或者
SELECT * FROM student WHERE age = 20 OR age = 31;
SELECT * FROM student WHERE age IN (20, 35, 41);
  • 查询NULL
SELECT * FROM student WHERE birthday IS NULL;
SELECT * FROM student WHERE birthday IS NOT NULL;
  • 模糊查询，_单个任意字符，%多个人一字符
SELECT * FROM student WHERE `name` LIKE '_ad%';
//姓名是四个字符
SELECT * FROM student WHERE `name` LIKE '____';
//包含w
SELECT * FROM student WHERE `name` LIKE '%w%';

排序查询

  • 默认是升序 ASC
//降序
SELECT * FROM student ORDER BY age DESC;
//第一个排序一样时，才会执行第二个条件
SELECT * FROM student ORDER BY age ASC, score DESC;

聚合函数

  • 将一列数据作为整体，进行纵向的计算
  • 排除了非空的数据，一般选择非空的列
  • 数量
SELECT COUNT(`name`) FROM student;
//处理空值
SELECT COUNT(IFNULL(birthday,"0000-00-00")) 数量 FROM student;
SELECT COUNT(*) FROM student;
  • 其他
//最大值
SELECT MAX(score) FROM student;
//最小值
SELECT MIN(score) FROM student;
//和
SELECT SUM(score) FROM student;
//平均值
SELECT AVG(score) FROM student;

分组查询

  • 分组之后查询的字段：分组字段、聚合函数
  • 把某一字段相同的内容取出分组
SELECT age, AVG(score) FROM student GROUP BY age;
SELECT age, AVG(score), COUNT(id) FROM student GROUP BY age;
//在分组前进行条件限定
SELECT age, AVG(score), COUNT(id) FROM student WHERE score > 100 GROUP BY age;
//在分组后进行条件限定
SELECT age, AVG(score), COUNT(id) FROM student WHERE score > 100 GROUP BY age HAVING COUNT(id) > 1;
//别名判断
SELECT age, AVG(score), COUNT(id) number FROM student WHERE score > 100 GROUP BY age HAVING number > 1;
  • where在分组前进行限定，如果不满足条件，则不参与分组；不可以进行聚合函数的判断
  • having在分组之后进行限定，如果不满足结果，则不会被查询；可以进行聚合函数的判断

分页查询

  • 从第0天开始，查3条
SELECT * FROM student LIMIT 0,3;
  • 开始的索引 = （当前的页码 - 1） * 每页显示的条数

DCL

  • 定义数据库权限、创建用户
  • DBA数据库管理员
  • 查询，切换到mysql数据库
SELECT * FROM `user`;
  • 创建
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456789';
// %表示可以在任意主机使用用户登陆数据库
CREATE USER 'lisi'@'%' IDENTIFIED BY '123456789';
  • 删除
DROP USER 'zhangsan'@'localhost';
  • 修改密码
ALTER user 'lisi'@'%' IDENTIFIED BY '987654321';

权限管理

  • 查询权限
SHOW GRANTS FOR 'lisi'@'%';
  • 授予权限
//给lisi授予查询dbtest3.Commpany的查看权限
GRANT SELECT ON dbtest3.Commpany TO 'lisi'@'%';
GRANT SELECT, DELETE,UPDATE ON dbtest3.Commpany TO 'lisi'@'%';
//授予所有权限
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
  • 撤销权限
//撤销lisi对dbtest3.Commpany的更新权限
REVOKE UPDATE ON dbtest3.Commpany FROM 'lisi'@'%';
//撤销所有权限
REVOKE ALL ON *.* FROM 'zhangsan'@'localhost';

约束

  • 对表中的数据进行限定，保证数据的正确性、有效性和完整性

主键约束

  • primary key
  • 非空且唯一
  • 复合主键，不能重复 PRIMARY KEY (rid, uid)
  • 一张表只能有一个字段为主键，表中记录的唯一标识
CREATE TABLE per (
	id INT PRIMARY KEY,
	`name` VARCHAR(20) NOT NULL UNIQUE
);
  • 删除主键
ALTER TABLE per DROP PRIMARY KEY;
  • 添加主键
ALTER TABLE per MODIFY id INT PRIMARY KEY;
  • 自动增长，每次插入都会自加1，不成功也会，插入语句时不需要给主键值
CREATE TABLE per (
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL UNIQUE
);
  • 删除自动增长
ALTER TABLE per MODIFY id INT;
  • 添加自动增长
ALTER TABLE per MODIFY id INT AUTO_INCREMENT;

非空约束

  • not null
  • 不能为空，插入数据时，不能为空值
CREATE TABLE per (
	id INT,
	`name` VARCHAR(20) NOT NULL
);
  • 删除非空约束，直接修改字段类型
ALTER TABLE per MODIFY `name` VARCHAR(20);
  • 添加非空约束，但是表内这个字段的数据不能为空
ALTER TABLE per MODIFY `name` VARCHAR(20) NOT NULL;

唯一约束

  • unique
  • 值不能重复，null可以重复
CREATE TABLE per (
	id INT,
	`name` VARCHAR(20) NOT NULL UNIQUE
);
  • 删除唯一约束（唯一索引）
ALTER TABLE per DROP INDEX `name`;
  • 添加唯一约束
ALTER TABLE per MODIFY `name` VARCHAR(20) UNIQUE;

外键约束

  • foreign key
  • 格式 CONSTRAINT 外键名 FOREIGN KEY (外键列名称 本表的字段) REFERENCES 要关联的表的名称(主键名称)
  • 让表与表产生关联，从而保证数据的正确性
CREATE TABLE Department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(30) NOT NULL,
	dep_location VARCHAR(30) NOT NULL
);

CREATE TABLE Employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL UNIQUE,
	age INT NOT NULL,
	dep_id INT,
	CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES Department(id)
);
  • 删除外键
ALTER TABLE Employee DROP FOREIGN KEY emp_dep_fk;
  • 添加外键
ALTER TABLE Employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES Department(id);

级联操作

  • 要先删除外键约束，重新添加
  • 更新和删除，可以单独设置，也可以同时设置
  • 级联更新，改变被关联的表，原表内容也会被改变
ALTER TABLE Employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES Department(id) ON CASCADE;
  • 级联删除
ALTER TABLE Employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES Department(id) ON UPDATE CASCADE;

多表查询

  • 要完成多表查询，要消除无用的数据
  • 查询所有，笛卡尔积，由两个集合AB，取这两个集合的所有组成情况
SELECT * FROM employee, dept;

内连接查询

  • 隐式内连接
SELECT * FROM employee, dept WHERE employee.dept_id = dept.id;
SELECT employee.name, dept.name, gender FROM employee, dept WHERE employee.dept_id = dept.id;

//专业版
SELECT
	t1.name, t2.name, t1.gender
FROM
	employee t1, dept t2
WHERE
	t1.dept_id = t2.id;
  • 显式内连接
SELECT * FROM employee INNER JOIN dept ON employee.dept_id = dept.id;
//INNER 省略
SELECT * FROM employee JOIN dept ON employee.dept_id = dept.id;

外连接查询

  • 左外连接查询，查询的是左表所有的数据以及与右表的交集
SELECT
	t1.*, t2.`name`
FROM
	employee t1
LEFT JOIN
	dept t2
ON
	t1.dept_id = t2.id;
  • 右外连接查询，查询的是右表所有的数据以及与左表的交集
SELECT
	t1.*, t2.`name`
FROM
	employee t1
RIGHT JOIN
	dept t2
ON
	t1.dept_id = t2.id;

子查询

  • 查询中嵌套查询，嵌套的查询为子查询
  • 子查询的结果是单行单列的，大于小于等于的单一条件，一个条件，一个类型的返回
SELECT * FROM employee WHERE employee.salary = (SELECT MAX(salary) FROM employee);
//查询比平均工资少的
SELECT * FROM employee WHERE employee.salary < (SELECT AVG(salary) FROM employee);
  • 子查询的结果是多行单列的，多个条件，一种类型的返回
SELECT * FROM employee WHERE dept_id IN (SELECT id FROM dept WHERE name = '财务部' OR name = '市场部');
SELECT * FROM employee WHERE dept_id IN (SELECT id FROM dept WHERE name IN ('财务部', '市场部'));
  • 子查询的结果是多行多列的，多个条件，多个类型的返回，子查询可以作为虚拟表
SELECT * FROM dept t1, (SELECT * FROM employee WHERE employee.join_date > '2013-08-29') t2
WHERE t1.id = t2.dept_id;

事务

  • 如果一个包含多个步骤的操作被事务管理，要么同时成功提交事物，要么失败进行回滚
  • Mysql事务中默认自动提交，一条DML语句会自动提交一次事务；Oracle数据库默认手动提交
  • 修改事务的默认提交方式
//查看 1代表自动提交 0代表手动提交
SELECT @@autocommit;
//修改
SET @@autocommit = 0;
  • 开启事务
START TRANSACTION;
  • 成功，提交事物
COMMIT;
  • 失败，回滚到开启前的状态
ROLLBACK;

四大特征

  • 原子性，是不可分割的最小操作单位，要么同时成功，要么同时失败
  • 持久性，当事务提交或回滚后，数据库会持久化得保存数据
  • 隔离性，多个事务之间，相互独立
  • 一致性，事务操作前后，数据总量不变，例转钱操作中，两个人的钱合起来是永远不变的

隔离级别

  • 如果多个事务操作同一批数据，会引发一些问题，设置不同的隔离级别就可以解决这些问题
  • 问题
脏读，一个事务读取到另一个事务没有提交的数据
不可重复读（虚读），在同一个事务中，两次读取到的数据不一样
幻读，一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，但第一个事务查询不到自己的修改
  • 隔离级别，从小到大，安全性越来越高，效率越来越低
read uncommitted 读未提交 产生的问题 脏读、不可重复读、幻读
read committed 读已提交，处理的过程中的变化数据不可读 产生的问题 不可重复读、幻读（Oracle默认）
repeatable read 可重复读 产生的问题 幻读（mysql默认）
serializable 串行化，多线程加锁 解决的所有问题
  • 查询隔离级别
SELECT @@transaction_isolation;
  • 设置隔离界别，要重新打开连接才能设置成功
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

数据库

  • DataBase，DB

数据库的设计

一对一的关系

  • 正反都相对应，人和身份证
  • 在任意一方添加外键，指向另一张表的主键，为外键添加唯一约束

一对多的关系

  • 部门和员工
  • 在多的一方建立外键，指向一的一方的主键，员工的部分外键指向部门

多对多的关系

  • 学生和课程/老师
  • 需要借助第三张表，中间表至少包含两个字段，这两个字段作为中间表的外键，分别指向两张表的主键

案例

  • 旅游网页，多个线路对应一个类别，多个线路被多个用户收藏 image.png
-- 类别
CREATE TABLE tab_category (
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

-- 线路表
-- 主键省略了外键名称
CREATE TABLE tab_route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate DATE,
	cid INT,
	FOREIGN KEY (cid) REFERENCES tab_category(cid)
);

-- 用户表
CREATE TABLE tab_user (
	uid INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) NOT NULL UNIQUE,
	PASSWORD VARCHAR(30) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex CHAR(1) DEFAULT '男',
	telephone VARCHAR(11),
	email VARCHAR(100)
);


-- 中间表，处理用户表和线路表
CREATE TABLE tab_favorite (
	rid INT,
	DATE DATETIME,
	uid INT,
-- 复合主键
	PRIMARY KEY (rid, uid),
	FOREIGN KEY (rid) REFERENCES tab_route(rid),
	FOREIGN KEY (uid) REFERENCES tab_user(uid)
);

数据库范式

  • 递次规范
  • 第一范式，每一列都是不可分割的原子数据项
  • 第二范式，非码属性必须完全依赖于候选码，在1NF的基础上清除非主属性对主码的部分函数依赖，表中总有一个属性或者属性组，能决定其他所有属性
  • 第三范式，在2NF的基础上，任何非主属性不依赖于其他非主属性，消除传递依赖

概念

  • 函数依赖，如果A属性的值可以确定唯一的B数据，则B依赖于A，例如学号依赖于姓名
  • 完全函数依赖，如果A是一个属性组，B的确定依赖于A属性组的所有属性，例如学号+课程名称决定分数，分数完全依赖学号+课程名称
  • 部分函数依赖，如果A是一个属性组，B属性值的确定，只需要依赖A属性组的某一些属性，例如学号+课程中，只需要学号就可以确定姓名，姓名部分依赖于A
  • 传递函数依赖，如果通过A的属性（属性组）的值，可以唯一确定B属性的值，又可以通过B属性（属性组）的值，唯一确定C的值，则C传递函数依赖于A，例如姓名和学号可以确定系，系可以确定系主任
  • 码，一张表中，一个属性或者属性组，可以确定其他所有属性，例如学号+课程名称可以确定成绩、系、姓名等
  • 主属性：码属性组中的所有属性
  • 非主属性，非码属性的所有属性

备份与还原

命令行

  • 备份
mysqldump -uroot -p123456789 dbtest3 > /Users/apple/Desktop/test/my.sql
  • 还原
//先创建，再还原资源
create database dbtest3;
use dbtest3;
source /Users/apple/Desktop/test/my.sql;
avatar
文章
阅读
粉丝