自我总结30
null和notnull
使用null的时候
create table t8(
id int auto_increment primary key,
name varchar(32),
email varchar(32)
)charset=utf8;
mysql> insert into t8 (email) values ('xxxx');
Query OK, 1 row affected (0.05 sec)
mysql> select * from t8;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | NULL | xxxx |
+----+------+-------+
1 row in set (0.00 sec)
mysql> select * from t8;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | NULL | xxxx |
+----+------+-------+
1 row in set (0.00 sec)
mysql> select * from t8 where name='';
Empty set (0.00 sec)
mysql> select * from t8 where name is null;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | NULL | xxxx |
+----+------+-------+
1 row in set (0.01 sec)
使用 notnull 的时候
使用 notnull的时候：
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 ('xxxx');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t9;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | | xxxx |
+----+------+-------+
1 row in set (0.00 sec)
mysql> select * from t9 where name='';
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | | xxxx |
+----+------+-------+
1 row in set (0.00 sec)
单表操作
分组
分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
select 聚合函数, 选取的字段 from employee group by 分组的字段;
group by: 是分组的关键词
group by 必须和 聚合函数(count) 出现
聚合函数
MAX 最大
MIN 最小
SUM 求和
AVG 平均数
COUNT 计数
as # 聚合函数结果作为新参数
例子： 以性别为例， 进行分组， 统计一下男生和女生的人数是多少个：
select count(id), gender from employee group by gender;
+-----------+--------+
| count(id) | gender |
+-----------+--------+
| 10 | male |
| 8 | female |
+-----------+--------+
表示对group by 之后的数据， 进行再一次的二次筛选
mysql> select depart_id,avg(age) from employee group by depart_id ;
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
| 1 | 45.2500 |
| 2 | 30.0000 |
| 3 | 20.0000 |
+-----------+----------+
3 rows in set (0.00 sec)
mysql> select depart_id,avg(age) from employee group by depart_id having avg(age) > 35;
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
| 1 | 45.2500 |
+-----------+----------+
1 row in set (0.00 sec)
where 条件语句和groupby分组语句的先后顺序：
where > group by > having(*********)
升序降序
升序降序
order by
order by 字段名 asc （升序） desc(降序);
例子： select * from employee order by age desc, id desc;
先根据年龄升序，然后如果年纪一样的时候，再根据id进行降序
分页
limit offset (取当前行数据索引),size（取多少条数据）;
例子：mysql> select * from employee limit 0,10;
总结：
使用顺序：
select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件；
where >group by >having>order by > order by > limit
多表的操作
外键
使用的原因：
a. 减少占用的空间
b. 只需要修改department表中一次， 其余的表中的数据就会相应的修改
一对多
constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
create table department(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset utf8;
insert into department (name) values ('研发部');
insert into department (name) values ('运维部');
insert into department (name) values ('前台部');
insert into department (name) values ('小卖部');
create table userinfo (
id int auto_increment primary key,
name varchar(32) not null default '',
depart_id int not null default 1,
constraint fk_user_depart foreign key (depart_id) references department(id)
#constraint fk_user_depart foreign key (depart_id) references department(id),
#constraint fk_user_depart foreign key (depart_id) references department(id),
)charset utf8;
insert into userinfo (name, depart_id) values ('zekai', 1);
insert into userinfo (name, depart_id) values ('xxx', 2);
insert into userinfo (name, depart_id) values ('zekai1', 3);
insert into userinfo (name, depart_id) values ('zekai2', 4);
insert into userinfo (name, depart_id) values ('zekai3', 1);
insert into userinfo (name, depart_id) values ('zekai4', 2);
insert into userinfo (name, depart_id) values ('zekai4', 5);
多对多
create table boy (
id int auto_increment primary key,
bname varchar(32) not null default ''
)charset utf8;
insert into boy (bname) values ('zhangsan'),('lisi'),('zhaoliu');
create table girl (
id int auto_increment primary key,
gname varchar(32) not null default ''
)charset utf8;
insert into girl (gname) values ('cuihua'),('gangdan'),('jianguo');
create table boy2girl (
id int auto_increment primary key,
bid int not null default 1,
gid int not null default 1,
constraint fk_boy2girl_boy foreign key (bid) references boy(id),
constraint fk_boy2girl_girl foreign key (gid) references girl(id)
)charset utf8;
insert into boy2girl (bid, gid) values (1,1),(1,2),(2,3),(3,3),(2,2);
mysql> select * from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;
+----+----------+------+------+------+------+---------+
| id | bname | id | bid | gid | id | gname |
+----+----------+------+------+------+------+---------+
| 1 | zhangsan | 1 | 1 | 1 | 1 | cuihua |
| 1 | zhangsan | 2 | 1 | 2 | 2 | gangdan |
| 2 | lisi | 5 | 2 | 2 | 2 | gangdan |
| 2 | lisi | 3 | 2 | 3 | 3 | jianguo |
| 3 | zhaoliu | 4 | 3 | 3 | 3 | jianguo |
+----+----------+------+------+------+------+---------+
5 rows in set (0.00 sec)
mysql> select bname, gname from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;
+----------+---------+
| bname | gname |
+----------+---------+
| zhangsan | cuihua |
| zhangsan | gangdan |
| lisi | gangdan |
| lisi | jianguo |
| zhaoliu | jianguo |
+----------+---------+
5 rows in set (0.00 sec)
mysql> select bname, gname from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid where bname='zhangsan';
+----------+---------+
| bname | gname |
+----------+---------+
| zhangsan | cuihua |
| zhangsan | gangdan |
+----------+---------+
2 rows in set (0.02 sec)
一对一
user :
id name age
1 zekai 18
2 zhangsan 23
3 xxxx 19
由于salary是比较敏感的字段，因此我们需要将此字段单独拆出来， 变成一张独立的表
private：
id salary uid (外键 + unique)
1 5000 1
2 6000 2
3 3000 3
create table user (
id int auto_increment primary key,
name varchar(32) not null default ''
)charset=utf8;
insert into user (name) values ('zhangsan'),('zekai'),('kkk');
create table priv(
id int auto_increment primary key,
salary int not null default 0,
uid int not null default 1,
constraint fk_priv_user foreign key (uid) references user(id),
unique(uid) # 唯一
)charset=utf8;
insert into priv (salary, uid) values (2000, 1);
insert into priv (salary, uid) values (2800, 2);
insert into priv (salary, uid) values (3000, 3);
错误示例：
insert into priv (salary, uid) values (6000, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'uid'
多表联查
left join 。。。 on
right join ... on
inner join
