数据库重点

最新推荐文章于 2024-04-12 15:47:51 发布

estelle0725

最新推荐文章于 2024-04-12 15:47:51 发布

阅读量265

点赞数

分类专栏：面试文章标签： sql

面试专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.数据库三大范式

https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html

1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

2．第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

2.数据库优化的方法

关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂
偶尔发现了这篇文章，总结得很经典，文章流量也很大，所以拿到自己的总结文集中，积累优质文章，提升个人能力，希望对大家今后开发中也有帮助

1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2、使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

delete from customerinfo

where CustomerID not in(select CustomerID from salesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）…替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

select* from customerinfo

where CustomerID not in(select ustomerID from salesinfo)

如果使用连接（JOIN）…来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

select* from customerinfo

left join salesinfo on customerinfo.CustomerID=salesinfo.CustomerID

where salesinfo.CustomerID isnull

连接（JOIN）…之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

select Name,Phone from client UNION

select Name,BirthDate from author UNION

select Name,Supplier from product

4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

begin; 
insert into salesinfo set CustomerID=14;
update inventory set Quantity=11 where item='book'; 
commit;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

lock table inventory write 
select Quantity from inventory where Item='book';
update inventory set Quantity=11 where Item='book';
unlocktables

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

 create table customerinfo( CustomerIDINT    not null ,PRIMARYKEY(CustomerID))TYPE=INNODB;
    
create table salesinfo( SalesIDNT  not null,CustomerIDINT    not null,
PRIMARYKEY(CustomerID,SalesID),
    
FOREIGNKEY(CustomerID)    REFERENCES    customerinfo(CustomerID)    ON    DELETE    CASCADE)TYPE=INNODB;

注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。

7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”…字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

下面是应该注意的几个方面。

首先，最好是在相同类型的字段间进行比较的操作。

在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

其次，在建有索引的字段上尽量不要使用函数进行操作。

例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
例如下面的查询将会比较表中的每一条记录。

SELECT    *    FROM    books

WHERE    name    like"MySQL%"

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT    *    FROM    books

WHERE    name＞="MySQL"    andname    ＜"MySQM"

最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

3.索引的作用以及优缺点

来源：https://www.cnblogs.com/shuaiandjun/p/5648020.html
为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：

在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

创建索引的方法和索引的特征创建索引的方法创建索引有多种方法，这些方法包括直接创建索引的方法和间接创建索引的方法。直接创建索引，例如使用CREATE INDEX语句或者使用创建索引向导，间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引。虽然，这两种方法都可以创建索引，但是，它们创建索引的具体内容是有区别的。使用CREATE INDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的索引。在使用这种方式创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合性，也就是说，既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引。

通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。

当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

索引的特征索引有两个特征，即唯一性索引和复合索引。唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQL Server自动创建一个唯一性索引。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同的姓名。

复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。

3.内连接外连接区别
内连接，也被称为自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据，舍弃了不匹配的数据。由于内连接是从结果表中删除与其他连接表中没有匹配的所有行，所以内连接可能会造成信息的丢失。内连接语法如下：

select fieldlist from table1 [inner] join table2 on table1.column = table2.column

内连接是保证两个表中所有行都满足连接条件，而外连接则不然。
外连接不仅包含符合连接条件的行，还包含左表(左连接时)、右表(右连接时)或两个边接表(全外连接)中的所有数据行。SQL外连接共有三种类型：左外连接(关键字为LEFT OUTER JOIN)、右外连接(关键字为RIGHT OUTER JOIN)和全外连接(关键字为FULL OUTER JOIN)。外连接的用法和内连接一样，只是将INNER JOIN关键字替换为相应的外连接关键字即可。

内连接只显示符合连接条件的记录，外连接除了显示符合条件的记录外，还显示表中的记录，例如，如果使用左外连接，还显示左表中的记录。

4.分页语句

1.oracle数据库分页
select * from
(select a.*,rownum rc from 表名 where rownum<=endrow) a
where a.rc>=startrow;

2.DB2数据库分页
Select * from
(select rownumber() over() as rc,a.* from
(select * from 表名 order by 列名) as a)
where rc between startrow and endrow;

3.SQL Server 2000数据库分页
Select top pagesize * from 表名
where 列名 not in
(select top pagesize*page 列名 from 表名 order by 列名)
order by 列名;

4.SQL Server 2005数据库分页
Select * from
(select 列名,row_搜索number() over(order by 列名1) as 别名from 表名) as t
where t.列名1>=startrow and t.列名1<=endrow;

5.MySQL数据库分页
Select * from 表名 limit startrow,pagesize;
(Pagesize为每页显示的记录条数)

6.通用模式
select * from
(select * from student where sid not in
(select sid from student where rownum<=(currentPage-1)*pageSize)
) where rownum <=pageSize;

作者：星空dream
来源：CSDN
原文：https://blog.csdn.net/qq_17045385/article/details/53750167
版权声明：本文为博主原创文章，转载请附上博文链接！

5.基本的增删改查数据库语句要会多表连接 avg()、sum()、count()、order by、top()、limit

CREATE DATABASE mydb2;
SHOW DATABASES ;
ALTER DATABASE mydb2 CHARACTER SET utf8;
SHOW TABLES ;
SELECT database();
USE mydb2;

CREATE TABLE stu(
   sid CHAR(6),
  sname VARCHAR(50),
  age INT,
  gender VARCHAR(50)
);

INSERT INTO stu VALUES
  ('S_1001','liuYi',35,'male'),
  ('S_1002','chenEr',15,'female'),
  ('S_1003','zhangSan','95','male'),
  ('S_1004','liSi',65,'female'),
  ('S_1005','wangWu',55,'male'),
  ('S_1006','zhaoLiu',75,'female'),
  ('S_1007','sunQi',25,'male'),
  ('S_1008','zhouBa',45,'female'),
  ('S_1009','wuJiu',85,'male'),
  ('S_1010','zhengShi',5,'female'),
  ('S_1011','xxx',NULL ,NULL );

#1 基础查询
#1.1 查询所有列
SELECT *FROM stu;
#1.2 查询指定列
SELECT sid, age FROM stu;

#2 条件查询
#2.2 查询性别为女，并且年龄50的记录
SELECT *FROM stu WHERE gender='male' AND age=50;
#2.3　查询学号为S_1001，或者姓名为liSi的记录
SELECT *FROM stu WHERE sid='S_1001' OR sname='liSi';
#2.4　查询学号为S_1001，S_1002，S_1003的记录
SELECT *FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');
#2.5　查询学号不是S_1001，S_1002，S_1003的记录
SELECT *FROM stu WHERE sid NOT IN ('S_1001','S_1002','S_1003');
#2.6　查询年龄为null的记录
SELECT *FROM stu WHERE age IS NULL ;
#2.7　查询年龄在20到40之间的学生记录
SELECT *FROM stu WHERE age BETWEEN 20 AND 40;
#2.8　查询性别非男的学生记录
SELECT *FROM stu WHERE gender != 'female';
SELECT *FROM stu WHERE NOT gender = 'female';
#2.9　查询姓名不为null的学生记录
SELECT *FROM stu WHERE sname IS NOT NULL ;

#3　模糊查询
#3.1　查询姓名由5个字母构成的学生记录
SELECT *FROM stu WHERE sname LIKE '_____';
#3.2　查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT *FROM stu WHERE sname LIKE '____i' ;
#3.3　查询姓名以“z”开头的学生记录
SELECT *FROM stu WHERE sname LIKE 'z_____';
#3.4　查询姓名中第2个字母为“i”的学生记录
SELECT *FROM stu WHERE sname LIKE '_i___';
#3.5　查询姓名中包含“a”字母的学生记录
SELECT *FROM stu WHERE sname LIKE '%a';




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

);

INSERT INTO emp VALUES
  (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL ,20),
  (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
  (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
  (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
  (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
  (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
  (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
  (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
  (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
  (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
  (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
  (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
  (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
  (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

SELECT *FROM emp;

#4　字段控制查询
#4.1　去除重复记录
#    去重复查询薪水

SELECT DISTINCT sal FROM emp;
#4.2　查看雇员的月薪与佣金之和
SELECT *,ifnull(sal,0) + ifnull(comm,0) FROM emp;
#4.3　给列名添加别名
SELECT *,ifnull(sal,0) + ifnull(comm,0) AS total FROM emp;


#5　排序
#5.1　查询所有学生记录，按年龄升序排序
SELECT *FROM stu ORDER BY age ASC ;
#5.2　查询所有学生记录，按年龄降序排序
SELECT *FROM stu ORDER BY age DESC ;
#5.3　查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT *FROM emp ORDER BY sal DESC, empno ASC ;

#6　聚合函数
#6.1　COUNT
#查询emp表中记录数
SELECT count(*) AS cnt FROM emp;
#查询emp表中有佣金的人数
SELECT count(comm)AS cnt FROM emp;
#查询emp表中月薪大于2500的人数
SELECT count(*)FROM emp WHERE sal > 2500;
#统计月薪与佣金之和大于2500元的人数
SELECT count(*)AS cnt FROM emp WHERE ifnull(sal,0) + ifnull(comm,0) > 2500;
#查询有佣金的人数，以及有领导的人数
SELECT count(comm),count(mgr)FROM emp;
#6.2　SUM和AVG
#查询所有雇员月薪和
SELECT sum(sal)FROM emp;
#查询所有雇员月薪和，以及所有雇员佣金和
SELECT sum(sal),sum(comm)FROM emp;
#查询所有雇员月薪+佣金和
SELECT sum(ifnull(sal,0) + ifnull(comm,0)) FROM emp ;
#统计所有员工平均工资
SELECT avg(sal)FROM emp;

#6.3　MAX和MIN
#查询最高工资和最低工资
SELECT max(sal), min(sal)FROM emp;

#查询各部门的人数
SELECT deptno, count(*)FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号和每个部门的工资和：
SELECT deptno,sum(sal)FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门的人数：
SELECT deptno,count(*)FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno,count(*)FROM emp WHERE sal > 1500 GROUP BY deptno;
-- 查询工资总和大于9000的部门编号以及工资和：
SELECT deptno,sum(sal)FROM emp GROUP BY deptno HAVING sum(sal) > 9000;
-- 查询工资大于1500的,工资总和大于6000的部门编号以及工资和：
SELECT deptno,sum(sal)FROM emp WHERE sal>1500 GROUP BY deptno HAVING sum(sal)>6000;

#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#
#1,创建表,结构如下:
#   表名:student
use mydb2;
drop TABLE student;
CREATE TABLE student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10) UNIQUE NOT NULL ,
  gender VARCHAR(50) DEFAULT 'man',
  age INT NOT NULL ,
  chinese DOUBLE NOT NULL ,
  math DOUBLE NOT NULL ,
  english DOUBLE NOT NULL
  );
INSERT INTO student (name, gender,age, chinese, math, english) VALUES
  ('zhangSan',DEFAULT,13,90,61.25,40),
  ('liSi','woman',13,92,71.50,48),
  ('wangWu',DEFAULT,14,60,66.50,57),
  ('zhaoLiu',DEFAULT,14,59,0.00,32),
  ('zhouQ','woman',13,90,85.00,80)
  ;
SELECT * FROM student;
DESC student;
#2,删除表中平均分低于60的数据.
DELETE FROM hw_stu WHERE sum(math+chinese+english)/3 < 60 ;

DELETE FROM hw_stu WHERE avg(math) + avg(chinese) + avg(english) < 60 ;
#3,将name列的类型改为varchar(50).
ALTER TABLE student CHANGE name name VARCHAR(50);
#4,查询表中数据的平均年龄.
SELECT avg(age)FROM student;
#5,将age列删除.
ALTER TABLE student DROP age;
#6,查询表中女生中平均分高于80分和男生中平均分高于60分的数据.
SELECT name,math,chinese,english,sum(math+chinese+english)/3 > 80,sum(math+chinese+english)/3 > 60 AND gender != 'woman'FROM hw_stu GROUP BY id;
#7,将表名修改为hw_stu
RENAME TABLE student TO hw_stu;
#8,增加列age int
ALTER TABLE hw_stu ADD age INT;
SELECT *FROM hw_stu;
#1,查询表中语文成绩比数学成绩高的所有数据的id,并且按照语文成绩升序排序,如果语文成绩相同,则按照数学成绩降序排序.
SELECT id,count(*)FROM hw_stu WHERE chinese>math GROUP BY id ORDER BY chinese ASC ,math DESC ;
#2,将表中英语成绩在40到60之间的,男生的数学成绩修改为80;
UPDATE hw_stu SET math=80 WHERE english BETWEEN 40 AND 60;
#3,练习五个聚合函数的使用,求最值,求数量,求平均值,求和.
SELECT max(math),min(chinese)FROM hw_stu;
SELECT count(english)FROM hw_stu;
SELECT avg(math)FROM hw_stu;
SELECT sum(chinese)FROM hw_stu;
#4,查询每人的平均分.
SELECT id,sum(math+chinese+english)/3 AS 平均分 FROM hw_stu GROUP BY id;
#创建表:
#表名:hw_dmt
#dmtid:部门编号 为主键
#dname:部门名称
#dloc:部门地址
USE mydb2;
DROP TABLE hw_dmt;
CREATE TABLE hw_dmt(
dmtid INT PRIMARY KEY ,
dname VARCHAR(10) UNIQUE NOT NULL ,
dloc VARCHAR(50)NOT NULL
);
INSERT INTO hw_dmt (dmtid,dname, dloc) VALUES
(10,'董事会','北京'),
(20,'财务部','上海'),
(30,'销售部','广州'),
(40,'出题部','大连')
;
SELECT *FROM hw_dmt;
#创建表:
#表名:hw_emp
#id:员工编号:为主键
#mgrid:上级的id号
#dmtid:部门编号  为外键 reference 表hw_dmt
#hiredate:入职日期 类型为date
#pay:工资
#prize:奖金
#name: 姓名
#job: 工作
USE mydb2;
DROP TABLE hw_emp;
CREATE TABLE hw_emp(
  id INT PRIMARY KEY ,
  name VARCHAR(50)NOT NULL ,
  job VARCHAR(50)NOT NULL ,
  mgrid INT ,
  hiredate DATE NOT NULL ,
  pay DOUBLE NOT NULL ,
  prize DOUBLE,
  dmtid INT REFERENCES hw_dmt(dmtid)
);
INSERT INTO hw_emp (id,name,job, mgrid,hiredate,pay, prize,dmtid) VALUES
  (1001,'张三疯','董事长',NULL ,'2000-01-01',88888,88888,10),
  (1002, '刘备','经理',1001,'2004-03.05',40000,NULL ,20),
  (1003,'关羽','会计',1002,'2003-07-09',12000,8000,20),
  (1004,'玉皇大帝','经理',1001,'2000-01-01',40000,40000,30),
  (1005,'李静','销售员',1004,'2002-11-11',8000,20000,30),
  (1006,'太白金星','销售员',1004,'2001-05-06',9000,30000,30),
  (1007,'张飞','会计',1002,'2005-02-27',10000,4000,20),
  (1008,'萧炎','清洁工',NULL ,'1995-08-21',99999,99999,40)
;
SELECT *FROM hw_emp;

#1,查询每个部门中工资大于部门平均工资的员工的姓名,工资
SELECT A.name,A.dmtid,A.pay,B.AVG FROM hw_emp A,
(SELECT dmtid,AVG(pay) AVG FROM hw_emp GROUP BY dmtid) B
WHERE A.dmtid = B.dmtid AND A.pay >B.AVG;
#2,查询出名字由四个字组成的管理人员姓名和综合薪水
SELECT name,pay+prize FROM hw_emp WHERE name LIKE '____' AND mgrid = 1001;
#3,找出奖金高于工资90%,有上级,且入职日期在2001年入职的人员的姓名,部门编号
SELECT name,dmtid FROM hw_emp WHERE  (pay*0.9) < prize OR  mgrid IS NULL  OR hiredate LIKE '2001_______';
#4,找出部门编号为20中所有经理,和部门编号为30中所有销售员的详细资料
SELECT * FROM hw_emp WHERE dmtid = 20 AND job='经理' OR dmtid = 30 AND job = '销售员';
#5,查询有两个名字的员工的工种个数和两个名字员工中的最大工资
SELECT job,concat(job),max(pay)FROM hw_emp WHERE name LIKE '__';
#6,查询每个工种的员工数量,平均工资,按照平均工资降序排序
SELECT job,avg(pay),count(*)FROM hw_emp GROUP BY hw_emp.job ORDER BY avg(pay) DESC ;