sql语句
一、数据定义
1.基本表
定义：
create table <表名> (<列名> <数据类型> [列级完整性约束条件]
,[ <列名> <数据类型> [列级完整性约束条件] ]);示例：
create table Course
(Cno char(4) primary key,
Cname char(40),
Cpno char(4),
Ccredit smallint,
Foreign key (Cpno) references Course (Cno)
);ps:定义表的数据类型要指出变量类型和长度 ，eg. char(40)
修改：
alter table <表名>/*注意!!修改表里面只有这五种操作，加/删除约束，加/删除/修改列!!,是不允许修改约束的，只能先drop再add*/
[add [column] <新列名> <数据类型> [完整性约束] ]
[add <表级完整性约束>]
[drop [column] <列名> [cascade|restrict]]
[drop constraint <完整性约束名> [restrict|cascade]]
[alter column <列名> <数据类型>];
/*
多个外键约束如sc表时候一定要加上约束名，以及使用on delete cascade可以更完整地约束
constraint fk_sno FOREIGN KEY ( Sno ) REFERENCES S ( Sno ) on delete cascade,
constraint fk_pno FOREIGN KEY ( Pno ) REFERENCES P ( Pno ) on delete cascade,
*/删除：
drop table <表名> [restrict|cascade];
建表时外键&主键&用户自定义完整性约束check语句：
create table S
(Sno char(5) primary key,
Sname char(8),
Ssex char(2) check (Ssex in ('男','女')),
Sage smallint,
Sdept char(2)
);
create table T
(Tno char(3) primary key,
Tname char(8),
Tsex char(2) check (Tsex in ('男','女')),
Tdept char(2)
);
create table P
(Pno char(2) primary key,
Pname char(20),
Tno char(3) references T(Tno)
);
create table SP
(Sno char(5),
Pno char(2),
Grade smallint,
primary key(Sno,Pno),
foreign key(Sno) references S(Sno),
foreign key(Pno) references P(Pno),
check (Grade >= 0 and Grade <=100)
)Insert into插入多行语句：
INSERT INTO P ( Pno, Pname, Tno )
VALUES
( '1', '数据库设计项目', '101' ),
( '2', '无人机飞行设计项目', '103' ),
( '3', '校园网络规划项目', '102' ),
( '4', '操作系统设计项目', '101' ),
( '5', '视觉处理项目', '102' ),
( '6', '大模型构建项目', '104' );二、数据查询
单表查询：
嵌套查询：
SELECT
tname
FROM
T
WHERE
tno IN /*括号一定要加，集合一定要加括号!!*/
(SELECT tno
FROM
p
WHERE
pname = '无人机飞行设计项目');eg.查询只参加一个项目的学生的学号。
select sno
from sp
group by sno/*必须使用group进行分组*/
having count(*) =1;/*只能用having语句跟在group by语句后面进行筛选，where语句用于group和聚集函数之前过滤数据*/eg.查询参加了所有项目的学生姓名。
select sname
from s
where not exists/*两次not exists语句进行相关子查询，每次选一个sno，进行pno的循环，在sp中找出全部满足条件的*/
( select *
from p
where not exists
( select *
from sp
where pno=p.pno and sno=s.sno
)
)eg.查询所有项目成绩均及格的学生的学号和平均成绩,其结果按平均成绩的降序排列。
select sno,avg(grade)
from sp sp1
where not exists/*not exists得有括号！！！*/
(select *
from sp sp2
where sp1.sno=sp2.sno and sp2.grade<60)
group by sno
order by avg(grade) desceg.查询参与1号项目，且成绩排名第2的学生姓名。（成绩相同按相同名次处理，比如88，88，85，则85为第2名）
select sno
from sp
where pno='1' and grade in(
select max(grade)
from sp
where pno='1' and grade <(/*循环一次找到最大，再循环找到次大*/
select max(grade)
from sp
where pno='1'
)
)三、数据更新
插入：
插入元组：
insert into <表> [ 属性列1,属性列2... ]
values (常量1,常量2...)插入子查询：
insert into <表> [ 属性列1,属性列2,... ]
子查询；insert into dept_age(Sdept,Avg_age)
select Sdept,AVG(Sage)
from Student
group by Sdept;修改：update-set语句
update sp--表名
set grade = grade*0.95
where pno='4'删除：
/*适用于单步调试功能，选定程序段来运行*/
set autocommit = false/*必须写上，否则自动提交就不能rollback回滚了*/
select*
from p;
delete
from p
where pno='5';
rollback;创建用户&授权
create user 'Root3'@'localhost'
identified by 'Root3'
/*必须单次对单个表授权，不能一起*/
grant select
on table s
to 'Root3'@'localhost'
grant select
on table p
to 'Root3'@'localhost'
grant select
on table t
to 'Root3'@'localhost'
grant all privileges
on table sp
to 'Root3'@'localhost'以下是 CREATE USER 语句的基本语法：
CREATE USER [IF NOT EXISTS] account_name
IDENTIFIED BY 'password'; 在 CREATE USER 关键字后指定帐户名称。帐户名称由两部分组成： username 和 hostname ，以 @ 符号分隔：
username@hostname username 是用户的名称。而 hostname 从用户连接到 MySQL 服务器的主机名。
hostname 帐户名称的部分是可选的。如果省略它，用户可以从任何主机连接。
没有主机名的帐户名等效于：
username@%触发器自定义抛出异常中断：
CREATE TRIGGER before_insert_S
BEFORE INSERT ON S
FOR EACH ROW
BEGIN
DECLARE rowCount INT;
-- 获取当前表中的行数
SELECT COUNT(*) INTO rowCount FROM S;
-- 如果表中的行数大于18，则阻止插入
IF rowCount > 18 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot insert more than 18 rows into table S';
END IF;
END
create trigger after_update_studentinfo
after update on student_info
for each row
begin
if(new.id<>old.id) then-- if后必须有then!!!
update student_login
set id=new.id
where id=old.id;
end if;-- end if后面必须加分号!!!
end
浙公网安备 33010602011771号