MYSQL

最新推荐文章于 2024-06-28 23:04:57 发布

@zhixun

最新推荐文章于 2024-06-28 23:04:57 发布

阅读量828

点赞数 24

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/Json_xun/article/details/134969238

版权

MYSQL

按年薪的高低显示员工的信息和年薪

SELECT *,salary * 12 * ( 1 + IFNULL(commission_pct,0)) AS 年薪

From employees

order by 年薪 DESC

按姓名的长度显示员工的姓名和工资

SELECT LENGTH(last_name) 姓名

from employees

order by 姓名 DESC

要求先按工资排序，在按员工编号排序

SELECT *

from employees

order by salary ASC,employess_id DESC

选择工资不在7000-12000的员工的姓名和工资，按工资降序

SELECT * FROM employees where salary not between 7000 and 12000 order by salary DESC

查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，在按部门升序

SELECT * FORM employees where email like ‘%e%’ order by LENGTH(email) DESC , department_id ASC

将姓变大写，名变小写，然后拼接,用下划线隔开

SELECT CONCAT(UPPER(last_name), ‘_’ ,LOWER(frist_name)) FROM employees

从第7个字符开始截取 (sql语言中索引是从1开始) 可用substr/substring

SELECT SUBSTR(‘ABCDEFGHI’ , 7) name #查询结果 GHI

若要截取前四个

SELECT SUBSTR(‘ABCDEFGHI’ , 1，4) name #查询结果 ABCD

姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), ‘_’,LOWER(SUBSTR(last_name,2))) NAME FROM employees

instr 放回字串第一次出现的索引，找不到则为0

SELECT INSTR(‘ABCD’,‘ABC’) NAME

TRIM 去前后空格，或者指定去除某个前后值

SELECT TRIM(‘a’ from ‘aaaaaaaaaaaaaa张aa志勋aaaaaaaaaaaa’) as name #显示结果张aa志勋

LPAD 左填充指定长度数字小于字符长度则进行截取

SELECT LPAD(‘HH’,10,‘-’) AS NAME #显示结果 --------HH

RPAD 右填充指定长度数字小于字符长度则进行截取

REPLACE 替换

SELECT REPLACE(‘AAAA张志勋’，‘A’,‘’) NAME # 显示结果张志勋

数学函数

ROUND 四舍五入

SELECT ROUND(1.65); # 显示结果2

CEIL 向上取整返回>=该参数的最小整数

SELECT CEIL(1.003) #结果2

FLOOR 向下取整

SELECT FLOOR(1.99) #结果 1

TRUNCATE 截断

SELECT TRUNCATE(1.65999,1) # 显示结果 1.6

MOD取余

SELECT MOD(10,3) # 10 % 3 显示结果 1 公式 a-a/b*b

日期函数

NOW 返回当前系统日期 + 时间

SELECT NOW()

CURDATE 返回系统日期不包含时间

SELECT CURDATE()

CURTIME 返回时间不包含日期

SELECT CURTIME()

可以获取指定的部分年，月，日，小时，分钟，秒

SELECT YEAR(NOW());

STR_TO_DATE 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE(‘1998-3-2’,‘%Y-%c-%d’)

DATA_FORMATE 将日期转换成字符

SELECT DATA_FORMATE(NOW(), ‘%y年%m月%d日’)

查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMATE(hiredate，‘%m月%d日%y年’)

FROM employees

WHERE commission is not null

其他函数

SELECT VERSION();

SELECT DATABASE();

SELECT USER();

流程控制函数

if 函数实现if - else

SELECT IF(‘10>5’,‘T’,‘F’);

CASE 函数的使用：使用一：switch case

查询员工的工资

部门号=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 AS 新工资

FROM employees

使用二：多重if

查询员工工资并评级

员工工资>15000 评级A

员工工资>10000 <15000评级B

员工工资>8000 < 10000 评级C

SELECT salary

CASE salary

WHEN salary > 15000 THEN ‘A’

WHEN salary > 10000 THEN ‘B’

WHEN salary > 8000 THEN ‘C’

ELSE ‘D’

END AS 工资级别

FROM employees

查询员工表的最大入职时间和最小入职时间的相差天数（DATADIFF）

SELECT DATADIFF(MAX(hiredate),MIN(hiredate)) from employee

查询哪个部门的员工人数>2

SELECT COUNT(*),department_id

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 2

查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

SELECT MIN(salary), manager_id

FROM employees

WHERE manager_id > 102

GROUP BY manager_id

HAVING MIN(salary) > 5000

非等值连接 sql 92

查询员工的工资和工资级别

SELECT salary,grade_level

FROM employees e , job_grade j

WHERE e.salary between j.lowset_sal and j.highest_sal

查询员工和上级名称

SELECT e1.employee_id,e1.last_name,e2.employee_id,e2.last_name,

FROM employees e1 ,employees e2

WHERE e1.manager_id = e2.employee_id

查询名字中包含e的员工名和工种名 sql 99

SELECT last_name,job_title

FROM employee e

INNER JOIN job j

ON e.job_id = j.job_id

WHERE e.last_name LIKE ‘%e%’

查询部门个数>3的城市名和部门个数

SELECT COUNT(*) 部门个数,city

FROM departments d

INNER JOIN loaction l

ON d.location_id = l.location_id

GROUP BY city

HAVING 部门个数 > 3

查询哪个部门的员工个数>3的部门名和员工个数，并降序排序

SELECT departments_name,COUNT(*) 员工个数

FROM departments d

INNER JOIN employees e

ON d.departments_id = e.departments_id

GROUP BY departments_name

HAVING 员工个数 > 3

ORDER BY COUNT(*) DESC

非等值连接

查询员工的工资和工资级别

SELECT salary,grade_level

FROM employees e ,

JOIN job_grade j

ON e.salary between j.lowset_sal and j.highest_sal

左外连接

查询哪个部门没有员工

SELECT d.*,e.employee_id

FROM department d

LEFT JOIN employees e

ON d.department_id = e.department_id

WHERE e.employee_id is NULL

返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id，工资

SELECT employees_name,job_id,salary

from employees

where job_id = (SELECT job_id from employees where employee_id = 141)

and

salary > (SELECT salary from employees where employee_id = 143)

返回工资最少的员工的last_name,job_id,salary

SELECT last_name,job_id,salary

from employees

where salary = (

SELECT MIN(salary) from employees

)

查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)

From employees

GROUP BY department_id

Having MIN(salary)> (

SELECT MIN(salary) from employee where department_id= 50

)

查询员工编号最小并且工资最高的员工信息

SELECT *

FROM employees

WHERE (employee_id,salary)=(

SELECT MIN(employee_id),MAX(salary) from employees

)

创建数据库

CREATE DATABASE IF NOT EXISTS books;

修改库的字符集

ALTER DATABASE book CHARACTER SET utf8

库的删除

DROP DATABASE IF EXISTS book

修改列名

ALTER TABLE book CHANGE COLUMN (旧列名) (新列名) 类型

修改列的类型或约束

ALTER TABLE book MODIFY COLUMN (列名) 新类型

添加新的列名

ALTER TABLE book ADD COLUMN (列名) 类型

删除列

ALTER TABLE book drop COLUMN (列名)

修改表名

ALTER TABLE 旧表名 RENAME TO 新表名

复制表结构

CREATE TABLE 新表 LIKE 需复制的表

复制表结构+数据（复制部分可添加条件）

CREATE TABLE 新表 SELECT * FROM 需复制的表

创建表约束

CREATE TABLE stuinfo(

id INT,

stuname VARCHAR(20),

gender CHAR(1),

seat INT,

age INT,

majorid INT,

CONSTRAINT pk PRIMARY KEY(id), #主键约束

CONSTRAINT uq UNIQUE seat, #唯一约束

CONSTRAINT ck CHECK(gender = ‘男’ or gender = ‘女’) #check约束

CONSTRAINT fk_stuinfo_majorid FOREING KEY(majorid) REFERENCES major(id) #外键

);

添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

自增 AUTO_INCREMENT

SET AUTO_INCREMENT_INCREMENT = 3 #设置步长

ALTER TABLE student AUTO_INCREMENT = 1000 #设置自增的初始值

事务ACID

A原子性：一个事务是不可再分的，要么都执行要么都不执行

C一致性：一个事务执行会是数据从一个一致性状态切换到另一个一致性状态

I隔离性：一个事务在执行的时候，不受其他事务的干扰

D持久性：一个事务一旦提交，则会永久的改变数据库里的数据

使用事务前需关闭AUTOCOMMIT

set autocommit = 0 # 只存在当前会话，关闭之后又会自动打开

事务的使用

set autocommit = 0

START TRANSCATION;

UPDATA employees Set balance = 500 where username = ‘张三’;

UPDATA employees SET balance = 1500 where username = ‘李四’;

COMMIT; # 提交

ROLLBACK; #回滚在事务没有提交前，数据都存在内存中，如果出现错误就会进行回滚，只有提交完之后才会存入磁盘

脏读

对于两个事务T1,T2, T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚了，T1读取的内容就是临时且无效的

不可重复读

对于两个事务T1,T2,T1读取了一个字段，然后T2更新该字段，T1再次读取同一个字段则数据跟第一次的不一致

幻读

对于两个事务T1,T2,T1从一个表中读取了一个字段，然后T2在该表中插入了新的行，如果T1再次读取同一个表，会发现行数跟第一次的行数不一致

如何避免

数据库系统具有隔离并发运行各个事务的能力，是他们不会相互影响，避免各种并发问题

在这里插入图片描述

查看隔离级别

SELECT @@tx_isolation

设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别

设置全局隔离级别

SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL 隔离级别

在这里插入图片描述

存储过程

插入到admin表中五条记录

DELIMITER $

CREATE PROCUDURE myol()

BEGIN

INSERT INTO admin(username,password)

VALUES(‘john1’,‘000’),(‘john2’,‘000’),(‘john3’,‘000’),(‘john4’,‘000’),(‘john5’,‘000’);

END $

根据女神名查询对应的男神信息

DELIMITER $

CREATE PROCUDURE myp2(IN beaultyName VARCHAR(20))

BEGIN

SELECT bo.* FROM boys bo RIGHT JOIN beaulty b ON bo.id = b.boyfriend_id

WHERE b.name = beaultyName;

END $

用户是否登录成功

DELIMITER $

CREATE PROCUDURE myp3(IN username VARCAHR(20),IN password VARCHAR(20))

BEGIN

DECLARE result INT DEFAULT ’ '; # 声明并初始化遍历

SELECT COUNT(*) INTO result # 赋值

FROM amin

WHERE admin.username = username AND admin.password = PASSWORD;

SELECT IF(result>0,‘成功’,‘失败’); # 使用

END $

根据女神名，返回对应的男神名

DELIMITER $

CREATE PROCUDURE myp4(IN beautyName varchar(20),OUT boyName varchar(20))

BEGIN

SELECT bo.boyName INTO boyName

FROM boys bo

INNER JOIN beauty b ON bo.id = b.boyfriend_id

WHERE b.name = beautyName

END $

#调用

CALL myp4(‘小张’,@bName);

SELECT @bName ;

删除存储过程

DROP PROCEDUCE p1;

查看存储过程的结构

SHOW CREATE PROCUDURE myp2;

创建函数

DELIMITER $

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型

BEGIN

函数体

END $

调用函数

SELECT 函数名(参数列表)

返回公司的员工个数

DELIMITER $

CREATE FUNCTION myf1() RETURNS INT

BEGIN

DECLARE c INT DEFAULT 0

SELECT COUNT(*) INTO c

FROM employees

RETURN c

END $

SELECT myf1();

批量插入

DELIMITER $

CREATE PROCEDURE myp1_while(IN insertCount INT)

BEGIN

DECLARE i int DEFAULT 1;

WHILE i < insertCount DO

INSERT INTO admin(username,password) VALUES(CONCAT(‘ROSE’),i),‘666’);

SET i = i +1 ;

END WHILE;

END$

call myp1_while(95);

批量插入20条

DELIMITER $

CREATE PROCEDURE myp1_while(IN insertCount INT)

BEGIN

DECLARE i int DEFAULT 1;

a:WHILE i < insertCount DO

INSERT INTO admin(username,password) VALUES(CONCAT(‘ROSE’),i),‘666’);

IF i >= 20 THEN LEAVE a;

END IF

SET i = i +1 ;

END WHILE a;

END$

只有偶数时插入

DELIMITER $

CREATE PROCEDURE myp1_while(IN insertCount INT)

BEGIN

DECLARE i int DEFAULT 1;

a:WHILE i < insertCount DO

IF MOD(i,2) != 0 THEN ITERATE a;

END IF

INSERT INTO admin(username,password) VALUES(CONCAT(‘ROSE’),i),‘666’);

SET i = i +1 ;

END WHILE a;

END$

在这里插入图片描述

向该表插入指定个数的，随机的字符串

DELEMITER $

CREATE PROCEDURE myp1_while(IN num INT)

BEGIN

DECLARE i int DEFAULT 1;

DECLARE str VARCHAR(26) DEFAULT ‘abcdefghijklmnopqrstuvwxyz’;

DECLARE startIndex INT DEFAULT 1;

WHILE i <= num DO

SUBSTR(str,startIndex)

SET len = FLOOR(RAND()*(26-startIndex)+1);

SET startIndex= FLOOR(RAND()*26+1);

INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));

SET i = i+1;

END WHILE;

END $

引擎

在这里插入图片描述

索引（排好序的快速查找的数据结构）

优势：提高数据检索的效率，降低数据库的IO成本，降低数据排序的成本，降低CPU的消耗

劣势：索引列是需要占用空间的，在提高查询速度的同时也会降低更新速度，每次进行写操作都会更新索引信息

在这里插入图片描述

导致SQL执行时间下降

查询语句写的不好
索引失效
1. 单值
2. 复合
关联查询太多join
服务器调优及各个参数设置（缓存、线程数等）

EXPLAIN(可用于索引优化)

在这里插入图片描述

Using FILESORT/Using temporary 表示出问题了

Using index 表示 sql语句写的很好

在这里插入图片描述

@zhixun

关注

24
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
MYSQL

SELECT *,salary * 12 * ( 1 + IFNULL(commission_pct,0)) AS 年薪order by 年薪 DESC。
复制链接

扫一扫

MYSQL

MYSQL

按年薪的高低显示员工的信息和年薪

按姓名的长度显示员工的姓名和工资

要求先按工资排序，在按员工编号排序

选择工资不在7000-12000的员工的姓名和工资，按工资降序

查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，在按部门升序

将姓变大写，名变小写，然后拼接,用下划线隔开

从第7个字符开始截取 (sql语言中索引是从1开始) 可用substr/substring

若要截取前四个

姓名中首字符大写，其他字符小写然后用_拼接，显示出来

instr 放回字串第一次出现的索引，找不到则为0

TRIM 去前后空格，或者指定去除某个前后值

LPAD 左填充指定长度 数字小于字符长度 则进行截取

RPAD 右填充指定长度 数字小于字符长度 则进行截取

REPLACE 替换

数学函数

ROUND 四舍五入

CEIL 向上取整 返回>=该参数的最小整数

FLOOR 向下取整

TRUNCATE 截断

MOD取余

日期函数

NOW 返回当前系统日期 + 时间

CURDATE 返回系统日期不包含时间

CURTIME 返回时间不包含日期

可以获取指定的部分 年，月，日，小时，分钟，秒

STR_TO_DATE 将字符通过指定的格式转换成日期

DATA_FORMATE 将日期转换成字符

查询有奖金的员工名和入职日期(xx月/xx日 xx年)

其他函数

流程控制函数

if 函数 实现if - else

CASE 函数的使用：使用一 ：switch case

查询员工的工资

使用二 ：多重if

查询员工工资并评级

查询员工表的最大入职时间和最小入职时间的相差天数（DATADIFF）

查询哪个部门的员工人数>2

查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

非等值连接 sql 92

查询员工 的工资和工资级别

查询员工和上级名称

查询名字中包含e的员工名和工种名 sql 99

查询部门个数>3的城市名和部门个数

查询哪个部门的员工个数>3的部门名和员工个数，并降序排序

非等值连接

查询员工 的工资和工资级别

左外连接

查询哪个部门没有员工

返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id，工资

返回工资最少的员工的last_name,job_id,salary

查询最低工资大于50号部门最低工资的部门id和其最低工资

查询员工编号最小并且工资最高的员工信息

创建数据库

修改库的字符集

库的删除

修改列名

修改列的类型或约束

添加新的列名

删除列

修改表名

复制表结构

复制表结构+数据（复制部分 可添加条件）

创建表 约束

添加外键

自增 AUTO_INCREMENT

事务ACID

使用事务前需关闭AUTOCOMMIT

事务的使用

脏读

不可重复读

幻读

如何避免

查看隔离级别

设置隔离级别

设置全局隔离级别

存储过程

插入到admin表中五条记录

根据女神名查询对应的男神信息

LPAD 左填充指定长度数字小于字符长度则进行截取

RPAD 右填充指定长度数字小于字符长度则进行截取

CEIL 向上取整返回>=该参数的最小整数

可以获取指定的部分年，月，日，小时，分钟，秒

if 函数实现if - else

CASE 函数的使用：使用一：switch case

使用二：多重if

查询员工的工资和工资级别

查询员工的工资和工资级别

复制表结构+数据（复制部分可添加条件）

创建表约束