MySQL學習筆記02_數據庫和表的基本操作，mysql學習筆記

MySQL學習筆記02_數據庫和表的基本操作，mysql學習筆記

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   DEFAULT

primary 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

mysql> explain select * from ti where id = 1 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: ti

         type: ref

possible_keys: id

          key: id

      key_len: 5

          ref: const

         rows: 1

        Extra: NULL

1 row in set (0.23 sec)

 

練習：創建唯一索引

create table t2(

                                                        id int not null,

                                                        name varchar(20) not null,

                                                        score float,

                                                        unique index unique_id(id ASC)

);

mysql> show create table t2 \G

*************************** 1. row ***************************

       Table: t2

Create Table: CREATE TABLE `t2` (

  `id` int(11) NOT NULL,

  `name` varchar(20) NOT NULL,

  `score` float DEFAULT NULL,

  UNIQUE KEY `unique_id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.08 sec)

 

練習：創建全文索引

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