MySql基础知识回顾

基本概念

数据 Data
数据库 Database DB
数据库管理系统 database management system DBMS
数据库应用程序 database application system DBAS
数据库管理员 database Administrator DBA
最终用户 User
数据库系统 database system DBS

数据库类型：
关系数据库（MySql）和非关系数据库（Redis）

Mysql默认端口号3306 oracle默认端口号1521

DBA通过DBMS对DB中的D进行操作
Structured Query Language （SQL）：非过程化语言，只需要提出做什么，不需要指明怎么做

SQL语言

数据查询语言 DQL： select from where
数据操作语言 DML：Insert Update Delete
数据定义语言 DDL：Create Alter Drop
数据控制语言 DCL：Grant Revoke
事务控制语言 TCL：Start Transaction Commit RollBack Set Transaction

数据库表操作

行：记录 列：字段

-- 创建数据库表
create TABLE t_student(
	sno int(6),
	sname varchar(10),
	sex char(1),
	age int(3),
	enterdate date,
	classname varchar(10),
	email varchar(15)
);
-- 查看表的结构，展示表的详细信息
DESC t_student;

-- 查看表中数据
select * from t_student;

-- 查看建表语句
show create TABLE t_student;
-- 结果如下
CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(10) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

注意事项
1.int显示宽度，如果超过，主动增大，底层4字节
2.时间可以–，//，…
3.字符串不分单双引号
4.当前时间是now() sysdate() Current_date()
5.char varchar是字符的个数不是字节的个数 可用binary和varbinary表示定长和不定长字节个数

create database AAA
drop database AAA
use AAA
create table XXX （）；
DROP TABLE XXX ;
insert into XXX (A,B,C) values(a,b,c)
select A,B from XXX where
UPDATE XXX SET A=q, B=b
Delete from XXX where
update XXX set A=aa where
constraint name primary key （）
constriant name check （x>12 and x<22）
ALTER TABLE XXX DROP i; 字段操作
ALTER TABLE XXX ADD i INT;
ALTER TABLE XXX MODIFY c CHAR(10); 改类型
ALTER TABLE XXX CHANGE i j BIGINT;改名改类型

非外键约束

primary key
自增：auto_increment
not null
default
check()
unique

约束分类： 表级约束，列级约束
列级约束是定义在列属性中的，而表级约束是定义在列之后的，两者本质上没什么区别，而如果你的约束需要同时对多列进行约束那么就只能采用表级约束，因为表级约束面向的是表（当然就包括所有列），而列级约束只能针对该列进行约束。

外键约束

-- 添加班级信息
create table t_student(
		cno int(4) primary key auto_increment,
		cname varchar(7) unique,
		croom char(5) unique
);

alter table t_student rename to t_class;

insert into t_class values (null,'java1','r1423'),(null,'java2','r4534'),(null,'java3','r5643');

select * from t_class;

-- 外键约束只有表级约束没有列级约束,在创建表的时候添加外键约束
create table t_student(
		sno int(6) primary key auto_increment,
		sname varchar(5) not null,
		classno int(4),
		CONSTRAINT fk_stu_classno FOREIGN key (classno) REFERENCES t_class (cno)
);

-- 在创建表之后添加外键约束
create table t_student(
		sno int(6) primary key auto_increment,
		sname varchar(5) not null,
		classno int(4)
);
alter table t_student add CONSTRAINT fk_stu_classno FOREIGN key (classno) REFERENCES t_class (cno);

-- 添加学生信息
insert into t_student VALUES(null,'张三',1);
-- 报错 1452 - Cannot add or update a child row: a foreign key constraint fails (`dtest`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
insert into t_student VALUES(null,'张三',3);
insert into t_student VALUES(null,'张三',2);
insert into t_student VALUES(null,'张三',3);

select * from t_student;

-- 报错：1451 - Cannot delete or update a parent row: a foreign key constraint fails (`dtest`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
delete from t_class where cno=1;

在删除的时候先删除从表再删除主表
直接删除主表不行，因为有外键约束：
加入外键策略

-- 添加班级信息
create table t_student(
		cno int(4) primary key auto_increment,
		cname varchar(7) unique,
		croom char(5) unique
);

alter table t_student rename to t_class;

insert into t_class values (null,'java1','r1423'),(null,'java2','r4534'),(null,'java3','r5643');

select * from t_class;

-- 外键约束只有表级约束没有列级约束,在创建表的时候添加外键约束
create table t_student(
		sno int(6) primary key auto_increment,
		sname varchar(5) not null,
		classno int(4),
		CONSTRAINT fk_stu_classno FOREIGN key (classno) REFERENCES t_class (cno)
);

-- 在创建表之后添加外键约束
create table t_student(
		sno int(6) primary key auto_increment,
		sname varchar(5) not null,
		classno int(4)
);
alter table t_student add CONSTRAINT fk_stu_classno FOREIGN key (classno) REFERENCES t_class (cno);

-- 添加学生信息
insert into t_student VALUES(null,'张三',1);
-- 报错 1452 - Cannot add or update a child row: a foreign key constraint fails (`dtest`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
insert into t_student VALUES(null,'张三',3);
insert into t_student VALUES(null,'张三',2);
insert into t_student VALUES(null,'张三',3);

select * from t_student;

-- 报错：1451 - Cannot delete or update a parent row: a foreign key constraint fails (`dtest`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
delete from t_class where cno=1;


-- 外键策略1
updata t_student set classno=null where classno =2;
delete from t_class where cno=2;


-- 外键策略2  cascade级联操作
alter table t_student drop FOREIGN key fk_stu_classno;
alter table t_student add CONSTRAINT fk_stu_classno  FOREIGN key (classno) REFERENCES t_class (cno) on update CASCADE on DELETE CASCADE;
UPDATE t_class set cno=5 where cno =3;
delete from t_class where cno=5;
-- 会把学生一同删掉


-- 外键策略3  置空操作
alter table t_student drop FOREIGN key fk_stu_classno;
alter table t_student add CONSTRAINT fk_stu_classno  FOREIGN key (classno) REFERENCES t_class (cno) on update set null on DELETE set null;
UPDATE t_class set cno=5 where cno =3;

-- 策略2和策略三可以混着使用

-- 应用场合：朋友圈删除用级联，单位班级删除用置空

DQL

-- 常见的DQL操作

-- 对整个表查询

-- 显示部分列

-- 显示部分行，where子句

-- 显示部分行，部分列

-- 起别名，直接跟在后面，或者as别名，别名有特殊符号可以用单双引号括起来

-- 算数运算符在字段中，也可以起别名，但是如何应对为null的数据？

-- 去重操作,如果有多个字段，则是对组合生效
select DISTINCT job from emp;

-- 排序
select * from emp order by sal; -- 默认升序
select * from emp order by sal asc;
select * from emp order by sal desc;
select * from emp order by sal desc,deptno desc; -- 在一个升序的情况内相同的字段的另一个字段按照降序排列

-- where子句 + 关系运算符 =  >=  >  <  <=  <>  != , 字符串默认不区分大小写
select * from emp where job='TEACHER';
select * from emp where job='teacher';
select * from emp where BINARY job='TEACHER'; -- 想让他区分加binary

-- where子句 + 逻辑运算符  and   BETWEEN and（包含边界）  &&   or  ||  in（10，20）

-- 模糊查询
select * from emp where job like "%A%";
select * from emp where job like "_A%";

-- 关于null的判断
select * from emp where job is null;
select * from emp where job is not null;

-- 小括号的使用，增加可读性

函数

-- 函数：封装了特定功能可以直接使用，提高select的能力
-- 函数没有改变数据自身的值，只是进行加工处理展示新的结果

-- 单行函数和多行函数
-- 除了这五个多行都是单行
max(sal)
min(sal)
count(sal)
sum(sal)
avg(sal)




lower(name)
upper(name)

-- 单行函数
-- 1 字符串函数
select name,length(name),substring(name,2,3) from t_student  -- 从哪里开始，截取几个

-- 2 数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from t_student  -- 会显示整个表
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual伪表，本身就不存在
select abs(-5),ceil(5.3),floor(5.9),round(3.14); -- 如果没有where条件可以不写from
select abs(-5) 绝对值,ceil(5.3),floor(5.9),round(3.14); -- 也可以取别名
select 10/3,10%3,mod(10,3);

-- 3 日期与时间函数  2023-03-14	2023-03-14	17:58:52
select CURRENT_DATE,CURDATE(),CURTIME();
SELECT now(),SYSDATE(),SLEEP(3),now(),SYSDATE();  
-- 当前时间和函数执行时间 2023-03-14 19:42:26	2023-03-14 19:42:26	0	2023-03-14 19:42:26	2023-03-14 19:42:29

-- 4 流程函数
select empno,sal if(sal>=2250,"gaoxin","dixin") as grand from emp;
select empno,sal,comm,sal+ifnull(comm,0) from emp;  -- 如果comm为null则取值0
select nullif(1,1),nullif(1,2); -- null 	1 如果相等和不相等
select job,
case job
	when "A" then "a"
	when "B" then "b"
	else "其他"
end "岗位"，-- case等值判断
sal from emp;

select job,
case job
	when job>1 then "a"
	when job<1 then "b"
	else "D"
end "岗位"，-- case区间判断
sal from emp;

-- 5 json函数

-- 6 其他函数

select DATABASE(),user(),version();


-- 多行函数在计算的时候会自动忽略null值
select count(*) from emp;
select count(1) from emp;

查询总结

-- 字段和多行函数不可以同时使用
SELECT num,AVG(sal) from emp  -- x  多对1
SELECT num,AVG(sal) from emp GROUP BY deptno;

SELECT num,AVG(sal) from emp GROUP BY deptno having avg(sal)>2000; -- 用于再一次过滤

-- 需要排除某一个字段，可以用where在分组前进行过滤，也可以用having在分组后进行过滤


SELECT column,group_function(COLUMN)
from table
[where condition]
[GROUP BY]
[HAVING]
[ORDER BY]

-- from where group select having group