35.1、数据库之库操作:

1、创建数据库(在磁盘上创建一个对应的文件夹):

create database [if not exists] db_name [character set xxx];


2、查看数据库:

show databases;

#查看所有数据库

show create database db_name;

#查看数据库的创建方式


3、修改数据库:

alter database db_name [character set xxx];


4、删除数据库:

drop database [if exists] db_name;


5、切换数据库:

use db_name;

#注意:进入到某个数据库后没办法再退回之前状态,但可以通过use进行切换,查看当前使用的数据库select database();


35.2、mysql数据库类型:

MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。

1、数值类型:

下面的表显示了需要的每个整数类型的存储和范围。

35、mysql数据库(ddl)_主键


2、日期和时间类型:

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

35、mysql数据库(ddl)_主键_02


3、字符串类型:

(1)说明:

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

该节描述了这些类型如何工作以及如何在查询中使用这些类型。

35、mysql数据库(ddl)_mysql_03


(2)补充:

1)CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

2)BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。

3)BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

4)有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度。


35.3、数据库表操作:

1、创建表(类似于一个excel表):

create table tab_name(

field1 type [完整性约束条件] [comment <注释内容>],

field2 type,

...

fieldn type

)[CHARACTER SET XXX,ENGINE=INNODB];


(1)创建一个员工表employee:

create table employee(

id int primary key auto_increment,

name varchar(20),

gender bit default 1,

-- gender char(1) default 1 或者 TINYINT(1)

birthday date,

entry_date date,

job varchar(20),

salary double(4,2) unsigned,

resume text

/*注意,这里作为最后一个字段不加逗号*/

);


(2)约束:

primary key

#非空且唯一,能够唯一区分出当前记录的字段称为主键!

auto_increment

#自动增加,主键字段必须是数字类型。

unique

#值唯一

not null

#值不为空

foreign key

#外键约束


2、查看表信息:

desc tab_name

show columns from tab_name

#查看表结构

show tables

#查看当前数据库中的所有的表

show create table tab_name

#查看当前数据库表建表语句


3、修改表结构:

(1)增加列(字段):

alter table tab_name add [column] 列名 类型[完整性约束条件][first|after 字段名];

alter table user add addr varchar(20) not null unique after username;

#添加单个字段

alter table user add addr varchar(20), add age int first, add birth varchar(20) after name;

#添加多个字段


(2)修改一列类型:

alter table tab_name modify 列名 类型 [完整性约束条件][first|after 字段名];

alter table user modify age tinyint default 20;

alter table user modify age int after id;


(3)修改列名:

alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件][first|after 字段名];

alter table user change age Age int default 28 first;


(4)删除列:

alter table tab_name drop [column] 列名;

alter table user add salary float(6,2) unique not null after name, drop addr;


(5)修改表名:

rename table 表名 to 新表名;


(6)修改表所用的字符集:

alter table table_name character set utf8;


(7)注意:

对表的列进行操作时根据需要加上"类型 [完整性约束条件][first|after 字段名]"条件,否则表列的类型和

约束会发生变化。


4、删除表:

drop table tab_name;


5、主键操作:

(1)添加主键:

alter table tab_name add primary key(字段名称,...)

mysql> create table test(num int auto_increment);

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> create table test(num int primary key auto_increment);


(2)删除主键:

alter table test modify id int;

#auto_increment没了,但这样写主键依然存在,所以还要加上下面这句才能删除主键。

alter table test drop primary key;

#仅仅用这句无法直接删除主键


6、索引操作:

(1)索引简介:

1)索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于

数据库良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于数据库性能的影响

愈发重要。

2)索引优化应该是对数据库查询性能优化最有效的手段了。

3)索引能够轻易将数据库查询性能提高好几个数量级。

4)索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。

5)索引特点:创建与维护索引会消耗很多时间和磁盘空间,但查询速度大大提高。


(2)创建表时添加索引:

1)语法:

CREATE TABLE 表名 (

字段名1 数据类型 [完整性约束条件…],

字段名2 数据类型 [完整性约束条件…],

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[索引名] (字段名[(长度)] [ASC|DESC])

);

2)创建普通索引示例:

--创建:

CREATE TABLE emp1 (

id INT,

name VARCHAR(30),

resume VARCHAR(50),

INDEX index_emp1_name(name)

/*KEY index_emp1_name(name)*/

/*普通索引*/

);

--查看索引:

mysql> show index from emp1\G;

35、mysql数据库(ddl)_主键_04

3)创建唯一索引示例:

--创建:

CREATE TABLE emp2 (

id INT,

name VARCHAR(30),

bank_num CHAR(18) UNIQUE,

/*唯一索引*/

resume VARCHAR(50),

UNIQUE INDEX index_emp2_name(name)

/*唯一索引*/

);

--查看索引:

mysql> show index from emp2\G;

35、mysql数据库(ddl)_主键_05

4)创建全文索引示例:

说明:在MySQL5.6之前,只有MyISAM引擎支持全文索引,我这里使用的是MySQL

5.6、innodb引擎。

--创建:

CREATE TABLE emp3 (

id INT,

name VARCHAR(30),

resume VARCHAR(50),

FULLTEXT INDEX index_emp3_resume(name)

/*全文索引*/

);

--查看索引:

mysql> show index from emp3\G;

35、mysql数据库(ddl)_mysql_06

5)创建多列索引示例:

--创建:

CREATE TABLE emp4 (

id INT,

name VARCHAR(30) ,

resume VARCHAR(50),

INDEX index_emp4_name_resume(name,resume)

/*联合索引*/

);

--查看索引:

mysql> show index from emp4\G;

35、mysql数据库(ddl)_主键_07


(3)使用CREATE在已存在的表上创建索引:

1)语法:

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

2)示例:

--创建:

CREATE INDEX index_emp1_resume ON emp1(resume);

--查看索引:

mysql> show index from emp1\G;

35、mysql数据库(ddl)_mysql_08


(4)ALTER TABLE在已存在的表上创建索引:

1)语法:

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名(字段名[(长度)] [ASC | DESC]);

2)示例:

--创建:

ALTER TABLE emp2 ADD UNIQUE INDEX index_emp2_resume(resume);

--查看:

mysql> show index from emp2\G;

35、mysql数据库(ddl)_mysql_09


(5)删除索引:

1)语法:DROP INDEX 索引名 on 表名;

2)示例:

DROP INDEX index_emp1_resume on emp1;

DROP INDEX index_emp2_resume on emp2;


(6)索引测试实验:

1)创建试验数据:

--创建表:

create table index_test(id int,name varchar(20));

--创建数据:

delimiter //

create procedure autoinsert()

BEGIN

declare i int default 1;

while(i<=500000) do

insert into index_test values(i,'lc');

set i=i+1;

end while;

END //

delimiter;

--调用函数:

call autoinsert();

--执行完成后删除函数:

drop PROCEDURE autoinsert;

2)花费时间比较:

--创建索引前:

select * from index_test where id=300000; --0.32s

--添加索引:

create index index_index_test_id on index_test(id);

--创建索引后:

select * from index_test where id=300000; --0.00s


7、创建文章表:

create table article(

id int primary key auto_increment,

title varchar(20),

publish_date INT,

click_num INT,

is_top TINYINT(1),

content TEXT

);


8、完整性约束条件之主键约束:

一张表只能有一个主键。

主键类型不一定非是整型。

(1)单字段主键:

create table user(

id INT primary key,

name varchar(20),

city varchar(20)

);


(2)多字段联合主键:

create table user(

id INT,

name varchar(20),

city varchar(20),

primary key(name,id)

);


9、外键约束:

(1)说明:

每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任。


(2)创建主表并插入数据:

1)创建主表:

CREATE TABLE ClassCharger(

id int PRIMARY KEY auto_increment,

name VARCHAR(20) not null,

age TINYINT,

is_marriged boolean

/*show create table ClassCharger: tinyint(4)*/

);

2)插入数据:

INSERT INTO ClassCharger(name,age,is_marriged) VALUES("冰冰",12,0),

("丹丹",14,0),

("歪歪",22,0),

("姗姗",20,0),

("小雨",21,0);


(3)创建子表并插入数据:

1)创建子表:

CREATE TABLE Student(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20) not null,

charger_id INT,

/*切记,作为外键一定要和关联主键的数据类型保持一致。*/

FOREIGN KEY(charger_id) REFERENCES ClassCharger(id)

);

2)子表插入数据:

INSERT INTO Student(name,charger_id) VALUES("alvin1",2),

("alvin2",4),

("alvin3",1),

("alvin4",3),

("alvin5",1),

("alvin6",3),

("alvin7",2);


(3)测试:

1)DELETE FROM ClassCharger WHERE name="冰冰";

#当子表中的记录引用主表中的记录时,是无法删除主表中的记录的。

35、mysql数据库(ddl)_主键_10

2)INSERT student(name,charger_id) VALUES("yuan",1);

#在子表中插入数据


(4)补充:

1)增加外键:

ALTER TABLE student ADD CONSTRAINT classcharger_fk_student FOREIGN KEY(charger_id) REFERENCES classcharger(id);

2)删除外键:

ALTER TABLE student DROP FOREIGN KEY classcharger_fk_student;


10、INNODB支持的ON语句:

(1)外键约束对子表的含义:

如果在父表中找不到候选键,则不允许在子表上进行insert/update。


(2)外键约束对父表的含义:

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于在定义子表的外键时指定的

on update/on delete子句。


(3)innodb支持的四种方式(默认情况下,mysql的外键约束类型是restrict(no action)类型):

1)cascade方式:

在父表上update/delete记录时,同步update/delete掉子表的匹配记录。

外键的级联删除,如果父表中的记录被删除,则子表中对应的记录自动被删除。

语法:

FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE


2)set null方式:

在父表上update/delete记录时,将子表上匹配记录的列设为null,要注意子表的外键列不能为not null。

语法:

FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL


3)No action方式:

如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作。


4)Restrict方式 :

同no action, 都是立即检查外键约束。


5)补充:

Set default方式:父表有变更时,子表将外键列设置成一个默认的值,要注意子表的外键要有默认值。

Innodb不能识别该约束。