返回

MySQL:数据库

数据库概念

数据库 DB

  • 数据库(DataBase) 简称DB
  • 按照一定格式存储数据的一些文件的组合
  • 存储数据的仓库,实际上就是一堆文件,这些文件中存储了具有特定格式的数据

数据库管理模型 DBMS

  • 数据库管理模型 (DataBaseManagment) 简称DBMS
  • 数据库管理系统是专门用来管理数据库中的数据的,数据库管理系统可以对数据库当中的数据进行增删改查
  • 常见的数据库管理系统
    • MySQL、Oracle、MS SQL Server、DB2、sybase等

结构化查询语言 SQL

SQL(Structured Query Language) 结构化查询语言,用于存取数据、更新、查询和管理关系数据库系统的程序设计语言

  • 程序员需要学习SQL语句,程序员通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中数据的增删改查操作
  • SQL是一套标准,程序员主要学习的就是SQL语句,这个SQL在mysql中可以使用,同时在Oracle中也可以使用,在DB2中也可以使用

三者之间的关系

DBMS == 执行 ==> SQL == 操作 ==> DB

MySQL安装

下面都是需要更改的部分,没提的位置全部下一步&完成

  1. 安装类型选项,选精简就好

截屏2020-11-20 下午9.49.14
截屏2020-11-20 下午9.49.14

  1. 更改字符集为UTF-8 后期避免乱码的问题

截屏2020-11-20 下午9.57.47
截屏2020-11-20 下午9.57.47

  1. 勾选加载系统变量的勾,免去后期操作

截屏2020-11-20 下午9.59.15
截屏2020-11-20 下午9.59.15

截屏2020-11-20 下午10.03.21
截屏2020-11-20 下午10.03.21

  1. 设置账户

截屏2020-11-20 下午10.00.42
截屏2020-11-20 下午10.00.42

MySQL卸载

  1. 运行安装包或者卸载程序进行卸载

  2. 打开隐藏文件夹选项,删除

    C:\ProgramData

    C:\Program Files(x86)

    两个目录下的MySQL目录

启动&停止 MySQL

cmd或powershell下命令:

下文中的MySQL是默认服务名

如在安装步骤3中更改过名称 下方的MySQL也需要对应更改

net start MySQL // 启动服务

net stop MySQL // 停止服务

SQL基本命令

语句基本常识

  • SQL语句大小写不敏感,但是系统词汇我们通常用大写

  • SQL命令可以多行输入,如需执行命令,在命令的最后一行结尾输入 ; 并回车

    mysql> SHOW DATABASES
    		-> ;
    		/*或者*/
    mysql> SHOW DATABASES;
    
  • 因为SQL是查询语言,所以返回内容都为查询结果

    Query OK1 row affected (0.00sec) -- 查询OK,有一条语句被影响
    

登陆mysql

mysql -uroot -p1234

-u后面跟用户名

-p后面跟密码 明文输入密码

如果-p后面不跟信息输入回车,会在第二行出现密文的密码输入提示

查看所有数据库

mysql>SHOW DATABASES;

# 显示初始数据库中所有的内容
+--------------------+
| Database           |
+--------------------+
| information_schema | -- 信息数据库 保存所有数据库中的元数据
| mysql              | -- 核心数据库
| performance_schema | -- 性能优化 5.5版本新增
| sys                | -- 可以快速了解元数据信息的系统库 5.7版本新增
| sakila             | -- 样板数据库
| world              | -- 国家信息数据库
+--------------------+
6 rows in set (0.00 sec)

新建数据库

create database mydb; -- 创建一个数据库mydb
create database mydb1 CHARCTER SET gbk; -- 创建一个编码集为GBK的数据库mydb1
create database mydb1; 
	==> Cant create dtabase 'mydb1',database exists
		-- 不能创建mydb1,因为数据库已经存在
create database if not exists mydb1; 
		-- 如果mydb1不存在,创建mydb1
	==> Query OK,1 row affected, 1 warning
		-- 因为已经存在mydb1,所以并没有被再次创建,而是一条警告语句

查看数据库的创建信息

show create data mydb1;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| mydb1    | CREATE DATABASE `new` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库

alter database mydb1 character set utf8;
show create data mydb1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| mydb1    | CREATE DATABASE `new` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

drop database mydb1;

查看当前使用的数据库

select database();
+------------+
| Database() |
+------------+
| NULL       |
+------------+

因为没有选择想要使用的数据库,所以为NULL

使用数据库

use mydb;
==>Database changed
select database();
+------------+
| Database() |
+------------+
| mydb       |
+------------+

退出数据库

exit;

SQL客户端工具

  • Navicate

  • SQLyog

  • DataGrip

SQL查询

  • 执行查询语句返回的是一张虚拟的表

基本查询

SELECT 列名 FROM 表名

关键词 描述
SELECT 指定要查询的列
FROM 指定要查询的表

查询部分列

SELECT 列名1,列名n FROM 表名;

查询所有列

SELECT * FROM 表名 ;
SELECT 列名1,列名2,列名3...列名-1 FROM 表名;

在生产环境中,优先使用列名查询。

*的方式需要转换成全列名,效率低,可读性差。

列的数据运算

SELECT 数值型列*12 FROM 表名;
算数运算符 描述
+ 两列做加法运算
- 两列做减法运算
* 两列做乘法运算
/ 两列做除法运算
  • % 在SQL语句中是占位符,不是非模运算符

列的别名 AS

SELECT 列名 AS '列标题' FROM 表名;

查询去重 DISTINCT

SELECT DISTINCT 列名 FROM 表名;

排序查询 ORDER BY

-- 单列查询
SELECT 列名 FROM 表名 ORDER BY 排序列 排序规则;
-- 多列查询
SELECT 列名 FROM 表名 ORDER BY 排序列1 排序规则1,排序列2 排序规则2;
排序规则 描述
ASC 升序排序(从小到大)
DESC 降序排序(从大到小)
  • 多列排序的执行规则

    首先执行排序1,当排序1中的某些数据相等时,对相等的部分执行排序2

条件查询 WHERE

SELECT 列名 FROM 表名 WHERE 条件;

查询条件不一定是数值,可以是字符、日期等

等值判断 =

SELECT 列名 FROM 表名 WHERE 列名 = 条件;

与编程语言不同( == ),mysql中等值判断使用 =

不等值判断 < > <= >= != <>

SELECT 列名 FROM 表名 WHERE 列名 [运算符] 条件;
运算符 描述
< 小于
<= 小于等于
> 大于
>= 大于等于
!= <> 不等于

逻辑判断 AND OR NOT

SELECT 列名 FROM 表名 WHERE 列名1=条件1 AND 列名2=条件2 OR 列名n=条件n;
运算符 描述
AND 与(都满足)为真
OR 或(满足一个)为真
NOT 非(不满足)为真

区间判断 BETWEEN AND

SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 条件1 AND 条件2; -- 闭区间,包含两遍的数值

条件1 必须小于 条件2

NULL值判断 IS NULL/IS NOT NULL

SELECT 列名 FROM 表名 WHERE 列名 IS NULL; -- 查找列中为空的数据

枚举查询 IN(值1,值2,值3)

SELECT 列名 FROM 表名 WHERE 列名 IN(1,2,3);

-- IN方法等价的第二种写法
SELECT 列名 FROM 表名 WHERE 列名 = 1,列名 = 2,列名 = 3;

IN的查询效率较低,可通过多条件拼接

模糊查询 LIKE

SELECT 列名 FROM 表名 WHERE 列名 LIKE _ (单个任意字符)
-- LIKE '睡_' 可以表示为睡觉 不能表示睡觉觉
-- LIKE '睡__' 可以表示为睡觉 也能表示睡觉觉
-- LIKE '_睡_' 可以表示为要睡觉

SELECT 列名 FROM 表名 WHERE 列名 LIKE % (任意长度的字符)
-- LIKE '睡%' 可以表示为 睡觉 也可以表示 睡觉觉 甚至是 睡觉吃饭喝水
-- LIKE '%睡' 可以表示 我 要睡 不能是 我要睡 觉
-- LIKE '%睡%' 可以表示 我 要睡觉 吃饭喝水

分支结构查询 CASE END

SELECT 列名
	CASE
		WHEN 条件1 THEN 结果1
		WHEN 条件2 AND 条件3 THEN 结果2
		WHEN 条件4 OR 条件5  THEN 结果3
		ELSE 结果
	END AS '列标题'
FROM 表名;

函数查询

时间查询

SELECT 时间函数([时间列表]) FROM 表名;
时间函数 描述
SYSDATE() 当前系统时间(日、月、年、时、分、秒)
CURDATE() 获取当前日期
CURTIME() 获取当前时间
WEEK(DATE) 获取指定日期为一年中的第几周
YEAR(DATE) 获取指定日期的年份
HOUR(TIME) 获取指定日期的小时值
MINUTE(TIME) 获取时间的分钟值
DATEDIFE(DATE1,DATE2) 获取DATE1和DATE2之间的相隔天数
ADDDATE(DATE,N) 计算DATE加上N天的日期

字符串查询

SELECT 字符串函数([参数列表]) FROM 表名;
字符串函数 说明
CONCAT(str 1,str 2,str 3…str n) 将多个字符串连接
INSERT(str,pos,len,newStr) 将str中指定pos位置开始的len长度的内容替换为newstr
LOWER(str) 将指定字符转换为小写
UPPER(str) 将指定字符转换为大写
SUBSTRING(str,num,len) 将str字符串指定num位置开始截取len个内容

MySQL中 ,字符串下标是从 1 开始的

聚合函数

SELECT 聚合函数(列名) FROM 表名;
聚合函数 说明
SUM() 求所有行中单列结尾的总和
AVG() 平均值
MAX() 最大值
MIN() 最小值
COUNT() 求总行数

聚合函数自动忽略NULL值,不做统计

分组查询 GROUP BY

-- 单列查询
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据();

-- 多列查询
SELECT 列名1,COUNT(列名2),列名3
FROM 表名
GROUP BY 列名1, 列名2;
/*	首先对列名1进行分组
		然后再对列名1相同但列名2不相同的项进行分组
		最后分别求出列名2分组以后的每组的行数 */

分组查询中,SELECT显示的列只能是分组依据的列,或者聚合函数列,不能出现其他列

SELECT 列名1,COUNT(*),列名3
FROM 表名
GROUP BY 列名1;
列1 COUNT() 列3
10 1 BOSS
100 1 MSG
110 6 Nancy
150 2 Shelley

表是以分组函数和聚合函数列,为主的,其他列的数据不会完全展示

110与150组中有多个数据,但是在列3中指展示了一个数据

分组过滤查询 HAVING

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据() HAVING 过滤规则;
关键字 说明
HAVING 过滤规则 过滤规则定义对分组后的数据进行过滤

限定查询 LIMIT

SELECT 列名 FROM 表名 LIMIT 起始行,查询行数;

在数据库中,行的下标是从0开始的,与数组不同

SQL查询编写顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列(asc|desc) LIMIT 起始行,总条数

SQL语句执行顺序

  1. FROM :指定数据表来源
  2. WHERE : 对查询数据做第一次过滤
  3. GROUP BY : 分组
  4. HAVING : 对分组后的数据进行第二次过滤
  5. SELECT : 查询各字段的值
  6. ORDER BY : 排序
  7. LIMIT : 限定查询结果

子查询

子查询(作为条件判断)

-- 当我们需要用表中的 单列单条 数据进行比较时的操作步骤
SELECT 获得单行数据 FROM 表名 WHERE 条件; -- 获得单行需要比较的数据
SELECT 列名 FROM 表名 WHERE 获得的单行数据; -- 得到需要的数据

-- 将两条语句进行整合
SELECT 列名 FROM 表名 WHERE 需要筛查的数据 比较 (SELECT 获得单条数据 FROM 表名 WHERE 条件);

将子查询“一行一列”的的结果作为外部查询的条件,做第二次查询

子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断

子查询(作为枚举查询条件)

-- 当我们需要用表中的 单列多行  数据进行比较时的操作步骤
SELECT 获得多行数据 FROM 表名 WHERE 条件; -- 获得多行需要比较的数据
SELECT 列名 FROM 表名 WHERE 获得的多行数据; -- 得到需要的数据

-- 将两条语句进行整合
SELECT 列名 FROM 表名 WHERE 需要筛查的数据 IN (SELECT 获得多条数据 FROM 表名 WHERE 条件);

将子查询“多行一列”的结果作为外部查询的枚举查询条件,做二次查询

-- ALL 与所有比较
-- 也就是说如果是 >子查询 就是要比所有条件都大 反之就是比所有的都小
SELECT 列名 FROM 表名 WHERE 需要筛查的数据 比较 ALL(SELECT 获得多条数据 FROM 表名 WHERE 条件);

-- ANY 与任意一个比较
-- 就是要比任意一个大就可以 反之就是比任意一个小就可以
SELECT 列名 FROM 表名 WHERE 需要筛查的数据 比较 ANY(SELECT 获得多条数据 FROM 表名 WHERE 条件);

子查询(作为表)

-- 当我们需要用表中的 多行多列  数据进行比较时的操作步骤
SELECT 获得多行多列数据 FROM 表名 WHERE 条件; -- 获得多行多列需要比较的数据
SELECT 列名	FROM 获得的多行多列数据; -- 得到需要的数据
-- 将两条语句进行整合
SELECT 列名 FROM (SELECT 获得多行多列数据 FROM 表名 WHERE 条件) AS TEMP;

将子查询“多行多列”的结果作为外部查询的一张表,做第二次查询

因为是将子查询生成一张虚拟表,但是一条语句中表名需要唯一,所以需要为子语句生成的表赋一个名字

合并查询 UNION

SELECT 列名 FROM 表名 UNION SELECT 列名 FORM 表名;
SELECT 列名 FROM 表名 UNION ALL SELECT 列名 FORM 表名; -- 去除重复数据

表连接查询 JOIN

SELECT 列名 FROM 1 连接方式 2 ON 连接条件;

内连接查询 INNER JOIN ON

-- SQL标准写法
SELECT 列名 FROM 表名 INNER JOIN 表名2 ON 表名1.列名 = 表名.列名; 
-- MySQL写法 简单但是通用性差
SELECT 列名 FROM 表名1,表名2 WHERE 表名1.列名 = 表名.列名; 

如果不指定连接条件,则会造成笛卡尔积的结果

A {a,b} B {1,2,3} –> {a,1},{a,2},{b,1},{b,2},{b,3}

多表连接查询

SELECT 列名 FROM 表名
INNER JOIN 表名x
ON 表名x.列名 = 表名y.列名
INNER JOIN 表名n
ON 表名n.列名 = 表名m.列名

左外连接查询 LEFT JOIN ON

SELECT 列名 FROM 表名 LEFT JOIN 表名2 ON 表名1.列名 = 表名.列名;

左外连接 以左表为主,依次向右表匹配,匹配不到则返回NULL

右外连接查询 RIGHT JOIN ON

SELECT 列名 FROM 表名 RIGHT JOIN 表名2 ON 表名1.列名 = 表名.列名;

右外连接 以右表为主,依次向左表匹配,匹配不到则返回NULL

DML操作

新增 INSERT

INSERT INTO 表名 (1,列2,列3...) VALUES (1,2,3...);

表名后的列名要和VALUES里的值要一一对应( 个数、顺序、类型)

修改 UPDATE

UPDATE 表名 SET 1 = 新值1,列2 = 新值2,... WHERE 条件

绝大多数情下都需要加WHERE条件,否则整表更新

删除 DELETE

DELETE FROM WHERE 条件;

删除时不添加WHERE则删除整张表

清空整表数据 TRUNCATE

TRUNCATE TABLE 表名;

DELECT 是在原表的基础上进行数据删除

TRUNCATE 是将整表进行删除,然后再用一样的表结构新建一张表

数据表操作

数据表修改 ALTER

ALTER TABLE 表名 操作;

向现有表添加列 ADD

ALTER TABLE 表名 ADD 新列名 数据类型;

修改表中的列 MODIFY

ALTER TABLE 表名 MODIFY 列名 数据类型;

删除表中的列 DROP

ATLER TABLE 表名 DROP 列名;

删除列时,只能删除一列

comments powered by Disqus