MySQL基础

最新推荐文章于 2022-07-29 20:57:11 发布

炳烛好影

最新推荐文章于 2022-07-29 20:57:11 发布

阅读量287

点赞数

分类专栏： MySQL 文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/ai_live/article/details/105901965

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、为什么要学习数据库

1.持久化数据

2.方便检索

3.存储大量数据

4.共享、安全

5.通过组合分析，获取新的数据

二、数据库的相关概念

DB

database数据库，存储一系列有组织数据的容器

DBMS

Database Management System数据库管理系统，使用DBMS管理和维护DB

SQL

StructureQueryLanguage结构化查询语言，程序员用于和DBMS通信的语言

三、数据库存储数据的特点

1、数据先放在表中，表再放在库中

2、一个库可以有多张表，每张表都有自己的唯一标识名

3、一张表的设计，类似于java中"类"的设计

表中的字段的设计，类似于属性的设计

表中的单条记录，类似于一个对象

表中的所有的记录，类似于对象的集合

orm ：object relation mapping 对象关系映射

四、初始MySQL

1、MySQL产品的介绍

MySQL 前身属于瑞典的一家公司AB，2008年被sun公司收购，2009年sun被oracle公司收购。

特点：

① 体积小、安装较方便

②开源、免费

③性能高、稳定性好

④兼容性好

2、MySQL产品的安装 ★

基于C/S架构的DBMS，需要安装服务端和客户端

www.oracle.com

MySQL5.5

3、MySQL服务的启动和停止 ★

方式一：图形化

右击——计算机管理——服务——MySQL服务

方式二：通过管理员身份运行dos

net start 服务名

net stop 服务名

4、MySQL服务的登录和退出 ★

方式一：通过dos命令

mysql -h主机名 -P端口号 -u用户名 -p密码

注意：

如果是本机，则-h主机名可以省略

如果端口号是3306，则-P端口号可以省略

方式二：通过图形化界面客户端

通过sqlyog，直接输入用户名、密码等连接进去即可

5、MySQL的常见命令和语法规范

①常见命令

show databases 显示当前连接下所有数据库
show tables 显示当前库中所有表
show tables from 库名显示指定库中所有表
show columns from 表名显示指定表中所有列
use 库名打开/使用指定库

②语法规范

不区分大小写

每条命令结尾建议用分号

注释：

#单行注释
– 单行注释
/多行注释/

五、DQL语言的学习 ★

DQL:Data Query Language 数据查询语言

select

1、基础查询

语法：

select 查询列表 from 表名

特点

①查询结果集是一个虚拟表

②查询列表可以是单个字段、多个字段、常量、表达式、函数，可以是以上的组合

起别名

select 字段名 as "别名" from 表名;

select 字段名 "别名" from 表名;

+的作用

作用：加法运算

如果两个操作数都是数值型，则直接做加法运算
如果其中一个为非数值型，则将强值转换成数值型，如果转换失败，则当做0
如果其中一个为null，则结果直接为null

'123'+4====》127

'abc'+4====》 4

去重

select distinct department_id from employees;

补充函数

select version();

select database();

select user();

select ifnull(字段名,表达式);

select concat(字符1，字符2,字符3);

select length(字符/字段);获取字节长度

2、条件查询

语法：

select 查询列表

from 表名

where 筛选条件;

特点：

筛选条件的分类：

①按条件表达式筛选

关系运算符：>= < <= > <> =

②按逻辑表达式筛选

逻辑运算符：and or not

③模糊查询

like:一般和通配符搭配使用

_ 任意单个字符

% 任意多个字符

between and：一般用于判断某字段是否在指定的区间

a between 10 and 100

in：一般用于判断某字段是否在指定的列表

a in(10,30,50)

is null:判断null值

3、排序查询

语法：

select  查询列表

from 表名

where 筛选条件

order by 排序列表 ;

特点：

①排序列表可以是单个字段、多个字段、函数、表达式、别名、列的索引，以及以上的组合

②升序，通过asc

降序，通过desc

4、常见函数

说明：sql中的函数分为单行函数和分组函数

调用语法：select 函数名(实参列表);

(1)字符函数

concat(str1,str2,..):拼接字符

substr(str,pos):截取从pos开始的所有字符，   起始索引从1开始

substr(str,pos,len):截取len个从pos开始的字符，起始索引从1开始

length(str):获取字节个数

char_length(str):获取字符个数

upper(str):变大写

lower(str):变小写

trim(【substr from】str):去前后指定字符，默认是去空格

left(str,len):从左边截取指定len个数的 字符

right(str,len):从右边截取指定len个数的 字符

lpad(str,substr,len):左填充

rpad(str,substr,len)：右填充

strcmp(str1,str2):比较两个字符的大小

instr(str,substr):获取substr在str中第一次出现的索引

(2)数学函数

ceil(x):向上取整

floor(x):向下取整

round(x,d):四舍五入

mod(x,y):取模/取余

truncate(x,d):截断，保留小数点后d位

abs(x):求绝对值

(3)日期函数

now():获取当前日期——时间

curtime():只有时间

curdate():只有日期

date_format(date,格式):格式日期为字符

str_to_date(str,格式):将字符转换成日期

datediff(date1,date2):获取两个日期之间的天数差

year(date)

month(date)

(4)流程控制函数

①if(条件，表达式1，表达式2)：如果条件成立，返回表达式1，否则返回表达式2

②case 表达式

when 值1 then 结果1

when 值2 then 结果2

...

else 结果n

end

5、分组函数

sum求和

avg平均

max最大

min最小

count个数

特点：

①实参的字段的类型，sum和avg只支持数值型，其他三个可以支持任意类型

②这五个函数都忽略null值

③count可以支持以下参数

count(字段)：查询该字段非空值 的个数

count(*) ：查询结果集的行数

count(1)：查询结果集的行数

④分组函数可以和distinct搭配使用，实现去重的统计

select count(distinct 字段) from 表

6、分组查询

语法：

select 分组函数,分组的字段

from 表名

where 分组前的筛选条件

group by 分组列表

having 分组后的筛选条件

order by 排序列表;

特点：

①分组列表可以是单个字段、多个字段

②筛选条件分为两类

	筛选的基表	使用的关键字	位置
分组前的筛选	原始表	where	group by 前面
分组后的筛选	分组后的结果集	having	group by 后面

7、连接查询

说明：当查询中涉及到了多个字段，则需要通过多表连接

笛卡尔乘积：

出现原因：没有有效的连接条件

解决办法：添加有效的连接条件

SQL92语法

select 查询列表 

from 表1 别名,表2 别名,... 

where 连接条件 

and 筛选条件

group by 分组列表

having 分组后筛选

order by 排序列表;

执行顺序：

1、from子句
2、where子句
3、and子句
4、group by子句
5、having子句
6、select子句
7、order by子句

(1)等值连接

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

为表起别名

①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

(2)非等值连接

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

(3)自连接

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

SQL99语法

(1)内连接

语法：

select 查询列表    

from 表1 别名

【inner】 join 表2 别名 on 连接条件  

【inner】 join 表3 别名 on 连接条件

where 筛选条件  

group by 分组列表

having 分组后的筛选

order by 排序列表

SQL92和SQL99的区别：

SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！

①等值连接

SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

②非等值连接

SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;

③自连接

SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

(2)外连接

说明：

查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null

应用场景：

一般用于查询主表中有但从表没有的记录

特点：

1、外连接分主从表，两表的顺序不能任意调换
2、左连接的话，left join左边为主表
右连接的话，right join右边为主表

语法：

select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名
on 连接条件
where 筛选条件;

①左连接

SELECT b.`name`
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

②右连接

SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

8、子查询

说明

当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询
外面的select语句称为主查询或外查询。

分类：

1、select后面
	要求：子查询的结果为单行单列（标量子查询）
2、from后面
	要求：子查询的结果可以为多行多列
3、where或having后面 ★
	要求：子查询的结果必须为单列
		单行子查询
		多行子查询
4、exists后面
	要求：子查询结果必须为单列（相关子查询）

特点：

1、子查询放在条件中，要求必须放在条件的右侧
2、子查询一般放在小括号中
3、子查询的执行优先于主查询
4、单行子查询对应了 单行操作符：> < >= <= = <>
   多行子查询对应了 多行操作符：any/some  all in

(1)放在where或having后面

①单行子查询

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

②多行子查询

in:判断某字段是否在指定列表内

any/some:判断某字段的值是否满足其中任意一个

all:判断某字段的值是否满足里面所有的

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);

(2)放在select后面

SELECT 
(
	SELECT COUNT(*)
	FROM employees
	WHERE department_id = 50
)  个数;

(3)放在from后面

SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

(4)放在exists后面

SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.id = b.boyfriend_id
);

9、分页查询

应用场景

当页面上的数据，一页显示不全，则需要分页显示

分页查询的sql命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面

语法：

select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 分组后筛选
order by 排序列表
limit 起始条目索引,显示的条目数

执行顺序

1》from子句
2》join子句
3》on子句
4》where子句
5》group by子句
6》having子句
7》select子句
8》order by子句
9》limit子句

特点：

①起始条目索引如果不写，默认是0
②limit后面支持两个参数
参数1：显示的起始条目索引
参数2：条目数

公式：

假如要显示的页数是page，每页显示的条目数为size

select *
from employees
limit (page-1)*size,size;

page	size=10
1                       limit 0,10
2			limit 10,10
3			limit 20,10
4			limit 30,10

10、union联合查询

说明

当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询

语法

select 查询列表 from 表1  where 筛选条件  
	union
select 查询列表 from 表2  where 筛选条件

特点

1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2、union实现去重查询
union all 实现全部查询，包含重复项

六、DML语言的学习

1、插入语句

语法

插入单行

insert into 表名(字段名1,字段名2 ,...) values (值1，值2,...);

插入多行

insert into 表名(字段名1,字段名2 ,...) values(值1，值2,...),(值1，值2,...),(值1，值2,...);

特点

①字段和值列表一一对应
包含类型、约束等必须匹配

②数值型的值，不用单引号
非数值型的值，必须使用单引号

③字段顺序无要求

2、修改语句

语法

update 表名 set 字段名 = 新值,字段名=新值,...
where 筛选条件;

3、删除语句

方式1：delete语句

delete from 表名 where 筛选条件;

方式2：truncate语句

truncate table 表名;

七、DDL语言的学习

说明

Data Define Language数据定义语言,用于对数据库和表的管理和操作

1、库和表的管理 √

(1)创建数据库

CREATE DATABASE stuDB;

CREATE DATABASE IF NOT EXISTS stuDB;

(2)删除数据库

DROP DATABASE stuDB;

DROP DATABASE IF EXISTS stuDB;

(3)创建表 ★

语法：
CREATE TABLE [IF NOT EXISTS] 表名(
	字段名  字段类型  【字段约束】,
	字段名  字段类型  【字段约束】,
	字段名  字段类型  【字段约束】,
	字段名  字段类型  【字段约束】,
	字段名  字段类型  【字段约束】
);

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT PRIMARY KEY,#添加了主键约束
	stuname VARCHAR(20) UNIQUE NOT NULL,#添加了唯一约束+非空
	stugender CHAR(1) DEFAULT '男',#添加了默认约束
	email VARCHAR(20) NOT NULL,
	age INT CHECK( age BETWEEN 0 AND 100),#添加了检查约束，mysql不支持
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)#添加了外键约束

);

(4)修改表[了解]

语法

ALTER TABLE stuinfo RENAME TO students;

ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP COLUMN 字段名 字段类型 【字段约束】;

(5)删除表 √

DROP TABLE IF EXISTS students;

(6)复制表√

#仅仅复制表的结构

CREATE TABLE newTable2 LIKE major;

#复制表的结构+数据

CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;

2、常见数据类型介绍 √

(1)整型

TINYINT SMALLINT  INT  BIGINT

(2)浮点型

FLOAT(m,n)
DOUBLE(m,n) 
DECIMAL(m,n)
m和n可选

(3)字符型

CHAR(n):n可选
VARCHAR(n)：n必选
TEXT
n表示最多字符个数

(4)日期型

DATE TIME  DATETIME TIMESTAMP

(5)二进制型

BLOB 存储图片数据

3、常见约束 √

说明

用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的！

NOT NULL 非空：用于限制该字段为必填项

DEFAULT 默认：用于限制该字段没有显式插入值，则直接显式默认值

PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空
	一个表只能有一个主键，当然可以是组合主键
	
UNIQUE 唯一：用于限制该字段值不能重复

CHECK检查：用于限制该字段值必须满足指定条件
	CHECK(age BETWEEN 1 AND 100)
	
FOREIGN KEY 外键:用于限制两个表的关系,要求外键列的值必须来自于主表的关联列
	要求：
	①主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
	②主表的关联列要求必须是主键

	字段是否可以为空	一个表可以有几个
主键	×	1个
唯一	√	n个

八、TCL语言的学习

事务和事务处理

概念

由一条或多条sql语句组成，要么都成功，要么都失败

特性：ACID

原子性
一致性
隔离性
持久性

分类

隐式事务

没有明显的开启和结束标记
比如DML语句的insert、update、delete语句本身就是一条事务

insert into stuinfo values(1,'john','男','ert@dd.com',12);

显式事务

具有明显的开启和结束标记

一般由多条sql语句组成，必须具有明显的开启和结束标记

步骤

取消隐式事务自动开启的功能
1、开启事务
2、编写事务需要的sql语句（1条或多条）
	insert into stuinfo values(1,'john','男','ert@dd.com',12);
	insert into stuinfo values(1,'john','男','ert@dd.com',12);
3、结束事务

SHOW VARIABLES LIKE '%auto%'

#演示事务的使用步骤

#1、取消事务自动开启
SET autocommit = 0;

#2、开启事务
START TRANSACTION;

#3、编写事务的sql语句

#将张三丰的钱-5000
UPDATE stuinfo SET balance=balance-5000 WHERE stuid = 1;

#将灭绝的钱+5000
UPDATE stuinfo SET balance=balance+5000 WHERE stuid = 2;

#4、结束事务

#提交
#commit;

#回滚
ROLLBACK;

SELECT * FROM stuinfo;