MySQL基础
文章目录
一、数据库概述、MySQL概述
1、数据库概述
DB、DBS、DBMS、SQL、JDBC
数据库系统是指基于数据库的应用系统,是一种按照数据库方式存储、管理并向用户或应用系统提供数据支持的系统。这种系统通常由数据库和应用程序两部分组成,并在数据库管理系统(DBMS)的支持下开发。
数据库:数据库是按照数据结构来组织、存储和管理数据的仓库,是存储在一起的相关数据的集合。
主流数据库:(关系型)Oracle,SQL Server,MySQL,Access,DB2 关系数据库:以关系(特定形式的二维数据表)的形式来表现数据以及数据之间的关系。
(非关系型)MongoDB
SQL:一门语言,现已发展为关系数据库的标准语言。
JDBC数据库接口:使用Java等程序设计工具开发数据库应用程序时,首先要使用某种“数据库接口”连接数据库。目前比较流行的数据库接口有ODBC、JDBC(Java)和ADO.NET等。
2、MySQL概述
MySQL隶属Oracle
1、MySQL数据库的安装与配置
-
官网下载MySQL8.0.22
-
安装教程:MySQL8.0.20下载与安装详细图文教程,mysql安装教程 - mysql安装配置教程 - 博客园
workname:MySQL80 username:root pwd:root
-
MySQL服务的启动与停止
两种方式:
一、win+r,services.msc 二、cmd管理员身份运行, 停止:net stop mysql80 启动:net start mysql80
MySQL客户端的登录与退出
一、开始程序-MySQL8.0 Command Line Client //但只能root用户
二、cmd管理员身份运行
mysql -h localhost -P 3306 -u root -p //-h host -P 3306 Port
或简写:mysql -u root -proot
退出:quit
2、MySQL常见命令
cmd中输入:
客户端登录:mysql -u root -proot
查看版本号:select version();或 mysql -V(cmd中输入)
退出:exit
查看当前所有数据库:show databases;
打开指定数据库:use 库名
查看数据表:show tables;
查看其它库的所有表:show tables from 库名;
创建表
create table 表名(
列名 列类型,
列名 列类型,
...
);
查看表结构:desc 表名;
MySQL的语法规范:
- 不区分大小写,但建议关键字大写,表名、列名小写
- 每条命令分号结尾
- 注释
- 单行注释:#注释文字 – 注释文字(–后需加空格)
- 注意:SQL Server单行注释:–注释
- 多行注释:/**/
- 单行注释:#注释文字 – 注释文字(–后需加空格)
MySQL的命名规范:
- 数据库命名规范
- 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’__'组成,命名简洁明确,多个单词用下划线分隔,**一个项目一个数据库,**多个项目慎用同一个数据库
- 数据表命名规范
- 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_'组成,命名简洁明确,多个单词用下划线分隔
- 全部小写命名,禁止出现大写
- 禁止使用数据库关键字,如:name,time ,datetime,password等
- 用单数形式表示名称,例如,使用 employee,而不是 employees
- 数据库字段命名规范
- 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_'组成,命名简洁明确,多个单词用下划线分隔
- 全部小写命名,禁止出现大写
- 禁止使用数据库关键字,如:name,time ,datetime,password等
个人总结:
- 一个项目一个数据库
- 数据表用单数表示名称
- 数据表、字段,禁止使用数据库关键字
- 无论数据库、表、字段,统一小写+下划线
二、SQL基本语法
SQL分类
- DDL(Data Definition Language) 数据定义语言
- 用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等
- DML(Data Manipulation Language) 数据操作语言
- 用来对数据库中表的数据进行增删改。关键字:insert,delete,update等
- DQL(Data Query Language) 数据查询语言
- 用来查询数据库中表的记录(数据)。关键字:select,where等
- DCL(Data Control Language) 数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别,及创建用户等。关键字:GRANT,REVOKE等
1、DDL_数据定义语言
数据类型
字符串 | 整型 | 日期 |
---|---|---|
VARCHAR(1~8000) | INT(整型) 4字节 | DATE(只存储日期,没有时间) |
NVARCHAR(1~4000) | BIGINT(长整型) 8字节 | DATETIME(存储日期和时间,精确到毫秒) |
CHAR(1~8000) | NUMRIC(有小数点,38位) | SMALLDATETIME(存储日期和时间,精确到分钟) |
NCHAR(1~4000) | FLOAT(浮点型) | TIME(小时 分钟 秒) |
TEXT(2^31-1) | DOUBLE | |
NTEXT | MONEY(钱) | |
SMALMONEY(小钱) | ||
BIT(0,1)(相当于bool) | ||
TINYINT(相当于bit 0~255)1字节 |
注:
- 带N的是Unicode字符(万国码)
- CHAR是定长,赋值长度不够时,用空格填充。而VARCHAR是不定长的。故而通常采用VARCHAR
操作数据库:(CRUD)
-
C(create)
create database db1; create database if not exists db1; create database db2 character set gbk;
-
R(retrieve)
show databases; //查询所有数据库的名称 show create database `test`; //查询数据库test的字符集(查询某个数据库的创建语句)
-
U(update)
修改数据库的字符集 alter database 数据库名称 character set 字符集名称;
-
D(delete)
drop database 数据库名称; drop database if exists 数据库名称;
-
使用数据库
use 数据库名称; select database(); //查询当前数据库
操作数据表:(CRUD)
-
C(create)
create table 表名( 列名1 数据类型1, 列名2 数据类型2, ... 列名n 数据类型n );
-
R(retrieve)
show tables; //查询当前数据库所有数据表的名称 desc 表名; //查询表结构
-
U(update)
修改表名 alter table 表名 rename to 新的表名 修改表的字符集 alter table 表名 character set utf8; 修改列 添加列 alter table 表名 add 列名 数据类型; 删除列 alter table 表名 drop 列名; 修改列名,类型 alter table 表名 change 原列名 新列名 新数据类型; alter table 表名 modify 原列名 新数据类型;
-
D(delete)
drop table 数据表名称; drop table if exists 数据表名称;
-
复制数据表
create table 表名 like 被复制的表名
2、DML_数据操作语言
增加
INSERT INTO students
(学号,班级,姓名,性别,年龄)
VALUES
('003','5班','小王','')
注:自增列不需要手动赋值
允许为null的可以不添加
删除
注:删除的时候,一定要写where语句
DELETE FROM students WHERE ID='001' AND ...
TRUNCATE TABLE students; -- 删除表,然后再创建一个一模一样的空表。需要删除整张表数据时,推荐使用,效率更高
修改
注:修改的时候,一定要写where语句
UPDATE students
SET age='15',
sex='男'
WHERE ID='002'
3、DQL_数据查询语言
语法:
SELECT 字段列表 FROM 表名列表
WHERE 条件列表
GROUP BY 分组列表
HAVING 分组之后的条件
ORDER BY 排序
LIMIT 分页限定
eg:
SELECT ID,name FROM students
WHERE sex='男' AND ...OR... (为了方便理解,可以加上括号)
--BETWEEN AND(相当于>= AND <=) (NOT BETWEEN AND)
或
WHRER ID in (001,003); --IN查询 (NOT IN) --IN遍历查询,耗费时长,不建议使用
ORDER BY ageDESC DESC,ID ASC (正序:ASC。倒序:DESC)
--ORDER BY 2,3 DESC (按照查询显示列排)(从1开始,而不是0)
DISTINCT 去重(查询唯一值)
TOP 查询前多少行
SELECT DISTINCT ID
SELECT TOP 行数 ID
SQL关键字执行顺序
1.查询中用到的关键词主要包含七个,并且他们的顺序依次为
select–from–where–group by–having–order by–limit
2、执行顺序
from–where–group by–having–select–order by–limit,
0、基础查询小技巧
- DISTINCT 去重
- 计算列
- 一般可以使用四则表运算计算一些列的值。(一般只会进行数值型的计算)
- ifnull(表达式1,表达式2)。表示某字段为null后的替换值
- 起别名
- as:as可以省略
1、条件查询where
- =、>、<、!=或<> AND、OR、NOT
- BETWEEN…AND
- (相当于>= AND <=),前面是较小值,后面是较大值
- IN(集合)
- IN 在…里 NOT IN 不在…里 --比较慢,消耗资源,不推荐
- IS NULL、IS NOT NULL
- null不能使用=、!=判断
LIKE(模糊查询)
- 占位符:
- _: 单个字符
- %: 多个字符
SELECT * FROM students WHERE name LIKE '%明%'
2、排序查询(order by)
order by 排序字段1 排序方式1,排序字段2 排序方式2...
排序方式
- ASC:升序,默认
- DESC:降序
eg:
ORDER BY ageDESC DESC,ID ASC
3、聚合函数
**5个聚合函数:**AVG()和SUM(),MIN()和MAX(),COUNT()
-
注意:所有聚合函数都排除null值
-
解决排除null值方案
-
选择不包含null的列进行计算,一般选择主键
-
IFNULL
COUNT(IFNULL(english,0))
-
4、分组查询(group by)
- 分组之后查询的字段:分组字段、聚合函数
- having与where的区别
- 执行顺序不同:where在分组之前,having在分组之后
- where后不可以跟聚合函数,having可以进行聚合函数的判断
eg:
SELECT sex,AVG(score),COUNT(id) as 人数 FROM students
WHERE score>70
GROUP BY sex
HAVING 人数 > 2;
5、分页查询(limit)
语法:
limit 开始的索引,每页查询的条数
eg:
SELECT * FROM students LIMIT 0,3;
公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
三、数据库设计
1、约束
约束分类:
- 主键约束:primary key
- 非空约束:not null
- 唯一约束:unique
- 外键约束:foreign key
非空约束:not null
-
创建表时添加约束
CREATE TABLE stu( id INT, name VARCHAR(20) NOT NULL -- name为非空 )
-
创建表完后,添加约束
ALTER TABLE stu MODIFY `name` VARCHAR(20) NOT NULL ALTER TABLE stu MODIFY `name` VARCHAR(20) -- 删除非空约束
唯一约束:unique,值不能重复,但可以为null
ALTER TABLE stu DROP INDEX phone_number; -- 删除唯一约束 注意:与删除非空约束不同
主键约束:primary key,非空且唯一
添加主键
id INT PRIMARY KEY,
ALTER TABLE stu DROP PRIMARY KEY; -- 删除主键
自动增长:auto_increment (自动增长的数据只跟上一条的数据有关系)
ALTER TABLE stu MODIFY id int auto_increment; -- 添加自动增长
ALTER TABLE stu MODIFY id INT; -- 删除自动增长
外键约束:foreign key
语法
添加外键
1)创建表时
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
eg:
CONSTRAINT stu_class_fk FOREIGN KEY (class_id) REFERENCES class(class_id)
2)修改表时
alter TABLES stu ADD CONSTRAINT stu_student_fk FOREIGN KEY (id) REFERENCES students(id)
删除外键
ALTER TABLE stu DROP FOREIGN KEY stu_student_fk;
注意:
- 要明确应该在哪个表中添加主键,一般如员工、学生等(而不是添加在部门、班级等)。(以防当删除部门时,员工没有了部门;或者将员工添加到一个不存在的部门)
- 这个表里面设置的外键必须是另外一个表的主键
外键约束–级联操作
-
添加外键,设置级联更新,设置级联删除
- 级联更新:ON UPDATE CASCADE
- 级联删除:ON DELETE CASCADE
alter TABLES stu ADD CONSTRAINT stu_student_fk FOREIGN KEY (id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE
注意:级联降低性能,谨慎使用
2、多表之间的关系
- 一对一:
- 一对多(多对一):
- 在多的一方添加外键,指向“1”的一方的主键
- 多对多
- 多对多关系实现需要借助中间表。中间表至少包含两个字段,分别指向两张表的主键。这两个字段可以组成联合主键(复合主键),以确保唯一性
3、数据库设计的范式
数据库设计范式:即设计数据库时,需要遵循的一些规范
设计关系数据库时,遵从不同的规范,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
分类:
-
第一范式(1NF):每一列都是不可分割的原子数据项
-
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
即:消除部分函数依赖
几个概念
- 函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A
- 例如:学号–>姓名,(学号,课程名称)–>分数
- 完全函数依赖:A–>B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值
- 完全函数依赖是函数依赖的子集。
- 部分函数依赖:A–>B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某一些值即可
- 例如,(学号,课程名称)–>姓名
- 传递函数依赖:A–>B,B–>C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A。即如果B依赖于A,C依赖于B,则C传递函数依赖于A
- 码:如果在一张表中,一个属性或属性组,被其他所有属性完全依赖,则称这个属性(属性组)为该表的码
- 主属性:码属性组中的所有属性
- 非主属性:除码属性组的属性
- 函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A
-
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
即:消除传递函数依赖
注:三大范式解决的问题:原子项、部分依赖、传递依赖
4、数据库的备份和还原
-
命令行
-
备份:mysqldump -u用户名 -p密码 数据库名 > 保存的路径
-
还原
- 登录数据库
- 创建数据库
- 使用数据库
- 执行文件 source 文件路径
-
-
图形化工具
四、多表查询、事务、DCL
多表查询
笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况
多表查询分类:
-
内连接查询
- 隐式内连接:使用where消除无用信息
- 显式内连接:
- 语法:select 字段列表 from 表名1 join 表名2 on 条件
-
外连接查询
- 左外连接:以左边表为主。查询左表所有数据以及两表交集部分
- 语法:select 字段列表 from 表名1 left join 表名2 on 条件
- 右外连接::以右边表为主。查询右表所有数据以及两表交集部分
- 左外连接:以左边表为主。查询左表所有数据以及两表交集部分
-
子查询
概念:查询中嵌套查询,称嵌套查询为子查询
事务
-
事务概念 数据库事务,Database Transaction
-
作为单个逻辑工作单元执行的一系列操作,要么完全执行,要么完全不执行
-
三个重要操作:
- 开启事务:start transaction
- 回滚事务:rollback
- 提交:commit
eg:
-- 转账 #0.开启事务 START TRANSACTION; #1.tom账户减500 UPDATE account SET balance=balance-500 WHERE `name`='tom'; -- fgh #2.john账户加500 UPDATE account SET balance=balance+500 WHERE `name`='john'; #3.提交或回滚事务 -- 发现执行没有问题,提交事务 COMMIT; -- 发现执行出现问题,回滚事务 ROLLBACK;
-
MySQL数据库中事务默认自动提交
- 事务提交的两种方式
- 自动提交
- MySQL自动提交
- 一条sql语句会自动提交一次事务。
- 手动提交
- Oracle数据库默认手动提交事务
- 需要先开启事务,再提交
- 自动提交
- 查看事务的默认提交方式
- 查看事务的默认提交方式:select @@autocommit; – 1代表自动提交,0代表手动提交
- 修改默认提交方式:set @@autocommit = 0;
- 事务提交的两种方式
-
事务,必须满足所谓的ACID(原子性、一致性、隔离性和持久性)
-
事务是数据库运行中的逻辑工作单位,由DBMS中的事务管理子系统负责事务的处理
-
-
事务的四大基本特征(ACID)
- 原子性:事务是不可分割的最小操作单元,要么同时成功,要么同时失败
- 持久性:当事务提交或回滚后,数据库会持久性的保存数据
- 隔离性:多个事务之间,相互独立,
- 一致性:事务操作前后,数据总量不变
-
事务的隔离级别(了解)
-
多个事务之间,相互独立。但如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
-
存在的问题:
- 脏读:一个事务,读取到另一个事务中没有提交的数据
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
- 幻读:一个事务操作数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
-
隔离级别
- read commit:读未提交
- 产生的问题:脏读、不可重复读、幻读
- read commited:读已提交(Oracle默认)
- 产生的问题:不可重复读、幻读
- repeatable:可重复读(MySQL默认)
- 产生的问题:幻读
- serializable:串行化
- 可以解决所有的问题
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
- read commit:读未提交
-
DCL_数据控制语言
- 管理用户
- 授权