Mysql数据库

MySQL数据库

1.常见命令

cmd:

mysql -u root - p

net start mysql

net stop mysql

2.数据类型

数字:int double(7,2)整数和小数最大位数，小数位数

字符:

char(10) 长度固定

varchar(10)长度可变 10为最长长度

日期 :

date(yyyy-MM-dd)

datetime(yyyy-MM-dd HH:mm:ss)

timestamp(yyyyMMddHHmmss)

3.创建表/约束

列级约束、表级约束

create table student(
    -- 主键约束
    sid int(4) primary key auto_increment,
    -- 非空且唯一
    sname varchar(40) not null unique ,
    -- 检查约束
    sex char(1) check (sex in('男','女')),
    birthday date,
    -- 外键约束
    -- 引用的列必须是主键或具有唯一约束
    cid int references classes(classid)
);

4.增删改

show tables;
select * from student;
--添加
insert into student values(9,'刘一水','男','1976-09-11',6);
--给部分列添加
insert into student (sname,sex,cid) values('谢梅','女',5)
--修改
update student set birthday = '1978-01-23' where sid=10;
--删除
delete from student where sid = 10;

5.查询

-- 查询基础
    select ename,jo,sal,deptno from emp;
    select ename,jo,sal,deptno where sal>2000;

SQl操作符

算数运算符

select sal*12 from emp;
select 2+3;
select 11%4 from dual; -- dual是一张虚拟表（可写可不写）
-- div取整数部分 
select 10/3;-- 3.3333
select 10 div 4;-- 3
-- mod和%结果一致

关系运算符

-- !=和<>表示不等于
select * from emp where deptno<>30;
select * from emp where deptno!=30;

逻辑运算符

-- and or not && || not
-- 查询薪资大于2000或工作为CLERK的员工信息
select * from emp where sal>2000 or job='CLERK';
-- XOR 逻辑异或
-- 规则: 有一个为null，结果则为null 
-- 两边值不同，结果为1，两边值相同，结果为0
select 5>3 xor 8<10; -- 结果0
select 5>3 xor null; -- 结果null

基本查询

去重

-- 去除重复行 distinct
-- 查询emp表中工种
select distinct job from emp;
select distinct deptno,job from emp;

别名

select sal*12 年薪 from emp;

-- 查询没有提成的员工信息
-- is null/is not null
select * from emp where comm is null;
-- 计算年薪
-- ifnull(column,0) 如果column不为null，结果就是该值，如果是null，结果为0
select ename 员工姓名,(sal+ifnull(comm,0))*12 年薪 from emp;

模糊查询

-- _ 匹配一个字符
-- % 匹配任意个字符
select * from emp where ename like 'S%';
-- 查询名字第三位为M的员工信息
select * from emp where ename like '__M%';
--查询名字中有M的员工信息
select * from emp where ename like '%M%';

正则匹配

regexp或rlike

-- 查询名字为4个字母的员工
select * from emp where ename regexp '^[A-Z]{4}$';
-- 查询名字以M开头的员工
select * from emp where ename rlike '^M';

in

select * from where ename in('ename1','ename2','ename3','ename4');

区别between A and B

-- 两头都包含
select * from emp where al between 1100 and 3000;

排序

使用order by子句进行排序，升序asc、降序desc。

-- 单列排序
-- 按照薪资升序排列
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，ename;
-- 按照别名排序
select ename 员工姓名,(sal+ifnull(comm,0))*12 年薪 from emp order by 年薪 desc;

复制

--  复制一张表，目标表不存在
creat table emp1 as select * from emp;
select * from emp1;
delete from emp1;
-- delete表还在 drop全删

-- 复制一张表，目标表存在
insert into emp1 select * from emp where sal>2000;
-- 注意：查询数据的列要和原表的列匹配
insert into emp1 select empno,ename,job,sak,deptno from emp where sal>2000;

-- union 对查询结果集的操作
insert into emp1 select * from emp where sal>2000 union
 select * from emp where ename rlike '^M';

常用函数

1.字符函数

lower(char),upper(char)

-- 将字符串转化为小写
select lower('ABC');
select lower(ename) from emp;

length(char)/char_length(char)

-- 返回字符串长度
select ename,length(ename) from emp;

**substr(str,pos,len) **

substring(str,pos,len)

mid(str,pos,len)

下标从1开始，最后一位下标-1

str 源字符串

pos 起始位置

len 截取长度（可省略）

-- 截取字符串
select substr('hello world',3,3)；-- llo
select substr('hello world',-4,3)；-- orl

**replace(str,old,new) **

-- 字符串的替换
select replace('zcripture clazzicz','z','s');

insert(str,pos,len,new)

-- 用新字符串替换一段旧字符串
select insert('hello oracle数据库',7,6,'MySQL');

concat(str1,str2,str3,…,strn)

-- 拼接字符串
select concat('hello','','world');
select concat('你好',ename) from emp;

space(n)

-- 由n个空格拼成的字符串
select concat('***',space(5),'***');

instr(str,sub,str)

-- 字串在原字符串中第一次出现的位置
select instr('abcdefcdrt','cd'); --3

format(x,d)

x——源数字，一般为double类型

d——小数位数，不能省略

-- 保留d位小数，四舍五入取整
select format(3.14159269793,3);-- 3.142

trim(str)

-- 返回源字符串去掉左右两边空格的副本
select concat ('***',trim('  abc  xyz    '),'**');
-- ***abc  xyz**

综合使用

-- 将姓名首字母大写，其余小写
select concat(substr(ename,1,1),lower(substr,2)) from emp;

2.数学函数

mod(m,n)

-- 求模
select mod(11,4);
select 11%4;

floor(n)

ceil（n）

-- 向上取整
select ceil(-2.567);-- -2

rand(n) 返回0-1间随机整数

-- 获取20-50之间的随机整数
select floor(30*rand()+20);-- 可以取到20 取不到50
-- 随机乱序
select * from emp order by rand();

pow(x,y)

-- 求x的y次幂
select pow(2,10); --1024

round(x[,d])

-- 保留d位小数，四舍五入取整
-- d小数位数，可以省略
select round(3.141592);-- 3

truncate(x[,d])

-- truncate(x[,d]) 保留d位小数，舍掉其余部分,不四舍五入
select truncate(3.1415926,4);

3.时间函数

-- 获取当前时间
select now(),sysdate(),current_timestamp();

-- curdate()、current_date获取年月日
select curdate(),current_date();

-- curtime()、current_time、current_time 获取时分秒
select curtime(),current_time(),current_time;

-- adddate(date,days|interval expr unit) 时间计算
-- date_add(date,interval expr unit)
select adddate(now(),10);-- 10天以后
select adddate(now(),interval 80 hour);-- 80小时以后
select adddate(now(),interval '5 8'day_hour);-- 5天8小时以后


-- datediff(date1,date2) 计算日期的差值
select datediff(now(),'2021-06-11');-- 相差天数

-- date(date) 提取年月日
-- time(date) 提取时分秒
select date(now()),time(sysdate());

-- week(date) 提取date在当年的第几周
select week(sysdate());

-- day(date),dayofmonth(now()) 一个月的第几天
select day(now()),dayofmonth(now());

-- dayofweek(date) 星期几
select dayofweek(now());

-- last_day(date) date所在月份的最后一天
select last_day(now());

综合实例

-- 显示入职时间超过38年的员工
select * from emp where adddate(hiredate,interval 38 year)<now();

4.转换函数

字符串和数字可以自动转换

-- date_format(date,format) 将日期类型转换为一定格式的字符串
select date_format(now(),'%Y/%m/%d %H:%i:%s');
select date_format(now(),'%Y/%m/%d %T');

-- str_to_date(str,format) 将时间字符串转换为datetime类型
-- str 要和format的格式保持一致，否则结果为null
select str_to_date('2021-09-15 12:34:27','%Y-%m-%d %T');

5.条件判断函数

-- if(expr,v1,v2) expr为true,结果为v1,expr为false,结果为v2
select ename,sal,if(sal>2500,'高工资','低工资') from emp;

-- ifnull(v1,v2) 如果v1不为null,结果为v1，如果v1为null，结果为v2

6.信息系统函数

-- version();查看系统版本号

-- connection_id();查看当前用户的连接数

-- user();

-- charset(str);

7.加密函数

md5(str);

sha(str);

高级查询

1.聚合函数

普通列不能和聚合函数一起查询

-- max(column) min(column)
select max(sal)最高薪资,min(sal)最低薪资 from emp;

-- count(*) 查询结果的所有行
-- count(column) 查询结果该列不为null的行数
-- count(distinct column) 查询结果该列相异值的数量
select count(*) from emp where sal>2000;
-- 统计有提成的员工的数量
select count(comm) from emp;
-- 查询有多少工种
select count(distinct job) from emp;

-- sum(column) 求和
-- avg(column) 求平均
select sum(sal) 总薪资,round(avg(sal),2) 平均薪资 from emp;

聚合函数除了count()，其余都忽略null值

2.分组查询

-- 分组查询
select cid ,round(avg(score),1) from stuscore group by cid;

-- 多列分组
select stuid,cid,round(avg(score),1) 平均成绩 from stuscore group by stuid,cid;

having 在分组之后进行条件筛选

where—>group by—>having—>order by

where后不能用别名 having可以

3.多表联结查询

多表联查时需要给表起别名，

如果没有给表起别名，使用别名.列名，

如果给表起了别名，必须使用别名.列名，否则会出错。

内联结

-- 两表联查
-- 方法一：
select s.sname,sc.*from student s
 inner join stuscore sc on s.stuid = sc.stuid;

-- 方法二：
select s.sname,sc.* from student s, stuscore sc 
where s.stuid = sc.stuid;

-- 三表联查
-- 方法一：
select s.sname,c.cname,sc.score from student s
  inner join stuscore sc on s.stuid = sc.stuid
  inner join course c on sc.cid = c.cid;

-- 方法二:
select s.sname,c.cname,sc.score from student s,course c,stuscore sc
  where sc.stuid = s.stuid and sc.cid = c.cid;

4.子查询

1.子查询的结果为一行一列，当作一个数值直接使用。

2.子查询是一列多行，使用关键字in。

3.子查询结果为一行多列，加小括号。

4.子查询结果为多行多列，当作一张新表。

-- 查询薪资比SCOTT低的员工信息
select * from emp where sal<
   (select sal from emp where ename='SCOTT');

5.合并查询

保证多个查询结果集的列必须一致

union: 合并去重

union all: 合并不去重

6.分页查询

limit start,count

satrt 起始行，从0开始，可以省略，默认为0

count 查询的行数

-- 查询6-10行
select * from emp limit 5,5;

7.SQL语句导入，导出

-- 导出数据
select * from emp where sal>2000 into outfile '路径';
-- 文件导入
load data infile '路径'into table emp1;

explain:分析sql语句查询计划。

6.数据库对象

1.索引

创建

-- 查看表索引
show keys from emp1;

-- 1.给job添加普通索引
create index job_index on emp1(job);

-- 2.给ename添加唯一索引
-- 某个列添加了唯一约束，系统会自动创建唯一索引
-- 如果该列没有唯一约束，可以单独添加唯一索引
create unique index ename_index on emp1(ename);
alter table emp1 add unique(ename);

-- 3.主键索引
-- 给列添加主键约束(primary key)时，系统会自动创建主键索引，不必重复设置

-- 4.全文索引(fulltext)，只适用于char、vachar，text类型的列
creat fulltext index job_fulltext on emp1(job);

-- 5.组合索引,一次给多个列添加索引

添加索引时，如何选择具体哪一个索引：

1.该列是主键，自动会添加主键索引

2.如果该列值必须唯一，考虑添加唯一索引

3.如果该列是字符类类型，考虑添加全文索引

4.添加普通索引或组合索引

2.视图

视图和表的区别：

• 表需要占用磁盘空间，视图不需要
• 视图不能添加索引
• 使用视图可以简化查询
• 视图有利于提高安全性，比如不同用户查看不同的视图

-- view(视图)
-- 创建视图，各个部门的平均薪资
creat or replace view view_avgsal as select deptno,round(avg(sal),2)avgsal from emp group by deptno;
-- 可以像普通表一样查询视图
select * from view_avgsal;
-- 修改视图,修改视图对应的sql语句
alter view view_avgsal as select * from emp;
-- 修改视图名
rename table view_avgsal to avgsal;

3.事务

由一组相关的DML(增删改)语句组成，该组的DML语句要么全部执行成功，要么都不执行。

事务四大特性(ACID)：

• 原子性：不可分割
• 一致性：事务开始时，数据库中数据一致，事务结束，数据也一致。
• 隔离性：多个并发的事务可以独立运行，而不能互相干扰。
• 持久性：事务提交后，数据库的变化就会永久的保留下来。

-- 事务(Transaction)
-- 事务执行完之后一定要提交，才能永久保存在数据库中
-- rollback,回退到最近的一次提交之后
update emp1 set sal = 2000 where empno = 7369;
-- 手动提交
commit;
-- 设置手动提交
set autocommit=0;
select @@autocommit;
select * from emp1;

隔离的四个级别：

• READ UNCOMMITTED:读取未提交，可能产生脏读，不可重复读，幻读
• READ COMMITTED：读取提交，可能产生不可重复读，幻读
• REPEATABLE-READ：可重复读，默认的隔离级别，解决了幻读
• SERIALIZABLE：可串行化 ，对表加锁，不允许多个事务同时操作同一张表，效率较低

read committed 读取提交，可能产生不可重复读

不可重复读：同一个事务中，相同的查询语句查询到的结果不同

锁的分类：

MySQL分为三类：全局锁，表级锁和行级锁

7.MySQL编程基础

变量

1.局部变量

定义在存储过程，函数，触发器begin/end语句块之间

declare v_name varchar(15) default '';

2.用户变量

不需要声明，直接使用

①set @num=1 或 set @num:=1

set @num=8;
set @num:=10;

②select @num:=18 或 select @num:=列 from 表名

注意：select可以直接查询变量的值

set可以使用=或:=，但是select必须用:=赋值

select @num:=100;
select @num:=sal from emp where ename = 'SCOTT';

3.全局变量

show variables like '%autocommit%';

4.会话变量

MySQL会将当前所有全局变量的值复制一份

-- 查看所有会话变量
show session variables;
-- 查看一个会话变量
sekect @@session var_name

语句

1.set

set 变量名 = 表达式;

set 变量名 := 表达式;

set 变量名1 = 表达式1,变量名2=表达式2;

2.select

select 表达式 into 变量 from 表名 --不产生结果集

select 会话变量:=表达式 --产生结果集

3.条件控制语句

游标

-- 用游标提取emp表中7788雇员的名称和职务
creat procedure findempbyempno()
begin
  -- 编码部分
  -- 定义变量
  declare V_name varchar(15);
  declare V_job varchar(10);
  -- 声明游标
  declare cur_emp cursor for select ename,job from emp where empno=7788;
  -- 打开游标
  open cur_emp;
  -- 提取数据
  fetch cur_emp into v_name,v_job;
  select concat('员工姓名：',v_name,'工作：',v_job);
  -- 关闭游标
  close cur_emp;
end;
 -- 调用存储过程
 call findempbyempno();
 
 
 -- 顺序显示emp表中雇员的编号、姓名、薪资、部门编号
 creat procedure pro_emp()
 begin
   -- 定义变量
   declare done int default 0;
   declare eno int;
   declare v_name varchar(15);
   declare v_sal decimal(7,2);
   declare v_deptno int unsigned;
   declare result varchar(2000) default '';
   declare cur_emp cursor for select empno,ename,sal,deptno from emp;
   --指定游标结束的标志
   declare continue handler for not found set done = 1;
   open cur_emp;
   -- 提取数据
   while done!=1 do
      fetch cur_emp into eno,v_name,v_sal,v_deptno;
      if done!=1 then
      set result = concat(result,'\r\n','编号：',eno,',姓名：',v_name,',薪资：',v_sal,',部门编号：',v_id);
   end if;
   end while;
   select result;
   -- 关闭游标
   close cur_emp;
end;
call pro_emp();

8.存储过程、函数和触发器

-- 存储过程 procedure
-- 使用过程输出emp表中的雇员编号为7788的姓名
create procedure pro_7788()
begin
   -- 定义变量
   declare v_name varchar(15);
   -- 将查询的值赋值给v_name
   select ename into v_name from emp where empno=7788;
   -- 输出变量的值
   select concat('编号为7788的雇员姓名：',v_name);
end;
-- 调用存储过程
call pro_7788;


-- 使用过程实现九九乘法表
create procedure pro_99()
begin
    declare i int default 1;
    declare j int default 1;
    declare result varchar(2000) default '';
    n1:loop
        set j=1;
        n2:loop
            if j<i then
                set result=concat(result,j,'x',i,'=',j*i,' ');
            else set result=concat(result,j,'x',i,'=',j*i);
            end if;
            set j=j+1;
            if j>i then leave n2;
            end if;
        end loop;
        if i<9 then
            set result=concat(result,'\r\n');
        end if;
        set i=i+1;
        if i>9 then leave n1;
        end if;
    end loop;
    select result;
end;
call pro_99();
drop procedure pro_99;

带参数的存储过程

存储过程的参数：

1.in 默认参数类型 可以省略

-- 创建一个过程，以雇员号为参数查询雇员的姓名和职位
creat procedure pro_findempbyo(eno int)
begin
   -- 定义变量
   declare v_name varchar(15);
   declare v_job varchar(10);
   select ename,job into v_name,v_job from emp where emp=eno;
   select v_name,v-job;
end;
call procedure pro_findempbyo(7369);

2.out

create procedure pro_findsalbyno(eno int,out vsal decimal(7,2))
begin
    select sal into vsal from emp where empno = eno;
end;
-- 调用
set @sal=0;
call pro_findsalbyno(7788,@sal);
select round(7788,@sal);

3.inout

-- 创建两个变量的值进行交换
create procedure pro_swap(inout num1 int,inout num2 int)
begin
    declare temp int default 0;
    -- 交换
    set temp = num1;
    set num1 = num2;
    set num2 = temp;
end;
set @n1=10;
set @n2=20;
call pro_swap(@n1,@n2);
select @n1,@n2;

自定义函数

-- 函数 function
-- 创建一个函数，以雇员号查询雇员的姓名
create function fun_findnamebyno(eno int)
returns varchar(15)
begin
    declare v_name varchar(15);
    select ename into v_name from emp where empno=eno;
    return v_name;
end;

存储过程和函数区别：

1.函数必须有声明返回值类型，存储过程没有。

2.函数必须返回一个和声明类型一致的值，存储过程无需返回任何值。

3.调用方式不同，函数使用select，存储过程使用call进行调用。

4.存储过程的参数有in，out，inout三种模式，函数一般只有in。

触发器

-- 触发器tigger
-- 在studet表建立触发器，在向student表中添加数据时触发
-- 将时间和学生信息存入logs表中
create tigger tri_student after insert
on student for each row
begin
-- 自动给logs表中添加一条数据
-- new 表示即将要添加的行
insert into logs(msg,createtime) values(new.sname,now());
end;
-- 触发触发器
insert into student values(6,'谢广坤');



-- 限制更改的薪资不能小于原来的薪资
create tigger tri_update_sal before update
on emp1 for each row
begin
-- new、old
-- 即将要修改的薪资 new.sal
-- 原来的薪资 用new.empno查询的薪资
-- declare v_sal decimal(7,2);
-- select sal into v_sal from emp1 where empno=new.empno;
-- 判断即将要要求的薪资是否小于原来的薪资
if new.sal<old.sal then
    -- 发起系统异常
    signal sqlstate '45008' set message_text='黑心老板！不允许降薪';
    end if;
end;

-- 限制每个部门下的员工数量不能超过5位
create tigger tri_deptcount after insert
on emp1 for each row
begin
declare v_count int default 0;
--查询即将要添加数据的部门的人数
select count(*) into v_count from emp1 where deptno=new.deptno;
-- 如果该部门人数大于等于5，发起异常
if v_count>=5 then
    signal sqlstate '450082' set message_text='部门已满员'; 
    end if;
end;