mysql修改员工表_mysql数据库简单练习（创建表格，增删改查数据）

1.创建一个员工表

字段     属性

id        整形(int)

name     字符串(varchar)(长度为20)

gender    字符串(长度为2)

birthday  日期型(date)

email       字符串(长度为10)

remark     字符串(长度为50)

CREATE TABLE workers(

sid INT,

sname VARCHAR(20),

gender VARCHAR(2),

birthday DATE,

email VARCHAR(10),

remark VARCHAR(50)

);

2.修改表练习

2.1 在员工表基础上增加age列

2.2 修改email列长度为50

2.3 删除remark列

2.4 列名name修改为username

ALTER TABLE workers ADD COLUMN age INT;

ALTER TABLE workers MODIFY COLUMN email VARCHAR(50);

ALTER TABLE workers DROP COLUMN remark;

ALTER TABLE workers CHANGE COLUMN sname username VARCHAR(20);

3 在员工表上练习数据的增删改操作

/*查看员工表格的所有数据*/

SELECT * FROM workers;

/*查看员工表格的sid，username,age数据*/

SELECT sid,username,age FROM workers;

/*查看员工表格的username,age数据*/

SELECT username,age FROM workers;

/* 将以下数据填入表格*/

INSERT INTO workers VALUES(1,'张三丰','男','1367-10-21','12321222@qq.com',102);

INSERT INTO workers VALUES(2,'达摩','男','1227-4-15','1121213242@126.com',54);

INSERT INTO workers VALUES(3,'梅超风','女','1547-6-1','232442@163.com',44);

INSERT INTO workers VALUES(4,'三体星人','未知','3012-8-15','12345678@fly.com',2000);

INSERT INTO workers VALUES(5,'超级赛亚人','男','1985-2-3','whosyourdaday@sina.com',25);

/*将sid为4的那一行的username改为“弗利萨”*/

UPDATE workers SET username = '弗利萨' WHERE sid = 4;

/*删除age为2000的那一行*/

DELETE FROM workers WHERE age = 2000;

/*将sid为4的那一行的age设为250*/

UPDATE workers SET age = 250 WHERE sid =4;

/*将sid为5的那一行的username改为“孙悟空”，年龄改为26*/

UPDATE workers SET username = '孙悟空',age = '26' WHERE sid = 5;