MySQL数据库及 SQL语句快速入门

madbeen

已于 2022-05-06 22:52:17 修改

阅读量209

点赞数

分类专栏：数据库原理文章标签： mysql sql 数据库 java navicat

于 2021-04-16 20:27:09 首次发布

本文链接：https://blog.csdn.net/weixin_43874676/article/details/115771152

版权

数据库原理专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL

Structured Query Language 结构化查询语言

DDL (Data Definition Language) 数据定义语言

操作数据库、表
DML (Data Manipulation Language) 数据操作语言

增删改
DQL (Data Query Language) 数据查询语言

查询
DCL (Data Control Language) 数据控制语言

授权

DDL

操作数据库：CRUD

# 查询
SHOW DATABASES;
# 创建
CREATE DATABASE `db1`;
# 查询数据库
SHOW CREATE DATABASE `db2`;
# 带判断创建
CREATE DATABASE IF NOT EXISTS `db1`;
# 创建并指定字符集
CREATE DATABASE IF NOT EXISTS `db2` CHARACTER SET GBK;
# 删除数据库
DROP DATABASE IF EXISTS `db2`; 
# 修改数据库
ALTER DATABASE `db1` CHARACTER SET gbk;
# 使用数据库
USE `db1`;
# 查询正在使用的数据库
SELECT DATABASE();

操作表

# 查询表
SHOW TABLES;
# 查询表结构
DESC stu_info;

数据类型

int 整数
daouble 小数,DOUBLE(4,1) = 小数点后1位，最大4位 100.0
date 日期，只包含yyyy-MM-dd
datetime，包含 yyyy-MM-dd HH:mm:ss
timestamp，时间戳 yyyy-MM-dd HH:mm:ss，若不赋值，则为系统时间
varchar，字符串 varchar(字符长度) zhangsan=8个字符张三=2个字符

# 创建表
CREATE TABLE stu_info(
	id INT,
	name VARCHAR(32),
	age INT,
	score DOUBLE(4,1),
	birthday DATE,
	insert_time TIMESTAMP
);
# 复制表
CREATE TABLE student LIKE stu_info;
# 删除表
DROP TABLE IF EXISTS student;

# 修改表名
ALTER TABLE stu_info RENAME to stu;
# 修改表的字符集
ALTER TABLE stu CHARACTER SET utf8;
SHOW CREATE TABLE stu;
# 添加列
ALTER TABLE stu ADD gender VARCHAR(10);
# 修改列名称、列类型
ALTER TABLE stu CHANGE gender sex VARCHAR(20);
# 只修改类型
ALTER TABLE stu MODIFY sex VARCHAR(30);
# 删除列
ALTER TABLE stu DROP sex;

DML

添加数据

insert into table_name(列名1, 列名2, … 列名n) values(值1, 值2, … 值n);

除了数字都要加引号

INSERT INTO stu(id, name, age, score) VALUES(1, 'Jackson', 18, 100.0);
INSERT INTO stu VALUES(2, 'beenbeen', 18, 101.0, NULL, NULL);

删除数据
- delete from table_name [where 条件];
  
  如果不加条件，则删除所有数据，但是有多少条数据就会执行多少条删除操作，删除操作可使用：
  - truncate table table_name; 删除表，但是再创建一个一模一样的空表，效率更高
```
DELETE FROM stu WHERE id=2;

# 删除所有数据
DELETE FROM stu;
# 删除表，再创建一个一模一样的空表
TRUNCATE TABLE stu;
```
修改数据
- update table_name set 列名1=值, 列名2=值, … 列名n=值 [where 条件];
  
  如果不加条件，则会修改表中所有的数据

DQL

语法：

select

字段列表，…

from

表名列表，…

where

条件列表，…

group by

分组字段

having

分组之后的条件

order by

排序

limit

分页限定

基础查询

多个字段查询
- SELECT name, age, sex FROM student;
去除重复

DISTINCT 结果集视为整体，结果集完全一样才去重
- SELECT DISTINCT address FROM student;
计算列

可以使用四则运算，一般只会进行数值型计算
- SELECT *, math+IFNULL(english, 0) FROM student;
如果有NULL参与计算，结果均为null

IFNULL(column_name, 0) 如果字段名为null，则替换为0
起别名
- SELECT *, math+IFNULL(english, 0) AS ‘总分’ FROM student;
- SELECT *, math+IFNULL(english, 0) ‘总分’ FROM student;

条件查询

where子句后的条件

运算符

>、<、<=、>=、=、<>不等于
between…and
IN(集合)
LIKE
IS NULL …
AND 或 &&
OR 或 ||
NOT 或 !

-- 年龄大于20岁小于30
SELECT * FROM student WHERE age>=20 AND age<=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30; -- 包含20和30
-- 年龄等于20岁
SELECT * FROM student WHERE age=20;
-- 年龄不等于20岁
SELECT * FROM student where age!=20;
SELECT * FROM student where age<>20;
-- 查询22岁，19岁，55岁，的信息
SELECT * FROM student WHERE age=22 OR age=55 OR age=19;
SELECT * FROM student WHERE age IN (22, 55, 19);
-- NULL
SELECT * FROM student WHERE english=NULL; -- 错误,NULL值不能使用= !=
SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student where english IS NOT NULL;

模糊查询

LIKE

占位符：

_：单个任意字符
%：多个任意字符，包括0个

-- 查询班里姓stu的
SELECT * FROM student WHERE name LIKE 'stu%';
-- 第二个字是t的人
SELECT * FROM student WHERE name LIKE '_t%';
-- 查询姓名是三个字的人
SELECT * FROM student WHERE name LIKE '___';
-- 查询姓名中包含s的人
SELECT * FROM student WHERE name LIKE '%s%';

排序查询

语法：order by 子句

order by 排序字段1 排序方式1, 排序字段2 排序方式2
- 缺省时-升序 ASC
- 降序 DESC
```
-- 按照数学成绩排名，如果数学成绩一样则按照英语成绩排名，（都为升序）
SELECT * FROM student ORDER BY english ASC, math ASC;
```
第二排序条件，当第一排序条件一样时才会使用第二排序条件

聚合函数

将一列数据作为一个整体，进行纵向的计算

聚合函数计算会排除NULL值

解决方案：
1. 选择不包含非空的列（NOT NULL，主键）
2. 使用IFNULL(expr, expre)
3. COUNT(*)

count：计算个数
- SELECT COUNT(name) FROM student;
max：计算最大值
- SELECT MAX(math) FROM student;
min：计算最小值
- SELECT MIN(math) FROM student;
sum：计算和
- SELECT SUM(math) FROM student;
avg：计算平均值
- SELECT AVG(math) FROM student;

分组查询

统计具有相同特征的数据

语法：group by 分组字段;
注意：
1. 分组后查询的字段：1. 分组字段。2. 聚合函数
2. 分组前增加限定条件
  1. where 在分组前进行限定，如果不满足则不参与分组
  2. having 在分组之后进行限定，如果不满足则不会被查询出来
  3. where后不可跟聚合函数，having后可进行聚合函数判断

-- 按照性别分组，分别查询男、女平均分
SELECT sex, AVG(math) FROM student GROUP BY sex;
-- 按照性别分组，分别查询男、女平均分，人数
SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;
-- 按照性别分组，分别查询男、女平均分，人数, 要求：分数低于70分的人不参与分组
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
-- 按照性别分组，分别查询男、女平均分，人数, 要求：分数低于70分的人不参与分组, 分组之后，人数要大于2人
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex, AVG(math), COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

分页查询

语法：limit 开始的索引，每页查询的条数
公式：– 公式：开始的索引 = （当前的页码- 1）* 每页显示的条数
分页操作是一个“方言”

-- 每一页显示三条数据
SELECT * FROM student limit 0, 3; -- 第一页
SELECT * FROM student limit 3, 3; -- 第二页

-- 公式：开始的索引 = （当前的页码- 1）* 每页显示的条数
SELECT * FROM student LIMIT 6, 3; -- 第三页

约束

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

分类：

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

非空约束

创建表时添加约束

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);

唯一约束

唯一约束：某一列的值不能重复

可以有null值，但是不能有两个null

创建表时添加约束

CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE 	-- 手机号
);

删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

表创建完后添加唯一约束

已有重复记录时，无法添加唯一约束
```
ALTER TABLE stu MODIFY phone_number VARCHAAR(20) UNIQUE
```

主键约束

注意：
1. 含义：非空、且唯一
2. 一张表只能有一个字段为主键
3. 就是表中记录的唯一标识

创建表时添加主键约束

CREATE TABLE stu(
	id INT PRIMARY KEY,			-- 给id添加主键约束
	name VARCHAR(255) NOT NULL
);

删除主键约束
```
ALTER TABLE stu DROP PRIMARY KEY
```

创建表后，增加主键约束

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长

如果某一列时数值类型的，使用auto_increment可以完成值的自动增长
1. 创建表时自动增长
```
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL
);
```
2. 可以自动增长，也可以手动委派，只和上一条数据有关，手动委派后下一条数据相比上一条数据自动增长
3. 手动添加自动增长
```
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
```
4. 删除自动增长
```
# 主键无法这样删除
ALTER TABLE stu MODIFY id INT;
```

外键约束

让表与表产生关系，保证数据的正确性

在创建表时，添加外键

语法

CREATE TABLE table_name (
	...
	...
	外键列
	constraint 外键名称 foreign key (外键列的名称) references 主表的名称(主表列的名称)
);

删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

在创建表之后添加外键

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

级联操作

添加外键时，设置级联更新

ALTER TABLE employee add CONSTRAINT emp_empt_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;

设置级联删除

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON DELETE CASCADE;

数据库设计

多表之间的关系
1. 一对一：
  - 人和身份证
    - 一个人只有一个身份证，一个身份证只能对应一个人
2. 一对多（多对一）：
  - 部门和员工
    - 一个部门有多个员工，一个员工只对应一个部门
3. 多对多：
  - 学生和课程
    - 一个学生可以选择很多们课程，一个课程可以被很多学生选择
数据库设计的范式

多表之间的关系

一对多（多对一）：
- 部门和员工
  - 实现方式：在多的一方建立外键，指向1的一方的主键
多对多：
- 学生和课程
  - 实现方式：借助中间表，中间表最少存在两个字段，这两个字段作为中间表的外键，分别指向两张表的主键
联合主键

数据库设计范式

设计数据库时，需要遵循的规范
- 分类：
  - 第一范式（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
        
        例如：学号->系名，系名->系主任
      - 码：如果再一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码
        
        例如：（学号+课程名称） -> 分数（学号+课程名称）为该表的码
        
        主属性：码属性组中的所有属性
        非主属性：除码属性组的属性
  - 第三范式（3NF）
    - 在2NF基础上，任何非主属性不依赖于其他属性（在2NF基础上消除传递依赖）

数据库的备份和还原

备份：mysqldump -uroot -proot db_name > 保存的路径
还原：
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。 source 文件路径 (备份的sql语句文件)

多表查询

语法

select

列名列表

from

表名列表

where…

笛卡尔积

有两个集合A、B，取这两个集合的所有组合情况

完成多表查询，需要消除无用的数据

多表查询的分类

内连接查询

隐式内连接：使用where条件来消除无用的数据

-- 查询员工表的名称，性别，部门表的名称
SELECT emp.name, emp.gender, dept.name FROM emp, dept WHERE emp.`dept_id` = dept.`id`;

SELECT 
	t1.name, t1.gender, t2.name 
FROM 
	emp t1, dept t2 
WHERE 
	t1.dept_id = t2.id;

显式内连接：

语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件

SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

SELECT 
	* 
FROM 
	emp t1 
INNER JOIN 
	dept t2 
ON 
	t1.dept_id = t2.id;

内连接查询注意事项：
1. 从哪些表中查数据
2. 条件是什么
3. 查询哪些字段

外连接查询
1. 左外连接：
  1. 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
```
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门则不显示部门名称

SELECT
	t1.*, t2.`NAME`
FROM
	emp t1
LEFT JOIN
	dept t2 
ON
	t1.dept_id = t2.id;
```
  1. 左外连接：查询的是左表所有数据以及其交集部分
2. 右外连接：
  1. 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
  2. 右外连接：查询的是右表所有数据以及其交集部分

子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

-- 查询工资最高的员工信息
-- 1. 查询工资最高的员工是多少
SELECT MAX(salary) FROM emp;
-- 2. 查询员工信息，并且工资等于 最高的
SELECT * FROM emp WHERE emp.salary = 9999;

-- 一条sql完成，子查询
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(emp.salary) FROM emp);

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(emp.salary) FROM emp);

子查询不同情况

子查询的结果是单行单列的：

子查询可以作为条件，使用运算符去判断。运算符： >, >=，<，<=，=

-- 查询工资最高的员工信息
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(emp.salary) FROM emp);

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(emp.salary) FROM emp);

子查询的结果是多行单列的：

子查询可以作为条件，使用运算符 IN(…) 来判断

-- 查询'财务部' 和 '市场部'所有的员工信息
SELECT 
	* 
FROM 
	emp 
WHERE 
	dept_id IN (SELECT id FROM dept WHERE dept.name IN ('市场部', '财务部'));

子查询的结果是多行多列的：

子查询可以作为一张虚拟表，进行表的查询

-- 查询员工的入职日期是2011-11-21之后的员工信息和部门信息
-- 日期的判断可以用字符串直接比较
SELECT 
	t2.*,t1.name
FROM 
	dept t1, (SELECT * FROM emp WHERE emp.join_date > '2011-11-21') t2
WHERE 
	t1.id = t2.dept_id;
	
-- 普通内连接
SELECT 
	t1.*, t2.name 
FROM 
	emp t1, dept t2 
WHERE 
	t1.dept_id = t2.id AND t1.join_date > '2011-11-21';

事务

基本介绍：

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作，要么同时成功，要么同时失败
操作：
1. 开启事务：start transaction;
2. 回滚：rollback;
3. 提交：commit;
MySQL 数据库中事务默认自动提交，Oracle默认手动提交
- 一条DML语句会自动提交一次事务
手动提交：
- 需要先开启事务，再提交
修改事务的默认提交方式：
1. 查看事务的默认提交方式：SELECT @@autocommit;
  
  1–自动提交 0–手动提交
2. 修改默认提交方式：set @@autocommit = 0;

四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性：事务操作前后，数据总量不变
隔离性：多个事务之间，相互独立
持久性：事务一旦提交或回滚后，数据库会持久化的保存

隔离级别

多个事物之间是隔离，相互独立的，但是多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题：

脏读：
- 一个事务，读取到另一个事务中没有提交的数据
不可重复读（虚读）：
- 在同一个事务中，两次读取到的数据不一样
幻读：
- 一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改记录

隔离级别：

READ UNCOMMITED：读未提交
- 产生的问题：脏读，不可重复读，幻读
READ COMMITED：读已提交（Oracle 默认）
- 产生问题：不可重复读，幻读
REPEATABLE READ：可重复读（MySql 默认）
- 产生问题：幻读
SERIALIZABLE：串行化
- 解决所有的问题

隔离级别从小到大，安全性越来越高，但是效率越来越低

数据库查询隔离级别：

SELECT @@tx_isolation;

数据库设置隔离级别：

SET GLOABLE TRANSACTION ISOLATION LEVEL 级别字符串;

MySQL忘记root用户怎么修改密码 5步

停止mysql服务
cmd：输入 mysqld --skip-grant-tables
打开新的cmd窗口：输入 mysql
DCL：修改root用户密码为root：update user set password = password(‘root’) where user = ‘root’;
打开任务管理器，手动结束mysql.exe 进程，启动mysql服务，使用新密码登录

madbeen

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库及 SQL语句快速入门

目录SQLDDLDMLDQL基础查询排序查询聚合函数分组查询分页查询约束非空约束唯一约束主键约束外键约束级联操作数据库设计多表之间的关系数据库设计范式数据库的备份和还原多表查询笛卡尔积多表查询的分类事务四大特征隔离级别MySQL忘记root用户怎么修改密码 5步SQLStructured Query Language 结构化查询语言DDL (Data Definition Language) 数据定义语言操作数据库、表DML (Data Manipulation Language)
复制链接

扫一扫