Mysql学习笔记02

最新推荐文章于 2024-07-22 14:15:06 发布

等待救赎的波

最新推荐文章于 2024-07-22 14:15:06 发布

阅读量584

点赞数

文章标签： java

本文链接：https://blog.csdn.net/qq_46110710/article/details/124308342

版权

Mysql学习笔记02

添加列

alter table tablename
add			(column datatypr [default expr]
					[,colum datatype]...
);

example

alter table `emp`
add image varchar(32) not null default '' after resume;

修改列

alter table tablename
modify   	(column datatypr [default expr]
					[,colum datatype]...
);

example

alter table `emp`
modify job varchar(60) not null default '';

删除列

alter table tablename
drop   	(column);

查看表的结构

desc 表名;

修改表名

rename table 表名 to 新表名;

修改字符集

alter table 表名 character set 字符集;

insert

insert into table_name[(colimn [, column...])]
values (value[,value...])

当不给某个字段值时，如果有默认值就会添加，否则报错

example

INSERT INTO `emp`
VALUES (100, '小妖怪', '男', '2000-11-10', '2010-11-10 11:11:11','巡山', 3000, '大王叫我来巡山');

update

update tb_name set col_name=expr1 [,col_name2=expr2 ...]
		[where where_definition]

.WHERE句指定应更新哪些行。如没有WHERE子句，则更新所有的行(记录)
如果需要修改多个字段，可以通过set字段1=值1,字段2=值2...

example

update employee set salary = 5000 where id = 100;

delete

delete from tb_name
[where where_definition]

example

delete from employee where name = '小妖怪';

select

select [distinct] *| {column1,column,column3..}
from tablename

example

select NAME,english from student;
select distinct english from student;

DISTINGT可选,指显示结果时，是否去掉重复数据

as

select column_name as 别名 from 表名;

example

select `name` as '名字', (chinese + english + math + 10) as total_score
from student;

比较运算符

between … and…
- 显示在某一区间的值
in(set)
- 显示在in列表中的值
like ’张pattern’
- 模糊查询
is null
- 是否为空

逻辑运算符

and
- 多个条件同时成立
or
- 多个条件任一成立
not
- 不成立

example

select *
from student
where name = '赵云';
select *
from student
where english > 90;
select *
from student
where (chinese + english + math) > 200;
select *
from student
where math > 60
  and id > 4;
select *
from student
where english > chinese;
select *
from student
where (chinese + english + math) > 200
  and math < chinese
  and name like '韩%';
select *
from student
where english >= 80
  and english <= 90;
select *
from student
where math in (89, 90, 91)
;
select *
from student
where chinese between 70 and 80;
select *
from student
where (chinese + math + english) in (189, 190, 191);
select *
from student
where name like '张%'
   or name like '宋%';
select *
from student
where math - chinese = 1;

order by

select column1, column2,column3..
from table;
order by column asc|desc

Asc升序[默认],Desc降序

count()

select count(*) (列名) from table_name
[where where_definition]

count(*)返回满足条件的记录的行数
count(列)统计满足条件的某列有多少个，但是会排除为null

sum()

select sum(列名){,sum(列名)...} from tablename
			[where where_definition]
			select sum(math) from student;

example

select sum(math),sum(english),sum(chinese) from student;

avg()

select avg(列名){,avg(列名)...} from tablename[where where_definition]

example

select avg(math + english + chinese)
from student;

max/min

select max(列名)from tablename[where where_definition]

example

select max(math), min(math)
from student;

group by 分组

select clumn1,column2,column3.. from table
group by column

having 对分组后的结果过滤

select column1,column2,column3..
from table
group by column having...

example

select avg(sal), max(sal), deptno
from emp
group by deptno;
-- 显示每个部门的每种岗位的平均工资和最低工资
select avg(sal), min(sal), deptno,job
from emp
group by deptno,job;
-- 显示平均工资低于2000的部门号和它的平均工资
select avg(sal),deptno from emp group by deptno having avg(sal) < 2000;

字符串函数

charset(str)
- 返回字符串字符集
concat(string2 [,…])
- 连接字符串
instr(string,substring)
- 返回substring在string中出现的位置，没有返回0
ucase(string2)
- 转换成大写
lcase(string2)
- 转换成小写
left(string2,length)
- 从string2中的左边起取length个字符
length(string)
- string长度[按照字节]
replace(str,search_str,replace_str)
- 在str中用replace_str替换search_str
strcmp(string1,string2)
- 逐字符比较两字符串大小
substring(str,position,[length])
- 从str的position开始[从1开始计算]，取length字符
ltrim(string2) rtrim(string2) trim
- 去除左/右/两边空格
  -- dual亚元表，系统表可以作为测算表使用

example

select charset(ename) from emp;
select concat(ename,' job is ',job) from emp;
-- dual亚元表，系统表可以作为测算表使用
select instr('ward','d') from dual;
select ucase(ename) from emp;
select left(ename,2) from emp;
select right(ename,2) from emp;
-- string长度 按字节
select length(ename) from emp;
select replace(job,'MANAGER','经理') from emp;
select strcmp('ll','ll') from dual;
select substring(ename, 1, 2)
from emp;
select ltrim('  123')from dual;
select rtrim('123 ')from dual;
select trim('  123 ')from dual;
-- 以首字母小写显示所有员工姓名
select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp
select concat(lcase(left(ename,1)),substring(ename,2)) from emp

数学相关函数

abs(num)
- 绝对值
bin(decimal_number)
- 十进制转二进制
ceiling(number2)
- 向上取整
conv(number2,from_base,to_base)
- 进制转换
floor(number)
- 向下取整
format(number,decimal_places)
- 保留小数位数
hex(decimalnumber)
- 转十六进制
least(number,number2 [,…])
- 求最小值
mod(numberator,denominator)
- 求余
rand([seed])
- 随机数0~1.0

example

select abs(-10) from dual;
select bin(10) from dual;
select ceiling(1.1) from dual;
select conv(2,10,2) from dual;
select floor(1.1) from dual;
select format(78.123458,2)from dual;
select least(0,1,-10,4) from dual;
select mod(10,3) from dual;
select rand(1) from dual;

时间日期相关函数

current_date()
- 当前日期
current_time()
- 当前时间
current_timestamp()
- 当前时间戳
date(datetime)
- 返回datetime的日期部分
date_add(date,interval d_value d_type)
- 在date2中加上日期或时间
date_sub(date,interval d_value d_type)
- 在date2上减去一个时间
datediff(date1,date2)
- 两个日期差(结果是天)
timediff(date1,date2)
- 两个时间差(多少小时多少分钟多少秒)
now()
- 当前时间
year|month|date(datetime) from_unixtime
- 年月日

实际开发中，可以使用int来保存一个unix时间戳,然后使用from_unixtime()进行转换

example

select current_date from dual;
select current_time() from dual;
select current_timestamp() from dual;

create table mes(id int,content varchar(30),sendtime datetime);
insert into mes
values (1, '北京新闻', current_timestamp());
select * from mes;
insert into mes
values (2, '北京新闻', now());
-- 显示新闻，发布日期只显示日期
select id,content,date(sendtime)from mes;
-- 10min内发布的新闻
select * from mes where date_add(sendtime,interval 10 minute) >= now();
select * from mes where date_sub(now(),interval 10 minute) <= sendtime;
-- 2011-11-11 和 1990-1-1差多少天
select datediff('2011-11-11','1990-01-01') from dual;

select datediff(now(),'2001-01-07')/365 from dual;
select datediff(date_add('2001-01-07',interval 80 year),now())/365 from dual;
select timediff('10:11:11','06:10:10') from dual;

select year(now()) from dual;
select month(now()) from dual;
select day(now()) from dual;
select year('2013-10-10') from dual;
-- 返回1970-1-1 到现在的秒数
select unix_timestamp() from dual;
-- 把unix_timestamp()秒数，转成指定格式的日期
select from_unixtime(1618483484,'%Y-%m-%d') from dual;select from_unixtime(1618483484,'%Y-%m-%d') from dual;
select from_unixtime(1618483484,'%Y-%m-%d %H:%i:%s') from dual;select from_unixtime(1618483484,'%Y-%m-%d') from dual;

加密和系统函数

user()
- 查询用户
database()
- 数据库名称
md5(str)
- md5方式加密
password(str)
- 通常用于对mysql数据库的用户密码加密，mysql8没有这个方法

example

select user()
from dual;
select database();
select md5('root')
from dual;
create table users(
    id int,
    `name` varchar(32) not null default '',
    pwd char(32) not null default ''
);
insert into users values (10,'123',md5('ro'));
select * from users;
select * from users where name = '123' and pwd = md5('ro');
select password('123') from dual;
select * from mysql.user;

流程控制函数

if(expr1,expr2,expr3)
- 如果expr1为true，则返回expr2，否则返回expr3
ifnull(expr1,expr2)
- 如果expr1不为空null，返回expr1，否则返回expr2
select case when expr1 then expr2 when expr3 then expr4 else expr5 end;
- 如果expr1为true，则返回expr2，如果expr3为true返回expr4，否则返回expr5

example

select if(true, '北京', '上海');
select ifnull(null, '上海');
select ifnull('北京', '上海');
select case when true then 'jack' when false then 'tom' else 'marry' end;

select ename, if(comm is null, 0.0, comm) comm
from emp;
select ename, ifnull(comm, 0.0) comm
from emp;
select ename,
       (select case
                   when job = 'clear' then '职员'
                   when job = 'MANAGER' then '经理'
                   when job = 'salesman' then '销售人员'
                   else job end) as job
from emp;

分页查询

select ... limit start,rows
表示从start+1行开始取，取出rows行，start从0开始计算

example

select *
from emp
order by empno
limit 0,3;

limit 每页显示记录数*(第几页-1),每页显示数;

improve

-- 查找1992.1.1后入职的员工
select *
from emp
where hiredate >= '1992-01-01';
select ename, sal
from emp
where ename like 'S%';
select ename, sal
from emp
where ename like 'S%';
-- 第三个字符为大写O的所有员工姓名和工资
select ename, sal
from emp
where ename like '__O%';
-- 查询没有上级的雇员的情况
select *
from emp
where mgr is null;
select *
from emp
order by sal asc;
-- 按照部门号升序工资降序
select *
from emp
order by deptno, sal desc;
-- 显示每种岗位的雇员总书、平均工资
select count(*), job, avg(sal)
from emp
group by job;
-- 显示雇员总数，以及获得补助的雇员数
select count(*), count(comm)
from emp;

-- 统计没有获得补助的雇员数,**
select count(*), count(if(comm is null, 1, null))
from emp;
select count(*), count(*) - count(comm)
from emp;
-- 显示管理者的总人数
select distinct count(distinct mgr) from emp;
-- 显示雇员工资的最大差额
select max(sal) - min(sal) from emp;

顺序

group by , having , orderby,limit

select column1,column2,column3.. from table
group by column
having condition
order by column
limit start,rows;

example

-- 统计各个部门的平均工资，并且大于1000，并且按照平均工资从高到低排序
select avg(sal) as avg_sal, deptno
from emp
group by deptno
having avg_sal > 1000
order by avg_sal desc
limit 0,2;