MySQL内容知识

1.新建数据库与新建表

1.1 DDL语言

其语句主要有CREATE,ALTER和DROP。

MySQL 是一个关系型数据库，库里包含若干个表，而每一张表都是由行和列组成的。

1.1.1 关于数据库操作的命令

-- 创建数据库
create database mydb;

 -- 当前都有什么数据库
show databases;

-- 切换到当前数据库下
use mydb;

-- 删除数据库
drop database mydb;

注意：删除时一定要注意数据库名字，确认删除的是自己的数据库。

1.1.2 关于数据表的操作命令

创建表结构

create table 表名（

                        列名 数据类型，

                        列名 数据类型，

                        列名 数据类型

）

-- 创建表
create table student(
				int id primary key auto_increment comment '学生学号',
				name varchar(10) comment '学生姓名',
				sex varchar(2) comment '学生性别',
				age int comment '年龄',
				major varchar(10) comment '专业',
				college varchar(10) comment '学院'
)

-- 显示当前数据库具有的表
show tables;

-- 显示表结构
desc student;

-- 删除表
drop table student;

修改表结构

-- 增加一列
alter table student add column aaa varchar(2);

-- 修改列名称
alter table student change column aa bb varchar(2);

-- 修改列的数据类型
alter table student modify column bb int ;

-- 删除某一列
alter table student drop bb;

1.2 DML语言

数据操作语言（DML:Data Manipulation Language）: 其语句包括动词INSERT（添加）、UPDATE（修改）和DELETE（删除）。

1.2.1 添加（insert）

-- 添加语句
insert into student(id,name,sex,age,major,college) values(901,'张三','男',20,'计科','信工学院');
insert into student(id,name,sex,age,major,college) values(902,'李四','女',20,'计科','信工学院');

1.2.2 修改操作（update）

-- 修改操作
update student set name='李四' , sex='女' where id=901;
update student set name='张三' , sex='男' where id=902;

1.2.3 删除操作（delete）

-- 删除操作
delete from student where id = 901;
delete from student where id = 902;

1.3 数据类型

1.3.1 数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

 1.3.2 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

1.3.3 字符串类型

 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

varchar(20) 长度可变----如果你存入的字符个数小于n 则mysql底层安装你存储字符个数分配空间。

char(20) 长度不可变----如果你存入的字符个数小于n mysql底层按照20的空间来分配。他一般用于长度固定的字符串。比如: 手机号，身份证号

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

问题： MySQL能不能存储音频 视频图片文件 ？

可以的 将音视频图片 转换成流 存储到数据库 blob 但是我们数据库 不存文件

文件一般存到磁盘 然后数据库中存储文件路径

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

2.查询

2.1准备工作

创建表（student）并放入数据

-- 查询是否存在student表，若存在则删除，不存在则不运行
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生表的主键列',
  `name` varchar(255) ,
  `age` int(11) ,
  `address` varchar(255),
  PRIMARY KEY (`id`) USING BTREE
) ;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 18, '北京');
INSERT INTO `student` VALUES (2, '李四', 19, '北京');
INSERT INTO `student` VALUES (3, '王五', 19, '北京');
INSERT INTO `student` VALUES (4, '钱七', 19, '北京');
INSERT INTO `student` VALUES (5, '关羽', 25, '南京');
INSERT INTO `student` VALUES (6, '张飞', 25, '南京');
INSERT INTO `student` VALUES (7, '赵云', 28, '南京');
INSERT INTO `student` VALUES (8, '项羽', 19, '南京');
INSERT INTO `student` VALUES (9, '刘备', 30, '上海');
INSERT INTO `student` VALUES (10, '曹操', 40, '许昌');
INSERT INTO `student` VALUES (11, '夏侯', 36, '许昌');
INSERT INTO `student` VALUES (12, '诸葛亮', 38, '上海');
INSERT INTO `student` VALUES (13, '周瑜', 78, '江东');
INSERT INTO `student` VALUES (14, '小乔', 56, '江东');
INSERT INTO `student` VALUES (15, '孙尚香', 59, '江东');

2.2 查询的开始

as 另起别名 、  *  所有的

-- 查询
select * from student ;  -- *表示所有的
select name as 姓名 from student; -- as 另起别名

2.3 去除重复的值

distinct 关键字

-- 去除重复值
select distinct address 城市 from student;

2.4 条件查询

and        or         like        _         %

1. 判断
select * from student where age>19;
select * from student where age>=19;
select * from student where age!=19;
select * from student where age<19;
select * from student where age<=19;

2. 多条件查询  and(与)  or(或)
select * from student where age>=19 and address='上海'

select * from student where age<=19 or address='上海'

3. 范围语句
select * from student where age>=19 and age<=30;

select * from student where age between 19 and 30; -- 等价于上面

4. 使用in
-- 此时我们有这样一个需求  查询年龄是 18 或者 25 或者 30 或者 36 或者 48 的学生
select * from student where age=18 or age=25 or age=30 or age=36 or age=48

-- 上面这种方式比较麻烦  我们可以使用in来替换上面的模式
select * from student where age in(18,25,30,36,48)


5. 模糊搜索 like 
  通配符: _统配一个字符    %统配n个字符。

-- 模糊查询
 -- 查询名字中第二个字符为三 _ 
 select * from student where name like '_三'; 
 
 -- 查询名字姓李  %统配任意字符
 select * from student where name like '李%'
 
 select * from student where name like '%三%'

2.5 排序查询

order by       desc : 降序      asc : 升序

 -- 根据多列进行排序年龄按照降序  姓名按照升序。
 select * from student order by age desc, id desc;

如果既有where又有order by 则where在前order by 在后

2.6 查询列上进行计算

可以进行加减乘除等

-- 年龄乘三
select age*3,name from student;

2.7 聚合函数

聚合函数总共有5个:
   max(列名):求某列的最大值。
   min(列名):求某列的最小值。
   sum(列名):求某列的和
   avg(列名):求某列的平均值
   count(列名):求某列的个数。

-- 求最大的年龄
select max(age) from student;

-- 求最小的年龄
select min(age) from student;

-- 求年龄的和
select sum(age) from student;

-- 求学生的个数.
select count(id) from student;

-- 求年龄平局值
select avg(age) from student;

2.8 分组查询

group by  语句将某一列相同数据视为一组， 然后进行查询与聚合函数连用。 

having 对分组进行条件检索。 

-- 求各个地区的人数
select count(*),address from student group by address;

-- 查询 最大值小于30 的地区 人数 和 平均年龄
select address,count(*),avg(age),max(age) from t_student group by address having max(age)<30;

注意: 如果使用了group by 那么select后只能根分组的条件列和聚合函数。

2.9 分页查询

limit关键字

当数据库表数据量比较大   例如： 1000w行数据 如果我们执行 select * from student 此时有可能数据库卡死 -- 拿出1000w数据到内存里，你的内存可能会不够。导致电脑卡死。
如果在java中 有可能内存直接溢出 所以实际开发中 都是分页查询 （部门查询）

 -- 从第3条记录查询 查询5条
select * from student limit 3,5;

--- 分页: 
select * from 表名 limit (n-1)*m,m; -- 查询第n页得m条记录。
--- n表示页码  m:表示每页得条数，

-- 第1页每页显示5条记录: 
select * from t_student limit 0,5;

-- 第2页每页显示5条记录: 
select * from t_student limit 5,5;

-- 第3页每页显示5条记录: 
select * from t_student limit 10,5;

-- 第4页每页显示5条记录: 
select * from t_student limit 15,5;

2.10 SQL的优先级

sql语法:
   select distinct *  from 表名 
       where 条件
       group by 分组
       having 分组条件
       order by 排序
       limit 分页。
 
上面为他的语法结构。顺序不能乱。 

mysql执行引擎:执行的顺序。

3 其他的函数

SQL也是一种编程语言，它里面也有函数，分为内置的函数个自定义的函数（高级）。

内置函数：

        1.  聚合函数 。

        2. 字符串函数。

        3. 时间函数。

        4. 算术函数。

        5. 其他函数。

3.1 字符串函数

常用函数

实例

-- 字符串拼接函数
select name from t_student;

select concat('我的名字叫:',name,",年龄:",age) as n from t_student;

-- 字符串转换为大写和小写
select name,upper(name),LOWER(name) from t_student;

-- 去除前后空格
select address,trim(address) from t_student;

-- 找到姓李得学生 字符串得截取
select * from t_student where name like '李%';

select * from t_student where substr(name,1,1)='李'

 3.2 数字函数

常见函数

实例

-- 向上取整 5.1你给定的值
select ceil(5.1),floor(5.9),mod(10,3),abs(-5.9) from t_student;

-- 要求如果status为0变为1 为1变为0  update
update aaa set status=abs(status-1); 

 3.3 时间函数

常用函数

实例

-- CURDATE()返回当前日期  后面可以不用跟表名
select curdate();
-- 可以用在where
select * from tb_emp where curdate()-entrydate=1

-- CURTIME()返回当前时间
select curtime() ;

-- NOW()返回当前日期和时间
select now();

-- YEAR(date)获取指定date的年份
select year('2022-04-25');

select * from tb_emp where year(entrydate)=2022

-- DATE_ADD(date, INTERVAL expr  type)返回一个日期/时间值加上一个时间间隔expr后的时间值
SELECT DATE_ADD('2020-04-25', INTERVAL 3 YEAR);

-- DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
select datediff('2020-05-25','2020-04-25');
-- 入职30天得员工.
select * from tb_emp where DATEDIFF(now(),entrydate)>30

 3.4 流程函数

常用函数

 实例

-- 如果年龄>30妇女  否则少女
select age,if(age>30,'妇女','少女') from tb_emp;

-- ifnull 如果为null，则
select job,ifnull(job,'工作待定') from tb_emp;

4 约束

约束：在表中为某些列添加约束，使得该列必须符合这个约束

4.1 常见约束

create table t_teacher(
    id int primary key auto_increment, -- 要求该列得值必须唯一且不能为null.
	  tname varchar(20) unique, -- unique 唯一约束 表示该列得值必须唯一。但是可以运行多个null
		age int not null, -- not null 非空约束 表示该列得值不能为null.
		sex char(2) default '男' -- default 默认约束 表示如果该列没有给定值 则默认为男
)

4.2 外键约束

 外键实例

外键约束用来让两张图的数据之间建立连接，保证数据的一致性和完整性。 
-- 外键
create table t_class(
    cid int primary key auto_increment,
		cname varchar(20)
);
create table t_stu(
     sid int primary key auto_increment,
		 sname varchar(20),
		 classid int -- 表示外键列。
);
-- add contraint 约束得名称  foregin key 外键得列名 REFERENCES 主表名(主键)  

-- alter table 从表名 add contraint 约束名随便起 foreign key 外键列名 references  主表(主键)
alter table t_stu add CONSTRAINT fk_stu_class FOREIGN key (classid) REFERENCES t_class(cid);

insert into t_class values(null,'QY145'),(null,'QY151');

insert into t_stu values(null,'李青华',2);
-- 因为classid=3 这个值 在我们得班级表中不存在。所以不能添加
insert into t_stu values(null,'张青',1);

图表解释

5 连表查询

1. 如果查询的结果在一张表中无法获取，需要关联多张表，那么这时候就需要连表查询。

2. 连表查询大多数都作用在外键的基础上。  --- 需要表与表之间有关联

5.1 表与表之间的关系

 一对一：在多的一方添加外键列

多对一：需要在创建一张表，该表中至少有两个外键列

5.2 多表连接查询

5.2.1 连接查询

内连接：相当于A、B 的交集部分数据

外连接：

        左外连接：查询左表所有数据，以及两张表交集部分

        右外连接：查询右表所有数据，以及两张表交集部分

自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询：

5.3 内连接查询语法

5.3.1 隐式内间接

5.3.2 显式内连接 

内连接查询的是交集部分

实例 

-- 隐式查询 select 列名.... from 表1,表2 where 连表得条件。
select * from tb_emp e, tb_dept d where e.dept_id=d.id;

-- 显示连接: A表 inner join B表 on 连表条件。 
select * from tb_emp e  join tb_dept d on e.dept_id=d.id;

5.4 外连接

外连接的语法：

5.4.1 左外连接

相当于查询表1（左表）的所有数据包括表一与表二交集部分的数据

5.4.2 右外连接 

相当于查询表二（右表）的所有数据，包含表一与表二交集部分的数据

实例

-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)
select * from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
select * from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- 2．查询dept表的所有数据，和对应的员工信息(右外连接)
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;

 5.5 自连查询

自己和自己相连接查询。
select * from A表 join A表 on 连表条件。

实例

-- 查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;

5.6 子查询 ---- 嵌套查询

	-- 根据财务部最高工资查询其他员工信息
	select * from tb_emp where salary>(	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部')

5.7 组合查询

多个查询的结果 组合到一起。
sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录，不合并。 

注意: 这两条sql返回的字段必须一样。

select name from tb_emp where salary>8000
	UNION 
	select * from tb_emp where age>40;