mysql 单表关联_MySQL 基础之 单表、多表联查

使用和不使用not null 的区别：

不使用： 查询时用‘name is null’ 作为条件

mysql>create table t8(

-> id int auto_increment primary key,

-> name varchar(32),

-> email varchar(32)

-> )charset=utf8;

mysql>insert into t8(email) values ('allen');

mysql> select * from t8;

+----+------+-------+

| id | name | email |

+----+------+-------+

| 1 | NULL | allen |

+----+------+-------+

1 row in set (0.01 sec)

mysql> select * from t8 where name is null;

+----+------+-------+

| id | name | email |

+----+------+-------+

| 1 | NULL | allen |

+----+------+-------+

1 row in set (0.00 sec)

使用：查询时用‘name=’‘ ’作为查询条件

mysql> create table t9(

-> id int auto_increment primary key,

-> name varchar(32) not null default '',

-> email varchar(32) not null default ''

-> )charset=utf8;

mysql> insert into t9 (email) values ('allen');

mysql> select * from t9;

+----+------+-------+

| id | name | email |

+----+------+-------+

| 1 | | allen |

+----+------+-------+

1 row in set (0.00 sec)

mysql> select * from t9 where name='';

+----+------+-------+

| id | name | email |

+----+------+-------+

| 1 | | allen |

+----+------+-------+

1 row in set (0.01 sec)

单表操作：

单表查询的语法：

select 字段1，字段2 from 表名

where 条件

group by field

having 筛选

order by field

limit 限制条数

分组：group by

分组指的是：

将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

用法：select 聚合函数，字段名 from 表名 group by 分组的字段；

group by 是分组的关键词， 必须和聚合函数 一起出现

where 条件语句和groupby分组语句的先后顺序：

where > group by > having(*********)

例子：

创建表：

create table emp(

id int not null unique auto_increment,

name varchar(20) not null,

gender enum('male','female') not null default 'male', #大部分是男的

age int(3) unsigned not null default 28,

hire_date date not null,

post varchar(50),

post_comment varchar(100),

salary double(15,2),

office int, #一个部门一个屋子

depart_id int

);

插入内容：

insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values

('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部

('alex','male',78,'20150302','teacher',1000000.31,401,1),

('wupeiqi','male',81,'20130305','teacher',8300,401,1),

('yuanhao','male',73,'20140701','teacher',3500,401,1),

('liwenzhou','male',28,'20121101','teacher',2100,401,1),

('jingliyang','female',18,'20110211','teacher',9000,401,1),

('jinxin','male',18,'19000301','teacher',30000,401,1),

('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门

('丫丫','female',38,'20101101','sale',2000.35,402,2),

('丁丁','female',18,'20110312','sale',1000.37,402,2),

('星星','female',18,'20160513','sale',3000.29,402,2),

('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门

('程咬金','male',18,'19970312','operation',20000,403,3),

('