每天记录一点数据库知识，持续更新~
一。2023-06-06
1.软件包
mysq-server mysql
2.启动数据库服务
systemctl start mysqld
systemctl enable mysqld (开机自启)
3.查询端口和进程
ps -C mysqld 查询进程
ss -nutlp | grep 3306 查看端口
nerstat -nutlp | grep mysqld 查看进程的状态
4.3306端口是mysql默认使用的端口，33060是mysql shell默认使用的端口，主要用于
执行各种数据库管理工作
5.进入数据库
mysql(无用户无密码)
mysql -uroot -proot -hlocalhost zabbix 指定用户密码和登录主机 数据库名字
6.退出数据库
exit/quit
7.查看数据库版本
select version();
8.查看登录的用户和客户端地址
select user();
9.查看所有的库
show databases;
10.查看当前在哪个库
select database();
11.进入某个库
user zabbix;
12.显示库里所有的表
show tables;
二。筛选条件-2023-06-07
1.查看一个表头(user表)
select name from user;
2.查看多个表头
select name,id from user;
3.查看root的所有信息
select * from user where name="root";
4.查看第三行的行号，用户名,uid
select name,id from,uid user where id=3;
5.查看前两行的信息
select * from user where id<=2;
6.查看uid大于5的信息
select * from user where uid>5;
7.查看uid不等于gid的信息
select * from user where uid != gid;
8.查看uid表头的值是（1，2，3，4）中的任意一个
select uid,name from user where uid in (1,2,3,4);
9.查看shell 表头的值不是("/bin/bash","sbin/nologin")
select shell,name from where shell not in ("/bin/bash","sbin/nologin");
10.查看id在10-20之间(包含10 20)
select id,name from user where id between 10 and 20;
11.找名字必须是三个字符的 _表示单个字符 %零个或多个
select name from user where name like"_ _ _";_中间没有间隔
12.找名字以a开头的
select name from user where name like"a%"
13.找名字里有数字的 []匹配任意字符
select name from user where name regexp "[0-9]";
14.查看名字以数字开头的 ^
select name from user where name regexp "^[0-9]";
15.查看名字以数字结尾的 $
select name from user where name regexp "[0-9]$";
16.查看名字以r开头或者t结尾的
select name from user where name regexp "^r|t$";
17.查看名字以r开头t结尾的
select name from user where name regexp "^r.*t$";
三。2023-06-08
18.逻辑与and 逻辑或or 逻辑非 not
select name,shell from tarena.user where shell != "/bin/bash";
select name,shell from tarena.user where not shell = "/bin/bash";
select name , uid from tarena.user where name="root" and uid = 1;
select name , uid from tarena.user where name = "root" or name = "bin" or uid = 1;
19.提高优先级（） 优先级()>and>or
select 2 + 3 * 5 ; 17
select (2 + 3 ) * 5 ; 25
select name , uid from tarena.user where name = "root" or name = "bin" and uid = 1 ;
select name , uid from tarena.user where (name = "root" or name = "bin") and uid = 1 ;
20.=等于 ！= 不等于
select name from tarena.user where name="apache" ;
select name , shell from tarena.user where shell != "/bin/bash";
21.is null 空 is not null非空
select id , name from tarena.user where name is null;
select id , name from tarena.user where name is not null;
22.定义别名as 去重distinct 合并concat
select name as 用户名 , homedir 家目录 from tarena.user;
select concat(name , "-" , uid , "-" , gid) as 用户信息 from tarena.user where uid <= 5;
select distinct shell from tarena.user where shell in ("/bin/bash","/sbin/nologin") ;
23.字符函数--//LENGTH(str) 返字符串长度，以字节为单位
select name , length(name) as 字节个数from tarena.user where name = "root" ;
24.字符函数--//CHAR_LENGTH(str) 返回字符串长度，以字符为单位
select name from tarena.employees where employee_id = 3 ;
25.//UPPER(str)和UCASE(str) 将字符串中的字母全部转换成大写
select upper(name) from tarena.user where uid <= 3 ;
select ucase(name) from tarena.user where uid <= 3 ;
26.//LOWER(str)和LCASE(str) 将str中的字母全部转换成小写
select lower("ABCD") ;
select lcase("ABCD") ;
27.//不是输出员工的姓 只输出名字
select substr(name,2,3) from tarena.employees where employee_id <= 3 ;
28.//INSTR(str,str1) 返回str1参数，在str参数内的位置
select instr(name,"a") from tarena.user where uid <= 3 ;
29.//查找名字里有英字及出现的位置
select name , instr(name,"英") from tarena.employees;
30.//TRIM(s) 返回字符串s删除了两边空格之后的字符串
select trim(" ABC ");
31.数学函数之--/ABS(x) 返回x的绝对值
select abs(-11);
32.//PI() 返回圆周率π，默认显示6位小数
select pi() ;
33.//MOD(x,y) 返回x被y除后的余数
select mod(10,3);
34.//输出1-10之间的偶数uid号
select name , uid from tarena.user where uid between 1 and 10 and mod(uid,2) = 0 ;
35.CEIL(x)、CEILING(x) 返回不小于x的最小整数 (x 是小数)
select ceil(9.23);select ceiling(9.23);
36.FLOOR(x） 返回不大于x的最大整数 (x 是小数)
select floor(9.23);
2023-06-09
37.日期函数
select curtime();//获取系统时间
select now() ;//获取系统日期+时间
select year(now()) ; //获取系统当前年
select month(now()) ; //获取系统当前月
select day(now()) ; //获取系统当前日
select hour(now()) ; //获取系统当前小时
select minute(now()) ; //获取系统当分钟
select second(now()) ; //获取系统当前秒
select time(now()) ;//获取当前系统时间
select date(now()) ; //获取当前系统日期
select curdate();//获取当前系统日志
select dayofmonth(curdate());//获取一个月的第几天
select dayofyear(curdate());//获取一年中的第几天
select monthname(curdate());//获取月份名
select dayname(curdate());//获取星期名
select quarter(curdate());//获取一年中的第几季度
select week(now());//一年中的第几周
select weekday(now())；//一周中的周几
38.聚集函数
//输出3号员工2018每个月的基本工资
select basic from tarena.salary where employee_id=3 and year(date)=2018;
//avg(字段名) //计算平均值
select avg(basic) from tarena.salary where employee_id=3 and year(date)=2018;
//sum(字段名) //求和
select sum(basic) from tarena.salary where employee_id=3 and year(date)=2018;
//min(字段名) //获取最小值 //max 最大值
select min(basic) from tarena.salary where employee_id=3 and year(date)=2018;
//count(字段名) //统计字段值个数
select count(bonus) from tarena.salary where employee_id=3 and year(date)=2018 and bonus<3000;
39.if函数
if(条件,v1,v2) 如果条件是TRUE则返回v1，否则返回v2
ifnull(v1,v2) 如果v1不为NULL，则返回v1，否则返回v2
select name , uid ,if(uid < 1000 , "系统用户","创建用户") as 用户类型 from tarena.user;
select name 姓名, ifnull(homedir,"NO home")as 家目录 from tarena.user;
40.case函数
如果字段名等于某个值，则返回对应位置then后面的值并结束判断，
如果与所有值都不相等，则返回else后面的结果并结束判断
select dept_id, dept_name,case dept_name
when '运维部' then '技术部门'
when '开发部' then '技术部门'
when '测试部' then '技术部门'
else '非技术部门'
end as 部门类型 from tarena.departments;
41.分组 group by
//统计每个部门的总人数
select dept_id , count(name) from tarena.employees group by dept_id ;
42.排序 order by xx asc 升序 desc 降序
#按照uid降序排序
select name , uid from tarena.user where uid is not null and uid between 100 and 1000 order by uid desc;
43.过滤练习
select 表头名 from 库.表 where 筛选条件 having 筛选条件;
查找部门人数少于10人的部门名称及人数
select dept_id , count(name) as numbers from tarena.employees group by dept_id having numbers < 10;
44.分页练习
SELECT语句 LIMIT 数字; //显示查询结果前多少条记录
SELECT语句 LIMIT 数字1,数字2; //显示指定范围内的查询记录
数字1 表示起始行 （0表示第1行） 数字2表示总行数
//仅仅显示查询结果的第1行 到 第3 （0 表示查询结果的第1行）
select * from user where shell is not null limit 0,3;
45.管理表记录
插入 insert intotarena.user values(40,"jingyaya","x",1001,1001,"teacher","/home/jingyaya","/bin/bash");
修改 update tarena.user set comment=NULL where id <= 10 ;
删除 delete from tarena.user where id <= 10 ;
2023-06-12
46.内连接 等值查询
