MySQL数据库的知识点

最新推荐文章于 2023-03-27 17:32:02 发布

置顶范高伦

最新推荐文章于 2023-03-27 17:32:02 发布

阅读量834

点赞数 1

本文链接：https://blog.csdn.net/weixin_44911419/article/details/100023125

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据库MySQL
适用于中小型企业，MySQL数据库适合搭集群，单独拿一台MyCat出来管理许多子数据库
通俗地讲，数据库就是把一些数据整合到一起，其实质还只存在硬盘里

SQL语句（Structured Query Language；结构化查询语言）— 掌握语句是重点

SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。

SQL语言由3种语言组成：

1、DDL - 表
2、DML - 数据
3、DCL - 权限

MySQL特征：

性能快捷、优化SQL语言、
容易使用、
多线程和可靠性、
多用户支持、
可移植性和开发源代码，
遵循国际标准和国际化支持、
为多种编程语言提供API

MySQL的不足之处:
不能直接处理XML数据、一些功能上支持的不够完善和成熟、不能提供任何OLAP（实时分析系统）功能

数据库系统的使用：
在这里插入图片描述
数据库基础知识
数据库由一批数据构成的 有序集合，这些数据被分门别类地存放在一些结构化的 数据表（table）里，而数据表之间又往往存在交叉引用的关系，这种关系使数据库又被称为 关系型数据库

档案柜 <==> 数据库
抽屉 <==> 表
文件 <==> 记录

数据库：
在这里插入图片描述
数据表：

数据在表中的存放：

数据 —— DML语言（Data Manipulation Language ；数据操作语言）：
增(insert)、删(delete)、改(update)、查(select)
权限 —— DCL语言（Data Control Language ；数据控制语言）：

用来控制存储许可、存取权限
GRANT、REVOKE
表 —— DDL语言（Data Definition Language；数据定义语言）

用来建立数据库，数据库对象和定义其列

00、MySQL中使用SQL语言几点说明

属于一个SQL语句，使用分号（；）结尾，否则mysql认为语句没有输入完
箭头（->）代表SQL语句没有输入完
取消SQL语句使用 ( \c )
SQL语句 关键字 和 函数名 不区分大小写（Linux区分，Windows不区分）
使用函数时，函数名和后面的括号之间不能有空格

表的主键（PRIMARY KEY）、
唯一性 (UNIQUE)、
非空值约束（NOT NULL）、

一、数据类型种类

数值型 分为整型和 浮点型

字符串:

在这里插入图片描述

日期和时间值

存储如“2005 -1-1” 或者“12:00:00”这样的数值的值。在MySQL中日期是按”年-月-日”的顺序。

NULL值

是一种无类型的值，表示“空，什么也没有”。

NULL 和 NOT NULL

默认为NULL，即插入值时没有在此字段插入值时自动填NULL，如果指定了NOT NULL，则必须在插入值时在此字段添入值，不允许插入NULL值。

DEFAULT

可以通过此属性来指定一个缺省值，即如果没有在此列添加值，那么默认添加 DEFAULT 后指定值。

ZEROFILL

前导零填充数值类型值以达到列的显示宽度。

举例：

create table  test2(num1 int default 1，num2 int zerofill);

在这里插入图片描述

字符串列类型

1、CHAR 和 VARCHAR类型

CHAR类型和 VARCHAR类型长度范围都是0~255之间的大小。他们之间的差别在于MySQL处理存储的方式：

CHAR 类型 把这个大小视为值的准确大小（用空格填补比较短的值）。

VARCHAR 类型 把它视为最大值并且只使用了存储字符串实际上需要的字节数（增加了一个额外的字节记录长度）。

因而，较短的值当被插入一个语句为 VARCHAR类型的字段时，将不会用空格填补（然而，较长的值仍然被截短）。

在这里插入图片描述
2、BLOB 和 TEXT类型
BLOB是二进制的对象，如果想存储二进制数 BLOB 将是最佳选择，而 TEXT与之相同，只是BOLOB按照二进制编码排序（区分大小写），TEXT按照字符文本模式排序（不区分大小写）。

3、ENUM 和 SET类型
是特殊的串类型，其列值必须从固定的串集中选择，二者差别为前者必须是只能选择其中的一个值，而后者可以多选。（不推荐使用）

二、字符串列类型的可选属性

BINARY
使用此属性可以使列值作为二进制串处理，即看成BLOB类型。
NULL 和NOT NULL
同数值型功能相同
DEFAULT
同数值型功能相同

说明：
每个时间和日期列类型都有一个零值，当插入非法数值时就用零值来添加
表示日期时必须先按：年，月，日的顺序给出

DATE ,TIME ,DATETIME 分别是存储日期，时间与日期和时间的组合，其格式为“YYYY-MM-DD”，“hh:mm:ss” 和 “YYYY-MM-DD hh:mm:ss”，对于 DATETIME类型，日期和时间部分都需要

TIMESTAMP
时间戳列类型以YYYYMMDDhhmmss的格式来表示值，其取值范围是19700101000000到2037年的某个时间，主要用于记录更改或创建某个记录

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

三、创建数据库操作・DDL

建立数据库操作：
语法：create database 数据库名
叙述：创建一个具有指定名称的数据库。如果要创建的数据库已经存在，或者没有创建它的适当权限，则此语句失败。

例：建立一个student库。

mysql> create database student;

1.创建表

查询—>新建查询

建立表操作：

语法：

create table 表名（
列名1  列类型  [<列的完整性约束>],
列名2  列类型  [<列的完整性约束>], 
... ...);

叙述：在当前数据库下新创建一个数据表。

列类型：表示该列的数据类型。

例：
建立一个表 school，其由两列组成，第一列属性为非空，并做为主键,并自增

create table school(
  school_id  int(10) not null auto_increment primary key,
  school_name varchar(20) 				
)；

   CREATE TABLE stu(
   Sno INT(4) PRIMARY KEY AUTO_INCREMENT,    #“4”表示学号类型
   Sname VARCHAR(20) UNIQUE,      #“20”表示字节长
   Ssex VARCHAR(4),               #“4”也表示字节长
   Sbirthday datetime,
   Stel VARCHAR(30)
   )

常见完整性约束：

PRIMARY  KEY 	            主码约束(主键)

UNIQUE						 唯一性约束

NOT  NULL				     非空值约束		
	
AUTO_INCREMENT	         用于整数列默认自增1

UNSIGNED                     无符号整数

DEFAULT default_value		默认值约束

DEFAULT cur_timestamp      创建新记录时默认保存当前时间（仅适用timestamp数据列）

ON UPDATE cur_timestamp     修改记录时默认保存当前时间（仅适用timestamp数据列）

CHARACTER SET name          指定字符集（仅适用字符串）

数据表类型：
在创建一个新的MySQL数据表时，可以为它设置一个类型，其中最重要的 3种类型 是

MyISAM：成熟、稳定和易于管理

InnoDB：加入事物、数据行级锁定机制、外键约束条件、崩溃恢复等新功能
 
HEAP：只存在于内存中，可做临时表

 create table tmp(…)ENGINE=MyISAM

主键与外键

数据表之间的关联 / 引用关系是依靠具体的 主键（primary key） 和 外键（foreign key） 建立起来的。
主键： 帮助MySQL以最快的速度把一条特定的数据记录的位置确定下来。
主键必须是唯一的
主键应该是紧凑的，因此整数类型比较适合
外键： 引用另外一个数据表的某条记录。
外键列类型 尽可能与 主键列类型 保持一致
外键列应该加上 NOT NULL

主键
create table student1(
   sid int not null auto_increment,
   name varchar(20) not null,
   gender char(1),
   primary key(sid,gender)
);

外键（自动检查外键是否匹配，仅适用InnoDB）
create table score(
   cid int not null auto_increment primary key,
   score int,
   sid int,
   foreign key(sid) references student(sid)
);

主表和从表:

当主表中没有对应的记录时，不能将记录添加到子表
——成绩表中不能出现在学员信息表中不存在的学号；
不能更改主表中的值而导致子表中的记录孤立
——把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变；
子表存在与主表对应的记录，不能从主表中删除该行
——不能把有成绩的学员删除了
删除主表前，先删子表
——先删学员成绩表、后删除学员信息表

选择主键的原则:
最少性_尽量选择单个键作为主键
稳定性_尽量选择数值更新少的列作为主键

2.查看表结构

显示表结构操作：      
语法：  describe  表名 ：                
Desc  表名:     
叙述： 用于显示表的创建结构
describe 表名

3.删除数据库或表操作

删除表操作
语法：drop table [if exists] tab_name [,tab_name]…
叙述：从数据库中删除给定的表。如果给出 if exists 子句，则删除不存在的表不会出错。

删除数据库操作
语法：drop database [if exists] db_name
叙述：删除给定的数据库。在删除一个数据库后，它就永远没有了，因此要特别小心。如果给出 if exists 子句，则删除不存在的数据库不会出错。

语法：drop table [if exists]  tab_name [,tab_name]...

if exists 是说，如果这个表存在你就删，不存在就别删了；还有 tab_name 后面用逗号隔开，加省略号的意思是可以一次删除好多表

4.更改表结构操作

语法：alter table 表名 action:

alter table stu add sweight double [after sname]

说明：action 可以是如下语句：

add 列名 建表语句 [first | after 列名]

可以为表添加一列，如果没指定 first 或者 after，则在列尾添加一列，否则在指定列添加新列

add primary key (列名) 为表添加一个主键，如果主键已经存在，则出现错误
add foreign key(列名) references 表名(列名)为表添加一个外键
alter 列名 set default 默认值 可以更改指定列默认值

change 旧列名 新列名  [first | after 列名]
可以更改列类型和列名称，如果原列的名字和新列的名字相同

modify 列名  [first | after 列名]
		 和change的作用相同

drop 列名            // 可以删除一列

drop primary key    // 可以删除主键

rename as 新表名     // 可以将表名更改

举例：

举例1：
向people表中添加字段address2, 类型为varchar, 最大长度为100
alter table people add address2 varchar(100);

举例2:	
将 people 表 中的 name 列 默认值改为 100:
alter table people alter name set default 100;

举例3:
向 student 表 增加 “入学时间” 列，其数据类型为日期型。
alter table student add scome date；

注：无论基本表中原来是否已有数据，新增加的列一律为空值。

举例4:
将年龄的数据类型 改为 半字长整数。
alter table student modify sage smallint；

注：修改原有的列定义有可能会破坏已有数据

四、MySQL运算符

1. 算术运算符

在这里插入图片描述
字符串自动转换数字
用字符串表示的数字在任何可能便于计算的地方都被自动地转换为数字。当执行转换时，MySQL遵循两个基本规则：

如果第一位是数字的，字符串被用于一个算数运算中，那么它被转换为这个数字的值。
如果第一个包含字符和数字混合的，字符串不能被正确的转换为数字，那么它被转换成0。

2. 比较运算符

比较运算符允许我们对表达式的左边和右边进行比较。一个比较运算符的结果总是 1（真），0（假），或是为 NULL（不能确定）。

比较运算符可以用于比较数字和字符串。数字作为浮点值比较，而字符串以不区分大小写
在这里插入图片描述

在这里插入图片描述
对于＜＞运算符，如果表达式两边不相等，返回真值，相等返回假值。还可以比较字符串

BETWEEN运算符 用于检验一个值（或者一个求值表达式）是否存在一个指定的范围内：
IＮ运算符 用于检验一个值（或者一个求值表达式）是否包含在一个指定的值集合中。

可以使用 IS NULL 或者 IS NOT NULL 运算符来测定是否为空。

可以使用特殊的 <=>运算符，MySQL称为 NULL安全的等于 运算符。这意味着即使当包含在比较运算符中的表达式含有一个NULL值时，MySQL也会为比较运算符返回一个真值或假值。
在这里插入图片描述
如果想执行通配符数据搜索，应该使用LIKE运算符。它通过在表达式中允许使用专门的通配字符，可以找出与指定搜索字符串全部或部分匹配的记录。

默认情况下，比较是不区分大小写的方式执行的。然而，以前我们注意到，可以添加 BINARY关键字让MySQL执行区分大小写的比较。
在这里插入图片描述
MySQL数据库中的通配符

“%” (百分号)  代表任意长度（长度可以为0）的字符串

举例：
a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串

“_ ”(下横线)  代表任意单个字符
举例：
a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串

REGEXP运算符 允许我们执行更复杂的字符串比较运算，在这里使用 UNIX正则表达式。
在这里插入图片描述

3. 逻辑运算符

在这里插入图片描述

4. 位运算符

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

五、插入记录操作・DML

语法：

INSERT into <表名> ([列名1],[列名2]) VALUES <值列表>

叙述：
如果表名后面没写字段名，则默认是向所有的字段添加值，另外字符串值应该用‘ ’或“ ”引号括起来

举例1：向 people表中添加一条记录：

insert into people(name,age) values(“zhangsan”,20);

在这里插入图片描述

插入案例

创建一张学生信息表，往表中插入数据
create table students(
  scode int not null auto_increment,
  sname varchar(20) not null,
  saddress varchar(20) default‘未知’,
  sgrade int,
  semail varchar(20),
  ssex bit,
  primary key(scode)
);

插入数据行 1

注意事项1:
每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；
在这里插入图片描述
注意事项2:
每个数据值的数据类型、精度和小数位数必须与相应的列匹配；

注意事项3:
如果在设计表的时候就指定了某列不允许为空，则必须插入数据；

注意事项4:
具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值在这里插入图片描述

1. 插入多行数据insert：

 INSERT INTO <表名>(列名)    VALUES(<列名值>), (<列名值>), (<列名值>)     ……

在这里插入图片描述

INSERT INTO <表名>(列名)
   SELECT <列名>
   FROM <源表名>

  INSERT INTO 	TongXunLu ('姓名','地址','电子邮件') 
         SELECT 	SName,SAddress,SEmail
          FROM 		Students

2. 更改记录操作update：

语法：

UPDATE <表名> SET <列名 = 更新值>  [WHERE <更新条件>]    # SET不区分大小写，都行

叙述：
where 子句是判断语句，用来设定条件，限制只更新匹配的行，如果不带where子句，则更新所有行数据。

举例：
将 student表中的所有学生名称为"Alex"的改为"Tom":

 update student set sname="Tom" where sname="Alex";

更新数据行：

UPDATE Student SET SSEX = 0

UPDATE Student 
SET Sdept ='家政系' 
WHERE Sdept = '刺绣系'
 
UPDATE Sc
SET Grade = Grade + 5
WHERE Grade <= 95

3. 删除记录操作delete：

语法：

DELETE FROM <表名> [WHERE <删除条件>]

叙述：
此语句删除表中的行，如果不带where子句，则删除整个表中的记录，但是表不被删除。

举例：
删除student表中的所有年龄小于18岁的记录：

delete from student where sage<18;

删除学生编号为’95005’的学生信息，包括成绩信息

 delete from student,sc using student,sc where student.sno = sc.studentid and student.sno='95005';

说明：
表和字段的引用方式有两种：绝对引用 和 相对引用

- 绝对引用：数据库名.表名(.字段名)
- 相对引用：表名.(字段名)

在这里插入图片描述

4. 查询select：

查询是最复杂的，它分为好多好多不同的查询
在这里插入图片描述
查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是先从数据表中提取数据，并按照表的形式显示出来

怎么查：
在这里插入图片描述

SQL语法：

SELECT [DISTINCT | DISTINCTROW | ALL] 
select_expression,... 
[FROM table_references 
   [WHERE where_definition] 
   [GROUP BY col_name,...] 
   [HAVING where_definition] 
   [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] 	
   [LIMIT [offset,] rows] 
   [PROCEDURE procedure_name]
]

查询记录操作：

distinct:      排重

from子句：      指定查询数据的表

where子句：     查询数据的过滤条件

group by子句：  对匹配where子句的查询结果进行分组

having子句：    对分组后的结果进行条件限制

order by子句：  对查询结果进行排序，后面跟desc降序或asc升序（默认）。　  	
	
limit子句：     对查询的显示结果限制数目

procedure子句： 查询存储过程返回的结果集数据

在这里插入图片描述

举例1 :	查询全体学生的学号与姓名。
      select sno,sname from student;

举例2 :	查询全体学生的详细记录。
      select  sno,sname,ssex,sage,sdept from student;  
      或 select   *  from student; 

举例3 : 	查全体学生的姓名及其出生年份。
        select sname,2014-sage from student； 

举例4 :	查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
		select sname,'year of birth: ',2008-sage, lower(sdept) from student;

举例5 :	查询选修了课程的学生学号(去掉重复的记录)
        select   distinct  studentid  from sc;

举例6 :	查询全体学生的学号与姓名，用中文显示列名。
        select sno as ‘编号’,sname as ‘姓名’ from student;

举例7 :	给表设置别名。
        select   s.sno,s.sname  from student as s; 

举例8 : 	查询年龄在20以下的学生的姓名。
        select sname from student where sage<20； 

举例9 :	查询全体学生的姓名、年龄，要求按照年龄降序排序。
		select sname,sage from student order by sage desc;

举例10 :	查询年龄最大的前3个学生的姓名和年龄，或第4、5个学生
        select sname,sage from student order by sage desc limit 3;或（limit 3,2）

使用集函数（统计函数）

SQL提供的 统计函数 称为 集函数.

主要的集函数：

记数函数： count(列名)计算元素的个数

求和函数： sum(列名)对某一列的值求和，但属性必须是整型

计算平均值：avg(列名)对某一列的值计算平均值

求最大值： max(列名) 找出某一列的最大值

求最小值： min(列名) 找出某一列的最小值

举例11 :	查询学生总数。
        select count(*) from student;

举例12 :	查询选修了课程的学生人数。
        select count(distinct studentid) from sc;

举例13 :	查询1号课程的学生平均成绩。
        select avg(grade) from sc where courseid=1;

举例14 :	查询1号课程的学生最高分和最低分。
        select max(grade) as ‘最高分’,min(grade) as ‘最低分’ from sc where courseid=1;

举例15 :	查询每个学生的平均成绩。
        select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid;

举例16 :	查询学生的平均成绩在70分以上的。
        select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid having avg(grade)>70;

在 WHERE 子句中使用谓词 :

BETWEEN  AND    :      在两数之间
 	
NOT BETWEEN AND　：  不在两数之间
	
IN <值表> ：    是否在特定的集合里（枚举）
	
NOT IN <值表>　：          与上面相反
	
LIKE		：           是否匹配于一个模式
	
IS NULL（为空的）或 IS NOT NULL（不为空的）REGEXP :     检查一个值是否匹配一个常规表达式。

举例17 :	查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 。
        select sname,sdept,sage from student where sage between 20 and 23;

举例18 :	查询年龄不在20~23岁之间的学生姓名、系别和年龄。
        select sname,sdept,sage from student where sage not  between 20 and 23;

举例19 :	查询'信息系'、'美术系'和'计算机系'学生的姓名和性别。
        select sname,ssex from student where sdept  in (‘信息系',‘美术系',‘计算机系');

举例20 :	查询学号为95001的学生的详细情况。
        select * from student where sno like '95001'；
       等价于：select * from student where sno='95001'; 

举例21 :	查询所有姓刘学生的姓名、学号和性别。
        select sname,sno,ssex from student where sname like ‘刘%';

举例22 :	某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
         select studentid,courseid from sc where grade is null;

举例23 :	查所有有成绩的学生学号和课程号。
        select studentid,courseid from sc where grade is not null;

举例24 :	查询计算机系年龄在20岁以下的学生姓名。
        select sname from student where sdept=‘计算机系' and sage<20;

举例25 :	查询信息系、美术系和计算机系学生的姓名和性别
        select sname,ssex from student where sdept in (‘信息系','美术','计算机系');
          可改写为：
        Select sname,ssex from student where sdept='信息系' or sdept='美术' or sdept='计算机系';

举例26 :	查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
        select studentid,grade from sc where courseid=3 orber by grade desc;

举例27 :	查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
        select * from student order by sdept,sage desc;

六、多表查询—问题

学员内部测试成绩查询的每次显示的都是学员的编号信息，因为该表中只存储了学员的编号；实际上最好显示学员的姓名，而姓名存储在学员信息表；

如何同时从这两个表中取得数据？

连接查询
同时涉及多个表的查询称为 连接查询
用来连接两个表的条件称为 连接条件
内连接 ( INNER JOIN )
外连接
——左外联结 (LEFT JOIN)
——右外联结 (RIGHT JOIN)
——全外联结（FULL JOIN）
外连接与普通连接的区别
普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出：

（嵌套查询：把一个查询嵌套在另一个查询里面）

多表查询 - 连接查询

select g.gid, s.sname,g.sno, g.score from t_grade g, t_student s where s.sno = g.sno;

在这里插入图片描述

多表查询 — 内连接-1

在这里插入图片描述

多表查询 — 内连接-2

在这里插入图片描述

多表查询 — 三表连接

在这里插入图片描述

多表查询 — 左外连接

在这里插入图片描述

多表查询 — 右外连接

在这里插入图片描述

子查询

将一个查询块嵌套在另一个查询块的 WHERE子句或 HAVING 短语的条件中的查询称为子查询。一个 SELECT-FROM-WHERE语句称为一个查询块

例如：

select sname from student where sno in (select studentid from sc where courseid=2);

例如：
查询选修了课程名为“Java”的学生学号和姓名

  select  sno,sname from student where sno in
 (select studentid  from sc where cno =
 (select cno from course where cname=‘Java’));

复制表

在这里插入图片描述
可以通过在SELECT查询中指定字段来限制出现在附表中的字段

通过使用 SELECT 语句创建已存在表的空副本，并且返回一个空结果集。

七、SQL语句实战练习

step1. 连接数据库
step2. 新建表
在这里插入图片描述

五个函数：count、min、max、

limit__从第几条（5）开始查，一共查几条（5）

几条查询SQL语句的练习：
在这里插入图片描述

dml:
1）新增数据
INSERT [INTO] <表名> ([列名1],[列名2]) VALUES <值列表>
–
2）修改语句
UPDATE <表名> SET <列名 = 更新值> [WHERE <更新条件>]
–
3）删除语句
DELETE FROM <表名> [WHERE <删除条件>]
–
4）查询
SELECT [DISTINCT | DISTINCTROW | ALL] select_expression,… [FROM table_references
[WHERE where_definition] [GROUP BY col_name,…] [HAVING where_definition]
[ORDER BY {unsigned_integer | col_name |formula} [ASC | DESC] ,…]
[LIMIT [offset,] rows] [PROCEDURE procedure_name]]
–
1，不能用*
2，where条件
3，count sum avg max min
4,group by having
5,order by asc|desc
6,limit
–
select size,color_id,count(size) from bbs_sku where price>0 GROUP BY size,color_id having size in (‘S’,‘M’,‘L’) ORDER BY color_id desc LIMIT 5,5
–
导入导出
多表联查
sql优化

1.选择部门30中的雇员
  select * from emp where deptno=30;
  
2.列出所有经理的姓名,编号和部门. 
  select ename,empno,dname from emp,dept where job='MANAGER' and emp.deptno=dept.deptno;  # MANAGER是字符串，加引号
  
3.列出佣金高于薪金的雇员. 
  select * from emp where comm>sal;
   
4.找出佣金高于或等于薪金50%的雇员
  select * from emp where comm>=sal*0.5;
  
5.找出部门10中所有经理和部门20中所有办事员的详细信息
  select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);

6.找出部门10中所有经理和部门20中所有 办事员 以及既不是 经理又不是办事员但薪金大于或等于2000的所有雇员的详细资料.
   select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20) or (job not in('MANAGER','CLERK') and (sal>=2000 and deptno=20);

7.找出收取佣金的雇员的不同工作. 
   select distinct job from emp where comm>0;
   select job from emp where comm is not null and comm != 0;       # : 收取佣金：comm is not null

8.找出不收取佣金或收取的佣金低于是200的雇员
   select *  from emp where comm<200 or comm is null;   # 不收取佣金：comm is null

9.找出各月最后一天受雇的所有雇员. 
   select *  from emp where hiredate = last_day(hiredate);

10.找出早于25年之前受雇的所有雇员. 
   select *  from emp where add_months(hiredate,25*12)<sysdate;   # add_months是增加月份的函数，若小于sysdate，就是25年前

11.查询只有首字母大写的所有雇员姓名. 
   select *  from emp where ename = initcap(ename);

12.查询所有雇员的信息,按照姓名排序. 
   select *  from emp order by ename;

13.查询所有雇员的信息,按薪金正序排序. 
    select *  from emp order by sal ;

14.查询所有经理,并按薪金正序 和 雇员姓名正序排序. 
    select *  from emp where job='MANAGER' order by sal ,ename;

15.查询姓名是MILLER的雇员.

16.查询薪金1500和3000,或者佣金高于500的雇员. 
   select *  from emp where sal in(1500,3000) or comm>500;
      
17.查询每个工种的人数. 
   select  job,count(job)  from emp group by job;

18.查询每个工种的人数.并按 工种的 人数排序.
     select  job,count(job) a  from emp group by job order by a;

19.查询MANAGER的平均工资。
   select round(avg(sal),2) from emp where job='MANAGER';  # 2 —— 保留两位小数

20.查询每个工种的人数,和平均工资,并按平均工资进行排序. 
   select count(job),avg(sal) a from emp group by job order by a;

21.查询部门 名称是SALES 的所有员工. 
   select * from emp ,(select * from dept where dname='SALES') a where emp.deptno=a.deptno;

22.查询部门名称是RESERCH的所有员工的平均工资. 
   select avg(sal) from emp ,(select * from dept where dname='REARCH') a where emp.deptno=a.deptno;

23.查询每个工种中雇员的最高薪金; 
   select job, max(sal) from emp group by job;

24.查询职务是经理的雇员的最低薪金;
   select job, min(sal) from emp where job = 'MANAGER';

25.新增两条记录
   empno为 57,ENAME是ANDY,JOB是MANAGER,SAL是3300,HIREDATE是'12-8月-90',DEPTNO是20;

   empno为 58,ENAME是JACK,JOB是MANAGER,SAL是3000,HIREDATE是'12-9月-90',DEPTNO是20;
   insert into emp values
       (57 , 'ANDY' , 'MANAGER' ,  null , '1990-08-12' , 3300 , null , 20),
       (58 , 'JACK' , 'MANAGER' ,  null , '1990-09-12' , 3000 , null , 20)

   修改EMPNO为 57的记录,将薪金涨20%,DEPTNO改成30;
     update emp set sal = sal*1.2 , deptno=30 where empno = 57
            
   修改ENAME是JACK的记录,将HIREDATE改成当前日期
      update emp set hiredate = sysdate where ename='JACK';
   
   删除EMPNO为58记录.
   delete  from emp where empno = 58;

26.查询平均工资高于1500的所有职务的名称和平均工资金额. 
    select job,avg(sal) from emp group by job having avg(sal)>1500

27.查询每个员工的详细信息.包括部门编号,部门名称及部门所在城市. 
   select emp.*,dname,loc from emp,dept where emp.deptno=dept.deptno;

28.按部门分组,查询出部门名称,及部门中员工的平均工资. 
   # 两表联查
   select a.*,dname from 
    (select round(avg(sal),2),deptno from emp group by deptno) a,dept where a.deptno=dept.deptno;

29.查询所有员工中工资数在前五的员工详细信息。并从高到低进行排序。
    select * from emp order by sal desc LIMIT 0,5

30.查询所有经理中工资第三高的员工的详细信息。
    select * from emp where job='MANAGER' order by sal desc LIMIT 2,1
   
31.找出所有员工中薪金 第五高 到 第八高 的员工信息。
    select * from emp order by sal desc LIMIT 4,4;

32.查询出SCOTT的上级领导的详细信息。
    select * from emp where empno in (select MGR from emp where ename='SCOTT');

33.查询出没有员工的部门。
    select * from dept where deptno not =(select distinct deptno from emp);

34．查询出平均高于2000的部门信息。
   select deptno, avg(sal) from emp group by deptno having avg(sal)>2000

35．向EMP表中插入一条记录 
 查询出所的员工的 员工编号，员工姓名，工资，职位，部门名称（包含未分配部门的员工，例如后插入的SCOTT）
  select * from emp e LEFT JOIN dept d on e.deptno=d.deptno

36．找到同名的所有员工
   select ename,count(ename) from emp group by ename having count(ename)>1

37.找出所有同名的员工后，通过一条语句只保留同名员工中的一个，其余的则删除掉。
select * from emp where 
    ename in (select ename from emp group by ename having count(ename)>1)

1.选择部门30中的雇员
  select * from emp where deptno=30;

2.列出所有经理的姓名,编号和部门.
  select ename,empno,deptno from emp where job='MANAGER' 

3.列出佣金高于薪金的雇员. 
   select * from emp where comm>sal;

4.找出佣金高于或等于薪金50%的雇员
  select * from emp where comm>=sal*0.5;

5.找出部门10中所有经理和部门20中所有办事员的详细信息
  select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);   
   
6.找出部门10中所有经理和部门20中所有办事员以及即不是经理又不是办事员但薪金
  大于或等于2000的所有雇员的详细资料.
    select * from emp 
      where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20) or  (job not in('MANAGER','CLERK') and sal>=2000 and deptno=20);

7.找出收取佣金的雇员的不同工作. 
  select distinct job from emp where comm>0;
  select job from emp where comm is not null and comm != 0 group by job

8.找出不收取佣金或收取的佣金低于是200的雇员
  select * from emp where comm<200 or comm>0;
  select * from emp where comm between 0 and 200
  
9.找出各月最后一天受雇的所有雇员.
  select * from emp where hiredate = last_day(hiredate);

10.找出早于25年之前受雇的所有雇员.
   SELECT * from emp where SUBDATE(hiredate,SYSDATE())>25

11.查询只有首字母大写的所有雇员姓名.

12.查询所有雇员的信息,按照姓名排序. 
   select * from emp order by ename;

13.查询所有雇员的信息,按薪金正序排序.
   select * from emp order by sal ;

14.查询所有经理,并按薪金正序和雇员姓名正序排序. 
   select * from emp where job='MANAGER' order by sal ,ename;

15.查询姓名是MILLER的雇员.
   select * from emp where ename='MILLER'

16.查询薪金1500和3000,或者佣金高于500的雇员. 
   select * from emp where sal in(1500,3000) or comm>500;

17.查询每个工种的人数. 
   select job,count(job)  from emp group by job  ;

18.查询每个工种的人数.并按工种的人数排序. 
   select job,count(job) a  from emp group by job order by a;

19.查询MANAGER的平均工资。
   select round(avg(sal),2) from emp where job='MANAGER';

20.查询每个工种的人数,和平均工资,并按平均工资进行排序. 
   select count(job),avg(sal) a from emp group by job order by a;

21.查询部门名称是SALES的所有员工.
   select * from emp where DEPTNO = 
   (SELECT deptno from dept where dname='SALES')

22.查询部门名称是RESERCH的所有员工的平均工资.
   select avg(sal) from emp where DEPTNO = 
   (SELECT deptno from dept where dname='RESERCH')

23.查询每个工种中雇员的最高薪金;
   select job, max(sal) from emp group by job;

24.查询职务是经理的雇员的最低薪金;
   select job, min(sal) from emp where job='MANAGER';

25.新增两条记录
   empno为57,ENAME是ANDY,JOB是MANAGER,SAL是3300,HIREDATE是'12-8月-90',DEPTNO是20;

   empno为58,ENAME是JACK,JOB是MANAGER,SAL是3000,HIREDATE是'12-9月-90',DEPTNO是20;
   insert into emp values(57,'ANDY','MANAGER',null,1990-08-12,3300,null,20),(58,'JACK','MANAGER',null,1990-09-12,3000,null,20)

修改EMPNO为57的记录,将薪金涨20%,DEPTNO改成30;
update emp set sal = sal*1.2 ,deptno=30 where empno = 57

修改ENAME是JACK的记录,将HIREDATE改成当前日期
update emp set hiredate = sysdate where ename='JACK';

删除EMPNO为58记录.
delete from emp where empno=58

26.查询平均工资高于1500的所有职务的名称和平均工资金额. 
     select job,avg(sal) from emp group by job having avg(sal)>1500

27.查询每个员工的详细信息.包括部门编号,部门名称及部门所在城市. 
    select emp.*,dname,loc from emp,dept where emp.deptno=dept.deptno;

28.按部门分组,查询出部门名称,及部门中员工的平均工资. 
    select e.deptno,e.a,d.DNAME from (SELECT deptno,avg(sal) a from emp GROUP BY deptno) e LEFT JOIN dept d on e.deptno=d.DEPTNO

29.查询所有员工中工资数在前五的员工详细信息。并从高到低进行排序。
    select * from emp ORDER BY sal desc LIMIT 0,5

30.查询所有经理中工资第三高的员工的详细信息。
     select * from emp where job='MANAGER' ORDER BY sal desc LIMIT 2,1

31.找出所有员工中薪金第五高到第八高的员工信息。
    select * from emp ORDER BY sal desc LIMIT 4,4

32.查询出SCOTT的上级领导的详细信息。
   select * from emp where empno=(SELECT mgr from emp where ename='SCOTT')

33.查询出没有员工的部门。
select * from emp e RIGHT JOIN dept d on e.DEPTNO=d.DEPTNO where e.EMPNO is null GROUP BY d.DEPTNO

34．查询出平均高于2000的部门信息。
    select deptno,avg(sal) from emp GROUP BY deptno having avg(sal)>2000

35．向EMP表中插入一条记录  
    insert into emp(empno,ename,sal,job,hiredate) values(7999,'SCOTT',2000,'MANAGER',sysdate());  

查询出所的员工的 员工编号，
员工姓名，工资，职位，部门名称（包含未分配部门的员工，例如后插入的SCOTT）
SELECT * from emp e LEFT JOIN dept d on e.deptno=d.deptno

36．找到同名的所有员工
    SELECT ename,count(ename) from emp group by ename having count(ename)>1

37.找出所有同名的员工后，通过一条语句只保留同名员工中的一个，其余的则删除掉。
    select * from emp where ename in (SELECT ename from emp group by ename having count(ename)>1)

FOR
select * from emp where  ename in (SELECT ename from emp group by ename  having count(ename)>1);
DELETE from emp where empno in 
(SELECT empno from emp LIMIT 0,1);
*/
SELECT * from emp where 
datediff(SYSDATE(),hiredate)>25*365

在这里插入图片描述

范高伦

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库的知识点

数据库mysqlSQL—>Structured Query Language结构化查询语言, 语句是重点：通俗地讲，数据库就是把一些数据整合到一起，其实质还只存在硬盘里特征：性能快捷、优化SQL语言、容易使用、多线程和可靠性、多用户支持、可移植性和开发源代码，遵循国际标准和国际化支持、为多种编程语言提供API数据 DML语言（Data Manipulation Language数据...
复制链接

扫一扫