01数据库简单操作

增

语法: insert into `表名`(字段名1,字段名2,...) values(值1,值2,...)
insert into `t_user`(`id`,`username`,`password`)values(null, 'dp', 12345),(null, 'lw', 123456);

删

语法:delete from `表名` where 条件
delete from t_user where username = 'xiaomei';

改

语法:update `表名`set`字段1` = 值1,`字段2` = 值2,...where 条件

update t_user set username = '小娟' where username = 'xiaomei';

查

select * from 表名

练习

表:

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50725
Source Host           : localhost:3306
Source Database       : mydb

Target Server Type    : MYSQL
Target Server Version : 50725
File Encoding         : 65001

Date: 2019-02-19 15:24:20
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `cate_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `goods_name` varchar(120) NOT NULL DEFAULT '',
  `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',
  `click_count` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES ('1', '4', 'kd876', '1388.00', '1665.60', '1', '9');
INSERT INTO `goods` VALUES ('3', '8', '诺基亚原装5800耳机', '68.00', '81.60', '24', '3');
INSERT INTO `goods` VALUES ('4', '8', '诺基亚n85原装充电器', '58.00', '69.60', '17', '0');
INSERT INTO `goods` VALUES ('5', '11', '索爱原装m2卡读卡器', '20.00', '24.00', '8', '3');
INSERT INTO `goods` VALUES ('6', '11', '胜创kingmax内存卡', '42.00', '50.40', '15', '0');
INSERT INTO `goods` VALUES ('7', '8', '诺基亚n85原装立体声耳机hs-82', '100.00', '120.00', '20', '0');
INSERT INTO `goods` VALUES ('8', '3', '飞利浦9@9v', '399.00', '478.79', '1', '10');
INSERT INTO `goods` VALUES ('9', '3', '诺基亚e66', '2298.00', '2757.60', '4', '20');
INSERT INTO `goods` VALUES ('10', '3', '索爱c702c', '1328.00', '1593.60', '7', '11');
INSERT INTO `goods` VALUES ('11', '3', '索爱c702c', '1300.00', '1500.00', '1', '0');
INSERT INTO `goods` VALUES ('12', '3', '摩托罗拉a810', '983.00', '1179.60', '8', '13');
INSERT INTO `goods` VALUES ('13', '3', '诺基亚5320 xpressmusic', '1311.00', '1573.20', '8', '13');
INSERT INTO `goods` VALUES ('14', '4', '诺基亚5800xm', '2625.00', '3150.00', '1', '6');
INSERT INTO `goods` VALUES ('15', '3', '摩托罗拉a810', '788.00', '945.60', '3', '8');
INSERT INTO `goods` VALUES ('16', '2', '恒基伟业g101', '823.33', '988.00', '0', '3');
INSERT INTO `goods` VALUES ('17', '3', '夏新n7', '2300.00', '2760.00', '1', '2');
INSERT INTO `goods` VALUES ('18', '4', '夏新t5', '2878.00', '3453.60', '1', '0');
INSERT INTO `goods` VALUES ('19', '3', '三星sgh-f258', '858.00', '1029.60', '12', '7');
INSERT INTO `goods` VALUES ('20', '3', '三星bc01', '280.00', '336.00', '12', '14');
INSERT INTO `goods` VALUES ('21', '3', '金立 a30', '2000.00', '2400.00', '40', '4');
INSERT INTO `goods` VALUES ('22', '3', '多普达touch hd', '5999.00', '7198.80', '1', '16');
INSERT INTO `goods` VALUES ('23', '5', '诺基亚n96', '3700.00', '4440.00', '8', '17');
INSERT INTO `goods` VALUES ('24', '3', 'p806', '2000.00', '2400.00', '100', '35');
INSERT INTO `goods` VALUES ('25', '13', '小灵通/固话50元充值卡', '48.00', '57.59', '2', '0');
INSERT INTO `goods` VALUES ('26', '13', '小灵通/固话20元充值卡', '19.00', '22.80', '2', '0');
INSERT INTO `goods` VALUES ('27', '15', '联通100元充值卡', '95.00', '100.00', '2', '0');
INSERT INTO `goods` VALUES ('28', '15', '联通50元充值卡', '45.00', '50.00', '0', '0');
INSERT INTO `goods` VALUES ('29', '14', '移动100元充值卡', '90.00', '110.00', '0', '0');
INSERT INTO `goods` VALUES ('30', '14', '移动20元充值卡', '18.00', '21.00', '9', '1');
INSERT INTO `goods` VALUES ('31', '3', '摩托罗拉e8 ', '1337.00', '1604.39', '1', '5');
INSERT INTO `goods` VALUES ('32', '3', '诺基亚n85', '3010.00', '3612.00', '4', '9');

--查询年龄为18的学生
select * from stu where age = 18;
-- 别名as可以省略
select id as "编号", username "名字", password "密码" from t_user where password = 123456;

-- 货架id为不为3
select * from goods where cate_id != 3;
select * from goods where cate_id <> 3;
--商品加>3000
select * from goods where shop_price>3000
-- 货架id为4或者11
select * from goods where cate_id = 4 or cate_id = 11;
select * from goods where cate_id in(4,11);
--价格在100 - 500之间的
select * from goods where shop_price between 100 and 500;
select * from goods where shop_price >= 100 and shop_price <= 500;
--  取出不属于第3栏目且不属于第11栏目的商品(使用and或使用not in分别实现)
select * from goods where cate_id != 4 and cate_id != 11;

-- 取出名字以"诺基亚"开头的商品
select * from where goods_name like "诺基亚%";

--取出名字为"诺基亚Nxx"的手机
select * from where goods_name like "诺基亚__";