一、表结构 1.约束条件，限制字段赋值 mysql> create table db1.t7( -> name char(10) not null, -> age tinyint unsigend default 19, -> class char(7) not null default "abc", -> pay float(7,2) default 28000); mysql> desc db1.t7; 列 类型 是否允许为空 主键 默认值 +-------+---------------------+------+-----+----------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+----------+-------+ | name | char(10) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | 19 | | | class | char(7) | NO | | abc | | | pay | float(7,2) | YES | | 28000.00 | | +-------+---------------------+------+-----+----------+-------+ 4 rows in set (0.01 sec)
2.修改表结构 mysql> desc db2.stuinfo; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 在db2.stuinfo表中添加sex列类型为enum("boy","girl")，不允许为空值，默认为boy mysql> alter table db2.stuinfo -> add -> sex enum("boy","girl") not null default "boy"; 在db2.stuinfo表中添加stu_id列类型为int，添加位置在第一列 mysql> alter table db2.stuinfo -> add -> stu_id int first; 在db2.stuinfo表中添加mail列类型为char(50)，默认为aaa@qq.com，添加位置为name列之后 mysql> alter table db2.stuinfo -> add -> mail char(50) default "aaa@qq.com" after name; mysql> desc db2.stuinfo; +--------+--------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------+------+-----+------------+-------+ | stu_id | int(11) | YES | | NULL | | | name | char(10) | YES | | NULL | | | mail | char(50) | YES | | aaa@qq.com | | | age | int(11) | YES | | NULL | | | sex | enum('boy','girl') | NO | | boy | | +--------+--------------------+------+-----+------------+-------+ 在db2.stuinfo表中修改age列类型为int，不允许为空值，默认为19 mysql> alter table db2.stuinfo -> modify age int not null default 19; 在db2.stuinfo表中修改name列类型为char(10)，修改位置为mail列之后 mysql> alter table db2.stuinfo -> modify -> name char(10) after mail; 在db2.stuinfo表中修改mail列名称为email列类型为char(50)，默认值为aaa@qq.com mysql> alter table db2.stuinfo -> change mail -> email char(50) default "aaa@qq.com"; mysql> desc db2.stuinfo; +--------+--------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------+------+-----+------------+-------+ | stu_id | int(11) | YES | | NULL | | | email | char(50) | YES | | aaa@qq.com | | | name | char(10) | YES | | NULL | | | age | int(11) | NO | | 19 | | | sex | enum('boy','girl') | NO | | boy | | +--------+--------------------+------+-----+------------+-------+ 删除db2.stuinfo表中的stu_id，drop age列 mysql> alter table db2.stuinfo -> drop stu_id, -> drop age; 修改db2.t1的表名为student mysql> alter table db2.t1 rename student;
二、MySQL键值 普通索引index 唯一索引unique 主键 primary key 外键foreign key 全文索引 fulltext 1.普通索引的使用 mysql> create table tea4( -> id char(6) not null, -> name varchar(4) not null, -> age int(3) not null, -> gender enum("boy","girl") default "boy", -> index(id),index(name)); ==>>建表时添加索引 mysql> desc tea4; +--------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------+------+-----+---------+-------+ | id | char(6) | NO | MUL | NULL | | | name | varchar(4) | NO | MUL | NULL | | | age | int(3) | NO | | NULL | | | gender | enum('boy','girl') | YES | | boy | | +--------+--------------------+------+-----+---------+-------+ key的MUL就是索引 mysql> show index from tea4 \G; ==>>查看索引，\G以列显示 mysql> drop index name on db2.tea4; ==>>已有表删除索引 mysql> create index name on db2.tea4(name); ==>>已有表创建索引 -------------------------------------------------------------------------------- drop index 索引名 on 表名; create index 索引名 on 表名(字段); -------------------------------------------------------------------------------- 2.主键的使用 使用规则： 字段值不允许重复，且不允许赋NULL值 一个表只能有一个主键 多个字段作为主键，叫做复合主键必须一起创建 主键字段的标志是RPI 主键通常与auto_increment连用 通常把表中唯一标识记录的字段设置为主键 主键 mysql> create table t8( name char(10) primary key, id int); ==>>创建表时添加主键 mysql> alter table db2.t8 drop primary key; ==>>清除主键 mysql> alter table db2.t8 add primary key(name); ==>>已有表添加主键 复合主键 mysql> create table t5( -> name char(10), -> class char(7), -> status enum("yes","no"), -> primary key(name,class) -> ); ==>>创建表时添加复合主键 mysql> alter table db2.t5 drop primary key; ==>>删除复合主键 mysql> alter table db2.t5 add primary key(name,class); ==>>已有表添加复合主键 mysql> alter table t5 add stu_num int after name; mysql> delete from t5; mysql> alter table db2.t5 add primary key(name,class,stu_num); mysql> desc t5; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | name | char(10) | NO | PRI | NULL | | | stu_num | int(11) | NO | PRI | NULL | | | class | char(7) | NO | PRI | NULL | | | status | enum('yes','no') | YES | | NULL | | +---------+------------------+------+-----+---------+-------+ 主键与auto_increment连用 mysql> create table t6( -> id int primary key auto_increment, -> name char(10), -> sex enum("men","women") -> ); mysql> desc t6; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(10) | YES | | NULL | | | sex | enum('men','women') | YES | | NULL | | +-------+---------------------+------+-----+---------+----------------+ mysql> insert into t6(name,sex) values("john","women"); mysql> select * from t6; +----+------+-------+ | id | name | sex | +----+------+-------+ | 1 | john | women | +----+------+-------+
3.外键的使用 外键功能：外键的值在另一字段内选择 使用规则： 表存储引擎必须是innodb 字段类型要一致 被参考字段必须是索引类型的一种（primary key） 创建外键： create table 表名( 字段名列表， foreign key（字段名） references 表名（字段名） ==>>指定外键 on update cascade ==>>同步更新 on delete cascade ==>>同步删除 ）engine=inndb； ==>>指定存储引擎 mysql> create table yg( -> yg_id int primary key auto_increment, -> name char(20) -> )engine=innodb; mysql> insert into yg(name) values("jerry"),("tom"); mysql> select * from yg; +-------+-------+ | yg_id | name | +-------+-------+ | 1 | jerry | | 2 | tom | +-------+-------+ mysql> create table gz( -> gz_id int, -> pay float(7,2), -> foreign key(gz_id) references yg(yg_id) -> on update cascade -> on delete cascade -> )engine=innodb; mysql> show create table gz \G; ==>>查看建表命令，可以看到外键，desc不显示外键信息 mysql> insert into gz values(1,22333); mysql> insert into gz values(2,2324); 同步更新与删除 mysql> select * from yg; +-------+-------+ | yg_id | name | +-------+-------+ | 1 | jerry | | 2 | tom | +-------+-------+ mysql> select * from gz; +-------+----------+ | gz_id | pay | +-------+----------+ | 1 | 22333.00 | | 2 | 2324.00 | +-------+----------+ mysql> update yg set yg_id=6 where yg_id=2; mysql> select * from yg; +-------+-------+ | yg_id | name | +-------+-------+ | 1 | jerry | | 6 | tom | +-------+-------+ mysql> select * from gz; +-------+----------+ | gz_id | pay | +-------+----------+ | 1 | 22333.00 | | 6 | 2324.00 | +-------+----------+ mysql> select * from yg; +-------+-------+ | yg_id | name | +-------+-------+ | 1 | jerry | +-------+-------+ mysql> select * from gz; +-------+----------+ | gz_id | pay | +-------+----------+ | 1 | 22333.00 | +-------+----------+ 让gz_id列不允许为空和重复 mysql> alter table gz add primary key(gz_id);
