2021-07-19

最新推荐文章于 2024-01-16 17:54:56 发布

YZzzz...

最新推荐文章于 2024-01-16 17:54:56 发布

阅读量268

点赞数 1

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_50713798/article/details/118911271

版权

Mysql全解

##这是一个单行注释
/*
多行注释
多行注释
多行注释
*/
/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
*/
-- 创建数据库表：
create table t_student(
        sno int(6), -- 6显示长度 
        sname varchar(5), -- 5个字符
        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;

整体不能反复运行，第二次整体运行会显示
)
Table ‘p_student’ already exists

接下来
注意事项
int 宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
时间的方式多样 ‘1256-12-23’ “1256/12/23” “1256.12.23”
字符串不区分单引号和双引号
如何写入当前的时间 now() , sysdate() , CURRENT_DATE()
char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。
如果不是全字段插入数据的话，需要加入字段的名字

create table t_student(
        sno int(6), -- 6显示长度 
        sname varchar(5), -- 5个字符
        sex char(1),
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
insert into t_student values (5555,'李四','女',25,'1999.5.6','北航软工','gyugayudg');
insert into t_student values (10010010,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student(sno, sex,age) values (1000,"男",58);
insert into t_student values (10010010,'张三','男',18,now(),'软件1班',"123@126.com");
select * from t_student;//查看表中数据

第三天

UPDATE t_student set sname = '智宝' where enterdate = '2022.5.8';
UPDATE t_student set sno = 6666 WHERE sname = "李四";
delete from t_student where sname = "智宝";
DELETE FROM t_student where sname = "LLLL"; --内容也不区分大小写

新的语句
注意事项
1.关键字，表名，字段名不区分大小写
2.默认情况下，内容不区分大小写
3.删除操作from关键字不可缺少
4.修改，删除数据别忘记加限制条件
在接下来

SELECT * FROM t_student;
-- 修改表的结构
-- 增加一列
alter table t_student add score DOUBLE(5,2);
## 增加一列放在最前面
alter table t_student add score1 double(5,1) first;-- 不能重名
/*
增加一列在sex的后面
*/
alter table t_student add appearance varchar(6) after sex;
-- 删除一列
alter table t_student drop score1;
-- 修改一列
alter table t_student modify score DOUBLE(9,2);-- 修改类型,不改名字
alter table t_student change score score1 double(5,2);-- 名字，类型都写该
-- 删除表
drop table t_student;

后面直接写了
非外键约束
1列级约束

create table t_student2(
        sno int(6) primary key auto_increment, -- 6显示长度 
        sname varchar(5) not null , -- 5个字符 
        sex char(1) default "男" check(sex = '男' || sex = "女"), -- default 为默认约束值 ，check 检查约束
        age int(3) check(age > 17 and age < 50),  
        enterdate date,
        classname varchar(10),
        email varchar(15) unique -- 限制唯一
);


insert into t_student2 values(1 ,"张三","有",19,'2020.5.9','计算机6班','@1720');
insert into t_student values(2 ,"李四","男",19,'2020.5.9','计算机6班','@1721');
insert into t_student values(1,null,"女",20,'2020-8-29','计算机7班','@1721');

2表级约束

create table t_student(
        sno int(6), -- 6显示长度 
        sname varchar(5) not null, -- 5个字符 not null 只能做表级约束
        sex char(1),
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
alter table t_student add constraint pk_stu primary key(sno);
alter table t_student add constraint ck_stu_sex check(sex = '男' || sex = '女');
alter table t_student modify sno int(6) auto_increment;-- 修改自增条件,这里modify前面没有add
alter table t_student add constraint ck_stu_age check(age > 18 and age < 50);
alter table t_student add constraint uq_stu_email unique(email);

desc t_student;-- 注意前面没有table

外键策略

-- 创建附表
create table t_class(
cno int(4) primary key auto_increment,
cname varchar(6) not null,
room char(4)
);
-- 创建子表
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4), -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
				constraint fk_stu_classno foreign key (classno) references t_class(cno)
);
insert into t_class values(null, "鱼鱼","1"),(null,"六六" ,"2"),(null,"住","2");
-- 上面
/*
INSERT into t_student values (1,"lll",3),(null,"gyg",4),(null,"bhb",1);
classno为4 与外键不匹配
*/
select * from t_class;
SELECT * from t_student;
/*
删除二班的学生
*/
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3) ;
-- 策略一：no action 
-- 先把添加外键的对象置为空
update t_student set classno = null where classno = 2;
-- 删除二班的学生
delete from t_class where cno = 2;
drop table t_student; -- 删除两个表
drop table t_class;
-- 策略二 cascade 级联操作：操作主表的时候影响从表的外键信息：
-- 删除外键约束
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束
alter table t_student add constraint fk_stuclassno foreign key (classno) references t_class (cno) on update cascade on delete CASCADE;
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;

查询操作

准备表：

准备四张表：dept(部门表),emp(员工表),salgrade(薪资等级表),bonus(奖金表)



create table DEPT(  
  DEPTNO int(2) not 

null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)  
);  
alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
        
create table EMP  
(  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),  
  MGR      int(4),  
  HIREDATE DATE,  
  SAL      double(7,2),  
  COMM     double(7,2),  
  DEPTNO   int(2)  
);  
alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
        
create table SALGRADE  
(  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
create table BONUS  
(  
  ENAME VARCHAR(10),  
  JOB   VARCHAR(9),  
  SAL   double(7,2),  
  COMM  double(7,2)  
);  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (40, 'OPERATIONS', 'BOSTON');  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (5, 3001, 9999);  
-- 查看表：
select * from dept; 
-- 部门表：dept:department 部分 ，loc - location 位置
select * from emp;
-- 员工表：emp:employee 员工   ,mgr :manager上级领导编号，hiredate 入职日期  firedate 解雇日期 ，common：补助
-- deptno 外键 参考  dept - deptno字段
-- mgr 外键  参考  自身表emp - empno  产生了自关联
select * from salgrade; -- 工资等级表
-- losal - lowsal
-- hisal - highsal
select * from bonus; -- 奖金表

查询操作精简版

select * from emp;-- 简单查询
select empno, job, sal,sal+ifnull(comm,0) "收入" from emp
where (sal + ifnull(comm,0)) >= 2000
order by empno;-- 改名中间用空格隔开，也可以加上as在前面 名字若中间没有空格隔开可以不加""或''
select * from emp where  BINARY job = 'clerk';-- 解决comm为null的问题
select distinct job from emp; --去重
select * from emp order by sal; -- 默认升序
select * from emp order by sal desc;-- 降序
select * from emp order by sal asc; -- 升序
select * from emp order by sal asc ,EMPNO desc; -- asl升序同时empno降序,中间用，隔开；
select * from emp where deptno <> 10;-- 意思是不等于10
select * from emp order by sal asc ,EMPNO desc; -- asl升序同时empno降序,中间用，隔开；
select avg(sal), deptno from emp group by deptno;

**【1】select语句总结
select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

注意：顺序固定，不可以改变顺序

【2】select语句的执行顺序
from–where – group by– select - having- order by**
复杂版

-- 查看emp表：
select * from emp;
-- where子句：将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的符合条件的数据：
-- where 子句 + 关系运算符
select * from emp where deptno = 10;
select * from emp where deptno > 10;
select * from emp where deptno >= 10;
select * from emp where deptno < 10;
select * from emp where deptno <= 10;
select * from emp where deptno <> 10;-- 意思是不等于10
select * from emp where deptno != 10;
select * from emp where job = 'CLERK'; 
select * from emp where job = 'clerk'; -- 默认情况下不区分大小写 
select * from emp where binary job = 'clerk'; -- binary区分大小写
select * from emp where hiredate < '1981-12-25';
-- where 子句 + 逻辑运算符：and 
select * from emp where sal > 1500 and sal < 3000;  -- (1500,3000)
select * from emp where sal > 1500 && sal < 3000; 
select * from emp where sal > 1500 and sal < 3000 order by sal;
select * from emp where sal between 1500 and 3000; -- [1500,3000]
-- where 子句 + 逻辑运算符：or
select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno = 10 || deptno = 20;
select * from emp where deptno in (10,20);
select * from emp where job in ('MANAGER','CLERK','ANALYST');
-- where子句 + 模糊查询：
-- 查询名字中带A的员工  -- %代表任意多个字符 0,1,2，.....
select * from emp where ename like '%A%' ;
-- -任意一个字符
select * from emp where ename like '__A%' ;
-- 关于null的判断：
select * from emp where comm is null;
select * from emp where comm is not null;
-- 小括号的使用  ：因为不同的运算符的优先级别不同，加括号为了可读性
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or  and > or
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500); 
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;
-- 统计各个部门的平均工资 ,只显示平均工资2000以上的  - 分组以后进行二次筛选 having
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
-- 统计各个岗位的平均工资,除了MANAGER
-- 方法1：
select job,avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2：
select job,avg(sal) from emp group by job having job != 'MANAGER' ;
-- where在分组前进行过滤的，having在分组后进行后滤。

– 统计[人数小于4的]部门的平均工资。
select deptno,count(1),avg(sal)
from emp
group by deptno
having count(1) < 4
那么为什么
select deptno,count(1),avg(sal)
from emp
where count(1) < 4
group by deptno;
这个不可以呢
原因是group by 的分组条件必须加在后面
所以只能用having

多表查询

– 查询员工的编号，姓名，部门编号：
select * from emp;
select empno,ename,deptno from emp;
– 查询员工的编号，姓名，部门编号,部门名称：
select * from emp; – 14条记录
select * from dept; – 4条记录
– 多表查询：
– 交叉连接：cross join
select *
from emp
cross join dept; – 14*4 = 56条笛卡尔乘积：没有实际意义，有理论意义
select *
from emp
join dept; – cross 可以省略不写，mysql中可以，oracle中不可以
– 自然连接：natural join
– 优点：自动匹配所有的同名列 ,同名列只展示一次，简单
select *
from emp
natural join dept;
select empno,ename,sal,dname,loc
from emp
natural join dept;
– 缺点：查询字段的时候，没有指定字段所属的数据库表，效率低
– 解决：指定表名：
select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno
from emp
natural join dept;
– 缺点：表名太长
– 解决：表起别名
select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e
natural join dept d;
– 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：
– 解决：内连接 - using子句：
select *
from emp e
inner join dept d – inner可以不写
using(deptno) – 这里不能写natural join了 ,这里是内连接
– using缺点：关联的字段，必须是同名的
– 解决：内连接 - on子句：
select *
from emp e
inner join dept d
on (e.deptno = d.deptno);
– 多表连接查询的类型： 1.交叉连接 cross join 2. 自然连接 natural join
– 3. 内连接 - using子句 4.内连接 - on子句
– 综合看：内连接 - on子句
select *
from emp e
inner join dept d
on (e.deptno = d.deptno)
where sal > 3500;
– 条件：
– 1.筛选条件 where having
– 2.连接条件 on,using,natural
– SQL99语法：筛选条件和连接条件是分开的

外连接

– inner join - on子句：显示的是所有匹配的信息
select *
from emp e
inner join dept d
on e.deptno = d.deptno;
select * from emp;
select * from dept;
– 问题：
– 1.40号部分没有员工，没有显示在查询结果中
– 2.员工scott没有部门，没有显示在查询结果中
– 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据
– 左外连接： left outer join – 左面的那个表的信息，即使不匹配也可以查看出效果
select *
from emp e
left outer join dept d
on e.deptno = d.deptno;
– 右外连接： right outer join – 右面的那个表的信息，即使不匹配也可以查看出效果
select *
from emp e
right outer join dept d
on e.deptno = d.deptno;
– 全外连接 full outer join – 这个语法在mysql中不支持，在oracle中支持 – 展示左，右表全部不匹配的数据
– scott ，40号部门都可以看到
select *
from emp e
full outer join dept d
on e.deptno = d.deptno;
– 解决mysql中不支持全外连接的问题：
select *
from emp e
left outer join dept d
on e.deptno = d.deptno
union – 并集去重效率低
select *
from emp e
right outer join dept d
on e.deptno = d.deptno;
select *
from emp e
left outer join dept d
on e.deptno = d.deptno
union all-- 并集不去重效率高
select *
from emp e
right outer join dept d
on e.deptno = d.deptno;
– mysql中对集合操作支持比较弱，只支持并集操作，交集，差集不支持（oracle中支持）
– outer可以省略不写