創(chuàng)建數(shù)據(jù)庫(kù)
創(chuàng)新互聯(lián)公司是一家網(wǎng)站建設(shè)、成都做網(wǎng)站，提供網(wǎng)頁(yè)設(shè)計(jì)，網(wǎng)站設(shè)計(jì)，網(wǎng)站制作，建網(wǎng)站，按需開發(fā)，網(wǎng)站開發(fā)公司，于2013年成立是互聯(lián)行業(yè)建設(shè)者，服務(wù)者。以提升客戶品牌價(jià)值為核心業(yè)務(wù)，全程參與項(xiàng)目的網(wǎng)站策劃設(shè)計(jì)制作，前端開發(fā)，后臺(tái)程序制作以及后期項(xiàng)目運(yùn)營(yíng)并提出專業(yè)建議和思路。
create database|schema [if not exists] db_name [character set= ] [collate= ]
注：schema可以理解為方案或者數(shù)據(jù)庫(kù)，與database一個(gè)意義
例創(chuàng)建一個(gè)students數(shù)據(jù)庫(kù)，且默認(rèn)字符集為‘gbk’、默認(rèn)排序?yàn)?gbk_chinese_ci';
create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci';
刪除數(shù)據(jù)庫(kù)
drop {database|schema} [if exists] db_name;
創(chuàng)建表的方式
直接創(chuàng)建一個(gè)空表
從其他表中查詢出數(shù)據(jù)，并以此創(chuàng)建一個(gè)有數(shù)據(jù)的表
以其他表為模板創(chuàng)建一個(gè)空表
create table [if not exists] tb.name (字段名稱1 字段定義 約束 索引，字段名稱2 字段定義 約束 索引）
例：
MySQL> create table tb1 (id int unsigned not null auto_increment primary key,name char(20) not null,age tinyint not null) engine='engine_name';
主鍵還可以如下單獨(dú)定義
mysql> create table tb1 (id int unsigned not null auto_increment ,name char(20) not null,age tinyint not null，primary key（id）)；
也可以將多個(gè)字段一起作為主鍵
mysql> create table tb1 (id int unsigned not null auto_increment ,name char(20) not null,age tinyint not null，primary key（id，name）)；
注：任何一個(gè)auto_increment字段都必須定義為主鍵
將id定義為主鍵，name定義為唯一鍵，age定義為索引
mysql> create table tb1 (id int unsigned not null auto_increment ,name char(20) not null,age tinyint not null，primary key（id）,unique key (name) index(age))；
mysql> show tables ;
+--------------------+
| Tables_in_students |
+--------------------+
| tb1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select id,name,age from tb1;
鍵是一種特殊的索引，其數(shù)值不能相同，而索引允許出現(xiàn)相同值，稱鍵為約束，屬于B+樹索引結(jié)構(gòu)
索引類型有兩種
BTREE索引
HASH索引
每個(gè)字段都支持B樹索引，但不一定支持HASH索引
mysql> create table corses (cid tinyint unsigned not null auto_increment ,course varchar(50) not null,primary key(cid));
Query OK, 0 rows affected (0.15 sec)
mysql> show table status like 'corses'\G
*************************** 1. row ***************************
Name: corses
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2017-02-12 10:45:11
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
注：創(chuàng)建表時(shí)如果未指定字符集，排序規(guī)則等，則從數(shù)據(jù)庫(kù)繼承；而數(shù)據(jù)庫(kù)創(chuàng)建時(shí)指定或者采取默認(rèn)
向表中插入及查看數(shù)據(jù) insert into 和select
例：
mysql> insert into corses (course) values ('kuihuabaodian'),('jiuyingzhenjing'),('rulaishezhang'); ##向corses表的courses字段插入數(shù)據(jù)
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from corses; #查看
+-----+-----------------+
| cid | course |
+-----+-----------------+
| 1 | kuihuabaodian |
| 2 | jiuyingzhenjing |
| 3 | rulaishezhang |
+-----+-----------------+
3 rows in set (0.00 sec)
show index from tb_name； 顯示表的索引
例：
mysql> show index from corses\G
*************************** 1. row ***************************
Table: corses 表名
Non_unique: 0 是否為唯一主鍵；0表示是唯一鍵，1表示不是唯一鍵
Key_name: PRIMARY 鍵名
Seq_in_index: 1 這個(gè)表的第1個(gè)索引，一個(gè)表中可以第一多個(gè)索引
Column_name: cid 索引在哪個(gè)字段上（cid）
Collation: A 排序規(guī)則
Cardinality: 3
Sub_part: NULL 索引長(zhǎng)度
Packed: NULL
Null:
Index_type: BTREE 索引類型
Comment:
Index_comment:
1 row in set (0.00 sec)
從以張表中查找出數(shù)據(jù)并建立一個(gè)新表
mysql> create table testcourses select * from corses where cid<2;
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from testcourses;
+-----+---------------+
| cid | course |
+-----+---------------+
| 1 | kuihuabaodian |
+-----+---------------+
1 row in set (0.00 sec)
mysql> create table testcourses select * from corses where cid<3;
ERROR 1050 (42S01): Table 'testcourses' already exists
mysql> desc courses;
ERROR 1146 (42S02): Table 'students.courses' doesn't exist
mysql> desc testcourses;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| cid | tinyint(3) unsigned | NO | | 0 |
| course | varchar(50) | NO | | NULL |
+--------+---------------------+------+-----+---------+-------+
2 rows in set (0.05 sec)
mysql> desc corses;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
+--------+---------------------+------+-----+---------+----------------+
| cid | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| course | varchar(50) | NO | | NULL |
+--------+---------------------+------+-----+---------+----------------
用dessc查看兩張表的結(jié)構(gòu)，可以看出不一樣
當(dāng)從一張表中復(fù)制數(shù)據(jù)時(shí)某些字段會(huì)消失
以一個(gè)表為模板創(chuàng)建一個(gè)結(jié)構(gòu)一樣的空表
mysql> create table test like corses;
Query OK, 0 rows affected (0.16 sec)
mysql> desc test;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| cid | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| course | varchar(50) | NO | | NULL |
+--------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc corses;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| cid | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| course | varchar(50) | NO | | NULL |
+--------+---------------------+------+-----+---------+----------------+
desc顯示兩個(gè)表的格式相同
注：
所以要想復(fù)制一張表中的數(shù)據(jù)，最好先依據(jù)原表創(chuàng)建一個(gè)完全相同的空表，在用insert命令導(dǎo)入原表的數(shù)據(jù)
修改表
alter table tb_name（添加，刪除，修改字段，修改索引，改表名，修改表屬性）
例
mysql> alter table test add unique key(course);
為test表添加一個(gè)唯一鍵（course）
mysql> alter table test change course Course varchar(50) not null;
將course字段（屬性）改為Course 且為varchar(50) 不能為空
新增一個(gè)日期字段
例：
mysql> alter table test add starttime date default '2017-2-12';
mysql> desc test;
+-----------+---------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+------------+----------------+
| cid | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| Course | varchar(50) | NO | UNI | NULL | |
| starttime | date | YES | | 2017-02-12 | |
+-----------+---------------------+------+-----+------------+----------------
修改表名test為mytest
mysql> alter table test rename mytest;
刪除表
mysql> create table hehe (sid int unsigned not null auto_increment primary key,name varchar(30),cid int not null);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into hehe (name,cid) values ('jiamian',2),('zxl',1);
mysql> select * from hehe;
+-----+---------+-----+
| sid | name | cid |
+-----+---------+-----+
| 1 | jiamian | 2 |
| 2 | zxl | 1 |
+-----+---------+-----+
2 rows in set (0.00 sec)
mysql> select * from Courses;
ERROR 1146 (42S02): Table 'students.Courses' doesn't exist
mysql> select * from corses;
+-----+-----------------+
| cid | course |
+-----+-----------------+
| 1 | kuihuabaodian |
| 2 | jiuyingzhenjing |
| 3 | rulaishezhang |
+-----+-----------------+
3 rows in set (0.00 sec)
做兩表的條件顯示
mysql> select name,course from hehe,corses where hehe.cid=corses.cid;
+---------+-----------------+
| name | course |
+---------+-----------------+
| zxl | kuihuabaodian |
| jiamian | jiuyingzhenjing |
+---------+-----------------+
2 rows in set (0.01 sec)
添加外鍵約束
外鍵約束只能添加在支持事物的存儲(chǔ)引擎上，且存儲(chǔ)引擎要一樣
外鍵約束的關(guān)聯(lián)的兩個(gè)字段類型要一樣
mysql> alter table corses engine=innodb; 修改引擎
mysql> alter table hehe modify cid tinyint unsigned not null; 修改字段類型一樣
mysql>alter table hehe add foreign key foreign_cid (cid) references corses (cid);
將hehe表的cid字段與corses表的cid字段關(guān)聯(lián)一起建立一個(gè)外鍵約束，外鍵名稱為foreign_cid
mysql> create table test1 (cid int unsigned not null auto_increment primary key,name varchar(50) not null,sid char not null);
mysql> insert into test1 (cid,name,sid) values (1,'zxl','A'),(2,'jiamian','B'),(3,'fade','C');
mysql> create table test2 (cid int unsigned not null auto_increment primary key,name varchar(50));
mysql> insert into test2 (cid,name) values (1,'hehe'),(2,'haha');
mysql> alter table test1 add foreign key foreign_cid (cid) references courses(cid);
索引：可以創(chuàng)建，查看，刪除，不可以修改
create index index_name on tb_name(字段）using BTREE/HASH;
且可以 （字段（length）desc|asc ）
length表示索引長(zhǎng)度、占的字符數(shù)；
asc表示創(chuàng)建索引后按照升序排，desc表示按照降序排
mysql> create index name_on_student on test1(name) using BTREE;
在test1表的name字段創(chuàng)建索引，并且為BTREE索引
mysql> show index from test1\G
*************************** 1. row ***************************
Table: test1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: cid
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: test1
Non_uniqu
Key_name: name_on_student
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment
mysql> drop index name_on_student ontest1;
在test1表的name字段創(chuàng)建一個(gè)長(zhǎng)度為5,且降序排列的BTREE索引
mysql> create index name_on_student on test1(name(5) desc) using BTREE;
mysql> show index from test1\G
*************************** 1. row ***************************
Table: test1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: cid
Collation: A
Cardinality:
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: test1
Non_unique: 1
Key_name: name_on_student 索引名稱
Seq_in_index: 1 這個(gè)表的第一個(gè)索引
Column_name: name 索引所在的字段（name）
Collation: A
Cardinality: 3
Sub_part: 5 索引長(zhǎng)度為5
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
重慶分公司
重慶分公司
