MySQL数据库基础知识及操作
- 一、数据库分类
- 二、SQL分类
- 三、数据类型
- 四、数据库的操作
- 五、数据库表的操作
- 六、MySQL表的增删改查(CRUD)
- 6.1 新增(Create)
- 6.2 查询(Retrieve)
- 1. 全列查询
- 2. 指定列查询
- 3. 别名
- 4. 去重(DISTINCT)
- 5. 排序(ORDER BY)
- 6. 条件查询(WHERE)
- 7. 分页查询(LIMIT)
- 8. 模糊查询(LIKE)
- 9. 范围查询
- 10. 运算符
- 6.3 修改(Update)
- 6.4 删除(Delete)
- 例子
一、数据库分类
关系型数据库(RSBMS):采用了关系模型来组织数据的数据库,基于标准的SQL,只是内部实现有一些区别,常用的关系型数据库包括:Oracle,MySQL,SQL server,PostgressSQL
非关系型数据库:不规定基于SQL实现,更多的是指NoSQL数据库,如:1.基于键值对:memcached,redis;2.基于文档:mongodb;3.基于列族:hbase;4.基于图:neo4j
二、SQL分类
- SQL(Structure Query Language)结构化查询语言
- SQL分类
DDL:数据定义语言,用来维护存储数据的结构,代表指令:create,drop,alter
DML:数据操纵语言,用来对数据进行操作,例如:insert,delete,update,DML中又单独分了一个DQL(数据查询语言),代表指令:select
DCL:数据控制语言,主要负责权限管理和事务,代表指令:grant,revoke,commit
三、数据类型
MySQL支持的常用数据类型
- 数值类型:TINYINT,INT,FLOAT(M,D)等
- 字符串类型:VARCGAR(SIZE),TEXT等
- 日期类型:DATATIME,TIMESTAMP等
四、数据库的操作
显示当前的数据库
SHOE DATABASES;
创建数据库
CREATE TABLE 数据库名称;
-- 如果系统没有 db_test 的数据库,则创建一个使用utf8mb4字符集的 db_test 数据库,如果有则不创建
CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4;
使用数据库
use 数据库名;
删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
-- 数据库删除以后,内部看不到对应的数据库,里边的表和数据全部被删除
五、数据库表的操作
查看表
-- 查看表结构
desc 表名
-- 查看某个数据库下的所有表
show tables;
创建表
CREATE TABLE 表名(字段名称 字段类型(长度) 约束,字段名称 字段类型(长度) 约束...)
删除表
DROP TABLE 表名;
六、MySQL表的增删改查(CRUD)
6.1 新增(Create)
-- 向表中插入全列数据
INSERT INTO 表名 VALUES
(值1,值2...),
(值1,值2...),
...;
-- 向表中插入指定列数据
INSERT INTO 表名 (列名1,列名2) VALUES
(值1,值2),
(值1,值2),
...;
6.2 查询(Retrieve)
1. 全列查询
SELECT * FROM 表名;
2. 指定列查询
SELECT 列明1,列明2... FROM 表名;
3. 别名
-- 为查询结果中的列指定别名
SELECT 列明 AS 别名 FROM 表名;
-- AS可以省略
SELECT 列明 别名 FROM 表名;
4. 去重(DISTINCT)
-- 使用DISTINCT关键字对某列数据进行去重
SELECT DISTINCT 列明 FROM 表名;
5. 排序(ORDER BY)
-- ASC为升序(从小到大)
-- DESC为降序(从大到小)
-- 默认为ASC
SELECT 列明1,列明2... FROM 表名 ORDER BY 列名; -- 升序
SELECT 列明1,列明2... FROM 表名 ORDER BY 列名 DESC; -- 降序
6. 条件查询(WHERE)
SELECT 列明1,列明2... FROM 表名 WHERE 条件;
7. 分页查询(LIMIT)
-- 从s开始,筛选n条结果
SELECT ... FROM 表名 [WHERE] [ORDER BY ...]LIMIT n OFFSET s;
8. 模糊查询(LIKE)
-- % 匹配任意多个字符(包括0个)
SELECT name FROM exam_result WHERE name LIKE '孙%';
SELECT name FROM exam_result WHERE name LIKE '%孙%';
-- _ 匹配严格的1个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';
9. 范围查询
(1) BETWEEN…AND…
SELECT 列明1,列明2... FROM 表名 WHERE...BETWEEN...AND...;
-- 查询语文成绩在[80,90]分的同学及语文成绩
SELECT name,chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
-- 使用AND也可以实现
SELECT name,chinese FROM exam_result WHERE chinese>=80 AND chinese<=90;
(2) IN
SELECT 列明1,列明2... FROM 表名 WHERE...IN...;
-- 查询数学成绩在是58或者59或者98或者99的同学及数学成绩
SELECT name,math FROM exam_result WHERE math IN(58,59,98,99);
-- 使用OR也可以实现
SELECT name,math FROM exam_result WHERE math=58 OR math=59 ORmath=98 ORmath=99;
10. 运算符
比较运算符
运算符 | 说明 |
>,>=,<,<= | 大于,大于等于,小于,小于等于 |
= | 等于 ,NULL不安全,例如NULL=NULL的结果是NULL |
<=> | 等于, NULL安全,例如NULL=NULL的结果是 TRUE(1) |
!=,<> | 不等于 |
BETWEEN a0 AND a1 | 范围匹配,[a0,a1],如果a0<=value<=a1,返回TRUE(1) |
IN(option,…) | 如果是option中的任意一个,返回TRUE(1) |
IS NULL | 是NULL |
IS NOT NULL | 不是NULL |
LIKE | 模糊匹配,%表示任意多个(包括0个字符),_表示任意一个字符 |
逻辑运算符
运算符 | 说明 |
AND | 多个条件必须都为TRUE(1),结果才是TRUE(1) |
OR | 任意一个条件为TRUE(1),结果为TRUE(1) |
NOT | 条件为TRUE(1),结果为FALSE(0) |
注意:
- WHERE条件可以使用表达式,但不能使用别名
- AND的优先级高于OR,在同时使用时,需要使用小括号()包裹优先执行的部分
6.3 修改(Update)
UPDATE 表名 SET 字段1 =值1,字段2 =值2...WHERE条件
6.4 删除(Delete)
DELETE FROM 表名 WHERE条件
例子
-- 创建学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT,
SN INT COMMENT '学号',
name VARCHAR(20) comment '姓名',
qq_mail VARCHAR(20) comment 'QQ邮箱');
-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result(
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)) ;
-- 插入数据
INSERT INTO exam_result VALUES
(1,'张三',67,98,56),
(2,'李四',87.5,78,77),
(3,'王五',88,98.5,90),
(4,'赵六',82,84,67),
(5,'孙行者',55.5,85,45),
(6,'行者孙',70,73.5,78.5),
(7,'者行孙',70,73.5,78.5),
(8,'孙权',75,65,30);
-- 全列查询
SELECT * FROM exam_result;
-- 指定列查询
SELECT id,name,chinese FROM exam_result;
-- 查询字段为表达式
-- 1. 表达式不包含字段
SELECT id,name,10 FROM exam_result;
-- 2. 表达式包含1个字段
SELECT id,name,english+10 FROM exam_result;
-- 3. 表达式包含多个字段
SELECT id,name,chinese+math+english FROM exam_result;
-- 别名
SELECT id,name,chinese+english+math as 总分 FROM exam_result;
SELECT id,name,chinese+english+math 总分 FROM exam_result;
-- 去重复:对某列数据进行去重
SELECT DISTINCT math from exam_result;
-- 条件查询:查询英语不及格的同学及英语成绩
SELECT name,english FROM exam_result WHERE english<60;
-- 模糊查询:LIKE
-- % 匹配任意多个字符(包括0个)
SELECT name FROM exam_result WHERE name LIKE '孙%';
SELECT name FROM exam_result WHERE name LIKE '%孙%';
-- _ 匹配严格的1个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';
-- 排序
SELECT name,math FROM exam_result ORDER BY math;
SELECT name,math FROM exam_result ORDER BY math DESC;
SELECT name,math,english,chinese FROM exam_result ORDER BY english,math DESC,chinese;
--分页查询
-- 起始下标为 0
-- 按 id 进行分页,每页 3 条记录,分别显示 第 1、2、3 页
-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;
-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 3;
-- 第 3 页,如果结果不足 3 个,不会有影响
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;
-- 修改
-- 将张三同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '张三';
-- 将李四同学的数学成绩变更为 60 分,语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '李四';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;
-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;
-- 删除
-- 删除赵六同学的考试成绩
DELETE FROM exam_result WHERE name = '赵六';
-- 删除整表数据
DELETE FROM exam_result;