數據庫基本知識

一、創建和查看數據庫

1.創建數據庫

CREATE DATABASE [IF NOT EXISTS]#可選內容 db_name [create_specification]#如下定義：
[DEFAULT] CHARACTER SET charset_name#字符集 | [DEFAULT] COLLATE collation_name#校對規則

（1）創建一個名稱為mydb1的數據庫

create database mydb1;

(2) 創建一個使用gbk字符集的mydb3的數據庫

create database mydb2 character set gbk;

(3)創建一個使用utf8字符集，并帶校對規則的mydb3數據庫

create database mydb3 character set utf8 collate utf8_bin;

2.查看數據庫

（1）顯示數據庫 語句：

show databases;

(2) 顯示數據庫創建語句：

show create database mydb3;

3.修改數據庫

ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [, alter_specification [,........#規格如下
[DEFAULT] CHARECTER SET charset_name [DEFAULT] COLLATE collation_name

(1) 查看服務器中的數據庫，并把其中某一個庫的字符集修改為utf8

alter database mydb2 character set utf8;

4.修改數據庫

DROP DATABASE [IF EXISTS] db_name

(1)刪除數據庫mydb3

drop database mydb3;

5.選擇數據庫

use 數據庫;
select database();#查看數據庫

二、數據庫中表的操作
1.學習數據庫的數據類型
2.增加表

CREATE TABLE table_name(
field1 datatype
field2 datatype
field3 datatype
field4 datatype

) character set 字符集 collate 校對規則 #建議默認
field:指定列名 datatype:指定列類型

（1）創建一個員工表employee

create table employee (
id int,
name varchar(20),
gender char(1),
birthday date,
entry_data date,
job varchar(50),
salay double,
resume text
);

3.查看表
查看表結構：desc tab_name;
查詢當前數據庫中的所有表：show tables;
查看表的建表語句：show create table;

4.修改表

ALTER TABLE table_name ADD (column datatype [DEFAUL expr] [,column datatype]......);

ALTER TABLE table_name MODIFY (column datatype [DEFAUL expr] [,column datatype]......);

ALTER TABLE table_name DROP (column );

修改列的名稱：ALTER TABLE table_name change [column]
old_col_name column_definition;

修改表的名稱：rename table 表名 to 新表名；

修改表的字符集編碼：alter table tab_name character set utf8;

(1)在上面員工表基本上增加一個image列

alter table employee add image blob;

(2)修改job列，使其長度為60

alter table employee modify job varchar(60);

(3)刪除gender列

alter table employee drop gender;

(4)表名改為user

alter table employee to user;

(5)修改表的字符集為utf8

alter table user character set utf8;

(6)列名name修改為username

alter table user change name username varchar(20)

5.刪除表

drop table table_name;

三、表的約束

1.PRIMARY KEY 主鍵約束：非空&唯一

create table employee (
id int primary key,
name varchar(20),
gender char(1),
birthday date,
entry_data date,
job varchar(50),
salay double,
resume text
);

2.NOT NULL非空約束：不能為空

create table employee (
id int primary key,
name varchar(20),
gender char(1) not null,
birthday date,
entry_data date,
job varchar(50),
salay double,
resume text
);

3.UNIQUE唯一約束：唯一

create table employee (
id int primary key,
name varchar(20) unique,
gender char(1) not null,
birthday date,
entry_data date,
job varchar(50),
salay double,
resume text
);

4.DEFAULT默認約束:

create table employee (
id int primary key,
name varchar(20) unique,
gender char(1) not null,
birthday date,
entry_data date,
job varchar(50) DEFAULT 'sxs',
salay double,
resume text
);

5.AUTO_INCREMENT設置表 的字段自動增長

create table employee (
id int primary key auto_increment,
name varchar(20) unique,
gender char(1) not null,
birthday date,
entry_data date,
job varchar(50) DEFAULT '實習生',
salay double,
resume text
);

四、索引

1.普通索引

可以創建在任何數據類型中，其值是否唯一和非空沒有固定的要求

2.唯一性索引

由UNIQUE定義的索引

3.全文索引
是由FULLTEXT 定義的索引，它只能創建在CHAR,VARCHAR或TEXT類型的字段上，而且，現在只有MyISAM存儲引擎支持全文索引

4.單列索引

表中單個字段的索引

5.多列索引

表中多個字段上創建的索引，只有在查詢條件中使用了這些字段中的第一個字段時，該索引才會被使用

6.空間索引

是由SPATIAL定義的索引，只能創建在空間數據類型的字段上（GEOMETRY,POINT,LINESTRING和POLYGON）

7.創建索引

（1）創建表的時候創建索引
CREATE TABLE 表名 （字段名 數據類型[完整約束條件]，字段名 數據類型[完整約束條件]
...............
字段名 數據類型
[UNIQUE #唯一索引 | FULLTEXT#全文索引 | SPATIAL#空間索引] INDEX | KEY#字段索引 [別名]#索引名稱 (字段名1#表的字段 [(長度)] #索引長度)[ASC#升序 | DESC#降序 ]）
);

創建普通索引

create table t1(
id INT,
name VARCHAR(20),
score FLOAT,
INDEX (id)
);

查找

explain select * from t1 where id = 1;

創建唯一性索引

create table t2(
id INT,
name VARCHAR(20),
score FLOAT,
UNIQUE INDEX unique_id (id ASC)
);

查看：show create table t2

創建全文索引

create table t3(
id INT,
name VARCHAR(20),
score FLOAT,
FULLTEXT INDEX fulltext_name (name)
)ENGINE=MyISAM;

創建單列索引

create table t4(
id INT,
name VARCHAR(20),
score FLOAT,
INDEX single_name (name)
);

創建多列索引

create table t5(
id INT,
name VARCHAR(20),
score FLOAT,
INDEX mutil_name (id,name(20))
);

創建空間索引

create table t6(
id INT,
space GEOMETRY NOT NULL,
SPATIAL INDEX space_name (space)
)ENGINE=MyISAM;

8.使用CREATE INDEX 語句在已創建的表上創建索引

CREATE [UNIQUE | FULLTEXT |SPATIAL] INDEX 索引名 ON 表名 （字段名 [(長度)] [ASC | DESC] ）;

create table book(
bookid INT NOT NULL,
bookname VARCHAR(225),
authors VARCHAR(225),
info VARCHAR(225),
comment VARCHAR(225),
publicyear YEAR NOT NULL
);
(1)創建普通索引在bookid這一列上

CREATE INDEX index_id on book(bookid);

(2)創建唯一索引在bookid這一列上

CREATE UNIQUE INDEX uniquex on book(bookid);

(3)創建多列索引

CREATE INDEX mutilx on book(authors,info);

(4)創建全文索引

drop table book;

create table book(
bookid INT NOT NULL,
bookname VARCHAR(225),
authors VARCHAR(225),
info VARCHAR(225),
comment VARCHAR(225),
publicyear YEAR NOT NULL
)ENGINE=MyISAM;

create FULLTEXT INDEX fulltextdx on book(bookname);

9.使用ALTER TABLE語句在已經存在的表上創建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 （字段名 [(長度)] [ASE | DESC]）

ALTER TABLE book ADD UNIQUE INDEX year (publicyear);

10.刪除索引

（1）ALTER TABLE 表名 DROP INDEX 索引名

ALTER TABLE book drop index year;

(2) DROP INDEX 索引名 ON 表名

DROP INDEX fulltextdx on book;