SQL常用语句

alter table t_user ADD id_num VARCHAR(255) not null

select *from t_user

select *from thing

alter table thing MODIFY thing_id int AUTO_INCREMENT


alter table thing add CONSTRAINT f1 FOREIGN KEY(id_num)  REFERENCES t_user(id_num)

alter table t_user MODIFY id  int    auto_increment

INSERT into t_user(username,password,sex,age,id_num)VALUES('刘娜','123','女',21,'20180217')
-- 更新 id=2 的性别为女
update t_user set id=2 where sex='女'

alter table thing  drop COLUMN thing_id 
-- 添加thing_id 为主键自动增长
alter table thing add thing_id int PRIMARY KEY  auto_increment not null 
-- 插入数据
insert into thing (id_num,thing_name,price)VALUES('20180216','流星蝴蝶剑',20000)

insert into thing (id_num,thing_name,price)VALUES('20180216','轩辕剑',30000)

insert into thing (id_num,thing_name,price)VALUES('19990201','大锤子',5000)
INSERT into thing  (id_num,thing_name,price)VALUES('20180217','血无痕',80000)

-- 查找用户总价最高的那条数据，用户所有信息查出来
select thing.id_num,username,password,sex,age,sum(thing.price) from t_user,thing where t_user.id_num=thing.id_num group by thing.id_num desc limit 1

-- 创建teacher表
create table teacher(t_id varchar(32) PRIMARY KEY not null,t_name VARCHAR(32) not null,sex varchar(32) not null,t_age int not null,t_subject varchar(32) not null)

-- 创建student表
create table student(s_id varchar(32) PRIMARY key not null,s_name varchar(32) not null,t_subject varchar(32) not null,s_sorce double not null)
-- 给student 添加外键

alter table student add CONSTRAINT f1 FOREIGN KEY(t_subject) REFERENCES teacher(t_subject)
-- 查看外键名字
-- show create table student 
-- 给主键表teacher 添加数据

insert into teacher VALUES('20180201','曹煌雯','男',21,'数学')
insert into teacher VALUES('20180202','刘娜','女',21,'英语 ')
insert into teacher VALUES('20180203','曾合群','男',21,'语文')
select *from teacher
-- 删除student 主键
alter table student drop PRIMARY key 
-- 给student添加数据
-- 学生张三
insert into student values('20210201','张三','语文',80)
insert into student values('20210201','张三','数学',85)
insert into student values('20210201','张三','英语',72)
-- 学生李四

insert into student values('20210202','李四','语文',63)
insert into student values('20210202','李四','数学',100)
insert into student values('20210202','李四','英语',59)
select *from student 

-- 查找学生的总分
select sum(s_sorce),s_name from student GROUP BY s_id
-- 查找各个学科老师教学生的分数，及其老师的信息
select t_name,teacher.t_subject,s_sorce from student,teacher where student.t_subject=teacher.t_subject 
-- 查询每个学科最高分
select t_subject as '学科',max(s_sorce) as '最高分' from student GROUP BY t_subject
-- 查询s_id 最大的信息
select *from student where s_id in (select max(s_id)from student)
-- 查询所有学科成绩的最高分
select max(s_sorce),t_subject from student GROUP BY  t_subject 
-- 查询出所有学科平均分数
select AVG(s_sorce),t_subject from student GROUP BY t_subject 
-- 查询出所有学科最低分
select MIN(s_sorce),t_subject from student GROUP BY t_subject
-- 查询所有学科成绩的最高分并且显示最高分名字的人是谁，授课教师是谁
select student.s_name as '姓名',student.s_sorce as '分数',teacher.t_subject as '学科',t_name  as '授课老师' from student,teacher where student.t_subject=teacher.t_subject    and  student.s_sorce in (select max(student.s_sorce) from student GROUP BY student.t_subject DESC) 
-- 创建视图，查询各个学科每个学科最高分，学生名字
create view view_1
as
select s_name as '学生姓名',s_sorce as '最高分',t_subject as '学科' from student where s_sorce in (select max(s_sorce) from student GROUP BY t_subject)
-- 查询视图
select *from view_1
-- 删除视图
drop view view_1
-- 创建存储过程，输入学生id就可以查到他所有信息
delimiter$$
CREATE PROCEDURE pro_stu(IN ssid varchar(32))
BEGIN
select *from student where s_id=ssid;
end$$
delimiter;
-- 调用存储过程
CALL pro_stu('20210202')

create table goods(id int PRIMARY KEY not null auto_increment,name varchar(32) not null,num int not null)

create table orders(o_id int PRIMARY KEY not null auto_increment,goods_id int not null, goods_num int not null)

select *from goods

select *from orders

-- 插入goods数据

INSERT into goods(name,num)values('手机','20')
insert into goods(name,num)values('电脑','10')
-- 创建触发器增加订单
delimiter$$
CREATE TRIGGER tri_1 AFTER INSERT on orders for EACH ROW
begin
-- 更新商品库库存
update goods set num=num-1 where id=1;
end$$
delimiter;
-- 触发器删除订单
delimiter$$
CREATE TRIGGER tri_2 AFTER DELETE on orders for each row
BEGIN
-- 更新库存
update goods set num=num+1 where id=1;
end$$
delimiter;
-- 触发触发器tri_1
insert into orders(goods_id,goods_num)values(1,1)
-- 查看是否成功,如果成功库存将会减一
select *from goods
select *from orders

-- 触发触发器tri_2,成功则goods表里数据+1
DELETE from orders where o_id=1

-- 删除上面的触发器
drop TRIGGER tri_1
drop TRIGGER tri_2
-- 上面的触发器不自动，得创建自动的tri_3
delimiter$$
CREATE TRIGGER tri_3 AFTER INSERT on orders for EACH ROW
begin
-- id=new.goods_id 意思是goods的id，因为到时候得插入商品表里的ID
update goods set num=num- new.num  where id= new.goods_id;  
end$$
delimiter;
-- 创建存储过程
delimiter$$
create PROCEDURE pro_1(in p_name varchar(32))

begin
select*from goods where name=p_name;
end$$
delimiter;

CALL pro_1('电脑')