（六天玩转mysql数据库笔记）
insert into stu_info(name, score) values('刘', 10); -- 查看所有字符集 show character set; -- 查看服务器、客户端默认字符集 show variables like 'character_set%'; -- set name = value 作用域是一次会话 set names gbk; -- 数据类型（列类型） -- 所谓数据类型，对数据进行统一分类，从系统的角度出发，为了能够使用 -- 统一的方式进行管理，更好的利用有限的空间 -- sql中将数据分成了三大类：数值类型、字符串类型、时间日期类型 -- 数值型 ： 整数型、小数型 -- 整数型 ： 5类 tinyint : 迷你整形 1字节 256 smallint : 小整形 2字节 65536 mediumint: 中整型 3字节 int : 标准整型 4字节 bigint: 大整型 8字节 -- 创建 整型表 create table my_int( int_tiny tinyint, int_small smallint, int_3 int, int_4 bigint )charset utf8; -- 插入 insert into my_int values(100, 100, 100, 100); -- 不合法的插入 insert into my_int values ('a', 'b', '199', 'f'); -- ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'int_tiny' at row 1 -- 超出范围 insert into my_int values (255, 100000, 100000000, 10000000); -- ERROR 1264 (22003): Out of range value for column 'int_tiny' at row 1 -- SQL中的数值类型全都是默认有符号，分正负，有时候需要使用无符号类型 int unsigned -- 增加无符号类型 alter table my_int add int_5 tinyint unsigned; -- 插入数据 insert into my_int values (127,1, 1, 1, 255); -- desc mysql> desc my_int; +-----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+-------+ | int_tiny | tinyint(4) | YES | | NULL | | | int_small | smallint(6) | YES | | NULL | | | int_3 | int(11) | YES | | NULL | | | int_4 | bigint(20) | YES | | NULL | | | int_5 | tinyint(3) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+-------+ -- 括号后面的（）内有个数字，代表显示宽度，数据最终显示的位数，没有特别的含义 -- 知识默认的告诉用户可以显示的形式而已 alter table my_int add int_6 tinyint(1) unsigned; insert into my_int values (127,1, 1, 1, 255, 255); -- 显示宽度的意义：当数据不够显示宽度时，会自动让数据编程对应的显示宽度 -- 通常需要搭配一个前导0来补齐，零填充会导致数值自动转换无符号 alter table my_int add int_7 tinyint(2) zerofill; insert into my_int values (127,1, 1, 1, 255, 1, 1); -- 零填充的意义（显示宽度）：保证数据格式 -- 小数型：带有小数点，或者范围超出整值类型 -- 浮点数:小数点浮动，精度有限，会丢失精度 -- 定点型:小数点固定，精度固定，不丢失精度 浮点型： float : 单精度，四字节，精度范围大约7位左右 double : 双精度，八字节，精度范围大约15位 -- 创建浮点表，直接float表示没有小数部分，float(M,D)，M表示总长度，D表示小数部分 create table my_float( f1 float, f2 float(10,2), f3 float (6, 2) )charset utf8; -- 插入数据 insert into my_float values (1000.10, 1000.10, 1000.10); insert into my_float values (9999999999, 99999999.99, 9999.99); insert into my_float values (3e38, 3.01e7, 1234.56); insert into my_float values (1234567890, 12345678.90, 1234.56); -- 浮点型,整数部分不能超过长度，小数部分可以（系统会自动四舍五入） -- 超出长度插入数据 insert into my_float values(123456, 1234.12345678, 123.98765432); insert into my_float values(123456, 1234.12, 1234.56); -- 结果：浮点数一定会进行四舍五入（超出精度范围）：浮点数如果因为系统进位导致整数 -- 部分超出指定长度，那么系统也允许成立 -- 定点型：绝对保证整数部分不会被四舍五入，不丢失精度，小数部分有可能 -- （理论上小数部分也不会丢失精度） decimal 变长，大致是每9个数字，采用4个字节存储，整数和小数分开计算 -- M最大是65,D最大是30 -- 创建定点整数表 create table my_decimal( f1 float (10, 2), d1 decimal (10, 2) )charset utf8; -- 定点数的整数部分一定不能超过长度（进位也不可以） -- insert insert into my_decimal values (12345678.90, 12345678.90); -- 有效 insert into my_decimal values (1234.123456, 1234.123456); -- 小数部分超出 -- Query OK, 1 row affected, 1 warning (0.03 sec) -- 查看warning show warnings; -- 浮点数如果进位导致长度溢出是允许的，定点不允许 insert into my_decimal values (99999999.99, 99999999.99); -- 没有问题 insert into my_decimal values (99999999.99, 99999999.999); -- 有问题 -- 如果需要粗略、级别大的用浮点 精确的 用定点 -- 时间日期类型 Datetime 格式是YYYY-mm-dd HH:ii:ss， 表示的范围从1000到9999年，有0值 DATE 就是datetime中的date部分 TIME 时间段，指定的某个区间之间，时间到+时间 timestamp 时间戳，格式与datetime一致 year 年份，两种形式 -- 创建时间日期表 create table my_table( d1 datetime, d2 date, d3 time, d4 timestamp , d5 year )charset utf8; -- 插入数据 ： 注意：（1） time 可以为负数 （2） year 可以用两位数插入 insert into my_table values ('2015-9-28 11:50:54', '2015-9-28', '11:50:54', '2015-9-28 11:50:54', 2015); -- 时间使用负数 insert into my_table values ('2015-9-28 11:50:54', '2015-9-28', '-11:50:54', '2015-9-28 11:50:54', 2015); insert into my_table values ('2015-9-28 11:50:54', '2015-9-28', '-2 11:50:54', '2015-9-28 11:50:54', 2015);-- 过去两天 -- year 可以使用两位 insert into my_table values ('2015-9-28 11:50:54', '2015-9-28', '-11:50:54', '2015-9-28 11:50:54', 69); insert into my_table values ('2015-9-28 11:50:54', '2015-9-28', '-11:50:54', '2015-9-28 11:50:54', 15); -- 当条目更新时，timestamp 字段会自动更新 -- 字符串类型 -- 定长字符串 char,磁盘（二维表）在定义的时候，就已经确定了最终数据的存储长度 char(L)：L代表length,可以存储的长度，单位字符，最大长度可以为255 char(4):在UTF8下，需要4 * 3 = 12 个字节 -- 变长字符串： varchar(L), L表示字符长度，理论长度是65536个字符，但是会多出1到2个字节来存储 -- 实际长度 varchar (10) 的确存了10个汉字，utf8环境 10 * 3 + 1 = 31 -- 当实际长度如果超过255，既不用定长，也不用变长，用文本字符串text -- 如何选择定长或变长字符串？？ -- 定长的磁盘空间比较浪费，但是效率高：如果数据基本上确定长度都一样，就使用定长 -- 如：身份证、电话号码、手机号码 -- 变长不浪费空间节省，但效率低：如果数据只能确定最大的长度，但具体长度有变化，如：姓名，家庭住址等 -- 文本字符串 如果数据量非常大，通常超过255个字符，就会使用文本字符串，文本字符串会根据存储格式分类： -- text 和 blob text : 存文本（二进制文件通常存储路径） blob : 存二进制数据（通常不用） -- 枚举：enum,事先将可能出现的结果都设计好，实际存储的数据必须是规定好的数据中的一个 --- 枚举使用方式： -- 定义：enum(可能出现的元素列表) -- 使用：存储数据，只能存储上面定义好的数据 -- 创建枚举表 create table my_enum( gender enum('男', '女', '保密') )charset utf8; -- 插入，枚举作用之一，规范数据 作用二，节省存储空间，枚举通常有一个别名，单选框， insert into my_enum values ('男'),('保密'); -- 有效数据 insert into my_enum values ('male'); -- 错误 ------- ***************----------------- 在mysql中，系统也会自动转换数据格式，枚举原理是：在进行数据规范（定义）时，系统会自动建立一个数字 与枚举元素的对应关系（关系放到日志中），然后在进行数据插入的时候，系统自动将字符串转换成相应 的数字存储，然后在进行数据提取的时候，系统会自动将数值转换成对应的字符串显示。 -- 枚举可以直接插入数值 insert into my_enum values (1), (2); -- 创建集合表 create table my_set( hobby set('篮球','排球','网球','足球') )charset utf8; -- 插入：既可以使用多个字符串，也可直接数值 insert into my_set values ('篮球,排球,网球'); insert into my_set values (3); -- 3 = 1 + 2 篮球加排球 -- 查看 select hobby + 0, hobby from my_set; +-----------+----------------+ | hobby + 0 | hobby | +-----------+----------------+ | 3 | 篮球,排球 | | 7 | 篮球,排球,网球 | +-----------+----------------+ -- 每一位用01（有点像chmod里面的权限设置），但是是反过来的，集合中的第一个在最终的数值里是最低位 -- 集合的强大在于能够规范数据和节省空间 -- mysql 记录长度 mysql规定，任何一条长度不能超过65535个字节（varchar 永远达不到理论值） varchar 的实际存储长度能达到多少呢？看字符集编码。UTF8下，varchar 的实际在utf8 21844 --- 求出varchar在utf8和gbk下的实际最大值 create table my_varchar_max_utf8( name varchar(65535) )charset utf8; -- ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); -- use BLOB or TEXT instead create table my_varchar_max_gbk( name varchar(65535) )charset gbk; -- ERROR 1074 (42000): Column length too big for column 'name' (max = 32767); -- use BLOB or TEXT instead -- 减小空间 create table my_varchar_max_utf8( name varchar(21845) -- 21845 * 3 = 65535, 但是varchar还需要一个存储长度的空间 2 字节 )charset utf8; -- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, -- not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs -- 这样是ok的 create table my_varchar_max_utf8( name varchar(21844) -- 21844 * 3 =65532 + 2 = 65534 )charset utf8; -- 补一个字节,从用户角度看，补齐65535个字节 create table my_varchar_max_2( int_1 tinyint, name varchar(21844) -- 21844 * 3 =65532 + 2 = 65534 )charset utf8; -- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, -- not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs -- *** 原因是：mysql记录中，如果有任何一个字段允许为空，那么系统会自动从整个记录 -- *** 中保留一个字节来存储null，如果想释放NULL所占用的字节，必须保证所有 -- *** 字段不允许为空。 -- 下面这样是 ok 的 create table my_varchar_max_2( int_1 tinyint not null, name varchar(21844) not null -- 21844 * 3 =65532 + 2 = 65534 )charset utf8; -- MySQL中text文本字符串不占用记录长度（额外存储），但是text文本字符串也是属于记录的一部分： -- 一定需要占据记录中的部分长度：10个字节（保存数据的地址以及长度） -- text占用是个字节 create table my_utftext( name varchar (21841) not null, -- 21841 * 3 + 2 = 65523 + 2 = 65525 content text )charset utf8; -- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, -- not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs -- 减去一个null,就成功了 create table my_utftext( name varchar (21841) not null, -- 21841 * 3 + 2 = 65523 + 2 = 65525 content text not null )charset utf8; -- *****列属性 -- 真正约束字段的是数据类型，但是数据类型的约束很单一，需要有一些额外的约束，来更加保证数据的合法性 -- * null/not null, -- 两个值，NULL(默认的)和NOT NULL(不为空) -- 虽然默认的，数据库基本都是字段为空，但是实际上在真实开发的时候， -- 要尽可能保证所有的数据都不应该为空，空数据没有意义，空数据没有办法参加运算 -- * comment -- 描述，没有实际意义，是用来专门描述字段的，会根据表创建语句保存，用来给数据库管理员来进行了解的 create table my_teacher( name varchar (20) not null comment '姓名', money decimal (10,2) not null comment '薪水' )charset utf8; -- * default -- 默认值，某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以自己设置 create table my_default( name varchar(20) not null, age tinyint unsigned default 0, gender enum('男', '女', '保密') default '男' )charset utf8; insert into my_default values ('刘', default , default ); insert into my_default(name) values ('刘'); -- * primary key -- 主键，一张表中只能有一个字段可以使用对应的主键，用来唯一的约束该字段里面的数据，不能重复，主键默认不为空 -- 一张表中最多有一个主键 -- 增加主键，sql中有三种方式给表增加主键： -- a.增加主键 create table my_pri1( name varchar (20) not null comment '姓名', number char (10) primary key comment '学号(不能重复)' )charset utf8; -- b.在创建表的时候，在所有的字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键， -- 可以使复合主键 create table my_pri2( number char(10) comment '学号', subject char(10) comment '课程代码', score tinyint unsigned default 60 comment '成绩', -- 增加主键限制 ： 学号 + 课程号 具有唯一性 primary key(number, subject) )charset utf8; +---------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+-------+ | number | char(10) | NO | PRI | | | | subject | char(10) | NO | PRI | | | | score | tinyint(3) unsigned | YES | | 60 | | +---------+---------------------+------+-----+---------+-------+ -- c.当表已经创建好之后，再次额外增加主键，可以通过修改字段属性，也可以直接追加 alter table 表名 add primary key(字段列表); -- 创建一个没有主键的表 create table my_pri3( subject char(10) not null comment '课程编号', name varchar(10) comment '课程名字' )charset utf8; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | subject | char(10) | NO | | NULL | | | name | varchar(10) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ -- 追加主键,前提是：表中字段对应的数据本身是独立的（不重复的） alter table my_pri3 modify subject char (10) primary key comment '课程编号'; alter table my_pri3 add primary key(subject); +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | subject | char(10) | NO | PRI | NULL | | | name | varchar(10) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ -- 主键的约束：主键对应的字段数据不允许重复，一旦重复，增和改操作失败，测试： -- 向pri1表插入数据 insert into my_pri1 values ('张三','07151111'), ('李四','07151112'); -- 成功 insert into my_pri2 values ('07151111', '0000', 60), ('07151112', '0001', 59); -- 成功 -- 插入冲突数据 insert into my_pri1 value ('王无', '07151111'); -- 失败 -- ERROR 1062 (23000): Duplicate entry '07151111' for key 'PRIMARY' insert into my_pri2 values ('07151111', '0000', 50), ('07151112', '0001', 30); -- ERROR 1062 (23000): Duplicate entry '07151111-0000' for key 'PRIMARY' -- 更新主键 & 删除主键：没有办法更新主键，主键必须先删除，才能增加 -- 删除主键 alter table 表名 drop primary key; -- 主键分类：在实际创建表的过程中，很少使用真实业务数据作为主键字段（业务主键，如学号，课程号），大部分的时候是 -- 使用逻辑性字段（字段没有业务含义，值是什么都没有关系），将这这种字段成为逻辑主键,因为主键的作用就是保证唯一， -- 所以只要能实现这个目的就是可以的 -- * auto_increment -- 自动增长，当对应的字段不给值、或者给默认值或者给null的时候，系统会自动从当前字段中已有的最大值在进行+1操作， -- 得到一个新的值，自增长通常是跟主键搭配 -- 自增长特点：auto_increment 1.任何一个字段要做自增长，必须本身是一个索引（key一栏有值） -- 2. 数字（整型） -- 3. （整型） create table my_auto( id int auto_increment comment '自动增长', name varchar (10) not null )charset utf8; -- ERROR 1075 (42000): Incorrect table definition; -- there can be only one auto column and it must be defined as a key (必须是key) create table my_auto( id varchar (1) primary key auto_increment comment '自动增长', name varchar (10) not null )charset utf8; -- ERROR 1063 (42000): Incorrect column specifier for column 'id' （必须是整型） create table my_auto( id int primary key auto_increment comment '自动增长', name varchar (10) not null )charset utf8; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ -- 自增长的使用，当自动增长被给定的值为null或不给的时候，会触发自动增长 insert into my_auto(name) values ('张三'); insert into my_auto values (null, '张三'); insert into my_auto values (default , '李四'); +----+------+ | id | name | +----+------+ | 1 | 张三 | | 2 | 张三 | | 3 | 李四 | +----+------+ -- 每次自增1，注意：：如果对应的字段输入了值，那么自增长失效，但是下一次还是能使能够正确 insert into my_auto values (6, '王五'); insert into my_auto values (null, '李六'); -- 删掉一部分 +----+------+ | id | name | +----+------+ | 1 | 张三 | | 2 | 张三 | +----+------+ -- 重新插入，会从原来的位置开始（之前插入了很多，过程中没写） +----+------+ | id | name | +----+------+ | 1 | 张三 | | 2 | 张三 | | 11 | 李四 | +----+------+ -- 确定下一次，show create table show create table my_auto; | Table | Create Table | +---------+------------------------------------------------- my_auto | CREATE TABLE `my_auto` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自动增长', `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 -- 下次是 12 -- 修改自增长：自增长如果是涉及到字段改变，必须先删除原来的自增长，然后增加（一张表只能有一个自增长） -- 修改当前自增长已经存在的值，修改只能比当前已有的自增长的最大值大，不能小（小不生效） alter table 表名 auto_increment = 值; alter table my_auto auto_increment = 4; -- 向下修改，不生效 my_auto | CREATE TABLE `my_auto` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自动增长', `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 | alter table my_auto auto_increment = 15; -- 向上修改， 生效 | my_auto | CREATE TABLE `my_auto` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自动增长', `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 | -- 自增长从1开始，每次增长1？？？所有系统的表现如字符集、校对集都是由系统内部的变量进行控制的 -- 查看自增长对应的变量值： show variables like 'auto_increment%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | --------- | auto_increment_offset | 1 | --------- +--------------------------+-------+ -- 修改变量可以实现不同的效果，修改是对整个数据库的修改，而不是单张表（修改是会话级别） set auto_increment_increment = 5; -- 修改自增初值 -- 删除自增长：自增长是字段的一个属性：可以通过modify来进行修改（保证字段没有auto_increment即可） alter 表名 modify 字段 类型; alter table my_auto modify id int primary key; -- 错误的：主键理论单独存在，此句被认为增加主键 -- ERROR 1068 (42000): Multiple primary key defined alter table my_auto modify id int; -- 正确的，只是改变了字段属性，数据还再。 | my_auto | CREATE TABLE `my_auto` ( `id` int(11) NOT NULL DEFAULT '0', `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- * unique key：唯一键，一张表往往需要很多字段需要具有唯一性，数据不能重复，但是一张表中只能有一个主键 -- 唯一键就可以解决表中有多个字段需要唯一性约束的问题 -- 唯一键的本质与主键差不多：唯一键默认的允许自动为空，而且可以多为空（空字段不参与唯一性比较） -- 增加唯一键：基本与主键差不多，有三种 -- a.在创建表的时候，字段之后直接跟 create table my_unique1( number char(10) unique comment '学号:唯一', name varchar (20) not null )charset utf8; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | number | char(10) | YES | UNI | NULL | | | name | varchar(20) | NO | | NULL | | +--------+-------------+------+-----+---------+-------+ -- b.在所有的字段之后增加unique(字段列表) create table my_unique2( number char(10) not null comment '学号', name varchar (20) not null, unique key(number) )charset utf8; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | number | char(10) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | +--------+-------------+------+-----+---------+-------+ -- 显示为pri:刚好是一个不为空的唯一键，而该表并没有主键 -- c.创建表之后增加唯一键 create table my_unique3( id int primary key auto_increment, number char(10) not null, name varchar (20) not null )charset utf8; -- 追加 alter table my_unique3 add unique key(number); +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | number | char(10) | NO | UNI | NULL | | | name | varchar(20) | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+ -- 唯一键作用 ： 唯一键与主键本质相同，唯一区别就是可以为空，如果不为空，则与主键相同，不能重复 insert into my_unique1 values (null, '张三'),('000999', '李四'),(null, '王五'); insert into my_unique1 values ('000999', '李四四'); --失败 -- ERROR 1062 (23000): Duplicate entry '000999' for key 'number' -- 更新唯一键 & 删除唯一键 -- 更新唯一键：先删除后新增（唯一键可以有多个，可以不删除） -- 删除唯一键 ： alter table 表名 drop unique key; -- 错误，因为唯一键有多个，数据库不认识 alter table 表名 drop index 索引名字; -- 唯一键默认使用字段名作为索引 alter table my_unique3 drop index number; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | number | char(10) | NO | | NULL | | | name | varchar(20) | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+ -- ** 索引 -- 几乎所有的索引都建立在字段之上，索引是系统根据某种算法，将已有的数据（或未来可能新增的数据），单独 -- 建立一个文件，文件能够实现快速的匹配数据，并且能够快速找到表中对应的记录。 -- 索引的意义： -- 1.提升查询数据的效率 -- 2.约束数据的有效性（唯一性等） -- 增加索引的前提条件：索引本身会产生索引文件（有时候有可能笔数据文件还大），会非常消耗磁盘空间。 -- 如果某个字段需要作为查询的条件经常使用，那么可以使用索引（一定会想办法增加）。 -- 如果某个字段需要进行数据的有效性约束，也可能使用索引（主键，唯一键）。 -- MySQL中提供了多种索引 -- 1. 主键索引 primary key -- 2. 唯一索引 unique key -- 3. 全文索引： fulltext index -- 4. 普通索引： index -- 全文索引：针对文章内部的关键字进行索引 -- 最大弊端在于：如何确定关键字 -- 英文很容易（英文单词与单词之间有空格）， -- 中文很难（没有空格，随意组合 分词：sphinx） -- ***************************数据库设计**************************** -- 关系（表与表）：将实体与实体的关系，反应到最终数据库表的设计上来：将关系分为三种： -- 一对一、一对多（多对一）和多对多（朋友圈） -- ** 一对一：一张表中的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。 学生表：姓名，性别，年龄，身高，体重，婚姻状况，籍贯，家庭住址，紧急联系人 对应到数据库： id(primary key)|姓名|性别|年龄|身高|体重|婚姻状况|籍贯|家庭住址|紧急联系人 表设计成以上形式，符合要求，其中姓名，性别，身高，体重使用常用数据，但是婚姻，籍贯，住址 和联系人不属于常用数据，但是我们习惯上用select * 查询，不常用的数据就会影响效率，实际又 不常用。 解决方案：将常用和不常用信息分开存储，分成两张表： *常用信息表* id(primary key)|姓名|性别|年龄|体重|身高| *不常用信息表*（保证不常用信息与常用信息一定能够对应上：找一个具有唯一性的字段来连接两张表） id(primary key)|婚姻|籍贯|地址|联系人| 一个常用表中的一条记录，永远只能在一张不常用表中匹配一条记录，反过来，一个不常用表的一条记录在常用 表中也只能匹配一条记录：一一对应关系 -- ** 一对多：一张表中有一条记录可以对应另外一张表中的多条记录，但是反过来，另外一张表 -- 中的一条记录只能对应第一张表的一条记录，这种关系就是一对多或多对一 老师与代课班级之间的关系： *老师表* id(primary key)|名字|年龄|性别|科目| *班级表* id(primary key)|年级|班号| 以上关系，一个老师可以在班级表中查找到多条记录（也可能是一条），但是一个班级只能被一个老师 带，是一种典型的一对多关系。 但是以上设计：解决了实体的设计表问题，但是没有解决实际关系问题：班级表找不到老师记录， 老师也找不到班级 解决方案：在班级表中增加一个字段指向老师表，因为班级能唯一匹配到一个老师表的记录 id(primary key)|年级|班号|老师id| -- * 多对多，一张表A中的一条记录能够对应另外一张表B中的多条记录，同时B表中的一条记录也能对应 -- 到A表多条记录，老师和学生 *老师表* T_id(primary key) | 姓名 | 性别| *学生表* S_id(primary key) | 姓名 | 性别| 以上设计方案：实现了实体的设计，但是没有维护实体的关系。 一个老师教过多个学生，一个学生也被多各老师教过 解决方案：增加一张新表，专门维护两张表之间的关系 *中间关系表* |ID | T_id | S_id | 增加中间表之后：中间表与老师表形成了一对多的关系，而且中间表示夺标，维护了能够 唯一找到一表的关系，同样的，学生表与中间表也是一个一对多的关系，一对多的关系可以匹配到 关联表之间的数据 学生找老师：找出S_id -> 中间表寻找匹配记录（多条） -> 老师表匹配（一条） 学生找老师：找出T_id -> 中间表寻找匹配记录（多条） -> 学生表匹配（一条） -- * 范式：Normal Format,是一种离散数学中的知识，是为了解决一种数据的存储与优化的问题 -- 保证数据存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储，终极目标是为了减少 -- 数据的冗余 -- 范式：是一种分层结构的规范，分为6层，每一层都比上一层更加严格，若要满足下一层范式， -- 前提是满足上一层范式。 -- 六层范式：1NF，2NF,...,6NF，其中，1NF是最低层，要求最低，6NF最高层，最严格 -- MySQL属于关系型数据库，有空间浪费，也是致力于节省存储空间，与范式所解决的问题不谋而合 -- 再设计数据库的时候，会利用到范式来指导设计。 -- 但是数据库不单是要解决空间问题，要保证效率问题，范式只为了解决空间问题，所以数据库的设计 -- 又不可能完全按照范式的眼球来实现，一般情况下，只有前三种范式需要满足。 -- 范式在数据库的设计中有指导意义，但是不是强制规范 -- ** 1NF，第一范式，在设计表存储数据的时候，如果表中设计的字段数据，在取出来使用之前 -- 还需要额外的处理（拆分），那么说表的设计不满足第一范式：第一范式要求字段数据具有原子性 -- 解决方案：字段拆离（主要看是够符合实际需求，没有绝对的原子非原子） -- ** 2NF，第二范式，在数据表设计的过程中，如果有复合主键（多字段主键），且表中有字段 -- 并不是由整个主键来确定，而是依赖主键中某个字段（主键的一部分），存在字段依赖之间的部分 -- 问题，称之为部分依赖，第二范式用来解决设计表不允许出现部分依赖 -- 一个不符合规范的表 create table not_sat_2NF( name varchar (20) not null comment '讲师姓名', gender enum('男','女'), `班级` varchar (10) not null comment '课程：编程语言+序号', `教室` char(4) comment 'A-D + 三位数字', `开始时间` datetime comment '这里用date更合理，不是关注点', `结束时间` datetime, primary key (name, `班级`) )charset utf8; insert into not_sat_2NF values ('张三', '男' , 'php260','A332', '2018-6-21 14:09:14', '2018-6-21 14:09:14'); +------+--------+--------+------+---------------------+---------------------+ | name | gender | 班级 | 教室 | 开始时间 | 结束时间 | +------+--------+--------+------+---------------------+---------------------+ | 张三 | 男 | php260 | A332 | 2018-06-21 14:09:14 | 2018-06-21 14:09:14 | +------+--------+--------+------+---------------------+---------------------+ 上面表中，因为老师没有办法作为独立主键，需要结合班级才能作为主键（复合主键，一个老师在一个 班永远只带一阶段课程），代课时间，开始和结束字段与单签代课主键（讲师和班级），但是性别并不 依赖班级，教室不依赖讲师，教室不依赖讲师，性别只依赖讲师，教室只依赖班级，出现了性别和教室 只依赖复合主键中的一部分，部分依赖，不符合第二范式。 解决方案：1.可以将性别与讲师单独成表，班级与教室也单独成表 2.主键取消复合主键，使用逻辑主键（多增加一个id） alter table not_sat_2NF drop primary key; alter table not_sat_2NF add id int not null auto_increment primary key first; -- ** 3NF 要满足第三范式，必须满足第二范式（无部分依赖），第一范式（原子性），理论上讲 -- 应该一张表中的所有字段都应该直接依赖主键（代表的是业务主键），如果表在设计中存在一个字段 -- 并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键，把这种不是直接依赖主键 -- 而是依赖非主键字段的依赖关系称之为传递依赖。 -- 第三范式就是要解决传递依赖问题，以上表格已经满足第一第二范式： +----+------+--------+--------+------+---------------------+---------------------+ | id | name | gender | 班级 | 教室 | 开始时间 | 结束时间 | +----+------+--------+--------+------+---------------------+---------------------+ | 1 | 张三 | 男 | php260 | A332 | 2018-06-21 14:09:14 | 2018-06-21 14:09:14 | +----+------+--------+--------+------+---------------------+---------------------+ 以上设计方案中，性别依赖讲师存在，讲师依赖主键，教室依赖班级，性别和教室都存在传递依赖 解决方案：将存在传递依赖的字段，以及依赖的字段本身单独取出 第一个：讲师表： |T_id|讲师姓名|性别| -- 其实ID=讲师 第二个：班级表： |class_id|班级|教室| -- class_id = 班级 --************因为逻辑主键本身没有实际意义，所以主要有逻辑主键，永远有传递依赖，他仅 -- 仅是保护数据安全，提高查询效率 -- ** 逆规范化：磁盘的利用率与效率的对抗 -- 有时候，再设计表的时候，如果一张表中有几个字段是需要从另外一张表中去获取信息，理论上讲， -- 的确可以获取到想要的数据，但是就是效率低一些，会刻意的在某些表中，不去保存另外表的主键 --（逻辑主键），而是直接保存想要的数据信息，这样一来，在查询的时候，一张表可以直接提供数据 -- 而不需要多表查询（效率低），但是会导致数据冗余增加。 ----------------*********************数据高级操作*********************** -- 围绕数据增删改查 -- * 新增数据：基本操作：insert into 表名 （字段列表） values(值列表) -- 在数据插入的时候，假设主键对应的值已经存在，插入一定会失败！ -- 主键冲突，当主键存在冲突的时候，可以选择性进行处理，更新和替换 -- 更新: insert into 表名（字段列表：包含主键）values(值列表） duplicate key update +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(20) | NO | | NULL | | | number | char(10) | NO | PRI | NULL | | +--------+-------------+------+-----+---------+-------+ 里面的数据为： +------+----------+ | name | number | +------+----------+ | 张三 | 07151111 | | 李四 | 07151112 | +------+----------+ insert into my_pri1 values ('张叁', '07151111'); -- ERROR 1062 (23000): Duplicate entry '07151111' for key 'PRIMARY' insert into my_pri1 values ('张叁', '07151111') on duplicate key update name='张叁'; +------+----------+ | name | number | +------+----------+ | 张叁 | 07151111 | | 李四 | 07151112 | +------+----------+ -- 替换 replace into 表名（字段列表：包含主键） values(列表) replace into my_pri1 values ('李思', '07151112'); +------+----------+ | name | number | +------+----------+ | 张叁 | 07151111 | | 李思 | 07151112 | +------+----------+ -- 使用replace，假设没有主键冲突，直接生效 replace into my_pri1 values ('王无', '07152222'); -- 蠕虫复制：从已有的数据中，去获取数据，然后又进行新增操作，导致的结果是数据成倍的增加。 -- 表创建的高级操作，从已有表创建新表。 create table 表名 like 表名; -- 只复制结构，不复制数据 -- 蠕虫赋值：先查数据，然后将查出的数据新增一遍,可以从别的表，也可以从自己的表 insert into 表名（字段列表） select 字段列表\* from 表名; -- 意义：1.从已有表拷贝数据到新表2.可以迅速让表中的数据膨胀到一定的数量级，测试表的压力及效率 -- 高级更新 -- 基本语法：update 表名 set 字段 = 值 where ; -- 高级新增语法：update 表名 set 字段 = 值 where [条件] limit 更新数量; 限制数量 -- **** 没有校对集，不区分大小写 -- * 删除数据 -- 与更新类似，可以通过limit来限制数量 delete form table where [条件] limit 数量; -- 清空表，重置自增长 truncate 表名; -- 先删除该表，再增加该表，数据没了 -- ****** 高级查询 -- 基本语法 select 字段列表 from 表名 [where 条件]; -- 完整语法 select [select 选项] 字段列表[字段别名] from 数据源 [where 条件子句][group by 子句] [having 子句] [order by 子句][limit 子句]; -- select 选项：select对查出来的结果的处理方式： all : 默认的，保留所有结果，即select * from table = select all * from table; distinct : 去重，查出来的结果，将重复的字段去除（所有字段都相同）,select distinct (字段列表) from table 是不能成立的 -- 字段别名 ： 当数据进行查询出来的时候（多表查询的时候）会有同名字段，需要对字段名进行重命名：别名 字段名 [as] 别名; select id, number as 学号, name as 姓名, sex as 性别 from table; -- 数据源：数据的来源，关系型数据的来源都是数据表，本质上只要保证数据类似二维表，最终都可以作为数据源 -- 数据源分为多种：单表数据源，多表数据源，查询语句。 -- 单表：上面的所有 -- 多表：表名,表名 select * from talbe1, table2; -- 默认是笛卡尔积，表一中的每条记录都要与另外一个表的全部连一次，没什么用，尽量避免 -- 子查询 select * from (select * from stu_info); -- ERROR 1248 (42000): Every derived table must have its own alias select * from (select * from stu_info) as s; -- 给个表名就正确了 -- where 子句，用来筛选数据，where 子句返回的结果：0或1，0代表false，1代表true -- 判断条件：<,>,<>,<=,>=,=,like,and,in,not in,between -- 逻辑运算符：&&(and)，||(or),!(not) where 原理：是唯一一个直接从磁盘获取数据的时候就开始判断的语句，从磁盘去除一条记录，开始运行where判断 判断的结果如果成立，则保存到内存，否则直接放弃。 select * from stu_info where 1; -- 全部查询 alter table stu_info add age tinyint unsigned; alter table stu_info add height tinyint unsigned; -- 给初值 update stu_info set age=floor(rand() * 20 + 20), height=floor(rand() * 20 + 170); +----+------+-------+---------------------+-------+------+--------+ | id | name | score | updata_time | name2 | age | height | +----+------+-------+---------------------+-------+------+--------+ | 2 | 刘 | 10 | 2018-06-21 21:16:52 | NULL | 20 | 173 | | 3 | | 20 | 2018-06-21 21:16:52 | NULL | 37 | 185 | | 4 | 刘 | NULL | 2018-06-21 21:16:52 | 刘 | 25 | 170 | +----+------+-------+---------------------+-------+------+--------+ -- 条件查询一：找出学生id = 1 或 3 或 5 的学生 select * from stu_info where id = 1 || id = 3 || id = 5; select * from stu_info where id in (1, 3, 5); -- 条件查询二： 查出区间落在身高在180，190之间 select * from stu_info where height >= 180 and height <= 190; select * from stu_info where height between 180 and 190; -- between 本身是闭区间，左边的一定要比右边的小，否则无效，因为内部默认替换成 >= and <= -- * group by 分组的意思，根据某个字段进行分组,不同的分到不同组 -- 根据性别分组 alter table stu_info add gender enum('男','女','保密'); insert into stu_info(name, score, name2, age, height, gender) (select name, score, name2, age, height, gender from stu_info); update stu_info set gender = (round(rand()) + 1); +----+------+-------+---------------------+-------+------+--------+--------+ | id | name | score | updata_time | name2 | age | height | gender | +----+------+-------+---------------------+-------+------+--------+--------+ | 2 | 刘 | 10 | 2018-06-21 21:39:03 | NULL | 20 | 173 | 男 | | 3 | | 20 | 2018-06-21 21:39:03 | NULL | 37 | 185 | 男 | | 4 | 刘 | NULL | 2018-06-21 21:42:36 | 刘 | 25 | 170 | 女 | | 5 | 刘 | 10 | 2018-06-21 21:42:36 | NULL | 20 | 173 | 女 | | 6 | | 20 | 2018-06-21 21:42:36 | NULL | 37 | 185 | 女 | | 7 | 刘 | NULL | 2018-06-21 21:39:03 | 刘 | 25 | 170 | 男 | +----+------+-------+---------------------+-------+------+--------+--------+ select * from stu_info group by gender; -- 结果如下 +----+------+-------+---------------------+-------+------+--------+--------+ | id | name | score | updata_time | name2 | age | height | gender | +----+------+-------+---------------------+-------+------+--------+--------+ | 2 | 刘 | 10 | 2018-06-21 21:39:03 | NULL | 20 | 173 | 男 | | 4 | 刘 | NULL | 2018-06-21 21:42:36 | 刘 | 25 | 170 | 女 | +----+------+-------+---------------------+-------+------+--------+--------+ -- 分组的意思：是为了统计数据（按组统计），按分组字段进行数据统计 -- count() ： 统计分组后的记录数，每一组有多少个 -- max() : 统计每组中最大的值 -- min() : 统计最小值 -- avg() : 均值 -- sum() : 统计和 -- 分组统计：身高、平均年龄和总年龄 select gender, count(*), max(height),min(height), avg(age), sum(age) from stu_info group by gender; +--------+----------+-------------+-------------+----------+----------+ | gender | count(*) | max(height) | min(height) | avg(age) | sum(age) | +--------+----------+-------------+-------------+----------+----------+ | 男 | 3 | 185 | 170 | 27.3333 | 82 | | 女 | 3 | 185 | 170 | 27.3333 | 82 | +--------+----------+-------------+-------------+----------+----------+ -- 对比原始数据，是正确的 -- count 函数，里面可以使用两种参数，*代表统计记录，字段名代表统计对应的字段，若为NULL，则统计时不加一 select gender, count(*), count(score), max(height),min(height), avg(age), sum(age) from stu_info group by gender;-- 男、女score中均有一个NULL，所以count(score)值为2 +--------+----------+--------------+-------------+-------------+----------+----------+ | gender | count(*) | count(score) | max(height) | min(height) | avg(age) | sum(age) | +--------+----------+--------------+-------------+-------------+----------+----------+ | 男 | 3 | 2 | 185 | 170 | 27.3333 | 82 | | 女 | 3 | 2 | 185 | 170 | 27.3333 | 82 | +--------+----------+--------------+-------------+-------------+----------+----------+ 表中结果会自动排序，男 -> 女 ，字符串字典排序，enum下标，默认升序。 group by 字段 [asc / desc] ; 对分组结果合并之后的整个结果进行排序 select gender, count(*), count(score), max(height),min(height), avg(age), sum(age) from stu_info group by gender desc;-- 男、女score中均有一个NULL，所以count(score)值为2 +--------+----------+--------------+-------------+-------------+----------+----------+ | gender | count(*) | count(score) | max(height) | min(height) | avg(age) | sum(age) | +--------+----------+--------------+-------------+-------------+----------+----------+ | 女 | 3 | 2 | 185 | 170 | 27.3333 | 82 | | 男 | 3 | 2 | 185 | 170 | 27.3333 | 82 | +--------+----------+--------------+-------------+-------------+----------+----------+ 多字段分组，先根据一个字段进行分组，然后对分组后的结果进行再次分组。 select name, gender, count(*) from stu_info group by name, gender; +------+--------+----------+ | name | gender | count(*) | +------+--------+----------+ | 刘 | 男 | 2 | -- name 为 刘的 男的有2个 | 刘 | 女 | 2 | -- name 为 刘 的 女的 有两个 | | 男 | 1 | -- name 为 的男的 1 个 | | 女 | 1 | -- name 为 的女的 1 个 +------+--------+----------+ -- 有一个函数，可以对分组的结果中的某个字段进行字符串连接（保留该组的所有的某个字段）。 group_concat(字段) +----+------+-------+---------------------+------+--------+--------+--------+ | id | name | score | updata_time | age | height | gender | class | +----+------+-------+---------------------+------+--------+--------+--------+ | 2 | 张三 | 11 | 2018-06-21 22:15:12 | 20 | 173 | 男 | class1 | | 3 | 李四 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 男 | class2 | | 4 | 王五 | 15 | 2018-06-21 22:15:12 | 25 | 170 | 女 | class1 | | 5 | 赵柳 | 10 | 2018-06-21 22:15:12 | 20 | 173 | 女 | class3 | | 6 | 泮七 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 女 | class2 | | 7 | 徐八 | 100 | 2018-06-21 22:15:12 | 25 | 170 | 男 | class2 | +----+------+-------+---------------------+------+--------+--------+--------+ select class, gender, count(*), group_concat(name) from stu_info group by class, gender; +--------+--------+----------+--------------------+ | class | gender | count(*) | group_concat(name) | +--------+--------+----------+--------------------+ | class1 | 男 | 1 | 张三 | -- class1 男生 1 个，叫张三 | class1 | 女 | 1 | 王五 | -- class1 女生 1 个，叫王五 | class2 | 男 | 2 | 李四,徐八 | --... | class2 | 女 | 1 | 泮七 | | class3 | 女 | 1 | 赵柳 | +--------+--------+----------+--------------------+ -- * 回溯统计：with rollup;任何一个分组后都会有一个分组，最后都需要想上级分组进行汇报统计 -- 根据当前分组的字段，这就是回溯统计：回溯统计的时候会将分组字段置空 select class, count(*) from stu_info group by class; +--------+----------+ | class | count(*) | +--------+----------+ | class1 | 2 | | class2 | 3 | | class3 | 1 | +--------+----------+ -- 回溯统计 select class, count(*) from stu_info group by class with rollup; +--------+----------+ | class | count(*) | +--------+----------+ | class1 | 2 | | class2 | 3 | | class3 | 1 | | NULL | 6 | +--------+----------+ -- 多字段分组回溯统计 select class, gender, count(*), group_concat(name) from stu_info group by class, gender with rollup; +--------+--------+----------+-------------------------------+ | class | gender | count(*) | group_concat(name) | +--------+--------+----------+-------------------------------+ | class1 | 男 | 1 | 张三 | | class1 | 女 | 1 | 王五 | | class1 | NULL | 2 | 张三,王五 | | class2 | 男 | 2 | 李四,徐八 | | class2 | 女 | 1 | 泮七 | | class2 | NULL | 3 | 李四,徐八,泮七 | | class3 | 女 | 1 | 赵柳 | | class3 | NULL | 1 | 赵柳 | | NULL | NULL | 6 | 张三,王五,李四,徐八,泮七,赵柳 | +--------+--------+----------+-------------------------------+ -- having 子句，与where子句一样：进行条件判断的，where是针对磁盘数据进行判断，进入内存后， -- 会进行分组等其他操作，分组结果就需要having来处理。 -- having可以做where几乎所有事情，但是where却不能做having能做的很多事 1.分组统计的结果的处理，只能用having来做 -- 求出每个班人数大于等于2的学生人数 select class, count(*), group_concat(name) from stu_info group by class having count(*) >= 2; +--------+----------+--------------------+ | class | count(*) | group_concat(name) | +--------+----------+--------------------+ | class1 | 2 | 张三,王五 | | class2 | 3 | 李四,泮七,徐八 | +--------+----------+--------------------+ 2.having 能使用字段别名，where不能，因为where是从磁盘取数据，二名字只有在内存中才能产生 -- 优化 select class, count(*) as tatal , group_concat(name) from stu_info group by class having tatal >= 2; +--------+-------+--------------------+ | class | tatal | group_concat(name) | +--------+-------+--------------------+ | class1 | 2 | 张三,王五 | | class2 | 3 | 李四,泮七,徐八 | +--------+-------+--------------------+ -- order by 排序，根据某个字段升序或降序排列，依赖校对集 order by 字段名 [asc desc]; -- 可以根据多个字段排序，先跟据某个字段排序，然后排序好的内容，再按照某个数据继续排序 select * from stu_info order by class, gender; +----+------+-------+---------------------+------+--------+--------+--------+ | id | name | score | updata_time | age | height | gender | class | +----+------+-------+---------------------+------+--------+--------+--------+ | 2 | 张三 | 11 | 2018-06-21 22:15:12 | 20 | 173 | 男 | class1 | | 4 | 王五 | 15 | 2018-06-21 22:15:12 | 25 | 170 | 女 | class1 | | 3 | 李四 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 男 | class2 | | 7 | 徐八 | 100 | 2018-06-21 22:15:12 | 25 | 170 | 男 | class2 | | 6 | 泮七 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 女 | class2 | | 5 | 赵柳 | 10 | 2018-06-21 22:15:12 | 20 | 173 | 女 | class3 | +----+------+-------+---------------------+------+--------+--------+--------+ select * from stu_info order by class, gender desc; +----+------+-------+---------------------+------+--------+--------+--------+ | id | name | score | updata_time | age | height | gender | class | +----+------+-------+---------------------+------+--------+--------+--------+ | 4 | 王五 | 15 | 2018-06-21 22:15:12 | 25 | 170 | 女 | class1 | | 2 | 张三 | 11 | 2018-06-21 22:15:12 | 20 | 173 | 男 | class1 | | 6 | 泮七 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 女 | class2 | | 3 | 李四 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 男 | class2 | | 7 | 徐八 | 100 | 2018-06-21 22:15:12 | 25 | 170 | 男 | class2 | | 5 | 赵柳 | 10 | 2018-06-21 22:15:12 | 20 | 173 | 女 | class3 | select * from stu_info order by class desc, gender desc; +----+------+-------+---------------------+------+--------+--------+--------+ | id | name | score | updata_time | age | height | gender | class | +----+------+-------+---------------------+------+--------+--------+--------+ | 5 | 赵柳 | 10 | 2018-06-21 22:15:12 | 20 | 173 | 女 | class3 | | 6 | 泮七 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 女 | class2 | | 3 | 李四 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 男 | class2 | | 7 | 徐八 | 100 | 2018-06-21 22:15:12 | 25 | 170 | 男 | class2 | | 4 | 王五 | 15 | 2018-06-21 22:15:12 | 25 | 170 | 女 | class1 | | 2 | 张三 | 11 | 2018-06-21 22:15:12 | 20 | 173 | 男 | class1 | +----+------+-------+---------------------+------+--------+--------+--------+ -- limit：主要用来实现数据的分页，为用户节省空间，提交服务器的响应效率 -- 减少资源的浪费 -- 方案一：只用来限制长度：limit 数据量 -- 方案二：限制起始位置、步长，主要作用，用来分页 -- 对于用户来讲：可以点击的分页按钮1，2，3，4 -- 对于服务器：分局用户选择的页码来获取不同的数据：limit offset,length， 记录数从0编号 -- length : 每页显示的数据量：基本不变 -- ************回顾 -- 列属性：主键，一般搭配自增长，主键、自增长每个表都只有一个，唯一键，有就不能重复 -- 关系：一对一，一对多，多对多 -- 范式：三层，一层，字段设计原子性；二层，不能存在部分依赖，主要没有复合主键就不会产生 -- 三层，不存在传递依赖（单独建表） -- 逆规范化：效率与磁盘空间的博弈 -- 高级数据操作： -- 主键冲突 （更新、替换），蠕虫复制 -- 更新操作，limit限制数量 -- 删除操作，limit,清空表（truncate） -- 查询操作，select选项（all，distinct）字段别名， -- 数据源（单表，多表，子查询），where子句（条件判断：从磁盘） -- groupby子句（分组统计，统计函数，分组排序，多字段分组，回溯统计） -- having子句(针对分组统计的结果进行判断)，order by子句（排序，多字段排序） -- limit子句（限制记录数，分页） -- 连接查询：将多张表（可以大于2张）进行记录的连接（按照某个指定的条件进行数据的拼接） -- join, 左边的表 join 右边的表 -- 最终结果：记录数有可能变化，字段是一定会增加（至少两张表合并） -- 连接查询的意义：用户查看数据的时候需要显示的暑假来自多张表 -- 连接查询分类，SQL中将连接查询分成四类，内连接、外连接、自然连接、交叉连接 -- 交叉连接，cross join，从一张表中循环取出每一条记录，每一条记录都去另外一张表 -- 进行匹配，并且匹配一定保留（无条件匹配），而连接本身字段就会增加， -- 最终形成结果叫做：笛卡尔乘积 -- 基本语法： 左表 cross join 右表; -- 等效于 from 左表，右表 -- 笛卡尔积没有意义，交叉连接存在的价值是保证连接这种结构的完整性 -- 内连接，inner join，从左表中取出每一条记录，去右表中与所有记录进行匹配， -- 匹配必须是某个条件在左表中与右表中相同，最终才会保留结果，否则不会保留 -- 基本语法：左表 [inner] join on 左表.字段 = 右表.字段; on 表是连接连接，条件字段就是代表相同的业务含义 +------------+-----------------+--------+---------------------+---------------------+---------------------+ | seckill_id | name | number | create_time | start_time | end_time | +------------+-----------------+--------+---------------------+---------------------+---------------------+ | 1000 | 1000秒杀iphone | 96 | 2018-04-30 14:29:22 | 2019-11-01 00:00:00 | 2019-11-02 00:00:00 | | 1001 | 1000秒杀iphone2 | 999 | 2018-04-30 14:29:22 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | | 1002 | 1000秒杀iphone3 | 50 | 2018-04-30 14:29:22 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | | 1003 | 1000秒杀iphone4 | 20 | 2018-04-30 14:29:22 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | | 1004 | 1000秒杀iphone | 100 | 2018-05-01 14:18:33 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | | 1005 | 1000秒杀iphone2 | 1000 | 2018-05-01 14:18:33 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | | 1006 | 1000秒杀iphone3 | 50 | 2018-05-01 14:18:33 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | | 1007 | 1000秒杀iphone4 | 20 | 2018-05-01 14:18:33 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | +------------+-----------------+--------+---------------------+---------------------+---------------------+ +------------+-------------+-------+---------------------+ | seckill_id | user_phone | state | create_time | +------------+-------------+-------+---------------------+ | 1000 | 15209202418 | 0 | 2018-05-20 20:06:11 | | 1000 | 15209202419 | 0 | 2018-05-20 20:03:39 | | 1001 | 15209202416 | 0 | 2018-05-20 20:12:43 | +------------+-------------+-------+---------------------+ select * from seckill_info inner join success_kill on seckill_info.seckill_id = success_kill.seckill_id; +------------+-----------------+--------+---------------------+---------------------+---------------------+------------+-------------+-------+---------------------+ | seckill_id | name | number | create_time | start_time | end_time | seckill_id | user_phone | state | create_time | +------------+-----------------+--------+---------------------+---------------------+---------------------+------------+-------------+-------+---------------------+ | 1000 | 1000秒杀iphone | 96 | 2018-04-30 14:29:22 | 2019-11-01 00:00:00 | 2019-11-02 00:00:00 | 1000 | 15209202418 | 0 | 2018-05-20 20:06:11 | | 1000 | 1000秒杀iphone | 96 | 2018-04-30 14:29:22 | 2019-11-01 00:00:00 | 2019-11-02 00:00:00 | 1000 | 15209202419 | 0 | 2018-05-20 20:03:39 | | 1001 | 1000秒杀iphone2 | 999 | 2018-04-30 14:29:22 | 2015-11-01 00:00:00 | 2019-11-02 00:00:00 | 1001 | 15209202416 | 0 | 2018-05-20 20:12:43 | +------------+-----------------+--------+---------------------+---------------------+---------------------+------------+-------------+-------+---------------------+ 字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要加上表名才能区分，而表名太长，通常可以使用别名 select c.user_phone as '联系方式', s.name as '秒杀商品', c.create_time as '秒杀时间' from seckill_info as s inner join success_kill as c on s.seckill_id = c.seckill_id; +-------------+-----------------+---------------------+ | 联系方式 | 秒杀商品 | 秒杀时间 | +-------------+-----------------+---------------------+ | 15209202419 | 1000秒杀iphone | 2018-05-20 20:03:39 | | 15209202418 | 1000秒杀iphone | 2018-05-20 20:06:11 | | 15209202416 | 1000秒杀iphone2 | 2018-05-20 20:12:43 | +-------------+-----------------+---------------------+ -- 内连接可以内有连接条件，系统会保留所有内容，笛卡尔成绩 -- on可以用where替代，但是where没有on效率高（where会一个一个匹配，在选，on先筛选，再匹配） select c.user_phone as '联系方式', s.name as '秒杀商品', c.create_time as '秒杀时间' from seckill_info as s inner join success_kill as c where s.seckill_id = c.seckill_id; -- 外连接，outer join，以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留 -- 能匹配，正确保留，不能匹配，其他表的字段都置空NULL。 -- 外连接分为两种，是以某张表为主，有主表 -- left join 左外连接（左连接），以左表为主表 -- right join 右连接，以右表为主 select c.user_phone as '联系方式', s.name as '秒杀商品', c.create_time as '秒杀时间' from seckill_info as s left join success_kill as c on s.seckill_id = c.seckill_id; +-------------+-----------------+---------------------+ | 联系方式 | 秒杀商品 | 秒杀时间 | +-------------+-----------------+---------------------+ | 15209202418 | 1000秒杀iphone | 2018-05-20 20:06:11 | | 15209202419 | 1000秒杀iphone | 2018-05-20 20:03:39 | | 15209202416 | 1000秒杀iphone2 | 2018-05-20 20:12:43 | | NULL | 1000秒杀iphone3 | NULL | | NULL | 1000秒杀iphone4 | NULL | | NULL | 1000秒杀iphone | NULL | | NULL | 1000秒杀iphone2 | NULL | | NULL | 1000秒杀iphone3 | NULL | | NULL | 1000秒杀iphone4 | NULL | +-------------+-----------------+---------------------+ -- 自然连接，nature join，就是是自动匹配连接条件，系统以字段名字作为匹配 -- 模式，同名字段作为条件，多个同名字都作为条件。 -- 自然连接，可以分为自然内连接，自然外连接. -- 自然内连接： select * from seckill_info natural join success_kill;-- 根据同名字段自动匹配，如果有多个，则多个同时作为条件 -- 自然外连接，左外 select * from seckill_info natural left join success_kill; ----------------------回顾------------------------------ -- 连接查询：多张表连接到一起，不管记录数如何，字段数一定会增加 -- 分类：内连接、外连接、自然连接、交叉连接 -- 交叉连接：cross join(笛卡尔） -- 内连接：inner join，左右两张表中有连接条件（不匹配忽略） -- 外连接：outer[left/right] join，主表有的记录一定会存在，匹配了就保留副表字段数据，没匹配置空 -- 自然连接：natural join，自动匹配条件（相同的字段名），using关键字 -- 外键,表中一个字段指向另外一个 表的主键 -- 创建时增加，在所有表字段后，使用foreign key （字段名） references 外表（字段） create table my_foreign1( group_id int primary key auto_increment, name varchar(20) not null comment '队伍名', stu_id int comment '学生id', foreign key (stu_id) references stu_info(id) )charset utf8; | my_foreign1 | CREATE TABLE `my_foreign1` ( `group_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL COMMENT '队伍名', `stu_id` int(11) DEFAULT NULL COMMENT '学生id', PRIMARY KEY (`group_id`), KEY `stu_id` (`stu_id`), CONSTRAINT `my_foreign1_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `stu_info` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | -- 外键要求字段身，必须是一个索引，若不指定，会自动命名外键索引 -- 增加外键，在新表增加后，修改表结构 alter table 表名 add [constraint 外键名字]foreign key （外键字段） references 外表（字段） create table my_foreign2( group_id int primary key auto_increment, name varchar(20) not null comment '队伍名', stu_id int comment '学生id' )charset utf8; -- 增加外键 alter table my_foreign2 add -- 指定外键名 constraint stu_id_1 -- 外键 foreign key (stu_id) references stu_info(id); -- 外键不可修改，外键删除，要根据外键名字删除，因为外键不唯一，不能像删除主键一样删除 alter table 表名 drop foreign key 外键名称; alter table my_foreign1 drop foreign key my_foreign1_ibfk_1; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | group_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | stu_id | int(11) | YES | MUL | NULL | | +----------+-------------+------+-----+---------+----------------+ | my_foreign1 | CREATE TABLE `my_foreign1` ( `group_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL COMMENT '队伍名', `stu_id` int(11) DEFAULT NULL COMMENT '学生id', PRIMARY KEY (`group_id`), KEY `stu_id` (`stu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | --外键已经删掉了，但原来字段依然保留为普通索引，即外键删除无法通过 --desc查看结果 -- 外键作用，默认有两点，一个对父表（被指向的表），一个队子表（外键字段所在的表） -- 对子表约束，写操作，若父表没有对应条目，写操作失败。 -----------父表--------------- +----+------+-------+---------------------+------+--------+--------+--------+ | id | name | score | updata_time | age | height | gender | class | +----+------+-------+---------------------+------+--------+--------+--------+ | 2 | 张三 | 11 | 2018-06-21 22:15:12 | 20 | 173 | 男 | class1 | | 3 | 李四 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 男 | class2 | | 4 | 王五 | 15 | 2018-06-21 22:15:12 | 25 | 170 | 女 | class1 | | 5 | 赵柳 | 10 | 2018-06-21 22:15:12 | 20 | 173 | 女 | class3 | | 6 | 泮七 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 女 | class2 | | 7 | 徐八 | 100 | 2018-06-21 22:15:12 | 25 | 170 | 男 | class2 | +----+------+-------+---------------------+------+--------+--------+--------+ insert into my_foreign2 values (null, 'Group1', 8); -- 新增一个不存在的条目 -- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`stuinfo`.`my_foreign2`, -- CONSTRAINT `stu_id_1` FOREIGN KEY (`stu_id`) REFERENCES `stu_info` (`id`)) insert into my_foreign2 values (null, 'Group1', 2); -- 对父表约束：父表数据进行写操作（删和写，都必须涉及到主键本身），如果对应的主键在子表中已经被数据所引用，那么就不允许操作 update stu_info set id=9 where id = 2; -- ERROR 1451 (23000): Cannot delete or update a parent row: -- a foreign key constraint fails (`stuinfo`.`my_foreign2`, -- CONSTRAINT `stu_id_1` FOREIGN KEY (`stu_id`) REFERENCES `stu_info` (`id`)) update stu_info set id = 9 where id = 7; -- 可以修改成功 -- 外键条件：1.外键必须要保证表的存储引擎是innodb（默认存储引擎），如果不是innodb -- 那么也可以创建成功，但是没有约束效果 -- 2.外键字段的字段类型（列类型），必须与父表的主键类型完全一致 -- 3.一张表中外键名字不能重复 -- 4.增加外键的字段（数据已经存在），必须保证数据与父表主键对应 -- 外键约束，就是指外键作用，之前所讲的是默认的作用，可以通过对外键的需求进行定制操作。 -- 外键约束有三种模式： -- district 严格模式（默认的），父表不能删除或更新已经被子表数据引用的记录 -- cascade 级联模式，父表的操作，对应子表关联的数据也跟着操作 -- setnull 置空模式，父表操作之后，子表对应的数据（外键字段）被置空 -- 通常合理的做法：删除的时候，子表置空，更新的时候，级联 foreign key (外键字段) references 父表（主键字段） on delete set null on update cascade ; -- 删除置空的前提条件，外键字段允许为空（如果不满条件，外键无法创建成功） -- 联合查询，将多次查询（多条select语句），在记录上进行拼接（字段不会增加） -- 基本语法：多条select语句构成，每一条select语句获得的字段数量必须严格一致（但是与字段类型无关） select 语句1 union [union 选项] select 语句2 ... union 选项：与select选项一样有两个：all , distinct (默认) 联合查询只要求字段数一样，数据类型不要求，最后字段名保存前面的 select id, name, score from stu_info union select group_id, name, stu_id from my_foreign2; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 2 | 张三 | 11 | | 3 | 李四 | 20 | | 4 | 王五 | 15 | | 5 | 赵柳 | 10 | | 6 | 泮七 | 20 | | 9 | 徐八 | 100 | | 2 | Group1 | 2 | +----+--------+-------+ -- 意义：1.查询同一张表，但是需求不同，如查询学生信息，身高升序，女生身高降序 -- 2.多表查询，多张表的结构是完全一样的，保存的数据（结构）也是一样的（分表，提升效率） -- 查询学生信息，男生升序，女生降序 select * from stu_info where gender = '男' order by age asc union select * from stu_info where gender = '女' order by age desc; -- ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY -- 在联合查询中,有ordby必须括起来 (select * from stu_info where gender = '男' order by age asc) union (select * from stu_info where gender = '女' order by age desc); +----+------+-------+---------------------+------+--------+--------+--------+ | id | name | score | updata_time | age | height | gender | class | +----+------+-------+---------------------+------+--------+--------+--------+ | 2 | 张三 | 11 | 2018-06-21 22:15:12 | 20 | 173 | 男 | class1 | | 3 | 李四 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 男 | class2 | | 9 | 徐八 | 100 | 2018-06-24 10:03:56 | 25 | 170 | 男 | class2 | | 4 | 王五 | 15 | 2018-06-21 22:15:12 | 25 | 170 | 女 | class1 | | 5 | 赵柳 | 10 | 2018-06-21 22:15:12 | 20 | 173 | 女 | class3 | | 6 | 泮七 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 女 | class2 | +----+------+-------+---------------------+------+--------+--------+--------+ -- 在联合查询中，让order by 生效，必须用limit (select * from stu_info where gender = '男' order by age asc limit 999999) union (select * from stu_info where gender = '女' order by age desc limit 999999); +----+------+-------+---------------------+------+--------+--------+--------+ | id | name | score | updata_time | age | height | gender | class | +----+------+-------+---------------------+------+--------+--------+--------+ | 2 | 张三 | 11 | 2018-06-21 22:15:12 | 20 | 173 | 男 | class1 | | 9 | 徐八 | 100 | 2018-06-24 10:03:56 | 25 | 170 | 男 | class2 | | 3 | 李四 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 男 | class2 | | 6 | 泮七 | 20 | 2018-06-21 22:15:12 | 37 | 185 | 女 | class2 | | 4 | 王五 | 15 | 2018-06-21 22:15:12 | 25 | 170 | 女 | class1 | | 5 | 赵柳 | 10 | 2018-06-21 22:15:12 | 20 | 173 | 女 | class3 | +----+------+-------+---------------------+------+--------+--------+--------+ -- 子查询，sub query，查询实在某个查询结果之上进行的（一条select语句内部包含另外一条select语句） -- 子查询分类有两种分类方式，按位置分类，按结果分类 -- 按位置分类：子查询（select语句）在外部查询（select语句）中出现的位置 -- from 子查询：子查询出现在from之后 -- where 子查询，子查询出现在where条件中 -- exists 子查询，子查询出现在exists里面 --按结果分类：根据子查询得到的数据进行分类（理论上讲，任何一个查询得到的结果都可以理解为二维表） -- 标量子查询：子查询得到的结果是一行一列（出现在where之后） -- 列子查询：子查询得到的结果是一列多行（出现在where之后） -- 行子查询：子查询得到结果是多列，一行或多行多列（出现在where之后） -- 表子查询：子查询得到的结果是多行多列（出现的位置是在from之后） -- 标量子查询 -- 知道班级名字为class1，想获取该班所有学生 1.确定数据源，获取所有的学生 select * from stu_info where id = ?; 2.获取班级ID，可通过班级名字确定 select id from my_calss where class_name = 'class1'; 即：select * from stu_info where id = (select id from my_class where class_name='class1'); -- 列子查询，一般返回的结果比较多，一列多行，需要使用in作为条件匹配，其实在MySQL中还有几个类似条件 -- all,some,any, =any() === in , any = some, =all | id | number | name | gender | age | c_id | +----+---------+------+--------+------+------+ | 1 | 7150000 | 张三 | 男 | 18 | 1 | | 2 | 7150001 | 李四 | 女 | 19 | 2 | | 3 | 7150002 | 王五 | 女 | 19 | 2 | | 4 | 7150003 | 赵柳 | 男 | 18 | 3 | | 5 | 7150004 | 小名 | 男 | 18 | 3 | | 6 | 7150004 | 小刚 | 女 | 18 | 1 | +----+---------+------+--------+------+------+ +----+------------+----------+ | id | class_name | room_add | +----+------------+----------+ | 1 | java001 | a330 | | 2 | php002 | a331 | | 3 | pyhon003 | a332 | +----+------------+----------+ -- 查询所有在读班级的学生（班级表中存在的班级） 1.确定数据源：学生 select * from my_stu where c_id in(?); 2.确定有效班级的id，所有班级id select id from my_class; select * from my_stu where c_id in(select id from my_class); -- any, some, all insert into my_stu values(null, '0715005', '小芳', '女', 16,5); mysql> select * from my_stu where c_id =any (select id from my_class); +----+---------+------+--------+------+------+ | id | number | name | gender | age | c_id | +----+---------+------+--------+------+------+ | 1 | 7150000 | 张三 | 男 | 18 | 1 | | 2 | 7150001 | 李四 | 女 | 19 | 2 | | 3 | 7150002 | 王五 | 女 | 19 | 2 | | 4 | 7150003 | 赵柳 | 男 | 18 | 3 | | 5 | 7150004 | 小名 | 男 | 18 | 3 | | 6 | 7150004 | 小刚 | 女 | 18 | 1 | +----+---------+------+--------+------+------+ 6 rows in set (0.00 sec) mysql> select * from my_stu where c_id =some (select id from my_class); +----+---------+------+--------+------+------+ | id | number | name | gender | age | c_id | +----+---------+------+--------+------+------+ | 1 | 7150000 | 张三 | 男 | 18 | 1 | | 2 | 7150001 | 李四 | 女 | 19 | 2 | | 3 | 7150002 | 王五 | 女 | 19 | 2 | | 4 | 7150003 | 赵柳 | 男 | 18 | 3 | | 5 | 7150004 | 小名 | 男 | 18 | 3 | | 6 | 7150004 | 小刚 | 女 | 18 | 1 | +----+---------+------+--------+------+------+ 6 rows in set (0.00 sec) mysql> select * from my_stu where c_id =all (select id from my_class); Empty set (0.00 sec) --- 否定结果 select * from my_stu where c_id !=any (select id from my_class); -- 所有结果 Null除外，与后面的所有进行比较，有一个不等于的就为true select * from my_stu where c_id !=some (select id from my_class); -- 所有结果， null除外 select * from my_stu where c_id !=all (select id from my_class); -- 和所有的比较，都不相等，就为true -- 行子查询，返回的结果可以是多行多列（一行多列） -- 需求，要求查询整个学生中，年龄最大同时身高最高的学生 alter my_stu add height tinyint unsigned; update my_stu set height = round(rand() * 50 + 150); 1.确定数据源 select * from my_stu where age = (?) and height = ?; 2.确定最大年龄和最高的身高 select max(age), max (height) from my_student; select * from my_stu where age = (select max(age) from my_stu) and height = (select max(height) from my_stu); -- 行子查询，需要构造行元素 select * from my_stu where -- (age, height)称之为行元素 (age, height) = (select max(age), max(height) from my_stu);-- max后面千万别加空格 -- 表子查询，子查询返回的结果是个多行多列的二维表，子查询返回的结果是当做二维表来使用 -- 需求，找出每个班最高的学生 -- 1.确定数据源，按照学生身高进行排序 select * from my_stu order by height desc; -- 2. select * from -- 构建表 (select * from my_stu order by height desc) as student group by c_id; -- exists子查询，用来判断某些条件是否满足（跨表），exists是接在where之后，exists返回的结果只有0和1 -- 需求，查所有的学生，前提是班级存在 1.确定数据源 select * from my_stu where ?; 2.确定条件是够满足 exists (select * from my_class); select * from my_stu where exists (select * from my_class); ----------------------视图------------------------------ -- view，是一种有结构（有二维表结构），但是没结果（结构中不真实存放数据）的虚拟表， -- 虚拟表的结构来源不是自己定义，而是从对应的基表中产生（视图的数据来源）. -- 创建视图 create view 视图名字 as select 语句;-- select 语句可以是普通查询，可以是连接查询，可以是联合查询，可以是子查询 -- 创建单表视图：基表只有一个 create view V1 as select * from my_stu; create view V2 as select * from my_class; -- 字段名重复的问题,会导致创建失败 create view V3 as select * from my_stu as s left join my_class c on s.c_id = c.id; -- 改成 create view V3 as select s.*, c.class_name, c.room_add from my_stu as s left join my_class c on s.c_id = c.id; -- 查看视图，查看视图的结构，视图是一张虚拟表，表的所有查看方式都适用于视图 show tables; desc V1; show create table V1; -- 视图比表还是有一个关键字区别,view,查看视图结构的创建语句可以用view关键字代替table show create view V2; -- 视图一旦创建，系统会在视图对应的数据库文件夹下创建一个对应的结构文件：frm -- 视图的使用 -- 视图主要是为了查询，将视图当做表一样查询即可 -- 视图使用： select * from V1; select * from V2; select * from V3; mysql> select * from V3; +----+---------+------+--------+------+------+--------+------------+----------+ | id | number | name | gender | age | c_id | height | class_name | room_add | +----+---------+------+--------+------+------+--------+------------+----------+ | 1 | 7150000 | 张三 | 男 | 18 | 1 | 188 | java001 | a330 | | 2 | 7150001 | 李四 | 女 | 19 | 2 | 176 | php002 | a331 | | 3 | 7150002 | 王五 | 女 | 19 | 2 | 165 | php002 | a331 | | 4 | 7150003 | 赵柳 | 男 | 18 | 3 | 195 | pyhon003 | a332 | | 5 | 7150004 | 小名 | 男 | 18 | 3 | 183 | pyhon003 | a332 | | 6 | 7150004 | 小刚 | 女 | 18 | 1 | 179 | java001 | a330 | | 7 | 0715005 | 小芳 | 女 | 16 | 5 | 198 | NULL | NULL | +----+---------+------+--------+------+------+--------+------------+----------+ -- 视图的执行，本质就是执行封装的select语句。 -- 视图的修改 -- 视图本身不可修改，但是视图的来源是可以修改的，修改视图，修改视图本身的来源语句（select语句） alter view 视图名字 as 新的select语句 alter view V1 as select id, number, name gender from my_stu; mysql> select * from V1; +----+---------+--------+ | id | number | gender | +----+---------+--------+ | 1 | 7150000 | 张三 | | 2 | 7150001 | 李四 | | 3 | 7150002 | 王五 | | 4 | 7150003 | 赵柳 | | 5 | 7150004 | 小名 | | 6 | 7150004 | 小刚 | | 7 | 0715005 | 小芳 | +----+---------+--------+ -- 视图删除 drop view 视图名字; create view V4 as select * from my_stu; drop view V4; -- 不能用table代替view -- 视图意义：1.视图可以节省SQL语句，将一条复杂的SQL语句使用视图进行保存，以后可以直接对 -- 视图进行操作 -- 2.数据安全，视图操作是主要针对查询的，如果对视图结构进行处理（删除），不会影响 -- 基表数据（相对安全） -- 3.视图往往实在大项目中使用，而且是多系统使用，可以对外提供有效、、有用的数据，但是 -- 隐藏关键的数据，数据安全 -- 4.视图可以对外提供友好型，不同的视图提供不同的数据，对外好像专门设计 -- 5.视图可以更好的（更容易）的进行权限控制 --视图数据操作，可以进行数据写操作，但是有甚多限制 -- 将数据直接在视图上操作 -- 新增数据，直接对视图进行数据新增。 -- 1.多表视图不能新增数据 -- 2.可以向单表视图插入数据，但是视图中包含的字段必须有基表中所有不能为空的数据字段（或者没有默认字段） -- 3.视图是可以像基表插入数据的 insert into V2 values (9, 'java0012', 'A322'); +----+------------+----------+ | id | class_name | room_add | +----+------------+----------+ | 1 | java001 | a330 | | 2 | php002 | a331 | | 3 | pyhon003 | a332 | | 9 | java0012 | A322 | +----+------------+----------+ -- 删除数据 -- 1.多表数据不能删除 delete from V3 where id = 1; --ERROR 1395 (HY000): Can not delete from join view 'stuinfo.v3' -- 2.单表视图可以删除数据 delete from V2 where id = 9; +----+------------+----------+ | id | class_name | room_add | +----+------------+----------+ | 1 | java001 | a330 | | 2 | php002 | a331 | | 3 | pyhon003 | a332 | +----+------------+----------+ --更新数据 -- 理论上来讲，无论是单表视图，还是多表视图 -- 更新限制：with check option，如果对视图在新增的时候，限制了某个字段有限制，那么在对视图进行数据 -- 更新操作时，系统会进行验证，要保证更新之后，数据依然可以被 -- 视图：age字段限制更新 create view V5 as select * from my_stu where age > 18 with check option ; +----+---------+------+--------+------+------+--------+ | id | number | name | gender | age | c_id | height | +----+---------+------+--------+------+------+--------+ | 2 | 7150001 | 李四 | 女 | 19 | 2 | 176 | | 3 | 7150002 | 王五 | 女 | 19 | 2 | 165 | +----+---------+------+--------+------+------+--------+ -- 表示视图的数据来源都是年龄大于18岁的，更新的时候不能将年龄大于18 的改成小于18的; update V5 set age = 15 where id = 2; -- ERROR 1369 (HY000): CHECK OPTION failed 'stuinfo.v5' -- 获取所有班级中，最高的一个学生 create view V6 as select * from my_stu order by height desc; select * from V6 group by c_id; --结果无效 -- 等效于 select * from my_stu group by c_id order by height desc; -- 视图算法：系统对视图以及外部查询视图的select语句的一中解析方式 -- 视图算法分为三类： -- undefine : 未定义，这不是一中实际使用的算法，是一种推卸责任的算法，告诉系统，视图没有定义 -- 让系统自己看着办 -- temptable ： 临时表算法，系统应该先执行视图的select语句，后执行外部查询语句 -- merger : 合并算法，系统应该先将视图对应的select语句与外部查询视图的select语句进行合并 -- 然后执行（效率高） -- 算法指定： create ALGORITHM=temptable view V7 as select * from my_stu order by height desc; -- 再查 select * from V6 group by c_id; -- 视图算法选择：如果视图的select语句中包含一个查询子句（五子句where，group，order，limit，having） -- 而且很有可能顺序比外面的查询语句要靠后，使用算法temptable，其他情况不用指定（默认即可） -----------数据的备份与还原----------- -- 备份：将当前已有的数据或记录保留 -- 还原：将已经保留的数据恢复到对应的表中 -- 1.防止数据丢失，被盗、误操作 -- 2.保护数据记录， -- 数据备份、还原方式有多种：1.直接数据表备份2.单表数据备份3.增量备份 --1.数据表备份，不需要通过sql来备份，直接进入到数据库文件夹，复制对应的表结构以及数据文件， -- 以后还原的时候，直接将备份的内容放进去即可 -- 前提条件：根据不同的存储引擎由不同的区别，主要有两种，innodb和mysiam（免费） -- innodb, 只有表结构，数据全部存储到ibdatal文件中 -- myisam，索引、数据、结构文件分开存储 -- 这种文件备份通常适用于myisam引擎，复制到其他数据库可以直接使用，innodb只能在创建的 -- 那个库里面使用 -- 单表数据备份，每次只能备份一张表，只能备份数据（表结构不能备份），通常的使用，将表结构 -- 的数据导出到文件 -- 1.备份，从表中保存一部分数据到外部文件中（outfile） select */字段列表 into outfile 文件所在路径 from 数据源; -- 前提是，外部文件不存在 -- 单表数据备份 select * into outfile 'D:/Program/dabase.bak/my_stu' from my_stu; -- 千万不能用txt打开，会改变字符集，内部字段之间默认用tab隔开 -- 高级备份，自己制定字段和行的处理方式 select */字段列表 into outfile 文件所在路径 filds 字段处理 lines 行处理 from 数据源; --filds：字段处理 -- enclosed by：字段使用什么内容包裹，默认是，空字符串 -- terminated ： 字段以什么结束，默认是\r,tab键 -- escaped by : 特殊符号用什么方式处理，默认是“\\”，使用反斜杠转义 -- lines：行处理 -- starting by ： 每行以什么开始，默认是“”，空字符串 -- endding by ： “‘\r’” select * into outfile 'D:/Program/dabase.bak/my_stu_2' -- 字段处理 fields enclosed by '"' -- 数据使用栓引号包裹 terminated by '|' -- 使用竖线分割字段数据 -- 行处理 lines starting by 'START:' from my_stu; START:"1"|"07150000"|"张三"|"男"|"18"|"1"|"188" START:"2"|"07150001"|"李四"|"女"|"19"|"2"|"176" START:"3"|"07150002"|"王五"|"女"|"19"|"2"|"165" START:"4"|"07150003"|"赵柳"|"男"|"18"|"3"|"195" START:"5"|"07150004"|"小名"|"男"|"18"|"3"|"183" START:"6"|"07150004"|"小刚"|"女"|"18"|"1"|"179" START:"7"|"0715005"|"小芳"|"女"|"16"|"5"|"198" -- 数据还原：将一个在外部保存的数据重新恢复到表中（如果表结构不存在，那么无法还原） load data infile 文件所在路径 into table 表名(字段列表) fields 字段处理 lines 行处理; mysql> delete from my_stu; -- 保留表结构，仅删除数据 Query OK, 7 rows affected (0.05 sec) mysql> select * from my_stu; Empty set (0.00 sec) load data infile 'D:/Program/dabase.bak/my_stu_2' into table my_stu fields enclosed by '"' -- 数据使用栓引号包裹 terminated by '|' -- 使用竖线分割字段数据 -- 行处理 lines starting by 'START:'; mysql> select * from my_stu; +----+---------+------+--------+------+------+--------+ | id | number | name | gender | age | c_id | height | +----+---------+------+--------+------+------+--------+ | 1 | 7150000 | 张三 | 男 | 18 | 1 | 188 | | 2 | 7150001 | 李四 | 女 | 19 | 2 | 176 | | 3 | 7150002 | 王五 | 女 | 19 | 2 | 165 | | 4 | 7150003 | 赵柳 | 男 | 18 | 3 | 195 | | 5 | 7150004 | 小名 | 男 | 18 | 3 | 183 | | 6 | 7150004 | 小刚 | 女 | 18 | 1 | 179 | | 7 | 0715005 | 小芳 | 女 | 16 | 5 | 198 | +----+---------+------+--------+------+------+--------+ 7 rows in set (0.00 sec) -- 小访问量的备份方式sql备份，备份的是SQL语句，系统会对表以及表结构进行处理， -- 变成对应的SQL语句，然后恢复的时候执行SQL语句就行了 -- 备份：mysql没有提供备份指令：需要利用mysql提供的软件，mysqldump.exe -- mysqldump.exe也是一种客户端，需要操作服务器，必须连接认证 mysqldump -hPup [数据表名字1[数据表名字2...]] > 外部文件.sql mysqldump -uroot -p12345678 stuinfo my_stu > D:/Program/dabase.bak/my_stu_3.sql -- 注意：这个不是sql语句，最后不要分号 --结果： -- MySQL dump 10.13 Distrib 5.5.50, for Win64 (x86) -- -- Host: localhost Database: stuinfo -- ------------------------------------------------------ -- Server version 5.5.50 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `my_stu` -- DROP TABLE IF EXISTS `my_stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `my_stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `number` varchar(10) NOT NULL, `name` varchar(20) DEFAULT NULL, `gender` enum('男','女') DEFAULT NULL, `age` tinyint(3) unsigned DEFAULT NULL, `c_id` int(11) NOT NULL, `height` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `my_stu` -- LOCK TABLES `my_stu` WRITE; /*!40000 ALTER TABLE `my_stu` DISABLE KEYS */; INSERT INTO `my_stu` VALUES (1,'7150000','张三','男',18,1,188),(2,'7150001','李四','女',19,2,176),(3,'7150002','王五','女',19,2,165),(4,'7150003','赵柳','男',18,3,195),(5,'7150004','小名','男',18,3,183),(6,'7150004','小刚','女',18,1,179),(7,'0715005','小芳','女',16,5,198); /*!40000 ALTER TABLE `my_stu` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-06-24 16:30:59 -- 整库备份： mysqldump -uroot -p12345678 stuinfo > D:/Program/dabase.bak/stuinfo.sql -- sql还原数据，第一种，使用mysql客户端还原 mysql -hPup 数据库名字 < 备份文件目录 mysql -uroot -p12345678 stuinfo < D:/Program/dabase.bak/my_stu_3.sql -- 第二种，使用SQL指令还原 source D:/Program/dabase.bak/my_stu_3.sql; -- sql备份优缺点： -- 优点： 可以备份结构 -- 缺点：会浪费空间，会额外增加sql指令 -- 增量备份，大项目，不是针对数据或者sql指令，是针对mysql文件日志文件进行备份 -- 增量备份：指定时间段开始备份，备份数据不会重复（不会浪费 空间），所有的操作都会备份 ---------------------********************回顾 -- 外键：关联关系，表中字段指向另外一张表的主键 -- 外键条件，字段类型必须一致，存储引擎必须为innodb -- 一张表中可以有多个外键，不能重名 -- 外键约束，子表约束，不能插入父表不存在的记录 --父表约束有三种：默认district严格，cascade，set null，on delete set null， on update cascade -- 联合查询：union，多表合并和单表不同查询条件，要求字段数量一致 -- 联合查询使用order by，select语句必须用括号，还必须用limit -- 子查询：一条查询语句中又出现了另外一条查询语句 -- 分类：按位置（from， where， exists），按返回结果（用途，标量，列，行，表） -- 视图：view，1.节省sql语句 2.安全性控制，视图本质是虚拟表，有结构无数据， -- 视图数据操作：多表只能改，单表合一增删改（增删有条件限制） -- 视图算法：三种，undifined 未定义，temptable临时表（做两次）和merge合并（效率高，只做一次） -- 文件备份与还原， -- 文件备份：存储引擎（myisam适用，直接复制粘贴） -- 单表数据备份：只能备份数据 -- SQL备份：备份的是SQL指令（mysqldump.exe 用客户端还原或source指令） -- 增量备份：备份系统日志文件 --------------------------事务------------------------------- -- 需求，有一张银行账户表，有A账户给B账户转账，A账户先减少，B账户增加，但是A操作完 -- 之后断电了。 -- 解决方案：A减少钱，但是不应该立即修改数据表，B收到钱之后，同时修改数据表 -- 事务安全问题 -- 事务：transaction， 一系列要发生的连续的操作 -- 事务安全：一种保护连续操作同时满足（实现）的一种机制 -- 事务安全的意义：保证数据操作的完整性 -- 创建一个账户表 create table my_account( number char(16) not null unique comment '账户', name varchar (20) not null, money decimal (10, 2) default 0.0 comment '账户余额' )charset utf8; -- 插入数据 insert into my_account values ('0000000000000001', '张三', 1000), ('0000000000000002', '李四', 2000), ('0000000000000003', '王五', 700); update my_account set money = money - 1000 where id = 1; -- 事务操作分为两种：自动事务（默认的），手动事务 -- 手动事务：操作流程 -- 1.开启事务：高速系统以下所有操作（写）不要直接写到事务表，而是写到事务日志 start transaction ; -- 2.进行事务操作：一系列操作 -- a. 李四账户减少 update my_account set money = money - 1000 where number = '0000000000000001'; -- b. 张三账户增加 update my_account set money = money + 1000 where number = '0000000000000002'; (此时用另外的账户看，数据没有变，自己的账户看，变了) -- c. 选择性的将日志文件操作的结果保存到数据表（同步）或者说直接清空事务日志（原来操作全部清空） --c1.提交事务 commit ; --c2.回滚事务 rollback; -- 事务原理：事务开启后，所有的操作都会临时保存到事务日志中，事务日志只有在得到commit命名才会同步到数据表，其他任何情况都会清空 -- 回滚点：在某一个成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面 -- 操作都已经成功，可以在当前成功的位置，设一个点，可供后续失败错做返回到该位置，而不是返回 -- 所有操作，这个点称之为回滚点 -- 设计回滚点语法： save point 回滚点名字; -- 回滚 -- 事务处理：张三加钱 update my_account set money = money + 10000 where number = 0000000000000001; -- 设置回滚点 savepoint sp1; -- 银行扣税 update my_account set money = money - 10000 * 0.05 where number = 0000000000000002; -- 回滚到回滚点 rollback to sp1; -- 继续操作 update my_account set money = money - 10000 * 0.05 where number = 0000000000000001; -- 查看结果 select * from my_account; -- 提交 commit ; -- 自动事务处理，在mysql中，默认的都是自动事务处理，用户操作完会立即同步到数据表中 -- 自动事务：系统通过autocommit变量控制 show variables like 'autocommit'; -- 关闭自动提交 set autocommit = 0; -- 关闭之后需要手动来选择处理：commit提交，rollback回滚 -- 注意，通常都会使用自动事务 -- 事务特性：ACID -- A:atomic 原子性，事务整个操作是一个整体，不可分割，要么全部成功，要么全部失败 -- C:consistency:一致性，实务操作前后，数据表中的数据没有变化 -- I:isolation，隔离性，实务操作不互相影响 -- D:durability，持久性，数据一旦改变，不可改变，永久的改变数据表数据 -- 锁机制：innodb默认是行锁，但是如果在事务操作的过程中，没有使用到索引，系统会自动全表检索数据，自动升级为表锁 -- 行锁：只有当前行被锁住，别的用户不能操作 -- 表锁：整张表被锁住，别的用户都不能操作 -- 变量，分为两种，系统变量和自定义变量 -- 系统变量：系统定义好的变量，大部分用户不需要使用系统变量，系统变量是用来控制服务器的表现的 -- 如：autocommit，auto_increment_increament -- 查看所有系统变量 show variables; -- 查看具体变量值：任何一个有数据返回值的内容都是有select查看 select @@version, @@autocommit; -- 修改系统变量分为两种，会话级别和全局级别 -- 修改会话级别 set autocommit = 0; set @@变量名 = 值; -- 全局级别，一次修改，永久生效 set global 变量 = 值; -- 自定义变量 -- 系统为了区分系统变量，用@区别系统变量 set @name = 值; set @name = '张三'; select @name; -- 因为在SQL中=常被用作比较，所以为了区分，通过:=来赋值 -- mysql中允许从数据表中获取数据，然后赋值给变量，两种方式 --方案1：边赋值，边查看结果 select @变量名 := 字段名 from 数据源; -- 如果使用=会变成比较符号 select @name := name from my_stu; -- 方案2：只有赋值不看结果，要求很严格，数据记录最多只允许获取一条mysql不支持数组 select 字段列表 fro m 表名 into 变量列表; select name, age from my_stu where id = 2 into @name, @age; -- 所有自定义变量都是会话级别，当前客户端档次连接有效 -- 所有自定义变量不区分数据库（用户级别） -- 需求：有两张表，一张订单表，一张商品表，每生成一个订单，意味着商品的库存就要减少 -- 触发器，trigger，事先为某张表绑定好一段代码，当表中的某些内容发生改变时（增删改） -- 系统会自动触发代码，执行。 -- 触发器：事件类型，触发时间，触发对象 -- 事件类型：增（insert）删（delete）改（update），三种类型 -- 触发时间：前（before）、后（after） -- 触发对象：表中的每一条记录（行） -- 一张表中只能拥有一种触发时间的一种类型的触发器：最多一张表能有留个触发器 -- 创建触发器，在sql中，没有大括号，都是用赌赢的字母符号代替的 -- 基本语法 -- 临时修改语句结束符 delmiter 自定义符号，后续代码中只有碰到自定义符号才算结束 create trigger 触发器名称 触发时间 事件类型 on 表名 for each row begin --代表左大括号，开始 --里面就是触发器的内容 end -- 代表右大括号， 结束 -- 语句结束符 自定义符号 -- 将临时修改过来 delmiter ; create table my_goods( id int primary key auto_increment, name varchar (20) not null, price decimal (10,2) not null, inv int comment '库存数量' )charset utf8; insert into my_goods values (null, 'iphone', 5288, 100), (null, 'iphoneS6', 6000, 100), (null, 'vivo', 3000, 100); create table my_order( id int primary key auto_increment, g_id int not null comment '商品id', g_number int comment '商品数量' )charset utf8; -- 触发器：订单生成一个，商品库存减少一个 -- 临时修改语句结束符 delimiter $$ create trigger after_order after insert on my_order for each row begin update my_goods set inv = inv - 1 where id = 2; end -- 结束触发器 $$ -- 修正 delimiter ; -- 查看所有触发器 show triggers [like pattern]; -- 查看触发器创建语句 show create trigger after_order; -- 所有的触发器，都会保存到一张表中：information_schema.triggers select * from information_schema.triggers; -- 使用触发器，不需要手动调用，当某种情况发生时，会自动触发（订单里面插入记录之后） mysql> select * from my_goods; +----+----------+---------+------+ | id | name | price | inv | +----+----------+---------+------+ | 1 | iphone | 5288.00 | 100 | | 2 | iphoneS6 | 6000.00 | 100 | | 3 | vivo | 3000.00 | 100 | +----+----------+---------+------+ mysql> select * from my_order; Empty set (0.00 sec) -- 插入订单 insert into my_order values (null, 1, 2); -- 查看goods表 mysql> select * from my_goods; +----+----------+---------+------+ | id | name | price | inv | +----+----------+---------+------+ | 1 | iphone | 5288.00 | 100 | | 2 | iphoneS6 | 6000.00 | 99 | | 3 | vivo | 3000.00 | 100 | +----+----------+---------+------+ -- 说明：触发器的确工作了，当插入订单时，商品确实减少了， -- 问题是减少的数量并不是订单的数量，而是固定死的减少的数量，——》触发器有问题 -- 触发器修改：不能修改 -- 触发器删除：drop trigger 触发器名字; -- 删除触发器 drop trigger after_order; -- 触发器记录：不管触发器是否出发了，只要当某种操作准备执行，系统就会将当前要操作的 -- 记录的当前状态和即将执行之后的新的状态分别保留下，供触发器使用； -- 其中，要操作的当前状态保存到old中，操作之后的状态保留在new之中 -- old代表的是旧记录 -- new代表的是新记录，假设发生之后的结果 -- 删除的时候是没有new的，插入的时候是没有old的 --old和new都是代表记录本身，任何一条记录除了有数据，还有字段名字 -- 使用方式：old.字段名 / new.字段名 delimiter $$ create trigger after_order after insert on my_order for each row begin update my_goods set inv = inv - new.g_number where id = new.g_id; end -- 结束触发器 $$ -- 修正 delimiter ; -- 插入订单 insert into my_order values (null, 1, 2); mysql> select * from my_goods; +----+----------+---------+------+ | id | name | price | inv | +----+----------+---------+------+ | 1 | iphone | 5288.00 | 100 | | 2 | iphoneS6 | 6000.00 | 99 | | 3 | vivo | 3000.00 | 100 | +----+----------+---------+------+ 3 rows in set (0.00 sec) mysql> select * from my_order; +----+------+----------+ | id | g_id | g_number | +----+------+----------+ | 1 | 1 | 2 | +----+------+----------+ 1 row in set (0.00 sec) mysql> insert into my_order values (null, 1, 2); Query OK, 1 row affected (0.10 sec) mysql> select * from my_goods; +----+----------+---------+------+ | id | name | price | inv | +----+----------+---------+------+ | 1 | iphone | 5288.00 | 98 | | 2 | iphoneS6 | 6000.00 | 99 | | 3 | vivo | 3000.00 | 100 | +----+----------+---------+------+ 3 rows in set (0.00 sec) -- 说明，触发器正确。 -- ----------------- 代码执行结构 -- 代码执行结构有三种： -- 顺序结构 -- 分支结构 -- 循环结构 -- 分支结构：事先准备多个代码块，按照条件选择性执行某段代码，在sql中，只有if分支 -- 基本语法： if 条件判断 then -- 满足条件要执行的代码 else --不满足条件要执行的代码 end if; -- 触发器结合if分支，判断商品订单够不够 -- 修改语句结束符 delimiter %% create trigger before_order before insert on my_order for each row begin -- 判断商品库存是否足够 -- 获取商品库存 select inv from my_goods where id = new.g_id into @inv_count; -- 比较库存 if @inv_count < new.g_number then -- 库存不够,触发器没有一个能否阻止时间发生的能力 insert into XXX values(XXX); -- 暴力报错 end if; end %% delimiter ; -- 插入订单 insert into my_order values(null, 1, 10000); -- ERROR 1146 (42S02): Table 'stuinfo.XXX' doesn't exist mysql> select * from my_order; +----+------+----------+ | id | g_id | g_number | +----+------+----------+ | 1 | 1 | 2 | | 2 | 1 | 2 | +----+------+----------+ -- 循环结构：某段代码在指定条件下重复执行 -- while 循环（没有for循环） while 条件判断 do -- 满足条件要执行的代码 -- 变更循环条件 end while; -- 循环控制：在循环内部进行循环判断和控制 -- mysql 中没有对应的continue 、break ，但是有替代品 -- iterate <----> continue -- leave <---> break --使用方式：itrate/leave 循环名字 -- 定义循环名字 循环名字:while 条件 domain -- 循环体 -- 循环控制 leave/itrate 循环名字; end while; -- 函数：将一块代码块封装到一个结构中，在需要执行代码块的时候，调用结构即可执行（代码复用） -- 函数分为两类：系统函数和自定义函数 -- 系统函数：系统定义好的函数，直接调动即可，任何函数都有返回值，因此，函数的调用是 -- 通过select调用 -- mysql中，字符串的基本操作单位（最常见是字符） -- 定义变量 set @cn = '世界你好'; set @en = 'hello world'; substring char_length ： 字符长度 -- 显示出来不太对 length ： 字节长度 -- 字符串截取 select substring(@cn, 1, 3); -- 函数一定要挨着括号 select char_length(@cn), char_length(@en), length(@cn), length(@en); instr ：判断字符串是否在某个具体的字符串中存在，存在返回位置 select instr(@cn, '界'), instr(@en, 'll'), instr(@cn, '拜拜'); select instr(@cn, '世'),instr(@cn, '界'),instr(@cn, '你'),instr(@cn, '好'); +------------------+------------------+------------------+------------------+ | instr(@cn, '世') | instr(@cn, '界') | instr(@cn, '你') | instr(@cn, '好') | +------------------+------------------+------------------+------------------+ | 1 | 3 | 5 | 7 | +------------------+------------------+------------------+------------------+ lpad ： 左填充，将字符串按照某个指定的填充方式，填充到指定的长度 select lpad(@cn, 20, '欢迎'), lpad(@en, 20, 'hey'); insert(str, pos, length, tarstr) 替换，找到目标位置的字符串,替换 select insert (@en, 3, 3, 'y'); strcmp ------------- 自定义函数------------ -- 函数要素：函数名，参数列表（形参和实参），返回值，函数体（作用域） -- 创建函数： create function 函数名 （形参列表） returns 数据类型 -- 规定要返回的数据类型 begin -- 函数体 -- 返回值 return 类型（指定数据类型） end -- 简单函数 create function display1() returns int return 100; -- 自定义函数与系统函数的调用方式是一样的 mysql> create function display1() returns int -> return 100; Query OK, 0 rows affected (0.03 sec) mysql> select display1(); +------------+ | display1() | +------------+ | 100 | +------------+ -- 查看函数 -- 查看所有函数： show function status [like 'pat']; mysql> show function status\G *************************** 1. row *************************** Db: stuinfo -- 函数属于数据库，只有在数据库下才可以调用 Name: display1 Type: FUNCTION Definer: root@localhost Modified: 2018-06-25 12:26:16 Created: 2018-06-25 12:26:16 Security_type: DEFINER Comment: character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci -- 查看函数创建 show create function display1; -- 修改函数：不能修改 -- 删除函数 drop function 函数名; -- 函数参数 -- 参数分为两类，定义时的参数叫形参，调用时的参数叫实参 -- 形参，必须指定数据类型 function 函数名 （形参名字 字段类型） returns 函数参数 -- 计算1到指定数值之间的和 delimiter $$ create function display2(int_1 int) returns int begin set @i = 1; -- 定义条件变量，@符号定义的是全局变量 set @res = 0; while @i <= int_1 do set @res = @res + @i; set @i = @i + 1; end while; return @res; end $$ delimiter ; mysql> select display2(10); +--------------+ | display2(10) | +--------------+ | 55 | +--------------+ -- 作用域 -- mysql中的作用域与js中的作用域完全一样，全部变量可以在任何地方使用，局部变量只能在函数内部使用 -- 全局变量：使用set关键字定义，使用@符号标志 -- 局部变量：使用declare关键字声明，没有@符号，所有的局部变量的声明，必须在函数体开始之前 -- 求和 1 - n 之间 5的倍数不要 delimiter $$ create function display3(n int) returns int begin declare i int default 1; -- 定义条件变量，@符号定义的是全局变量 declare res int default 0; my_while:while i <= n do if i % 5 = 0 then set i = i + 1; iterate my_while; else set res = res + i; set i = i + 1; end if; end while; return res; end $$ delimiter ; ------------ 存储过程 -- 存储过程简称：过程，procedure，是一种用来处理数据的方式 -- 存储过程是一种没有返回值的函数，往往是完成数据的操作 -- 创建过程基本语法 create procedure name (args ...) begin --过程体 end -- 查看过程 show procedure status [like pattern]; --- 查看创建语句 show create PROCEDURE name; -- 调用过程，过程没有返回值，意味着不用select调用，过程有个专门的关键字：call -- 调用 call pro_name(); -- 过程不能修改 -- 过程删除 drop procedure name(); -- 过程参数，函数的参数需要数据类型指定，过程笔函数更加严格 -- 过程还有字节的类型限定：三种类型 -- in，数据只是从外部传入给内部使用(值传递)，可以是数据，也可以是变量 -- out，只允许过程内部使用（不用外部传数据），给外部使用的（应用传递，外部的数据会被先清空才进去内部，只能是变量） -- inout，外部的可以在内部使用，而内部使用也可以给外部使用，典型的引用传递 -- 使用 create procedure name(in arg type, out arg2 type, inout arg3 type) -- 过程参数 delimiter $$ create procedure pro2(in int_1 int, out int_2 int, inout int_3 int) begin select int_1, int_2, int_3; -- int_2 一定是null end $$ delimiter ; -- 调用 mysql> set @int_1 := 1; mysql> set @int_2 := 2; mysql> set @int_3 := 3; mysql> call pro2(@int_1, @int_2, @int_3); +-------+-------+-------+ | int_1 | int_2 | int_3 | +-------+-------+-------+ | 1 | NULL | 3 | +-------+-------+-------+ -- 存储过程对于变量的操作（返回）是滞后的，是在存储过程调用结束后才将内部修改的值修改到变量上 delimiter $$ create procedure pro4(in int_1 int, out int_2 int, inout int_3 int) begin select int_1, int_2, int_3; -- int_2 一定是null(三个变量当前局部变量) set int_1 = 10; set int_2 = 100; set int_3 = 1000; -- 查看局部变量 select int_1, int_2, int_3; -- 查看全局变量 select @int_1, @int_2, @int_3; -- 修改全局变量 set @int_1 = 'a'; set @int_2 = 'b'; set @int_3 = 'c'; -- 查看全局变量 select @int_1, @int_2, @int_3; end $$ delimiter ;
