数据库表的约束,关联与查询

一.表的约束

1.设置t_hero的主键为id

mysql> alter table t_hero add primary key(id);

 2.设置t_hero id属性非空

mysql> alter table t_hero modify id int not null;

3.设置name属性为非空非重复 

mysql> alter table t_hero modify name varchar(255) not null;
mysql> alter table t_hero add unique (name);

4.设置检查约束

mysql> alter table t_hero add constraint chk_gender check(gender in ('男', '女'));

 
mysql> desc t_hero;

 二.查询语句练习

1.基本查询语句

-- 查询所有数据
select * from t_hero;

-- 查询需要的字段信息
select id, name, password from t_hero;
-- 查询一个字段，一个等值条件
select name from t_hero where id = 1;

-- select 字段列表  from 表名称  where 条件

 2.聚合函数使用

mysql> select count(*) from t_hero;
+----------+
| count(*) |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)

3.查询时的别称使用

mysql> select count(*) 总数 from t_hero;
+------+
| 总数 |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

 4.常见条件查询

mysql> select id,name,address from t_hero where id in (2,4,8);
+----+--------+------------+
| id | name   | address    |
+----+--------+------------+
|  2 | 关羽   | 河东解良   |
|  4 | 诸葛亮 | 南阳卧龙岗 |
|  8 | 武松   | 清河县     |
+----+--------+------------+

 5.模糊查询

mysql> select * from t_hero where name like "猪%";
+----+--------+--------+--------+---------+
| id | name   | book   | gender | address |
+----+--------+--------+--------+---------+
| 13 | 猪八戒 | 西游记 | 男     | 高老庄  |
+----+--------+--------+--------+---------+

6.逻辑运算符and

select id,name,address from t_hero where name like "林%" and book = "红楼梦";
+----+--------+----------------+
| id | name   | address        |
+----+--------+----------------+
| 17 | 林黛玉 | 金陵贾府潇湘馆   |
+----+--------+----------------+

7. 分组查询

mysql> select book from t_hero group by book;
+----------+
| book     |
+----------+
| 三国演义 |
| 水浒传   |
| 西游记   |
| 红楼梦   |
+----------+

8.结果排序 

mysql> select * from t_hero where id <= 6 order by id;
+----+--------+----------+--------+------------+
| id | name   | book     | gender | address    |
+----+--------+----------+--------+------------+
|  1 | 刘备   | 三国演义 | 男     | 幽州涿郡   |
|  2 | 关羽   | 三国演义 | 男     | 河东解良   |
|  3 | 张飞   | 三国演义 | 男     | 幽州涿郡   |
|  4 | 诸葛亮 | 三国演义 | 男     | 南阳卧龙岗 |
|  5 | 曹操   | 三国演义 | 男     | 沛国谯县   |
|  6 | 宋江   | 水浒传   | 男     | 山东郓城县 |
+----+--------+----------+--------+------------+

三.外键与多表关联

1.创建用于外键关联的表

mysql> select * from bookName;
+----+----------+
| id | name     |
+----+----------+
|  1 | 三国演义 |
|  2 | 水浒传   |
|  4 | 红楼梦   |
|  3 | 西游记   |
+----+----------+

2.创建外键相关字段

mysql> alter table t_hero add bookNum int;

mysql> desc t_hero;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| id      | int             | NO   | PRI | NULL    |       |
| name    | varchar(255)    | NO   | UNI | NULL    |       |
| book    | varchar(50)     | YES  |     | NULL    |       |
| gender  | enum('男','女') | YES  |     | NULL    |       |
| address | varchar(100)    | YES  |     | NULL    |       |
| bookNum | int             | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+

3.多表关联

mysql> alter table t_hero
    -> ADD CONSTRAINT fk_hero_book
    -> foreign key(bookNum) references bookName(id);

mysql> UPDATE t_hero
    -> SET bookNum = 1
    -> WHERE book = "三国演义";

mysql> UPDATE t_hero
    -> SET bookNum = 2
    -> WHERE book = "水浒传";

mysql> UPDATE t_hero
    -> SET bookNum = 3
    -> WHERE book = "西游记";
 
mysql> UPDATE t_hero
    -> SET bookNum = 4
    -> WHERE book = "红楼梦";