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、数值类型:
下面的表显示了需要的每个整数类型的存储和范围。
2、日期和时间类型:
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
3、字符串类型:
(1)说明:
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
该节描述了这些类型如何工作以及如何在查询中使用这些类型。
(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;
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;
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;
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;
(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;
(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;
(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="冰冰";
#当子表中的记录引用主表中的记录时,是无法删除主表中的记录的。
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不能识别该约束。