Mysql

-- 重置表中的自增字段为1
ALTER TABLE course3 AUTO_INCREMENT=1;
-- 插入部分数据其他值自动生成
INSERT INTO course3(c_name,c_teacher) VALUES('数学','张无忌');
INSERT INTO course3(c_name,c_teacher) VALUES('语文','张三丰');
INSERT INTO course3(c_name,c_teacher) VALUES('英语','杨过');
INSERT INTO course3(c_name,c_teacher) VALUES('物理','郭靖');

-- 插入数据

INSERT INTO course3(c_name,c_teacher) VALUES
('英语1','杨过'),
('物理2','郭靖'),
('数学3','张无忌');



--  插入所有数据值一一对应
INSERT INTO course3 values(NULL,'化学','洪七公',CURTIME(),2);
INSERT INTO course3 values(Null,'地理','梁静茹',CURTIME(),2);

INSERT INTO course3(c_name,c_teacher) VALUES('生物','杨老师');
INSERT INTO course3(c_name,c_teacher) VALUES('政治','郭老师');
INSERT INTO course3(c_name,c_teacher) VALUES('小学数学','杨过');
INSERT INTO course3(c_name,c_teacher) VALUES('大学物理','郭靖');

----------------------------------------------------------
-- 子查询
CREATE TABLE score(
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY key,
s_name char(10) DEFAULT NULL,
score INT(3) DEFAULT 0,
c_name char(10) DEFAULT NULL)COMMENT'学生成绩表';


INSERT INTO score(s_name,score,c_name) VALUES
('刘一',79,'18期'),
('陈二',71,'18期'),
('张三',85,'18期'),
('李四',60,'19期'),
('王五',67,'19期'),
('赵六',70,'19期'),
('孙七',100,'20期'),
('周八',88,'20期'),
('吴九',89,'21期'),
('郑十',92,'21期');

-- 查询出成绩高于平均分的学生信息
SELECT * FROM score WHERE score>(SELECT AVG(score.score) FROM score);

-- 查询出高于本班学生平均成绩的学生成绩
SELECT * FROM score 
where c_name='19期' AND score>(SELECT AVG(score) FROM score WHERE c_name='19期');

-- 相关子查询
-- in 子查询
-- select * from 表名 where 字段 in(数据集合);
SELECT * FROM score WHERE id in (1,5,8);


---------------------------------------------------------

CREATE TABLE course3(
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,  -- 主键自增 AUTO_INCREMENT
c_name CHAR(20) NOT NULL UNIQUE KEY,
-- c_teacher CHAR(10),
c_time TIMESTAMP DEFAULT now()
-- type TINYINT(1) DEFAULT 3
);

-- 添加表的字段 ALTER 表 ADD 新字段
ALTER TABLE course3 ADD c_teacher CHAR(10);
ALTER TABLE course3 ADD type TINYINT(1) DEFAULT 1;

-- 在某个字段后添加字段 AFTER 关键字
ALTER TABLE course3 ADD c_teacher CHAR(10) AFTER c_name, ADD type TINYINT(1) DEFAULT 4;

-- 修改某个字段 MODIFY 关键字
ALTER TABLE course3 MODIFY type INT UNSIGNED DEFAULT 2;

ALTER TABLE students ADD nianlin INT(2) AFTER sex;
ALTER TABLE students ADD score FLOAT(4) AFTER id;

-------------------------------------------------------
-- 修改数据记录 UPDATE 表 SET 字段='' 
UPDATE course3 SET c_teacher='苍老师';


UPDATE course3 SET c_teacher='郭靖' where id =10 OR c_name='英语';

--------------------------------------------------------
CREATE TABLE girls(
girl VARCHAR(20),
mutchNum INT);


CREATE TABLE boys(
boy VARCHAR(20),
mutchNum INT);


-- 插入数据
INSERT INTO girls(girl,mutchNum) VALUES
('girl-01',1),
('girl-02',2),
('girl-03',3),
('girl-04',4),
('girl-05',5),
('girl-06',6),
('girl-07',7);

INSERT into boys(boy,mutchNum) VALUES
('boy-01',1),
('boy-02',2),
('boy-03',3),
('boy-04',4),
('boy-08',8),
('boy-09',9),
('boy-10',10);


-- 内连接  INNER JOIN 等值连接，左右表符合连接条件的记录
select * from boys INNER JOIN girls;
select * from boys,girls;
select * from boys join girls; -- 隐式连接
select * from boys CROSS JOIN girls; -- 交叉连接



-- 查询出mutchNum相等的信息 等值连接
select * from boys INNER JOIN girls where boys.mutchNum=girls.mutchNum;
select * from boys JOIN girls where boys.mutchNum=girls.mutchNum;
select * from boys INNER JOIN girls on boys.mutchNum=girls.mutchNum;
select * from boys INNER JOIN girls USING(mutchNum);  -- 相同字段mutchNum显示

-- 查询出mutchNum相等的信息
select * from boys LEFT join girls 
on boys.mutchNum=girls.mutchNum
where girls.mutchNum IS NOT NULL;

-- 查询出boys中没有匹配到girls的
select * from boys LEFT join girls
on boys.mutchNum=girls.mutchNum
where girls.mutchNum is null;

-- 查询出girls中没有匹配到boys的信息
select * from boys RIGHT JOIN girls ON boys.mutchNum=girls.mutchNum
where boys.mutchNum is NULL;

select boys.boy,boys.mutchNum from boys RIGHT JOIN girls on boys.mutchNum=girls.mutchNum where boys.mutchNum is NOT NULL;


-- 左连接 LEFT JOIN 读取左表的全部数据，右表不匹配条件则为null
select * from boys LEFT join girls on boys.mutchNum=girls.mutchNum;


-- 右连接 RIGHT JOIN 与左连接刚好相反
select * from boys RIGHT JOIN girls on boys.mutchNum=girls.mutchNum


-- 全连接，full join 返回左右表的所有数据信息 mysql没有该字段，oracle中有的。
select boy 嘉宾,mutchNum 编号 from boys UNION select girl 嘉宾,mutchNum 编号 from girls;  -- 将两个表整合为一个信息表

 

--sql随机查询：clickhouse查询也适用
select *  from table_name where date between '2019-10-01' and '2019-12-31' and id='310100' order by rand() limit 30;