CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
#
CREATE TABLE IF NOT EXISTS stu(NO INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAr(20)
);
#插入数据
INSERT INTO stu(NAME)VALUES("张三"),("李四"),("王五"),("六留"),("七七"),("八八");
#创建科目表
CREATE TABLE IF NOT EXISTS sub(subno INT PRIMARY KEY AUTO_INCREMENT,
subname VARCHAr(20)
);
#插入数据
INSERT INTO sub(subname)VALUES("java"),("python"),("html"),("hadoop"),("js");
#创建中间表---分数表
CREATE TABLE IF NOT EXISTS score(NO INT,score INT,subno INT);
#插入数据
INSERT INTO score(NO,score,subno)VALUES(1,88,1),(2,90,3),(3,85,3),
(4,60,4);
#三表关联查询，第一个表关联中间表的结果，然后关联第二个表
#st学生表 sc分数表 su科目表
SELECt st.*,sc.*,su.* FROM stu st
INNER JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERe st.no > 1;
#------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS abc(sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAr(20),
gender VARCHAr(20),
class_jb INT(10)
);
SELECt * FROM abc;
INSERT INTO abc(sname,gender,class_jb)VALUES
("张三","男",1),
("李四","男",1),
("王五","男",1);
SELECt * FROM abc;
CREATE TABLE IF NOT EXISTS cba(cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAr(20),
teacher_id INT(10)
);
SELECt * FROM cba;
INSERT INTO cba(cname,teacher_id)VALUES
("语文",1),
("数学",2),
("英语",3);
CREATE TABLE IF NOT EXISTS sss(sid INT PRIMARY KEY AUTO_INCREMENT,
student_id VARCHAr(20),
corse_id INT(10),
numder INT(100)
);
INSERT INTO sss(student_id,corse_id,numder)VALUES
(1,1,79),
(1,2,80),
(1,3,70),
(2,1,66);
SELECt st.*,sc.*,su.* FROM stu st
INNER JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERe st.no > 1;
RIGHT
#左连接 left join...on
SELECt * FROM abc a LEFT JOIN cba c ON a.sid=c.cid;
#右连接 right join...on
SELECt * FROM abc a RIGHT JOIN cba c ON a.sid=c.cid;
###################################################################################
CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
#建表
CREATE TABLE IF NOT EXISTS emp(emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAr(20),
salary INT,
dept_id INT,
manager_id INT
);
INSERT INTO emp(emp_name,salary,dept_id,manager_id)VALUES("tom",15000,1,NULL),
("lucy",16000,2,NULL),
("morty",14000,5,NULL),
("张建国",4000,1,1),
("李华",6000,1,1),
("孙思妙",3000,1,1),
("王强",6000,2,2),
("周杰",5000,2,2),
("曹光思",7000,5,3),
("刘毅真",8000,5,3),
("孙岩",5000,5,3);
SELECt * FROM emp;
#删除库
DROp DATABASE info;
#删除表
DROP TABLE emp;
#子查询，单值
SELECT * FROM emp WHERe salary >(SELECt salary FROM emp WHERe emp_name = "王强");
SELECt * FROM emp WHERe dept_id (SELECt dept_id FROM emp WHERe dept_name="张建国")
AND salary > (SELECt salary FROM rmp WHERe emp_namr="孙岩");
#子查询，多值
SELECt * FROM emp WHERe dept_id IN (SELECt dept_id FROM emp WHERe emp_name LIKE "孙%");
#子查询，中间表
SELECt * FROM (SELECt * FROM emp WHERe dept_id > 2 AND salary > 5000) e;
CREATE TABLE IF NOT EXISTS emp01(empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAr(20),
job VARCHAr(20),
mgr INT,
hiredate DATE,
aal DOUBLE,
comm DOUBLE,
deptno INT
)AUTO_INCREMENT=1000;
CREATE DATABASE emp01;
INSERT INTO emp01(ename,job,mgr,hiredate,aal,comm,deptno)VALUES
("甘宁","文员",1013,"2000-12-17",8000.00,NULL,20),
("黛琦丝","销售员",1006,"2001-02-20",16000.00,3000.00,20),
("股天正","销售员",1006,"2001-2-22",12500.00,5000.00,20),
("刘备","经理",1009,"2001-04-02",29750.00,NULL,20),
("谢逊","销售员",1006,"2001-09-28",12500.00,14000,30),
("关羽","经理",1009,"2001-05-01",28500.00,NULL,30),
("张飞","经理",1009,"2001-09-01",24500.00,NULL,10),
("诸葛亮","分析师",1004,"2007-04-19",30000.00,NULL,20),
("张无忌","董事长",NULL,"2000-11-17",50000.00,NULL,10),
("韦一笑","销售员",1006,"2001-09-08",15000.00,NULL,30),
("周泰","文员",1008,"2007-05-23",11000.00,NULL,30),
("程普","文员",1006,"2001-12-03",9500.00,NULL,30),
("庞统","分析师",1004,"2001-12-03",30000.00,NULL,20),
("黄盖","文员",1007,"2002-01-23",12000.00,NULL,10),
("张三","保洁员",1001,"2003-05-01",80000.00,50000,50);
SELECt * FROM emp01;
SELECt * FROM emp01 WHERe aal >(SELECt aal FROM emp01 WHERe ename = "关羽");
##########################################################################################
#1.1创建索引:单独创建索引
CREATE INDEX index_name ON emp(emp_name);
SELECt *FROM emp WHERe emp_name = "lucy";
#1.2创建索引:修改表结构创建索引
ALTER TABLE emp ADD INDEX index_salary(salary);
#1.3创建索引:创建表时，创建索引
CREATE TABLE IF NOT EXISTS article(id INT NOT NULL AUTO_INCREMENT,
title VARCHAr(30),
content VARCHAr(50),
dt DATE,
PRIMARY KEY(id),
INDEX index_title (title)
);
DROp TABLE article;
#2.删除索引
DROP INDEX index_name ON emp;
#3.1唯一索引:列的值必须唯一
CREATE UNIQUE INDEX index_name ON emp(emp_name);
#3.2修改表创建唯一索引?
#3.3修改表时创建唯一索引?
#6.组合索引
CREATE INDEX index_name ON emp(emp_name);
SELECT * FROM article WHERe content="123" AND dt="2020-10-10";
