从零开始的数据库_MySQL篇 ( 02 )

MySQL

承接从零开始的数据库_MySQL篇 ( 01 )

MySQL条件筛选

使用tarena.sql数据库作为案例

基础查询

• **语法格式1 : select 字段列表 from 库名.表名； **

mysql> select name from  tarena.user;        //查看一个表头
mysql> select name ,uid from  tarena.user;    //查看多个表头
mysql> select *  from  tarena.user;        //查看所有表头

• 语法格式2 : select 字段列表 from 库名.表名 where 筛选

mysql> select *  from  tarena.user where  name = “root”;        //查找root用户信息 

• 尾缀添加 \G 显示的数据变成竖着显示 , 数据过长使用

• 尾缀添加 ； 显示的数据横着显示.

• \c 终止sql命令

• 默认命令不支持tab键补全

• SQL命令不区分字母大小写(密码 , 变量值除外)

1. 数值比较

• 比较符
  • = != > >= < <=

  mysql> select  id,name,uid,gid  from  tarena.user where  id <= 3;
  mysql> select  id,name,uid,gid  from  tarena.user where  uid = gid;

2. 范围匹配

• in (列表值) //在…里
• not in (列表值) //不在…里
• between 数值1 and 数值2 //在…之间

mysql> select name , uid  from  tarena.user where  uid  in (1 , 3 , 5 , 7);
mysql> select name , shell  from  tarena.user where  shell  not in ("/bin/bash","/sbin/nologin");
mysql> select id , name , uid  from  tarena.user where id  between 10 and 20 ;//id表头的值 在 10  到  20 之间即可,包括10和20本身

3. 模糊匹配

• where 字段名 like “表达式”
• 通配符
  • _表示一个字符
  • % 表达零个到多个字符

mysql> select name from  tarena.user where  name like "_ _ _ _"; //找名字必须是4个字符的（没有空格挨着敲）

mysql> select name from  tarena.user where  name like "a%";  //找名字以字母a开头的（没有空格挨着敲）

mysql> select name from  tarena.user where  name like "%_ _ _ _%";（没有空格挨着敲）
mysql> select name from  tarena.user where name  like "_ _%_ _";（没有空格挨着敲）
mysql> select name from  tarena.user where name  like "_ _ _ _%";（没有空格挨着敲）//查找名字至少是4个字符的表达式

4. 正则匹配

• where 字段名 regexp ‘正则表达式’
• 常用的正则:
  • ^ 匹配行首
  • $ 匹配行尾
  • [] 匹配范围内任意一个
  • * 前边的表达式出现零次或多次
  • | 或者
  • . 任意一个字符

mysql> select name from  tarena.user where name regexp "[0-9]"; //查看名字里有数字的
mysql> select name from  tarena.user where name regexp "^[0-9]"; //查看名字以数字开头
mysql> select name from  tarena.user where name regexp "[0-9]$"; //查看名字以数字结尾
mysql> select name from  tarena.user where name regexp "^r|t$"; //查看名字以r开头或t结尾
mysql> select name from  tarena.user where name regexp "^r.*t$"; //名字r开头t结尾

5. 逻辑比较

• 多个筛选条件
  • and 多个判断条件必须同时成立
  • or 多个判断条件其中某个条件成立即可
  • not 取反

mysql> select name , uid from tarena.user where name="root" and uid = 0;

6. 优先级

• ()可以提高运算优先度

mysql> select   (2  + 3 ) * 5 ; //先加法再乘法
select name , uid from tarena.user  
where (name = "root" or name = "bin")  and uid = 1 ;  

7. 字符比较

• 字符使用双引号表示

mysql> select  name  from  tarena.user where name="apache" ;

• 空 is null 表头下没有数据
• 非空 is not null 表头下有数据
• mysql服务 , 使用关键字 null 或 NULL 表示表头没有数据

mysql> insert into tarena.user(id,name) values(71,"");    //零个字符
mysql> insert into tarena.user(id,name) values(72,"null");//普通字母
mysql> insert into tarena.user(id,name) values(73,NULL);  //表示空
mysql> insert into tarena.user(id,name) values(74,null);  //表示空

别名/去重/拼接

1. 别名 as

• 定义别名 as 或 空格

mysql> select name , homedir  from tarena.user;
mysql> select name as 用户名 , homedir  家目录 from tarena.user;

2. 去重 distinct

• select distinct显示去重后的结果

mysql> select distinct shell  from  tarena.user  where shell in ("/bin/bash","/sbin/nologin") ;

3. 拼接 concat

• select concat(表头名1 , 表头名2) 显示拼接表头1,2后的结果

mysql> select concat(name,"-",uid) as 用户信息 from tarena.user where uid <= 5;
+--------------+
| 用户信息     |
+--------------+
| root-0       |
| bin-1  


mysql>  select concat(name , "-" , uid)  as  用户信息  from tarena.user where uid <= 5;       //2列拼接

mysql>  select concat(name , "-" , uid , "-" , gid)  as  用户信息  from tarena.user where uid <= 5;    //多列拼接

函数

什么是MySQL函数

• MySQL服务自带的命令

• 提供了各种功能的函数

• 比如统计函数 , 处理日常的函数,处理字符函数 , 数学函数

函数的组成

• 函数名()

version()  
user()  
databases()

函数的用法

• 单独使用 select now( )；
• 函数套用函数 select year(now())
• 使用函数处理表头下的数据 select count(name) from user

常用函数的使用

1. 字符函数

• length(表头名) //返回字符串长度 , 以字节为单位

mysql> select name , length(name) as 字节个数from tarena.user where name = "root" ;

• char_length(表头名) //返回字符串的长度 , 以字符为单位

mysql> select name , char_length(name) from tarena.employees where employee_id = 3 ;

• upper(表头名) 和 ucase(表头名) //将字符串中的字母全部转换成大写

mysql> select upper(name) from  tarena.user where uid <= 3 ;

• lower(表头名) 和 lcase(表头名) //将字符串中的字母全部换成小写

mysql> select lower("ABCD") ;

• substr(s, start ,end) //从s的start位置开始取出到end长度的子串

mysql> select substr(name,2,3) from  tarena.employees where employee_id <= 3 ;

• instr(表头名, 字符) //返回字符 , 在表头名数据中的位置

mysql> select instr(name,"a") from  tarena.user where uid <= 3 ;

• trim(s) //返回字符串s删除了两边空格之后的字符串

mysql> select trim("  ABC  ");

2. 数学函数

处理数字或数值类型的表头

• abs(x) //返回x的绝对值

mysql> select abs(11);

• pi() //返回圆周率 , 默认显示6位小数

mysql> select pi() ;

• mod(x,y) //返回x被y除后的余数 , 模

mysql> select mod(10,3);
//输出1-10之间的偶数uid号
mysql> select name , uid from user where uid between 1 and 10 and  mod(uid,2) = 0 ;

• ceil(x) , ceiling(x) //返回不小于x的最小整数 (x是小数)

mysql> select ceil(3.3333);

• floor(x) //返回不大于x的最大整数(x是小数)

mysql> select floor(3.3333);

3. 日期函数

对存储日期时间数据的表头做处理

• now() //获取日期和时间

• curtime() //获取时间

  • hour() //小时 minute() //分钟 second() //秒

• curdate() //获取日期

  • year() //年 month() //月 day() //日 week() //周
  • date() //日期 weekday() //周几 dayname() //星期名
  • monthname() //月份名 dayofyear //一年中的第几天
  • dayofmonth() //月中的第几天 time() //日中的时间

4. 聚集函数

数据统计命令,对数据做统计 , 处理表头下存储的数据

• avg(字段名) //计算平均值

mysql> select avg(basic) from salary where employee_id=3 and year(date)=2018;

• sum(字段名) //求和

mysql> select sum(basic) from salary where employee_id=3 and year(date)=2018;

• min(字段名) //获取最小值

mysql> select min(basic) from salary where employee_id=3 and year(date)=2018;

• max(字段名) //获取最大值

mysql> select max(basic) from salary where employee_id=3 and year(date)=2018;

• count(字段名) //统计字段值个数

mysql> select count(basic) from salary where employee_id=3 and year(date)=2018;

5. 数学函数

• 对行中的列做计算

`+`          加法          uid + gid
`-`          减法          uid - gid
`*`          乘法          uid * gid
`/`          除法          uid / gid
`%`          求模          uid % gid
`()`    提高优先级          (uid + gid) / 2

6. 判断函数

• if函数

  • if(条件 , v1 , v2) //如果条件是TRUE则返回v1 , 否则返回v2

mysql> select  if(1 = 2 , "a","b");

• ifnull函数

  • ifnull(v1 , v2) //如果v1不为NULL , 则返回v1 , 否则返回v2

 mysql> select  ifnull("abc","xxx");
 mysql> select  ifnull(null,"xxx");

• case函数

  • 语法 : 如果字段名等于某个值 , 则返回对应位置then后面的结果 , 如果与所有值都不相等 , 则返回else后面的结果.
  • case 表头名 when 值1 then 输出结果 when 值2 then 输出结果 else 输出结果 end

mysql> select  * from tarena.departments;
select dept_id, dept_name,
case dept_name
when '运维部' then '技术部门'
when '开发部' then '技术部门'
when '测试部' then '技术部门'
else '非技术部门'
end as  部门类型   from  tarena.departments;

​ case when 判断条件 then 输出结果 when 判断条件 then 输出结果 else 输出结果

mysql> select dept_id,dept_name,
case
when dept_name="运维部"  then "技术部"
when dept_name="开发部"  then "技术部"
when dept_name="测试部"  then "技术部"
else "非技术部"
end as 部门类型  from  tarena.departments;

mysql> select dept_id,dept_name,
    -> case
    -> when dept_name in ("运维部","开发部","测试部") then "技术部"
    -> else "非技术部"
    -> end as 部门类型  from  tarena.departments;