MySQL数据库入门与高级应用-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43296748/article/details/103498465

文章目录

1、什么是MySQL

MySQL是一种关系型数据库，使用表格的形式存储我们的数据

2、什么是数据库（DB）

数据库（DB）其实就是保存在电脑上面的文件，这个文件有独特的保存数据结构，并且对这种结构文件提供专门的软件来对这些文件进行管理，我们将这些管理数据文件的软件称之为数据库管理软件（DBMS）

3、什么是数据库管理软件（DBMS）

因为数据库文件保存数据，我们无法直接打开查看，所以我们消炎药借助一些软件来查看和操作，这些软件就是数据管理软件，常见的数据软件有：
1.Oracle
2.SqlServer
3.MySQL
4.Access
5.DB2
6.SyBase

4、SQL 统一的结构化查询语言

各大数据库厂商意识到如果每个管理软件在操作数据库的时候都不一样,这样对数据库的发展,包括人员的培训都提高了很大的成本,这些厂商共同制定了一个数据库的操作语言,将这个语言称之为SQL语言,初衷是可以使用这个SQL语言来操作所有的数据库。然后根据后期发展大的数据库厂商在这个SQL语言的标准上,增加了我这个数据库独特的编程语言（方言,以后再学习Hibernate的时候需要对数据库的方言进行配置）

5、安装MySQL数据库

1.解压下载的安装包到硬盘中（最好安装的路径不要有中文和空格）

我的安装路径为 D:\myinstall\java\mysql\mysql-5.7.21-winx64

2.在解压的根目录下创建一个空的文件夹data和一个my.ini文件

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口 port = 3306
# 设置mysql的安装目录
basedir=D:\myinstall\java\mysql\mysql-5.7.21-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\myinstall\java\mysql\mysql-5.7.21-winx64\data
# 允许最大连接数
max_connections=200
#服务端使用的字符集默认为8比特编码的latin1字符集 	character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

3.安装MySql 服务

1）配置MySql环境变量

	为了以后可以方便的在控制台输入mysql的命令,我们可以将mysql配置到环境变量中。
	MYSQL_HOME----> MySql的家路径		D:\myinstall\java\mysql\mysql-5.7.21-winx64
	PATH	----> %MYSQL_HOME%\bin

2）安装MySql服务

	a. mysqld install      安装 Service successfully installed. 	
	b. mysqld --initialize-insecure --user=mysql   初始化

3）启动MySQL 服务

	net start mysql        MySQL 服务正在启动 . MySQL 服务已经启动成功。

4) 关闭MySql 服务器

	net stop mysql        MySQL 服务正在停止. MySQL 服务已成功停止。

5) 卸载MySql

	1.停止MySql 服务 net stop mysql
	2.删除mysql 服务 sc delete mysql
	3.删除MySql安装文件删除文件
	4.重启电脑
	5. MySql 数据库的常见操作

6) 如何登录MySql

	mysql -u用户名 -p密码默认情况下 我们刚装的数据库是没有密码的,所以直接-p
	刚装好的数据库 默认的用户名叫做 root

6. 数据库的快速入门

创建一个数据库

create database dbname;

使用我们刚创建的数据库

use dbname;

创建表

如果想创建一张表,必须要选择在哪一个数据库中创建否则会出错。
创建一张学生表 t_student
create table 表名(
	列名 列类型,列名 列类型,
	...
	列名 列类型
);
create table t_student( sno int,
name varchar(50), age int,
adress varchar(100), des varchar(200)

);

查看当前的数据库中有哪些表

show tables;

插入数据

向数据库中的表插入数据（记录）插入部分的列
insert into 表名(要插入的列名1,列名2,,,) values(填写对应的列值,,,)
插入全部的列
insert into 表名 values(列值1,,,,)
-- 插入所有的列
insert into t_student Values(1001,'zhangsan',22,'hefei','	好学生'); 
insert into t_student values(1003,'王五',24,'安庆','好学生');
--插入部分列
--未插入的列,以null值补全Insert into t_student(sno,name,age) values(1002,'lisi',23);

查询表中的所有记录

select * from 表名;

删除记录

delete from 表名 where 条件 （不加where即为清空表）

删除表

drop table 表名

删除数据库

drop database 数据库名;

7. SQL语言的分类

标准的SQL 分为:DDL DML DCL
因为查询语句在SQL中比较重要,用的也很多,所以将查询从DML 中分离出来形成一个DQL语句。将安全事务从DCL 分离形成 TCL 语句
现在的语句一般认为有下面几种
DDL(★★★)
数据定义语言,代表关键字为 create drop alter
DML(★★★)
数据操纵语言,代表关键字为 insert,update,delete
DCL
安全性相关的,主要是负责对数据库用户的操纵（创建,授权,收权,删除… 主要是DBA的工作）
DQL (程序员重点掌握★★★★★)
数据查询语言,代表关键字为 select TCL (★★★★★)
事务控制语言, commit rollback…

DDL 定义语言

1）创建数据库

create database 数据库名字;

2）删除数据库

drop database 数据库名字;

3）创建表

create table 表名 values(
	列名 列类型,
	列名 列类型,
	...
	列名 列类型
);

4）删除表

drop table 表名;

5）查看表结构

desc 表名;  DESC t_student;

6）修改表名

alter table 旧表名 rename to 新表名

7）修改列名

alter table 表名 change 旧字段名 新字段名 新数据类型

8）修改列类型

alter table 表名 modify 字段名

9）添加列

alter table 表名 add 新字段名

10）添加指定列

alter table 表名 add 新字段名数据类型

11）删除列

alter table 表名 drop 字段名

12）mysql 关键字处理

	-- 如果要创建一张表,表名叫做 table
	-- 使用 ` 将 关键字 引起来。
	CREATE TABLE `table`( t_id INT,
	t_name VARCHAR(30)
	);

DML：数据操作语言

1）插入语句：insert into 表名values(字段值1,字段值2,…);

	备注：赋值的顺序必须和表中字段顺序一致且字段数量一致。
insert into 表名(字段1,字段2,...) values (字段值1,字段值2,...);
	备注：根据需要往表中插入数据；

2）修改数据：update 表名 set 字段名=‘新的字段值’;

备注：上面这种写法会将表中所有纪录中的字段值都修改为新的值，不是改变一个，
      为避免这种情况，往往在后面加上相应的条件可以用关键字where表示；

3）删除语句：

delete from  表名 where 条件
Truncate table 表名；删除全部数据
备注：第二种方式为先将表删除再创建表；

DCL语言：数据控制语言

1）创建用户：

create user 用户名@地址 identified by ‘密码’;

2）给用户授权：

grant 权限1,.... on 数据库.* to 用户; 
*：代表所有的表

3）创建用户：

create user 用户名@‘%’ identified by ‘密码’;可以在任何电脑上访问用户

4）撤销授权：

revoke 权限1... on 数据库名.* from 用户名		@localhost;

5）删除用户：

drop user 用户名；

6）修改用户密码：

a.use mysql;
b.update user set password=password(‘新 密码’) 
c.where user=’用户名’ and host=’Ip’;
d.flush privileges;
备注：MySQL数据库5.7版本开始就没有password字段了，更新为
authentication_string;

DQL语言：数据查询语言

1) 条件查询 where

根据一定条件查询出结果，常常和一些运算符和关键字一起使用
运算符: >  <  =  !=  <>  >=  <=
关键字: between and  前包括后也包括   
        is  null   not   and   or  in()
exists：表示存在，当子查询的结果存在，就会显示主查询中的所有数据。
当外表是大表，内表是小表的时候，那么使用in
当外表是小表，内表是大表的时候，那么使用exists

2) 模糊查询 like

在不知道具体内容的情况下使用，关键字like，需要注意的符号：_表示一个字符，%表示任意字符；
-- 查询姓名由5个字母构成的学生记录
SELECT * FROM stu WHERE sname LIKE '_____';
-- 查询姓名中第2个字母为‘a’的学生纪录
SELECT * FROM stu WHERE sname LIKE '%a%';

3) 字段控制查询

去除重复纪录，使用关键字DISTINCT
-- 查询emp表中的薪水等级的内容
SELECT DISTINCT sal FROM emp;
-- 字段控制的另一个关键字IFNULL(参数1,参数2);
-- 参数1:为NULL值的字段 参数2：将NULL赋值一个默认值
SELECT ename,sal,comm,sal+IFNULL(comm,0.0) as money FROM emp;-- 此处的as可以省略

4）排序查询

升序asc和降序desc：默认的排序方式为升序 asc
-- 查询所有的学生纪录，按年龄排序
SELECT * FROM stu ORDER BY age;
-- 查询所有的学生纪录，按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;
-- 查询所有的雇员，按月薪降序排序，如果月薪相同，按编号升序排序
SELECT * FROM emp ORDER BY sal DESC ,empno ASC;

5）聚合函数

SUM();求和
-- 查询所有员工的月薪和；
SELECT SUM(sal) FROM emp;
-- 查询所有雇员的月薪和，以及佣金和
SELECT SUM(sal),SUM(comm)FROM emp;
-- 查询所有雇员的月薪加佣金和
SELECT SUM(sal+IFNULL(comm,0.0)) FROM emp;
MAX();求最大值
-- 查询最高工资和最低工资
SELECT MAX(sal),MIN(sal) FROM emp;
MIN();求最小值
COUNT();统计个数，统计指定列不为null的个数
-- 查询emp表中纪录数：
SELECT COUNT(*) FROM emp;
-- 查询emp中有佣金的人数
SELECT COUNT(comm) FROM emp;
-- 查询emp中月薪大于2500的人数
SELECT COUNT(sal) FROM emp WHERE sal>2500;
-- 查询有佣金的人数，以及有领导的人数
SELECT COUNT(comm) ,COUNT(mgr) FROM emp ;
AVG();求平均数
-- 统计所有员工的平均工资
SELECT AVG(sal) FROM emp;

6）分组查询

关键字：group by 对查询结果再次进行操作
-- 查询每个部门的编号以及每个部门工资大于1500的人数
SELECT deptno,COUNT(sal) FROM emp WHERE sal>1500 GROUP BY deptno;
-- 查询工资总和大于9000的部门编号以及工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
备注：where 和 having 关键字的区别？
Where 后面跟的条件必须为表中自身拥有的字段值，而having后面跟的条件为查询结果的值

7）分页查询

关键字limit MYSQL特有的一个功能，其他数据库没有。
Limit用来限定查询结果的起始行，以及总行数。
语法：limit 参数1，参数2;
参数1：从哪条记录数开始    0表示第一条纪录
参数2：查询多少条记录    
例：limit 3,5; 查询的是第4条纪录到第8条纪录

8. 数据库常见的数据类型

int：整数类型，默认长度为11
double：浮点类型，书写方式为(5,2)表示有五位数组成，其中小数位后面保留两位小数。最大值为999.99；
decimal：精确小数类型，书写方式为(5,2)表示有五位数组成，其中小数位后面保留两位小数。定义跟金钱有关系的变量;
char:固定长度字符串类型
varchar:变长字符串类型
Text：字符串类型
Date：日期类性  格式：yyyy-MM-dd
Time：时间类型  格式：hh:mm:ss

char和varchar之间的区别？
Char声明字段开辟的存储空间用不完不会释放（查询效率高），而varchar声明的字段开辟的存储空间用不完会释放出去（查询效率低）；

9.MySql中的相关约束

作用：都是为了保证数据的准确性
主键约束：primary key
特点：将字段设为主键后，该字段的值不能为空，且不能重复
备注：一张表中只能声明一个主键

1) 主键声明的方式：

1 创建表的时候，在字段的后面直接添加primary key
2 创建表的时候，在声明完所有的字段之后添加primary key(主键字段)
3.创建表之后，通过修改表结构来添加主键：alter table 表名 add primary key

2) 主键自增长 auto_increment

MySql提供了主键自动增长的功能！这样用户就不用再为是否有主键是否重复而烦恼了，
当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键
值+1，也就不会有重复主键的可能了。
声明方式：1. 创建表的时候，直接在字段后面声明

3) 通过修改表结构声明 alter table 表名 change 字段字段数据类型 auto_increment;

4) 主键的删除

Alter table 表名 drop primary key;

5) 删除主键自增长

Alter table 表名 change 字段 数据类型;

6) 非空约束

指定非空约束的列不能没有值，也就是插入纪录时，对添加的非空约束的列一定要给值；
在修改纪录时，不能把非空列的值设置为NULL；
声明方式：创建表时，在字段后面添加 NOT NULL

7) 唯一约束 unique

可以为字段指定唯一的约束，当为字段指定唯一的约束后，他的值不能重复。
一张表中可以有多个唯一约束。

8) 外键约束

外键和主键是建立表与表之间的关系的唯一途径，往往一张表的主键是另一张表的外键。
语法：	1.创建表的时候添加外键   2.通过修改表结构创建外键
Constraint 外键名 foreign key(字段名) references 相对于哪张表的外键(表主键);

9) 表与表之间的关系

一对一 一对多（多对一） 多对多

10) 多表之间的查询

1.内连接：多表中的公共部分，就是我们数学中的集合的交集

语法：1. select 列名，列名...from 表名1，表名2 where 表名1.字段=表名2.字段;
-- 查询每种水果的价格
SELECT A.`A_ID`,A_NAME,B_PRICE FROM A,B WHERE  	A.`A_ID`=B.`A_ID`;
-- 语法2:select * from 表名1 inner join 表名2 on 条	件 
SELECT A.`A_ID`,A_NAME,B_PRICE FROM A JOIN B ON  	A.`A_ID`=B.`A_ID`;
2.select * from 表名1 inner join 表名2 on 条件;

2.左外连接：左表数据不动，右边表的数据往左表上添加，不管是否找到，都将显示左边表中全部记录。

语法: select * from 表1 left outer join 表2 on 条件;
-- 不管能否查到水果对应的价格，都要把水果显示出来。
SELECT * FROM A LEFT OUTER JOIN B ON A.`A_ID`=B.`A_ID`;

3.右外连接：右表数据不动，左边表的数据往右表上去添加，不管是否找到，都将显示右边表中全部记录。

语法: select * from 表1 right outer join 表2 on 条件;
-- 不管能否查到价格对应的水果，都要把价格显示出来。
SELECT * FROM A RIGHT OUTER JOIN B ON A.`A_ID`=B.`A_ID`;

4.全外连接：左外连接和右外连接的结果合并，且会去掉重复的记录。

语法：select * from 表1 full outer join 表2 on 条件;但是mysql数据库不支持此语法。Oracle数据库支持
SELECT * FROM A FULL OUTER JOIN B ON A.`A_ID`=B.`A_ID`;
-- 我们又必须要这个结果怎么办？这个时候可以通过联合查询来得到结果
-- 需求1：将左连接和右连接整合在一起显示出来。（使用union all）
-- 需求2：将左连接和右连接整合在一起 去除重复的 显示出来。（使用union）
SELECT * FROM A LEFT OUTER JOIN B ON A.`A_ID`=B.`A_ID` UNION ALL SELECT * FROM A RIGHT OUTER JOIN B ON A.`A_ID`=B.`A_ID`;
SELECT * FROM A LEFT OUTER JOIN B ON A.`A_ID`=B.`A_ID` UNION  SELECT * FROM A RIGHT OUTER JOIN B ON A.`A_ID`=B.`A_ID`;

SQL语句子查询

  子查询：把一个sql的查询结果作为另外一个查询的参数存在。
  常见的子查询有where型子查询(将查询结果当成条件来用)  
 -- 需求1：查询价格最贵的水果名称
  -- 1.找出最贵价格
  SELECT MAX(B_PRICE) FROM B;
  -- 2.找出最贵价格的id
  SELECT A_ID FROM B WHERE B_PRICE=(SELECT MAX(B_PRICE) FROM B);
  -- 3.最贵价格的id到A表中匹配水果
  SELECT A_NAME FROM A  WHERE A_ID=(SELECT A_ID FROM B WHERE B_PRICE=(SELECT MAX(B_PRICE) FROM B)) ;

  from型子查询（将查询结果当成表来用）

10.视图

视图是查询结果形成的一张虚拟表（并不是我们自行创建的表，而是将一个频繁使用的查询结果当做一个表）
语法：create view 视图名 as select 语句
视图的作用：
1）简化查询语句
2）方便设置权限控制
3）对大数据进行分表
视图和标的关系：视图是标的查询结果，自然表的数据改变了，影响视图的结果
视图必须包含表中没有默认值也不能为空的列。

11.触发器

Trigger、枪击、扳机、引线的意思。
作用：监视某种情况并触发某种操作。
四要素：监视地点监视事件触发时间触发事件
语法：create trigger triggerName after/before insert/update/delete on 表名 for each row begin 表数据的操作语句, end;
备注：因为sql语句是以;结尾，所以在使用触发器的时候需要将结束符修改一下
修改结束符的语句：delimiter $ 设置$为结束符