MySQL入门很简单——读书笔记
来源：互联网 发布：淘宝电子邮箱注册 编辑：程序博客网 时间：2024/05/21 17:12
第五章 操作数据库
3、数据库的存储引擎
4、如何选择存储引擎
5.1、创建数据库CREATE DATABASE 数据库名; eg:CREATE DATABASE example;
查看现有数据库SHOW DATABASES;
5.2、删除数据库 DROP DATABASE 数据库名;
eg:⑴建立数据库 mybook; CREATE DATABASE mybook;
⑵查看现有数据库 SHOW DATABASES;
⑶删除数据库 DROP DATABASE mybook;
⑷查看现在的数据库，判断是否成功删除 SHOW DATABASES;
5.3、数据库的存储引擎 SHOW ENGINES;默认为:InnoDB
在命令列界面更好看效果，所有YES全是支持mysql的存储引擎
mysql> show engines;
9 rows in set
查看数据库的当前引擎 只能在dos界面下使用 SHOW VARIABLES LIKE 'storage_enigine';
4、如何选择存储引擎
InnoDB优点:⑴支持自动增长列auto_increment，自动增长列的值不为空，且值必须唯一。MySQL中规定自增列为主键。在插入值时，如果自动增长列不输入值，则插入的值为自动增长后的值;如果输入的值为0或NULL，则插入的值也为自动增长后的值;如果插入某个确定的值，且该值在前面没有出现过，则可以直接插入。
⑵支持外键(FOREIGN KEY)，当删除、更新父表的某条信息时，子表也必须有相应的改变。
⑶InnoDB存储引擎创建的表结构存储在.frm文件中
数据存储在innodb_data_home_dir
索引存储在innodb_data_file_path定义的表空间中。
InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。对于频繁的更改、删除操作，可以实现数据的提交(COMMIT)和回滚(ROLLBACK)。缺点是读写效率稍差，占用的数据空间相对比较大。
MyISAM特性:
⑴MyISAM存储引擎的表存储成3个文件，文件名与表相同，扩展名为frm(存储表结构),MYD是MYData的缩写，故存储数据;扩展名MYI，是MYIndex的缩写,故存储表的索引。
⑵MyISAM存储引擎的表支持3种不同的存储格式，————静态型、动态型、压缩型。静态型是MyISAM存储引擎默认的存储方式，其字段长度固定。动态型是包含变长字段，记录的长度不固定。压缩型需要使用myisampack工具创建，占用的磁盘空间小。
MyISAM存储引擎的优势在于占用空间小，插入、读取也叫读写数据处理速度快。缺点是不支持事务的完整性、并发性。
Memory存储引擎很少用，memory表所有数据存储在内存上，内存出异常会影响数据的完整性。该文件的文件名与表名相同，frm存储表结构。其数据文件存储在内存中，读写速度非常快，有利于对数据的快速处理，提高表的处理效率。服务器需要有足够的内存来维持MEMORY存储引擎的表的使用，如果不需要则释放内存，删除不需要的表，会影响数据完整性，所以基本不用。使用Hash索引，比B型速度快，想要使用B型索引，在创建时使用。
5、修改默认存储引擎
MySQL安装目录下，my.ini的配置文件中，mysqld部分存在"default-storage-engine=INNODB"，修改INNODB为需要的存储引擎，重启服务器后会生效。再执行SHOW VARIABLES LIKE 'storage_engine'，查看默认引擎修改是否成功。
第6章 创建、修改和删除表
6.1创建表6.1.1创建表的语法形式
CREATE TABLE 表名(属性名 数据类型[完整性约束条件],...);
CREATE TABLE example0(
id INT,
name VARCHAR(20),
sex boolean
);
完整性约束条件：
完整性约束条件:
①PRIMARY KEY : 该字段能为以表示该表中的每条信息。如同身份证和人的关系。帮助MySQL以最快的速度查找到表中的某一条信息。
eg:
CREATE TABLE example1(
stu_id INT PRIMARY KEY,
stu_name VARCHAR(20),
stu_sex boolean
);
多字段主键：
eg:
CREATE TABLE example2(
stu_id INT,
course_id INT,
grade FLOAT,
PRIMARY KEY(stu_id,course_id)
);
②foreign KEY :
example2表的stu_id,course_id是主键，example3表的stu_id,course_id是外键
eg:
CREATE TABLE example3(
id INT PRIMARY KEY,
stu_id INT,
course_id INT,
CONSTRAINT c_fk FOREIGN KEY(stu_id,course_id) REFERENCES example2(stu_id,course_id)
);
③not NULL :
eg:
CREATE TABLE example4(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
stu_id INT,
CONSTRAINT d_fk FOREIGN KEY(stu_id) REFERENCES example1(stu_id)
);
④unique :
eg:
CREATE TABLE example5(
id INT PRIMARY KEY,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
⑤auto_increment :在没有设置初值的情况下，自增字段从1开始增加。
插入该纪录时，不设置自增字段的值、自增字段处插入的值为 NULL 或者 0 时，该字段的值在上亿条记录的基础上增加1。
eg:
CREATE TABLE example6(
id INT PRIMARY KEY auto_increment,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
⑥default :
CREATE TABLE example7(
id INT PRIMARY KEY auto_increment,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL,
English VARCHAR(20) DEFAULT 'zero',
Math FLOAT DEFAULT 0,
Computer FLOAT DEFAULT 0
);
6.2查看表结构
6.2.1查看标的基本结构语句 DESCRIBE 表名; eg: DESCRIBE example1;
缩写为 DESC 表名; eg:DESC example1;
6.2.2查看表的详细结构语句 SHOW CREATE TABLE 表名;
eg:工具——命令列界面下
mysql> show create table example1;
查询结果:
| example1 | CREATE TABLE `example1` (
`stu_id` int(11) NOT NULL,
`stu_name` varchar(20) DEFAULT NULL,
`stu_sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
6.3修改表
6.3.1修改表名
语句 ALTER TABLE 旧表名 RENAME [TO] 新表名;
eg:
①ALTER TABLE example0 RENAME user;
②查看表结构 DESC example0; 结果[Err] 1146 example0表不存在
③查看修改后的表结构 desc user; 结果为刚才example1的结果
6.3.2修改字段的数据类型
语句 ALTER TABLE 表名 MODIFY 属性名 数据类型;
eg:
①修改表的字段的数据类型 ALTER TABLE user MODIFY name VARCHAR(30);
②查看表结构 DESC user;
6.3.3修改表的字段名
语句 ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
6.3.3.1只改字段名
eg:
①查看表结构 DESC example1;
②修改表的字段名 ALTER TABLE example1 CHANGE stu_name name VARCHAR(20);
③查看表结构 DESC example1; 显示修改成功
6.3.3.2修改字段名和字段数据类型
①查看表结构 DESC example1;
②修改表的字段名和数据类型 ALTER TABLE example1 CHANGE stu_sex sex INT(2);
③查看表结构 DESC example1; 显示修改成功
6.3.4增加字段，新增字段默认为表的最后1个字段
语句 ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST|AFTER| 属性名2];
6.3.4.1增加无完整性约束条件的字段
语句 ① ALTER TABLE user ADD phone VARCHAR(20);② DESC user;显示成功添加字段
6.3.4.2增加有完整性约束条件的字段，新增字段默认为表的最后1个字段
eg: ① ALTER TABLE user ADD age INT(4) NOT NULL;② DESC user;显示成功添加字段 NULL--对应NO
6.3.4.3在表的第一个位置增加字段
语句 eg：
① ALTER TABLE user ADD num INT(8) PRIMARY KEY FIRST;
② DESC user; 显示成功添加第一个属性为主键
6.3.4.4在表的指定位置之后增加字段
语句 eg:
① ALTER TABLE user ADD address VARCHAR(30) NOT NULL AFTER phone;
② DESC user; 显示成功在phone后添加address字段
6.3.5删除字段 语句 ALTER TABLE 表名 DROP 属性名;
eg:
① DESC user;
② ALTER TABLE user DROP id;
③ DESC user;显示成功删除id字段
6.3.6修改字段的排列位置 语句 ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2;
6.3.6.1将字段修改到第一个位置
eg: ALTER TABLE user MODIFY name VARCHAR(30) FIRST;
① DESC user;
② ALTER TABLE user MODIFY name VARCHAR(30) FIRST;
③ DESC user;显示name字段成功改到第一个位置
6.3.6.2将字段修改到指定位置
eg:ALTER TABLE user MODIFY sex TINYINT(1) AFTER age;
① DESC user;
② ALTER TABLE user MODIFY sex TINYINT(1) AFTER age;
③ DESC user;成功显示sex字段在age之后
6.3.7修改表的存储引擎 语句 ALTER TABLE 表名 ENGINE=存储引擎名;
eg: ① ALTER TABLE user ENGINE=MyISAM;
② 查看表的详细结构 命令列界面 SHOW CREATE TABLE user;
| user | CREATE TABLE `user` (
`name` varchar(30) DEFAULT NULL,
`num` int(8) NOT NULL,
`phone` varchar(20) DEFAULT NULL,
`address` varchar(30) NOT NULL,
`age` int(4) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
6.3.8删除表的外键结构 语句 ALTER TABLE user DROP FOREIGN KEY 外键名;
eg:①查看表的详细结构 在工具--命令列界面下SHOW CREATE TABLE example3;
mysql> show create table example3;
| example3 | CREATE TABLE `example3` (
`id` int(11) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_fk` (`stu_id`,`course_id`),
CONSTRAINT `c_fk` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
②删除外键 ALTER TABLE example3 DROP FOREIGN KEY c_fk;
③查看表的详细结构 在工具--命令列界面下SHOW CREATE TABLE example3;
mysql> show create table example3;
| example3 | CREATE TABLE `example3` (
`id` int(11) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_fk` (`stu_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
example3中不存在外键了， FOREIGN KEY 变成了 KEY, 操作成功。
6.4删除表 语句 DROP TABLE 表名;
eg: ① DROP TABLE example5;
② DESC example5; example5 表不存在
6.4.2 删除被其他表关联的父表
DROP TABLE example1; 结果显示[Err] 1217 - Cannot delete or update a parent row: a foreign key constraint fails
删除失败，是因为有外键依赖于该表
先删除外键约束，再删除此父表表
①查看子表的详细结构，再删除父表
mysql> show create table example4;
| example4 | CREATE TABLE `example4` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `d_fk` (`stu_id`),
CONSTRAINT `d_fk` FOREIGN KEY (`stu_id`) REFERENCES `example1` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
②删除子表外键
ALTER TABLE example4 DROP FOREIGN KEY d_fk;
③查看子表的详细结构
mysql> show create table example4;
| example4 | CREATE TABLE `example4` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `d_fk` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | d_fk从 FOREIGN KEY 变为了 KEY,example4 没有了依赖于 example1 的外键约束，此时可以删除表example1
④删除主表
DROP TABLE example1;
⑤查看 example1表结构
DESC example1; 结果显示不存在
CREATE TABLE gradeInfo(
id INT(10) PRIMARY KEY NOT null UNIQUE auto_increment,#编号
s_num INT(10) NOT NULL,#学号
course VARCHAR(20) NOT NULL,#课程名
score VARCHAR(4)#成绩
);
ALTER TABLE gradeInfo ENGINE=MyISAM;
CREATE TABLE teacherInfo(
id INT(4) PRIMARY KEY NOT NULL UNIQUE auto_increment,
t_id INT(10) NOT NULL UNIQUE,
name VARCHAR(30) NOT NULL,
birthday datetime,
sex VARCHAR(4) NOT NULL,
wages FLOAT
) ENGINE=MyISAM;
create TABLE worker(
id INT(4) NOT NULL UNIQUE PRIMARY KEY auto_increment,#编号
num INT(10) NOT NULL UNIQUE,#员工号
d_id INT(4),#部门号
name VARCHAR(20) NOT NULL,#姓名
sex VARCHAR(4) NOT NULL,#性别
birthday date,#出生日期
address VARCHAR(50)#家庭住址
);
create TABLE annimalInfo(
id INT(4) PRIMARY KEY NOT NULL UNIQUE auto_increment,#编号
name VARCHAR(30) NOT NULL,#名称
category VARCHAR(8) NOT NULL,#种类
behavior VARCHAR(50),#习性
fur VARCHAR(10)
) ENGINE=MyISAM;
CREATE TABLE car(
id INT(4) PRIMARY KEY NOT NULL UNIQUE auto_increment,#编号
num INT(10) NOT NULL,#类型号
name VARCHAR(20) NOT NULL,#名称
company VARCHAR(50),#生产商名
address VARCHAR(50)#生产地址
);
第七章 索引
索引的作用：①由数据库表中一列或多列组合而成，对数据库表中以劣或多列的值进行排序的一种结构。其作用是提高对表中数据的查询速度。②通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。
③不同的存储引擎定义了每个表的最大索引数和最大索引长度。
所有的存储引擎对每个表至少支持16个索引，总索引长度至少为256个字节。有些存储引擎支持更多的索引数和更大的索引长度。
索引有两种存储类型，包括B型树（BTREE）和哈希（HASH）索引。
INNODB 和 MyISAM 存储引擎支持 BTREE 索引，MEMORY 存储引擎支持HASH索引和BTREE索引，默认为HASH索引
索引能够提高查询速度：①对于有依赖关系的子父表之间的联合查询 ②使用分组和排序子句进行数据查询
索引的缺点:①建立和维护索引需要消耗时间，耗费时间的数量随着数据量的增加而增加
②索引需要占用物理空间，每一个索引要占一定的物理空间
③增加、删除、修改数据时，要动态维护索引，造成数据的维护速度降低了。
最好的办法是，先删除索引，再插入数据。插入完成后，再创建索引。
7.1.2索引的分类
①普通索引：在创建索引时，不附加任何限制条件。可以建立在任何数据类型，其值是够唯一和非空有字段本身的完整性约束条件决定。
②唯一性索引：使用 UNIQUE 参数可以在unique完整性约束的字段上设置索引为唯一性索引。可以迅速确定找到某头条记录、
③全文索引：使用 FULLTEXT 参数在 CHAR\VARCHAR\TEXT 类型的字段上，查询数量较大的字符串类型的字段时，使用全文索引可以提高查询的速度。
④单列索引：在表的某一个字段上创建索引。可以是普通索引，唯一性索引，全文索引。
⑤多列索引：在表的多个字段上创建一个索引。注意：只有查询条件中使用了这些字段中第一个字段是，索引才会被使用。
⑥空间索引：初学者很少用，使用 SPATIAL 参数只能在空间数类型上建立空间索引，可以提高系统获取空间类型数据的效率。MySQL中的空间数据类型包括 GEOMETRY/POINT/LINESTRING/POLYGON 。目前只有MyISAM存储引擎支持空间索引，空间索引的字段不能为空值。
7.1.3索引设计的原则
①选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
②为经常需要排序、分组和联合操作的字段建立索引：经常需要 ORDER BY\GROUP BY\DISTINCT\UNION 等操作的字段，排序操作会浪费很多时间，为其建立索引，可以有效避免排序操作。
③为常作为查询条件的字段建立索引
④建立索引限制的数目：索引的数目并不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大。修改表时，对索引的重构和更行很麻烦。越多的索引，会是更新表变的很浪费时间。
⑤尽量使用数据量少的索引：索引的值很长，会影响查询速度。eg：对 CHAR(100) 类型的字段检索需要的时间肯定比 CHAR(10)类型的字段需要的时间要多。
⑥尽量使用前缀来索引：如果索引字段的值很长，最好使用值得前缀来索引。eg：Text 和 BLOB 类型的字段，进行前面若干字段的检索，提高检索速度。
⑦删除不再使用或很少使用的索引：是的存储空间得到释放，也会减少索引对数据封信和改变表结构的影响。
7.2创建索引
7.2.1创建表的时候直接创建索引
语句 CREATE TABLE 表名(
属性名 数据类型[完整性约束条件],
属性名 数据类型[完整性约束条件],
...
属性名 数据类型
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名](属性名1[(长度)])[ASC|DESC]
);
①创建普通索引
CREATE TABLE index1(
id INT,
name VARCHAR(20),
sex boolean,
INDEX(id)
);
在命令列界面，使用 SHOW CREATE TABLE index1; 查看表的详细结构
①创建普通索引
CREATE TABLE index1(
id INT,
name VARCHAR(20),
sex boolean,
INDEX index1_id(id)
);
在命令列界面，使用 SHOW CREATE TABLE index1; 查看表的详细结构
mysql> show create table index1;
| index1 | CREATE TABLE `index1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
KEY `index1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
KEY 字段上建立了名为 index1_id 的索引
在命令列界面，通过 EXPLAIN SELECT * FROM index1 WHERE id=1; 查看索引是否被使用
mysql> explain select * from index1 where id=1;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | index1 | ref | index1_id | index1_id | 5 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
possible_keys：index1_id
key：index1_id
说明index1_id索引已经存在
②创建唯一性索引
CREATE TABLE index2(
id INT UNIQUE,
name VARCHAR(20),
UNIQUE INDEX index2_id(id)
);
命令列界面，查看详细表结构
mysql> show create table index2;
| index2 | CREATE TABLE `index2` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
UNIQUE KEY `index2_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
UNIQUE KEY 字段上建立了名为 index2_id 的索引
③创建全文索引
CREATE TABLE INDEX3(
id INT,
info VARCHAR(20),
FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM; 注意：存储引擎必须是MyISAM,因为 FULLTEXT 类型的索引只有 MyISAM 才能支持
命令列界面，查看详细表结构
mysql> show create table index3;
| index3 | CREATE TABLE `index3` (
`id` int(11) DEFAULT NULL,
`info` varchar(20) DEFAULT NULL,
FULLTEXT KEY `index3_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
④创建单列索引
CREATE TABLE index4(
id INT,
subject VARCHAR(30),
INDEX index4_st(subject(10))
);
命令列界面，查看详细表结构
mysql> show create table index4;
| index4 | CREATE TABLE `index4` (
`id` int(11) DEFAULT NULL,
`subject` varchar(30) DEFAULT NULL,
KEY `index4_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
SUBJECT 字段长为 30，而 index4_st 索引的长度只有10，这样可以提高查询速度。对于字符类型的数据，可以不用查询全部信息，只查询其前面的若干字符信息。
⑤创建多列索引
CREATE TABLE index5(
id INT,
name VARCHAR(20),
sex CHAR(4),
INDEX index5_ns(name,sex)
);
命令列界面，查看详细表结构
mysql> show create table index5;
| index5 | CREATE TABLE `index5` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` char(4) DEFAULT NULL,
KEY `index5_ns` (`name`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
在命令列界面，通过 EXPLAIN SELECT * FROM index5 WHERE name='aaa'; 查看索引是否被使用
mysql> explain select * from index5 where name='aaa';
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | index5 | ref | index5_ns | index5_ns | 63 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
mysql> explain select * from index5 where sex='aaa';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | index5 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
possible_keys=NULL,key=NULL 说明多列索引只有查询条件中使用了这些字段中第一个字段是，索引才会被使用. Extra=Using where,说明没有使用索引。
我的疑惑：任何成功使用索引的查询在 Extra=Using index condition 是这样显示的，但是我的一直都是 Extra=Using where,不清楚原因。
⑥创建空间索引
CREATE TABLE index6(
id INT,
space geometry NOT NULL,
SPATIAL INDEX index6_sp(space)
)ENGINE=MyISAM; 索引的存储引擎必须是MyISAM
命令列界面，查看详细表结构
mysql> show create table index6;
| index6 | CREATE TABLE `index6` (
`id` int(11) DEFAULT NULL,
`space` geometry NOT NULL,
SPATIAL KEY `index6_sp` (`space`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
空间类型包括 GEOMETRY\POINT\LINESTRING\POLYGON
7.2.2在已经存在的表上创建索引
语句 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(属性名[长度])[ASC|DESC]);
①.创建普通索引
CREATE TABLE example0(
id INT(11),
name VARCHAR(20),
sex TINYINT(1)
);
CREATE INDEX index7_id ON example0(id);
命令列界面，查看详细表结构
mysql> show create table example0;
| example0 | CREATE TABLE `example0` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
KEY `index7_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
②.创建唯一性索引
语句 eg: CREATE UNIQUE INDEX index8_id ON index8(course_id); course_id 要有唯一性约束
③.创建全文索引
语句 eg: CREATE FULLTEXT INDEX index9_info ON index9(info); 表 index9 的存储引擎必须是 MyISAM 类型; info 字段必须为 CHAR\VARCHAR\text 等类型
④.创建单列索引
语句： CREATE INDEX index10_addr ON index10(address(4)); 查询时，可以只查询 address 字段的前4个字符，不需要查询全部。
⑤.创建多列索引
语句： CREATE INDEX index11_na ON index11(name,address); 查询条件中必须有name字段才能使用索引
⑥.创建空间索引
语句： CREATE SPATIAL INDEX index12_line ON index12(line);
7.2.3用 ALTER TABLE 语句创建索引
语句： ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名 [长度]) [ASC|DESC]);
①.创建普通索引
ALTER TABLE example0 ADD INDEX index13_name(name(10));
命令列界面，查看表的详细结构
mysql> show create table example0;
| example0 | CREATE TABLE `example0` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
KEY `index7_id` (`id`),
KEY `index13_name` (`name`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
②.创建唯一性索引
语句： CREATE TABLE index14 ADD UNIQUE INDEX index14_id(course_id);
③.创建全文索引
语句: ALTER TABLE index15 add FULLTEXT INDEX index15_info(info);
FULLTEXT 用来设置使用全文索引，表 index15 的存储引擎必须是 MyISAM 类型;info 字段必须为 CHAR\VARCHAR\TEXT 等类型。
④.创建单列索引
语句: ALTER TABLE index16 ADD INDEX index16_addr(address(4));
查询 address 字段的前4个字符，而不需要查询全部。
⑤.创建多列索引
语句： ALTER TABLE index17 add INDEX index17_na(name,address);
查询条件中必须有name字段才能使用索引。
⑥.创建空间索引
语句: ALTER TABLE index18 ADD SPATIAL INDEX index18_line(line);
SPATIAL 用来设置索引为空间索引; 表index18的存储引擎必须是 MyISAM 类型, line 字段必须是非空的，而且必须是空间数据类型。
7.3删除索引
语句： DROP INDEX 索引名 ON 表名;
①.命令列界面，查看表结构
mysql> show create table index1;
| index1 | CREATE TABLE `index1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
KEY `index1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
②.删除索引 DROP INDEX index1_id ON index1;
命令列界面，查看表结构
mysql> show create table index1;
| index1 | CREATE TABLE `index1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
KEY index_id 已经不存在
几个值得注意的问题：
①.MySQL中索引、主键和唯一性的区别是什么？
索引建立在一个或者几个字段上，索引的建立是让表中的记录以一定的顺序排序，这样可以提高查询速度。
主键是表中记录的唯一标识。不同的记录主键值不同，就像没条记录都有自己的一个独特身份证号。一般推荐系统自动建立主键。
唯一性是建立在表中一个或几个字段上，为了对于不同的记录，具有唯一性的字段的值是不同的。
②.表中建立了索引以后，导入大量数据为什么会很慢？
对已经建立了索引的表中插入数据时，插入一条数据就要对该记录按索引排序。所以，导入大量数据的时候速度会很慢。
第八章 视图
试图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口，可以看到系统专门提供的数据。这样，用户可以只看到对自己有用的数据，而不用看到整个数据表中的其他数据。
8.1视图简介
8.1.1 视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。
数据库中只存在了视图的定义，而并没有存放视图中的数据。使用视图进行查询时，数据库系统会从原来的表中取出对应的数据。
视图是在原有的表或者视图的基础上重新定义的虚拟表，这可以从原有的表上选取对用户有用的信息。那些对用户没用，或者用户没有权限去了解的信息，都可以直接屏蔽掉。
这样既使应用简单化，也保证了系统的安全。
视图的作用：
1.使操作简单化：视图需要达到的目的是所见即所需。从试图看到的信息就是所需要了解的信息。视图可以简化对数据的操作。
2.增加数据的安全性：通过视图，用户只能查询和修改指定的数据，指定数据以外的信息，用户根本接触不到。
数据库授权命令可以限制用户的操作权限，但不能先知道特定行和列上。使用视图后，可以简单方便的将用户的权限先知道特定的行和列上。
保证敏感信息不会被没有权限的人看到，保证一些机密信息的安全。
3.提高表的逻辑独立性：视图可以屏蔽原有表结构变化带来的影响。原有增加列、删除列和未被引用的列，对视图不会造成影响。
同样，如果修改了表中的某些列，可以使用修改视图来解决这系列带来的影响。
8.2创建视图
8.2.1创建视图的语法形式
CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
ALGORITHM 表示视图选择的算法;UNDEFINED 是MySQL自动选择所要使用的算法， MERGE 表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分;TEMPTABLE 将视图的结果存入临时表，然后使用临时表执行语句。
CASCADED 表示更新视图时要满足所有相关视图和表的条件，该参数为默认值;LOCAL 表示更新视图时，要满足该视图本身定义的条件即可。
技巧：使用 CREATE VIEW 语句创建视图时，最好加上 WITH CHECK OPTION 参数，而且最好加上 CASCADED 参数。这样，从视图上派生出来的新视图后，更新新视图需要考虑其父视图的约束条件。这种方式比较严格，可以保证数据的安全性。
创建视图，需要有 CREATE VIEW 的权限。
SELECT SELECT_priv,CREATE_view_priv FROM mysql.user WHERE user='root';
8.2.2 在单表上创建视图
CREATE VIEW department_view1 AS SELECT * FROM department;
DESC department_view1;
创建带属性清单的视图
CREATE VIEW department_view2(name,function,location)
AS SELECT d_name,function,address FROM department;
DESC department_view2;
8.2.3在多表上创建视图
use test;
use job;
DESC department;
DESC department;
CREATE TABLE department(
d_id INT(4) NOT NULL PRIMARY KEY,
d_name VARCHAR(20) NOT NULL UNIQUE,
function VARCHAR(50),
address VARCHAR(50)
);
DESC worker;
CREATE ALGORITHM=MERGE VIEW worker_view1(name,department,sex,age,address)
AS SELECT name,department.d_name,sex,2009-birthday,worker.address FROM worker,department WHERE worker.d_id=department.d_id
WITH LOCAL CHECK OPTION;
2009-birthday 对应 age
DESC worker_view1;
8.3查看视图
DESCRIBE worker_view1;
DESC worker_view1;
8.3.2 SHOW TABLE STATUS LIKE '视图名';查看视图基本信息
SHOW TABLE STATUS LIKE 'worker_view1';
mysql> show table status like 'worker_view1';
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| worker_view1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
表的说明(Comment)项的值为WIEW，说明该表为视图。存储引擎、数据长度等信息显示为 NULL ，说明该表是虚拟表，与普通表有差异。同样使用 SHOW TABLE STATUS LIKE 'department';
mysql> show table status like 'department';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| department | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 8388608 | NULL | 2015-05-07 03:30:39 | NULL | NULL | utf8_general_ci | NULL | | |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
department表的基本信息都显示出来，包括存储引擎、创建时间等。但是Comment项没有信息。这就是视图和普通表最直接的区别。
使用 SHOW TABLE STATUS LIKE '表名/视图名'; 视图各个属性显示 NULL ，只有 Comment 显示值为 VIEW。而普通表各个属性显示正常，只有 Comment 无任何显示。
8.3.3 SHOW CREATE VIEW 查看视图详细(其实是所有)信息
语句 SHOW CREATE VIEW 视图名;
SHOW CREATE VIEW worker_view1;
mysql> SHOW CREATE VIEW worker_view1;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| worker_view1 | CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `worker_view1` AS select `worker`.`name` AS `name`,`department`.`d_name` AS `department`,`worker`.`sex` AS `sex`,(2009 - `worker`.`birthday`) AS `age`,`worker`.`address` AS `address` from (`worker` join `department`) where (`worker`.`d_id` = `department`.`d_id`) WITH LOCAL CHECK OPTION | utf8 | utf8_general_ci |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
8.3.4 在 VIEWS 表中查询所有视图详细信息，通常情况下使用 SHOW CREATE VIEW 视图名; 进行查询
SELECT * FROM information_schema.VIEWS;
8.4修改视图
①. CREATE OR REPLACE VIEW
②. ALTER
8.4.1 CREATE OR REPLACE VIEW 语句修改视图
语句： CREATE OR REPLACE VIEW [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
eg: CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW department_view1(department,function,location) AS SELECT d_name,function,address FROM department;
DESC department_view1;
8.4.2 ALTER 语句修改视图
语句： ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
eg:
ALTER VIEW department_view2(department,name,sex,location)
AS SELECT d_name,worker.name,worker.sex,worker.address
FROM department,worker
WHERE department.d_id=worker.d_id
WITH CHECK OPTION;
8.5 更新视图
包括插入（INSERT）、更新（UPDATA）、删除(DELETE)表中的数据。
SELECT * from department;
INSERT INTO department VALUES
(1001,'人事部','管理公司人事变动','2号楼3层'),
(1002,'生产部','主管生产','5号楼1层');
CREATE VIEW department_view3(name,function,address)
AS SELECT d_name,function,address
FROM department
WHERE d_id=1001;
SELECT * FROM department_view3;
等价于
UPDATE department SET d_name='科研部',function='新产品研发',address='3号楼5层' WHERE d_id=1001;
不可更新的视图：
①视图中含有 SUM()、COUNT()、MAX()、MIN()等函数
eg: CREATE VIEW worker_view4(name,sex,total)
AS SELECT name,sex,COUNT(name) FROM worker;
②视图中包含 UNION/UNION ALL/DISTINCT/GROUP BY/HAVING 等关键字。
eg: CREATE VIEW worker_view5(name,sex,address) AS SELECT name,sex,homeaddress FROM worker GROUP BY d_id;
③常量视图
eg: CREATE VIEW worker_view6 AS SELECT 'Aric' as name;
mysql > UPDATE worker_view6 SET name='aaa';
ERROR 1228(HY000):The target table worker_view6 of the UPDATE is not updatable
④视图中包含子查询
eg: CREATE VIEW worker_view7(name) AS SELECT (SELECT name FROM worker);
⑤由不可更新的视图导出的视图
eg: CREATE VIEW worker_view8 AS SELECT * FROM worker_view7;
mysql > UPDATE worker_view8 SET name='Aric';
ERROR 1288(HY00):The target table worker_view8 of the UPDATE is not updatable
⑥创建视图时， ALGORITHM 为 TEMPTABLE 类型。
eg: CREATE ALGORITHM=TEMPTABLE VIEW worker_view9 AS SELECT * FROM worker;
⑦试图对应的表上存在没有默认值的列，而且该列没有包含在视图里。
⑧ WITH [CASCADED|LOCAL] CHECK OPTION也将决定视图是否更新。没有指明时，默认为 CASCADED
eg:
mysql > CREATE VIEW view_test AS SELECT * FROM worker WHERE age<25 WITH LOCAL CHECK OPTION;
mysql > CREATE VIEW view_test1 AS SELECT * FROM view_test WHERE age>15 WITH LOCAL CHECK OPTION;
mysql > CREATE VIEW view_test2 AS SELECT * FROM worker WHERE sex='F' WITH CASCADED CHECK OPTION;
view_test1 和 view_test2 这两个视图是在 view_test 的基础上创建而成的。
更新 view_test1
mysql > UPDATE view_test1 SET age=26 WHERE num=1; 试图更新成功，因为条件 age=26 满足 view_test1 中 age>15 的条件。
更新 view_test2
mysql > UPDATE view_test2 SET age=26 WHERE num=2; ERROR : CHECK OPTION failed.... 视图二更新失败是因为不满足视图 view_test 中 age<25 的条件，所以更新失败。
8.6 删除视图
删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除视图的数据。
语句 ： DROP VIEW [IF EXISTS] 视图名列表 [RESTRICT|CASCADE]
eg: DROP VIEW IF EXISTS worker_view1;
mysql > SHOW CREATE VIEW worker_view1; ERROR :TABLE doesnot EXIST.
同时删除两个视图
eg: DROP VIEW IF EXISTS department_view1,department_view2;
mysql > SHOW CREATE VIEW department_view1; ERROR : TABLE doesnot EXIST
mysql > SHOW CREATE VIEW department_view2; ERROR : TABLE doesnot EXIST
查看用户的 DROP 权限
mysql > SELECT DROP_priv FROM mysql.user WHERE user='root';
+-----------+
| drop_priv |
+-----------+
| Y |
+-----------+
CREATE TABLE work_info(
id INT(10) PRIMARY KEY NOT NULL UNIQUE,#编号
name VARCHAR(20) NOT NULL,#姓名
sex VARCHAR(4) NOT NULL,#性别
age INT(5),#年龄
address VARCHAR(50),#家庭住址
tel VARCHAR(20)#电话号码
);
CREATE TABLE college(
number INT(10) PRIMARY KEY NOT NULL UNIQUE,#学号
name VARCHAR(20) NOT NULL,#姓名
major VARCHAR(20) NOT NULL,#专业
age INT(5)#年龄
);
CREATE TABLE mytable(
id INT(10) PRIMARY KEY NOT NULL UNIQUE,#编号
name VARCHAR(20) NOT NULL,#姓名
sex VARCHAR(4),#性别
info text#备注
);
第九章 触发器
触发器(TRIGGER)是由事件来触发某个操作。这些事件包括 INSERT 语句, UPDATE , DELETE 语句。eg:当学生表中增加了一个学生的信息时，学生的总数就必须同时改变。可以在这里创建一个触发器，每次增加一个学生的记录，就执行一次计算学生总数的操作。这样就可以保证，每次增加学生的纪录后，学生总数与记录数是一致的。触发器触发的执行语句只有一个，也可能有多个。
语句: CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;
BEFORE|AFTER 参数指定了触发器执行的时间,BEFORE 指出发时间之前执行处罚语句,AFTER 表示在触发事件之后执行触发语句;"触发事件"参数是指触发的条件，其中包括 INSERT 、 UPDATE 、 DELETE 。"表名" 参数触发事件操作的标的名称; FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器;"执行语句" 参数指触发器被触发后执行的程序。
eg:
①创建表
CREATE TABLE trigger_time(
exec_time time
);
②创建触发器
CREATE TRIGGER dept_trig1 BEFORE INSERT ON department FOR EACH ROW INSERT INTO trigger_time VALUES(NOW());
③向 department 表插入数据
SELECT * FROM department;
INSERT INTO department VALUES(1003,'销售部','负责产品生产','1号楼销售大厅');
④查看是否引发触发器
SELECT * FROM trigger_time;
9.1.2 创建有多个执行语句的触发器
语句:
delimiter &&
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句1;
执行语句2;
END &&
delimiter;
eg:
①创建触发器
delimiter &&
CREATE TRIGGER dept_trig2 AFTER DELETE
ON department FOR EACH ROW
BEGIN
INSERT INTO trigger_time VALUES('21:01:01');
INSERT INTO trigger_time VALUES('22:01:01');
END &&
delimiter;
②删除数据语句
DELETE FROM department WHERE d_id=1003;
SELECT * FROM trigger_time;
注意：MySQL中，一个表在相同触发时间的相同触发事件，只能创建一个触发器。
9.2查看触发器
9.2.1 SHOW TRIGGERS 语句查看触发器信息
语句:
SHOW TRIGGERS;查看所有触发器的详细信息
mysql> show triggers;
+------------+--------+------------+---------------------------------------------------------------------------------------------------+--------+---------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+------------+--------+------------+---------------------------------------------------------------------------------------------------+--------+---------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| dept_trig1 | INSERT | department | INSERT INTO trigger_time VALUES(NOW()) | BEFORE | NULL | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci |
| dept_trig2 | DELETE | department | BEGIN
INSERT INTO trigger_time VALUES('21:01:01');
INSERT INTO trigger_time VALUES('22:01:01');
END | AFTER | NULL | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci |
+------------+--------+------------+---------------------------------------------------------------------------------------------------+--------+---------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
查询指定触发器的详细信息
语句: SELECT * FROM information_schema.triggers WHERE trigger_name='触发器名';
eg: SELECT * FROM information_schema.triggers WHERE trigger_name='dept_trig1';
mysql> select * from information_schema.triggers where trigger_name='dept_trig1';
+-----------------+----------------+--------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+----------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED | SQL_MODE | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+-----------------+----------------+--------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+----------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| def | test | dept_trig1 | INSERT | def | test | department | 0 | NULL | INSERT INTO trigger_time VALUES(NOW()) | ROW | BEFORE | NULL | NULL | OLD | NEW | NULL | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci |
+-----------------+----------------+--------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+----------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
9.3 触发器的使用
DROP TABLE trigger_test;
CREATE TABLE trigger_test(
id INT(4) PRIMARY KEY UNIQUE NOT NULL auto_increment,
info VARCHAR(30)
);
DROP TRIGGER dept_trig1;
DROP TRIGGER before_insert;
delimiter &&
CREATE TRIGGER before_insert BEFORE INSERT
ON department FOR EACH ROW
BEGIN
INSERT INTO trigger_test VALUES(NULL,"before_insert");
END &&
delimiter;
DROP TRIGGER dept_trig2;
DROP TRIGGER after_insert;
delimiter &&
CREATE TRIGGER after_insert AFTER INSERT
ON department FOR EACH ROW
BEGIN
INSERT INTO trigger_test VALUES(NULL,"after_insert");
END &&
delimiter;
DELETE FROM department WHERE d_id=1003;
INSERT INTO department VALUES(1003,'销售部','负责产品生产','1号销售大厅');
SELECT * FROM trigger_test;
在激活触发器时，对触发器中的执行语句存在一些限制。例如，触发器不能包含 START TRANSACTION/COMMIT/ROLLBACK 等关键词，也不能包含 CALL 语句。
在触发器执行过程中，任何步骤出错都会阻止程序向下执行。
9.4删除触发器
语句: DROP TRIGGER 触发器名;
eg: DROP TRIGGER dept_trig1;
mysql> select * from information_schema.triggers where trigger_name='dept_trig1';
Empty set
CREATE TABLE product(
id INT(10) PRIMARY KEY NOT NULL UNIQUE,#编号
name VARCHAR(20) NOT NULL,#产品功能
function VARCHAR(50),#主要功能
company VARCHAR(20),#生产厂商
address VARCHAR(50)#家庭住址
);
CREATE TABLE operate(
op_id INT(10) PRIMARY KEY NOT NULL UNIQUE auto_increment,#编号
op_type VARCHAR(20) NOT NULL,#操作方式
op_time time NOT NULL#操作时间
);
CREATE TRIGGER product_af_del AFTER DELETE
ON product FOR EACH ROW
INSERT INTO operate VALUES(NULL,'Delete product',NOW());
第十章 查询数据
10.1基本查询语句
语句:
SELECT 属性列表 FROM 表名和视图列表
[WHERE 条件表达式1]
[GROUP BY 属性名1 [HAVING 条件表达式2]]
[ORDER BY 属性名2 [ASC|DESC]]
CREATE TABLE employee(
num INT(4) PRIMARY KEY UNIQUE NOT NULL,
d_id INT(4) NOT NULL,
name VARCHAR(20) NOT NULL,
age INT(4) NOT NULL,
sex VARCHAR(10),
homeaddr VARCHAR(30)
);
INSERT INTO employee VALUES
(1,1001,'张三',26,'男','北京市海淀区'),
(2,1001,'李四',24,'女','北京市昌平区'),
(3,1002,'王五',25,'男','湖南长沙市'),
(4,1004,'Aric',15,'男','England');
SELECT num,name,age,sex,homeaddr FROM employee;
SELECT * FROM employee;
SELECT * FROM employee WHERE d_id IN(1001,1004);
SELECT * FROM employee WHERE name NOT IN('张三','李四');
SELECT * FROM employee WHERE age BETWEEN 15 AND 25;
SELECT * FROM employee WHERE age NOT BETWEEN 15 AND 25;
SELECT * FROM employee WHERE name LIKE 'Aric';
SELECT * FROM employee WHERE name='Aric';
SELECT * FROM employee WHERE homeaddr LIKE '北京%'; %任意字符(通配符)
SELECT * FROM employee WHERE name like 'Ar_c'; _代表一个字符
SELECT * FROM employee WHERE name NOT LIKE '张%';
SELECT * FROM work WHERE info is NULL;
SELECT * FROM work WHERE info is NOT NULL;
SELECT * FROM employee WHERE d_id=1001 AND sex LIKE '男';
SELECT * FROM employee WHERE d_id<1004 AND age<26 AND sex='男';
SELECT * employee WHERE num IN(1,2,3) AND age BETWEEN 15 AND 25 AND homeaddr LIKE '%北京市%';
SELECT * FROM employee WHERE d_id=1001 OR sex LIKE '男';
SELECT * FROM employee WHERE num IN(1,2,3) OR age BETWEEN 24 AND 25 OR homeaddr like '%北京市%';
SELECT * FROM employee WHERE num IN(1,3,4) AND age=25 OR sex='女';
SELECT * FROM employee WHERE sex='女' OR num IN(1,3,4) AND age=25;
SELECT DISTINCT d_id FROM employee;
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age DESC;
SELECT * FROM employee ORDER BY d_id ASC,age DESC;
10.2.12 分组查询
语句: GROUP BY [HAVING 条件表达式][WITH ROLLUP];
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。 GROUP_CONCAT() 函数会把每个分组中指定字段值都显示出来。同时， GROUP BY 关键字通常与集合函数一起使用。集合函数包括 COUNT()/SUM()/AVG()/MAX()/MIN()
SELECT * FROM employee GROUP BY sex;
SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex;
+-----+--------------------+
| sex | GROUP_CONCAT(name) |
+-----+--------------------+
| 女 | 李四 |
| 男 | 张三,王五,Aric |
+-----+--------------------+
SELECT sex,COUNT(sex) FROM employee GROUP BY sex;
SELECT sex,COUNT(sex) FROM employee GROUP BY sex HAVING COUNT(sex)>=3;
SELECT * FROM employee FROM GROUP BY d_id,sex; 先按d_id分组， 再按sex分组
SELECT sex,COUNT(sex) FROM employee GROUP BY sex WITH ROLLUP; WITH ROLLUP 在数据记录的最后统计一个记录数总和，跟 COUNT() 连用。
SELECT sex,COUNT(sex) FROM employee GROUP BY sex WITH ROLLUP;
10.2.12 用 LIMIT 限制查询结果的数量
1.不指定初始位置
eg:
SELECT * FROM employee LIMIT 2; 从初始位置开始，显示前两条记录
SELECT * FROM employee LIMIT 6; 从初始位置开始，显示前6条记录，如果不足6条，则显示有的全部记录。
2.指定初始位置
SELECT * FROM employee LIMIT 0,2;从初始位置开始，显示前两条记录，"0"代表初始位置
SELECT * FROM employee LIMIT 1,2;从第2条记录开始，显示两条记录
10.3集合函数查询
10.3.1 COUNT()
SELECT COUNT(*) FROM employee;
SELECT d_id,COUNT(*) FROM employee GROUP BY d_id;
10.3.2 SUM()函数
SELECT * FROM grade;
SELECT num,SUM(score) FROM grade WHERE num=1001;
10.3.3 AVG()函数
SELECT AVG(age) FROM employee;
SELECT course,AVG(score) FROM grade GROUP BY course;
10.3.4 MAX()函数
SELECT MAX(age) FROM employee;
SELECT course,MAX(score) FROM grade GROUP BY course;
SELECT MAX(name) FROM work;
SELECT MIN(age) FROM age;
SELECT course,MIN(score) FROM grade GROUP BY course;
内连接
SELECT num,name,employee.d_id,age,sex,d_name,function FROM employee,department WHERE employee.d_id=department.d_id;
外连接
①左连接 eg:
SELECT num,name,employee.d_id,age,sex,d_name,function FROM employee LEFT JOIN department ON employee.d_id=department.d_id;
②右连接 eg:
SELECT num,name,employee.d_id,age,sex,d_name,function FROM employee RIGHT JOIN department ON employee.d_id=department.d_id;
10.4.3 复合条件连接查询
SELECT num,name,employee,d_id,age,sex,d_name,function FROM employee,department WHERE employee.d_id=department.d_id AND age>24;
SELECT num,name,employee,d_id,age,sex,d_name,function FROM employee,department WHERE employee.d_id=department.d_id ORDER BY age ASC;
10.5 子查询
IN eg:
SELECT * FROM employee WHERE d_id IN(SELECT d_id FROM department);
SELECT * FROM employee WHERE d_id NOT IN(SELECT d_id FROM department);
10.5.2 带比较运算符的子查询
CREATE TABLE scholarship(
level INT(4),
score INT(4)
);
INSERT INTO scholarship VALUES
(1,90),
(2,80),
(3,70);
CREATE TABLE computer_stu(
id INT(4)PRIMARY KEY NOT NULL,
name VARCHAR(30) NOT NULL,
score INT(4) NOT NULL
);
INSERT INTO computer_stu VALUES
(1001,'Lily',85),
(1002,'Tom',91),
(1003,'Jim',87),
(1004,'Aric',77),
(1005,'Lucy',65),
(1006,'Andy',99),
(1007,'Ada',85),
(1008,'Jeck',70);
SELECT id,name,score FROM computer_stu WHERE score>=
(SELECT score FROM scholarship WHERE LEVEL=1);
SELECT * FROM employee;
SELECT * FROM department;
SELECT d_id,d_name FROM department WHERE d_id!=
(SELECT d_id FROM employee WHERE age=24);
SELECT d_id,d_name FROM department WHERE d_id<>
(SELECT d_id FROM employee WHERE age=24);
10.5.3 带 EXISTS 关键字的子查询 内层语句不返回记录，只返回真假值;当内层语句返回值为真，则进行外层查询
SELECT * FROM employee;
SELECT * FROM department;
SELECT * FROM employee WHERE EXISTS
(SELECT d_name FROM department WHERE d_id=1003);
SELECT * FROM employee WHERE EXISTS
(SELECT d_name FROM department WHERE d_id=1004);
SELECT * FROM employee WHERE age>24 AND EXISTS
(SELECT d_name FROM department WHERE d_id=1003);
SELECT * FROM employee WHERE NOT EXISTS
(SELECT d_name FROM department WHERE d_id=1003); 因为内层语句返回记录，故 NOT EXISTS 为假
10.5.4 带 ANY 关键字的子查询
SELECT * FROM computer_stu;
SELECT * FROM scholarship;
SELECT * FROM computer_stu
WHERE score>=ANY
(SELECT score FROM scholarship);
10.5.5 带 ALL 关键字的子查询
SELECT * FROM computer_stu WHERE score>=ALL
(SELECT score FROM scholarship);
10.6 合并查询结果 UNION/ UNION ALL
UNION 将所有查询结果合并到一起，去除掉相同的
UNION ALL 关键字则只是简单的合并到一起
语句: SELECT 语句1 UNION|UNION ALL SELECT语句2 UNION|UNION ALL SELECT语句n;
SELECT d_id FROM department UNION SELECT d_id FROM employee;
SELECT d_id FROM department UNION ALL SELECT d_id FROM employee;
10.7 位表和字段取别名
10.7.1 为表取名
SELECT * FROM department d WHERE d.d_id=1001;
10.7.2 为字段取别名
语句: 属性名 [AS] 别名
SELECT d_id AS department_id, d_name AS department_name FROM department;
SELECT d.d_id AS department_id,d.d_name AS department_name,d.function,d.address FROM department d WHERE d.d_id=1001;
10.8 使用正则表达式查询
10.8.1 查询以特定字符或字符串开头的记录
使用字符"^" 可以匹配以特定字符或字符串开头的记录
CREATE TABLE info(
id INT NOT NULL PRIMARY key UNIQUE,
name VARCHAR(30) NOT NULL
);
DELETE FROM info;
INSERT INTO info VALUE
(1,'Aric'),
(2,'Eric'),
(4,'Jack'),
(5,'Lucy'),
(6,'Lily'),
(8,'aaa'),
(9,'dadaaa'),
(10,'aaabd'),
(11,'abc12'),
(12,'ad321'),
(17,'ababab');
SELECT * FROM info WHERE name REGEXP '^L';
SELECT * FROM info WHERE name REGEXP '^aaa';
10.8.2 查询以特定字符或字符串结尾的记录
SELECT * FROM info WHERE name REGEXP 'c$';
SELECT * FROM info WHERE name REGEXP 'aaa$';
10.8.3 用符号 "." 来代替字符串中的任意一个字符
SELECT * FROM info WHERE name REGEXP '^L..y$';
SELECT * FROM info WHERE name REGEXP '[ceo]';
SELECT * FROM info WHERE name REGEXP '[0-9]';
SELECT * FROM info WHERE name REGEXP '[0-9a-c]';
SELECT * FROM info WHERE name REGEXP '[^a-w0-9]';
10.8.6 匹配指定字符串
SELECT * FROM info WHERE name REGEXP 'ic';
SELECT * FROM info WHERE name REGEXP 'ic|uc|ab';
10.8.7 使用"*" 和 "+" 来匹配多个字符
"*" 表示任意字符
"+" 表示至少一个字符
SELECT * FROM info WHERE name REGEXP 'a*c'; c之前出现任意个a，可以有，也可以任意个
SELECT * FROM info WHERE name REGEXP 'a+c';
10.8.8 使用{M}或者{M,N}来指定字符串连续出现的次数
SELECT * FROM info where name REGEXP 'a{3}';
SELECT * FROM info where name REGEXP 'ab{1,3}';
CREATE TABLE student(
id INT(10) PRIMARY KEY NOT NULL UNIQUE,#学号
name VARCHAR(20) NOT NULL,#姓名
sex VARCHAR(4),#性别
birth YEAR,#出生年份
department VARCHAR(20),#院系
address VARCHAR(50)#家庭住址
);
CREATE TABLE score(
id INT(10) PRIMARY KEY NOT NULL UNIQUE auto_increment,#编号
stu_id INT(10) NOT NULL,#学号
c_name VARCHAR(20),#课程名
grade INT(10)#分数
);
第11章 插入、更新与删除数据
11.1 插入数据
11.1.1 为表的所有字段插入数据
1. INSERT 语句中不指定具体的字段名
语句: INSERT INTO 表名 VALUES(值1 ,值2,...,值n);
DESC product;
SELECT * FROM product;
INSERT INTO product VALUES(1001,'ABC药物','治疗感冒','ABC制药厂','北京市昌平区');
2. INSERT 语句中列出所有字段
语句: INSERT INTO 表名(属性1,属性2,...,属性n) VALUES (值1,值2,...,值n);
INSERT INTO product(id,name,function,company,address) VALUES(1002,'BCD','治疗头疼','BCD制药厂','北京市海淀区');
SELECT * FROM product WHERE id=1002;
INSERT INTO product(id,function,name,address,company) VALUES(1003,'治疗癌症','AB康复丸','北京市顺义区','AB康复制药厂');
11.1.2 为表的指定字段插入数据
语句: INSERT INTO 表名(属性1,属性2,...,属性m) VALUES(值1,值2,...,值m);
INSERT INTO product(id,name,company) VALUES(1004,'EF咳嗽灵','EF制药厂');
mysql> show create table product;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| product | CREATE TABLE `product` (
`id` int(10) NOT NULL,
`name` varchar(20) NOT NULL,
`function` varchar(50) DEFAULT NULL,
`company` varchar(20) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
因为 function/company/address 默认值是 NULL，所以可以不输入值，若不能为 NULL ，输入空值，则会出现 "Field 'name' doesn't have a default value" 这样的错误
INSERT into product(id,company,name) VALUES(1005,'北京制药厂','OK护嗓药');
11.1.3 同时插入多条记录
语句: INSERT INTO 表名[(属性列表)] VALUES
(取值列表1),
(取值列表2),
...,
(取值列表n);
INSERT INTO product VALUES
(1006,'头疼灵1号','治疗头疼','DD制药厂','北京市房山区'),
(1007,'头疼灵2号','治疗头疼','DD制药厂','北京市房山区'),
(1008,'头疼灵3号','治疗头疼','DD制药厂','北京市房山区');
SELECT * FROM product WHERE id>=1006 AND id<=1008;
INSERT INTO product(id,name,company) VALUES
(1009,'护发1号','北京护发素厂'),
(1010,'护发2号','北京护发素厂'),
(1011,'护发3号','北京护发素厂');
SELECT * FROM product WHERE id>=1009 AND id<=1011;
11.1.4 将查询结果插入到表中
语句: INSERT INTO 表名1 (属性列表1) SELECT 属性列表2 FROM 表名2 WHERE 条件表达式;
eg:
DROP TABLE medicine;
CREATE TABLE medicine(
id INT(4) PRIMARY KEY UNIQUE,
name VARCHAR(20),
function VARCHAR(20),
company VARCHAR(20),
address VARCHAR(20)
);
INSERT INTO product(id,function,name,address,company)
SELECT id,function,name,address,company FROM medicine;
SELECT * FROM product WHERE id>=20 AND id<=2003;
11.2 更新数据
UPDATE 表名 SET 属性名1=取值1,属性名2=取值2,...,属性名n=取值n WHERE 条件表达式;
SELECT * FROM product WHERE id=1001;
UPDATE product SET name='AAA感冒药',address='北京朝阳区' WHERE id=1001;
SELECT * FROM product WHERE id=1001;
SELECT * FROM product WHERE id>=1009 AND id<=1011;
UPDATE product SET function='护理头发',address='北京市昌平区' WHERE id>=1009 AND id<=1011;
SELECT * FROM product WHERE id>=1009 AND id<=1011;
11.3 删除数据
语句: DELETE FROM 表名 [WHERE 条件表达式];
SELECT * FROM product WHERE id=1011;
DELETE FROM product WHERE id=1011;
SELECT * FROM product WHERE id=1011;
SELECT * FROM product WHERE address='北京市顺义区';
DELETE FROM product WHERE address='北京市顺义区';
SELECT * FROM product;
DELETE FROM product;
SELECT * FROM product;
CREATE TABLE food(
id INT(10) PRIMARY KEY NOT NULL UNIQUE auto_increment,
name VARCHAR(20) NOT NULL,
company VARCHAR(30) NOT NULL,
price FLOAT,
produce_time YEAR,
validity_time INT(4),
address VARCHAR(50)
);
INSERT INTO food VALUES
(1,'AA饼干','AA饼干厂',2.5,2008,3,'北京'),
(2,'CC牛奶','CC牛奶厂',3.5,2009,1,'河北'),
(3,'EE果冻','EE果冻厂',1.5,2007,2,'北京'),
(4,'FF咖啡','FF咖啡厂',20,2002,5,'天津'),
(5,'GG奶糖','GG奶糖厂',14,2003,3,'广东');
CREATE TABLE teacher(
id INT(4) PRIMARY KEY NOT NULL UNIQUE auto_increment,
num INT(10) NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday datetime,
address VARCHAR(50)
);
INSERT INTO teacher VALUES
(1,1001,'张三','男','1984-11-08','北京市昌平区'),
(2,1002,'李四','女','1970-01-21','北京市海淀区'),
(3,1003,'王五','男','1976-10-30','北京市昌平区'),
(4,1004,'赵六','男','1980-06-05','北京市顺义区');
CREATE TABLE animal(
id INT(4) PRIMARY KEY UNIQUE NOT NULL auto_increment,
name VARCHAR(20) NOT NULL,
kinds VARCHAR(30) NOT NULL,
legs INT(4) NOT NULL,
behavior VARCHAR(30)
);
第12章 MySQL运算符
4.异或运算 XOR 其中一个操作数为 NULL,结果为 NULL
SELECT NULL XOR 1,NULL XOR 0,3 XOR 1,1 XOR 0,0 XOR 0,3 XOR 2 XOR 0 XOR 1;
12.5 位运算符(二进制基础上)
SELECT 5&6,5&6&7;
SELECT 5|6,5|6|7;
SELECT ~1;MySQL中，常量是8个字节，每个字节是8位，那么一个常量就是64位
SELECT BIN(~1);
"^"位异或
SELECT 5^6;
按位左移右移
SELECT 5<<2,5>>2;
第13章 MySQL函数
ABS(x)
CEIL(X) CEILING(x)
FLOOR(X)
RAND()
RAND(x)
SIGN(X)
PI()
TRUNCATE(X,D)
ROUND(X)
ROUND(X,D)
POW(X,Y) POWER(X,Y)
SQRT(X)
EXP(X)
MOD(N,M)
LOG(X)
LOG10(X)
RADIANS(X)
DEGREES(X)
SIN(X)
ASIN(X)
COS(X)
ACOS(X)
COS(X)
ACOS(X)
TAN(X)
ATAN(X)
ATAN(X) ATAN2(Y,X)
COT(X)
SELECT ABS(0.5),ABS(-0.5),PI();
SELECT SQRT(16),SQRT(2),MOD(5,2);
SELECT CEIL(2.3),CEILING(-2.3),CEILING(-2.3);
SELECT FLOOR(2.3),FLOOR(-2.3);
SELECT RAND(),RAND(),RAND(2),RAND(2);
SELECT RAND(2),RAND(3);
SELECT ROUND(2.3),ROUND(2.5),ROUND(2.53,1),ROUND(2.55,1);
SELECT TRUNCATE(2.53,1),TRUNCATE(2.55,1);
SELECT SIGN(-2),SIGN(0),SIGN(2);
SELECT POW(3,2),POWER(3,2),EXP(2);
SELECT LOG(7.38905609893065),LOG10(100);
SELECT RADIANS(180),DEGREES(3.141592653589793);
SELECT SIN(0.5235987755982989),ASIN(0.5);
SELECT ASIN(2);
SELECT COS(1.0471975511965979),ACOS(0.5);
SELECT ACOS(-2);
SELECT TAN(0.7853981633974483),ATAN(1),ATAN2(1);
SELECT COT(1),1/TAN(1);
13.3 字符串函数
CREATE TABLE t2(s VARCHAR(30));
INSERT INTO t2 VALUES('beijing');
SELECT s,CHAR_LENGTH(s),LENGTH(s) FROM t2;
SELECT CONCAT('bei','ji','ng'),CONCAT_WS('-','bei','ji','ng');
SELECT s,INSERT(s,4,4,'fang') FROM t2;
SELECT UPPER('mysql'),UCASE('mysql'),LOWER('MYSQL'),LCASE('MYSQL');
SELECT s,LEFT(s,3),RIGHT(s,3) FROM t2;
SELECT s,LPAD(s,10,'+-'),RPAD(s,10,'+-') FROM t2;
SELECT CONCAT('+',' me ','+'),CONCAT('+',LTRIM(' me '),'+'),CONCAT('+',RTRIM(' me '),'+'),CONCAT('+',TRIM(' me '),'+');
SELECT TRIM('ab' FROM 'ababddddabddab');
SELECT REPEAT('mysql-',2);
SELECT CONCAT('+',SPACE(4),'+'),REPLACE('mysql','sql','book');
SELECT STRCMP('abc','abb'),STRCMP('abc','abc'),STRCMP('abc','abd');
SELECT s,SUBSTR(s,4,3),MID(s,4,3) FROM t2;
SELECT s,LOCATE('jin',s),POSITION('jin' IN s),INSTR(s,'jin')FROM t2;
SELECT s,REVERSE(s) FROM t2;
SELECT ELT(2,'me','my','he','she');
SELECT FIELD('he','me','my','he','she');
SELECT FIND_IN_SET('like','i,like,bei,jing');
SELECT MAKE_SET(11,'a','b','c'),MAKE_SET(7,'a','b','c','d');
13.4 日期和时间函数
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME();
SELECT NOW(),LOCALTIME(),SYSDATE(),CURRENT_TIMESTAMP();
SELECT NOW(),UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW());
DROP TABLE t4;
CREATE TABLE t4(
dt datetime,
d date,
t time
);
INSERT INTO t4 VALUES (NOW());
SELECT dt,UNIX_TIMESTAMP(dt),FROM_UNIXTIME('1256458559') FROM t4;
SELECT CURDATE(),`UTC_DATE`(),CURTIME(),UTC_TIME();
SELECT dt,MONTH(dt),MONTHNAME(dt) FROM t4;
SELECT MONTH('2008-8-8'),MONTHNAME('2008-8-8');
SELECT dt,DAYNAME(dt),DAYOFWEEK(dt),WEEKDAY(dt) FROM t4; DAYOFWEEK(date)1表示星期日，2表示星期一 WEEKDAY(date) 0表示星期一，1表示星期二
ALTER TABLE t4 ADD (d date);
DELETE FROM t4;
DESC t4;
INSERT INTO t4 VALUES(NOW(),'2009-10-25','16:15:59');
SELECT d,WEEK(d),WEEKOFYEAR(d),dt,WEEK(dt),WEEKOFYEAR(dt) FROM t4;
SELECT d,DAYOFYEAR(d),DAYOFMONTH(d) FROM t4;
SELECT d,YEAR(d),QUARTER(d) FROM t4; QUARTER()季度：1~4
SELECT dt,HOUR(dt),MINUTE(dt),SECOND(dt) FROM t4;
SELECT dt,EXTRACT(YEAR FROM dt),EXTRACT(MINUTE FROM dt) FROM t4;
SELECT t,TIME_TO_SEC(t),SEC_TO_TIME(58559) FROM t4;
SELECT d,TO_DAYS(d),FROM_DAYS(734070),DATEDIFF(d,'2009-10-24') FROM t4;
SELECT d,ADDDATE(d,3),SUBDATE(d,3),t,ADDTIME(t,5),SUBTIME(t,5);
ADDDATE(dt,INTERVAL expr type) DATE_ADD(dt,INTERVAL expr type)
SELECT dt,ADDDATE(dt,INTERVAL '1 1'YEAR_MONTH) FROM t4;
SELECT dt,ADDDATE(dt,INTERVAL '-1 -1'YEAR_MONTH) FROM t4;
SELECT d,DATE_FORMAT(d,'%b %D %Y') FROM t4;
SELECT d,DATE_FORMAT(d,'%j') DAY,DATE_FORMAT(d,'%W')WEEK FROM t4;
SELECT t,TIME_FORMAT(t,'%r') FROM t4;
13.4 日期和时间函数
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME();
SELECT NOW(),LOCALTIME(),SYSDATE(),CURRENT_TIMESTAMP();
SELECT NOW(),UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW());
DROP TABLE t4;
CREATE TABLE t4(
dt datetime,
d date,
t time
);
INSERT INTO t4 VALUES (NOW());
SELECT dt,UNIX_TIMESTAMP(dt),FROM_UNIXTIME('1256458559') FROM t4;
SELECT CURDATE(),`UTC_DATE`(),CURTIME(),UTC_TIME();
SELECT dt,MONTH(dt),MONTHNAME(dt) FROM t4;
SELECT MONTH('2008-8-8'),MONTHNAME('2008-8-8');
SELECT dt,DAYNAME(dt),DAYOFWEEK(dt),WEEKDAY(dt) FROM t4; DAYOFWEEK(date)1表示星期日，2表示星期一 WEEKDAY(date) 0表示星期一，1表示星期二
ALTER TABLE t4 ADD (d date);
DELETE FROM t4;
DESC t4;
INSERT INTO t4 VALUES(NOW(),'2009-10-25','16:15:59');
SELECT d,WEEK(d),WEEKOFYEAR(d),dt,WEEK(dt),WEEKOFYEAR(dt) FROM t4;
SELECT d,DAYOFYEAR(d),DAYOFMONTH(d) FROM t4;
SELECT d,YEAR(d),QUARTER(d) FROM t4; QUARTER()季度：1~4
SELECT dt,HOUR(dt),MINUTE(dt),SECOND(dt) FROM t4;
SELECT dt,EXTRACT(YEAR FROM dt),EXTRACT(MINUTE FROM dt) FROM t4;
SELECT t,TIME_TO_SEC(t),SEC_TO_TIME(58559) FROM t4;
SELECT d,TO_DAYS(d),FROM_DAYS(734070),DATEDIFF(d,'2009-10-24') FROM t4;
SELECT d,ADDDATE(d,3),SUBDATE(d,3),t,ADDTIME(t,5),SUBTIME(t,5);
ADDDATE(dt,INTERVAL expr type) DATE_ADD(dt,INTERVAL expr type)
SELECT dt,ADDDATE(dt,INTERVAL '1 1'YEAR_MONTH) FROM t4;
SELECT dt,ADDDATE(dt,INTERVAL '-1 -1'YEAR_MONTH) FROM t4;
SELECT d,DATE_FORMAT(d,'%b %D %Y') FROM t4;
SELECT d,DATE_FORMAT(d,'%j') DAY,DATE_FORMAT(d,'%W')WEEK FROM t4;
SELECT t,TIME_FORMAT(t,'%r') FROM t4;
SELECT GET_FORMAT(datetime, 'ISO'),GET_FORMAT(DATE, 'EUR'),GET_FORMAT(TIME, 'USA');
SELECT dt,DATE_FORMAT(dt,GET_FORMAT(datetime,'INTERNAL')) FROM t4;
SELECT d,DATE_FORMAT(dt,GET_FORMAT(DATE, 'EUR')) FROM t4;
SELECT t,TIME_FORMAT(t,GET_FORMAT(TIME, 'USA'));
- MySQL入门很简单——读书笔记
- 《MYSQL入门很简单》
- MySQL入门很简单-索引
- MySQL入门很简单-视图
- MySQL入门很简单-触发器
- MySQL入门很简单-MySQL数据类型
- MySQL入门很简单-MySQL存储引擎
- MySQL简单入门——安装配置启动登录
- MySql简单入门二——数据库中的数据类型
- mysql简单入门三——创建表相关语句
- MySQL入门1——数据类型和简单数据表操作
- 《MYSQL入门很简单》学习笔记1——概论，数据类型，操作数据库
- 《MYSQL入门很简单》学习笔记2——表，索引，视图，触发器
- 视频专辑：MySQL入门很简单视频教程
- 【转载】《MySQL入门很简单》学习笔记
- MySQL入门很简单笔记-数据库基础
- MySQL 简单入门
- mysql 简单入门讲解
- MySQL集群搭建步骤详解
- linux 关闭窗口后 进程结束 解决办法
- linux 添加用户
- 虚拟机Ubuntu扩大硬盘容量
- VC编程实现色彩空间XYZ与LAB相互转换
- MySQL入门很简单——读书笔记
- 程序中的@Override是什么意思
- jvm 命令 大全
- 文献阅读：Institutional, cultural and transaction cost influences on entry mode choice and performance
- VC编程实现色彩空间RGB与XYZ相互转换
- 一个目标100亿的互联网金融创业项目完整思路(毫无保留,赤裸裸全部是干货分享)
- Linux基础知识复习之命令篇
- 到底哪卡了 ---TraceView
- 【你知道吗？Web的26项基本概念和技术】
