MySQL单表操作

--创建一个员工信息数据库
CREATE	DATABASE	Employee_information;

--创建员工信息表
CREATE	TABLE	USER(
	ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(100) NOT NULL,
	AGE INT NOT NULL,
	SEX CHAR(1) NOT NULL DEFAULT 'M',
	WAGES DOUBLE DEFAULT 0
);

--插入五条数据
INSERT INTO USER(NAME,AGE,WAGES) VALUES
	("Milke",31,10000),
	("Nick",36,12000),
	("Rich",33,70000);

INSERT INTO USER(NAME,AGE,SEX,WAGES) VALUES
	("Rose",32,'W',8000),
	("Marry",25,'W',9800);

--修改Rich数据工资数据错误
UPDATE USER SET WAGES = 7000 WHERE NAME = "Rich";
	
--查询表中所有数据
SELECT ID AS "ID", NAME AS "姓名",AGE AS "年龄",SEX AS "性别",WAGES AS "工资" FROM USER;

--查询所有男性员工数据
SELECT ID AS "ID", NAME AS "姓名",AGE AS "年龄",SEX AS "性别",WAGES AS "工资" FROM USER WHERE SEX = 'M';

--查询所有年龄超过30的女性教师信息
SELECT ID AS "ID", NAME AS "姓名",AGE AS "年龄",SEX AS "性别",WAGES AS "工资" FROM USER WHERE AGE >30 AND SEX = 'W';

--查询所有年龄超过30或者工资大于10000的教师信息
SELECT ID AS "ID", NAME AS "姓名",AGE AS "年龄",SEX AS "性别",WAGES AS "工资" FROM USER WHERE AGE >30 OR WAGES >= 10000;

--查询工资排行前3位的教师信息
SELECT ID AS "ID", NAME AS "姓名",AGE AS "年龄",SEX AS "性别",WAGES AS "工资" FROM USER ORDER BY WAGES DESC LIMIT 3;

--查询所有M开头的员工信息
SELECT ID AS "ID", NAME AS "姓名",AGE AS "年龄",SEX AS "性别",WAGES AS "工资" FROM USER WHERE NAME LIKE "M%";

--删除Mike的教师信息
DELETE FROM USER WHERE NAME = "Milke";

--修改所有女性员工的工资，源于有基础上增加5000
UPDATE USER SET WAGES = WAGES+5000 WHERE SEX = "W";


--创建一张employee员工表，包含以下信息
CREATE	TABLE	employee(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(200) NOT NULL,
	age INT NOT NULL,
	sex CHAR(2) NOT NULL DEFAULT '男',
	address VARCHAR(200),
	phone INT(11)
);

--修改员工表的结构，添加一列信息，学历（education）
ALTER TABLE employee ADD COLUMN education VARCHAR(100);

--修改员工表的结构，删除一列信息，家庭住址	（drop column）
ALTER TABLE employee DROP COLUMN address;

--将employee表的名字修改为emp（sqlite不可直接修改“列”名）
RENAME TABLE employee TO emp;

--向员工表添加如下信息
INSERT INTO emp(name,age,sex,phone,education)  VALUES
	("A",22,'男',123456,"小学"),
	("B",21,'男',119,"中学"),
	("C",23,'男',110,"高中"),
	("D",18,'女',114,"大学");

--修改员工表的数据，将电话号码以11开头的学员的学历改为“大专”
UPDATE emp SET education = "大专" WHERE phone LIKE "11%";

--删除员工表的数据，姓名以C开头，性别为"男"的记录删除
DELETE FROM emp WHERE name LIKE "C%" AND sex = '男';

--查询员工表的数据，将所有年龄小于22岁的，学历为“大专”的，员工的姓名和学号显示出来
SELECT ID AS "学号",name AS "姓名" FROM emp WHERE  education = "大专" AND age <= 22;


--查询员工表的数据，查询所有信息，列出前3项的记录(limit)
SELECT ID AS "学号",name AS "姓名",age AS "年龄",sex AS "性别",phone AS "电话",education AS "专业" FROM emp LIMIT 3;

--查询出所有员工的姓名，性别，年龄降序排列
SELECT ID AS "学号",name AS "姓名",age AS "年龄",sex AS "性别",phone AS "电话",education AS "专业" FROM emp ORDER BY name,sex,age DESC;