MySQL入门练习：增删改

创建数据表：

创建数据表SQL语句

添加数据：

INSERT INTO `stu` VALUES ('1', '王永', '23', 'm', '北京', '1500',null, '1990-1-19');
INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500','110452', '1992-11-1');
INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500','110245', '1993-12-1');
INSERT INTO `stu` VALUES ('4', '宋永合', '25', 'f', '北京', '1500',null, '2001-11-17');
INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000','63465', '1995-1-1');
INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500','46346', '1998-1-1');
INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600','46534', '1950-1-1');
INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500','3448', '2001-11-17');
INSERT INTO `stu` VALUES ('9', '张玲', '23', 'm', '广州', '2500','43456', '2013-1-1');
INSERT INTO `stu` VALUES ('10', '王刚', '18', '男', '山西', '3500','4633', '1990-1-1');
INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500','35356', '2001-11-17');
INSERT INTO `stu` VALUES ('12', '陈永', '24', 'm', '北京', '1500','3563', '2016-1-1');
INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500','8633', '2001-11-17');
INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500',null, '1990-1-1');
INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500','5353', '2001-11-17');
INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000','5374', '1990-1-1');
INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500','5373', '1990-11-1');
INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600','35773', '1990-3-1');
INSERT INTO `stu` VALUES ('19', '王刚', '23', '男', '北京', '3500','13737', '2001-11-17');
INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500','73243', '1990-8-1');
INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500','132783', '1990-4-21');
INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500','13278', '2001-11-17');
INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500','732783', '2001-7-1');
INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500','37537', '2001-11-17');
INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500','37252', '1990-1-1');
INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500','45343', '1990-1-1');
INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000','345345', '1990-1-1');
INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500','145345', '2001-11-17');
INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600','543453', '1990-1-1');
INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500','7327', '1990-1-1');
INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500','372374', '2001-11-17');
INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500','14510', '2001-11-17');
INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500','11450', '2004-7-1');
INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500',null, '1990-1-1');
INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500','114530', '2001-11-17');
INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500','14210', '1990-1-1');
INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500','14530', '1990-1-1');
INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000','2452110', '1990-1-1');
INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500','14523410', '1895-10-1');
INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600','45342', '2001-11-17');
INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500','175210', '2012-1-15');
INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500','142210', '1990-1-21');
INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500','345373', '2013-10-1');
INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500','175210', '1990-1-1');
INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500','4534', '1990-1-1');
INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500','14510', '2001-11-17');
INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500','157210', '1990-1-31');
INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500','145210', '1990-1-1');
INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000',null, '2001-11-17');
INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500','114520', '1990-1-1');
INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600','11450', '1990-1-1');
INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500','1452410', '2013-1-11');
INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500','18710', '1990-1-1');
INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500','114270', '2001-11-17');
INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500','117520', '1990-1-1');

1. 删除数据
   删除姓名为王刚学生信息

DELETE FROM stu WHERE name='王刚';

删除性别不为女且不为男的所有学生信息

DELETE FROM stu WHERE gender != '女' AND gender != '男';

删除年龄小于20的所有学生信息

DELETE FROM stu WHERE age<20;

删除性别为男且年龄在30岁以上的所有学生信息

DELETE FROM stu WHERE gender='男' AND age >= 30;

删除学号为1, 2,3的学生信息

DELETE FROM stu WHERE sno=1 OR sno=2 OR sno=3;
或
DELETE FROM stu WHERE sno IN (1, 2, 3);

删除电话为null的学生信息

DELETE FROM stu WHERE tel IS NULL;

2. 修改数据
   将salary大于3000的工资增加500

UPDATE stu SET salary=salary+500 WHERE salary>3000;

将年龄大于23的学生工资增加300

UPDATE stu SET salary=salary+300 WHERE age>23;

将学号为1，11，21，31，41的学生，年龄增加一岁，工资增加25%

UPDATE stu SET age=age+1,salary=salary*1.25 WHERE sno=1 OR sno=11 OR sno=21 OR sno=31 OR sno=41;
或
UPDATE stu SET age=age+1,salary=salary*1.25 WHERE sno IN (1, 11, 21, 31, 41);

3. 查询数据
   查询地址在山西的学生数据

SELECT * FROM stu WHERE address='山西';

查看工资超过4000的男同学信息

SELECT * FROM stu WHERE salary>4000 AND gender='男';

查看每个学生工资增长1000以后的信息

SELECT sno,name,age,gender,address,salary+1000,tel,birthday FROM stu;

查看每个学生年薪，年薪是工资*12

SELECT sno,name,age,gender,address,salary*12 AS year_salary,tel,birthday FROM stu;

查看李永的学生信息

SELECT * FROM stu WHERE name='李永';

查看李姓学生的信息

SELECT * FROM stu WHERE name LIKE'李%';

查看1990年出生的学生信息

SELECT * FROM stu WHERE YEAR(birthday)=1990;