数据库02
一、表结构
1.1约束条件：作用：限制如何给字段赋值
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
字段名 | 类型| 空 | 键值 | 默认值 |额外设置
#####################################################
#约束条件： #
# null 允许为空 ，默认下允许 #
# not null 不允许为空 #
# key 键值类型 #
# default 设置默认值，缺省为null，设置的值要与字段类型 #
匹配 #
####################################################
1.2修改表结构
命令：alter table 库名.表名 执行动作；####################################################################################################
执行动作：
1.添加新字段 add（约束条件可以不指定，由系统自动赋予）
add 字段名 类型 约束条件；（不指定添加位置，默认在末尾）
add 字段名 类型 约束条件 after 字段名；（指定添加在哪一个字段的末尾，指定位置）
add 字段名 类型 约束条件 first；（把字段放在开头）
mysql> desc T3; +-------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------------------+------+-----+---------+-------+ | class | char(9) | YES | | NULL | | | name | char(10) | NO | | NULL | | | age | tinyint(4) | YES | | 19 | | | likes | set('music','game','sport','eat') | YES | | music | | +-------+-----------------------------------+------+-----+---------+-------+
现在增加一个分数字段：mysql> alter table T3 add grade int not null default 60;
mysql> desc T3; +-------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------------------+------+-----+---------+-------+ | class | char(9) | YES | | NULL | | | name | char(10) | NO | | NULL | | | age | tinyint(4) | YES | | 19 | | | likes | set('music','game','sport','eat') | YES | | music | | | grade | int(11) | NO | | 60 | | +-------+-----------------------------------+------+-----+---------+-------+
2.修改字段类型 modify（修改时若表里已经有数据了，那么新的类型与约束值不能与已有数据产生冲突）
modify 字段名 新的类型宽度，约束条件；（也可以使用after 字段 | first 来改变位置）
如：将class和name字段改变位置：alter table T3 modify name char(10) not null first;
mysql> desc T3; +-------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------------------+------+-----+---------+-------+ | name | char(10) | NO | | NULL | | | class | char(9) | YES | | NULL | | | age | tinyint(4) | YES | | 19 | | | likes | set('music','game','sport','eat') | YES | | music | | | grade | int(11) | NO | | 60 | | +-------+-----------------------------------+------+-----+---------+-------+
3.修改字段名：change
change 原字段名 新的字段名 类型（宽度） 约束条件；（若类型约束条件与原来不同了，也就改变了，也就是说change 既可以改名
也可以改字段类型）
如：alter table T3 change grade grades int not null ;不仅改变了字段名，也改变了约束条件
mysql> desc T3; +--------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------------------+------+-----+---------+-------+ | name | char(10) | NO | | NULL | | | class | char(9) | YES | | NULL | | | age | tinyint(4) | YES | | 19 | | | likes | set('music','game','sport','eat') | YES | | music | | | grades | int(11) | NO | | NULL | | +--------+-----------------------------------+------+-----+---------+-------+
4.删除字段：
alter table 表名 drop 字段名；
5.修改表名：
alter table 表名 rename 新表名；
###########################################################################################################################
二、MySQL的键值
键值的介绍：键值相当于书的目录，对表中的字段值进行排序
索引类型包括btree b+tree hash
优点：通过创建索引，可以保证数据库表中每一个行数据的唯一性，可以加快数据的查询速度
缺点：索引占用物理内存
当对表中数据进行增删改查时，索引也要跟着动态调整，降低了维护的速度
1.index 普通索引：
—在已有的表中添加index字段
—建表时添加index字段
—查看表索引
—删除表索引
1）在已有的表下建立索引:
mysql> create index xingming on T3(name); #建立一个index普通索引，索引名叫xingming ，位于在T3表的name字段
查看时在key 行有MUL字样
mysql> desc T3; +--------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------------------+------+-----+---------+-------+ | name | char(10) | NO | MUL | NULL | | | class | char(9) | YES | | NULL | | | age | tinyint(4) | YES | | 19 | | | likes | set('music','game','sport','eat') | YES | | music | | | grades | int(11) | NO | | NULL | | +--------+-----------------------------------+------+-----+---------+-------+
2）查看索引：show index from 表名\G;
ysql> show index from T3\G; *************************** 1. row *************************** Table: T3 Non_unique: 1 Key_name: xingming Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:
3）删除索引：drop index 建立的索引名 on 所在表名
mysql> drop index name on T3;
mysql> desc T3; +--------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------------------+------+-----+---------+-------+ | name | char(10) | NO | | NULL | | | class | char(9) | YES | | NULL | | | age | tinyint(4) | YES | | 19 | | | likes | set('music','game','sport','eat') | YES | | music | | | grades | int(11) | NO | | NULL | | +--------+-----------------------------------+------+-----+---------+-------+
4）新建表的时候添加索引
在建表的最后添加： index（字段名）
> create table T4(
-> name char(10),
-> age int,
-> index(name));
2.primary key 主键
—字段值不允许重复，且不允许赋为null值
—一个表中只能有一个primary key 字段
—多个字段都作为主键，称为复合主键，这些字段必须一起创建主键
—主键字段标识 PRI
—主键经常与auto_increment连用
—通常把表中唯一标识的字段设为主键
#############################################################
具体要求如下：
- 建表时，创建主键
- 在已有表里添加主键
- 建表时创建复合主键
- 删除主键
- 设置字段值自增长
1）建表时创建主键：（复合主键：primary key(class,name,pay) ）
>create table T1(
>name char(10) primary key,
>id int);
2)在已有表中添加主键
alter table 表名 add primary key(字段名);
3）移除主键：(移除主键前一定要确保无自增属性)
alter table 表名 drop primary key ;
在建表的时候，如果主键字段为int类型，还可以为其设置AUTO_INCREMENT自增属性，这样当添加新的表记录时，此字段的值会自动从1开始逐个增加，无需手动指定。比如，新建一个表，将id列作为自增的主键字段：
mysql> create table T1(
-> name char(10),
-> id int auto_increment);
插入表数据（主键不允许为空，但是与auto连用后，可根据数据的插入自增）
mysql> insert into T1(name) values("bob");
Query OK, 1 row affected (0.04 sec)
mysql> insert into T1(name) values("lucy");
Query OK, 1 row affected (0.03 sec)
查看表值：
mysql> select *from T1; +------+----+ | name | id | +------+----+ | bob | 1 | | lucy | 2 | +------+----+
如果要删除id的主键信息，首先是要用alter table来改变字段的auto_increment类型的，否则无法删去，因为auto_increment 必须是主键时适用。
3.foreign key 外键
插入记录时，字段值在另一个表字段值的范围内进行选择
使用规则：—表存储引擎必须时innodb
—字段类型要一致
—被参照字必须是索引类型的primary key
创建一个员工表，把yg_id设为主键并自增
mysql> create table yg( -> yg_id int primary key auto_increment, -> name char(16) -> )engine=innodb;
创建一个工资表，工资表中的gz_id设为员工表中yg_id的外键
mysql> create table gz( -> gz_id int, -> name char(16), -> gz float(7,2), -> foreign key(gz_id) references yg(yg_id) -> on update cascade on delete cascade ###设置删除与更新同步 -> )engine=innodb;###存储引擎
向其中插入如下的数据：
ysql> select *from yg; +-------+-------+ | yg_id | name | +-------+-------+ | 1 | jerry | | 2 | tom | +-------+-------+ 2 rows in set (0.00 sec) mysql> select *from gz; +-------+-------+----------+ | gz_id | name | gz | +-------+-------+----------+ | 1 | jerry | 12000.00 | | 2 | tom | 8000.00 | +-------+-------+----------+ 2 rows in set (0.05 sec)
验证同步更新：
update yg set yg_id=123 where name="jerry";
这里只更新了yg表，但是gz表也一起更新了
mysql> select *from yg; +-------+-------+ | yg_id | name | +-------+-------+ | 2 | tom | | 123 | jerry | +-------+-------+ 2 rows in set (0.00 sec) mysql> select *from gz; +-------+-------+----------+ | gz_id | name | gz | +-------+-------+----------+ | 123 | jerry | 12000.00 | | 2 | tom | 8000.00 | +-------+-------+----------+
删除指定表的外键约束
先通过SHOW指令获取表格的外键约束名称：
mysql> show create table gz\G;
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`name` char(16) DEFAULT NULL,
`gz` float(7,2) DEFAULT NULL,
KEY `gz_id` (`gz_id`),
CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
发现外键名称为：gz_ibfk_1
接下来删除这个外键
alter table gz drop foreign key gz_ibfk_1;
