MySQL基本增删改查语句练习

转载

mb601cf713ef4ca 2021-03-03 13:02:44

文章标签 MySQL 文章分类 MySQL 数据库

创建测试表的SQL语句在文章末尾

*********************************************************************************

<插入数据>
col：字段
val：值

插入指定列
insert into [表名] (col1, col2, ……) values (val1, val2, ……);
例子：insert into area (title, description) values ('灵溪', '牛肉粒');

插入所有列
insert into [表名] values (val1, val2, ……);
例子：insert into area values (2, '龙港', '猪肉铺', 1);

插入多个列
insert into [表名] values (val1, val2, ……), (val1, val2, ……), (val1, val2, ……);
例子：insert into area (col1, col2, ……) values ('马站', '煎包'), ('南宋', '五香干'),
('金乡', '豆沙包'); //注意：如果省略了字段，则值的数量必须等于字段的数量
*************************************************************************************************
<修改数据>

update [表名] set col1 = newVal1, col2 = newVal2, ...;
例子：update area set description =' 榨菜', parent_id = 3 where title = '南宋';
('金乡', '豆沙包'); //注意：如果省略了字段，则值的数量必须等于字段的数量
*************************************************************************************************
<删除数据>

delete from [表名] where [条件];
例子：delete from area where id = 4;
('金乡', '豆沙包'); //注意：如果省略了字段，则值的数量必须等于字段的数量
*************************************************************************************************
<查询>

{条件查询：where}
条件表达式的意义，表达式为真，则该行取出
（●）比较运算符：=， !=， <， >， <= ，>=
（●）模糊查询：like，not like（'%'匹配任意多个字符，'_'匹配任意单个字符）
（●）范围查询：in，not in，between...and...
（●）是否为null：is null，is not null

{分组：group by}
一般要配合聚合函数（max，min，sum，avg，count）使用才有意义。
求和用sum()，sum()是对符合条件的记录的数值列求和。
求行的个数用累计count()，count()是对查询中符合条件的结果(或记录)的个数，一般根据主键来计算。
有如下表fruit及数据
+------+---------+-------+
| id | fname | price |
+------+---------+-------+
| 1 | 苹果 | 2 |
| 2 | 香蕉 | 5 |
+------+---------+-------+
例子：
（●）查询记录总数：select count(price) from fruit; //执行结果为2，表示有2条记录
（●）查询字段之和：select sum(price) from fruit; //执行结果为7，表示各记录的price字段之和为7
筛选：having
排序：order by
限制：limit

{连接查询}
（●）左连接（格式：.. left join .. on）
语法：
select [字段] from [表A] left join [表B] on [表A].[表A的字段a] = [表B].[表B的字段a];
注意：如果字段在A表和B表都存在，得在字段前面加上表名来区分出是哪个表的字段。
例子：
select goods_id, goods_name, goods.cate_id, cate_name from goods
left join cate on goods.cate_id = cate.cate_id;
（●）内连接（左右连接的交集。格式：.. inner join .. on）
语法：
select [字段] from [表A] inner join [表B] on [表A].[表A的字段a] = [表B].[表B的字段a];
例子：
select goods_id, goods_name, goods.cate_id, cate_name from goods
inner join cate on goods.cate_id = cate.cate_id ;
说明：左连接是以在左边的表的数据为主，沿着左表查右表（如果左表的某行在右表中没有匹配行，
则在相关联的结果集行中右表的所有选择列表列均为空值）。右连接和左连接本质一样，
只不过变成了以右表为主去查左表。内连接是以两张表都有的共同部分数据为准，也就是左右连接的数据交集。

{子查询}
（●）where型子查询：内层sql的返回值在where后作为条件表达式的一部分。
语法：
select [字段] from [表A] where [表A的字段a] = (select [表B的字段a] from [表B] where ...);
例子：
select goods_id, goods_name from goods where cate_id = (select cate_id from cate where cate_name = 'GSM手机');
（●）from型子查询：内层sql查询结果，作为一张表，供外层的sql语句再次查询。
语法：
select [字段] from (select * from ...) as [新的表名] where ....
例子：
select goods_id, goods_name from (select * from goods) as g1 where goods_id >= 2;
-------------------------------------------------------------------------------------------------
<查询练习题>

取出名字为“诺基亚xx”的手机（x为任意的1个字符）
select goods_id, goods_name, shop_price from goods where goods_name like '诺基亚__';

把good表中商品名为“诺基亚xxx”的商品，改为“HTCxxx”
提示：大胆的把列看成变量，参与运算，甚至调用函数来处理（substring()，concat()）
update goods set goods_name = concat('HTC', substring(goods_name, 4)) where goods_name like '诺基亚___';

查询每个类型下面：最贵商品价格；最低商品价格；商品平均价格；商品库存量
提示：聚合函数（max，min，sum，avg，count）与group的综合运用
（●）
最贵商品价格：select cate_id, max(shop_price) from goods group by cate_id;
（●）
最低商品价格：select cate_id, min(shop_price) from goods group by cate_id;
（●）
商品平均价格：select cate_id, avg(shop_price) from goods group by cate_id;
（●）
商品库存量：select cate_id, sum(goods_amount) from goods group by cate_id;

查询该店的商品比市场价所节省的价格
select goods_id, goods_name, market_price - shop_price as cheap_price from goods;

查询每个商品所积压的货款
提示：库存*单价
select goods_id, goods_name, goods_amount * shop_price from goods;

查询该店积压的总货款
select sum(goods_amount * shop_price) from goods;

查询该店每个类型下面积压的货款
select cate_id, sum(goods_amount * shop_price) as stocked_money from goods group by cate_id;

查询比市场价省钱5元以上的商品及该商品所省的钱（请分别用where和having来实现）
（●）where实现：
select goods_id, goods_name, market_price - shop_price as cheap_price from goods
where market_price - shop_price > 5; //这里不能用别名（cheap_price）来判断
（●）having实现：
select goods_id, goods_name, market_price - shop_price as cheap_price from goods
having cheap_price > 5; //having必须用别名（cheap_price）来做判断（不能用表达式market_price - shop_price这种）

查询积压货款超过200元的类型，以及该类型积压的货款
select cate_id, sum(goods_amount * shop_price) as stocked_money from goods group by cate_id having stocked_money > 200;

查询出2门及2门以上不及格者的平均成绩
select student_name, avg(score) as avg_score, sum(score < 60) as fail_subject from student group by student_name having fail_subject >= 2;
--------------------------------------------------------------------------------------------------------------------------------
{连接查询}

取出所有商品的商品名，类型名，价格
select cate_name, goods_name, shop_price from goods
left join cate on goods.cate_id = cate.cate_id;

取出类型6的商品的商品名，类型名，价格
select cate_name, goods_name, shop_price from goods
left join cate on goods.cate_id = cate.cate_id
where goods.cate_id = 6;

取出类型6的商品的商品名，类型名，价格
select goods_name, cate_name, standard_name from goods
left join cate on goods.cate_id = cate.cate_id
left join standard on goods.standard_id = standard.standard_id
where goods.cate_id = 6;

查询2006-6-1到2006-7-1之间举行的所有比赛，列出字段：比赛id，队伍1，比分，队伍2，时间
select mid, a.tname as t1, rate_score, b.tname as t2, play_time from play
left join team as a on play.hid = a.tid
left join team as b on play.gid = b.tid
where play_time between '2006-06-01' and '2006-07-01';
----------------------------------------------------------------------------------------
{子查询}

查询出最新的商品，即主键最大
select goods_id, goods_name from goods
where goods_id = (select max(goods_id) from goods);

查询出商品编号为6的商品的类型
（●）左连接：
select goods_id, goods_name, cate_name from goods
left join cate on goods.cate_id = cate.cate_id where goods_id = 6;
（●）子查询：
select cate_name from cate where cate_id =
(select cate_id from goods where goods_id = 6);

查询goods表中的每个类型下面最新的商品
（●）用where型子查询：
select goods_id, goods_name, cate_id from goods where goods_id in
(select max(goods_id) from goods group by cate_id);
（●）用from型子查询：
select goods_id, goods_name from (select goods_id, cate_id, goods_name from goods
order by goods_id desc) as g1 group by g1.cate_id; //最新的商品：desc排序后，group分组取最后一条数据

查出所有商品的类型
（●）用exists型子查询：
select cate_id, cate_name from cate where exists
(select * from goods where goods.cate_id = cate.cate_id);
（●）用distinct值不重复：
select distinct cate.cate_name from goods left join cate
on goods.cate_id = cate.cate_id; //distinct必须用于首个字段
（●）用group by分组：
select cate.cate_id, cate.cate_name from goods left join cate
on goods.cate_id = cate.cate_id group by cate_name;
*************************************************************************************************

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `area`
-- ----------------------------
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` char(10) NOT NULL DEFAULT '' COMMENT '地区名称',
  `description` varchar(30) NOT NULL DEFAULT '' COMMENT '地区描述',
  `parent_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '父id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`,`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='地区表';

-- ----------------------------
-- Records of area
-- ----------------------------

-- ----------------------------
-- Table structure for `cate`
-- ----------------------------
DROP TABLE IF EXISTS `cate`;
CREATE TABLE `cate` (
  `cate_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `cate_name` varchar(90) NOT NULL DEFAULT '' COMMENT '类型名称',
  `parent_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '父id',
  PRIMARY KEY (`cate_id`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COMMENT='产品类型表';

-- ----------------------------
-- Records of cate
-- ----------------------------
INSERT INTO `cate` VALUES ('1', '手机类型', '0');
INSERT INTO `cate` VALUES ('3', 'GSM手机', '1');
INSERT INTO `cate` VALUES ('4', '4G手机', '1');
INSERT INTO `cate` VALUES ('5', '双模手机', '1');
INSERT INTO `cate` VALUES ('6', '手机配件', '0');
INSERT INTO `cate` VALUES ('7', '充电器', '6');
INSERT INTO `cate` VALUES ('8', '耳机', '6');
INSERT INTO `cate` VALUES ('9', '电池', '6');
INSERT INTO `cate` VALUES ('11', '读卡器和内存卡', '6');
INSERT INTO `cate` VALUES ('12', '充值卡', '0');
INSERT INTO `cate` VALUES ('13', '小灵通/固话充值卡', '12');
INSERT INTO `cate` VALUES ('14', '移动手机充值卡', '12');
INSERT INTO `cate` VALUES ('15', '联通手机充值卡', '12');

-- ----------------------------
-- Table structure for `fruit`
-- ----------------------------
DROP TABLE IF EXISTS `fruit`;
CREATE TABLE `fruit` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) NOT NULL DEFAULT '' COMMENT '水果名称',
  `price` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '每斤价格',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='水果表';

-- ----------------------------
-- Records of fruit
-- ----------------------------
INSERT INTO `fruit` VALUES ('1', '苹果', '2');
INSERT INTO `fruit` VALUES ('2', '香蕉', '5');

-- ----------------------------
-- Table structure for `goods`
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cate_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '类型id',
  `standard_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '规格id',
  `goods_name` varchar(10) NOT NULL DEFAULT '' COMMENT '商品名称',
  `goods_amount` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '库存量',
  `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '本店价格',
  `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '市场价',
  `click_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '点击量',
  PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='商品表';

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES ('1', '1', '1', '华为', '3', '15.00', '20.00', '20');
INSERT INTO `goods` VALUES ('2', '6', '3', '诺基亚2', '5', '10.00', '15.00', '16');
INSERT INTO `goods` VALUES ('3', '1', '3', '小米', '4', '25.00', '30.00', '13');
INSERT INTO `goods` VALUES ('4', '6', '1', '诺基亚10', '6', '30.00', '40.00', '11');
INSERT INTO `goods` VALUES ('5', '6', '1', '诺基亚101', '1', '10.00', '15.00', '7');
INSERT INTO `goods` VALUES ('6', '12', '3', '苹果', '2', '15.00', '25.00', '32');
INSERT INTO `goods` VALUES ('7', '6', '2', '诺基亚102', '4', '20.00', '30.00', '25');
INSERT INTO `goods` VALUES ('8', '12', '2', '魅族', '2', '20.00', '30.00', '18');

-- ----------------------------
-- Table structure for `play`
-- ----------------------------
DROP TABLE IF EXISTS `play`;
CREATE TABLE `play` (
  `mid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '比赛id',
  `hid` int(10) unsigned NOT NULL COMMENT '主队id',
  `gid` int(10) unsigned NOT NULL COMMENT '客队id',
  `rate_score` varchar(10) NOT NULL DEFAULT '' COMMENT '比分',
  `play_time` date NOT NULL COMMENT '开赛时间',
  PRIMARY KEY (`mid`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='比赛表';

-- ----------------------------
-- Records of play
-- ----------------------------
INSERT INTO `play` VALUES ('1', '1', '2', '2:0', '2006-05-21');
INSERT INTO `play` VALUES ('2', '2', '3', '1:2', '2006-06-21');
INSERT INTO `play` VALUES ('3', '3', '1', '2:5', '2006-06-25');
INSERT INTO `play` VALUES ('4', '2', '1', '3:2', '2006-07-21');

-- ----------------------------
-- Table structure for `standard`
-- ----------------------------
DROP TABLE IF EXISTS `standard`;
CREATE TABLE `standard` (
  `standard_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `standard_name` varchar(5) NOT NULL DEFAULT '' COMMENT '规格名称',
  PRIMARY KEY (`standard_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='规格表';

-- ----------------------------
-- Records of standard
-- ----------------------------
INSERT INTO `standard` VALUES ('1', '小型');
INSERT INTO `standard` VALUES ('2', '中型');
INSERT INTO `standard` VALUES ('3', '大型');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `student_name` varchar(5) NOT NULL DEFAULT '' COMMENT '学生姓名',
  `subject` varchar(10) NOT NULL DEFAULT '' COMMENT '课程',
  `score` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='学生成绩表';

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '数学', '90');
INSERT INTO `student` VALUES ('2', '张三', '语文', '50');
INSERT INTO `student` VALUES ('3', '张三', '地理', '40');
INSERT INTO `student` VALUES ('4', '李四', '语文', '55');
INSERT INTO `student` VALUES ('5', '李四', '政治', '45');
INSERT INTO `student` VALUES ('6', '王五', '政治', '30');
INSERT INTO `student` VALUES ('7', '赵六', '数学', '96');
INSERT INTO `student` VALUES ('8', '赵六', '语文', '88');
INSERT INTO `student` VALUES ('9', '赵六', '英语', '52');

-- ----------------------------
-- Table structure for `team`
-- ----------------------------
DROP TABLE IF EXISTS `team`;
CREATE TABLE `team` (
  `tid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '队伍id',
  `tname` varchar(10) NOT NULL DEFAULT '' COMMENT '队伍名称',
  PRIMARY KEY (`tid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='队伍表';

-- ----------------------------
-- Records of team
-- ----------------------------
INSERT INTO `team` VALUES ('1', '国安');
INSERT INTO `team` VALUES ('2', '申花');
INSERT INTO `team` VALUES ('3', '公益');

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。