函数
用法
ABS(x)
返回x的绝对值
SQRT(x)
返回x的平方根
POW(x,y)
返回x的y次方根
CEIL(x)
返回大于x的最小整数,即向上取整
FLOOR(x)
返回小于x的最大整数,即向下取整
MOD(x,y)
返回x/y的值
RAND()
返回0-1的随机数
ROUND(x,y)
返回x的四舍五入,y为保留几位小数
TRUNCATE(x,y)
不考虑四舍五入,截断x,y为截断的小数点位数
FORMAT(x,y)
强制保留y位,考虑四舍五入,和ROUND(x,y)区别是整数超过三位会以逗号分隔,返回文本
练习
SELECT CEIL(2.1) #3 向上取整
SELECT FLOOR(2.1) #2 向下取整
SELECT RAND() #返回0-1之间的随机数 0.6553991843530901
SELECT ROUND(2.5686,3) #四舍五入保留三位 2.569
SELECT TRUNCATE(2.5686,3) #截断小数点后三位 2.568
SELECT FORMAT(123859.5686,3) #四舍五入,整数超过三位逗号分隔 123,859.569
函数
用法
CONCAT(S1,S2…Sn)
拼接字符串
CONCAT_WS(s,S1,S2…Sn)
拼接字符串。但每个字符会加s
LENGTH(s)
返回字符串s的字符串,和字符集有关,一个字符占3个字节
LEFT(s,n) RIGHT(s,n)
返回字符串左边/右边的第n个字符
TRIM(s)
去除s开始和结束的空格
SUBSTRING(s,index,len)
返回字符串s的index位置截取len字符
练习
SELECT CONCAT('Hello','World') #HelloWorld
SELECT CONCAT_WS('%','A','B','C') #A%B%C
SELECT LENGTH('hello world') #11
SELECT LEFT('helloworld',5),RIGHT('helloworld',5) #hello world
SELECT TRIM(' YYYY-MM-dd') #YYYY-MM-dd
SELECT SUBSTRING('helloworld',1,5) #hello
函数
用法
CURDATE()
返回当前系统日期
CURTIME()
返回当前系统时间
NOW()/SYSDATE()/LOCALTIME()/LOCALTIMESTAMP()
返回当前系统日期时间
YEAR(date)/MONTH(date)/DAY(date)/ HOUR(time)/MINUTE(time)/SECOND(time)
返回具体的年月日时分秒
DATEDIFF(DATE1,DATE2)
返回两个日期的日期间隔
DATE_FORMAT(datetime,fmt)
按照字符串fmt格式化日期datetime值
练习
SELECT CURDATE(),CURTIME(); # 2022-07-04 07:47:5
SELECT NOW(),SYSDATE(),LOCALTIME(),LOCALTIMESTAMP() #2022-07-04 07:52:23
SELECT YEAR(NOW()) AS '年',MONTH(NOW()) AS '月',DAY(NOW()) AS '日',HOUR(NOW()) AS '时',MINUTE(NOW()) AS '分',SECOND(NOW()) AS '秒' #年 月 日 时 分 秒
2022 7 5 20 37 45
SELECT DATEDIFF('2022-07-04','2022-07-01') #3
SELECT DATE_FORMAT(CURDATE(),'%y%m%d') #220705
# 从员工表查询这个月过生日的员工
SELECT *
FROM t_employee
WHERE MONTH(CURDATE())=MONTH(birthday)
# 从员工表查询年龄大于40岁的员工
SELECT *
FROM t_employee
WHERE YEAR(NOW())-YEAR(birthday)>40
函数
用法
password(str)
返回字符串str的加密版本,41位长的字符串(mysql8不再支持)
md5(str)
返回字符串str的md5值,也是一种加密方式
SHA(str)
返回字符串str的sha算法加密字符串,40位十六进制值的密码字符串
SHA2(str,hash_length)
返回字符串str的sha算法加密字符串,密码字符串的长度是hash_length/4。hash_length可以是224、256、384、512、0,其中0等同于256。
练习
SELECT MD5('123456') #e10adc3949ba59abbe56e057f20f883e
SELECT SHA('123456') #7c4a8d09ca3762af61e59520943dc26494f8941b
函数
用法
VERSION()
返回数据库版本信息
USER()
查询当前登录用户
DATABASE()
查询当前使用哪个数据库
练习
SELECT VERSION() #8.0.25
SELECT USER() #root@localhost
SELECT DATABASE() #atguigu
函数
用法
IF(value,T,F)
相当于Java中的三元运算符,value为真返回T,否则返回F
IFNULL(value,value2)
判断value是否为空,为空返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …ELSE 结果n END
流程控制语句,相当于Java中 if …else if
CASE 条件 WHEN 常量值1 THEN 结果1 WHEN 常量值2 THEN 结果2 …ELSE 结果n END
流程控制语句,相当于Java中的switch …case
练习
SELECT pname,
CASE
WHEN pname='iphone11' THEN '苹果11'
WHEN pname='iphone12' THEN '苹果12'
WHEN pname='iphone13' THEN '苹果13'
ELSE '安卓'
END AS 手机品牌
FROM product
SELECT pname,price,
CASE price
WHEN 3999 THEN '苹果11'
WHEN 4999 THEN '苹果12'
WHEN 5999 THEN '苹果13'
ELSE '安卓'
END AS 手机
FROM product
inner join 表名 on 连接条件
查询所有员工的姓名,部门编号,部门名称
SELECT ename,t_employee.`did`,dname
FROM t_employee INNER JOIN t_department ON t_employee.`did`=t_department.`did`
left join 表名 on 连接条件
查询所有员工,包括没有指定部门的员工,他们的姓名、薪资、部门编号、部门名称
SELECT ename,salary,t_employee.`did`,dname
FROM t_employee LEFT JOIN t_department ON t_employee.`did`=t_department.`did`
right join 表名 on 连接条件
查询部门编号、部门名称、员工姓名
SELECT t_department.`did`,dname,ename
FROM t_employee RIGHT JOIN t_department ON t_employee.`did`=t_department.`did`
SQL语句1 union SQL语句2 查询字段必须相同
union 和 union all 区别是前者会去掉重复数据,后者不会去掉重复数据
查询所有员工和所有部门
SELECT *
FROM t_employee LEFT JOIN t_department ON t_employee.`did`=t_department.`did`
UNION
SELECT *
FROM t_employee RIGHT JOIN t_department ON t_employee.`did`=t_department.`did`
select 字段名 from 表1,表2 where 连接条件
SELECT *
FROM t_employee,t_department
WHERE t_employee.`did`=t_department.`did`
limit 记录的下标,每页的记录数
第n页,每页显示total条
limit (n-1)*tatal,total
每页显示5条,第一页 limit (1-1)*5,5 limit 0,5
每页显示5条,第二页 limit (2-1)*5,5 limit 5,5
每页显示5条,第三页 limit (3-1)*5,5 limit 10,5
SELECT *
FROM t_employee
LIMIT 5,5
查询每个部门平均薪资与公司平均薪资的差值
SELECT did,AVG(salary),AVG(salary) - (SELECT AVG(salary) FROM t_employee) AS '差值'
FROM t_employee
GROUP BY did
查询员工最高的员工姓名和薪资
SELECT ename,salary
FROM t_employee
WHERE salary=(SELECT MAX(salary) FROM t_employee)
查询比全公司平均薪资高的男员工姓名和薪资
SELECT ename,salary
FROM t_employee
WHERE salary>(SELECT AVG(salary) FROM t_employee) AND gender='男'
当子查询的结果是单列单个值,那么可以直接使用比较运算符,如“<”、“<=”、“>”、“>=”、“=”、“!=”等与子查询结果进行比较。
当子查询的结果是单列多个值,那么可以使用比较运算符IN或NOT IN进行比较。
当子查询的结果是单列多个值,还可以使用比较运算符, 如“<”、“<=”、“>”、“>=”、“=”、“!=”等搭配ANY、SOME、ALL等关键字与查询结果进行比较。
查询和"白露" “谢吉娜” 同一个部门员工的姓名和电话
SELECT ename,tel,did
FROM t_employee
WHERE did IN(SELECT did FROM t_employee WHERE ename='白露' OR ename='谢吉娜')
带关键字ANY的子查询
关键字ANY表示满足其中任意一个条件。只要满足内层查询语句返回的结果中的任意一个,就可以通过条件执行外层语句。
SELECT ename,tel,did
FROM t_employee
WHERE did =ANY(SELECT did FROM t_employee WHERE ename='白露' || ename='谢吉娜');
=ANY等价于IN
带关键字ALL的子查询
关键字ALL表示满足所有条件。使用关键字ALL时,只有满足内层查询语句的所以返回结果,才可以执行外层查询语句。
查询薪资比“白露”,“李诗雨”,“黄冰茹”三个人的薪资都要高的员工姓名和薪资
SELECT ename,salary
FROM t_employee
WHERE salary >ALL(SELECT salary FROM t_employee WHERE ename IN('白露','李诗雨','黄冰茹'));
关键字ANY和ALL的使用方法是一样的,但两者存在着很大的区别,使用ANY关键字时,只要满足内层查询语句结果中的任意一个,就可以通过该条件来执行外层查询语句;而关键字ALL则需要满足内层查询语句返回的所有结果,才可以执行外层查询语句。
查询每个部门的平均薪资,关联部门表,查询结果为部门表所有信息和平均薪资
SELECT *
FROM t_department LEFT JOIN (SELECT did,AVG(salary) AS pj FROM t_employee GROUP BY did) a
ON t_department.did=a.did
使用EXISTS时,内层查询不返回查询语句,而是返回一个布尔值(true false)。如果EXISTS子查询返回查询到满足条件记录,返回true,执行外层查询,否则返回false,不执行外层查询。
查询“t_employee”表中是否存在部门编号为NULL的员工,如果存在,查询“t_department”表的部门编号、部门名称
SELECT *
FROM t_department
WHERE EXISTS(SELECT * FROM t_employee WHERE did IS NULL)
#仅仅是复制表结构,可以用create语句
CREATE TABLE department LIKE t_department;
#使用INSERT语句+子查询,复制数据,此时INSERT不用写values
INSERT INTO department (SELECT * FROM t_department WHERE did<=3);
#同时复制表结构+数据
CREATE TABLE d_department AS (SELECT * FROM t_department);
#如果select后面是部分字段,复制的新表就只有这一部分字段
唯一并且非空
一个表最多只能有一个主键约束
如果主键是由多列组成,可以使用复合主键
新建学生表(学号 、姓名) 课程表(课程号 、课程名)选课表(学号、课程号、成绩)。其中学生表主键是学号,课程表主键是课程号 选课表主键是学号+课程号
CREATE TABLE stu(
sid INT PRIMARY KEY,
sname VARCHAR(50)
)
CREATE TABLE course(
cid INT PRIMARY KEY,
cname VARCHAR(50)
)
CREATE TABLE xuanke(
sid INT ,
cid INT ,
score INT,
PRIMARY KEY(sid,cid)
)
允许为null
一个表可以存在多个唯一约束
** 创建tmp表,限制编号、身份证、手机号唯一**
CREATE TABLE tmp(
id INT UNIQUE KEY,
tname VARCHAR(50),
tel INT UNIQUE KEY,
icard INT UNIQUE KEY
)
只能某个列单独限定非空
一个表可以又很多列存在非空约束
** 创建成绩表,限制成绩不能为空**
CREATE TABLE cj(
id INT PRIMARY KEY,
source INT NOT NULL
)
创建学生表,性别列默认设置为男
CREATE TABLE stu(
id INT PRIMARY KEY,
gender CHAR(2) DEFAULT '男'
)
创建学生表,检查约束年龄18-35
CREATE TABLE stu(
id INT PRIMARY KEY,
gender CHAR(2) DEFAULT '男',
age INT CHECK (age>18 && age<45))
事务保证所有事务都作为一个工作单元来执行,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改,整个事务回滚(rollback)到最初状态。
事务的ACID属性:
MySQL默认情况下是自动提交事务。
提交 commit
回滚 ROLLBACK
#开启手动提交事务模式
set autocommit = false; 或 set autocommit = 0;
#恢复自动提交模式
set autocommit = true; 或 set autocommit = 1;
SET autocommit = FALSE;#设置当前连接为手动提交模式
UPDATE t_employee SET salary = 15000
WHERE ename = '孙红雷';
COMMIT;#提交
SET autocommit=FALSE
DELETE FROM stu WHERE sid=1
ROLLBACK;
start transaction;
START TRANSACTION; #开始事务
UPDATE t_employee SET salary = 0
WHERE ename = '李冰冰';
#下面没有写commit;那么上面这句update语句没有正式生效。
commit;#提交
START TRANSACTION;
DELETE FROM t_employee;
ROLLBACK; #回滚
DDL语句不支持事务
登录验证:主机IP地址+用户名+密码三重验证
IP地址可以是一个明确的IP(例如:192.168.1.25),可以是某个IP段(例如:192.168.1.%),可以是任意IP地址(%)。
mysql -h mysql服务器的IP地址 -P端口号 -u用户名 -p
Enter password: ******
第1步,选择工具栏中的用户管理器工具按钮,打开用户管理界面。
第2步,如果要创建新用户,选择“添加新用户”按钮,弹出新用户信息填写窗口。用户名和主机文本框必须填写,其他项可以不填写,按照默认值处理。如果密码和再一次输入密码框为空,表示密码为空。如果要设置密码必须保证密码框和再一次输入密码框输入相同字符,并在Plugin选择合适的插件“caching_sha2_password”或“mysql_native_password”,默认是“caching_sha2_password”插件。如果需要还可以在下面填写用户资源限制参数,默认值是0表示不限制。
第3步,如果要修改用户信息,可以直接在“用户”下拉列表中选择用户,然后在右边直接修改用户信息。
第4步,如果是对已有的用户进行授权操作,或撤销已有用户的授权,可以直接在“用户”下拉列表中选择用户,然后在左下方选择权限等级,右边对应权限打对勾表示授予该项权限,不打对勾表示不授予该项权限。