mysql学习笔记20211117
一、创建emp表
create table emp(empno int primary key auto_increment,ename varchar(100),job varchar(20),sex varchar(10),mgr int,hiredate date,sal int,comm int,deptno int);
insert into emp values(1000,'马云','董事长','男',null,'1998-10-21',50000,null,10); insert into emp values(1001,'王明','总监','男',1000,'1999-5-3',30000,null,20);
insert into emp values(1002,'王军','经理','男',1001,'2005-8-10',15000,null,20);
insert into emp values(1003,'张杰','总监','男',1000,'2000-6-30',28000,null,30);
insert into emp values(1004,'张云','经理','女',1003,'2003-1-28',14500,null,30);
insert into emp values(1005,'王海涛','总监','男',1000,'1999-10-15',29400,null,40);
insert into emp values(1006,'赵强','经理','男',1005,'2001-8-8',13900,null,40);
insert into emp values(1007,'孙杰','员工','男',1002,'2008-3-26',8000,'1000',20);
insert into emp values(1008,'李然','员工','女',1002,'2005-4-21',10500,'1200',20);
insert into emp values(1009,'张佳林','员工','男',1006,'2003-10-1',11000,'800',40);
insert into emp values(1010,'孙冉','员工','女',1004,'2004-11-11',9500,'1000',30);
insert into emp values(1011,'王琳','员工','女',1004,'2007-9-10',7800,'1500',30);
insert into emp values(1012,'司浩然','员工','男',1006,'2009-12-3',6500,'700',40);
insert into emp values(1013,'何佳','员工','女',1004,'2010-2-28',6000,'900',30);
insert into emp values(1014,'田甜','员工','女',1004,'2011-3-9',5800,null,3);
二、创建dept表
字段deptno部门编号，dname部门名称，loc所在城市
create table dept(deptno int,dname varchar(30),loc varchar(30));
insert into dept values(10,'总裁办','杭州'),(20,'研发部','北京'),(30,'测试部','上海'),(40,'运维部','郑州'),(50,'市场部','深圳'),(60,'销售部','洛阳');
三、mysql函数；
字符串（文字）；
concat(字符串1，字符串2，字符串3)；//字符串拼接
select concat('你','好');
length（字符串)；返回字符串的长大
select length('你好');
substring(字符串，开始位置，截取的长度);截取指定位置的字符串
select substring('hello',3,2);
replace(字符串，被替换的原字符串，替换成对应文字)；字符串替换
select replace('hello','el','ok');
strcmp(字符串1，字符串2)；比较两个字符串的大小（按文字编码比较）
中文>英文>数字>符号
select strcmp('你好','hello');
练习
2.
select ceil(12.5);
select floor(12.5);
alter table emp rename emp001;
1.
alter table emp001 rename emp;
2.
select concat('好好学习','天天向上');
3.
select length('好好学习');
4.
select upper('Hello');
select lower('Hello');
5.1
select substring('好好学习天天向上',1,2);
5.2
select substring('好好学习天天向上',5,8);
5.3
select substring('好好学习天天向上',3,2);
5.4
6.1
select trim(' he llo ');
6.2
select ltrim(' he llo ');
6.3
select rtrim(' he llo ');
7.
select replace('abcdef','a','c');
select replace('abcdef','abc',123);
8.1
select strcmp('a','b');
8.2
select strcmp('b','b');
8.3
select strcmp('b','a');
9.1
select concat((select substring('he llo',1,2)),(select substring('he llo',5,3)));
9.2
select length((select substring(' hello ',2,5)));
[object Object]10.
select ceil(10.8);
select ceil(-10.8);
11.
select floor(10.8); select floor(-10.8);
12.
select mod(10,3);
13.
select rand();
14.
其他函数
sum
avg
count
max
min
11.
select sum(sal) from emp;
12
select avg(sal) from emp;
13
select count(empno) from emp;
14
select count(empno) from emp where comm!=0;
15.
select max(sal) from emp;
16.
select min(sal) from emp;
17.
select * from emp001 where !ifnull(comm,'没有奖金');
18.
select * from emp where comm>0;
19
select ifnull(comm,'没有奖金') from emp001;
完
