MySQL学习笔记（二）

1、把查询结果中去除重复记录

 

2、连接查询 

从一张表中单独查询，称为单表查询。emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字，这种跨表查询，多张表联合起来查询数据，被称为连接查询。

（1）连接查询的分类

根据表连接的方式分类：内连接、外连接、全连接（几乎不用）

内连接又分为等值连接、非等值连接和自连接

外连接又分为左外连接（左连接）、右外连接（右连接）

（2）当两张表进行连接查询时，没有任何条件的限制会发生什么现象？

最终的查询结果条数，是两张表条数的乘积，这种现象被称为笛卡尔积现象

表的连接次数越多效率越低，尽量避免表的连接次数

例子：查询每个员工所在的部门名称

（3）避免笛卡尔积现象就是连接时加条件

 （4） 内连接的等值连接

例子：查询每个员工所在部门名称，显示员工名和部门名

 SQL92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面 

SQL99的优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

（5）内连接的非等值连接

例子：找出每个员工的薪资等级

（6）内连接的自连接

例子：查询员工的上级领导（一张表看成两张表）

（7）外连接

内连接的特点：查询出能够匹配上条件的数据，A和B两张表没有主次关系

外连接：两张表连接产生了主次关系

外连接的查询结果条数一定大于等于内连接的查询结果条数

 

（8）三张以上的表

select ...

from a join b on a和b的连接条件

join c on a和c的连接条件

join d  on a和d的连接条件

一条SQL中内连接和外连接可以混合

例子：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级 

3、子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询

select ...(select) from ...(select) where ...(select)

（1）where子句中的子查询

例子：找出比最低工资高的员工姓名和工资

（2）from子句中的字查询

 注意：from后面的子查询，可以将子查询的查询结果当做一张临时表

例子：找出每个岗位的平均工资的薪资等级

（3）select后面出现的子查询（了解）

例子：找出每个员工的部门名称

 注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，多余1条会报错

4、union合并查询结果集

例子：查询工作岗位是MANAGER和SALESMAN的员工

a连接b连接c

a 10条记录 

b 10条记录 

c 10条记录 

匹配次数是：1000

a连接b一个结果：10*10=100次

a连接c一个结果：10*10=100次

使用union的话：100+100=200次

5、limit（重要）

limit将查询结果集的一部分取出来，通常使用在分页查询当中（分页作用是提升用户体验）

完整用法：limit startIndext,length

缺省用法：limit 5; 这是取前5

注意：mysql当中limit在order by之后执行

例子：按照薪资降序，取出排名在前5名的员工

分页

 每页显示3条记录

第一页：limit 0,3

第二页：limit 3,3

第三页：limit 6,3

第四页：limit 9,3

...

每页显示pageSize条记录

第pageNo页：limit (pageNo-1)*pageSize, pageSize

6、关于DQL语句的总结

select ... from ... where ... group by ... having ... order by ... limit ...

执行顺序：from、where、group by、having、select、order by、limit

7、表的创建（建表）

建表属于DDL语句，DDL包括：create、drop、alter

（1）语法

create table 表名(字段名1 数据类型,字段名2 数据类型, 字段名3 数据类型);

表名建议以t_或者tbl_开始，可读性强

（2）关于mysql中常见的数据类型

• varchar：可变长度的字符串，最长255。节省空间，会根据实际的数据长度动态分配空间。优点节省空间，缺点需要动态分配空间，速度慢。
• char：定长字符串，最长255。不管实际的数据长度，分配固定长度的空间取存储数据。优点不需要动态分配空间，速度快，缺点使用不当可能会导致空间的浪费。

比如性别字段选char，姓名字段选varchar

• int：数字中的整数型，最长11，等同于java中的int
• bigint：数字中的长整型，等同于java中的long
• float：单精度浮点型数据
• double：双精度浮点型数据
• date：短日期
• datetime：长日期
• clob：字符大对象，最多可以存储4G的字符串，超过255个字符的都要采用CLOB大对象来存储。比如存储一篇文章。
• blob：二进制大对象，Binary Large Object，专门用来存储图片、声音、视频等流媒体数据。往BLOB类型的字段上插入数据时，例如插入一个图片、视频等，需要使用IO流才行。

举例：t_movie电影表

电影编号 no(bigint)

电影名 name(vachar)

描述信息 description(clob)

上映日期 playtime(data)

时长 time(double)

海报 image(blob)

类型 type(char)

（3）创建一个学生表（学号、姓名、年龄、性别、邮箱地址）

create table t_student(no int, name varchar(32), age int(3), sex char(1),email varchar(255));

删除表

drop table t_student;  当这张表不存在的时候会报错

drop table if exits t_student;

（4）插入数据

insert into 表名(字段名1,字段名2,字段名3,...) values (值1,值2,值3,...)；

注意：字段名要和值一一对应，数量要对应，数据类型要对应

数据库中的命名规范：所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接

注意：insert语句但凡是执行成功了，必然会多一条记录。没有给其他字段指定值的话，默认值是NULL

创建表时给默认值：

create table t_student(no int, name varchar(32), age int(3), sex char(1) default '女',email varchar(255));

insert语句的字段名可以省略，但是值都要按顺序写上：

（5）插入日期 

数字格式化：format(数据,'格式')

str_to_data：将字符串varchar类型转换成date类型，通常使用在插入insert上

date_format：将date类型转换成具有一定格式的varchar字符串类型，通常使用在查询select上

mysql的日期格式：

• %Y 年
• %m 月
• %d 日
• %h 时
• %i 分
• %s 秒

str_to_date

 如果提供的字符串是'%Y-%m-%d'的格式，str_to_date就可以不写了

date_format

以上的SQL语句实际上是进行了默认的日期格式化，自动将数据库中的date类型转换成varchar类型，并且采用的格式是mysql默认的日期格式：%Y-%m-%d

 （6）date和datetime两个类型的区别

date是短日期：只包括年月日，默认格式%Y-%m-%d

datetime是长日期：包括年月日时分秒，默认格式%Y-%m-%d %h:%i:%s

在mysql中获取系统当前时间使用now()函数，并且获取的时间带有时分秒信息

（7）修改update

语法格式：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3...where 条件;

注意：没有条件限制会导致所有数据全部更新

（8）删除数据delete

语法格式：delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除