编辑代码

CREATE DATABASE ems CHARACTER SET utf8mb4;

USE ems;

CREATE TABLE emp(
empno   INT PRIMARY KEY,
ename   VARCHAR(20) UNIQUE NOT NULL,
job     VARCHAR(20) NOT NULL,
mgr     INT ,
sal     DECIMAL(7,2),
comm    DECIMAL(7,2),
deptno  INT  
) ;

INSERT INTO emp VALUES(9902,'赵六','分析员',9566,4000,NULL,20);
INSERT INTO emp VALUES(9966,'李四','分析员',6655,4000,NULL,20);
INSERT INTO emp (empno,ename,job,sal,deptno)
VALUES(9839,'刘一','董事长',6000,10);
INSERT INTO emp
SET empno=9369,ename='张三',job='保洁',
mgr=9902,sal=900.00,comm=NULL,deptno=20;

INSERT INTO emp
VALUES
(9499,'孙七','销售',9698,2600,300,30),
(9521,'周八','销售',9698,2250,500,30),
(9654,'吴九','销售',9698,2250,1400,30),
(9982,'陈二','经理',9839,3450,NULL,10),
(9988,'王五','分析员',9566,4000,NULL,20),
(9844,'郑十','销售',9698,2500,0,30),
(9900,'萧十一','保洁',9698,1050,NULL,30);

UPDATE emp SET sal=sal+200 WHERE ename='张三';
UPDATE emp SET sal=sal+300 WHERE job='保洁';
UPDATE emp SET sal=sal+500;
DELETE FROM emp WHERE ename='孙七';
DELETE FROM emp WHERE job='分析员';
DELETE FROM emp;
CREATE TABLE tab_truncate(
    id INT(3) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(4)
);
CREATE TABLE dept_index(
	id INT,
	deptno  INT ,
	dname   VARCHAR(20),
	introduction VARCHAR(200),
	address GEOMETRY NOT NULL SRID 4326,
	PRIMARY KEY(id),        -- 创建主键索引
	UNIQUE INDEX (deptno),  -- 创建唯一性索引
	INDEX (dname),          -- 创建普通索引
	FULLTEXT (introduction),-- 创建全文索引
	SPATIAL INDEX (address) -- 创建空间索引
) ;

INSERT INTO tab_truncate(name) VALUES('A'),('B'),('C'),('D'),('E');
TRUNCATE TABLE tab_truncate;
SHOW CREATE TABLE emp\G

SELECT * FROM tab_truncate