11.MySQL

一、MySQL简介

数据库 DB

存储、维护和管理数据的集合 (文件系统 )，MYSQL关系型数据库，用于保存实体和实体之间的关系

数据库管理系统

    指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统 访问数据库中的数据 

 

数据库软件应该为 数据库管理系统，数据库是通过数据库管理系统创建和操作的

 

安装了Tomcat的是Web服务器，安装了MySQL的是数据库服务器

 

Oracle：大型

DB2：可大可小

SQL Server：Windows

MySQL：中小型

 

数据库的安装

1、安装路径不要有中文（重要），不要有空格
2、MySQL默认端口是3306，不要修改。
3、设置MySQL的编码集（采用UTF-8的编码）
4、要把黑窗口的勾勾选上。

数据库的卸载

找到MySQL的安装路径，找到my.ini的配置文件。
basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/" 安装的路径
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" MySQL存储数据的路径
1、通过控制面板卸载MySQL
2、找到上面的两个路径，删除文件夹

 

二、MySQL管理

1. 停止mysql 服务 net stop mysql，要是遇到错误5，就用管理员权限启动cmd

2.启动mysql 服务 net start mysql

3.登录 Mysql  mysql  -uroot  -proot

4.修改密码  

（1）停止 mysql 服务(services.msc )

（2）mysqld --skip-grant-tables 启动服务器

（3）新打开 cmd 输入mysql -u root -p 不需要密码(开启一个mysql服务,不需要进行认证)， show databases;查看数据库，输入命令 use mysql;使用mysql数据库，修改密码：update user set password=password('abc') WHERE User='root'

（4）关闭两个cmd 窗口，在任务管理器结束 mysqld 进程

（5）重启 mysql 服务

 

针对每一个应用创建一个数据库，在数据库创建多个表，以保存程序中实体的数据。

数据库中一行记录与对象之间的关系：

列：字段

行：一条记录 ( 实体)

MySQL中叫字段，字段和Java中的类的属性是一一对应的，每一条记录对应是一个Java实例对象

 

 三、SQL语句

SQL结构化查询语言

第四代语言，Java第三代语言

各数据库厂商都支持 ISO 的SQL 标准，厂商在标准的基础上做了自己的扩展

 

分类

DDL：数据定义语言，用来定义数据库对象：库、表、列等； CREATE 、ALTER、DROP

DML：数据操作语言，用来定义数据库记录（数据）； INSERT 、UPDATE、DELETE

DQL：数据查询语言，用来查询记录（数据），SELECT

DCL：数据控制语言，用来定义访问权限和安全级别； IF ELSE，不用

 

注意： sql 语句以 ;结尾

CURD : 增删改查

1、DDL(管理数据库)

（1）创建

Create database mydb1;
Create database mydb2 character set gbk;
Create database mydb3 character set gbk COLLATE gbk_chinese_ci;

（2）查询

show databases;  // 查看当前数据库服务器中的所有数据库
Show create database mydb2;  // 查看前面创建的 mydb2数据库的定义信息
Drop database mydb3;  // 删除前面创建的 mydb3数据库

（3）修改

alter database mydb2 character set utf8;  // 查看服务器中的数据库，并把 mydb2 的字符集修改为 utf8

（4）删除

drop database mydb3;  

（5）查看当前使用的数据库

select database();

（6）切换数据库

use mydb2;

（7）操作数据表

create table 表名 (
  字段 1 字段类型 ,
  字段 2 字段类型 ,
  ...
  字段 n 字段类型
  );

（8）当前数据库中的所有表

SHOW TABLES;

（9）查看表的字段信息

DESC employee;

常用数据类型

（1）数值型
TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE

int：整型

double：浮点型，例如 double(5,2) 表示最多5 位，其中必须有 2 位小数，即最大值为 999.99；

 

（2）逻辑性 对应boolean
BIT

 

（3）字符串型

char ：固定长度字符串类型； char(10) 'abc '，无字符串，性能高

varchar ：可变长度字符串类型； varchar(10) 'abc'，常用

 

（4）大数据类型（一般不用）

text ：字符串类型 ;大量数据，二进制

blob ：字节类型；图片视频音频

 

（5）日期型

DATE、TIME、DATETIME、TIMESTAMP

date ：日期类型，只包含日期，格式为： yyyy-MM-dd ；

time ：时间类型，只包含时分秒，格式为： hh:mm:ss

timestamp ：时间戳类型，不传入数据，默认选择当前系统时间， yyyy-MM-dd hh:mm:ss 会自动赋值，不太实用，到2023年就不能用了

datetime: 日期时间类型，需要手动录入时间，yyyy-MM-dd hh:mm:ss

2、DML 操作 (重要 )

DML 是对表中的数据 进行增、删、改的操作。不要与 DDL (对表)混淆了。

INSERT 、 UPDATE 、 DELETE

 

在mysql 中，字符串类型和日期类型都要用单引号括起来。 'tom' '2015-09-04'

数据与字段的类型相同，字段长度需要控制

空值：null（不占内存）

 

解决中文乱码的问题：

[client]
port=3306
[mysql]
default-character-set=gbk，修改完要重启服务

 

1、插入INSERT

 INSERT INTO 表名（列名 1 ，列名2 ... ） VALUES(列值 1 ，列值2...);

如果插入空值，请使用 null

插入的日期和字符一样，都使用引号括起来

每一列全部给值，列值就不用写

 

sqlyog82、navicat第三方客户端界面

 

2、修改UPDATE

UPDATE 表名 SET 列名1= 列值 1，列名 2= 列值2 。。。 WHERE 列名 =值

where相当于if

 

3、删除DELETE

delete from 表名 [where ];

truncate 表名; 删除所有的数据;

truncate 和 delete的区别：
（1）truncate删除数据，先删除整个表。再创建一个新的空的表。（效率）
（2）delete删除数据，一条一条删除的。（*****）
（3）事物（insert update delete）

 

3、DQL（重要，查询） 12种

（1）查询所有列

SELECT * FROM 表名 ;

（2）查询指定列

SELECT  id,NAME,gender  FROM  student;

（3）查询时给字段添加别名( AS )

SELECT NAME AS '姓名',sex AS '性别' FROM student;
SELECT NAME '姓名',sex '性别' FROM student;  // 也可省略AS

（4）查询时添加常量列

SELECT id,NAME,gender,age,'你好' AS 'hello'  FROM student;
// as设置别名

（5）查询时合并列

SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student;

（6）查询时去除重复记录（DISTINCT）

SELECT DISTINCT gender FROM student;

（7）条件查询

条件查询(where)

1.逻辑条件： and(与)     or(或)

SELECT * FROM student WHERE id=2

2. 比较条件： >   <   >=  <=  =  <>(不等于)     between and (等价于>= 且 <=)

SELECT * FROM student WHERE servlet>70;

3. 判空条件(null 空字符串)：  is null / is not null / =''  / <>''

null 无值

‘’ 空字符串  有值

SELECT * FROM student WHERE address IS NULL
SELECT * FROM student WHERE address='';

4.模糊条件： like

通常使用以下替换标记

% : 表示任意个字符

_ : 表示一个字符

SELECT * FROM student WHERE NAME LIKE '李%';

（8）聚合查询

聚合查询（使用聚合函数的查询）

常用的聚合函数： sum()  avg()  max()  min()  count()

SELECT SUM(servlet) AS 'servlet的总成绩' FROM student;
SELECT AVG(servlet) AS 'servlet的平均分' FROM student;
SELECT MAX(servlet) AS '最高分' FROM student;
SELECT MIN(servlet) AS '最低分' FROM student;
SELECT COUNT(*) FROM student; // 多少个学生
SELECT COUNT(id) FROM student; // 

count() 函数统计的数量不包含null的数据，使用count统计表的记录数，要使用不包含null值的字段

（9）分页查询

分页查询（limit 起始行,查询几行）

起始行从0开始

分页：当前页  每页显示多少条

分页查询当前页的数据的sql: SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;

SELECT * FROM student LIMIT 0,2;  // 查询第1,2条记录（第1页的数据）
SELECT * FROM student LIMIT 2,2;  // 查询第3,4条记录（第2页的数据）
SELECT * FROM student LIMIT 2,2;  // 查询第5,6条记录（第3页的数据）
SELECT * FROM student LIMIT 6,2;  // 查询第7,8条记录 (没有记录不显示)

（10）查询排序（order by）

order by 字段 asc/desc

asc: 顺序，正序。数值：递增，字母：自然顺序（a-z），默认

desc: 倒序，反序。数值：递减，字母：自然反序(z-a)

SELECT  *  FROM student ORDER BY id ASC;

（11）分组查询(group by)

SELECT gender,COUNT(*) FROM student GROUP BY gender;

（12）分组查询后筛选

SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;

 四、数据约束

给表添加数据约束，从而约束用户操作表数据的行为

 1、默认值约束（DEFAULT）

CREATE TABLE test(
    NAME VARCHAR(20),
    gender VARCHAR(2) DEFAULT '男'
)

INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME,gender) VALUES('张三',NULL);

SELECT * FROM test;

当前没有插入默认值字段的时候，默认值才会起作用

 2、非空（not null）

CREATE TABLE test(
    NAME VARCHAR(20) NOT NULL,
    gender VARCHAR(2)
)

不能不插入值。不能为null

3、唯一约束

CREATE TABLE test(
    id INT UNIQUE,
    NAME VARCHAR(20)
)

4、主键约束

 主键约束(primary key)（唯一+非空）

注意：

（1）通常情况下，我们会给每张表都会设置一个主键字段，用来标记记录的唯一性

（2）但是不建议把业务含义字段作为主键，因为随着业务的变化，业务字段可能会出现重复。

（3）建议给每张张独立添加一个叫id的字段，把这个id字段设置成主键，用来作为记录的唯一性

 

 5、自增

AUTO_INCREMENT

 

6、外键

外键作用： 约束两种表的数据

当有了外键约束之后，操作数据（管理数据）的顺序如下：

插入数据： 先插入主表的数据，再插入副表数据

修改数据： 先修改主表数据，再修改副表数据

删除数据： 先删除副表数据，再删除主表数据

//  员工表（副表： 被别的表约束。外键设置在副表）
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
    　　　　　　　　　　　　　　//   外键名称   外键字段  参考          
)

//  部门表（主表：约束别人的表）
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)

7、级联技术

级联： 当有了外键的时候，我们希望修改或删除数据的时候，修改或删除了主表的数据，同时能够影响副表的数据，这时就可以使用级联。

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    -- 添加级联修改： ON UPDATE CASCADE
    -- 添加级联删除： ON DELETE CASCADE 
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 
    --          外键名称                     外键字段  参考          
)

 五、数据库设计

三大范式：

1、 要求表的每个字段必须独立的不可分割的单元

2、在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的。一张表应该只表达一个意思

3、在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系

 

六、多表查询

1、交叉连接查询

产生笛卡尔积

SELECT employee.name,dept.name FROM employee,dept;

多表查询的步骤： 1）确定查询哪些表  2）确定查询哪些字段  3）确定连接条件(规则： 表数量-1)

2、内连接查询（使用最多）

SELECT e.name,d.name
    FROM employee e,dept d
    WHERE e.deptId=d.id;

SELECT e.name,d.name
    FROM employee e
    INNER JOIN dept d
    ON e.deptId=d.id;

3、左外连接查询（其次）

左外连接查询效果： 左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候，就显示满足条件的的数据，但是如果不满足连接条件，则显示null

SELECT d.name,e.name
    FROM dept d
    LEFT OUTER JOIN employee e
    ON d.id=e.deptId;    

4、右外连接查询

右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，就显示满足条件的数据，但是如果不满足连接条件，则显示null

SELECT d.name,e.name
    FROM employee e
    RIGHT OUTER JOIN dept d
    ON d.id=e.deptId;   

5、自连接查询

SELECT e.name AS '员工',b.name AS '上司'
    FROM employee e
    LEFT OUTER JOIN employee b
    ON e.bossId=b.id;

七、存储过程

存储过程，其实就是带逻辑的（多个）sql语句。也是sql编程

1、特点：

（1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程

（2）存储过程的效率会非常高！因为存储过程是在数据库服务器端执行。

（3）存储过程的移植性非常差的！

2、语法：

创建存储过程
-- 定义结束符号
DELIMITER 结束符号
CREATE PROCEDURE 存储过程名称 (形式参数列表) 
BEGIN
    多个sql语句
END 结束符号

-- 调用存储过程
CALL 存储过程名称(实际参数列表);

参数类型：
IN： 输入参数，可以携带数据到存储过程中
OUT: 输出参数，可以携带数据到存储过程外面。
INOUT: 输入输出参数。

--带有输入参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testByIn(IN eid INT)  -- 参数类型（IN） 参数名称 数据类型(int)
BEGIN
    SELECT * FROM employee WHERE id=eid;
END $

-- 调用
CALL pro_testByIn(2);

--带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))
BEGIN
    -- 修改变量n
    SET n = '输出参数';
END $

定义变量去接收输出参数数据

--带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))
BEGIN
    -- 查看n变量
    SELECT n;
    -- 修改n变量
    SET n = '500';
END $

-- 定义会话变量调用存储过程
SET @n='100';
CALL pro_testByInOut(@n);
-- 查看n
SELECT @n;

--带有判断条件的存储过程
DELIMITER $
CREATE PROCEDURE pro_testByIf(IN num INT,OUT str VARCHAR(20))
BEGIN
    IF num=1 THEN
        SET str = '星期一';
    ELSEIF num= 2 THEN
        SET str ='星期二';
    ELSEIF num=3 THEN
        SET str = '星期三';
    ELSE
        SET str = '错误参数';
    END IF;
END $

CALL pro_testByIf(5,@str);
SELECT @str;

--带有循环条件的存储过程
DELIMITER $
CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT)
BEGIN
    -- int result =0;
    -- for(int i=1;i<=100;i++){
    --     result += i;
    -- }
    
    -- 定义局部变量
    DECLARE    i INT DEFAULT 1;
    DECLARE result INT DEFAULT 0;
    WHILE i<=num DO
        SET result = result + i;
        SET i = i+1;
    END WHILE;
    SET score = result;
END $


CALL pro_testByWhile(200,@score);
SELECT @score;

--携带数据库的数据给输出参数(INTO)
DELIMITER $
CREATE PROCEDURE pro_testByData(IN eid INT,OUT sname VARCHAR(20))
BEGIN
    SELECT NAME INTO sname FROM employee WHERE id=eid;
END $

CALL pro_testByData(2,@sname);
SELECT @sname;ss

3、mysql数据库三种变量：

（1）全局变量。mysql内置的变量，mysql程序关闭的时候全局变量才会失效！！

character_set_client: mysql接收的客户端的数据编码

character_set_results： mysql使用什么编码输出给客户端数据

查看某个全局变量： select @@变量名

修改某个全局变量： set @@变量名=值

（2）会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效！！  

查看某个会话变量： select @变量名

修改/定义某个会话变量： set @变量名=值

（3）局部变量：在存储过程中定义的变量。存储过程结束局部变量失效！！

查看某个局部变量： select 变量名

修改某个局部变量： set 变量名=值

定义某个局部变量： declare 变量名 数据类型;

-- 定义会话变量去接收输出参数数据            
-- set @n='eric';
CALL pro_testByOut(@n);
-- 查看会话变量n
SELECT @n;

八、触发器

当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完成