天天看點
傳回

mysql資料庫基礎(2）索引、主鍵、複合主鍵、外鍵

增、删、改、查

加“”表示為普通字元

mysql> insert into  t9 values("NULL",21,"game,flim");

+++++++++++++++++++++++++++++++

添加新字段

mysql> alter table  t4 add age int(2) default 19;        //預設在最後面

mysql> alter table  t4 add mail char(50) after name;   //加在name後面

mysql> alter table  t4 add stu_num char(10) first;      //加在最前面

修改字段類型 ，類型要與字段存儲的資料比對,不能有沖突.

mysql> alter table  t4 modify name varchar(3);

ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> alter table  t4 modify name varchar(15);

Query OK, 1 row affected (0.44 sec)

修改字段位置

mysql> alter table  t4  modify age int(2) default  19

after name;

删除字段

mysql> alter table  t5 drop start;

mysql> select  * from t5;

mysql> desc  t5;

修改字段名

alter table  t4 change stu_num stu_id char(10);

修改表名

alter table   t9 rename stuinfo;

應用

mysql> create table t7(meeting datetime,party timestamp);

                                             不自動指派      會自動指派目前時間

mysql> insert into t7 values(now(),now());

Query OK, 1 row affected (0.07 sec)

mysql> select * from t7;

+---------------------+---------------------+

| meeting             | party               |

+---------------------+---------------------+

| 2018-10-16 09:31:50 | 2018-10-16 09:31:50 |

+---------------------+---------------------+

mysql> insert into t7(meeting) values(now());

mysql> select * from t7;

+---------------------+---------------------+

| meeting             | party               |

+---------------------+---------------------+

| 2018-10-16 09:31:50 | 2018-10-16 09:31:50 |

| 2018-10-16 09:33:12 | 2018-10-16 09:33:12 |

+---------------------+---------------------+

mysql> select * from t7;

+---------------------+---------------------+

| meeting             | party               |

+---------------------+---------------------+

| 2018-10-16 09:31:50 | 2018-10-16 09:31:50 |

| 2018-10-16 09:33:12 | 2018-10-16 09:33:12 |

| 2018-10-17 08:25:00 | 2018-10-16 09:34:01 |

| NULL                | 2018-10-17 08:25:00 |

+---------------------+---------------------+

mysql> desc teadb.t5;

+----------+----------+------+-----+---------+-------+

| Field    | Type     | Null | Key | Default | Extra |

+----------+----------+------+-----+---------+-------+

| name     | char(10) | YES  |     | NULL    |       |

| birthday | date     | YES  |     | NULL    |       |

| start    | year(4)  | YES  |     | NULL    |       |

| uptime   | time     | YES  |     | NULL    |       |

| party    | datetime | YES  |     | NULL    |       |

+----------+----------+------+-----+---------+-------+

mysql> insert into t5(name,start) values("tom",50);

mysql> select * from t5;

+------+------------+-------+----------+---------------------+

| name | birthday   | start | uptime   | party               |

+------+------------+-------+----------+---------------------+

| bob  | 1993-07-02 |  1993 | 08:30:00 | 2018-10-15 20:30:00 |

| tom  | NULL       |  2050 | NULL     | NULL                |

+------+------------+-------+----------+---------------------+

//year預設為4位數，1-69--->20   70-99--->19

mysql> create table t88(name char(3),level int(3) zerofill);    //顯示寬度，0補足位

Query OK, 0 rows affected (0.25 sec)

mysql> insert into t88 values("tom",1131);

mysql> insert into t88 values("tom",113);

mysql> insert into t88 values("tom",11);

mysql> insert into t88 values("tom",3);

mysql> select * from t88;

+------+-------+

| name | level |

+------+-------+

| tom  |  1131 |

| tom  |   113 |

| tom  |   011 |

| tom  |   003 |

+------+-------+

mysql> create table t9(name char(10) not null,age tinyint(2) default 19,likes set("eat","sleep","film","game") not null default "game,eat");

//no null:不能為空值 ,預設可以為空   //detault:預設值

mysql> insert into t9(name) values("bob");

Query OK, 1 row affected (0.02 sec)

mysql> select * from t9;

+---------+---------+----------------+

| name | age  |    likes   |

+---------+---------+----------------+

| bob  |   19 | game,eat  |

+---------+---------+----------------+

mysql> desc t9;

+-------+-------------------------------------------------------+-------+-----+------------+--------+

| Field | Type                             | Null | Key | Default | Extra |

+-------+-------------------------------------------------------+------+------+------------+-------+

| name | char(10)                          | NO |   | NULL   |     |

| age  | tinyint(2)                         | YES |   | 19     |     |

| likes  | set('eat','sleep','film','game')         | NO |   | game  |     |

+-------+-------------------------------------------------------+------+-----+-----------+---------+

//不輸入會預設補内容

添加字段 預設在最後

mysql> alter table t4 add nanjing int(2) default 22;

mysql> select * from t4;

+--------+------+------+------+---------+---------+

| stu_id | name | age  | mail | homedir | nanjing |

+--------+------+------+------+---------+---------+

| NULL   | jack |   19 | NULL | USA     |      22 |

| NULL   | lll  |   19 | NULL | han     |      22 |

+--------+------+------+------+---------+---------+

索引

索引是指給字段排隊，類似書的目錄

優點：可以加快查詢資料速度

缺點：減慢寫入的速度，增加減少都需要重新排列。排隊資訊需要檔案來存儲，占記憶體

平時我們看不到排序，查詢時直接會提示資料在幾行。

建立表的時候添加索引：  //可同時添加多個索引

mysql> create table t12( name char(10),class char(7),age int(2) , index(name),index(class));

mysql> desc t12;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| name  | char(10) | YES  | MUL | NULL    |       |

| class | char(7)  | YES  | MUL | NULL    |       |

| age   | int(2)   | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

mysql> show index from t12\G;       //檢視索引

mysql> drop index name on t12;         //删除索引

mysql> desc t12;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| name  | char(10) | YES  |     | NULL    |       |

| class | char(7)  | YES  | MUL | NULL    |       |

| age   | int(2)   | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

mysql> create index name on t12(name);    //在已有的表裡添加索引，索引名與表名一緻

mysql> desc t12；

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| name  | char(10) | YES  | MUL | NULL    |       |

| class | char(7)  | YES  | MUL | NULL    |       |

| age   | int(2)   | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

建立主鍵的格式1

mysql> create table t22(name char(10),stu_num char(9),primary key(stu_num));

建立主鍵的格式2（同上）

mysql> create table t23(name char(10),stu_num char(9)primary key);

+---------+----------+------+-----+---------+-------+

| Field   | Type     | Null | Key | Default | Extra |

+---------+----------+------+-----+---------+-------+

| name    | char(10) | YES  |     | NULL    |       |

| stu_num | char(9)  | NO   | PRI | NULL    |       |

+---------+----------+------+-----+---------+-------+

mysql> select * from t22;

+------+-----------+

| name | stu_num   |

+------+-----------+

| tom  | nsd180705 |           //設值主鍵字段（stu_num）值不于允許重複，不能為空

+------+-----------+

複合主鍵（表中多個字段一起做主鍵，表中多行設符合主鍵不能重複不能為空）

mysql> create table t24(cip char(15),sport smallint(2),status enum("deny","all"),primary key(cip,sport));

mysql> select * from t24;

+-------------+-------+--------+

| cip         | sport | status |

+-------------+-------+--------+

| 192.168.4.1 |    21 | deny   |

| 192.168.4.1 |    22 | deny   |

| 192.168.4.2 |    22 | deny   |

| 192.168.4.3 |    22 | all    |

+-------------+-------+--------+

删除主鍵，不删除内容，還是不能為空，表将不受限制

mysql> alter table t24 drop primary key;               //删除

在已有表裡建立複合主建（如果表中有重複的資料不能建立，需要先改表）

mysql> alter table t24 add primary key(cip,sport);      //建立

主鍵+自增長（自己存亦遵循限制規則//空值，重複）//自增長從最大的開始

//删除也從曆史最大值開始記錄

mysql> create table t25(stu_id int primary key auto_increment,name char(5),age tinyint(2) unsigned);                                   自加1

mysql> desc t25;

+--------+---------------------+------+-----+---------+----------------+

| Field  | Type                | Null | Key | Default | Extra          |

+--------+---------------------+------+-----+---------+----------------+

| stu_id | int(11)             | NO   | PRI | NULL    | auto_increment |

| name   | char(5)             | YES  |     | NULL    |                |

| age    | tinyint(2) unsigned | YES  |     | NULL    |                |

+--------+---------------------+------+-----+---------+----------------+

mysql> insert into t25(name,age)values("bob",19);

mysql> insert into t25(name,age)values("tom",18);

mysql> select * from t25;

+--------+------+------+

| stu_id | name | age  |

+--------+------+------+

|      1 | bob  |   19 |

|      2 | tom  |   18 |                   //自加一

+--------+------+------+

mysql> alter table t88 add id int(2) zerofill primary key auto_increment first;

mysql> select * from t88;

+----+------+-------+

| id | name | level |

+----+------+-------+

| 01 | tom  |  1131 |

| 02 | tom  |   113 |

| 03 | tom  |   011 |

| 04 | tom  |   003 |

+----+------+-------+

先珊自增字段才能珊主鍵

mysql> alter table t25 modify stu_id int(2) not null;         //先删除自增長段

mysql> desc t25;

+--------+---------------------+------+-----+---------+-------+

| Field  | Type                | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+-------+

| stu_id | int(2)              | NO   | PRI | NULL    |       |  //原來有auto_increment

| name   | char(5)             | YES  |     | NULL    |       |

| age    | tinyint(2) unsigned | YES  |     | NULL    |       |

+--------+---------------------+------+-----+---------+-------+

mysql> alter table t25 drop primary key;              //珊主鍵

mysql> desc t25;

+--------+---------------------+------+-----+---------+-------+

| Field  | Type                | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+-------+

| stu_id | int(2)              | NO   |     | NULL    |       |

| name   | char(5)             | YES  |     | NULL    |       |

| age    | tinyint(2) unsigned | YES  |     | NULL    |       |

+--------+---------------------+------+-----+---------+-------+

外鍵

定義：讓目前表字段的值在另一個表中字段值的範圍内選擇，保證資料的一緻性。

使用外鍵的條件:

表的存儲引擎必須是innodb

字段類型要一緻

被參照字段必須要是索引類型的一種（primary key）

mysql> create table ygtab(yg_id int(2)primary key auto_increment,naem char(10)) engine=innodb;

mysql> insert into ygtab(naem)values("bob");

mysql> insert into ygtab(naem)values("tom");

mysql> create table gztab(gz_id int(2),pay float(7,2),foreign key(gz_id) references ygtab(yg_id)on update cascade on delete cascade)engine=innodb;

//regerences:引用  foreign key:外鍵

mysql> desc gztab;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| gz_id | int(2)     | YES  | MUL | NULL    |       |

| pay   | float(7,2) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

準備案例：

mysql> insert into gztab values(1,28000);

mysql> insert into gztab values(2,38000);

mysql> select * from ygtab;

+-------+------+

| yg_id | naem |

+-------+------+

|     1 | bob  |

|     2 | tom  |

+-------+------+

mysql> select * from gztab;

+-------+----------+

| gz_id | pay      |

+-------+----------+

|     1 | 28000.00 |

|     2 | 38000.00 |

+-------+----------+

//改參考的員工表字段，自己表應該也同部更新  //單向同步

mysql> update ygtab set yg_id=8 where yg_id=2;

mysql> select * from gztab;

+-------+----------+

| gz_id | pay      |

+-------+----------+

|     1 | 28000.00 |

|     8 | 38000.00 |

+-------+----------+

2 rows in set (0.00 sec)

mysql> select * from ygtab;

+-------+------+

| yg_id | naem |

+-------+------+

|     1 | bob  |

|     8 | tom  |

+-------+------+

mysql> delete from ygtab where yg_id=8;          //删除員工表資訊

mysql> select * from gztab;                     //工資表資訊自動删除

+-------+----------+

| gz_id | pay      |

+-------+----------+

|     1 | 28000.00 |

+-------+----------+

1 row in set (0.00 sec)

//有利有弊

//有bug，gz_id 需要做主鍵不然可以連發工資，因為參考表有記錄,被參考的表無法删除，需要先删除外鍵，再删除表（主鍵還在）

轉載于:https://www.cnblogs.com/hanlongyu/p/9807423.html

資料庫
上一篇: 資料庫--主鍵與外鍵
下一篇: mysql資料庫表聯合主鍵 索引_mysql資料庫基礎(2）索引、主鍵、複合主鍵、外鍵...

繼續閱讀