欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL语句的一些应用语句

程序员文章站 2022-06-15 19:15:38
show databases;show variables like '%char%';-- 创建数据库的sqlcreate database if not exists study charset =utf8;use develop;show tables;-- 删除数据库的sqldrop database if exists develop;-- 创建一个用户create user 'develop'@'%' identified by 'sa';-- 删除一个用户drop...
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') ;

本文地址:https://blog.csdn.net/weixin_43501566/article/details/108568912