MySQL学习

最新推荐文章于 2023-04-03 17:26:00 发布

爱一半码一半

最新推荐文章于 2023-04-03 17:26:00 发布

阅读量311

点赞数 2

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_41650354/article/details/98884012

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

https://www.cnblogs.com/developer_chan/p/9205401.html 摘自作者：posted

https://www.cnblogs.com/developer_chan/p/9158079.html mysql面试题

查看数据库

show databases;

使用数据库

use 数据库名；

创建表

CREATE TABLE employee (
	employee_id INT (6) PRIMARY KEY auto_increment COMMENT '员工编号',
	first_name VARCHAR (20) NOT NULL COMMENT '名',
	last_name VARCHAR (25) NOT NULL COMMENT '性',
	email VARCHAR (25) NOT NULL COMMENT '邮箱',
	phone_number VARCHAR (20) NOT NULL COMMENT '电话号码',
	job_id VARCHAR (10) NOT NULL COMMENT '工种编号',
	salary DOUBLE (10, 2) NOT NULL COMMENT '月薪',
	commission_pct DOUBLE (4, 2) NOT NULL COMMENT '奖金率',
	manager_id INT (6) NOT NULL COMMENT '上级领导的员工编号',
	department_id INT (4) NOT NULL COMMENT '部门编号',
	hiredate datetime NOT NULL COMMENT '入职时间'
)ENGINE=InnoDB  DEFAULT CHARSET=uft8 COMMENT = '员工表';

# -----------------------------
CREATE TABLE departments (
	department_id INT (4) PRIMARY KEY auto_increment COMMENT '部门编号',
	department_name VARCHAR (3) NOT NULL COMMENT '部门名称',
	manager_id INT (6) NOT NULL COMMENT '部门领导的员工编号',
	location_id INT (4) NOT NULL COMMENT '位置编号'
) COMMENT '部门表' -- ---------------------------
CREATE TABLE locations (
	location_id INT (11) PRIMARY KEY auto_increment COMMENT '位置编号',
	stree_address VARCHAR (12) NOT NULL COMMENT '街道',
	postal_code VARCHAR (12) NOT NULL COMMENT '邮编',
	city VARCHAR (30) NOT NULL COMMENT '城市',
	state_province VARCHAR (25) NOT NULL COMMENT '州/省',
	country_id VARCHAR (2) NOT NULL COMMENT '国家编号'
) -- ------------
CREATE TABLE jobs (
	job_id VARCHAR (10) PRIMARY KEY COMMENT '工种编号',
	job_title VARCHAR (35) NOT NULL COMMENT '工种名称',
	min_salary INT (6) NOT NULL COMMENT '最低工资',
	max_salary INT (6) NOT NULL COMMENT '最高工资'
);

查询表

SELECT
	employee_id,
	first_name,
	last_name,
	email,
	phone_number,
	job_id,
	salary,
	commission_pct,
	manager_id,
	department_id,
	hiredate
FROM
	employee

查询常量值

select 100; -> 100
select 'joken'; -> joken

查询表达式

select 100%98; ->2
select 100/98; -> 1.0204

查询函数

select VERSION(); ->8.0.18

起别名

SELECT
	first_name as 姓,
	last_name as 名,
	email  邮箱
FROM
	employee

去重

select DISTINCT 去重多少字段就写多少 from 表名字
去掉姓和名重复的员工
```
SELECT DISTINCT
	first_name as 姓,
	last_name as 名

FROM
	employee
```

显示表结构

desc departments

CONCAT

把查询的字段拼接到一起
显示出表job_grades的全部列，各个列之间用逗号连接，列头显示OUT_PUT
select CONCAT(grade_level,',',lowest_sal,',',highest_sal) as OUT_PUT from job_grades
concat(char1,char2,char3)；当char1或char2、char3为null，则拼接之后的字符串为null，若为''，则不受影响。
当为NULL的解决方式 ↓

IFNULL

IFNULL(字段, 0)

查询有奖金的员工工资

# 查询奖金为null的员工工资
select salary from employee where salary is null

# 查询奖金不为null的员工工资
select salary from employee where salary is not null

like模糊查询

_下滑线占一个位置
当用特殊字符查询时需要转义 “\” 或者使用"_Q_" ESCAPE 'Q'

查询员工名中第二个字符为"_"的员工名

select last_name from employee where last_name like '_Q_%' ESCAPE 'Q'

like查询不出来值为NULL的

SELECT * FROM job_grades
A	1000	2999
B	3000	5999
C	6000	9999
D	10000	14999
E	15000	24999
F	25000	40000
G		NULL	NULL			
NULL	NULL	NULL						
NULL	NULL	NULL				

select * from job_grades where grade_level like '%%';
A	1000	2999
B	3000	5999
C	6000	9999
D	10000	14999
E	15000	24999
F	25000	40000
G	NULL	NULL	

select * from job_grades where grade_level like '%%' AND lowest_sal like '%%' AND highest_sal like '%%';
A	1000	2999
B	3000	5999
C	6000	9999
D	10000	14999
E	15000	24999
F	25000	40000

select * from job_grades where grade_level like '%%' or lowest_sal like '%%' or highest_sal like '%%';
A	1000	2999
B	3000	5999
C	6000	9999
D	10000	14999
E	15000	24999
F	25000	40000
G	NULL	NULL

between and

在什么之间
not between and

in

比使用or更简单
in列表的值必须一致

排序

asc代表的是升序，desc代表的是降序，如果不写默认是升序
order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
order by 子句一般是放在查询语句的最后面，limit子句除外

常见函数

类似于java:将一组逻辑语句封装到方法体中，对外暴露方法名。好处：1、隐藏了实现细节 2、提高代码的重用性
调用: select 函数(实参列表) from 表
单行函数
如： concat、 length、 ifnull
分组函数。做统计使用,又称为统计函数、聚合函数、组函数。

字符函数

# 1、字符长度, 一个汉字3个字节长度
select LENGTH('小柯');

# 2、concat 拼接字符串
# 3、upper、lower 
select UPPER('asdfAAA');
select LOWER('asdASD');
# 示例: 将姓变大写, 名变小写, 然后拼接
select CONCAT(UPPER(first_name),LOWER(last_name)) 姓名 from employee; //STEVENk_ing

#substr、 substring 索引是从1开始的,两个一样的效果
#截取从指定索引处后面所有字符
select SUBSTR('啦啦啦德玛西亚', 4) as la;  //德玛西亚
#截取从指定索引处指定字符长度的字符
select SUBSTR('啦啦啦德玛西亚', 2, 4) as la; //啦啦德玛
#示例: 姓 中首字符大写, 其他字符小写然后用_拼接,显示出来。
select CONCAT(UPPER(SUBSTR(last_name,1,1)),'_', LOWER(SUBSTR(first_name, 2))) 姓 from employee;  //C_ohn

# 5、instr 返回子串第一次出现的索引,如果找不到返回0
select INSTR('啦啦啦德玛西亚', '啦') as la; // 1

# 6、 trim去前后空格
select LENGTH(TRIM('    德玛西亚   ')) as la; // 12
select LENGTH(TRIM('a' from 'aaa德玛aa西亚aaa')) as la; // 14

# 7、 lpad用指定的字符实现左填充指定长度
select LPAD('德玛西亚',5,6) as la; // 6德玛西亚
# 8、 rpad用指定的字符实现有填充指定长度
select RPAD('德玛西亚',5,6) as la; // 德玛西亚6
#当字符长度不够，只截取对应字符
select RPAD('德玛西亚',3,6) as la; //德玛西

# 9、replace替换
select REPLACE('啦啦啦德玛西亚', '啦', '嘻') as la; //嘻嘻嘻德玛西亚

数学函数

# round四舍五入
select ROUND(-1.55);  //-2

#ceil 向上取整
select CEIL(5.2); // 6

#floor
select FLOOR(4.32); //4

#truncate截断
select TRUNCATE(1.6578, 3); //1.657
# mod取余 相当于 10%3
#取余算法 a-a/b*b -> a/b的整数
select MOD(10, 3) //1

日期


select YEAR(now()) as 年, MONTH(NOW()) 月, DAY(NOW()) 日, HOUR(NOW()) 小时, MINUTE(NOW()) 分钟, second(NOW()) 秒;
-- 2020	1	18	9	16	27
select YEAR('2000-01-02') as 年,YEAR(hiredate) as 年, MONTH(hiredate) 月, DAY(hiredate) 日, HOUR(hiredate) 小时, MINUTE(hiredate) 分钟, second(hiredate) 秒, DATE(hiredate) as 时间 FROM employee;
-- 2000	2020	1	17	15	54	55	2020-01-17

#字符转化成指定格式的日期
select STR_TO_DATE('09-18-1995','%m-%d-%Y') 日期; -- 1995-09-18
#将日期转化为字符
select DATE_FORMAT(now(),'%Y年%m月%d日') as 字符; -- 2020年01月18日
#示例：查询有奖金的员工们和入职日期
select first_name, last_name, DATE_FORMAT(hiredate, '%Y年%m月%d日 %H时%i分%s秒') 入职日期 FROM employee where salary is not null; -- 小	柯	2020年01月17日 15时54分55秒
select first_name, last_name, DATE_FORMAT(hiredate, '%y年%c月%d日 %h时%i分%s秒') 入职日期 FROM employee where salary is not null; -- 小	柯	20年1月17日 03时54分55秒

其他函数

select VERSION();  //8.0.18
select DATABASE(); //xiaoke
select USER(); //root@113.118.100.105

流程控制函数 case when

--  流程控制函数
select IF(10 < 5, '大', '小') 判断;

/**
mysql:
  case 要判断的字段或表达式
  when 常量1 then 要显示的值1或者语句
  when 常量2 then 要显示的值2或者语句
  ......
  else  要显示的值2或者语句
	end

相当于java:
  switch(要判断的字段或表达式){
  case 常量1 : 要显示的值1或者语句; break;
	......
	DEFAULT : 要显示的值1或者语句; break;
}
示例：查询员工的工资，要求：
部门号=30，显示的工资高为1.1倍，
部门号=40，显示的工资高为1.2倍，
部门号=50，显示的工资高为1.3倍，
其他部门，显示的工资高为原工资，
*/
select salary 原工资, department_id,
CASE department_id
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
WHEN 50 THEN salary * 1.3
ELSE salary
END   新工资
FROM
employee

/**
mysql:
CASE
WHEN 条件1 then  要显示的值1或语句1
WHEN 条件2 then  要显示的值2或语句2
......
else 要显示的值n或语句n
END

相当于java中
if(条件1){
要显示的值1或语句1;
}else if(条件2){
要显示的或语句2;
}else{
要显示的值n或语句n;
}
示例：查询员工的工资级别情况
如果工资>20000, 显示A级别
如果工资>15000, 显示B级别
如果工资>10000, 显示C级别
否则：显示D级别
*/
SELECT salary, 
CASE 
WHEN salary> 20000 THEN 'A'
WHEN salary> 15000 THEN 'B'
WHEN salary> 10000 THEN 'C'
ELSE 'D'
END  工资级别
from employee


备注： else可以省略

聚合函数

-- 聚合函数
select SUM(salary) 总数, ROUND(AVG(salary), 2) 平均数, MAX(salary) 最大值, MIN(salary) 最小值, COUNT(salary) 个数 FROM employee

-- 查询当前时间与1995-1-1 之间相差多少天
select DATEDIFF(NOW(),'1995-08-14')

sum、avg一般用于处理数值型，max、min、count可以处理任何类型(因为字符型可以排序)
以上分组函数都忽略null值，所以不能使用count(字段) 做统计
可以和distinct搭配实现去重的运算
一般使用count(0)或者count(*)做统计

和分组函数一同查询的字段要求是 group by 后的字段

-- 查询所有部门员工个数大于2的部门
-- ① 查询所有的部门
select  department_id FROM employee GROUP BY department_id;
-- ② 查询那个部门的员工个数大于2
select count(0), department_id FROM employee GROUP BY department_id HAVING count(0) > 2;

-- 查询领导编号大于102的每个顶到手下的最低工资高i大于5000的领导编号是哪个，以及其最低工资
-- 先查询每个领导手下员工的最低工资
select MIN(salary), manager_id from employee GROUP BY manager_id;
-- 在加入查询条件，条件在原表，就在where后面，否则在having后面
select manager_id, MIN(salary) 最低工资 from employee where  manager_id > 200 GROUP BY manager_id HAVING 最低工资 > 5000

分组查询的筛选条件分为两类
分组前筛选原始表跟where
分组后筛选分组后的结果集跟having

子查询

-- 子查询
-- 位置 
-- select后面 
-- FROM后面
-- where或HAVING后面
-- exiets后面
-- 案例 返回job_id与145号员工相同, salary比Marlow员工多的员工 姓名，job_id和工资

-- ① 141号员工的job_id
select job_id from employee where manager_id = 145 
-- ② 143号员工的salary
select salary from employee where last_name = 'Marlow'
-- ②导入①

select last_name, job_id, salary from employee WHERE job_id = (
select  job_id from employee where manager_id = 145  #使用等于这里的值必须唯一
) and salary > (select salary from employee where last_name = 'Marlow')



# 示例：查询员工编号最小并且工资最高的员工信息
select * from employee e 
where  e.manager_id = ( 
select MIN(manager_id) FROM employee
) and e.salary = (
 select MAX(salary) from employee
)
-- 换一种写法
SELECT * from employee e
where (e.employee_id, e.salary) = (
select MIN(employee_id), MAX(salary) FROM employee
)


-- 查询部门id和员工表中部门id一样的部门名称
select department_name FROM departments d where EXISTS (
C where e.department_id = d.department_id)

select department_name FROM departments d where d.department_id in (
select department_id from employee
)
-- 结论： exists 的查询都可以用in来代替

-- 查询各部门中工资比本部门平均工资高得员工的员工号、姓名和工资。
-- 各部门的平均工资
select department_id, AVG(salary) from employee
GROUP BY department_id
-- 
select e.manager_id, e.last_name, e.salary,d.salary 平均工资  FROM employee e
LEFT JOIN (
select department_id, AVG(salary) salary from employee GROUP BY department_id
) d ON e.department_id = d.department_id
where e.salary > d.salary

sql查询语法：

select
列名 
from 表1 别名
left|right|full [outer] join 表2 别名 on 链接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句

UNION和UNION ALL

要求:
1、要求多条查询语句的查询列数是一致的。
2、要求多条查询语句的查询的每一列的类型和顺序最好一致。
3、union关键字默认去重, 如果使用union all 可以包含重复项。

插入语句

表列的增删改查

通用：alter table 表名 add|drop|change|modify column 字段类型。change是改字段，modify改字段类型。

#创建表
create table copy01(
c_id int(10) PRIMARY key,
c_name VARCHAR(20)
);

DESC copy01;
#添加新列
alter table copy01 ADD COLUMN c_add VARCHAR(10);
#修改列
alter table copy01 CHANGE COLUMN c_add c_data TIMESTAMP;
#修改列leix
alter table copy01 MODIFY COLUMN c_data datetime;
#删除列
alter table copy01 DROP COLUMN c_data; 

#表的复制
#1、复制表的结构
create table copy02 like copy01;
DESC copy02;

#2、复制表的结构+数据
create table copy03 
select * from copy02;

#3、仅仅复制某些字段，不需要值
create table copy04
select c_id FROM copy03 
where 0;

#删除表
drop table if EXISTS copy04;

小数：

char和varchar

写法	M的意思	特点	空间的耗费	效率	应用	范围
char(M)	最大的字符	固定长度的字符	比较耗费	高	如：存男女	-128~+127
varchar(M)	最大的字符	可变长度的字符	比较节省	低	如:存地址	65535

查看约束和索引

desc 表名；
show index from 表名；

创建表带约束、外键

修改约束

通用：alter table 表名 add|modify column 字段类型约束

事务(ACID)

A:原子性(Atomicity)

事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。

B:一致性(Consistency)

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

C:隔离性(Isolation)

一个事务的执行不能被其他事务干扰。

D:持续性/永久性(Durability)

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

变量的声明、复制、查看

#声明并初始化
SET @name='xiaoke';
SET @name='wang';
SET @count=11;
#赋值
select COUNT(0) INTO @emp_count FROM employee;
#查看
select @name;
SELECT @count;
select @emp_count;

存储过程

含义：

一组预先编译好的SQL语句的集合，理解成批处理语句。

作用

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的链接次数，提高了效率。

创建语法

#存储过程语法
DELIMITER 某标识符号
create procedure 存储过程名(参数列表)
begin
    存储过程体(一体合法的SQL语句)
end 某标识符号

注意：
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
IN stuname varchar(20)

参数模式：
IN: 输入参数
OUT: 输出参数
INOUT: 即可以输入也可以输出

2、存储过程体仅仅只有一句话,begin end 可以省略。
3、存储过程体中的每条SQL语句的结尾要求必须加分号。

二、调用语法
 CALL 存储过程名(实参列表);


-- 示例
#创建函数
CREATE PROCEDURE myp1()
begin 
 insert into copy03 (c_id, c_name, sex) VALUES(7, '7', '7'),(8, '8', '8'),(9, '9', '9');
END 

DELIMITER $
CREATE PROCEDURE myp2()
begin 
 insert into copy03 (c_id, c_name, sex) VALUES(10, '10', '10'),(11, '11', '11'),(22, '22', '22');
END  $
#运行函数
CALL myp1() 
CALL myp2() $

IN的存储方式

-- 示例：创建存储过程实现用户是否登录成功

DELIMITER $
create PROCEDURE myp5(IN username int(20), IN c_password VARCHAR(20))
BEGIN
		DECLARE result INT DEFAULT 0; #声明并初始化
		select COUNT(*) INTO result #赋值
		from copy03 
		where c_id = username AND c_name = c_password;
		SELECT IF(result>0, '成功', '失败') AS '登录'; #这里的select相当于打印
END $

#调用
CALL myp5(8, '8');

# 输入id找名字
--  一个传入值、一个返回值
DELIMITER $
CREATE PROCEDURE myp6(IN id int(20), OUT bouName VARCHAR(20))
BEGIN
   SELECT c.c_name INTO bouName
	 FROM copy03 c
   where c.c_id = id;
END $
#调用
CALL myp6(9, @bName) $
select @bName

-- 多个返回值
DELIMITER $
CREATE PROCEDURE myp7(IN id int(20), OUT bouName VARCHAR(20), OUT xName VARCHAR(20))
BEGIN
   SELECT c.c_name, c.sex INTO bouName,xName
	 FROM copy03 c
   where c.c_id = id;
END $
#调用
CALL myp6(8, @kkk) 
SELECT @kkk
CALL myp7(9, @bName, @cname) 
select @bName
SELECT @cname

--   INOUT
DELIMITER $
CREATE PROCEDURE myp9(INOUT a int(10), INOUT b int(20))
BEGIN
  SET a = a*2;
  SET b = b + 3;
END $

#调用
SET @m = 10;
SET @n = 10;
CALL myp9(@m, @n);
SELECT @m,@n

删除存储过程

drop procedure 存储过程名；

查看存储过程的信息

show create procedure 存储过程名

函数

set global log_bin_trust_function_creators=TRUE; mysql8需要设置这个才可以使用函数


-- 无参
DELIMITER $
CREATE FUNCTION myfunction1() RETURNS INT
BEGIN
   DECLARE c INT DEFAULT 0; #定义变量
   SELECT COUNT(0) INTO c #赋值
   FROM copy03;
   RETURN c;
END $

--
SELECT myfunction()

-- 有参
DELIMITER $
CREATE FUNCTION getfunction(cname VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
  DECLARE ss VARCHAR(20) DEFAULT '';
  SELECT sex INTO ss
  FROM copy03 where c_name = cname;
  RETURN ss;
END $
--
SELECT getfunction('9');

存储过程使用判断

#存储过程判断
#创建存储过程，根据传入的成绩来显示登记，如：90-100显示A 以此为类推。
CREATE PROCEDURE test_case1(IN score INT)
BEGIN 
 CASE
 WHEN score >= 90 and score <= 100 then select 'A';
 WHEN score >=80 THEN SELECT 'B';
 WHEN score >=70  THEN SELECT 'C';
 ELSE SELECT 'D';
 END CASE;
END 


#调用
CALL test_case1(84);

存储函数使用判断


CREATE FUNCTION test_case2(score INT) RETURNS CHAR
BEGIN 
 IF score >= 90 and score <= 100 THEN RETURN 'A';
	ELSEIF score >=80 THEN RETURN 'B';
	ELSEIF score >=70 THEN RETURN 'C';
  ELSE RETURN 'D';
	END IF;
END 
#调用
select test_case2(38);

查看函数

show create function 函数名；

删除函数名

drop function 函数名；

存储函数 - 循环插入

create PROCEDURE pro_while04(IN insertCount INT)
BEGIN
 DECLARE i INT DEFAULT 1;
 while i <= insertCount DO
   INSERT INTO copy01(c_name) VALUES('rose');
   SET i = i + 1 ;
  END WHILE;
END
CALL pro_while04(5);

存储函数 - 循环插入 + 条件判断

-- 批量插入，根据次数插入到admin表中多条记录，如果次数大于20则停止
CREATE PROCEDURE test_while6(IN insertCount INT)
BEGIN
   DECLARE i INT DEFAULT 1;
   a:WHILE i <= insertCount DO
      INSERT INTO copy01(c_name) VALUES(CONCAT('rose',i)); 
      IF i>= 20 THEN LEAVE a;
      END IF;
      SET i =i+1;
    END WHILE a;
END $

mysql备份

为什么备份
- 保存数据的完整性
备份方式
- mysqldump -hIP -uroot -p123456 数据库名表名1 表名2 > /tmp/a.sql
- 定时备份：https://www.cnblogs.com/linkstar/p/6420965.html

地址:https://www.cnblogs.com/developer_chan/p/9231761.html

爱一半码一半

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习

https://www.cnblogs.com/zhuyongzhe/p/7686098.html 摘自作者：posted查看数据库show databases;使用数据库use 数据库名；创建表CREATE TABLE employee ( employee_id INT (6) PRIMARY KEY auto_increment COMMENT '员工编号'...
复制链接

扫一扫