【MySQL学习

MySQL笔记

一.什么是数据库？

简而言之就是存储数据的仓库，数据库（dataBase）是按照数据结构来组织、存储和管理数据的仓库，每个数据都有一个或多个不同的API用于创建，访问，管理、搜索与复制。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

1, 数据库操作

1.创建数据库：create database 库名；
2.使用库：use 库名；
3.删除库：drop database 库名；
4.查看当前版本号：select version();
5.查看当前用户：select user();

2. 数据表操作

创建数据表：

create table 表名(
字段名 字段类型 约束，
字段名 字段类型 约束，。。。
字段名 字段类型 余数
)；

create table my_data(
	sid int primary key,
	sname varchar(20) not null,
	ssex char(2) default '男',
	sbirth date unique
);

删除数据表：

drop table my_data;

约束：primary key 主键 （PK）：值唯一且不能为null；一个表中只能有一个主键，但可以由多列组合成复合主键；
主键在创建表时添加;选取主键的原则是最少原则；ALTER TABLE userroles2 ADD CONSTRAINT pk_ur2 PRIMARY KEY(uid,rid);
auto_increment 自增性；

not null 非空；
default 默认；
unique 唯一；
foreign key 外键（FK）:
添加外键：alter table my_data add constraint fk_01 foreign key(外键) references 主表(主键)；

4.修改表名及结构

//修改表明
alter table tablename rename to newname;

//修改类型
alter table tablename modify colname coltype;

//修改列
alter table tablename change oldcolname newcolname coltype;

//添加一列
alter table tablename add newcolname coltype first|after XXX;//添加到那一列后；

//删除一列
alter table tablename drop colname;

//修改列的位置
alter table tablename modify colname coltype first | after XXX;

5.数据操作

插入数据：

insert into 表名Student values(null,'A','男','1999-04-23');
//插入多组数据
insert into 表名Student values(null,'A','男','1999-04-23'),(null,'B','男','2001-08-02');
//插入表的某些列
insert into 表名Student(sname,sex) values('C','男');

创建表并添加记录，此时表是不存在的：
create table 表名1 as select* from 表名2；
create table 表名1 as select sname,sex from 表名2；
insert into 表名1 select * from 表名2,；此时表1是先建好的；
insert into 表1(sname,sex) select sname,sex from 表2；

6.修改数据

update 表名 set 列=值，列=值，。。。 where 条件；
例如：update employee set empname=‘张三’ where empid = 1;

7. 删除数据

delete from 表名 where 条件;
注意：如果删除父表中的记录，必须看子表中是否有记录引用，没有引用的数据才可以删除，如果delete后面没有条件，就会删除表中的所有记录；
例如：delete from employee where empid > 10;
如何删除外键呢：alter table 表名 drop foreign key 外键名；
delete from 表名 属于 dml语句
truncate table 表名 属于 ddl 功能相当于 delete from 表名 会删除表中的所有的记录
delete 删除的数据可以恢复，不会清除主键的自增量
truncate 删除的数据不能恢复,主键自增从1开始，不能用在有主外键约束的关系的表中

truncate table 与 delete from table的区别:
delete可以通过WHERE语句选择要删除的记录，但执行的速度不快，而且还可以返回被删除的记录数。而truncate table无法删除指定的记录，而且不能返回被删除的记录。但它执行得非常快。
一般MySQL并不确定删除的这6条记录是哪6条，为了更保险，我们可以使用order by 对记录进行排序.
如果要删除表中的所有数据，建议使用truncate table， 尤其是表中有大量的数据，使用truncate table是将表结构重新建一次速度要比使用delete from快很多，而delete from是一行一行的删除，速度很慢。

8.查询数据

select now()查询当前时间
select * from 表1；查询表1的所有数据；

基础查询：

-- 1.获取所有的行与列
SELECT * FROM employees;
-- 2.获取部分列
SELECT first_name,last_name,salary FROM employees;
-- 3.查询列起别名
SELECT last_name AS 姓, first_name AS 名 , salary  AS 工资 FROM employees;
-- 4.查询的表起别名
SELECT e.`last_name` 姓 ,e.`first_name` 名 ,e.`salary` 工资 FROM employees e;
-- 5.查询部分行 where[筛选满足条件的记录]
-- 查询employee_id 为116的员工信息
SELECT * FROM employees WHERE employee_id=116;
-- 查询员工工资在2500到5000之间的员工信息[包含2500和5000] 多个条件 用 and  或者是 or 链接
SELECT * FROM employees WHERE salary>=2500 AND salary<=5000;
-- between and  等价  >=   and   <=
SELECT * FROM employees WHERE salary BETWEEN 2500 AND 5000;

-- 查询工资等于3200 或者  2800 或者为4200的员工信息
SELECT * FROM employees WHERE salary=2800 OR salary=3200 OR salary=4200;
-- in
SELECT * FROM employees WHERE salary IN (2800,3200,4200);

空查询，信息匹配查询

-- 查询邮箱为空的学生信息
SELECT * FROM student WHERE email IS NULL OR email='';
-- 查询邮箱不为空的学生信息
SELECT * FROM student WHERE email IS NOT NULL AND email!='';
-- 模糊查询 like
SELECT * FROM employees WHERE first_name LIKE '%a%';-- 查询first_name包含a
-- 查询first_name a开头
SELECT * FROM employees WHERE first_name LIKE 'a%';
-- 查看first_name a开头的，firstname长度一共为4个长度  %:匹配任意长度  _:一个长度
SELECT * FROM employees WHERE first_name LIKE 'a___';

排序查询和limit 查询

-- 按照工资排序由高到低排序 order by 字段  [asc|desc] asc:升序 desc：降序; 默认是升序；
SELECT * FROM employees ORDER BY salary ASC (DESC);
-- 请按照部门编号从高到低 , 工资由低到高 输出员工信息 [多列排序]
SELECT * FROM employees ORDER BY department_id DESC,salary ;

-- 查看前10条记录  1次
SELECT * FROM employees LIMIT 0,10;
-- 每次看num条记录  n次  limit (n-1)*num,num;-- 规律；
SELECT * FROM employees ORDER BY department_id DESC,salary  LIMIT 3,3;

聚合函数：

五大聚合函数 count:统计个数 sum avg max min 忽略null值

SELECT COUNT(*) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT SUM(salary) FROM employees;
SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary)/COUNT(*) FROM employees;

聚合函数返回的是单行单列的结果，使用聚合后，它的前面不能添加其他的列，所添加的列必须存在于group by 子句中；

-- 统计每个部门的人数，并按照人数从高到低排序
SELECT department_id,COUNT(*) FROM employees GROUP BY department_id ORDER BY COUNT(*) DESC;

having与where区别：where过滤筛选满足条件的记录；having是对分组后的记录再次进行过滤筛选；
distinct：去重；去的是整条记录重复，例如：如果表中学号，得分去重，就算分数有重复也是不能去除重复的分数信息；SELECT DISTINCT salary FROM employees ORDER BY salary;

多表查询与子查询

联接查询

分为内联接inner join，左外联接left join，右外联接right join；
1.内联接语法：查找的是两个表相关联的数据；

SELECT * FROM emp,dept WHERE emp.`departid` = dept.`deptid`;
SELECT * FROM emp INNER JOIN dept ON emp.`departid`=dept.`deptid` ;
-- 3个表联接：
SELECT Student.`StudentNo`,Student.`StudentName`,Sub.`SubjectName`,Score.`StudentScore` FROM Student INNER JOIN Score 
ON Student.`StudentNo` = Score.`StudentNo` INNER JOIN Sub ON Sub.`SubjectId` = Score.`SubjectId`;

2.外联接语法：
左外联接：以左表为基准，联接两个表；
右外联接：以右表为基准；

SELECT * FROM emp LEFT JOIN dept ON emp.`departid`=dept.`deptid`;
SELECT emp.*,dept.* FROM dept RIGHT JOIN emp ON emp.`departid`=dept.`deptid`;

3.子查询：

SELECT Score.`StudentNo`,Score.`SubjectId`,Score.`StudentScore`,
(SELECT Student.`StudentName` FROM Student WHERE Student.`StudentNo` = Score.`StudentNo`)Stuname,
(SELECT Sub.`SubjectName` FROM Sub WHERE Sub.`SubjectId` = Score.`SubjectId`)Subname
FROM Score;

any和all，exists和not exists,union和union all

any:tab1 内的数只要有大于tab2内的一个数就能找出；all：则必须该数要大于tab2内所有数才能被找出；

-- 1 5 13  27
SELECT * FROM tb1;
-- 6 14 11 20
SELECT * FROM tb2;
-- 大于任意一个数
SELECT num1 FROM tb1 WHERE num1 > ANY (SELECT num2 FROM tb2);
select num1 from tab1 where nu1 > all(select num2 from tab2);

当exists内的子查询有结数据返回，则进行前面的查询，若是内部没有数据返回，则前面查询的没有结果；
not exists 刚好相反；

-- exists
SELECT * FROM tb1 WHERE EXISTS (SELECT * FROM tb2 WHERE  num2>50);
-- not exists
SELECT * FROM tb1 WHERE NOT EXISTS (SELECT * FROM tb2 WHERE  num2>50);

union ：将两个表链接并去重，但注意两个表的列要匹配；
union all :将两个表做并集链接查询；

-- 合并查询的结果
SELECT * FROM tb1 UNION SELECT * FROM tb2;

SELECT * FROM tb1 UNION ALL SELECT * FROM tb2;

正则查询：

select * from employee where first_name regexp '^s';//查找以s开头；
select * from employee where first_name regexp 'n$';//查找以n结尾；
select * from employee where first_name
regexp 'j.hn' //查找字符串长度为4，‘.’可以为任何字母；
select * from employee where first_name regexp 'j*hn';//查找以j开头，hn结尾，*是可以用多个任意字符代替；

^.e+ 开头是任意字母，第二个是e的都可以；
^.[ae] 开头任意，第二个字母是a,或e,即[内的字符都行]；
e{2,3};e匹配2到3次，{n,}e匹配n次；

二、查询常用函数

1.数学函数：

abs(x):返回x的绝对值；
PI：返回数学函数π值；
mod(x,y):x对y求余；
sqrt():求平方根；
ceil(x):向上取整；floor(x):向上取整；
rand()：获取随机数；
round(x，n);对x进行四舍五入；精确到小数点后n位；
truncate(x,n):对x进行截断，n是精确位数，0是只保留正数；
sign(x):返回x的符号，正数：1，负数：-1,0:0；
char_length(‘ab’):返回字符串长度；
POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值。
EXP(x)返回e的x乘方后的值
concat(s1,s2,…)连接多个字符串；
CONCAT_WS(’-’,‘hello’,‘world’);用‘-’将字符串连接起来；
INSERT(‘helloworld’,6,5,‘世界’)：替换函数，从第6个位置开始，5个长度，替换成世界；
LEFT(‘hellowrold’,5);-- 字符串截取，从左边截取5个长度，right(‘hellowrold’,5):从右边截取5个长度；
rpad(‘hellowrold’,18,‘hello’):多出的位置用第二个字符串不全；
trim():去掉字符串的前后空格；ltrim()去掉左边空格，rtirm()去掉右边空格；
replace(‘helloworld’,‘hello’,‘你好’)将第一个字符串中包含的第二个字符串替换成第三个字符串；
lower（str）将字符串转换成小写；upper（str）转成成大写；
SUBSTRING(‘helloworld’,6):从第6个位置截取字符串；SUBSTRING(‘helloworld’,1，5)和MID(‘helloworld’,1,5)：将字符串从1开始截取5个长度
left(s,n),right(s,n):将字符串s从左（右）截取n个长度；
reverse（str）:将字符串进行反转；
locate(‘o’,‘helloworld’)与position(‘o’,'helloworld):找到o在字符串中从左往右第一次出现的下标；
找apple在后面数组中的位置：[‘pear’,‘orange’,‘apple’,‘melon’]
SELECT FIELD(‘apple’,‘pear’,‘orange’,‘apple’,‘melon’);
SELECT FIND_IN_SET(‘apple’,‘pear,orange,apple,melon’);

2.日期函数

– 系统当前时间
SELECT NOW();
– 系统当前时间【年月日】
SELECT CURDATE(); – 年月日
SELECT CURRENT_DATE(); – 年月日
SELECT CURRENT_TIME; – 时分秒
SELECT CURTIME(); – 时分秒
– adddate()加上天数
SELECT ADDDATE(NOW(),40);
– 加上年数；
SELECT ADDDATE(NOW(),INTERVAL 40 YEAR);
– 加上秒,最大到59
SELECT ADDTIME(NOW(),59),NOW();
– 两个时间差的天数
SELECT DATEDIFF(NOW(),‘2020-5-8’);
– 格式日期转换；
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d’);
– 当前时间减去后一个时间；
SELECT DATE_SUB(NOW(),INTERVAL 20 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 20 YEAR);
– 返回挡球时间的年份，月份或日期；
SELECT EXTRACT(MONTH9（year或day） FROM NOW());

%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

if: 和三目运算符相似； select salary if(salary > 2000,‘高工资’,‘低工资’) from employees;
ifnull :判空，当ID为空时 设置为字符串，不为空输出ID； select manager_id,ifnull(manager_id,‘无上级领导’) from employees;

等值判断：case when
select salary,
case salary when 24000 then ‘高收入’
when 15000 then ‘低收入’
else ‘基本工资’
end
from employees;

三、存储过程与函数

1、创建存储过程

-- 不带参数的过程；
delimiter $$
create procedure demo1()
	begin
		select count(*) from emp;
	end$$
delimiter ;
call demo();
-- 带输出参数的过程；
delimiter $$ 
create procedure domo2(out num int)
	begin
		select count(*) from emp;
	end$$
delimiter ;
call demo(@num);
select @num;
-- 带有输入参数的；
delimiter $$
create procedure demo3(out p_num int ,in p_name char(50))
	begin
		select count(*) into p_num from emp where empname like p_name;
	end$$
delimiter ;
call demo(@num,@name);
select @num;
-- 带有输入输出参数的；
delimiter $$
create procedure demo4(inout num int)
	begin
		select num = num *10;
	end$$
delimiter ;
set @num =10;
call demo4(@num);
select @num;

创建存储函数

delimiter $$
create function f01() returns int
	begin
	declare all_num int;
	select count(*) into all_num from emp;
	return all_num;
	end$$
delimiter ;
-- 调用方式：
select f01();|set @num = f01(); select @num;

触发器

delimiter $$
	create trigger f02() after insert on emp
	for each row begin
	update dept set deptno = deptno+1 where deptid = new.deptid;
	end$$
delimiter ;
delimiter $$
create trigger before delete on emp
for each row
	begin
		update dept set deptno = deptno - 1 where deptid = old.deptid;
	end$$
delimiter ;

循环语句：

-- 第一种循环；
delimiter $$
create procedure sum1(a int)
	begin
		declare p_sum int default 0;
		declare i int default 1;
		while i<=a do
			set p_sum = p_sum +i;
			set i = i+1;
		end while;
	end$$
delimiter ;
-- 调用过程：计算前100项和；
call sum1(100);
-- 第二种循环：
delimiter $$
create procedure sum2(a int)
	begin
		declare p_sum int default 0;
		declare i int default 1;
		loop_name: loop
		if i>a then
			leave loop_name;
		end if;
			set p_sum = p_sum +i;
			set i = i+1;
		end loop;
	end$$
delimiter ;
call sum2(100);