/*

创建表格

*/

CREATE TABLE dept(

DEPTNO INT PRIMARY KEY AUTO_INCREMENT,

`NAME` VARCHAR(20),

LOC VARCHAR(20)

);

CREATE TABLE emp(

EMPNO INT PRIMARY KEY AUTO_INCREMENT,

ENAME VARCHAR(20),

JOB VARCHAR(20),

MGR INT,

HIREDATE DATE,

SAL DOUBLE,

COMM DOUBLE,

DEPTNO INT

)

CREATE TABLE salgrade(

GRADE INT PRIMARY KEY AUTO_INCREMENT,

LOSAL INT,

HISAL INT

)

--删除表

DROP TABLE dept

DROP TABLE emp

DROP TABLE salgrade

/*

添加数据

*/

INSERT INTO emp 

VALUES(NULL,"张三0","程序猿",137,"1954-3-3",200,250,7),

(NULL,"张三1","程序猿",137,"1954-3-3",200,250,7),

(NULL,"张三2","攻城狮",137,"1958-3-9",200,70,6),

(NULL,"张三3","程序猿",182,"1924-3-3",220,20,7),

(NULL,"张三4","加班狗",137,"1949-10-1",250,280,3),

(NULL,"张三5","程序猿",151,"1954-3-3",200,250,7),

(NULL,"张三6","程序猿",137,"1954-3-3",240,3000,7),

(NULL,"张三7","狗腿子",188,"1954-3-3",280,250,5),

(NULL,"张三8","程序猿",137,"1955-3-3",200,270,7),

(NULL,"张三9","老板娘",159,"1974-3-3",210,250,1)

--查询

SELECT ename FROM emp

SELECT * FROM emp WHERE ename = "张三0"

SELECT * FROM emp WHERE ename = "张三4" AND sal = 250

SELECT * FROM emp

SELECT ename,sal * 12 AS 年薪 FROM emp 

--练习

CREATE DATABASE db2 CHARSET utf8;

SHOW DATABASES

ALTER DATABASE db2 CHARSET gbk;

SHOW CREATE DATABASE db2

DROP DATABASE db2

CREATE TABLE t1(

l1 INT PRIMARY KEY AUTO_INCREMENT,

l2 DOUBLE NOT NULL,

l3 VARCHAR(20) DEFAULT "ctrl",

l4 VARCHAR(20) UNIQUE,

l5 DATE,

l6 TIME,

l7 DATETIME,

l8 TIMESTAMP

)

INSERT INTO t1 

VALUES(NULL,2.3,DEFAULT,"dd","2020-2-20","15:3:3","2020-2-20 15:3:3","2020-2-20 15:3:3")

INSERT INTO t1(l4)

VALUES("ad")

TRUNCATE TABLE t1

ALTER TABLE t1 CHANGE l7 l7 DATETIME UNIQUE

INSERT INTO t1(l6) VALUES("15:3:3")

DELETE FROM t1 WHERE l1 = 4

ALTER TABLE t1 CHANGE l6 l6 TIME UNIQUE;

UPDATE  t1 SET l2 = 1 WHERE l4 = 'ad'

DESC t1

ALTER TABLE t1 DROP l8

ALTER TABLE t1 ADD l8 TIMESTAMP

ALTER TABLE t1 DROP l4

ALTER TABLE t1 ADD l4 VARCHAR(20) AFTER l3

SHOW DATABASES