[大数据]MySQL 增删改 insert delete update 实例

-- 创建一个员工emp，选用适当类型
-- id-整型 name-字符型 sex-字符型 birthday-日期型(date)
-- entry_date 日期型(date)? job-字符型 ?
-- salary-小数型??? resume-文本型
-- 表emp上增加image列，varchar类型（要求在resume后面）
-- 修改job列，使其长度60
-- 表名修改为employee
-- 修改表的字符集为utf8
-- 列名name修改为user_name
CREATE TABLE emp (
?? ?id INT,
?? ?`name` VARCHAR(32),
?? ?sex CHAR(1),
?? ?birthday DATE,
?? ?entry_date DATETIME,
?? ?job VARCHAR(32),
?? ?salary DOUBLE,
?? ?`resume` TEXT)CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO emp
?? ?VALUES (100,'小妖怪','男','2000-11-11','2010-11-10 11:11:11',
?? ?'巡山',3000,'大王叫我来巡山')

SELECT * FROM emp;

ALTER TABLE emp
?? ?ADD image VARCHAR(60) NOT NULL DEFAULT ''
?? ?AFTER RESUME;
DESC emp; -- 显示表结构，查看表所有列

ALTER TABLE emp
?? ?MODIFY job VARCHAR(60) NOT NULL DEFAULT '';

ALTER TABLE emp
?? ?DROP sex ;
?? ?
RENAME TABLE emp TO employee;

ALTER TABLE employee
?? ?CHARSET utf8;
DESC employee;

ALTER TABLE employee
CHANGE `name` user_name VARCHAR(32)NOT NULL DEFAULT'';
-- 插入数据
INSERT INTO employee (id,user_name,birthday,entry_date,job,salary,`resume`,image)
?? ?VALUES(22,'jack','1997-2-4','2008-3-2 16:22:11','打虎',3000,'井冈山打虎','凶恶');
SELECT * FROM employee;
-- 修改数据 update
UPDATE employee SET salary = 5000; #没有where修改全部员工
UPDATE employee
?? ?SET salary = 3000
?? ?WHERE user_name = '小妖怪';# where有条件修改
INSERT INTO employee
?? ?VALUES(200,'老妖怪','1992-2-2','2000-1-5 2:2:2','吃喝',6000,'享乐败类','废物');
UPDATE employee
?? ?SET salary = salary + 1000
?? ?WHERE user_name = '老妖怪';
-- 删除数据delete
DELETE?? ?FROM employee
?? ?WHERE user_name = '老妖怪';#删除老妖怪
DELETE FROM employee;#删除所有记录，小心
?? ?
?? ??? ?
SELECT * FROM employee;