sql 数据类型转换_入门数据分析(2.1)——MYSQL基础学习1

1.SQL基础

1.1SQL的分类

1.2导入数据

use test;source D:est.sql;

1.3表的复制

create table dept2 as select * from dept1;create table dept2 like dept1;

1.4查看建表语句

show create table ;

---

2.条件查询

2.1and语句

#找出工作岗位SALESMAN中薪资高于1500的员工，要求显示员工名，工作岗位，薪资select ename,job,sal from emp where job='SALESMAN' and sal>1500;

2.2or语句

#找出工作岗位是SALESMAN和MANAGER的员工，要求显示员工名，工作岗位，薪资select ename,job,sal from emp where job='SALESMAN' or job='MANAGER';

2.3in语句

#找出工作岗位是SALESMAN和MANAGER的员工，要求显示员工名，工作岗位，薪资select ename,job,sal from emp where job in ('SALESMAN','MANAGER');

2.4not in 语句

#找出除薪资是1500和3000之外的员工信息select * from emp where sal not in (1500,3000);

2.5like语句

#找出以S开头的员工信息 %代表0或多个字符  _代表1个字符select * from emp where ename like 'S%';#找出名字中第二个字母为“A”的员工信息select * from emp where ename like '_A%';#找出商品中含'_'的商品名称select ename from emp where ename like '%_%';

---

3 排序操作

3.1asc

#找出员工的姓名和薪资，要求薪资以升序排列select ename,sal from emp order by sal asc;

3.2desc

#找出员工的姓名和薪资，要求薪资以降序排列select ename,sal from emp order by sal desc;

3.3多字段排序

select ename,sal from emp order by sal asc,ename desc;

---

4单行处理函数/数据处理函数

4.1substr

#找出员工姓名首字母是A的员工信息，要求显示员工名和薪资select ename,sal from emp where ename like 'A%';#方法一select ename,sal from emp where substr(ename,1,1)='A';#方法二

4.2length

#找出名称长度大于5的员工信息，要求显示员工名和薪资select ename,sal from emp where length(ename)>5;

4.3trim

#找出工作岗位是manger的员工信息select * from emp where job=' manger';select * from emp where job=trim(' manger');

4.4round

select round(123.45); #123select round(123.59); #124select round(123.59,1); #123.6select round(123.59,-1); #120

4.5 rand

select rand() ;select round(rand()*100);

4.6case...when...then...else...end

#如果job为manger薪水上涨10%，如果job为SALESMAN工资上涨10%select ename,sal,job,  (case job   when 'manger' then sal*1.1   when 'salesman' then sal*1.5      else sal   end) as newsal from emp;

4.7 ifnull (空值处理函数)

#查询员工年薪，显示员工名，月薪，月补助，以及年薪select ename,sal,comm,(sal+ifnull(comm,0))*12 as yearsal from emp;

4.8date

#str_to_date 将字符串类型转换为日期类型insert into t_student(sno,sname,birth) values(2,'lisi','1980-08-01');insert into t_student(sno,sname,birth) values(3,'wangli',str_to_date('1980-09-12','%Y-%m-%d'));#date_format 将日期类型转换为字符串类型select ename,date_format(hiredate,'%Y/%m/%d') as hiredate from emp;

---

5分组/多行处理函数

5.1sum/avg/max/min

select sum(sal) from emp;select avg(sal) from emp;select max(sal) from emp;select min(sal) from emp;

5.2count

select count(comm) from emp;#计算非空字段数select count(*) from emp;#计算总字段数