SQL语句的一些应用语句 介绍三个开发技术小知识点 数据库系统概论(1) Mysql备份和恢复的类型,备份恢复的几种方法! --个人整理 MySql数据库基本操作(一) 前八月融合创新提升服务,大数据搭建信息网 长三角税收一体化按下“快进键” 一些建议:给当年刚做程序员的我 谁是物联网江湖明日之星?答案是新CIoT 企业信用有效“变现” 江苏银行物联网金融规模达185亿 2020年中国5G物联网行业:中国通信运营商5G投资达1.23万亿元-可下载 搭建 GFS 分布式文件系统-----实操 排障集锦:九九八十一难之第十五难!-------------史上最全rsync报错详解 docker系列1---docker隔离和限制技术 Linux系统安全及应用加固———最适合新手学,新手都能看懂!超详细的理论+超详细的实验!呕心沥血之作完成! LAMP架构集成ownCloud搭建私有云盘 Shell脚本正则表达式(一) Linux第一人称侵入进程的好地方 Windows DOS常用命令(持续更新完善) 大数据驱动新材料研发 变革从内而发,一站式大数据平台和多模数据库雄起 雕佛像、打棒球、开飞机、去北极,这个90后程序员有点不一样 12项特色功能构建施工现场“物联网” 400亿美元!英伟达收购Arm 软银套现离场物联网梦醒 卫星物联网再次引发热议,原因在这里 信用飞:航旅行业数字化前景可期,大数据预防砍头息乱象 大数据加持自动驾驶产业发展 上海智能网联汽车公共数据中心揭牌 恒久科技(002808.SZ):子公司闽保信息与鸿达兴业签署智能制造及大数据平台综合应用项目战略合作框架协议 工业平板电脑如何在物联网中发挥作用? 400亿美元!英伟达收购Arm 软银套现离场物联网梦醒 “数字心脏”动态解析消费密码——国家级消费市场大数据联合实验 重庆:大数据智能化牵引产业结构调整质效提升 分析物联网技术在智能家居中的应用场景 国家级消费市场大数据联合实验室在上海先行先试 山西吕梁培训搬迁户成为大数据标注员 昔日贫困户,玩转高科技 知乎热议:2020年程序员是否过剩? 柯建东:宁波抢抓工业物联网新机遇,要做好四个平台建设 旷视:城市物联网的实现,是从超级应用到操作系统的沉淀过程 排障集合———忘记原密码,如何重新修改MySQL登入密码 【Redis】主从复制 网易2021批笔试题解 msf工具之木马程序制作以及伪装 【JAVA】滴滴-2021校招在线笔试-DE数据开发试卷-0913 机器学习13 -- 无监督学习之自监督 2020年全国数学建模大赛 B题部分代码和模型的建立 jumpserver安装(centos) Redis 知识点总结 通过RabitMQ实现分布式事务 高焕堂《嵌入式UML设计》读书笔记_第一章 2020年中国5G在物联网行业:中国通信运营商5G投资达1.23万亿元(可下载) 面试题精选:数据伪造
您的位置:首页 >前端 >

SQL语句的一些应用语句

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 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.studentadd (classname varchar(100));-- 修改gender列的长度alter table study.studentmodify gender char(2);-- 修改gender列为sex,长度增加为10alter table study.studentchange gender sex char(10);-- 删除列alter table study.studentdrop classname;-- 修改表名alter table study.studentrename 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)当表有新增的列时,会产生毁灭性的buginsert 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'的学生的年龄改为25update study.student set age = 25 where id = 'S_1003';-- 2. 将'wangwu'的性别改成NULLupdate 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(empnoINT,/*员工编号*/enameVARCHAR(50),/*员工名称*/jobVARCHAR(50),/*工作职位*/mgrINT,/*管理编号*/hiredateDATE,/*入职时间*/salDECIMAL(7,2),/**薪酬/commdecimal(7,2),/*奖金*/deptnoINT/*部门编号*/);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(deptnoINT,/*部门编号*/dnamevarchar(40) , /*部门名称*/locvarchar(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;unionselect min(sal) fromemp;select sum(sal),sum(comm)from emp;unionselect max(sal) fromemp;select* from emp;unionselect * fromemp;select* from emp;union allselect * fromemp;-----分组查询 group byselectdeptno, count(*) from emp group by deptno;select job ,count(*) from emp group bydeptno;select job ,sum(sal) from emp group byjob;select job ,sum(sal) ,avg(sal),max(sal),min(sal)from emp group by job;---------------组条件-----------------分组前条件select deptno ,count(*)fromemp wheresal>15000 group bydeptno;----------------分组后条件select deptno ,count(*)fromemp group by deptno having count(*)>3;select deptno ,sum(sal) fromemp group by deptno having count(*)>3;------分组后排序select deptno ,count(*)fromemp wheresal>15000 group bydeptno havingcount(*)>1 order bydeptno desc ;/*第五条数据开始,往后查4条数据*/select * from emp order byempno limit5,4;/*分页查询*/select*fromemp order byempno limit 5;select*fromemp order byempno 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 * fromemp where comm>sal * 0.30;select * from empwhere deptno =10 and job='manager'1、找出部门编号为10中的所有经理,部门编号为20中所有销售员,还有即不是经理也不是销售员但其工资大或大于或等于20000的所有员工详细资料。select* from emp where deptno='10'and job='manager'unionselect * from emp where deptno ='20'and job='salesman'unionselect*from emp where (job<>'manager'or job <>'salesman')andsal >=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 joindept on emp.deptno=dept.deptno;等价于selectemp.empno,emp.ename,dept.dname,emp.hiredate from emp, dept where emp.deptno=dept.deptno;select*from emp order by empno ;show fullcolumns from dept;------left joinselect empno,ename,dname,hiredate from emp left joindept on emp.deptno =dept.deptno;------ right joinselectempno ,ename,dname,hiredate from emp right join dept on emp.deptno =dept.deptno;----子查询:在另一条是 select 作为条件select *from emp where deptno in (selectdeptno from dept where loc='北京');select *from emp where notexists(selectdeptno from dept where loc='北京'and dept.deptno=emp.deptno);------1、查询出部门内所有员工薪酬低于20000的员工的部门名称及位置;select dname ,loc from dept wheredeptno in(select deptno from emp wheresal<20000);select dname ,loc from dept wheredeptno in(select deptno from emp group by emp.deptno havingmax(sal)<20000) ;2、查询‘武汉’地区所有员工的工作(job);select job fromemp wheredeptno in (selectdeptno from dept whereloc='武汉');3、查询所有job 不为‘chairman’的员工的工号、姓名、部门名称和办公地址;selectempno,ename ,dname,loc from emp left joindept onemp.deptno =dept.deptno where job<>'chairman';===更高效的写法selectempno,ename ,dname,loc from emp left joindept onemp.deptno =dept.deptno where not exists(select1 fromemp e where e.empno=emp.empno and job ='chairman') ;

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。