4.8号下午课程

今天下午学的是用sqlyou操作

示例写法：

-- 删除数据库
DROP DATABASE java0203;
-- 创建数据库
CREATE DATABASE java0223 CHARACTER SET utf8;
-- 创建表
CREATE TABLE student(
sid INT,
sname VARCHAR(20),
ssex CHAR(5),
birth DATE,
money DOUBLE
)
-- 新增所有数据
INSERT INTO student VALUES(1,'海绵宝宝','男','1997-07-07',50.8);
-- 新增部分字段
INSERT INTO student(sid,sname,birth) VALUES(2,'派大星','1998-08-08');
-- 修改数据
UPDATE student SET ssex='女' WHERE sid=2;
UPDATE student SET money=60 WHERE sid=2;
UPDATE student SET ssex='女',money=100 WHERE sid=1;
-- 不加条件修改所有的语法
UPDATE student SET ssex='男';
-- 删除数据
DELETE FROM student WHERE sid=2;
-- 清空表里的数据
DELETE FROM student;
-- 专门清除表数据
TRUNCATE TABLE student;
-- 查询表中所有数据
SELECT * FROM student;

 

作业练习：

-- 创建新的数据库
CREATE DATABASE java0408xiawu CHARACTER SET utf8;
-- 创建表
CREATE TABLE emp(
empno INT,
ename VARCHAR(5),
job VARCHAR(5),
mgr CHAR(4),
hiredate DATE,
sal DOUBLE,
comm DOUBLE,
deptno CHAR(4)
)
-- 新增所有数据
INSERT INTO emp VALUES(1001,'甘宁','文员',1013,'2000-12-17',8000.00,NULL,20);
INSERT INTO emp VALUES(1002,'黛绮丝','销售员',1006,'2001-02-20',16000.00,3000.00,30);
INSERT INTO emp VALUES(1003,'殷天正','销售员',1006,'2001-02-22',12500.00,5000.00,30);
INSERT INTO emp VALUES(1004,'刘备','经理',1009,'2001-04-02',29750,NULL,20);
INSERT INTO emp VALUES(1005,'谢逊','销售员',1006,'2001-09-28',12500,14000,30);
INSERT INTO emp VALUES(1006,'关羽','经理',1009,'2001-05-01',28500,NULL,30);
INSERT INTO emp VALUES(1007,'张飞','经理',1009,'2001-09-01',24500,NULL,10);
INSERT INTO emp VALUES(1008,'诸葛亮','分析师',1004,'2007-04-19',30000,NULL,20);
INSERT INTO emp VALUES(1009,'曾阿牛','董事长',NULL,'2001-11-17',50000,NULL,10);
INSERT INTO emp VALUES(1010,'韦一笑','销售员',1006,'2001-09-08',15000,0.00,30);
INSERT INTO emp VALUES(1011,'周泰','文员',1008,'2007-05-23',11000,NULL,20);
INSERT INTO emp VALUES(1012,'程普','文员',1006,'2001-12-03',9500,NULL,30);
INSERT INTO emp VALUES(1013,'庞统','分析师',1004,'2001-12-03',30000,NULL,20);
INSERT INTO emp VALUES(1014,'黄盖','文员',1007,'2001-01-23',13000,NULL,10);
INSERT INTO emp VALUES(1015,'张三','保洁员',1001,'2013-05-01',80000,50000,50);
-- 删除数据
TRUNCATE TABLE emp;
-- 查询表中所有数据
SELECT * FROM emp;