首页 > 代码库 > MySQL学习笔记02_数据库和表的基本操作
MySQL学习笔记02_数据库和表的基本操作
02_1 操作数据库
(1)创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[, create_specification]...]解释:[IF NOT EXISTS]创建时提前检查一下是否存在数据库create_specification:(创建条件) [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name CHARACTER SET编码集 COLLATE校对规则
- 创建一个名称为mysql的数据库
CREATE DATABASE mysql;
- 创建一个使用utf8字符集的mysql2数据库
CREATE DATABASE mysql2 CHARACTER SET utf8;
- 创建一个使用utf8字符集,并带校对规则的mysql3数据库
CREATE DATABASE mysql3 CHARACTER SET gbk COLLATE gbk_bin;
- 列出可用的字符集:
SHOW CHARACTER SET;mysql> show character set;
- 列出gbk和gb2312字符集的校对规则:
SHOW COLLATION LIKE ‘字符集名%’;mysql> show collation like ‘gbk%‘;mysql> show collation like ‘gb2312%‘;
- 校对规则一般有这些特征:
- 两个不同的字符集不能有相同的校对规则。
- 每个字符集有一个默认校对规则。
- 校对规则一般有这些特征:
(2)查看数据库
显示数据库语句:SHOW DATABASES;显示数据库创建语句:SHOW CREATE DATABASE db_name;
(3)修改数据库
ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [,alter_specification]…]解释:create_specification:(创建条件)[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name修改某一个库的字符集为utf8:Alter database mydb character set utf8;
(4)删除数据库
DROP DATABASE [IF NOT EXISTS] db_name;删除创建的数据库mydb;drop database mydb;
(5)选择数据库
USE db_name;使用mydb3数据库:use mydb3;查看当前选择的数据库:SELECT DATABASE();
02_2 数据类型
- 整型:TINYINT SMALLINT MEDIUMINT INT BIGINT
- 浮点类型和定点数类型:FLOAT DOUBLE DECLMAL(M,D)
- 日期与时间类型:YEAR DATE TIME DATETIME TIMESTAMP
- 字符串和二进制类型:CHAR VARCHAR BINARY VARBINARY BLOB TEXT ENUM SET BIT
02_3 数据表的基本操作
(1)增加表
create table table_name(field1 datatype,field2 datatype,field3 datatype,field4 datatype,.fieldn datatype)character set 字符集 collate 校对规则field:指定列名 datatype:指定列类型,列和列的声明之间用,隔开,最后的生命处无,但括号外要加;;
练习:创建一个员工表employee
create table employee( id int, name varchar(20), gender char(1), birthday date, entry_date date, job varchar(50), salay double, resume text ); |
(2)查看表
查询当前数据库中所有的表:show tables;查看表结构:desc tab_name;或者describe tab_name;查看表的键表语句:show create table table_name;
(3)修改表
追加列: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);修改表的名称:rename table table_name to 新表名;修改列的名称:alter table table_name change [column] old_col_name column_definition;修改表的字符编码:alter table table_name character set utf8;修改字段的排列位置:alter table tab_name modify 字段名1 数据类型 first|after 字段名2
练习: (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; rename table employee to user; (5)修改表的字符为utf8; alter table user character set utf8; (6)列名name修改为username; alter table user change name username varchar(20); (7)将数据表grade的username字段修改为表的第一个字段; alter table grade modify username varchar(20) first; (8)将数据表grade的id字段插入到字段grade字段的后面; alter table grade modify id int(20) after grade; |
(4)删除表
drop table table_name;
删除上面的user表: drop table user; |
02_4 表的约束
数据库中的表的约束:约束表中的列的值的特点,维护数据库完整性的规则
PRIMARY KEY FOREIGN KEY NOT NULL UNIQUE DEFAULTprimary key:主键约束,用于唯一标识对应的记录foreign key:外键约束not null:非空约束unique:唯一性约束default:默认约束,用于设置字段的默认值
(1)主键约束
单字段主键:字段名 数据类型 primary key
练习:设置id为主键 create table employee( id int primary key, name varchar(20), gender char(1), birthday date, entry_date date, job varchar(50), salay double, resume text ); show tables; desc employee; |
多字段主键:primary key (字段名1,字段名2,字段名3,…,字段名n)
create table example( stu_id int, grade float, course_id int, primary key(stu_id,course_id) ); |
(2)非空约束
字段名 数据类型 NOT NULL;
练习:设置性别不能为空 drop table employee; create table employee( id int primary key, name varchar(20), gender char(1) not null, birthday date, entry_date date, job varchar(50), salay double, resume text ); show tables; desc employee; |
(3)唯一约束
字段名 数据类型 UNIQUE;
练习:设置姓名不允许重复 drop table employee; create table employee( id int primary key, name varchar(20) unique, gender char(1) not null, birthday date, entry_date date, job varchar(50), salay double, resume text ); show tables; desc employee; |
(4)默认约束
字段名 数据类型 DEFAULT 默认值;
练习:设置job的默认值为实习生 drop table employee; create table employee( id int primary key, name varchar(20) unique, gender char(1) not null, birthday date, entry_date date, job varchar(50) default ‘sxs‘, salay double, resume text ); show tables; desc employee; |
(5)设置表的字段值自动增加
字段名 数据类型 AUTO_INCREMENT;
练习:将表中的id字段设置为自动增加 drop table employee; create table employee( id int primary key auto_increment, name varchar(20) unique, gender char(1) not null, birthday date, entry_date date, job varchar(50) default ‘sxs‘, salay double, resume text ); show tables; desc employee; |
(6)索引的概念
在数据库中查找特定的数据,例如:当执行“select*from student where id=10000”语句时,mysql数据库必须从第一条记录开始遍历,直到找到id为10000的数据。显然,这样的效率极低。为此,mysql允许建立索引来加快表的查询和排序。索引可以提高数据的查询速度。数据库的索引好比新华字典的音序表,它是对数据库中一列或多列的值进行排序后的一种结构,其作用就是提高表中数据的查询速度,mysql中的索引分为很多种,如下所示:
- 普通索引:是由KEY或INDEX定义的索引,可以创建在任何数据中,其值是否唯一和非空没有固定的要求,由字段本身的约束条件所决定;
- 唯一性索引:唯一索引是由UNIQUE定义的索引,该索引所在字段的值必须是唯一的;
- 全文索引:是由FULLTEXT定义的索引,它只能创建在CHAR、VARCHAR 或TEXT类型的字段上,而且,现在只有MyISAM存储引擎支持全文索引;
- 单列索引:单列索引指的是在表中单个字段上创建索引,可以是普通索引,唯一索引或全文索引,只要保证索引只对应表中的一个字段即可;
- 多列索引:多列索引指的是在表中多个字段上创建索引,只有在查询条件中使用了这些字段中的第一个字段时,该索引才会被使用;
- 空间索引:由SPATIAL定义的索引,只能创建在空间数据类型的字段上(GEOMETRY\POINT\LINESTRING\POLYGON)现在只有MyISAM存储引擎支持空间索引。
注意:虽然索引可以跳高数据的查询速度,但索引会占用一定的磁盘空间,并且在创建和维护索引时,其消耗的时间是随着数据量的增加而增加的,因此,使用索引是,应该综合考虑索引的优点和缺点。
(7)创建索引
- 创建表的时候创建索引
CREATE TABLE 表名(字段名 数据类型[完整性约束条件], 字段名 数据类型[完整性约束条件], ...... 字段名 数据类型 [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [别名](字段名1 [(长度)]) [ASC升序|DESC降序]));
练习:创建普通索引 create table ti( id int, name varchar(20), score float, index (id) ); desc ti;
select * from ti where id = 1 \G 为了查看索引是否被使用,可以使用explain语句进行查看 explain select * from ti where id = 1 \G
练习:创建唯一索引 create table t2( id int not null, name varchar(20) not null, score float, unique index unique_id(id ASC) );
练习:创建全文索引 create table t3( id int not null, name varchar(20) not null, score float, fulltext index fulltext_name(name) )ENGINE=MyISAM;
练习:创建单列索引 create table t4( id int not null, name varchar(20) not null, score float, index single_name(name(20)) );
练习:创建多列索引 create table t5( id int not null, name varchar(20) not null, score float, index multi(id name(20)) ); explain select * from t5 where id = 1;
练习:创建空间索引 create table t6( id int, space GEOMETRY NOT NULL, SPATIAL INDEX sp(space) )engine=MyISAM; |
- 使用CREATE INDEX 语句在已经存在的表上创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] 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 varchar(255) null, publicyear YEAR NOT NULL);
练习:创建普通索引在bookid这一列上 create index index_id on book(bookid);
练习:创建唯一索引在bookid这一列上 create unique index unqidx on book(bookid);
练习:创建单列索引
练习:创建多列索引 create index mulitidx on book(authors(20),info(20));
练习:创建全文索引,注意只能加在引擎MyISAM的表上 drop table book; create table book( bookid int not null, bookname varchar(255) not null, authors varchar(255) not null, info varchar(255) null, comment varchar(255) null, publicyear YEAR NOT NULL )engine=MyISAM; create FULLTEXT index ftindex on book (bookname);
练习:创建空间索引 create table t7( g geometry not null )engine=MyISAM; create spatial index spatidx on t7(g); |
- 使用ALTER TABLE 语句在已经存在表上创建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (字段名 [(长度)] [ASC|DESC]);
练习:创建普通索引 drop table book; create table book( bookid int not null, bookname varchar(255) not null, authors varchar(255) not null, info varchar(255) null, comment varchar(255) null, publicyear YEAR NOT NULL ); alter table book add index indexone (bookid); |
(8)删除索引
方式1:ALTER TABLE 表名 DROP INDEX 索引名
alter table book drop index indexone; |
方式2:DROP INDEX 索引名 ON 表名;
drop index indexone on book; |
本文链接:http://www.cnblogs.com/homewch/p/6018642.html
MySQL学习笔记02_数据库和表的基本操作