-- 创建一个员工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;