Mysql笔记

xiaoxiao2021-02-28  3

(六天玩转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 ;

 

转载请注明原文地址: https://www.6miu.com/read-1600260.html

最新回复(0)