SQL常用操作（21天打卡）
1.创建数据库
create DATABASE istester;
2.删除数据库
drop DATABASE istester;
3.创建新表
1）创建 istester 和 doT 表
create table istester (
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
uname VARCHAR(20) NOT NULL,
sex VARCHAR(4),
birth YEAR,
department VARCHAR(20),
address VARCHAR(50),
idoT VARCHAR(20)
);
create table idoT(
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
stu_id INT(10) NOT NULL,
c_name VARCHAR(20),
istester VARCHAR(50),
grade INT(10)
);
2）根据已有的表创建新表（复制表）
create table istester2 like istester;
create table idoT2 as select * from idoT where 2=1;
create table idoT2 as select id,stu_id,istester from idoT where 1<>1;
4.删除表
drop table istester2;
drop table idoT,idoT2,istester,istester2;
;
5.增加字段
Alter table istester add column istester2 VARCHAR(20) NOT NULL
6.主键
1）添加主键
ALTER TABLE istester add primary key(idoT);
说明：若原来表中已有主键，执行时会报错（1068，如下）需要删除原来主键再重新添加；
2）删除主键
ALTER TABLE istester drop primary key;
说明：删除主键是不需要写字段名，否则会报错（1064，语法错误）；若有多个主键，会删除所有主键；
7.几个简单的基本入门sql语句
插入：
INSERT INTO istester(id,uname,idoT) VALUES(1,'idoT',2020);
INSERT INTO istester(id,uname,idoT) VALUES(2,'idoT2',2020);
INSERT INTO idoT(id,stu_id,c_name,grade) VALUES(4,11,'idoT',90);
INSERT INTO istester(id,uname,sex,idoT) values(11,'idoT3',1,2020),(12,'idoT4',2,2020);
删除
delete from istester where id = 1 ;
更新
update istester set uname='idoT666' where id = 12 ;
查找
select * from istester where uname like '%idoT%';
排序
select * from istester order by id desc;
总数
select count(id) as totalcount from istester;
求和
select sum(grade) from idoT;
平均值
select avg(id) as vagvalue from istester;
最大值
select max(id) as "maxvalue" from istester;
最小值
select min(id) as minvalue from istester;
8.模糊查询（like）
select * from istester where uname like '%idoT%' order by id desc limit 5;
select * from istester t where t.uname like '%ido%' order by id desc limit 5;
select * from istester.idoT s where s.stu_id like '%1%';
9.拷贝表数据（从其他表）
INSERT INTO istester2 select * from istester;
INSERT INTO idoxu(id,stu_id,grade) SELECT id,id,idoT FROM istester;
10.修改表名
ALTER TABLE idoxu RENAME TO idoT2;
11.修改表字段名
ALTER TABLE istester CHANGE uname aname varchar(60);
12.跨数据库之间表的拷贝
CREATE TABLE 表名 like 数据库名.表名;
13.查询between的使用
select * from idoT where grade BETWEEN 90 and 95;
select id,stu_id,c_name,grade from idoT where grade not BETWEEN 90 and 95;
14.查询in的使用
select id,stu_id,c_name,grade from idoT where grade in (90,92,93);
select id,stu_id,c_name,grade from idoT where grade not in (90,92,93);
15.子查询
select id,stu_id,c_name,grade from idoT where id in(select id from istester);
16.多表（左链接、右链接、内链接）
左链接
select i.id,i.stu_id,i.c_name,i.grade,t.aname,t.sex from idoT i LEFT JOIN istester t on i.id=t.id;
右链接
select i.id,i.stu_id,i.c_name,i.grade,t.aname,t.sex from idoT i RIGHT JOIN istester t on i.id=t.id;
内链接
select i.id,i.stu_id,i.c_name,i.grade,t.aname,t.sex from idoT i INNER JOIN istester t on i.id=t.id;
17.将查询结果作为表进行查询
select * from (select id,stu_id,c_name,grade from idoT) d where d.id >=3;
18.四表查询
select i.id,i.stu_id,i.c_name,i.grade
from idoT i LEFT JOIN istester t on i.id=t.id
RIGHT JOIN istester2 c on i.id=c.id
INNER JOIN idoT2 d on i.id=d.id
where 1=1;
19.多表复杂查询
select d.*
FROM (select d.id,d.stu_id,d.c_name,d.grade from idoT d order by grade desc limit 10) i,idoT d
where i.id=d.id ORDER BY stu_id desc limit 10;
20.选择从10到15的记录
select *
from (select * from idoT ORDER BY id asc limit 15) i
order by id desc limit 5;
21.创建视图
CREATE VIEW istester_view as select id,stu_id,c_name,grade from idoT where id in (select id from istester);
22.删除视图
视图是基于 SQL 语句的结果集的可视化的表。
drop view istester_view;
