MySQL学习笔记(上)
DDL
MySQL数据库基本操作
-
创建数据库的语法形式
CREATE DATABASE database_name;
-
数据库命名规则
- 由字母、数字、下划线、@、#和$符号组成
- 首字母不能是数字和$符号
- 标识符不允许是MySQL的保留字
- 不允许由空格和特殊字符
- 长度小于128位
-
查看数据库
SHOW DATABASES;
-
选择数据库
USE database_name;
-
删除数据库
DROP DATABASE database_name;
MySQL数据库中存储引擎和数据类型
存储引擎
-
查看数据库支持的存储引擎
SHOW ENGINES;
-
查看方式二
SHOW VARIABLES LIKE 'have%';
-
查询默认存储引擎
SHOW VARIABLES LIKE 'storage_engine%';
-
修改默认存储引擎
打开my.ini配置文件,修改default-storage-engine=配置引擎
-
选择存储引擎
特性 | MyISAM | InnoDB | MEMORY |
---|---|---|---|
存储限制 | 有 | 64TB | 有 |
锁机制 | 表锁 | 行锁 | 表锁 |
B树索引 | 支持 | 支持 | 支持 |
哈希索引 | 不支持 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 | 不支持 |
集群索引 | 不支持 | 支持 | 不支持 |
数据缓存 | 支持 | 支持 | |
索引缓存 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | 不支持 | 不支持 |
空间使用 | 低 | 高 | N/A |
内存使用 | 低 | 高 | 中等 |
批量插入的速度 | 高 | 低 | 高 |
支持外键 | 不支持 | 支持 | 不支持 |
事务安全 | 不支持 | 支持 | 不支持 |
数据类型
- 整数类型
整数类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
TINYINT | 1 | 有符号-128,无符号0 | 有符号127,无符号255 |
SMALLINT | 2 | 有符号-32768,无符号0 | 有符号32767,无符号65535 |
MEDIUMINT | 3 | 有符号-8388608,无符号0 | 有符号8388607,无符号1677215 |
INT和INTEGER | 4 | 有符号-2147483648,无符号0 | 有符号2147483647,无符号4294967295 |
BIGINT | 8 | 有符号-9223372036854775808,无符号0 | 有符号9223372036854775807,无符号18446744073709551615 |
- 浮点数类型
浮点数类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
FLOAT | 4 | ±1.75494351E-38 | ±3.402823466E+38 |
DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 |
- 定点数类型
定点数类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
DEC(M,D)和DECIMAL(M,D) | M+2 | 与DOUBLE相同 | 与DOUBLE相同 |
- 位类型
位类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
BIT(M) | 1-8 | BIT(1) | BIT(64) |
- 日期和时间类型
日期和时间类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
DATE | 4 | 1000-01-01 | 9999-12-31 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 4 | 19700101080001 | 2038年的某个时刻 |
TIME | 3 | -835:59:59 | 838:59:59 |
YEAR | 1 | 1901 | 2155 |
- 字符串类型
CHAR系列字符串类型 | 字节 | 描述 |
---|---|---|
CHAR(M) | M | M为0-255之间的整数 |
VARCHAR(M) | M | M为0-65535之间的整数 |
- TEXT系列字符串类型
TEXT系列字符串类型 | 字节 | 描述 |
---|---|---|
TINYTEXT | 0-255 | 值的长度为+2个字节 |
TEXT | 0-65535 | +2 |
MEDIUMTEXT | 0-167772150 | +3 |
LONGTEXT | 0-4294967295 | +4 |
- BINARY系列字符串类型
BINARY | 字节 | 描述 |
---|---|---|
BINARY(M) | M | 允许长度为0-M |
VARBINARY(M) | M | 允许长度为0-M |
可以存储二进制数据
表的操作
-
创建表的语法形式
CREATE TABLE table_name( 属性名1 数据类型, 属性名2 数据类型, ... 属性名n 数据类型 ); # 注意最后一个属性语句后面不用加上逗号,且需要在括号外加上分号
-
查看表结构
DESCRIBE table_name;
DESC table_name;
-
查看表详细定义
SHOW CREATE TABLE table_name;
-
删除表
DROP TABLE table_name;
修改表
-
重命名表
ALTER TABLE table_name RENAME 新表名;
- 在表的最后一个位置增加字段
ALTER TABLE table_name ADD 属性名 属性类型;
-
在表的第一个位置添加字段
ALTER TABLE table_name
ADD 属性名 属性类型 FIRST;
-
在表的指定字段之后添加字段
ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名;
-
删除字段
ALTER TABLE table_name
DROP 字段名;
修改字段
- 1.修改字段的数据类型
ALTER TABLE table_name
MODIFY 属性名 新数据类型;
-
2.修改字段的名字
ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型;
-
3.同时修改字段的名字和属性
ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型;
-
4.修改字段顺序
ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2;
操作表的约束
MySQL支持的1完整性约束
约束 | 含义 |
---|---|
NOT NULL | 约束字段的值不能为空 |
DEFAULT | 设置字段的默认值 |
UNIQUE KEY(UK) | 约束字段的值是唯一 |
PRIMARY KEY(PK) | 约束字段为表的主键,可以作为该表记录的唯一标识 |
AUTO_INCREMENT | 约束字段的值为自动增加 |
FOREIGN KEY(FK) | 约束字段为表的外键 |
设置非空约束(NOT NULL,NK)
-
语法格式
CREATE TABLE table_name( 属性名 数据类型 NOT NULL, ... );
设置字段的默认值(DEFAULT)
-
语法格式
CREATE TABLE table_name( 属性名 数据类型 DEFAULT 默认值, ... );
设置唯一约束
-
语法格式
CREATE TABLE table_name( 属性名 数据类型 UNIQUE, ... );
设置主键约束
主键字段的值默认是唯一、非空的
-
单字段主键
CREATE TABLE table_name( 属性名 数据类型 PRIMARY KEY, ... );
-
多字段主键
CREATE TABLE table_name( 属性名 数据类型, ... [CONSTRAINT 约束名] PRIMARY KEY(属性名1,属性名2,...) );
设置字段值自动增加
-
语法格式
CREATE TABLE table_name( 属性名 数据类型 AUTO_INCREMENT, ... );
设置外键约束
外键约束:用于保证多个表之间的参照完整性,使子表中的一个字段依赖与父表的主键字段
- 语法格式
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
...
CONSTRAINT 外键约束名 FOREIGN KEY(属性名1)
REFERENCES 父表名(属性名2)
);
索引的操作
创建和查看索引
- 1.创建表时创建普通索引
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
...
INDEX|KEY [索引名](属性名1 [(长度)] [ASC|DESC])
);
-
-
例子
CREATE TABLE t_dept( deptno INT, dname VARCHAR(20), loc VARCHAR(40), INDEX index_deptno(deptno) );
-
2.校验索引是否被使用
EXPLAIN SELECT * FROM t_dept WHERE deptno = 1;
-
-
2.在已经存在的表上创建普通索引
CREATE INDEX 索引名 ON 表名(属性名【(长度)】【ASC|DESC】);
-
3.通过ALTER TABLE创建普通索引
ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】);
创建和查看唯一索引
唯一索引:在创建表的时候,限制索引的值必须是唯一的
-
1.创建表时创建唯一索引
CREATE TABLE table_name( 属性名 数据类型, 属性名 数据类型, ... 属性名 数据类型, UNIQUE INDEX|KEY 【索引名】(属性名1【(长度)】【ASC|DESC】) );
-
2.在已经存在的表创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(属性名【(长度)】【ASC|DESC】);
-
3.通过ALTER TABLE创建唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】);
创建和查看全文索引
全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上
-
创建表时创建全文索引
CREATE TABLE table_name( 属性名 数据类型, 属性名 数据类型, ... 属性名 数据类型, FULLTEXT INDEX|KEY 【索引名】(属性名1【(长度)】【ASC|DESC】); )
-
创建过程与唯一索引相似,只是将UNIQUE INDEX 替换为了FULLTEXT INDEX
创建和查看多列索引
多列索引:指在创建索引时,关联的字段不是一个,而是多个。只有查询条件中使用了所关联字段中第一个字段,多列字段才会被使用
-
1.创建表时创建多列索引
CREATA TABLE table_name( 属性名 数据类型, 属性名 数据类型, ... INDEX|KEY 【索引名】(属性名1【(长度)】【ASC|DESC】, ..... 属性名n【(长度)】【ASC|DESC】) );
-
2.在已经存在的表上创建多列索引
CREATE TABLE 索引名 ON 表名(属性名1【(长度)】【ASC|DESC】, ..... 属性名n【(长度)】【ASC|DESC】);
-
3.通过ALTER TABLE创建多列索引
ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名1【(长度)】【ASC|DESC】, ..... 属性名n【(长度)】【ASC|DESC】);
删除索引
-
语法格式
DROP INDEX index_name ON table_name;
视图的操作
视图的特点
- 视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系
- 视图是由基本表(实表)产生的表(虚表)
- 视图的建立和删除不影响基本表
- 对视图内容的更新(添加、删除和修改)直接影响基本表
创建视图
-
语法格式
CREATE VIEW view_name AS 查询语句
-
例子
CREATE VIEW view_selectproduct AS SELECT id,name FROM t_product;
-
查询视图
SELECT * FROM view_selectproduct;
创建各种视图
-
1.查询常量语句的视图
create view view_test1 as select 3.1415926;
-
2.使用聚合函数(SUM、MIN、MAX、COUNT等)查询语句的视图
create view view_test2 as select COUNT(name) from t_student;
-
3.实现排序功能(ORDER BY)查询语句的视图
create view view_test3 as select name from t_student ORDER BY id DESC;
-
4.实现了表内连接查询语句的视图
create view view_test4 as select s.name from t_student as s,t_group as g WHERE s.group_id=g.id AND g.id=2;
-
5.实现表外连接(LEFT JOIN和RIGHT JOIN)查询语句的视图
create view view_test5 as select s.name from t_student as s LEFT JOIN t_group as g ON s.group_id=g.id WHERE g.id=2;
-
6.实现子查询相关查询语句的视图
create view view_test6 as select s.name from t_student as s where s.group_id IN (select id from t_group);
-
7.实现记录联合(UNION和UNION ALL)查询语句的视图
create view view_test7 as select id,name from t_student UNION ALL select id,name from t_group;
-
查看视图
-
SHOW TABLES语句查看视图名
SHOW TABLES;
-
SHOW TABLES STATUS语句查看视图详细信息
SHOW TABLES STATUS;
-
SHOW CREATE VIEW语句查看视图定义信息
SHOW CREATE VIEW view_name;
-
DESCRIBE|DESC语句查看视图设计信息
DESCRIBE|DESC view_name;
-
通过系统表查看视图信息
USE information_schema; select * from views where table_name='view_name';
删除视图
-
语法格式
DROP VIEW view_name【,view_name】...
修改视图
在视图已经存在的情况下,使用CREATE VIEW语句来修改视图会导致报错,只能采用先删除视图再重新创建的方法修改视图数据
-
CREATE OR REPLACE VIEW语句修改视图
CREATE OR REPLACE VIEW view_name AS 查询语句;
-
ALTER语句修改视图
ALTER VIEW view_name AS 查询语句;
利用视图操作基本表
-
检索(查询)数据
通过视图查询数据比直接查询表更加安全、简单、实用
SELECT * FROM view_name;
-
利用视图操作基本表数据
通过修改视图数据,会直接对基本表数据造成影响
INSERT INTO view_name(属性1,属性2,...) VALUES(数据1,数据2,...);
触发器的操作
创建触发器
-
创建有一条执行语句的触发器
CREATE TRIGGER trigger_name BEFORE|AFTER trigger_EVENT ON table_name FOR EACH ROW trigger_STMT;
-
trigger_EVENT:指触发事件,包含DELETE、INSERT、UPDATE语句
-
table_name:触发器操作表的名字
-
FOR EACH ROW:表示任何一条记录上的操作满足触发事件都会触发该触发器
-
trigger_STMT:表示激活触发器后被执行的语句
-
-
创建包含多条执行语句的触发器
CREATE TRIGGER trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW BEGIN trigger_STMT END
多个执行语句之间需要用分号(;)隔开,这与语句结束符号冲突,此时可利用关键字DELIMITER语句,将结束符号暂时设置为“$$”,触发器创建语句结束后再将其修改回“;”。
DELIMITER $$ CREATE TRIGGER tri_name AFTER|BEFORE trigger_EVENT ON table_name FOR EACH ROW BRGIN trigger_STMT1; trigger_STMT2; ... trigger_STMTn; END $$ DELIMITER ;
查看触发器
-
1.通过SHOW TRIGGERS语句查看触发器
SHOW TRIGGERS;
-
2.通过查看系统表tirggers实现查看触发器
USE information_schema; SELECT * FROM triggers; # 或者 SELECT * FROM triggers WHERE TRIGGER_NAME='tri_name';
删除触发器
-
语法格式
DROP TRIGGER tri_name;