数据库基本知识

一、创建和查看数据库

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;