(数据库存储应用)S2数据库和表的基本操作

一、数据库的基础知识

操作数据库

推荐关键字大写 变量小写

1. 查看库

show databases;

2. 创建数据库

create database [if not exists(提前检查是否存在)] name [属性];
属性：character set charset_name 字符集
	collate collation_name 校对规则

~ 校对规则：指定排序方式

• 创建mydb1数据库

CREATE DATABASE mydb1;

• 创建mydb2数据库编码为gbk

CREATE DATABASE mydb1 mydb2 CHARATER SET gbk;

• 创建mydb3数据库编码为gbk并带校对规则

create database mydb3 character set utf8 collate utf8_bin;

3. 查看数据库信息
查看所有

show databases;

显示数据库创建语句

show create database 数据库名字;

4. 修改数据库
数据库安装成功后编码集就确定了，可以使用alert更改编码集

alter database 要修改的数据库名 default character set 修改成编码格式 [ collate 修改成编码格式_bin ]

修改编码[和校对规则。]

修改后

5. 删除数据库

drop database 数据库名称;

6. 选择数据库

use 数据库名;

查看当前所选数据库

select database();

操作表

1. 增加表
2. 修改表
3. 查勘表

二、 数据类型与表操作

数据类型

使用MySQL存储时，不同的数据类型决定了mysql存储数据方式的不同

1. 整型：

数据类型	字节数	无符号取值范围	有符号取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32768
MEDIUMINT	3	0~ 2^ 24	-2^12 ~ 2^12
INT	4	0~2^ 32	-2^16 ~ 2^16
BIGINT	4	0 ~ 2^64	-232~232

2. 浮点类型和定数类型

数据类型	字节数	无符号取值范围	有符号取值范围
FLOAT	4
DOUBLE	8
DECIMAL(M, D)	M+2	M表示长度，D表示小数点位数	3.1415变为3.14

3. 日期与时间

数据类型	字节数	取值范围	日期格式	领值
YEAR	1	1901~2155	YYYY’	0000
DATE	4	1000-01-01~9999-12-3	YYYY-MM-DD	0000-00-00
TIME	3	-838:59:59~838-59-59	HH-MM-SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-3 23:59:59	YYYY-MM-DD HH-MM-SS	0000-00-00 00:00:00
TIMESTAMP（时间戳）	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH-MM-SS	0000-00-00 00:00:00

1. year类型
2.  
3.  
4.  
5. TIMESTAMP:使用CURRENT_TIMESTAMP来输入当前系统时间/值为NULL时自动获取系统时间/无输入也是获取系统时间。

字符串

1. char（）和varchar（）
   a. char()为定长， 括号是多少就占多少字节。
   b. varchar()为变长，括号中为最大长度，实际长度以实际字符串来计算。

varchar比char省空间 char比varchar速度快。

2. binary和 varbinary
   类似char和varchar
3. TEXT
   存储大文本

数据类型	储存范围
TINYTEXT	0~255字节
TEXT	0~2^16字节
MEDIUMTEXT	0 ~ 2……
LONGTEXT	0~很大

4. BLOB类型
   存储大二进制类型，如图片和pdf文档。

数据类型	储存范围
TINYBLOB	0~255
BLOB	0~65535
MEDIUMBLOB	0~16777215
LONGBLOB	0~4GB

5. ENUM类型
   枚举类型
   ENUM(‘值1’,‘值2’,‘值3’,‘值n’)
   • 数据只能从枚举列表中获取，并且只能取一个。
     枚举列举中的每个值都有一个顺序编号，MySQL中存的是编号，而不是列表中的值
6. SET
   用于表示字符串对象
   SET(‘值1’,‘值2’,‘值3’,‘值n’)
   SET(‘值1’,‘值2’,‘值3’,‘值n’)，中的每个值都有一个顺序编号，MySQL中存的是编号，而不是列表中的值
7. BIT类型
   用于表示二进制数据
   BIT(M)
   • M表示每个值得位数如BIT(1) 表示0或1；如同java中的布尔
     BIT(6) 存’101’与’000101’相同。

操作表

1. 增加表

create table table_name(
	字段名（列名） 数据类型，
	字段名 数据类型
)character set 字符集 collate 校对规则

~ 创建员工表 employee

create table employee(
	id int, name varchar(20),
	 gender char(1), birthady date,
	  entry_date date, job varchar(50),
	   salary double, resume text
);

2. 查看表
查看表结构：desc tableName;
查看当前所有表：show tables;
查看当前数据库表建表语句：
show create table tabName;
3. 修改表
增加列：ALTER TABLE tabName ADD (column datatype [default expr][, column datatype] )

修改列：ALTER TABLE tabName MODIFY (column datatype [default expr][, column datatype] )

删除列：ALTER TABLE tabName DROP (column datatype [default expr][, column datatype] )

• 修改表名：rename table 旧表名 to 新表名；
• 修改表的字符集：
  alter table 表名 character set 编码;
• 修改列名：
  alter table 表名 change 列名 新列名 字符类型;
  

4. 删除表
   drop table tabName;

约束

约束条件	说明
primary key	主键约束
foreign key	外键约束
not null	非空约束
unique	唯一性约束
default	默认值约束，设置字段的默认值

• 主键约束

   1. 单字段约束
   	在创建表时‘字段名 属性 primary key’
   2.多字段主键
   	在创建表时：
   	‘create table one(
   			id int,
   			name varchar(20),
   			grade int
   			primary key(id, name)
   	);’
  

• 非空约束

    在创建表时‘字段名 属性 not null’
  

• 唯一约束

    在创建表时‘字段名 属性 unique’
  

• 默认约束

    1. 在不给字段值时有默认的值		
    在创建表时‘字段名 属性 default 默认值（数字直接写，字符串加引号）’
  

• 自增

  在创建表时‘字段名 属性 auto_increment’

索引

概念

好比音序表，是对数据库一列或者多列的值进行排序后的一种结构

索引

1. 普通索引：
	由key或者index定义的索引，可以创建在任何类型字段上，由 自身是否唯一和非空的约束决定。
2. 唯一性索引：
		由unique定义索引，该字段值必须唯一
3. 全文索引：
		由fulltext定义的索引，他只能创建在CHAR、varchar或者text类型字段上，只有myisam引擎支持全文索引。

4. 单列索引：
	创建在表中的某一个字段上，保证该索引对应表中的一个字段。
5.多列索引
	创建在表中的多个字段上，只有查询条件使用的这些字段中的第一个字段时，该索引才会被使用。
6. 空间索引:
	由SPATiAL定义，他只能创建在空间数据类型的字段上。只用在MyISAM引擎

索引增加查询速度，但占用内存空间，并且在创建和维护索引时随数据量增加而增加。

创建索引

创建表时创建索引

create table demo(
	id int [约束条件],
	.
	.
	.
	[unique|fulltext|spatial] index|key [别名] (字段1[(长度)][ASC|DESC])
)

索引就是将字段进行排序，可以选则升序ASC降序DESC
关键字参考上文。

• 创建普通索引

create table demo(
	id int [约束条件],
	.,
	.,
	.,
	index (id)
);

• 创建唯一索引

create table demo(
	id int NOT null,
	.,
	.,
	.,
	unique index unique_id(id ASC)
);

• 创建全文索引

create table demo(
	id int [约束条件],
	name varchar(20),
	.,
	.,
	fulltext index fulltext_name(name)
)engine=MyISAM;

• 创建单列索引

create table demo(
	id int not null,
	name varchar(20) not null,
	.,
	.,
	index single_name(name(20))
);

• 多列索引

create table demo(
	id int not null,
	name varchar(20) not null,
	.,
	.,
	index mu(id,name(20))
);

select * from demo where id = 1;

• 创建空间索引

create table demo(
	id int not null,
	name varchar(20) not null,
	spa GEOMETRY notnull,
	.,
	SPATIAL index sp(spa)
)engine=MYISAM;

使用create INDEX语句在已存在的表上创建索引

create [ UNIQUE|FULLTEXT|SPATAL] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC|DESC])

先创建一张表

create table book(
	bookid int not null,
	bookname varchar(255) not null,
	authors varchar(255) not null,
	info varchar(255) null,
	comment varcahr(255) null,
	publicyear year not null
);

• 创建普通索引

create index index_id on book(bookid);

• 创建唯一索引

create unique index unique_id on book(bookid);

• 创建单列索引

create index mu on book(authors(20));

• 多列索引

create index mu on book(authors(20),info(20));

• 创建全文索引(表的引擎必须为MyISAM)

create fulltext index fulltext_name on book(bookname);

• 创建空间索引(表的引擎必须为MyISAM，数据类型为空间类型)

create spatial index 别名 on 表名(字段名);

使用alter table 在已存在的表创建语句

alter table 表名 add [unique|fulltext|spatial] index|key [别名] (字段1[(长度)][ASC|DESC]);

先创建一张表

create table book(
	bookid int not null,
	bookname varchar(255) not null,
	authors varchar(255) not null,
	info varchar(255) null,
	comment varcahr(255) null,
	publicyear year not null
);

• 创建普通索引

alter table book add index index_id(bookid) ;

删除索引

• alter table 删除

alter table 表名 drop index 索引名;

• drop index删除

drop index 索引名 on 表名；