SQL练习

The one

SELECT last_name FROM employees;SELECT `first_name`,`job_id`,`salary`,`hiredate` FROM employees;SELECT * FROM employees;#查询常量值SELECT 100;SELECT \'john\';SELECT 100/45;SELECT VERSION();SELECT 100%78 AS 结果;#起别名SELECT salary AS \'out put\' FROM employees;#案例：去重 DISTINCT  查询员工表中涉及到的所有部门编号SELECT DISTINCT department_id FROM employees;SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;#显示出 表 employee是中的全部 全部列，各个列之间用逗号隔开,列头显示成 out putSELECTCONCAT(`first_name`,\',\',`email`,\',\',`job_id`,\',\',IFNULL(`commission_pct`,0)) AS \'out put\'FROMemployees;#--------------------------------------------------------------------#条件查询  查询工资》12000的员工# >  <  !=  <>  >= <=SELECT*FROMemployeesWHEREsalary > 12000;#查询部门编号在 90 - 110 之间，或者工资高于 15000的员工股信息SELECT*FROMemployeesWHERE#department_id < 90 or department_id >110 or salary>15000NOT(department_id >=90 AND department_id <=110) OR salary >15000# 模糊查询 like 查询员工名中第二个字符为_的员工名# ESCAPE 表明’$‘ 是一个转义字符SELECTlast_nameFROMemployeesWHERElast_name LIKE \'_$_%\' ESCAPE \'$\';#-----------------------------------------#between and 等价于 《= and 》= 顺序不能颠倒SELECT*FROMemployeesWHERE#department_id < 90 and department_id >110employee_id BETWEEN 100 AND 120#------------------------------------------------#in/*含义：判断某字段的值是否属于in列表中的某一项*/# 查询员工的工种编号是 IT_PROG，AD_VP AD_PRES中的一个员工名或工种编号SELECTlast_name,job_idFROMemployeesWHEREjob_id = \'IT_PROG\' OR job_id = \'AD_VP\' OR  job_id = \'AD_PRES\'#------------------------------------------SELECTlast_name,job_idFROMemployeesWHEREjob_id IN (\'IT_PROG\', \'AD_VP\', \'AD_PRES\')#---------------------------------------------#is null  is not nullSELECTlast_name,commission_pctFROMemployeesWHERE#commission_pct is nullcommission_pct IS NOT NULL#查询员工号为176的员工的姓名，部门编号，年薪SELECTlast_name,department_id,salary*12*(1+IFNULL(commission_pct,0))FROMemployeesWHEREemployee_id = 176#查询没有奖金，切工资小于18000的 salary,last_nameSELECTsalary,last_name,commission_pctFROMemployeesWHEREcommission_pct IS NULL AND salary < 18000

The two

#排序查询SELECT * FROM employees;#工资排序SELECT * FROM employees ORDER BY salary DESC;SELECT * FROM employees ORDER BY salary ASC;# 查询部门编号》=90的员工信息，按入职时间的先后进行排序SELECT *FROM employeesWHERE department_id >=90ORDER BY hiredate ASC;#---------------------------------------------#按年薪的高低显示 员工的信息和年薪SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪FROM employeesORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪FROM employeesORDER BY 年薪 DESC;#别名#---------------------------------------------------#按姓名长度显示员工的姓名和工资SELECT LENGTH(last_name) AS 姓名长度,last_name,salaryFROM employeesORDER BY LENGTH(last_name) ASCSELECT * FROM employeesORDER BY salary ASC,employee_id DESC;#1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪FROM employeesORDER BY 年薪 DESC,LENGTH(last_name) ASC;#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序SELECT last_name,salaryFROM employees#where not(salary>=8000 and salary <= 17000)WHERE salary NOT BETWEEN 8000 AND 17000ORDER BY salary DESC;#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序SELECT email,department_idFROM employeesWHERE email LIKE \'%e%\'ORDER BY LENGTH(email) DESC,department_id ASC;SELECT UPPER(\'asdddd\');SELECT LOWER(\'AssssA\')#将姓变大写，名变小写，然后拼接SELECT CONCAT(UPPER(first_name),LOWER(last_name)) AS 姓名 FROM employees#首字母大写，其他小写，并用_拼接出来SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),\'_\',LOWER(SUBSTR(last_name,2))) AS 名字FROM employees#instr 返回子串第一次出现的索引，如果找不到返回0SELECT INSTR(\'杨不悔爱上了张无忌\',\'张无忌\') AS uat;#lpad 用指定的字符实现左填充指定长度SELECT LPAD(\'张三丰\',10,\'*\') AS ouut;#rpad 用指定的字符实现右填充指定长度SELECT RPAD(\'张三丰\',10,\'*\') AS ouut;#replace 替换  三 替换成 四  out（张四张四）SELECT REPLACE(\'张三张三\',\'三\',\'四\')

The Three

SELECT ROUND(1.5)SELECT ROUND(1.522,2)SELECT CEIL(-1.95)  #上取整 -1SELECT FLOOR(-9.19); #下取整 -10#日期函数SELECT NOW();#curdate 返回当前系统日期，不包含时间SELECT CURDATE();#curtime 返回当前时间，不包含系统日期SELECT CURTIME();#将日期转换成字符串SELECT STR_TO_DATE(\'1998-3-2\',\'%Y-%c-%d\') AS out_outSELECT * FROM employees WHERE hiredate = STR_TO_DATE(\'4-3 1992\',\'%c-%d %Y\');#查询有奖金的员工名和入职日期（xx/年、xx/月..）SELECT last_name,DATE_FORMAT(hiredate,\'%m月/%d日 %y年\') 入职日期FROM employeesWHERE commission_pct IS NOT NULLSELECT last_name,commission_pct,IF(commission_pct IS NULL,\'没\',\'有\') AS 备注FROM employeesSELECT salary AS 原始工资,department_id,CASE department_idWHEN 30 THEN salary * 1.1WHEN 40 THEN salary * 1.2WHEN 50 THEN salary * 1.3ELSE salaryEND AS 新工资FROM employeesSELECT last_name,job_id AS job,CASE job_idWHEN \'AD_PRES\' THEN \'A\'WHEN \'ST_MAN\' THEN \'B\'WHEN \'IT_PROG\' THEN \'C\'WHEN \'SA_REP\' THEN \'D\'WHEN \'ST_CLERK\' THEN \'E\'END AS GradeFROM employeesWHERE job_id = \'AD_PRES\';

The Four

INSERT INTO beauty VALUES(14,\'唐三\',\'1\',\'1998-12-01\',\'asd\',NULL,2),(15,\'唐三\',\'1\',\'1998-12-01\',\'asd\',NULL,2),(16,\'唐三\',\'1\',\'1998-12-01\',\'asd\',NULL,2);SELECT * FROM beauty#修改beauty表中姓唐的女神的电话为134.。。。UPDATE beauty SET phone=\'13845454478\' WHERE NAME LIKE \'唐%\';UPDATE boys SET boyName = \'张飞\',userCP = 10 WHERE id = 2;SELECT * FROM boys#修改张无忌的女朋友的手机号为114UPDATE boys boINNER JOIN beauty b ON bo.id = b.boyfriend_idSET b.phone = \'114\'WHERE bo.boyName = \'张无忌\';

事务

DROP TABLE IF EXISTS accountCREATE TABLE account(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(32),balance DOUBLE);INSERT INTO account(username,balance)VALUES(\'张三\',1000),(\'李四\',1000);#开启事务SET autocommit = 0;START TRANSACTION;UPDATE account SET balance = 1500 WHERE username = \'张三\';UPDATE account SET balance = 1000 WHERE username = \'李四\';#rollback; 回滚#结束事务COMMIT;SELECT * FROM account

事务的特点

隔离级别

并发问题

系统变量

#查看所有的全局变量SHOW GLOBAL VARIABLES;  ˈveərɪəblz#查看部分全局变量SHOW GLOBAL VARIABLES LIKE \'%char%\';#查看指定的全局变量SELECT @@global.autocommit;#自动提交#为某个指定的全局变量赋值SET @@global.autocommit=1;#只会在本次回话中有效，换个连接不好用了，如果需要彻底改，则需要更改配置文件#查看所有的会话变量SHOW SESSION VARIABLES;SHOW VARIABLES; #默认是会话#为某个会话变量赋值  当前会话有效SET @@session.tx_isolation=\'read-uncommitted\';SELECT @@session.tx_isolation;SET SESSION tx_isolation = \'read-commited\';

自定义变量

/*说明：变量是用户自定义的，不是由系统的使用步骤：声明赋值使用（查看，比较，运算等）*/#1、用户变量/*作用域：针对于当前会话连接有效，同于会话变量的作用域*/赋值的操作符：  =  或:=1.声明并初始化SET @用户变量名=值;或SET @用户变量值:=值;或SELECT @用户变量名:=值;2.赋值

局部 变量（dɪˈkleə®）

存储过程

过程语法

#一、创建语法CREATE PROCEDURE 存储过程名(参数列表)  #prəˈsiːdʒə(r)BEGIN储存过程提（一组合法的sql语句）END注意：1、参数列表包含三个部分参数模式 参数名  参数类型例：IN stuname VARCHAR(20)参数模式：IN  该参数可以作为输入，也就是参数需要调用方法传入值OUT  该参数可以作为输出，也就是该参数可以作为返回值INOUT  该参数既可以作为输入又可以作为输出，也就是该参数 即需要传入，也有返回值2.如果存储过程提仅仅只有一句话，  BEGIN END 可以省略存储过程体中的么调sql语句的结尾要求必须加分号存储过程的结尾可以使用 DELIMITER 重新设置DELIMITER  结束标记DELIMITER $二、调用语法CALL 储存过程名(实参列表)

案例

#空参列表案例 ： 插入到 admin表中五条记录SELECT * FROM adminDELIMITER $CREATE PROCEDURE myp1()BEGININSERT INTO admin(username,`password`)VALUES(\'jonhin\',\'0000\'),(\'lila\',\'1111\'),(\'tom\',\'1111\'),(\'jieke\',\'1111\'),(\'rose\',\'1111\');END $CALL myp1()$SELECT * FROM admin$#---------------------------#案例 创建存储过程实现，根据女神名，查询对应的男神信息DELIMITER $CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))BEGINSELECT bo.*FROM boys boRIGHT JOIN beauty b ON bo.id = b.boyfriend_idWHERE b.name = beautyName;END $CALL myp2(\'小昭\')$#-----------------------------------#out根据女神名，返回对应的男神名CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGINSELECT bo.boyName INTO boyNameFROM boys boINNER JOIN beauty b ON bo.id = b.boyfriend_idWHERE b.name = beautyName;END $CALL myp5(\'小昭\',@bName)$SELECT @bName$#多个CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT lingyige)BEGINSELECT bo.boyName,bo.lingyige INTO boyName,lingyigeFROM boys boINNER JOIN beauty b ON bo.id = b.boyfriend_idWHERE b.name = beautyName;END $CALL myp6(\'小昭\',@bName,@lingyige)$SELECT @bName,@lingyige$#---------------------------------------------#inout#传入两个值，a和b，最终 a和b值X2CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)BEGINSET a=a*2;SET b=b*2;END$SET @m=10$SET @n= 20$CALL myp8(@n,@m)$SELECT @m,@n$

储存过程删除

drop procedure myp1;drop procedure myp1,myp2;  错误查看储存过程desc myp2;  X错误show create procedure myp2;

函数

#一、创建语法CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型BEGIN函数体END/*注意：参数列表 包含两部分参数名 参数类型函数体：肯定会有return语句，如果没有 会 报错如果 return语句 没有放在函数体的最后也不报错，但不建议return  值;使用 delimiter 语句设置结束标记*/#案例演示#返回公司的员工个数#无惨有返回DELIMITER $CREATE FUNCTION myf1() RETURNS INTBEGINDECLARE d INT DEFAULT 0;SELECT COUNT(*)	INTO dFROM employees;RETURN d;END $SELECT myf1()$#有参有返回#根据员工名返回它的工资DELIMITER $CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLEBEGINSET @sal = 0;SELECT salary INTO @salFROM employeesWHERE last_name = empName;RETURN @sal;END $SELECT myf2(\'k_ing\')$查看

查看函数

SHOW CREATE FUNCTION myf1;删除函数drop function myf1;

流程控制

#没有添加循环控制语句#批量插入，根据次数插入到admin表中多条记录DELIMITER $CREATE PROCEDURE pro_while(IN insertCount INT)BEGINDECLARE i INT DEFAULT 1;WHILE i<= insertCount DOINSERT INTO admin(username,`password`) VALUES(CONCAT(\'role\',i),\'1111\');SET i= i+1;END WHILE;END $CALL pro_while(100)$

例题

/*一、已知表stringcontent其中字段id 自增长content varchar(20)向该表插入指定个数的，随机的字符串*/USE testDROP TABLE IF EXISTS stringcontent;CREATE TABLE stringcontent(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(20));DELIMITER $CREATE PROCEDURE test_randstr_insert(IN insertCount INT)BEGINDECLARE i INT DEFAULT 1;DECLARE str VARCHAR(26) DEFAULT \'abcdefghijklmnopqrstuvwxyz\';DECLARE startIndex INT DEFAULT 1;DECLARE len INT DEFAULT 1;WHILE i<=insertCount DOSET len = FLOOR(RAND()*(20-startIndex+1)+1);SET startIndex = FLOOR(RAND()*26+1);INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));SET i = i+1;END WHILE;END $CALL test_randstr_insert(10);$SELECT * FROM stringcontent;$