本文介绍了如何查看和修改MySQL数据库的版本、字符集，以及如何执行常见的SQL操作，如用户权限修改、数据导入、表结构查询、数据过滤和排序、多表查询和DML操作。还涵盖了子查询、聚合函数、流程控制函数和数据库管理的相关知识。
查看数据库版本:select version();
查看表字符集:show create database/table 数据库名/表名;
字符集修改在my.ini 内添加
mysql
default-character-set=utf8
mysqld
character-set-server=utf8
collation-server=utf8_general_ci
重启服务即可
修改加密规则: use mysql
alter user 'root'@'localhost'identified with mysql_native_password by '密码'
刷新权限:flush privileges
注释:
#单行注释
-- 单行注释
/**/多行注释
数据导入:
source 盘符(d:\xxx.sql)
起别名:
1.select xx as aa from dual
2.select xx bb from dual
3.select xx "b b" from dual
去除重复行:
distinct关键字:select distinct department_id from employees
空值参与运算: 结果为空
空值:null(:它不等同于0,'','null')
着重号:``
用于区分掉关键字
查询常数:
select "123",emp_id from emp
显示表结构:
1.desc emp;
2.describe emp;
过滤数据:
select * from emp where id = xx;
算数运算符:+ - * /(div) %(mod)
比较运算符:
=: 等于
<=>: 安全等于,可以对null进行判断
<>(!=): 不等于
<: 小于
<=: 小于等于
>: 大于
>=: 大于等于
isnull(字段),is null,is not null,判断是否为空:
select * from emp where salary is notnull
least(A,B):找最小值
grearest(A,B):找最大值
between and:两值之间
in:判断一个值是否在另一个里面
not in:判断是否不在里面
like:模糊匹配,%不确定个数字符(0到多个) _:代表一个不确定的字符
\:转移字符:将特殊符号转为普通(或者使用escape关键字)
select* from emp where name like "_$_a%" escapse '$'
regexp:判断是否符合正则表达式规则
rlike:判断是否符合正则表达式规则
逻辑运算符:not 或 !
and 或 &&
or 或||
xor 逻辑异或
注意:and优先级高于or
排序与分页:
排序:order by 升序 asc 降序 desc 默认是升序排列
支持二级排序:select salary,id from emp order by id asc,salary desc,如果第一列值唯一则不进行第二列排序
分页: limit 偏移量,条目数 mysql8.0新特性:limit 条目数 offset 偏移量
声明顺序:where.....order by.....limit
多表查询: 两表连接条件要明确
每个表中出现了相同字段,就要查询这个字段的时候需要说明是哪个表的字段
在from中给表起别名:
可以给表起别名,在select和where中去使用别名,一旦使用了别名,每个地方都要使用别名
n各表链接,需要n-1个条件
多表连接查询分类:
等值连接vs非等值连接
自连接vs非自连接
eg:
select e1.employee_id, e1.last_name, e1.manager_id, e2.manager_id, e2.last_name
from employees e1,
employees e2
where e1.manager_id= e2.employee_id
内连接vs外连接
内连接:查询一样的数据,不一样舍去
外连接:顾名思义与上相反
外连接分类:左外连接,右外连接,全连接(所有都要)
sql右92语法和99语法:
99语法:内连接:inner(这个inner可省略)join on,left join ..on 左连接,right join ....on右连接,满外连接:mysql不支持
合并查询结果:union,将结果组成单个结果集
union all 多了个公共部分
union:要去重,所以效率低
7种join的实现
注:超过三张表禁止使用join效率会变差
SQL99新特性:
自然连接:nature join,自动将两张或多张表内相同字段进行等值连接
using:将 join ...on 这个 on的条件替换为了using(相同字段)
单行函数:ABS(X)返回绝对值
sing(x)正数返回1负数返回0,零也是0
PI():圆周率
ceil,ceiling:返回最小整数
floor:返回最大整数
least:返回最小
greatest:返回最大
mod(x,y):返回x除y后余数
rand():返回0-1随机数
rand(x):返回0-1随机数,x为种子
round(x):四舍五入后接近x值
round(x,y):四舍五入后接近x值,保留y位
truncate(x,y):返回数字x阶段为y位的结果
sqrt(x):返回x平方根,负数返回null
三角函数:略,自行百度
字符串函数:
ascii:查ascii值
length:字节个数
concat:连接在一起
concat_ws("分隔符"):用分隔符连接
insert:某值替换,通过索引替换
replace:替换,通过内容替换
upper/ucase:转大写
lower/locase:转小写
left:左边取几个字符
right:右边取几个字符串的字符
lpad:右对齐
rpad:左对齐
ltrim/rtrim/trim去除空格
trim(x from xxx):从 xxx种去除 x 也就是指定去除
repeat:重复值
space:提供空格
strcmp:比较ascii值
substr:截取字符串
locate:返回某字符串出现位置
elt:返回指定字符串位置
field:返回字符串第一次出现位置
find_in_set:返回字符串第一次出现位置
reverse:反转字符
nullif:相等返回null,不等返回值
日期和时间类型函数
curdate():返回年月日
curtime():返回时分秒
now()/current_timestamp()/localtime()/localtimestamp()查看当前时间
日期与时间戳转换
unix_timestamp() 当前时间时间戳
unix_timestamp(date) 当前传入date时间戳
select from_unixtime(timestamp)将unix时间戳转普通时间
获取月份,星期,星期数,天数等函数
year/mouth/day
hour/minute/second
monthname,返回月份
dayname:返回某天
weekday;返回周几,周一是0
quarter,返回日期对应季度,1-4
week,weekofyear:返回一年的第几周
dayofyear:返回一年中第几天
dayofmonth:当月第几天
dayofweek:周几,周日是1
日期操作函数
extract(type from date) 返回日期中指定部分,type值返回值
时间和秒钟转换函数
time_to_sec():转换公式为小时*3600+分钟*60+秒
sec_to_time():转化为包含小时分钟秒的时间
计算日期和时间的函数:
adddate()/date_add()/subdate()/date_sub() :年月日时分秒加减操作
日期格式化和解析
日期转字符串
字符串转日期
date_format()
time_format()
get_format()
str_to_date()
fmt格式百度一下
流程控制函数
if(v,v1,v2):v是true返回v1否则返回v2
ifnull(v,v1,v2):同上
case when 条件1 then 结果1 when条件2 then 结果2.... else end
case expr when 常量值1 then 值1 when 常量值2 zhen 值2 else 值n end
加密解密函数(了解)
password 8.0不能用
md5
sha
encode 8.0不能用
decode 8.0不能用
MYSQL信息函数
version
connect_id 当前mysql服务器连接数
database,schema 当前所在数据库
user,current_user,system_user,session_user 当前连接用户名
其他函数
format(value,n) value格式 b四舍五入保留多少位
conv:进制转换
inet_aton将以点分割的ip转化为数字 192*256三次方+168*256的2次方+1*256+100
inet_ntoa将以数字形式ip转化为以点分割的
benchmark,将表达式重复执行n次
convert(value usig charcode),转化字符编码
5大常用聚合函数
avg
sum
max
min
count 不计算空值
group by,分组
多列分组
注意:select中 分组函数字段一定要出现在组函数中,group by中数据不一定出现在select中
group by 声明在 from where之后 order by, limit 前
group by中使用with rollup(计算整体平均值)
having过滤数据:过滤条件中使用了聚合函数,那么就要用having替换where
同时having要求放于group by之后,使用having前提是使用了group by
有聚合函数用having ,没右就用where
where与having对比
having适用范围广
没有聚合函数where效率高于having
sql执行顺序:
from....where......group by.........having.....select.....distingct.......order by.....limit
子查询:(可放在having/case/from(把结果当一张新表)/order by内)
子查询分类:
单行子查询vs多行子查询
相关子查询vs不相关子查询
相关子查询:
select last_name, salary, department_id
from employees e1
where salary > (
select avg(salary)
from employees e2
where department_id = e1.department_id
)
注意:除了group by 和limit 其他位置都可以声明子查询
内查询中出现了外查询的表,那就是相关子查询
exist和not exist关键字
数据库的创建修改与删除
create/alter/drop/rename/truncate
创建数据库:create database 名称
创建数据库并指定字符集:create database character set 字符集
判断库是否存在,不存在则创建:create database if not exists 名称
注意数据库不能改名字
查看数据库:show databases
指定使用数据库:use 数据库名
查看当前数据库中表:show tables
查看当前数据库:select database
查看指定数据库下表:
show tables from mysql
修改数据库:
alter database 库名 character set 字符集
删除数据库
drop database 库名
drop database if exists 库名
表数据类型:字符,数值,日期类型
查看表结构:desc 表名
查询创建表的语句:show create table 表明
基于现有表创建表:create table 表名 as select.....
修改表
添加字段:
alter table add 字段名 类型 first/after 字段名 //默认添加到现有表后
修改字段:
alter table modify字段名 类型 default xxx
修改字段名:
alter table change字段名 新字段名 类型
删除字段
alter table drop column 字段名
重命名表
rename table 表名 to表名
或者:alter table 表名 rename to 表名
删除表
drop table 表名
清空表
truncate table 表名
truncate table 与delete from的区别
truncate table执行数据不可恢复,不支持回滚,后者可支持回滚
TCl中的commit与rollback
DML:增删改
增一条数据:
insert into 表 values()
insert into 表 (字段) values()
增多条
insert into 表 (字段) values(),(),(),()
将查询结果插入
insert into 表(字段) 查询语句selet//注意字段要一 一 对应
跟新数据:
update 表 set 字段 where
删除数据
delete from 表 where
Mysql8.0新特性:计算列
在create table 或者 alter table都支持计算列
create table test1(
a int,
b int,
c int generated always as (a+b) virtual
)
4527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
