数据库02

一、表结构

　　1.1约束条件：作用：限制如何给字段赋值

字段名 | 类型| 空 | 键值 | 默认值 |额外设置

#####################################################

#约束条件： #

#　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;