=pod
1-8这些都是关于数据创建,删除,修改,查询库信,选择数据库的基本操作。
=cut
1.创建数据命令
CREATE DATABASE dab_name CHARACETER SET [=] char_name
2.查询数据库
SHOW DATABASE;
3.查询数据库创建过程(信息)
SHOW CREATE DATABASE db_name;
4.查询当前所在库的位置
SELECT DATABASE();
5.选择数据库
USE db_name;
6.修改数据的字符集信息
ALTER DATABASE db_name CHARACTER char_name;
7.删除数据库
DROP DATABASE db_name;
8.查询数据的版本信息
SELECT VERSION();
=pod
Mysql数据库中存储引擎和数据类型的基本知识
=cut
ENGINES:
1.Federated: Federated MySQL storage engine(联合MySQL存储引擎)
2.CSV: CSV storage engine(CSV存储引擎)
3.MRG_MYISAM: Collection of identical MyISAM tables(相同的MyISAM表集合)
4.BLACKHOLE: /dev/null storage engine (anything you write to it disappears)(/dev/null存储引擎(任何你写它消失))
5.MyISAM: MyISAM storage engine
6.MEMORY: Hash based, stored in memory, useful for temporary tables(基于散列的,存储在内存中,用于临时表)\
7.ARCHIVE: Archive storage engine(存档存储引擎)
8.InnoDB: Supports transactions, row-level locking, and foreign keys(支持事务、行级锁和外键)
9.PERFORMANCE_SCHEMA: Performance Schema(性能模式)
1.查询SQL引擎
SHOW ENGINES; #SHOW ENGINES\G;
2.查询存储引擎
SHOW VARIABLES LIKE 'have%';
3.查询默认存储引擎
SHOW VARIABLES; #查询所的信息
SHOW VARIABLES LIKE 'storage_engine%'; #查询默认存储引擎
4.修改默认存储引擎:
vi /etc/my.cnf
default-storage-engine=INNODB #修改值,重启 mysql
#======================
存储引擎的特性:
#======================
-----------------------------------------------------------------------------
特性 MyISAM InnoDB MEMORY
-----------------------------------------------------------------------------
存储限制 有 64TB 有
-----------------------------------------------------------------------------
事物安全 不支持 支持 不支持
-----------------------------------------------------------------------------
锁机制 表锁 行锁 表锁
-----------------------------------------------------------------------------
B树索引 支持 支持 支持
-----------------------------------------------------------------------------
哈希索引 不支持 不支持 支持
-----------------------------------------------------------------------------
全文索引 支持 不支持 不支持
-----------------------------------------------------------------------------
集群索引 不支持 支持 不支持
-----------------------------------------------------------------------------
数据缓存 支持 支持
-----------------------------------------------------------------------------
索引缓存 支持 支持 支持
-----------------------------------------------------------------------------
数据可压缩 支持 不支持 不支持
-----------------------------------------------------------------------------
空间使用 低 高 N/A
-----------------------------------------------------------------------------
内存使用 低 高 中等
-----------------------------------------------------------------------------
批量插入的速度 高 低 高
-----------------------------------------------------------------------------
支持外键 不支持 支持 支持
-----------------------------------------------------------------------------
MyISAM 存储引擎:由于不引擎不支持事务,也不支持外键,所以访问速度比较快。因些
对事务完完整性没有要求并以访问为主的应用适合使用该存储引擎。
InnoDB存储引擎:由于该引擎在事务上具有优势,即支持具有提交,回滚和崩溃恢复能力
的事务安装,所以比MyISAM存储引擎占用更多的磁盘空间,因此需要进行频繁的更新,删除
同时还对事务的完整性要求比较高,需要实现并发控制,此时适合使用该存储引擎。
MEMORY存储引擎: 该存储引擎使用内存来存储数据,因此该存储引擎的访问速度快,
但安全上又没有保障。如果应用中涉及数据比较小,需要进行快速访问,则适合使用该
存储引擎。
#======================
数据类型:
#======================
1.整数类型
2.浮点数类型
3.定点数类型
4.位类型
5.日期类型
6.时间类型
1.1整数类型:
TINYINT 1 有符号-128,无符号0 有符号128,无符号255
SMALLINIT 2 有符号-32768,无符号0 有符号32767,无符号65535
MEDIUMINT 3 有符号-8388608,无符号0 有符号8388607,无符号1677215
INT和INTEGER 4 有符号-2147483648,无符号0 有符号2147483647,无符号4294967295
BIGINT 8 有符号-9223372036854775808,无符号0 有符号9223372036854775807,无符号186446744073709551615
查看系统帮助:
HELP contents;
查看系统类型:
HELP Data Types;
#======================
日期类型:
#======================
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 828:59:59
YEAR 1 1901 2155
如果要表示年月日,一般会使用DATE类型
如果要表示年月时分秒,一般会使用DATETIME类型
如果需要经常插入或者更新日期为当前系统时间,一会般使用TIMESAMP类型
如果要表示时分秒,一般会使用TIME类型
如果要表示年份,一般会使用YEAR类型
#创建表
CREATE TABLE d_test(f_date DATE,f_datetime DATETIME,f_timesamp TIMESTAMP,f_time TIME,f_year YEAR);
#查询表结构:
SHOW COLUMNS FROM d_test;
#查询当前日期时间
SELECT CURDATE(),NOW(),NOW(),time(NOW()),YEAR(NOW());
#插入数据
INSERT INTO d_test VALUES(CURDATE(),NOW(),NOW(),time(NOW()),YEAR(NOW()));
#======================
字符串类型
#======================
CHAR(M) M M为0-255之间的整数
VARCHAR(M) M M为0-65 535之间的整数
#=======================
TEXT系列字符串类型
#=======================
TINYTEXT 0-255
TEXT 0-65 535
MEDIUMTEXT 0-167 772 150
LONGTEXT 0-4 294 967 295
#==========================================
表的操作:
表的创建表,查看表,删除表和修改表
#==========================================
表:
表是包含数据库中所的数据的数据库对象,表是以行和列的格式组织的。
其中每一行代表一条唯一的记录,每一列代表记录中的一个字段。
表中的数据库对象包含列,索引和触发器。
1.列(Columns): 也称为属性列,在具体创建表时,必须指定列的名字和数据类型
2.索引(Index): 是指根据指定的数据库表列建立起来的顺序,提供了快速访问数据的途径,
且可以监督表的数据,使其索引所指定的列中的数据不重复。
3.触发器(Triggers): 是指用户定义的事务命令的集合,当对一个表中的数据插入,更新或
删除时这组命令就会自动执行,可以来确保数据的完整性和安全性。
#====================
创建表
#====================
1.创建表语法:
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
.
.
.
属性名 数据类型
);
1.1.先创建数据库company
CREATE DATABASE company CHARACTER SET utf8;
1.2.选择该库
USE company;
1.3.创建表t_dept;
CREATE TABLE IF NOT EXISTS t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
2.查询表结构:
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
3.查询表创建过程或信息
SHOW CREATE TABLE table_name;
SHOW CREATE TABLE table_name\G;
#====================
删除表
#====================
1.删除表的语法形式
DROP TABLE table_name;
DROP TABLE t_dept;
2.修改表名
ALTER TABLE old_table_name RENAME [TO] new_table_name;
ALTER TABLE t_dept RENAME TO d_dept;
3.增加字段
3.1.在表的最后一个位置增加字段
ALTER TABLE table_name ADD 属性 数据类型
ALTER TABLE t_dept ADD descri VARCHAR(30);
3.2.在表的第一个位置增加字段
ALTER TABLE table_name ADD 属性 数据类型 FIRST;
ALTER TABLE t_dept ADD d_t VARCHAR(30) FIRST;
3.3.在表的指定字段之后增加字段
ALTER TABLE table_name ADD 属性 数据类型 AFTER 属性名;
ALTER TABLE t_dept ADD d_descri VARCHAR(30) AFTER deptno;
4.修改字段
4.1.修改字段的数据类型
ALTER TABLE table_name MODIFY 属性名 数据类型;
ALTER TABLE t_dept MODIFY deptno VARCHAR(30);
4.2.修改字段的名字
ALTER TABLE table_name CHANGE 旧的属性名 新的属性名 旧的数据类型;
ALTER TABLE t_dept CHANGE dname pname VARCHAR(30);
4.3.同时修改字段的名字和属性
ALTER TABLE table_name CHANGE 旧的属性名 新的属性名 新的数据类型
ALTER TABLE t_dept CHAAGE pname d_no INT;
4.4.修改字段的顺序
ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST |AFTER 属性名2
#====================
操作的的约束
#====================
所谓的完整性是指数据的准确性和一致性,而完整性检查就是指检查数据的准确性和一致性。
MySQL提供一致机制来检查数据库表的中数据是否满足规定的条件,以保证数据表中的数据完准
确性和一致性,这就是约束机制。
完整性约束
---------------------------------------------------------------------------------------
完整性约束关键字 含义
---------------------------------------------------------------------------------------
NOT NULL 约束字段的值不能为空
---------------------------------------------------------------------------------------
DEFAULT 设置字段的默认值
---------------------------------------------------------------------------------------
UNIQUE KEY (UK) 约束字段的值是唯一
---------------------------------------------------------------------------------------
PRIMARY KEY (PK) 约束字段为表的主键,可以作为该表记录的唯一标识
---------------------------------------------------------------------------------------
AUTO_INCREMENT 约束字段的值自动增加
---------------------------------------------------------------------------------------
FOREIGN KEY (FK) 约束字段为表的外键
---------------------------------------------------------------------------------------
约束可以分为:
单列约束,即每个约束只约束一列数据;
多列约束,即每个约束可以约束多列数据;
1.设置非空约束( NOT NULL, NK)
约束某个字段的值不允许为空值。
CREATE TABLE table_name (
属性名字 数据类型 NOT NULL,
......
......
);
CREATE TABLE d_dept (deptno INT NOT NULL,dname VARCHAR(20),loc VARCHAR(30));
2. 设置字段默认值(DEFAULT)
指定字段的默认值,当在插入数据该设置了字段默认值,如果没有数据值插入将为自动设置默认值。
CREATE TABLE table_name (
属性名 数据类型 DEAULT 默认值,
... ...
);
ALTER TABLE d_dept(deptno INT NOT NULL,dname VARCHAR(30) DEFAULT 'laomeng',loc VARCHAR(40));
INSTER INTO d_dept (deptno,loc) VALUES(1,"Hell test");
SELECT * FROM d_dept;
3.设置唯一约束(UNIQUE,UK)
当数据表中的某个字段值不允许重复时,则可以使用UK约束进行设置。
CREATE TABLE table_name(
属性名 数据类型 UNIQUE,
... ...
);
CREATE TABLE p_dept (deptno INT, dname VARCHAR(30) UNIQUE NOT NULL,loc VARCHAR(30));
INSERT INTO p_dept(1,"laomeng","hello test");
INSERT INTO p_dept VALUES (2,"laomeng","hello test");
ERROR 1062 (23000): Duplicate entry 'laomeng' for key 'dname'
如果想给字段dname上设置UK约束设置一个名字,可以执行SQL语CONSTARAINT
CREATE TABLE l_dept(
deptno INT,
dname VARCHAR(20),
loc VARCHAR(30),
CONSTRAINT uk_name UNIQUE(dname)
);
4.设置主键约束(PERIMARY KEY,PK)
当想用数据库表中的某个字段来唯一标识所有记录时,则可以使用PK约束进行设置。
即PK约束在创建数据时为某些字段加上"PRIMARY KEY"约束条件,则该字段可以唯一地标识所有记录。
设置主键条件:
值是唯一,非空值。
主键分为:
单字段主键 和 多字段主键
4.1.单字段主键
CREATE TABLE table_name(
属性名 数据类型 PRIMARY KEY,
... ...
);
CREATE TABLE m_dept(deptno INT PRIMARY KEY,dname VARCHAR(30),loc VARCHAR(40)) ;
CREATE TABLE m_dept(
deptno INT PRIMARY KEY,
dname VARCHAR(30),
loc VARCHAR(40),
CONSTRAINT pk_no PRIMARY KEY(deptno)
);
5.多字段主键约束
CREATE TABLE table_name(
属性名 数据类型,
......
[CONSTRANIT 约束名] PRIMARY KEY (属性名,属性名)
);
CREATE TABLE g_dept(
deptno INT,
dname VARCHAR(30),
loc VARCHAR(30),
CONSTRAINT pk_deptno_dname PRIMARY KEY(deptno,dname)
);
6.设置字段值增加增加(AUTO_INCREMENT)
AUTO_INCREMENT是MYSQL唯一扩展的完整性约束,当数据库表中插入记录时,字段上的值会自动生成唯一
ID.该字段必须是整数类型。由于设置AUTO_INCREMENT约束的字段会生成唯一ID,所以该字段经常会设置
为PK主键。
CREATE TABLE table_name(
属性名 数据类型 AUTO_INCREMENT,
... ...
);
CREATE TABLE w_dept(deptno INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(30),loc VARCHAR(30));
7.设置外键约束(FOREIGN KEY,FK)
外键约束保证多个表(常为两个表)之间参照完整性,即构建两个表之间的参照关系。
设置外键约束的两之间具有父子关系,即子表中某个字段的值范围由父表所决定的。
例:表示一种部门和雇员之间的关系,即每个部门有多个雇员。首先应该有两个表:
部门表和员工表,然后雇员表中有一个表示部门编号的字段deptno,其依赖部门表的主键,
这样字段deptno就是雇员表的外键,通过该字段建立了部门表和雇员表建立了关系。
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
... ...
CONSTRAINT 外键约束名 FOREIGN (属性名1)
PEFERENCE 表名 (属性2)
);
CREATE TABLE q_dept(
deptno INT PRIMARY KEY,
dname VARCHAR(30),
loc VARCHAR(30)
);
CREATE TABLE q_employee(
empno INT PRIMARY KEY,
ename VARCHAR(30),
job VARCHAR(40),
MGR INT,
Hiredate DATE,
sal DOUBLE(10,2),
comm DOUBLE(10,2),
deptno INT,
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES q_dept(deptno)
);
INSERT INTO q_dept VALUES(1,"guanmei","test helo");
INSERT INTO q_dept VALUES(2,"wanger","test helo");
#====================================
索引的操作
包含创建索引,修改索引,删除索引
#====================================
一个索引包含表中按照一定顺序排序的一列或多例字段。
MySQL支持6种索引:普通索引,唯一索引,全文索引,单列索引,多列索引和空间索引。
备注:创建索引可以提高查询速度,但过多创建索引则会占据太多的磁盘空间。
创建索引的情景:
1.经常被查询的字段,即在WHERE 子句中出现的字段。
2.在分组的字段,即在GROUP BY 子句中出现在的字段。
3.在依赖关系的子表和父表之间的联合查询,即主键或外键字段。
4.设置唯一完整性约束的字段
以下情况下,不适合创建索引:
1.在查询中很少被使用的字段
2.拥有许多重复的字段
1.创建普通索引和查看普通索引
所谓的普通索引,就是创建索引时,不附加任何条件(唯一,非空间限制)。该类型的索引
创建在任何数据类型的字段上。
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
... ...
属性名 数据类型,
INDEX|KEY [索引名](属性名1 [(长度)] [ASC|DESC])
);
备注:MYSQL所支持的存储引擎对每个表至少支持16个索引,总索引长度至少256个字节。
CREATE TABLE t_dept(
deptno INT,
dname VARCHAR(30),
loc VARCHAR(30),
INDEX index_deptno(deptno)
);
1.2.查询表和索引
SHOW CREATE TABLE t_dept;
EXPLAIN SELECT * FROM t_dept WHERE deptno=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_dept
type: ref
possible_keys: index_deptno
key: index_deptno
key_len: 5
ref: const
rows: 1
Extra: NULL
1 row in set (0.03 sec)
1.3.在已经存在的表上创建普通索引
CREATE INDEX 索引名
ON 表名 (属性名 [长度] [ASC|DESC]);
CREATE TABLE t_name(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30),age INT);
CREATE INDEX index_id ON t_name(id);
1.4.通过SQL语句ALTER TABLE 创建普通索引
ALTER TABLE table_name
ADD INDEX|KEY 索引名 (属性名 [长度] [ASC|DESC]);
CREATE TABLE l_test(id INT,name VARCHAR(30),age INT);
ALTER TABLE l_test ADD INDEX index_id(id);
2.创建和查看唯一索引
所谓唯一索引,就是在创建索引时,限制索引的值必须是唯一的。
在MYSQL中,根据创建索引方式,可以分为自动索引和手动索引两种。
自动索引:是指在数据库表里设置完整性约束时,该表会被系统自动创建索引。
手动索引:是指手动在表上创建索引.
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
... ...
属性名 数据类型,
UNIQUE INDEX|KEY [索引名](属性名1 [(长度)] [ASC|DESC])
);
CREATE TABLE m_dept(
deptno INT UNIQUE ,
dname VARCHAR(30),
loc VARCHAR(30),
UNIQUE index_deptno(deptno)
);
SHOW CREATE TABLE m_dept;
EXPLAIN SELECT * FROM m_dept WHERE deptno=10\G;
2.1在已经存在的表上创建唯一索引
CREATE UNIQUE INDEX 索引名
ON 表名(属性名 [长度] [ASC|DESC]);
CREATE TABLE w_dept(
id INT UNIQUE KEY AUTO_INCREMENT,
name VARCHAR(30),
age INT
);
2.2.通过SQL语句ALTER TABLE 创建唯一索引
ALTER TABLE table_name
ADD UNIQUE INDEX|KEY 索引名(属性名 [长度] [ASC|DESC]);
CREATE TABLE h_dept(id INT,name VARCHAR(30),age INT);
ALTER TABLE h_dept ADD UNIQUE INDEX index_id(id);
3.创建和查看全文索引
全文索引主要关键在数据类型为CHAR,VARCHAR 和TEXT的字段上,以便能更快速地查询数据量
较大的字符串类型字段。
备注:MYSQL从5.23.23版本可以支持全文索引,只能在存储引擎为MyISAM的数据库上创建全文
索引.在默认情况下,全文索引的搜索执行方式不区分大小小写,如果全文搜索所关联的字段为
二进制数据类型,则以区分大小写的搜索方式执行。
3.1.创建表时创建全文索引
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
... ...
FULLTEXT INDEX|KEY [索引名](属性名1 [长度] [ASC|DESC] )
);
CREATE TABLE t_dept(
id INT,
name VARCHAR(30),
loc VARCHAR(40),
FULLTEXT INDEX index_loc(loc)
)ENGINE=MyISAM;
3.2.在已存在的表上创建全文索引
CREATE FULLTEXT INDEX 索引名
ON 表名(属性名 [长度] [ASC|DESC]);
CREATE TABLE l_dept(id INT,name VARCHAR(30),loc VARCHAR(40))ENGINE=MyISAM;
CREATE FULLTEXT INDEX index_loc ON l_dept(loc);
3.3通过ALTER TABLE 创建全文索引
ALTER TABLE table_name
ADD FULLTEXT INDEX|KEY 索引名 (属性名 [长度] [ASC|DESC]);
CREATE TABEL m_pt(id INT, name VARCHAR(30),loc VARCHAR(40))ENGINE=MyISAM;
ALTER TABLE m_pt ADD FULLTEXT INDEX index_loc(loc);
4.创建和查看多列索引
所谓多列索引,是指在创建索引时,所关联的字段不一个字段,而是多个字段。
4.1.创建表时创建多列索引
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
... ...
属性名 数据类型,
INDEX|KEY [索引名](属性名1 [长度] [ASC|DESC],
... ...
属性名 [长度] [ASC|DESC],)
);
CREATE TABLE h_pt(
id INT,
name VARCHAR(30),
loc VARCHAR(40),
INDEX index_name_loc(name,loc)
);
4.2在已存在的表上创建多列索引
CRTEATE INDEX 索引名
ON 表名(属性名1 [长度] [ASC|DESC],
... ...
属性名N [长度] [ASC|DESC]
)
CREATE TABLE d_pt(
id INT,
name VARCHAR(30),
loc VARCHAR(40),
)ENGINE=MyISAM;
CREATE INDEX index_name_loc
ON d_pt(name,loc);
4.3通过ALTER TABLE创建多列索引
ALTER TABLE table_name
ADD INDEX|KEY 索引名(属性1 [长度] [ASC|DESC],... ....属性N [长度] [ASC|DESC]);
5.删除索引的语法形式
DROP INDEX index_name
ON table_name;
#=================================================================
视图的操作
视图的基本操作:创建,查看,更新和删除
#=================================================================
视图:本质上是一张虚拟表,其内容与真实的表相似,包含一系列带有名称的列和行数据。
视图特点:
1.视图的列可以来不同的表,是表抽象和在逻辑意义上建立的新关系。
2.视图是由基本表(实表)产生的表(虚表)。
3.视图的建立和删除不影响基本表。
4.对视图的内容更新,删除,修改直接影响基本表。
5.当视图来自多个基本表,不允许添加和删除数据。
1.创建视图的语法:
CREATE VIEW view_name
AS 查询语句;
CREATE VIEW view_selectproduct
AS SELECT id,name FROM t_product;
SHOW CREATE VIEW view_name;
SHOW CREATE VIEW view_selectproduct;
1.1.创建常量视图:
CREATE VIEW SELECT 3.14159246;
1.2.封装聚合函数(SUM,MIN,MAX,COUNT)
CREATE VIEW view_count
AS SELECT COUNT(name) FROM t_student;
1.3.封装排序功能(ORDER BY)
CREATE VIEW view_order_by
AS SELECT name FROM s_student ORDER BY id DESC;
1.4.封装内连接查询语句视图:
CREATE VIEW view_cou
AS SELECT s.name FROM t_student AS s, t_group AS g
WHERE s.group_id=g.id AND g.id=2;
1.5.封装表外连接(LEFT JOIN AND RIGHT JOIN)
CREATE VIEW view_left_join
AS SELECT s.name FROM t_student AS s LEFT JOIN t_group AS g
ON s.group_id= g.id WHRER g.id=2;
1.6.封装子查询相关语句
CREATE VIEW view_check
AS SELECT s.name FROM s_student AS s WHERE s.group_id
IN (SELECT id FROM t_group);
1.7.封装实现记录联合(UNION AND UNION ALL)
CREATE VIEW view_union_all
AS SELECT id,name FROM t_student
UNION ALL
SELECT id,name FROM t_grop;
2.查看视图:
SHOW TABLES;
2.1.SHOW TABLE STATUS 语句查询视图详细信息
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern%'];
2.2.SHOW CREATE VIEW 语句查询视图的定义操作
SHOW CREATE VIEW view_name;
3.删除视图瓣语法:
DROP VIEW view_name [,view_namen]....
4.修改视图:
4.1.CREATE OR REPLACE VIEW 语句修改视图:
CREATE OR RELPACE view_name
AS 查询语句;
CREATE OR REPLACE VIEW view_selectproduct
AS SELECT name FROM t_student;
4.2.ALTER 语句修改视图:
ALTER VIEW view_name
AS 查询语句;
ALTER VIEW view_selectproduct
AS SELECT name FROM t_product;
#======================================================
触发器
基本操作:创建,查看和删除
#======================================================
1.创建一条执行语句的触发器
CREATE TRIGGER trigger_name
TRIGGER_TIME
TRIGGER_EVENT
ON table_name FRO EACH ROW TIRGGER_STMT;
trigger_name:标识触发器名称,用户自行指定;
备注:在具体创建触发器时,触发器标识符不能与已经存在的重复。
TRIGGER_TIME 标识触发时机,取值为 BEFORE 或 AFTER,BFORE是指定触发器事件之前执行触发语句,
ALTER是指定在触发事件之后执行触发语句。
TRIGGER_EVENT:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
table_name:标识建立触发器的表名,即在哪张表上建立触发器;
FOR EACH ROW 参数表示任何一条记录上操作满足触发事件都会触发该触发器。
TRIGGER_STMT:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。
由此可见,可以创建建立6触发器,即: BEFORE INSERT ,BEFORE UPDATE, BEFORE DELETE
ALTER INSERT, ALTER UPDATE,ALTER DELETE.
TRIGGER_EVENT详解:
MySQL除了对INSERT,UPDATE,DELETE基本操作定义外,还定义了LOAD DATA和REPLACE语句,
这两种语句也能引起上述6种类型触发器的触发。
LOAD DATA 语句用于将一个文件装入一个数据表中,相当与一系列的INSERT操作。
REPLACE语句一般来说和INSERT语句很像,只是在表中有primary key or unique索引时,如果
插入数据和原来的primary key or unique索引一致时,会先删除原来的数据,然后增加一条新
数据,也就是说,一条REPLACE语句等价于一条INSERT语句,有时候等价于一条DELETE语句加上一条INSERT
INSERT触发器:插入某一行时激活触发器,可能能过INSERT,LOAD DATA,REPLACE语句触发;
UPDATE型触发器:更改某一行时激活触发器,可能通过UPDATE语句触发;
DELETE型触发器:删除某一行时激活触发器,可能通过DELETE,REPLACE语句触发;
CREATE TRIGGER tri_diarytime
BEFORE INSERT
ON g_dept FOR EACH ROW
INSERT INTO g_diary VALUES(NULL,'g_dept',NOW());
1.2创建多条执行语句的触发器
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE|ALTER TRIGGER_EVENT
ON table_name FOR EACH ROW
BEGIN
TRIIGER_STMT
END
$$
DELIMITER;
BEGIN...END详解:
在MySql中,BEGIN...END语法为:
BEGIN
[statement_list]
END
其中,statement_list代表一个或多个语句的列表,列表的内的每条语句都必须用分号(;)
来结尾。而在MYSQL中,分号是语句结束标识符,遇到分号表示该段语句的结束,MYSQL可以
执行了。因此解释器遇到statement_list中的分号就开始执行,然后就会报错,因为没有找到
和BEGIN匹配到的END. 这时解释器就会用到DELIMITER命令(DELIMITER是定界符,分隔符的意思),
它是一条命令,不需要语句结束标识,语法为:
DELIMITER new_delemiter
new_delemiter可以设为1个或多个长度的符号,默认是分叼(;),我们可以把它修改为其他符号,
如$:
DELIMITER $
一个完整的创建触发器示例
假设系统中有两个表:
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新,代码如下:
复制代码 代码如下:
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;
变量详解
MySQL 中使用 DECLARE 来定义一局部变量,该变量只能在 BEGIN … END 复合语句中使用,并且应该定义在复合语句的开头,
即其它语句之前,语法如下:
DECLARE var_name[,...] type [DEFAULT value]
其中:
var_name 为变量名称,同 SQL 语句一样,变量名不区分大小写;type 为 MySQL 支持的任何数据类型;可以同时定义多个同类型的变量,用逗号隔开;变量初始值为 NULL,如果需要,可以使用 DEFAULT 子句提供默认值,值可以被指定为一个表达式。
对变量赋值采用 SET 语句,语法为:
SET var_name = expr [,var_name = expr] ...
NEW 与 OLD 详解
上述示例中使用了NEW关键字,和 MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示
触发器的所在表中,触发了触发器的那一行数据。
具体地:
在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
使用方法: NEW.columnName (columnName 为相应数据表某一列名)
另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。
查看触发器
和查看数据库(show databases;)查看表格(show tables;)一样,查看触发器的语法如下:
SHOW TRIGGERS [FROM schema_name];
其中,schema_name 即 Schema 的名称,在 MySQL 中 Schema 和 Database 是一样的,也就是说,可以指定数据库名,这样就
不必先“USE database_name;”了。
删除触发器
和删除数据库、删除表格一样,删除触发器的语法如下:
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
触发器的执行顺序
我们建立的数据库一般都是 InnoDB 数据库,其上建立的表是事务性表,也就是事务安全的。这时,若SQL语句或触发器执行失败,MySQL 会回滚事务,有:
①如果 BEFORE 触发器执行失败,SQL 无法正确执行。
②SQL 执行失败时,AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败,SQL 会回滚。
===========================================
数据的操作:
插入数据记录;
更新数据记录;
删除数据记录;
============================================
1.插入数据记录
1.1插入完整的数据记录
INSERT INTO table_name(field1,field2,field3,.....fieldn)
VALUES(value1,value2,$vlaue3... ...valuen);
#插入指定字段的值
CREATE TABLE l_test(deptno INT,dname VARCHAR(30),loc VARCHAR(40));
INSERT INTO l_test(deptno,dname,loc) VALUES(1,"laomeng","test");
#插入数据所有字段的值
INSERT INTO table_name VALUES(value1,value2,value3,... ...valuen);
INSERT INTO l_test VALUES(1,"laomeng","test");
2.插入多条记录
2.1插入多条完整的记录
INSERT INTO table_name(field1,field2,field3,......fieldn)
VALUES(value11,value21,value31,......valuen1),
(value12,value22,value32,......valuen2),
(value13,value23,value33,......valuen3),
... ...
(value1m,vlaue2m,value3m,......valuenm);
INSERT INTO table_name
VALUES(value11,value21,value31,......valuen1),
(value12,value22,value32,......valuen2),
(value13,value23,value33,......valuen3),
... ...
(value1m,vlaue2m,value3m,......valuenm);
CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
CREATE TABLE d_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
INSERT INTO t_dept VALUES
(1,"cj1","sx1"),
(2,"cj2","sx2"),
(3,"cj3","sx3"),
(4,"cj4","sx4"),
(5,"cj5","sx5");
2.2.插入多条部分字段值记录
CREATE TABLE l_dept(id INT,name VARCHAR(30),age INT,sex CHAR(1),remark VARCHAR(140));
INSERT INTO l_dept VALUES
(1,"lm1",23,"m"),
(2,"lm2",25,"f"),
(3,"lm3",30,"m"),
(4,"lm4",27,"f");
2.3.插入查询结果
INSERT INTO table_name(field1,field2,field3,.....fieldn)
SELECT (field1,field2,field3,......fieldn)
FROM table_name2
WHERE ....;
CREATE TABLE m_dept(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30),loc VARCHAR(40));
SHOW COLUMNS FROM m_dept;
CREATE TABLE m_loader(id INT,name VARCHAR(30),dname VARCHAR(30),loc VARCHAR(40));
SHOW COLUMNS FROM m_loader;
INSERT INTO m_loader VALUES
(1,'cj1','dept1','sx1'),
(2,'cj2','dept2','sx2'),
(3,'cj3','dept3','sx3'),
(4,'cj4','dept4','sx4'),
(5,'cj5','dept5','sx5');
INSERT INTO m_dept(name,loc) SELECT name,loc FROM m_loader;
3.更新数据记录
.更新特定数据记录
.更新所有数据记录
3.1.更新特定数据记录
UPDATE table_name
SET field1=value1,
field2=value2,
field3=value3,
fieldn=valuen,
WHERE CONDITION;
CONDITION 参数指定更新满足条件的特定数据记录
UPDATE m_dept SET loc='test' WHERE name='cj1';
3.2.更新所有数据记录
UPDATE table_name
SET field1=value1,
field2=value2,
field3=value3,
fieldn=valuen,
WHERE CONDITION;
UPDATE m_dept SET loc='Hello' WHERE id<4;
4.删除指定的数据记录
DELETE FROM table_name WHERE CONDITION;
DELETE FROM m_dept WHERE id=5;
DELETE FROM m_dept; #清空所有字段
=====================================================================================
单表数据记录查询
通过学习后可以掌握:
1.简单的数据记录查询
2.条件数据记录查询
3.排序数据记录查询
4.限制数据记录查询
5.统计函数和分组查询
=====================================================================================
设计库(company)
设计表(t_employee,雇员表)
CREATE DATABASE company CHARACTER SET utf8;
use company;
CREATE TABLE t_employee
(empno INT,
ename VARCHAR(20),
job VARCHAR(40),
MGR INT,
Hiredate DATE,
sal DOUBLE(10,2),
comm DOUBLE(10,2),
deptno INT);
INSERT INTO t_employee VALUES
(7369,"SMITH","CLERK",7902,"1981-03-12",800,NULL,20),
(7349,"ALLEN","SALESMAN",7698,"1982-03-12",1600,300.00,30),
(7521,"WARD","SALESMAN",7698,"1983-03-12",1250,500.00,30),
(7566,"JONES","MANAGER",7839,"1981-03-12",2975,NULL,20),
(7654,"MARTIN","SALESMAN",7698,"1981-03-12",1250,1400.00,30),
(7698,"BLAKE","MANAGER",7839,"1981-03-12",2850,NULL,30),
(7782,"CLARK","MANAGER",7839,"1985-03-12",2450,NULL,10),
(7788,"SCTOOT","ANALYST",7566,"1981-03-12",3000,NULL,20),
(7839,"KING","PERSIDENT",NULL,"1981-03-12",5000,NULL,10),
(7844,"TURNER","SALESMAN",7689,"1989-03-12",1500,0.00,30),
(7876,"ADAMS","CLERK",7788,"1988-03-12",1100,NULL,20),
(7900,"JAMES","CLERK",7689,"1997-03-12",950,NULL,30),
(7902,"FORD","ANALYST",7566,"0000-00-00",3000,NULL,20),
(7934,"MILLER","CLERK",7782,"1981-03-12",1300,NULL,10);
1.字段去重查询----DISTINCT
SELECT DISTINCT field1,filed2,..fieldn FROM table_name;
SELECT DISTINCT job FROM t_employee;
2.数学四则运算符查询数据
运算符 描述
+ 加法
- 减法
* 乘法
/(DIV) 除法
%(MOD) 求余
SELECT ename ,sal*12 FROM t_employee;
3.设置显示格式查询
在MYSQL中提供函数CONCAT()来连接字符串,从而实现设置显示数据的格式
CONCAT(str1,str2,...)
SELECT CONCAT(ename,'雇员年薪:',sal*12) AS yearsalary FROM t_employee;
4.多条数据记录查询
SELECT field1,field2,field3....fieldn
FROM table_name
WHERE CONDITION;
可以带以条件:
4.1.BETWEEN AND 在两者之间,这个范围是一个数字范围
4.2.IS NULL 字段为空
4.3.IN 在集合里
4.4.LIKE 模糊查询
5.MYSQL支持的比较运算符:
运算符 描述
> 大于
< 小于
= 等于
!=(<>) 不等于
>= 大于等于
<= 小于等于
逻辑运算符:
AND(&&) 逻辑与
OR(||) 逻辑或
XOR 逻辑异或
NOT(!) 逻辑非
#查询ename,job,sal三个信息并工作种类是CLERK和工资大于800的
SELECT ename,job,sal FROM t_employee WHERE job='CLERK'&& sal>800;
#范围查询
SELECT feild1,field2,.....fieldn
FROM table_name
WHERE field1 BETWEEN VALUE1 AND VALUE2;
#查询薪资范围在1000到2000之间的员工
SELECT ename FROM t_employee WHERE sal BETWEEN 1000 AND 2000;
SELECT field1,field2,....fieldn
FROM table_name
WHERE feild1 IS NULL;
#查询所有员工中不领取奖金的员工有哪些
SELECT ename FROM t_employee WHERE comm IS NULL;
#查询所有员工中哪引起员工有奖金
SELECT ename FROM t_employee WHERE comm IS NOT NULL;
#集合查询
SELECT field1,field2,.....fieldn
FROM table_name
WHERE field IN(value1,value2,value3,....valuen);
#查询"SMITH","WARD","CLARK"三个员工的所有信息
SELECT * FROM t_employee WHERE ename IN("SMITH","WARD","CLARK");
#查询所有员工中工号是7521或7782或7566或7788的员工姓名
SELECT ename FROM t_employee WHERE empno=7521 OR empno=7782 OR empno=7566 OR empno=7788;
SELECT ename FROM t_employee WHERE empno IN(7521,7782,7566,7788);
#不在集合范围的查询
SELECT ename FROM t_employee WHERE empno NOT IN(7521,7782,7566,7788);
#带LIKE的模糊查询
SELECT field1,field2,.......fieldn
FROM table_name
WHERE field LIKE value;
SELECT field1,field2,.......fieldn
FROM table_name
WHERE field [NOT] LIKE value;
LIKE支持的通配符如下:
"_": 该通配符匹配单个字符
"%": 该通配符值可以匹配任意长度的字符串.
#查询所有员工的中以A开头的员工姓名有哪几个
SELECT ename FROM t_employee WHERE ename LIKE 'A%';
'%%':匹配所有
'_VALUE':匹配第二传下是VALUE
'%VALUE%':匹配包含VALUE
后续中。。。。。。。
mysql基础学习笔记
最新推荐文章于 2024-08-17 13:24:35 发布