散逝65342758
用sql語句創建student,course,sc表.
create table student
(
id int Not Null,
studentnumber varchar(22),
name id int Not Null,
`````
)
craete table course
(
`````
)
craete table sc
(
``````
)
如何在數據庫中創建選課SC表
先創建需要外鍵參照的兩個主表(學生)主鍵學號，（課程）主鍵課號。
然後創建選課表SC，學號和課號聯合作主鍵，分別作外鍵。
create table sc(學號 char(6)，課號 char(6)，成績 int，primary key （學號，課號）,foreign key (學號)references 學生(學號)，foreign key (課號)references 課程(課號))
sql中SC表是什麼意思？
應該沒猜錯是 學生選課表 student ——class 裏麵應該有 sid cid score這些字段 不知道實際情況是不是
SQL語句的一道題 三個基本表：學生表(Student)、課程表（Course）、學生選課表（SC）
1. select * from SC
2. select Sname，Sage from Student where Sdept = '計算機'
3. select Sno,Cno,Grade from SC where Grade >= 70 and Grade <=>
4. select Sname,Sage from Student where Sage between 18 and 20 and Ssex = '男'
5. select top 1 Grade from SC where Cno = 'C01'
6. select max(Sage),min(Sage) from Student
7. select Sdept,sum(Sno) from Student group by Sdept
8. select course.Cname,sum(sc.Sno),max(Grade) from SC
join studet on Student.Sno = SC.Sno
join Course on Course.Cno = SC.Cno
group by course.cname,max(grade)
9. select sum(Cno),avg(Grade) from SC
join Course on Course.Cno = SC.Cno
join Student on Student.Sno= SC.Sno
order by SC.Sno
10. select Stuent.Sno，Stuent.Sname，sum(Grade) A from SC
join Student on Student.Sno = SC.Sno
group by sc.Sno,student.Sname
having A > 200
11. select Student.Sname,Student.Sdept from Student
join Course on Course.Cno = SC.Cno
join SC on SC.Sno = Student.Sno
where SC.Cno = 'C02'
12. select Student.sname,course.cno,sc.grade from sc
join student on student.sno = sc.sno
join course on course.cno = sc.cno
where sc.grade >= 80
order by sc.grade desc
13. select cno,cname from
(
select course.cno,course.cname,sun(sno) from student
join course on course.cno = sc.cno
join sc on sc.sno = student.sno
group by cno,cname
having sun(sno) > 0
)
14. ① select student.sname,student.sdept from
(
select student.sname,student.sdept,course.cname from student
join sc on sc.sno = student.sno
join course on course.cno = sc.cno
where course.cname = 'C01'
)
② select student.sno,student.sname from
(
select student.sno,student.sname,student.sdept,sc.grade from sc
join student on student.sno = sc,sno
where student.sdept = '信息' and sc.grade >= 80
)
③ select top 1 student.sname from
(
select student.sname,student.sdept,sum(sc.grade) from sc
join student on student.sno = sc.sno
where student.sdept = '計算機'
group by student.sname,student.sdept
order by
)
15. delete from sc where grade <>
16. update sc set grade += 5 from sc
join course on course.cno = sc.cn
join student on student.sno = sc.sno
where student.sno =
(
select student.sno from sc
join course on course.cno = sc.cno
where course.cname = 'c01'
)
17. update sc set grade += 10 from sc
join student on student.sno = sc.sno
where student.sno =
(
select student.sno from student
join sc on sc.sno = student.sno
join course on course.cno = sc.cno
where student.sdept = '計算機' and course.cname = '計算機文化基礎'
)
18. create view [A] as
select student.sno,student.sname,student.sdept,course.cno,course.cname,sc.grade from sc
join student on student.sno = sc.sno
join course on course.cno = sc.cno
19. create view [A] as
select student.sno,avg(sc.grade) from sc
join student on student.sno = sc.sno
group by student.sno
20. create view [A] as
select student.sno,sum(sc.grade) from sc
join student on student.sno = sc.sno
group by student.sno
21. create index A on student(sname)
22. 不會
數據庫中用MYSQL創建SC表時出錯
我寫的是mysql數據庫裏的創建此表的sql語句【已測試可以的】：
CREATE TABLE student(學號 INTEGER,姓名 VARCHAR(22),性別 VARCHAR(22),出生日期 DATE,班級 VARCHAR(22),入學成績 INTEGER ) ;
=====================================-=============-======
你的列名和類型都擠的太緊了，需要空格的~~ 還有最後的入學成績類型n(5,1)是什麼？？
數據庫基礎用SQL語言創建student表 急...........
/*1:建立學生表*/
create table student (
SNO char(10) primary key,
SNAME char(4),
SSEX char(2),
SAGE int,
sdept char(5)
);
/*2:建立course表*/
create table course(
CNO char(10) primary key,
CNAME char(20),
TEACHER char(20)
);
/*3:建立課程表*/
create table SC (
CNO char(5) primary key,
CNAME char(10),
TNO char(3),
foreign key(TNO) references teacher(TNO)
);
/*4:建立SC表*/
create table sc(
SNO char(3),
CNO char(5),
primary key(SNO,CNO),
GRADE VARCHAR2(10),
foreign key(SNO) references student(SNO),
foreign key(CNO) references course(CNO)
);
類似的一個例子 你看著改一下名字 大小就行了
試用SQL查詢語句表達學生-課程數據庫中3個基本表S,SC,C的查詢
1)
CREATE?TABLE?SC(
Sno?int,
Cno?int,
Grade?decimal(3,1)
);
注：
因為不知道?S表的?Sno?的數據類型??和??Couser?的?Cno?的數據類型。
因此這裏暫時把?Sno?與?Cno?數據類型寫成?int,
如果知道那2個表的那2列的數據類型的話。
那麼這裏應該把數據類型修改成和那2列一樣。
2)
SELECT
*
FROM
Course
WHERE
Cname?LIKE?'MIS!_%'?ESCAPE?'!'
AND?Cname?LIKE??'%係_';
3）
SELECT
count(?distinct?Sno??)
FROM
SC;
4）
UPDATE
SC
SET
Grade?=?(?SELECT?AVG(Grade)?FROM?SC?WHERE??Cno?=?3)
WHERE
Sno?=?95003
AND?Cno?=?3;
5）
SELECT
*
FROM
S
WHERE
Sage?>?(?SELECT?MAX(s2.Sage)?FROM?S?s2?WHERE?s2.Sdept?=?'CS'?)
ORDER?BY
Sage;
--?中間可能斷掉了部分題目
（3）
SELECT
S.SName
FROM
S?JOIN?SC??ON??(?S.Sno?=?Sc.Sno)
JOIN?Course??ON?(Course.Cno?=?Sc.Cno)
WHERE
Course.不知道哪一列是?老師列了...
AND??S.Ssex?=?'女';
（4）
SELECT
Cno
FROM
Course
WHERE
NOT?EXISTS(?SELECT?1?FROM?SC??JOIN??S??ON(S.Sno?=?SC.SNO)??WHERE?S.Sname=?'張同學'??AND?SC.Cno?=?Course.Cno);
用SQL語句創建這三張表
CREATE TABLE S
(Sno int PRIMARY KEY,
Sname varchar(10) NOT NULL,
Sex char(2) CHECK (Sex='男' OR Sex='女'),
age int NOT NULL,
dept varchar(10))
GO
CREATE TABLE C
(Sno int IDENTITY(1,1) PRIMARY KEY,
Cname varchar(10) NOT NULL,
Cpno int,
Ccredit int NOT NULL)
GO
CREATE TABLE Sc
(Sno int IDENTITY(1,1) PRIMARY KEY,
Scno int,
Grade int)
Sc表的兩個字段重名，這是不被允許的。
請問如何用SQL語句為指定用戶授權？
SQL語言用GRANT語句向用戶授予操作權限，GRANT語句的一般格式為：
GRANT <權限>[,<權限>]...
[ON <對象類型> <對象名>]
TO <用戶>[,<用戶>]...
[WITH GRANT OPTION];
其語義為：將對指定操作對象的指定操作權限授予指定的用戶。
不同類型的操作對象有不同的操作權限，常見的操作權限如表3-4所示。
表3-4 不同對象類型允許的操作權限
對象 對象類型 操作權限
屬性列 TABLE SELECT, INSERT, UPDATE, DELETE ALL PRIVIEGES
視圖 TABLE SELECT, INSERT, UPDATE, DELETE ALL PRIVIEGES
基本表 TABLE SELECT, INSERT, UPDATE, ALTER, INDEX,DELETE ALL PRIVIEGES
數據庫 DATABASE CREATETAB
詳細信息…
接受權限的用戶可以是一個或多個具體用戶，也可以是PUBLIC即全體用戶。
如果指定了WITH GRANT OPTION子句，則獲得某種權限的用戶還可以把這種權限再授予別的用戶。如果沒有指定WITH GRANT OPTION子句，則獲得某種權限的用戶隻能使用該權限，但不能傳播該權限。
例1
把查詢Student表權限授給用戶U1
GRANT SELECT ON TABLE Student TO U1;
例2
把對Student表和Course表的全部權限授予用戶U2和U3
GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2, U3;
例3
把對表SC的查詢權限授予所有用戶
GRANT SELECT ON TABLE SC TO PUBLIC;
例4
把查詢Student表和修改學生學號的權限授給用戶U4
詳細信息…
這裏實際上要授予U4用戶的是對基本表Student的SELECT權限和對屬性列Sno的UPDATE權限。授予關於屬性列的權限時必須明確指出相應屬性列名。完成本授權操作的SQL語句為：
GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;
例5
把對表SC的INSERT權限授予U5用戶，並允許他再將此權限授予其他用戶
GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;
詳細信息…
執行此SQL語句後，U5不僅擁有了對表SC的INSERT權限，還可以傳播此權限，即由U5用戶發上述GRANT命令給其他用戶。
例如U5可以將此權限授予U6：
GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION;
同樣，U6還可以將此權限授予U7：
GRANT INSERT ON TABLE SC TO U7;
因為U6未給U7傳播的權限，因此U7不能再傳播此權限。
例6
DBA把在數據庫S_C中建立表的權限授予用戶U8
GRANT CREATETAB ON DATABASE S_C TO U8;
如何用SQL語句對所有的用戶表授權
use databasename
grant
[DELETE, INSERT, REFERENCES, SELECT, UPDATE] on tablename to username
go
you can make a loop to go through all the tables and users in the database
轉載請注明出處句子大全網 » 用sql語句創建student,co
