MySQL---单表查询综合练习

创建emp表

CREATE TABLE emp(

empno INT(4) NOT NULL COMMENT '员工编号',

ename VARCHAR(10) COMMENT '员工名字',

job VARCHAR(10) COMMENT '职位',

mgr INT(4) COMMENT '上司',

hiredate DATE COMMENT '入职时间',

sal INT(7) COMMENT '基本工资',

comm INT(7) COMMENT '补贴',

deptno INT(2) COMMENT '所属部门编号'

);

emp表添加主键

mysql> alter table emp add primary key(empno);     

Query OK, 0 rows affected (0.01 sec)               

Records: 0  Duplicates: 0  Warnings: 0 

emp表添加外键约束    

#在MySQL中，可以使用alter  table 语句来添加外键约束。以下是基本的语法：

alter table  table_name
ADD FOREIGN KEY (column_name) REFERENCES other_table(other_column);
在这个语句中：

• table_name是你想要添加外键的表的名称。
• column_name是你想要设置为外键的列的名称。
• other_table是包含外键引用的表的名称。
• other_column是在other_table中的列的名称。

 ALTER TABLE emp ADD CONSTRAINT f_ed_key  FOREIGN KEY (deptno)   REFERENCES dept(deptno);

emp表中插入数据

INSERT INTO `emp` VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');

INSERT INTO `emp` VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');

INSERT INTO `emp` VALUES('7521','李兴','售货员','7698','1995-07-22','1250','500','30');

INSERT INTO `emp` VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');

INSERT INTO `emp` VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');

INSERT INTO `emp` VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');

INSERT INTO `emp` VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');

INSERT INTO `emp` VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');

INSERT INTO `emp` VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');

INSERT INTO `emp` VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30');

INSERT INTO `emp` VALUES('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20');

INSERT INTO `emp` VALUES('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30');

INSERT INTO `emp` VALUES('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20');

INSERT INTO `emp` VALUES('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');

查询emp表的内容

mysql> select * from emp;

1.选择部门30中的所有员工

mysql> select ename 员工,deptno 部门编号 from emp where deptno=30;  

2.列出所有办事员的姓名，编号和部门编号

mysql> select ename 姓名,empno  编号 ,deptno 部门编号 from emp;

3.找出佣金高于薪金的员工

mysql> select ename 员工,sal 薪金,comm 佣金 from emp where comm > sal;

4.找出没有佣金的员工

#在MySQL中，NULL和0是两种不同的表示方式。

#NULL在MySQL中表示未知或者无效的值。例如，如果一个员工的佣金字段为NULL，那么这可能意味着他们的佣金信息未知或者无效，可以通过 IS NULL 条件来查询这些记录。

#0则表示佣金确实存在，只是其值为0。在这种情况下，可以通过等于0的条件来查询这些记录。

mysql> select ename 员工,comm 佣金 from emp where comm is null;

 5.找出佣金高于薪金的60%的员工

mysql> select ename 员工,sal 薪金,comm 佣金 from emp where comm > sal*0.6; 

 6.找出部门10中所有人事部长和部门20中所有办事员的详细资料

mysql> select * from emp where (deptno=10 and job='人事部长') or (deptno=20

and job='办事员');

7.找出收取佣金的员工

mysql> select ename 员工,comm 佣金 from emp where comm is not null; 

8.找出不收取佣金或收取的佣金低于100的员工

mysql> select ename 员工,comm 佣金 from emp where (comm is null) or (comm < 100);

 9.找出姓张的员工的信息

#在MySQL中，regexp是一个操作符，用于执行正则表达式模式匹配。^：匹配字符串的开始

mysql> select * from emp where ename regexp '^张'; 

10.显示员工的姓名和受雇日期,新的员工排在最前面

#在MySQL中，order by 语句用于对查询结果进行排序，以下是基本的语法：

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

在这个语句中：

• column1, column2, ... 是你想要从数据库中选择的列的名称。
• table_name 是你想要从中选择数据的表的名称。
• ASC 是指定列按升序排序（默认）。
• DESC 是指定列按降序排序。

mysql> select ename 姓名,hiredate 受雇日期 from emp order by hiredate desc;

11.按工作的降序排序,若工作相同则按薪金排序

mysql> select empno 编号,ename 姓名,job 工作,sal 薪资 from emp order by job desc,sal;

12.查出不姓刘的员工的信息

#在MySQL中，LIKE操作符用于在WHERE子句中搜索列中的指定模式。LIKE操作符通常与%和_通配符一起使用。

% 是一个通配符，代表任意数量的字符。

_ 也是一个通配符，但只代表一个字符。

mysql> select * from emp where ename not like '刘%';  

13.取出姓李的员工

以下两种SQL语句等价

mysql> select ename 员工 from emp where ename like '李%'; 

mysql> select ename 员工 from emp where ename regexp '^李';