mysql -韩

创建表

create table 'goods' (id INT, goods_name VARCHAR(10),price DOUBLE);

如果加了NOT NULL 则不允许为空

create table 'goods' (id INT , goods_name VARCHAR(10),price DOUBLE NOT NULL);

设置默认值

create table 'goods' (id INT , goods_name VARCHAR(10),price DOUBLE NOT NULL DEFAULT 100);

添加数据(多条主要符号)

INSERT INTO 'goods' (id,goods_name,price)

VALUES(10, '华为手机’,200),

               (11, '华为手机1’,2001);

修改表

update employee set salary =5000

 update employee set salary =3000 where user_name = '小妖怪’

update employee set salary = 3000,job = '出主意的‘’where user_name='老妖怪' 修改了两列的值

删除语句

delect from employee where user_name ='老妖怪'; 删除表中名称为‘老妖怪’的记录

查询语句select1

distinct 去重 

select * from student；

select ‘name’，english from student; 查询表中所有学生的姓名和对应的英语成绩

select distinct * from student; 过滤表中重复数据

查询语句 select2

select 'name' ,(chinese+english+math) from student ; 查询学生的总分

select 'name' ,(chinese+english+math) as total_score from student ;   使用别名

查询语句 select3

比较运算符：> < <= >= = <> != 大于 小于  等于 不等于

between...and... 显示在某一区间的值

IN(set)显示在in列表中的值，例：in(100,200)

like '张pattern' 模糊查询

not like 模糊查询

not null 判断是否为空

逻辑运算符：

and or not

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>90;

select * from student where english>chinese ;

select * from student where (chinese + english + math)>200 and math<chinese and 'name' like '韩%’；韩%表示名字以韩开头的，name加引号区分关键字，为了规避关键字，可以使用反引号解决比如有个表名为create

课堂练习

select * from student where english between 80 and 90; 或者  english>=80 and enlish <=90

select * from student where math in (89,90,91);

select * from student where  'name' like '李%';

select * from student where math >80 and chinese >80 ;

select 语句 order by 

select * from student order by math;对数学成绩排序后输出升序（默认）

select * from student order by math desc ; 数学成绩由高到低

select 'name' ,(chinese + math +english) as total_score  from student order by total_score desc; 对总分按从高到低的顺序输出，使用别名排序

select 'name' ,(chinese + math +english) as total_score from student where 'name' like '韩%’ order by total_score;对总分排序，无总分列，相当于显示总分列

select 函数 count：返回查询结果有多少行

select count(*) from student;

select count(*) from student where math >90;统计数学成绩大于90的学生有多少个

select count(*) from student where (math + english + chinese)>250

合计函数-sum sum函数放回满足where条件的行的和，一般使用在数值列

select sum(math) from student; 统计班级数学总成绩

select sum(math) as math_total_score,sum(english),sum(chinese) from student;统计一个班级语文，英语，数学，各科的总成绩

select sum(math+english+chinese) from student;统计一个班级的总分

avg函数,max,min

select avg(math) from student;

select max(math+english+chinese),min(math+english+chinese) from student;

分组查询

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的部门号和它的平均工资

1.显示各个部门的平均工资 和部门号

2.在1的结构基础上，进行过滤，保留avg(sal) < 2000

3.也可以使用别名过滤

select avg(sal),deptno from emp group by deptno having avg(sal) < 2000; 

select avg(sal)as avg_sal,deptno from emp group by deptno having avg_sal < 2000; 

字符串函数

select concat (ename,'job is ', job) from emp;连接字符串，将多个列拼接为一列

select instr ('jsndhunping','ping') from dual;返回出现的位置

select ucase(ename) from emp;转换为大写

select lcase(ename) from emp;转换为小写

select left(ename,2) from emp;从左边起取2个字符

select length(ename) from emp;string长度(按照字节)

select ename,replace(job,'MANAGER‘,'经理')；展示了两列，其中一列如果是manager就替换为经理

select strcmp('hsp','hsp') from dual;逐字符比较两字串的大小 相同0

select substring (ename,1,2) from emp;从ename列的第一个位置开始取出2个字符（从1开始计算）

select format(78.3456,2) from dual;保留2位小数

select rand() from dual;返回随机数 （加个种子数，固定随机）

查询加强

select * from emp where hiredate > '1992-01-01' ;如何查找1992.1.1后入职的员工

select ename ,sal from emp where ename like 's%' ;如何显示首字母位s的员工姓名和工资（%表示0到多个任意字符，_表示单个任意字符）

select ename ,sal from emp where ename like '___s' ;显示第三个字符位大写o的所有员工的姓名和工资

select * from emp where mgr is null;如何显示没有上级的雇员的情况

desc emp;查询表结构

select * from emp order by sal asc; 如何按照工资的从低到高顺序（升序），显示雇员的信息

select * from emo order by deptno asc,sal desc ;按照部门号升序而雇员的工资降序排列，显示雇员信息

分页查询

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

select * from emp order by empno limit 每页显示的记录数*（第几页-1）,每页显示记录数;

分组增强

select count(*),avg(sal),job from emp group by job; 显示每种岗位的雇员总数，平均工资

select count(*) count(comm) from emp 显示雇员的总数，以及获得补助的雇员数,count某一列，如果为空不被统计进去

select count(*),count(if(comm is null,1 null)) from emp;显示雇员的总数,以及统计没有获得补助的雇员数

select count(*),count(*)-count(comm) from emp;

select count(distinct mgr) from emp;显示管理者的总人数(去重)

select max(sal)-min(sal) from emp;显示雇员工资的最大差额

顺序

select deptno ,avg(sal) as avg_sal

from emp

group by deptno

having avg_sal>1000

order by avg_sal desc

limit 0,2

统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录limit

按照顺序写

多表查询

select *

from emp,salgrade

where sal between losal and hisal; 显示各个员工的姓名，工资，及其工资的级别 

自连接

select worker.ename as '职员表',boss.ename as '上级名'

from emp.worker , emp.boss

where worker.mgr = boss.empno;

显示公司员工名字和他的上级的名字

员工名字在emp，上级名字也在emp，不用as也可取别名

多行子查询

如何显示与smith同一部门的所有员工

先查询到smith的部门号得得到

把上面的select语句当做一个子查询来使用

select * 

from emp

where deptno = (select deptno from emp where ename = 'smith')

查询10号部门有哪些工作 

select distinct job from emp where deptno = 10;

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno =  10) and deptno <> 10

子查询临时表

select goods_id,ecs_goods.cat_id,goods_name,shop_price

from (

select cat_id,max(shop_price) as max_price

from ecs_goods

group by cat_id

)temp,ecs_goods

where temp.cat_id = ecs_goods.cat_id

and temp.max_price = ecs_goods.shop_price

all和any的使用

select ename,sal,deptno

 from emp

where sal > all (

select sal from emp where deptno = 30 )显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号

select ename,sal,deptno

 from emp

where sal > all (

select max(sal) from emp where deptno = 30 )

多列子查询

如何查询与smith的部门和岗位完全相同的所有雇员（并且不含smith本人）

1.得到smith的部门和岗位

select deptno,job

from emp

where ename='smith'

2.把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配

select  * from emp

where (deptno,job) =(

select deptno,job

from emp

where ename='smith'

) and ename != 'smith'

请查询 和宋江数学，英语，语文成绩完全相同的学生

select * from student where(math,english,chinese)=(

select math,english,chinese from student where 'name'='宋江'

)

子查询练习

查找每个部门工资高于 本部门平均工资的人的资料（或者改为最高工资）

1.先得到每个部门的部门号和对应的平均工资

select deptno,avg(sal) as avg_sal from emp group by deptno

2.把上面的结果当做子查询，和emp进行多表查询

select ename ,sal, temp.avg_sal,emp.deptno

from emp,（select deptno,avg(sal) as avg_sal from emp group by deptno）tmp

where emp.deptno = temp.deptno and emp.sal > tem.avg_sal

查找每个部门的信息（包括：部门号，编号，地址）和人员数量

1.部门号，编号，地址 来自dept表

select dname, dept.deptno,loc,per_num

from dept,(select  count(*) as per_num deptno from emp group by deptno) tmp

where tmp.deptno =dept.deptno

说明：tmp.* 表示把该表的所有列都显示出来，但多个表中列不重复时才可以直接写列名

索引

create index empno_index on emp (empno) 创建一个索引 （普通索引）占用磁盘空间，查询速度快

show index from t25;查询表是否有索引

create unique index id_index on t25 (id);添加唯一索引

alter table t26 add primary key (id)添加主键索引，后面添加

drop index id_index on t25；删除索引

alter table t26 drop primary key;删除主键索引

视图

creat view emp_view03

as

select   empno,ename,dname,grade from emp，dept，salgrade where emp.deptno = dept.deptno and (sal between losal and hisal)

练习

select dname from dept

select ename (sal+ IFNULL(comm,0)*13  as '年收入' from emp

练习4

13 select ename from emp where ename not like '%R%'; 不包含R

14 select left(ename,3) from emp  显示姓名前三个字符

15 select replace (ename,'A','a') from emp用a替换所有A

15 select ename hiredate  from emp where DATE_ADD(hiredate,INTERVAL 10 YEAR) <=NOW 显示满10年服务年限的员工的姓名和受雇日期

18 select ename hiredate from emp order by hiredate 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面

19 select ename,job,sal from emp order by job desc,sal 显示所有员工的姓名，工作和薪金，按工作降序排序，若工作相同则按薪金排序