SQL陳述句的一些應用陳述句

show databases;
show variables like '%char%';
--  創建資料庫的sql
create database if not exists study charset =utf8;
use develop;

show tables;

-- 洗掉資料庫的sql
drop database if exists develop;

-- 創建一個用戶
create user 'develop'@'%' identified by 'sa';
-- 洗掉一個用戶
drop user 'develop'@'%';
-- 給develop用戶賦予對develop資料的所有表的權限
grant all privileges on develop.* to 'develop'@'%' identified by 'sa';



insert into mysql.user(Host,User,Password,ssl_cipher, x509_issuer,x509_subject)
values('%', 'develop', password('sa'),'','','');

update mysql.user set Password=Password('1') where user = 'develop' and host ='%';

select host,user, Password from mysql.user;

delete from mysql.user where user = 'develop' and host ='%';

use study;

-- 創建表
create table student(
  sno int,
  name varchar(10),
  age int,
  sex char(1)
);
-- 洗掉表
drop table student;

-- 創建帶有自增主鍵的表
create table student(
  id int unsigned auto_increment,
  name varchar(10),
  age int,
  gender char(1),
  primary key (id)
);

-- 添加classname欄位
alter table study.student
  add (
    classname varchar(100)
  );

-- 修改gender列的長度
alter table study.student
  modify gender char(2);

-- 修改gender列為sex，長度增加為10
alter table study.student
    change gender sex char(10);

-- 洗掉列
alter table study.student
    drop classname;

-- 修改表名
alter table study.student
    rename to stu;

show create table stu;

-- 在stu表中插入一個學生記錄
insert into stu(name, age, sex) values('zhangsan', 20, 'male');

select * from stu;

insert into stu(name) values('lisi');
insert into stu(id, name) values(3, 'xiaoer');
insert into stu(id, name) values(3, 'xiaoming');
insert into stu(sex, name, age, id) values('male', 'xiaohong', 3, 4);

-- 在stu表中插入行的另外一種寫法
-- 不建議使用此方式：1) 列不明確，2)當表有新增的列時，會產生毀滅性的bug
insert into stu values (5, 'wangwu', 20, 'male');

alter table stu modify name varchar(20);

-- 通過update陳述句修改記錄
update stu set name='zhangsansan', age = 25 where id = 1;

-- between 條件
update stu set age = age + 1 where age between 10 and 20;

-- 上機練習
-- 創建一個學生表:
create table student(
  id char(6),
  name varchar(50),
  age int,
  gender varchar(50),
  tag varchar(2)
);

-- 插入測驗資料
INSERT INTO student VALUES('S_1001', 'liuYi', 35, 'male', '');
INSERT INTO student VALUES('S_1002', 'chenEr', 15, 'female', '');
INSERT INTO student VALUES('S_1003', 'zhangSan', 95, 'male', '');
INSERT INTO student VALUES('S_1004', 'liSi', 65, 'female', '');
INSERT INTO student VALUES('S_1005', 'wangWu', 55, 'male', '');
INSERT INTO student VALUES('S_1006', 'zhaoLiu', 75, 'female', '');

-- 1. 將為'S_1003'的學生的年齡改為25
update study.student set age = 25 where id = 'S_1003';

-- 2. 將'wangwu'的性別改成NULL
update study.student set gender = null where name = 'wangWu' or id = 'S_1005';

-- 3. 將年齡大于等于35歲的人標記成'1'
update study.student set tag = '1' where age >= 35;

-- 4. 將年齡大于等于15歲小于35歲的人標記成'0'
update study.student set tag = '0' where age >= 15 and age < 35;

-- 洗掉陳述句
delete from study.student where id = 'S_1001' and gender = 'famale';
delete from study.student;

-- 截斷：清空student表的所有資料，無法恢復
truncate table student;

-- 查詢示例
-- 創建員工表
CREATE TABLE emp(
	empno		INT,/*員工編號*/
	ename		VARCHAR(50),/*員工名稱*/
	job		VARCHAR(50),/*作業職位*/
	mgr		INT,/*管理編號*/
	hiredate	DATE,/*入職時間*/
	sal		DECIMAL(7,2),/**薪酬/
	comm		decimal(7,2),/*獎金*/
	deptno		INT/*部門編號*/
);
drop table dept;
alter table dept default character set utf8;/*修改表編碼格式為utf8*/
alter table dept convert to character set utf8;/*修改列屬性的格式為utf8*/

SHOW FULL COLUMNS FROM dept;/*查看dept表的編碼格式*/
-- 創建部門表
CREATE TABLE dept(
	deptno		INT  ,/*部門編號*/
	dname		varchar(40) , /*部門名稱*/
	loc		varchar(40)   /*作業地*/
);
-- 插入員工表測驗資料
INSERT INTO emp VALUES (1009, 'zenganiu', 'chairman', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, 'liubei', 'manager', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, 'guanyu', 'manager', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, 'zhangfei', 'manager', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, 'zhugeliang', 'analyst', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, 'pangtong', 'analyst', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, 'daiyisi', 'salesman', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, 'yintianzheng', 'salesman', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, 'xiexun', 'salesman', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, 'weiyixiao', 'salesman', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, 'chengpu', 'clerk', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, 'huanggai', 'clerk', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, 'zhoutai', 'clerk', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, 'ganning', 'clerk', 1013, '2000-12-17', 8000, NULL, 20);

-- 插入部門表測驗資料
INSERT INTO dept (deptno,dname,loc)VALUES (10, '企劃部', '北京');
INSERT INTO dept (deptno,dname,loc)VALUES (20, '公關部', '上海');
INSERT INTO dept (deptno,dname,loc)VALUES (30, '銷售部', '廣州');
INSERT INTO dept (deptno,dname,loc)VALUES (40, '財務部', '武漢');

-- 基礎查詢
select * from emp;
-- 指定列查詢
select empno, ename from emp;

-- distinct 對查詢出的記錄去重
select distinct job from emp;
select distinct deptno from emp;
select distinct job, deptno from emp;

-- 列運算
select *, sal * 1.5 from emp;
select *, sal + comm from emp;

-- null值轉換函式：ifnull(field, value)
select *, sal + IFNULL(comm, 0) from emp;
select empno,ename,job,ifnull(mgr,'boss'),hiredate,sal,comm,deptno from emp;

-- 字串連接
select concat('$', sal) from emp;
select concat(ename, job) from emp;
select concat('my name is ', ename, ' my job is ', job) from emp;

-- 給列起別名
select concat('my name is ', ename, ' my job is ', job) as name_job from emp;
select *, sal + IFNULL(comm, 0) as 獎金 from emp;
select ename as 姓名 from emp;

-- 條件查詢
select empno,ename,sal,comm from emp where sal > 10000 and comm is not null;
select empno,ename,sal,comm from emp where sal between 20000 and 30000;
select empno,ename,sal,comm from emp where job in ('chairman', 'manager');

-- 模糊查詢
SELECT * FROM emp WHERE ename LIKE 'z_______';
select * from emp where ename like '______';
select * from emp where ename like 'z%';
select * from emp where ename like '%z%';
select * from emp where ename like '_h%';
select * from emp where ename like '%n_';
use Student;
-- 排序,升序和降序，默認是升序，asc升序，desc降序
select * from emp order by empno;
select * from emp order by empno asc;
select * from emp order by empno desc;

-----復合函式
------count函式
select count(*)from emp;
select count(comm)from emp;
select count(*)from emp where sal >25000;
select count(*)from emp where sal +ifnull(comm)>25000;
select count(comm),count(mgr)from emp ;

/*sum函式*/
select sum(sal) from emp;
select sum(sal),sum(comm) from emp;
select sum(sal)+ifnull(comm,0)from emp;
select sum(sal)/count(*)from emp;
select sum(ename) from emp;

-------avg函式
select avg(sal)from emp;
select avg(ename)from emp;
---------max函式
select max(sal)from emp;
select max(ename)from emp;
select max(comm)from emp;

---------- -  min 函式
select min(sal)from emp;
select min(ename)from emp;
select min(comm)from emp;


-----------------union 函式
select max(sal)from emp;
union
select min(sal) from  emp;


select sum(sal),sum(comm)from emp;
union
select max(sal) from  emp;



select  * from emp;
union
select * from  emp;

select  * from emp;
union all
select * from  emp;

-----分組查詢 group by

select  deptno, count(*) from emp group by deptno;
select job ,count(*) from emp group by  deptno;
select job ,sum(sal) from emp group by  job;
select job ,sum(sal) ,avg(sal),max(sal),min(sal)from emp group by job;
---------------組條件
-----------------分組前條件

select deptno ,count(*)from  emp where  sal>15000 group by  deptno;
----------------分組后條件
select deptno ,count(*)from  emp group by deptno having count(*)>3;
select deptno ,sum(sal) from  emp group by deptno having count(*)>3;
------分組后排序
select deptno ,count(*)from  emp where  sal>15000 group by  deptno having  count(*)>1 order by  deptno desc ;



/*第五條資料開始，往后查4條資料*/
select * from emp order by  empno limit  5,4;

/*分頁查詢*/
select  *from  emp order by  empno limit 5;
select  *from  emp order by  empno limit 0,5;/*從0開始，包括第一行，到第一行*/

/*一頁5條，查詢第三頁的資料*/

select  *from emp order by empno limit 10,5;

select  * from emp order by empno limit 10 , 5;


/*
1、查詢出部門編號為30的員工
2、查詢出所有銷售員的姓名、編號和部門編號
3、找出獎金高于工資的員工
4、找出獎金高于工資30%的員工
5、找出部門編號為10的所有經理，和部門編號為20中所有銷售員的詳細資料
*/

select * from emp where deptno=30;
select   ename,empno,deptno from emp where job='salesman';

select * from emp where comm>sal;
select * from  emp where comm>sal * 0.30;
select * from emp  where deptno =10 and job='manager'



1、找出部門編號為10中的所有經理，部門編號為20中所有銷售員，還有即不是經理也不是銷售員但其工資大或大于或等于20000的所有員工詳細資料，
select  * from emp where deptno='10'and job='manager'
union
select * from emp where deptno ='20'and job='salesman'
union
select  *from emp where (job<>'manager'or job <>'salesman')and  sal >=200000;
2、無獎金或者獎金低于1000的員工

select *from emp where comm is null or comm<1000;
------3、查詢名字由8個字符組成的員工
  select  *from emp where ename like '________';
4、查詢2000年入職的員工

select  *from emp where year(hiredate)=2000;
5查詢所有員工的詳細資訊，用編號升序排序，

select  *from emp order by empno ;

use Student

-- 插入部門表測驗資料
INSERT INTO dept VALUES (10, '企劃部', '北京');
INSERT INTO dept VALUES (20, '公關部', '上海');
INSERT INTO dept VALUES (30, '銷售部', '廣州');
INSERT INTO dept VALUES (40, '財務部', '武漢');

INSERT INTO dept VALUES (10, '企劃部', '北京');
INSERT INTO dept VALUES (20, '公關部', '上海');
INSERT INTO dept VALUES (30, '銷售部', '廣州');
INSERT INTO dept VALUES (40, '財務部', '武漢');

/*我們要取出員工工號，姓名、部門名稱  、入職時間、等資訊；該如何取*/
select emp.empno , emp.ename, dept.dname,emp.hiredate from emp inner join  dept on emp.deptno=dept.deptno;

等價于
select  emp.empno,emp.ename,dept.dname,emp.hiredate from emp, dept where emp.deptno=dept.deptno;


select  *from emp order by empno ;

show full  columns from dept;


------left join
select empno,ename,dname,hiredate from emp left join  dept on emp.deptno =dept.deptno;
------ right join
select  empno ,ename,dname,hiredate from emp right join dept on emp.deptno =dept.deptno;


----子查詢：在另一條是 select 作為條件
select *from emp where deptno in (select  deptno from dept where loc='北京');
select *from emp where not  exists(select  deptno from dept where loc='北京'and dept.deptno=emp.deptno);

------1、查詢出部門內所有員工薪酬低于20000的員工的部門名稱及位置；
select dname ,loc from dept where  deptno in(select deptno from emp where  sal<20000);


select dname ,loc from dept where  deptno in(select deptno from emp group by emp.deptno having  max(sal)<20000) ;


2、查詢‘武漢’地區所有員工的作業（job）;

select job from  emp where  deptno in (select  deptno from dept where  loc='武漢');


3、查詢所有job 不為‘chairman’的員工的工號、姓名、部門名稱和辦公地址；

select  empno,ename ,dname,loc from emp left join  dept on  emp.deptno =dept.deptno where job  <>'chairman';
===更高效的寫法
select  empno,ename ,dname,loc from emp left join  dept on  emp.deptno =dept.deptno where not exists(select  1 from  emp e where e.empno=emp.empno and job ='chairman') ;