二、数据库和表的基本操作

操作数据库

创建数据库

//创建数据库，并先检查是否已经存在，可省略不检查
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,...]]

//创建的条件
create_specification:
    [DEFAULT]CHARACTER SET charset_name指定数据库采用的字符集
    [DEFAULT]COLLATE collation_name指定数据库字符集的比较方式

例一：创建一个名为mydb1的数据库

CREATE DATABASE mydb1;

例二：创建一个使用gbk字符集的mydb2数据库

CREATE DATABASE mydb2 CHARACTER SET gbk;

例三：创建一个使用utf-8字符集，并带校对规则的mydb3数据库【校对规则见MySQL文档】

CREATE DATABASE mydb3 character set utf8 collate utf8_bin;

查看数据库

//显示数据库语句
SHOW DATABASES
//显示数据库创建语句
SHOW CREATE DATABASE db_name

例一：查看当前数据库服务器中的所有数据库

show databases;

例二：查看mydb2数据库的定义信息

show create database mydb2;

修改数据库

ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [,...]]

alter_specifucation:
  [DEFAULT]CHARACTER SET charset_name
  [DEFAULT]COLLATE collation_name

例一：查看服务器中的数据库，并把其中一个库的字符集改为utf8

show create database mydb2;  
alter database mydb2 character set utf8;

删除数据库

DROP DATABASE [IF EXISTS] db_name

例一：删除mydb1

drop database mydb1;

选择数据库

USE db_name
SELECT DATABASE();查看数据库

例一：选择mydb1数据库

use mydb1;

例二：查看当前选择的数据库

select database();

数据库中表的操作

MySQL数据库中的数据类型

整型数据：

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32768
MEDIUMINT	3	0~16777215	-8388608~8388608
INT	4	0~4294967295	-2147483648~2147483648
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775808

浮点数类型和定点数类型

数据类型	字节数
FLOAT	4
DOUBLE	8
DECIMAL(M,D)【M表示数据长度，D表示小数点后的长度】	M+2

日期与时间类型

数据类型	字节数	日期格式	零值
YEAR	1	YYYY	0000
DATE	4	YYYY-MM-DD	0000-00-00
TIME	3	HH:MM:SS	00：00：00
DATETIME	8	YYYY-MM-DD HH:MM:SS	0000-00-00 00：00：00
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	0000-00-00 00：00：00

字符串和二进制类型

CHAR：固定长度字符串，char（4），查询速度快
VARCHAR：可变长度字符串，varchar（4）
BINARY和VARBINARY类型，同上
TEXT类型【存大文本】：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
BLOB类型【存大二进制】：TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB
ENUM类型：枚举类型
SET类型：同枚举，但存储的是每个值的序号
BIT类型：表示二进制数，BIT(1)表示1位二进制

增加表

CREATE TABLE table_name
(
    field1 datatype,
    field2 datatype,
    field3 datatype,
)character set 字符集 collate
校对规则【一般不指定，使用数据库默认的字符集和校对规则】

//field：指定列名，datatype：指定列类型
create table employee(
    id int,
    name varchar(20),
    gender char(1),
    birthday date,
    entry_date date,
    job varchar(50),
    salay double,
    resume text
);

查看表

SHOW TABLES;查看所有表
DESC employee;查看employee表结构
SHOW CREATE TABLE employee;查看建表语句

修改表

//增加一列
ALTER TABLE table_name ADD(column datatype [DEFAULT expr][,column datatype]...);
//修改列属性
ALTER TABLE table_name MODIFY(column datatype [DEFAULT expr][,column datatype]...);
//删除列
ALTER TABLE table_name DROP(column);
//修改表名
REMANE TABLE 表名 TO 新表名;
//修改列的名称
ALTER TABLE table_name change [column] old_name new_name datatype;
//修改表的字符集编码
ALTER TABLE table_name CHARACTER SET utf8;

例一：在员工表中添加一列image列

alter table employee add image blob;

例二：修改job列，使其长度为60

alter table employee modify job varchar(60);

例三：删除gender列

alter table employee drop gender;

例四：表名改为user

rename table employee to user;

例五：修改表的字符集为utf8

alter table user character set utf8;

例六：列名name修改为username

alter table user change name username varchar(20);

删除表

DROP TABLLE table_name;

例一：删除user表

drop table user;

表的约束

概述：为了防止数据表中插入错误的数据，需要对表定义一些规则，即表的约束。

约束条件 说明
PRIMARY KEY 主键约束，用于唯一标识对应的记录
FOREIGN KEY 外键约束
NOT NULL 非空约束
UNIQUE 唯一性约束
DEFFAULT 默认值约束，用于设置字段的默认值

主键约束 PRIMARY KEY

//1.单字段主键
CREATE TABLE example1(id INT PRIMARY KEY,
                      name VARCHAR(20)
);
//多字段主键
CREATE TABLE example1(id INT,
                      name VARCHAR(20),
                      grade FLOAT,
                      PRIMARY KEY(id,name)
);

非空约束 NOT NULL

CREATE TABLE example1(id INT,
                      name VARCHAR(20) NOT NULL,
                      grade FLOAT,
                      PRIMARY KEY(id,name)
);

唯一约束 UNIQUE

CREATE TABLE example1(id INT,
                      name VARCHAR(20) NOT NULL,
                      grade FLOAT UNIQUE,
                      PRIMARY KEY(id,name)
);

默认约束 DEFAULT

CREATE TABLE example1(id INT,
                      name VARCHAR(20) NOT NULL,
                      grade FLOAT,
                      job VARCHAR(20) DEFAULT 'sxs',
                      PRIMARY KEY(id,name)
);

设置表的字段值自动增加（AUTO_INCREMENT）

CREATE TABLE example1(id INT PRIMARY KEY AUTO_INCREMENT,
                      name VARCHAR(20) NOT NULL,
                      grade FLOAT
);

索引

概述：在数据库操作中，需要经常查询其中一条记录，比如，查询id=1000的记录，这时候就需要一种效率高的查询方法，即索引。是对表中的值进行排列后的一种结构。

普通索引：是由KEY或者INDEX定义的索引，可以创建在任何类型，其值是否唯一或者非空没有要求。

唯一性索引：由UNIQUE定义的所以，该索引字段必须唯一

全文索引：由FULLTEXT定义的所以，只能创建在char、varchar或者text类型的字段上，并且只有MyISAM存储引擎支持全文索引

单列索引：指在表中单个字段上创建索引，可以是普通索引，唯一索引或者全文索引

多列索引：指表中多个字段上创建索引，只有在查询中使用了这些字段中的第一个字段时，该索引才会被使用

空间索引：由SPATIAL定义的索引，只能创建在空间数据类型的字段上。只能在存储引擎为MyISAM的表中创建
空间数据类型：GEOMETRY，POINT，LINESTRING，POLYGON

创建索引
创建表的时候创建索引

CREATE TABLE 表名(
    字段名 数据类型 [完整性约束条件]
    ....
    [UNIQUE或FULLTEXT或SPATIAL] INDEX或KEY [别名] (字段名1 [(长度)]) [ASC或者DESC]
);

例一：创建普通索引

CREATE TABLE example1(id INT, name VARCHAR(20) NOT NULL, grade FLOAT, INDEX (id));

例二：创建唯一索引

CREATE TABLE example1(id INT, name VARCHAR(20) NOT NULL, grade FLOAT, UNIQUE INDEX unique_id(id ASC););

例三：创建全文索引

CREATE TABLE example1(id INT, name VARCHAR(20) NOT NULL, grade FLOAT, FULLTEXT INDEX fulltext_name(name))ENGINE = MyISAM;

例四：创建多列索引

CREATE TABLE example1(id INT, name VARCHAR(20) NOT NULL, grade FLOAT, INDEX multi(id,name(20)));

例五：创建空间索引

CREATE TABLE example1(id INT, name VARCHAR(20) NOT NULL, grade FLOAT, space GEOMETRY NOT NULL, SPATIAL INDEX sp(space))ENGINE = MyISAM;

CREATE INDEX语句

CREATE [UNIQUE或者FULLTEXT或者SPATIAL] INDEX 索引名 ON 表名 (字段名 [(长度)] [ASC或者DESC]);

例一：对book表创建普通索引

CREATE INDEX index_id ON book (id)

例二：创建唯一索引

CREATE UNIQUE INDEX uniqueidx ON book (id);

使用ALTER TABLE语句创建索引

ALTER TABLE 表名 ADD [UNIQUE或者FULLTEXT或者SPATIAL] INDEX 索引名 (字段名 [(长度)] [ASE或者DESC])；

例一：在book表上创建普通索引

ALTER TABLE book ADD INDEX indexone (id);

删除索引

ALTER TABLE 表名 DROP INDEX 索引名；

DROP INDEX 索引名 ON 表名;