MySQL数据库

知识点目标

1、MySQL的基本概念
2、MySQL的数据类型
3、DDL（建库建表）
4、DCL（权限控制）
5、DML（增删改）
6、DQL（查询）

MySQL的基本概念

数据库的概念

数据库Database，就是数据的仓库，项目中用于持久化保存数据，可以很方便的对数据进行管理。

数据库管理系统

DBMS（database management system）MySQL是属于一种专门用于管理数据库的软件

常用的DBMS：

• MySQL “开源”的数据库，被Oracle收购，功能全面，适用中小型项目
• Oracle 甲骨文公司的大型数据库，收费的，功能强大
• SQLServer 微软公司的，只能运行在Windows上
• PostgreSQL 开源的数据库，功能强大，适合中大型项目
• DB2 IBM的数据库，和服务器绑定

其它概念：

DBA：数据库管理员

MySQL的安装

玩转MySQL（一）MySQL的安装_1个月玩转mysql-CSDN博客

MySQL的使用

使用客户端连接MySQL

客户端：

• 控制台

• 第三方软件

  navicat

  workbench

创建数据库

导入导出数据库

导出数据：点击数据库右键，转储SQL文件，保存文件

导入数据：新建数据库，点数据库右键，运行SQL文件

创建表

表的相关概念

表由行和列组成

行也叫记录/数据…，一张表有多行记录，每一行有多个列，列也叫字段

设计表（一张表对应Java程序中的类，字段对应类的属性）

常用的字段类型：

• tinyint ----> java 的byte 1字节
• smallint ----> short 2字节
• mediumint —> 3字节
• int/integer 整数 4字节
• bigint 8字节 类似java的long
• float 4字节 单精度浮点型
• double 8字节 双精度
• decimal(n,m) 8字节 设置整数和小数位
• date 日期
• datetime 日期+时间
• timestamp 时间戳
• char(n) 固定长度字符类型
• varchar(n) 可变长度字符类型
• text 长文字
• bit 单个比特 0/1
• blob 长二进制字符串

面试题：char和varchar的区别

char是固定长度的，char(10)，如：存 abc 长度还是10，不足位置补空格

varchar是可变长度的，varchar(10)，存abc，只占三个长度

SQL语句

SQL（Structured Query Language）结构化查询语言，用于对数据库进行管理

分为：

• DDL 数据定义语言（对数据库、表、字段、约束等进行操作）
• DCL 数据控制语言（用户授权控制）
• DML 数据操作语言（数据增删改）
• DQL 数据查询语言（数据查询）

DDL

数据库操作

新建数据库

create database 数据库名;
use 数据库;
-- 不存在的情况下创建数据库
create database if not exists 数据库名;

删除数据库

drop database 数据库名;
-- 存在再删除
drop database if exists 数据库名;

表的操作

创建表：

create table 表名(
	字段名 类型 约束,
	字段名 类型 约束,
	字段名 类型 约束
);

约束起到控制数据值的作用，约束的类型：

• 主键，表只能有一个主键，不能重复，不能为空，用于唯一标识这行记录

  primary key

• 自增，只能用于整数类型，自动递增，不需要输入

  auto_increment

• 非空，强制要求输入

  not null

• 唯一，不能重复

  unique

-- 删除表
drop table student;
-- 创建学生表
create table student(
	id int primary key auto_increment,
    name varchar(20) not null unique,
	age int not null,
	gender varchar(1) not null,
	address varchar(200)
);

练习：创建汽车表：id、牌照、型号、价格、颜色、产地、出厂日期

id设置自增主键，其它字段都为非空，牌照不能重复

字段的操作

-- 修改表 添加新字段
alter table car add column product_date date not null;
-- 修改表 删除字段
alter table car drop column product_date;

DML

数据操作语言，包含对数据的增删改操作

插入

单行插入

insert into 表名(列名,列名,列名,列名) values(值,值,值,值);

多行插入

insert into 表名(列名,列名,列名,列名) values(值,值,值,值),(值,值,值,值),(值,值,值,值);

案例：

-- 单行插入
insert into car(number,type,price,color,address,product_date)
values('A0001','小米su7',300000.00,'银色','武汉','2023-11-12');
-- 多行插入
insert into car(number,type,price,color,address,product_date)
values('A0002','小米su8',350000.00,'银色','武汉','2023-11-11'),
('A0003','小米su9',350000.00,'银色','武汉','2024-11-11'),
('A0004','小米su10',450000.00,'银色','武汉','2024-11-12'),
('A0005','小米su11',550000.00,'银色','武汉','2024-11-15');

更新

update 表名 set 列=值,列=值,列=值 where 条件

where条件

• and 两个条件要同时成立
• or 两个条件成立一个

update car set price = price + 10000 where type = '小米su7' or type = '小米su8';

-- 给银色的小米su7、su8涨价10000，400000封顶
update car set price = price + 10000 
where color = '银色' and price <= 390000 and (type = '小米su7' or type = '小米su8');

删除

delete from 表 where 条件;

-- 删除0005
delete from car where number = 'A0005';
-- 清除所有数据 自增值没有删除
delete from car;
-- 清除所有数据 清理自增最大值
truncate table car;

练习：

1）向学生表插入5条数据

2）给武汉的女学生加2岁

3）设置id为3学生的年龄为25，性别为女，籍贯为上海

4）删除年龄超过25岁的男学生

CREATE DATABASE if not EXISTS testdb;

use testdb;

drop DATABASE testdb;

drop TABLE student;
CREATE TABLE `student` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(20) not null UNIQUE,
    `age` INT NOT NULL,
    `gender` VARCHAR(1) NOT NULL,
    `address` VARCHAR(200)
);

INSERT INTO `aigou`.`student` (`id`, `name`, `age`, `gender`, `address`) VALUES 
(1, '李四', 26, '男', '武汉'),
(2, '王五', 20, '女', '济南'),
(3, '赵六', 15, '女', '青岛'),
(4, '钱七', 24, '女', '孝感'),
(5, '孙八', 23, '男', '德州');

UPDATE student set age = age + 2 WHERE gender = '女' AND address = '武汉';

UPDATE student SET age = 25,gender = '女',address = '上海' WHERE id = 3;

DELETE FROM student WHERE age > 25 AND gender = '男';
SELECT * FROM student;

DCL

数据控制语言，用户和权限的操作，需要超管的权限完成

用户操作

create user '用户名'@'域名' identified with mysql_native_password by '密码';

域名：用户所在的服务器
localhost 或 本机的IP
%  代表可以通过远程主机访问

-- 创建用户并设置密码
create user 'qiangge'@'localhost' 
identified with mysql_native_password by '123456';

-- 删除用户
drop user '用户名'@'域名';

-- 设置root的远程连接
use mysql;
-- 修改root的域名为%
update user set host = '%' where user = 'root';
-- 刷新权限
flush privileges;

用户授权

grant 权限 on 数据库.表 to 用户名@域名;

权限：
select 		查询
insert 		插入
update		更新
delete		删除
create		创建库、表。。
drop		删除库、表。。
replication slave 主从复制
all			全部

数据库和表支持通配符 *
*.*

-- 授予权限
grant all on *.* to 'qiangge'@'localhost';
-- 刷新权限
flush privileges;

取消权限

revoke 权限 on 数据库.表 from 用户名@域名;

-- 取消权限
REVOKE select,insert,delete,update on *.* from 'qiangge'@'localhost';
-- 刷新权限
flush privileges;

案例

创建test1数据库，在其中创建一个书籍表，包含：id、书名、作者、出版日期、页数、类型、价格

1）插入几本书

2）给作者张三的书籍涨价5元

3）给页数在100以下，类型为小说的书籍降价5元，最低价不能低于10元

4）删除出版日期在2024-11-11日的书籍

5）创建test2数据库，在其中歌曲表，包含：id、歌名、播放时长、演唱者、类型

6）创建用户，能登录系统

7）给用户授权，能对test1的书籍表执行增删改查，只能对歌曲表进行查询

-- 创建test1数据库，在其中创建一个书籍表，包含：id、
-- 书名、作者、出版日期、页数、类型、价格
-- Title, author, publication date, page count, type, price

-- 1）插入几本书

-- 2）给作者张三的书籍涨价5元

-- 3）给页数在100以下，类型为小说的书籍降价5元，最低价不能低于10元

-- 4）删除出版日期在2024-11-11日的书籍

-- 5）创建test2数据库，在其中歌曲表，包含：id、歌名、播放时长、演唱者、类型

-- 6）创建用户，能登录系统

-- 7）给用户授权，能对test1的书籍表执行增删改查，只能对歌曲表进行查询

-- 创建 test1 数据库
CREATE DATABASE IF NOT EXISTS test1;

-- 使用 test1 数据库
USE test1;

-- 创建书籍表
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(20) NOT NULL,
    author VARCHAR(20) NOT NULL,
    publication_date DATE NOT NULL,
    page_count INT NOT NULL,
    type VARCHAR(20) NOT NULL,
    price DOUBLE NOT NULL
);

-- 删除表
DROP TABLE books;

-- 查询创建后的书籍表结构
SHOW COLUMNS FROM books;

-- 插入几本书
INSERT INTO books (title, author, publication_date, page_count, type, price) VALUES 
('书名1', '张三', '2023-01-01', 95, '小说', 25.00),
('书名2', '李四', '2023-02-01', 200, '科幻', 30.00),
('书名3', '张三', '2023-03-01', 90, '小说', 20.00),
('书名4', '王五', '2023-04-01', 120, '历史', 22.00),
('书名5', '张三', '2024-11-11', 80, '小说', 15.00);

DELETE FROM books;

-- 查询插入后的书籍表
SELECT * FROM books;

-- 给作者张三的书籍涨价5元
UPDATE books SET price = price + 5 WHERE author = '张三';

-- 查询更新后的书籍表
SELECT * FROM books;

-- 给页数在100以下，类型为小说的书籍降价5元，最低价不能低于10元
-- UPDATE books SET price = GREATEST(price - 5, 10) WHERE page_count < 100 AND type = '小说';
UPDATE books SET price = price - 5 WHERE page_count < 100 AND type = '小说' AND price > 10

-- 查询更新后的书籍表
SELECT * FROM books;

-- 删除出版日期在2024-11-11日的书籍
DELETE FROM books WHERE publication_date = '2024-11-11';

-- 查询删除后的书籍表
SELECT * FROM books;

-- 创建 test2 数据库
CREATE DATABASE IF NOT EXISTS test2;

-- 使用 test2 数据库
USE test2;

-- 创建歌曲表
CREATE TABLE songs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    duration TIME NOT NULL,
    singer VARCHAR(50) NOT NULL,
    type VARCHAR(20) NOT NULL
);

-- 删除歌曲表songs
DROP TABLE songs;;

-- 查询创建后的歌曲表结构
SHOW COLUMNS FROM songs;

-- 创建用户
CREATE USER 'qhq'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

-- 给用户授权，能对 test1 的书籍表执行增删改查
GRANT ALL PRIVILEGES ON test1.books TO 'qhq'@'localhost';

-- 给用户授权，只能对 test2 的歌曲表进行查询
GRANT SELECT ON test2.songs TO 'qhq'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查询用户权限
SHOW GRANTS FOR 'qhq'@'localhost';