sql语句怎么写?

CREATE DATABASE exam;
/创建部门表/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);

/创建雇员表/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
 hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) ,
//CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno);
//本来应该添加这个外键约束,但是为了以后做案列所以不加这个约束,因为加了这个约束,那么emp表中deptno的值就必须是dept表中的主键的某一值
);

/创建工资等级表/
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);

/创建学生表/
CREATE TABLE stu(
sid INT PRIMARY KEY,
sname VARCHAR(50),
age INT,
gander VARCHAR(10),
province VARCHAR(50),
tuition INT
);

/插入dept表数据/
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

/插入emp表数据/
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO emp VALUES (1015, '张三', '保洁员', 1009, '2001-09-01', 24500, 50000, 50); //注意 ,在部门表中根本没有50部门

/插入salgrade表数据/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

/插入stu表数据/
INSERT INTO stu VALUES ('1', '王永', '23', '男', '北京', '1500');
INSERT INTO stu VALUES ('2', '张雷', '25', '男', '辽宁', '2500');
INSERT INTO stu VALUES ('3', '李强', '22', '男', '北京', '3500');
INSERT INTO stu VALUES ('4', '宋永合', '25', '男', '北京', '1500');
INSERT INTO stu VALUES ('5', '叙美丽', '23', '女', '北京', '1000');
INSERT INTO stu VALUES ('6', '陈宁', '22', '女', '山东', '2500');
INSERT INTO stu VALUES ('7', '王丽', '21', '女', '北京', '1600');
INSERT INTO stu VALUES ('8', '李永', '23', '男', '北京', '3500');
INSERT INTO stu VALUES ('9', '张玲', '23', '女', '广州', '2500');
INSERT INTO stu VALUES ('10', '啊历', '18', '男', '山西', '3500');
INSERT INTO stu VALUES ('11', '王刚', '23', '男', '湖北', '4500');
INSERT INTO stu VALUES ('12', '陈永', '24', '男', '北京', '1500');
INSERT INTO stu VALUES ('13', '李雷', '24', '男', '辽宁', '2500');
INSERT INTO stu VALUES ('14', '李沿', '22', '男', '北京', '3500');
INSERT INTO stu VALUES ('15', '王小明', '25', '男', '北京', '1500');
INSERT INTO stu VALUES ('16', '王小丽', '23', '女', '北京', '1000');
INSERT INTO stu VALUES ('17', '唐宁', '22', '女', '山东', '2500');
INSERT INTO stu VALUES ('18', '唐丽', '21', '女', '北京', '1600');
INSERT INTO stu VALUES ('19', '啊永', '23', '男', '北京', '3500');
INSERT INTO stu VALUES ('20', '唐玲', '23', '女', '广州', '2500');
INSERT INTO stu VALUES ('21', '叙刚', '18', '男', '山西', '3500');
INSERT INTO stu VALUES ('22', '王累', '23', '男', '湖北', '4500');
INSERT INTO stu VALUES ('23', '赵安', '23', '男', '北京', '1500');
INSERT INTO stu VALUES ('24', '关雷', '25', '男', '辽宁', '2500');
INSERT INTO stu VALUES ('25', '李字', '22', '男', '北京', '3500');
INSERT INTO stu VALUES ('26', '叙安国', '25', '男', '北京', '1500');
INSERT INTO stu VALUES ('27', '陈浩难', '23', '女', '北京', '1000');
INSERT INTO stu VALUES ('28', '陈明', '22', '女', '山东', '2500');
INSERT INTO stu VALUES ('29', '孙丽', '21', '女', '北京', '1600');
INSERT INTO stu VALUES ('30', '李治国', '23', '男', '北京', '3500');
INSERT INTO stu VALUES ('31', '张娜', '23', '女', '广州', '2500');
INSERT INTO stu VALUES ('32', '安强', '18', '男', '山西', '3500');
INSERT INTO stu VALUES ('33', '王欢', '23', '男', '湖北', '4500');
INSERT INTO stu VALUES ('34', '周天乐', '23', '男', '北京', '1500');
INSERT INTO stu VALUES ('35', '关雷', '25', '男', '辽宁', '2500');
INSERT INTO stu VALUES ('36', '吴强', '22', '男', '北京', '3500');
INSERT INTO stu VALUES ('37', '吴合国', '25', '男', '北京', '1500');
INSERT INTO stu VALUES ('38', '正小和', '23', '女', '北京', '1000');
INSERT INTO stu VALUES ('39', '吴丽', '22', '女', '山东', '2500');
INSERT INTO stu VALUES ('40', '冯含', '21', '女', '北京', '1600');
INSERT INTO stu VALUES ('41', '陈冬', '23', '男', '北京', '3500');
INSERT INTO stu VALUES ('42', '关玲', '23', '女', '广州', '2500');
INSERT INTO stu VALUES ('43', '包利', '18', '男', '山西', '3500');
INSERT INTO stu VALUES ('44', '威刚', '23', '男', '湖北', '4500');
INSERT INTO stu VALUES ('45', '李永', '23', '男', '北京', '1500');
INSERT INTO stu VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');
INSERT INTO stu VALUES ('47', '送小强', '22', '男', '北京', '3500');
INSERT INTO stu VALUES ('48', '关动林', '25', '男', '北京', '1500');
INSERT INTO stu VALUES ('49', '苏小哑', '23', '女', '北京', '1000');
INSERT INTO stu VALUES ('50', '赵宁', '22', '女', '山东', '2500');
INSERT INTO stu VALUES ('51', '陈丽', '21', '女', '北京', '1600');
INSERT INTO stu VALUES ('52', '钱小刚', '23', '男', '北京', '3500');
INSERT INTO stu VALUES ('53', '艾林', '23', '女', '广州', '2500');
INSERT INTO stu VALUES ('54', '郭林', '18', '男', '山西', '3500');
INSERT INTO stu VALUES ('55', '周制强', '23', '男', '湖北', '4500');



数据库设计

在这里插入图片描述
在这里插入图片描述


在这里插入图片描述
单表查询练习

/*1\. 查询出部门编号为30的所有员工*/
SELECT *
FROM emp
WHERE deptno=30;

/*2\. 查询所有销售员的姓名、编号和部门编号。*/
SELECT ename, empno, deptno
FROM emp
WHERE job='销售员';

/*3\. 找出奖金高于工资的员工。*/
SELECT *
FROM emp
WHERE comm > sal

/*4\. 找出奖金高于工资60%的员工。*/
SELECT *
FROM emp
WHERE comm > sal*0.6;

/*5\. 找出部门编号为10中所有经理,和部门编号为20中所有销售员的详细资料。*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员')

/*6\. 找出部门编号为10中所有经理,部门编号为20中所有销售员,还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员') OR (job NOT IN ('经理', '销售员') AND sal >= 20000)

/*8\. 无奖金或奖金低于1000的员工。*/
SELECT *
FROM emp
WHERE comm IS NULL OR comm < 1000

/*9\. 查询名字由三个字组成的员工。*/
SELECT *
FROM emp
WHERE ename LIKE '___'

/*10.查询2000年入职的员工。*/
SELECT *
FROM emp
WHERE hiredate LIKE '2000-%'

/*11\. 查询所有员工详细信息,用编号升序排序*/
SELECT * 
FROM emp
ORDER BY empno

/*12\. 查询所有员工详细信息,用工资降序排序,如果工资相同使用入职日期升序排序*/
SELECT *
FROM emp
ORDER BY sal DESC, hiredate ASC

/*13\. 查询每个部门的平均工资*/
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno

/*14\. 查询每个部门的雇员数量。*/
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno

/*15\. 查询每种工作的最高工资、最低工资、人数*/
SELECT job, MAX(sal), MIN(sal), COUNT(*)
FROM emp
GROUP BY job

多表查询练习

/*
1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
列:d.deptno, d.dname, d.loc, 部门人数
表:dept d, emp e
条件:e.deptno=d.deptno
*/
SELECT d.*, z1.cnt 
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1
WHERE d.deptno = z1.deptno

/*
3. 列出所有员工的姓名及其直接上级的姓名。
列:员工姓名、上级姓名
表:emp e, emp m
条件:员工的mgr = 上级的empno
*/

SELECT e.ename, IFNULL(m.ename, 'BOSS') 领导
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno


/*
4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列:e.empno, e.ename, d.dname
表:emp e, emp m, dept d
条件:e.hiredate<m.hiredate
*/

SELECT e.empno, e.ename, d.dname
FROM emp e, emp m, dept d
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno

/*
5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
列:* 
表:emp e, dept d
条件:e.deptno=d.deptno
*/
SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno

/*
7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
列:job, count(*)
表:emp e
条件:min(sal) > 15000
分组:job
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000


/*
8. 列出在销售部工作的员工的姓名,假定不知道销售部的部门编号。
列:e.ename
表:emp
条件:e.deptno=(select deptno from dept where dname='销售部')
*/

SELECT *
FROM emp e
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='销售部')



/*
9. 列出薪金高于公司平均薪金的所有员工信息,所在部门名称,上级领导,工资等级。
列:* 
表:emp e
条件:sal>(查询出公司的平均工资)
*/
SELECT e.*, d.dname, m.ename, s.grade
FROM emp e, dept d, emp m, salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal

---------------

SELECT e.*, d.dname, m.ename, s.grade
FROM 
  emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno
        LEFT OUTER JOIN emp m ON e.mgr=m.empno
        LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal>(SELECT AVG(sal) FROM emp)


SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;



/*
10.列出与庞统从事相同工作的所有员工及部门名称。
列:e.*, d.dname
表:emp e, dept d
条件:job=(查询出庞统的工作)
*/

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统')



/*
11.列出薪金高于在部门30工作的所有员工的薪金 的员工姓名和薪金、部门名称。
列:e.ename, e.sal, d.dname
表:emp e, dept d
条件;sal>all (30部门薪金)
*/
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30)

/*
13.查出年份、利润、年度增长比
*/
SELECT y1.*, IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz*100, '%'), '0%') 增长比
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year=y2.year+1;

在这里插入图片描述
SQL : Structured Query Language (结构化查询语言)。 作用是: 客户端使用SQL来操作服务器。
SQL标注: 是由国际标准化组织(ISO)制定的,对DBMS的统一操作方式。
SQL方言: 每个DBMS都除了会遵循标准之外,会有自己的方言。也就是自己所以特有的特殊的语句或者句法。
SQL语法:

  • SQL语句可以在单行或者多行书写,以分号结束 ,没有写分号,可以换行 ;
  • 可以使用空格或者缩进来增强语句的可读性 ;
  • MySQL是不区分大小写的,不过建议大写 。
  • 数据库中所有的字符串类型都需要用单引号,不能使用双引号

SQL语句的分类

  1. DDL (Data Definition Language ) : 数据定义语言,用来对数据库或表的结构进行操作 。
  2. 也就是创建、删除、修改 库和表结构 DML (Data Manipulation Language ) :
  3. 数据操作语言,用来对表的记录进行更新 , 也就是对表的记录进行增、删、改 DQL (Dat Query Language ) :
  4. 数据查询语言 , 用来对表的记录进行查询 DCL (Dat Control Language ) : 数据控制语言 ,
    用来定义访问权限和安全级别用来定义访问权限和安全级别

    DDL _ 对库的操作
    查看所有数据库: SHOW DATABASES ;
    切换(选择要操作的)数据库:USE 数据库名 ;
    创建数据库:CREATE DATABASE mydb1 [CHARSET=utf8] //方框号是表示可选择的。 语句中没有方框号
    删除数据库:DROP DATABASE mydb1
    修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8

DDL _ 对表的操作
创建表

CREATE TABLE  表名(
    列名 列类型,
    列名 列类型,
    ...
    列名 列类型
  );

查看当前数据库中所有表名称:SHOW TABLES;
查看表结构:DESC 表名;
删除表:DROP TABLE 表名;
修改_添加列:

 ALTER TABLE 表名 
    ADD (
      列名 列类型,
      列名 列类型,
      ...
    );

修改_修改列类型 ALTER TABLE 表名 MODIFY 列名 列类型;
修改_修改列名:ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
修改_删除列:ALTER TABLE 表名 DROP 列名;
修改_修改表名称:ALTER TABLE 原表名 RENAME TO 新表名;
在这里插入图片描述
where条件语法
条件必须是一个boolean类型的值或表达式
where 属性判断
条件的运算符:
在这里插入图片描述
WHERE age >= 18 AND age <= 80 选中年龄在大于18和小于80
WHERE age BETWEEN 18 AND 80 选中年龄在大于18和小于80

WHERE name='zhangSan' OR name='liSi' 选中名字是zhanSan或者liSi的
WHERE name IN ('zhangSan', 'liSi') ; 选中名字是zhangSan和liSi

WHERE age IS NULL 选择年龄值为空的
WHERE age IS NOT NULL选择年龄值不为空的

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
上述执行就可以发现 , 查询出的人名中并没有张三这个人,因为张三的部门是50,而在部门表中并没有50这个部门。 如果想要显示张三,并且对应部门的名称显示NULL 的话,需要外连接
外连接

/*
   左链接 ,先查询出左表(以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULL  
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno ;

/*
   右链接 ,先查询出右表(以右表为主),然后查询左表,左表中满足条件的显示出来,不满足条件的显示NULL  
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno ;
/*
   全外连接 
SELECT e.ename,e.sal,d.dname
FROM emp AS e FULL OUTER JOIN dept d
ON e.deptno=d.deptno ;
但是MySQL数据库不支持,可以用UNION ALL  的方式来解决
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno 
UNION ALL
SELECT e.ename,e.sal,d.dname
FROM emp AS e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno ;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
如上代码所示, 外连接有左右之分,左链接 ,先查询出左表(以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULL,比如,员工中的张三,先把张三查询出来,然后右表(部门表)中并没有50号部门,所以显示NULL 。 右链接 ,先查询出右表(以右表为主),然后查询左表,左表中满足条件的显示出来,不满足条件的显示NULL ,比如部门表中有个40号财务部门,先查询出来,但是左表(员工)中并没有40号部门的员工,所以左侧都显示为NULL.

子查询
子查询就是嵌套查询,select中包含多个select 。 子查询出现的位置 ,在where后,作为条件的一部门 ;在from后,作为被查询的一条表 。当子查询出现在where后作为条件时,可以用any、all 关键字 。
子查询的结果集的形式有 : 单行单列、单行多列、多行单列、多行多列

/*
  子查询案例一 : 查询工资高于关羽工资的员工
  子查询作为条件 。 形式为单行单列
  子条件是关羽的工资
*/
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename="关羽") ;
/*
   子查询案例二 : 工资高于30部门所有人的员工信息
   子查询作为条件  。 形式为多行单列
   子条件是30部门所有人的工资
*/
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30) ;
/*
   子查询案例三 : 查询工作和工资与殷天正完全相同的员工
   子查询作为条件 。  形式为单行多列
   子条件是殷天正的工作和工资
*/
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename="殷天正"); 
/*
   子查询案例四 : 查询员工编号为1006的员工名称、部门名称、员工工资、部门地址
   子查询 作为表。 形式为多行多列
   子条件是部门表中的部门名称、部门地址 , 部门编号作为去除笛卡尔积的条件也必须被查询
*/
SELECT e.ename,e.sal,d.dname,d.loc
FROM emp AS e , (SELECT dname ,loc ,deptno FROM dept) AS d 
WHERE e.deptno=d.deptno AND e.empno=1006 ; 
  • 4
    点赞
  • 18
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:创作都市 设计师:CSDN官方博客 返回首页
评论

打赏作者

紫楠&馨芷

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值