mysql标准写法及其他常见问题

/*
Navicat MySQL Data Transfer
Source Server         : localhost_3306
Source Server Version : 50549
Source Host           : localhost:3306
Source Database       : testmarket
Target Server Type    : MYSQL
Target Server Version : 50549
File Encoding         : 65001
Date: 2017-09-11 17:10:43
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for god        账单(商品)表  子表(从表)
-- ----------------------------
DROP TABLE IF EXISTS `god`;
CREATE TABLE `god` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `gname` varchar(50) NOT NULL,
  `gnumber` int(11) NOT NULL,
  `gunit` varchar(50) NOT NULL,
  `gprice` double(10,2) NOT NULL,
  `gpay` int(10) NOT NULL,
  `pname` varchar(50) NOT NULL,
  `gdesc` varchar(255) DEFAULT NULL,
  `gdate` varchar(50) NOT NULL,
  PRIMARY KEY (`gid`),
  KEY `pname` (`pname`),                                                    #级联删除 更新 sql语句可以成功运行
  CONSTRAINT `pname` FOREIGN KEY (`pname`) REFERENCES `provide` (`pname`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for provide    供应商  主表(父表)  提供外键
-- ----------------------------
DROP TABLE IF EXISTS `provide`;
CREATE TABLE `provide` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `pname` varchar(50) NOT NULL,
  `pdesc` varchar(50) DEFAULT NULL,
  `pcontact` varchar(50) NOT NULL,
  `pphone` int(20) NOT NULL,
  `paddr` varchar(50) NOT NULL,
  PRIMARY KEY (`pid`),
  KEY `pname` (`pname`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of provide
-- ----------------------------
INSERT INTO `provide` VALUES ('2', '苹果公司', 'iphone', '乔布斯', '123456', '美国');
INSERT INTO `provide` VALUES ('3', '小米科技', '小米手机', '雷军', '123123', '北京');
INSERT INTO `provide` VALUES ('5', '华为', '', '大嘴', '1234', '深圳');
INSERT INTO `provide` VALUES ('6', '中兴', '中兴电子', '王', '4564', '北京');
INSERT INTO `provide` VALUES ('7', '三星', '', '罗', '456', '韩国');
-- ----------------------------
-- Records of god
-- ----------------------------
INSERT INTO `god` VALUES ('1', '米2s', '2', '元', '4000.00', '0', '小米科技', '', '2013-03-12');
INSERT INTO `god` VALUES ('2', 'iphone4', '23', '元', '86000.00', '1', '苹果公司', '', '2013-03-12');
INSERT INTO `god` VALUES ('3', '米2', '23', '元', '46999.00', '1', '小米科技', '', '2013-04-12');
INSERT INTO `god` VALUES ('4', '米3', '2', '元', '22222.00', '0', '小米科技', '', '2013-03-12');
INSERT INTO `god` VALUES ('7', 'iphone5', '2', '元', '150000.00', '1', '小米科技', '', '2013-03-12');
INSERT INTO `god` VALUES ('8', 'iiii', '1', '元', '12.00', '0', '苹果公司', '', '2012-2-2');
INSERT INTO `god` VALUES ('8', 'iiii', '1', '元', '12.00', '0', '苹果公司', '', '2012-2-2');
-- ---------------------------

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`uname` varchar(50) NOT NULL,
`upwd` varchar(50) NOT NULL,
`usex` int(50) NOT NULL,
`uage` int(11) NOT NULL,
`uphone` int(11) NOT NULL,
`uaddr` varchar(50) DEFAULT NULL,
`urole` int(11) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'zhangsan', '123456', '2', '22', '123', '上海', '2');
INSERT INTO `user` VALUES ('2', 'lisi', '123456', '1', '22', '123', '北京', '1');
INSERT INTO `user` VALUES ('3', 'wangwu', '123456', '1', '22', '11', '上海', '0');
SET FOREIGN_KEY_CHECKS=1;
1.商品表

2.供应商表：

问题：查询出不同供应商的名称、编号、交易总额、商品总量及其总商品数量，没有的记为0，写出对应sql语句？

错误答案：

SELECT provide.pid as '供应商编号',provide.pname as '供应商名称', sum(god.gprice) as '总交易金额' from god,provide where provide.pname in(select provide.pname from provide ) and god.pname=provide.pname group by provide.pname;

只有现实小米科技2和苹果公司因为provide.pname = god.pname 关联的话就是查询公共的而三星中兴华为在供应商表中是没有的所以使用左右外连接

正确解答：

SELECT provide.pid as '供应商编号',provide.pname as '供应商名称', IFNULL(SUM(god.gprice),0) as '总交易金额',IFNULL(COUNT(god.pname),0) as '商品总量',IFNULL(SUM(god.gnumber),0) as '总商品数量'
FROM provide
LEFT JOIN god
ON provide.pname = god.pname
GROUP BY provide.pname

2.SET FOREIGN_KEY_CHECKS=0/1的作用?

在mysql中取消外键，Mysql中如果表和表之间建立的外键约束，则无法删除表及修改表结构。

解决方法是在Mysql中取消外键约束: SET FOREIGN_KEY_CHECKS=0; 然后将原来表的数据导出到sql语句，重新创建此表后，再把数据使用sql导入，然后再设置外键约束: SET FOREIGN_KEY_CHECKS=1;

当然欲使sql语句的级联更新和删除成功应该也要加上 ON DELETE SET NULL ON UPDATE CASCADE

先看On Delete属性，可能取值如上图为：No Action, Cascade,Set Null, Restrict属性。

当取值为No Action或者Restrict时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。

当取值为Cascade时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。

当取值为Set Null时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）

当取值为No Action或者Restrict时，则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新。

当取值为Cascade时，则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则也更新外键在子表（即包含外键的表）中的记录。

当取值为Set Null时，则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）。

4.扩展一个主外键的例子

简单描述：
这些关系基本上依靠外键进行管理，在关系中所有表中具有相同含义的字段作为公共部分来连接不同表中的记录。外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。

有两张表，第一张表是记录公司有多少人，都有谁，也就是员工编号及员工姓名这些基本表。另一张表记录每个月发给用户多少工资，所谓工资表是也。
但是工资表里面不能以员工姓名为主键，同样要通过员工id，因为员工的姓名是可能重复的啊。部门经理叫张三，小弟也叫张三，那这俩张三的工资能一样 吗？
并且员工表里面的每个人都有工资，否则谁也不给你干活，且一个人只能有一份工资，否则老板也不同意了。所以员工表和工资表是通过员工id进行关联的一 对一关系。
/*
建立员工表
*/
create table employees ( 
id int(5) not null auto_increment , 
name varchar(8) not null, 
primary key (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;#5表示设置自增的起始值 可省略
/*
建立工资表
*/
create table payroll( 
id int(5) not null, 
emp_id int(5) not null, 
name varchar(8) not null, 
payroll float(4,2) not null, 
primary key(id), 
index emp_id (emp_id),
foreign key (emp_id) references employees (id)
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
参照完整性：
当外键与另一个表的字段有关系，而且这种关系是惟一时，这个系统就称为处于参照完整性的状态。也就是说，如果一个字段在所有的表中只出现一次，而且每个表的这个字段
的变化都会影响其他表，这就是存在参照完整性。术语理解上可能不太方便，其实就是说要在有外键的表中保持所有数据的一致性。比如说“张三”离职了，在员工表里面肯定
没有这个人了，可是如果在工资表里面还存在这个孩子，那么老大就会很生气的

MySQL的外键只能在InnoDB表中使用：所以，如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

MySQL创建外键语法：

创建外键的语法是这样的：FOREIGN KEY (当前表的字段名)… REFERENCES 参照表 (参照表的字段名)

foreign key (emp_id) references employees (id); 的意思就是说当前表的emp_id字段是以employees的id字段为外键的。

注意事项：

一旦建立外键，MySQL只允许向当前表中加入外键表中已有的数据列。比如说贪官表里有“王二麻子”，那么在情妇表只才能有“王二麻子的情妇”。也就是说只有确认一个人是贪官了，才能把其情妇信息列入此表中，否则是不行滴。
关系中的所有表必须是innoDB表，在非InnoDB表中，MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。
用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。
在外键关系中，字段的数据类型必须相似，这对于大小和符号都必须匹配的整数类型尤其重要。
即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误(即使这样做可能会破坏更早创建的外键)
删除外键方法：（上面已经细致说明）

posted @ 2017-09-11 18:43 starskyhu 阅读(1213) 评论(0) 编辑收藏举报

刷新页面返回顶部

starskyhu

mysql标准写法及其他常见问题

公告