MySQL数据库

一、什么是数据库

        数据库就是用来存储数据的，网页、程序等现实的数据都是从数据库中取出来的。Mysql是一个关系型数据库，存储数据形式是表格形式。

        一个项目对应着一个数据库，数据库服务器下面可以有很多个数据库，一个数据库中可以有很多个表，一个表中可以有很多个字段和数据。

（本文主要讲解 mysql 在 cmd/可视化工具 上的操作命令）

二、通过命令操作数据库

#1.连接数据库服务器的命令
mysql -uroot -p
Enter password: ******

#2.展示当前服务器下面有多少个数据库
show databases;

#3.新建一个数据库 	  create database 库名;
create database person;

#4.删除库  		drop database 库名;	//慎用
drop database person;
#5.选中库			use 库名;
use person;
#6.查看当前数据库下面有多少张表
show tables;

#7.创建表
create table 表名字 (字段1 数据类型,字段2 数据类型,...);

#8.换一种方式创建表
create table person (
 -> id int,
 -> name varchar(32),
 -> age int
 -> );

#9.查看表的结构
desc person;

#10.删除表
drop table person;		//慎用

2.1、修改创建好的表字段

#1.删除age字段   语法格式:alter table 表名 drop  字段;
alter table person drop age;

#2.添加age字段语法格式:  alter table 表名  add 字段 数据类型;
alter table person add age int;

#3.在指定的字段的后面添加一个字段，name的字段的后面添加一个字段 
#语法格式:alter table 表名 add 字段 数据类型 after 字段;
alter table person add sex boolean after name;

#4.修改一个字段的数据类型  name数据类型 varchar 改为char类型
#语法格式:alter table 表名 modify 字段 修改后的数据类型;
alter table person modify name char(20);

#5字段和数据类型一起修改
alter table person change sex gender int;

#6.添加一个字段
#数据类型 text  文本 字符串 不限制大小
alter table person add info text;

#7.加薪资字段  小数
#  float(8,2);
#  double(8,2);
#  decimal(8,2);
#最大长度是8位 其中小数占2位
alter table person add salary decimal(8, 2);

#8.展示表中的数据
desc person;

2.2、添加数据到表

#1.插入一条数据到表中
insert into person values (值1, 值2,....);

#2.删除数据
DELETE FROM 表名称 WHERE 列名称 = 值
#删除多条数据
delete from person where id in (1,3,4);


#3.修改数据
UPDATE 表名称 SET 列名称 = 新值，列名称=新值,... WHERE 列名称 = 某值

#4.查询
#选择所有的数据
select * from person;

#查询指定的字段的数据
#select 字段1,字段2,字段3,... from 表名;
select id, name, salary  from person;

#按照字段进行查询的时候，可以对字段进行起别名
#select 字段1 as 别名1, 字段2 as 别名2 , ... from 表名
select id as "编号", name as "名字", salary as "薪资" from 		person;

2.3、通过条件查询数据

下面的运算符可在 WHERE 子句中使用：

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

代码案例如下：

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

#例1
select * from person where id=6;
#例2
select * from person where name like "%王%";

#例3
select * from person where gender = 1 and(&&) age = 29;
#例4
select * from person where age < 30 or(||) gender = 0;

#ORDER BY 语句用于对结果集进行排序。
select 字段 from 表 order by 字段 asc;默认是升序  asc可以省略
select 字段 from 表 order by 字段 desc;
#例5
select * from person order by salary;
#例6
select * from person order by salary desc, age asc;

#额外：
limit :限制输出
#注意事项:    limit 必须在where  和   order by的后面
	limit 数字;
	limit 数字1, 数字2;
#数字1    偏移量
#数字2    数据的个数

2.4、通过条件查询指定字段的数据

select 方法(字段名字) FROM 表名;

#方法:
#求一个字段中数据的最大值
	max();	   
#求一个字段中数据的最小值
	min();
#求一个字段中数据的平均值
	avg();
#求一个字段中所有数据的和
	sum();
#统计当前表中数据的个数
	count(*);

2.5、事务操作

事务操作：事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
set autocommit = 0;
语句1;
语句2;
commit;(提交)		或者		rollback;(回滚)

2.6分组统计

        GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

#以谁分组，就展示谁（select）
	select 字段名 from 表名 group by 字段名;
#展示且计数
	select 字段名,count(*)  from 表名 group by 字段名;
#一般使用group by的时候 需要对分组之后的数据进行筛选，使用having，不能where
	select 字段名,count(*) from 表名 where 属性 > 1 group by gender having count(*) > 1;

三、约束

3.1、数据约束

        在创建表的时候，一定要对字段进行约束，这样插入数据的时候，才更加合理，约束性。

#1、默认值	default
	如果添加数据时当前字段没有被赋值，则设置为默认值
	
#2、非空	not null
	如果当前字段由非空约束，则添加数据时该字段必须被赋值

#3、唯一	unique
	如果当前字段由唯一约束，则不能添加重复数据

#4、主键	primary key
	如果当前字段由主键约束，该字段的数据必须唯一且非空。一个表只有一个主键，且主键字段和数据真实属性无关

#5、自增长	auto_increment	(一般配合主键修饰)
	如果当前字段由自增长约束，插入新数据时会自动创建主键字段的值
	
#6、时间字段
	timestamp	默认值		current_timestamp
	date
	
#综合实例
mysql> create table person6(
    -> id int primary key auto_increment,
    -> name varchar(32) not null,
    -> age int default 10,
    -> info text unique,
    #添加系统当前时间
    -> createTime timestamp default current_timestamp
    -> );

3.2、外键的约束

        如果两个表中有相关的数据，删除一个表（主表）的数据，另一个表（副表）的数据也要改变。因此可以添加一个外键约束，将两个表联系起来。

mysql> create table 表名 (
	-> constraint fk_表1_表2 foreign key(该表的属性)  references 另一个表(对应的属性)
    -> );
    
#加上外键约束以后，增删改  有的时候会报错:
#只有按照下面的操作才不会报错
增加的时候: 先增加主表 再增加副表
删除的时候: 先删除副表 再删除主表
修改的时候: 先修改副表 再修改主表

#级联操作：
修改和删除时只需要修改一个，两个表的数据都会修改
#级联修改和级联删除
mysql> create table 表名 (
	-> constraint fk_表1_表2 foreign key(该表的属性)  references 另一个表(对应的属性)
    -> on delete cascade
    -> on update cascade
    -> );

四、联表查询（重点）

4.1、常规的联表查询

mysql> select 字段	//查什么
    -> from 表1,表2	//从哪里查
    -> where 表1.属性 = 表2.属性 and 表1.属性 = 值;

下边是例子：

mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       4 |    A部    |
|       5 |    B部    |
|       6 |    C部    |
+---------+-----------+


mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      3 | 张三    |       4 | 2023-04-10 10:46:04  |
|      4 | 李四    |       4 | 2023-04-10 10:46:08  |
|      5 | 王五    |       4 | 2023-04-10 10:46:11  |
+--------+----------+---------+---------------------+


#例子1：联表查询，查看 erbei 属于哪个部门
mysql> select emp.emp_name, dept.dept_name
    -> from emp, dept
    -> where emp.dept_id = dept.dept_id and emp.emp_name = "李四";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| 李四     |    A部    |
+----------+-----------+

4.2、内连接联表查询

        只是换了一种写法

#可以关联一个表，也可以关联多个表
SELECT 	column_name(s)  	查什么
FROM 	table_name1    		从哪查  表1
INNER JOIN table_name2   	关联 表2
ON 		table_name1.column_name=table_name2.column_name		通过哪个属性关联
(
    INNER JOIN table_name2   	关联 表3
	ON 		table_name1.column_name=table_name2.column_name
)

#INNER JOIN 与 JOIN 是相同的

4.3、外连接（开发少用）

#分为左外连接和右外连接:

#LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
mysql> select *
    -> from dept d
    -> left outer join emp e
    -> on d.dept_id = e.dept_id;
+---------+-----------+--------+----------+---------+---------------------+
| dept_id | dept_name | emp_id | emp_name | dept_id | reg_time            |
+---------+-----------+--------+----------+---------+---------------------+
|       4 |   A部    |      3 |   张三    |       4 | 2023-04-10 10:46:04 |
|       4 |   B部    |      4 |   李四    |       4 | 2023-04-10 10:46:08 |
|       6 |   C部    |   NULL |  NULL     |    NULL | NULL                |
|       7 |   D部    |   NULL |  NULL     |    NULL | NULL                |
+---------+-----------+--------+----------+---------+---------------------+


#RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
mysql> select *
    -> from emp e
    -> right outer join dept d
    -> on e.dept_id = d.dept_id;
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time            | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
|      3 |  张三    |       4 | 2023-04-10 10:46:04 |       4 |   A部    |
|      4 |  李四    |       4 | 2023-04-10 10:46:08 |       4 |   B部    |
|   NULL | NULL     |    NULL | NULL                |       6 |   C部    |
|   NULL | NULL     |    NULL | NULL                |       7 |   D部    |
+--------+----------+---------+---------------------+---------+-----------+

五、开发中如何使用多表联查

5.1、一对多查询或者多对一查询

#场景: 一个老师对应多个学生
#常规查询:
mysql> select teacher.t_name, student.s_name
    -> from teacher, student
    -> where teacher.t_id = student.teacher_id and teacher.t_name = "老师";
    
#内连接查询:
mysql> select t.t_name,s.s_name
    -> from teacher t
    -> inner join student s
    -> on t.t_id = s.teacher_id
    -> where t.t_name = "老师";

5.2、多对多查询

#场景: 一个学生对应多个课程，一个课程对应多个学生

#增加一个中间表，各存放一个数据
#常规查询:
mysql> select stu.s_name, course.c_name
    -> from stu, stu_course, course
    -> where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "学生名";
    
#内连接查询: 
mysql> select s.s_name, c.c_name
    -> from stu s
    -> inner join stu_course sc
    -> on s.s_id  = sc.s_id
    -> inner join course c
    -> on sc.c_id = c.c_id
    -> where s.s_name = "学生名";

六、对sql的归纳（了解）

1.数据库查询语言  DQL
	查询select
2.数据库定义语言  DDL
	建库、建表、修改库、修改表
3.数据库操作语言  DML
	insert delete update
4.数据库事务语言  DTL
	事务
5.数据库控制语言  DCL
	权限管理