mysql 叶子_mysql增删改查操作
CREATE TABLE([列级完整性约束条件]
[,[列级完整性约束条件]]
…
[,]);
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)
/*表级完整性约束条件，Cpon是外码，被参照表是Course,被参照列是Cno*/ 参照表和被参照表可以是同一个表
);
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
FOREIGN KEY(Sno) REFERENCES Student(Sno), /*表级完整性约束条件，Sno是外码，被参照表是Student*/
FOREIGN KEY(Cno)REFERENCES Course(Cno) /*表级完整性约束条件，Cno是外码，被参照表是Course*/
);
修改基本表：
ALTER TABLE
[ADD[COLUMN][完整性约束]]
[ADD]
[DROP[COLUMN][CASCADE|RESTRICT]]
[DROP CONSTRAINT [RESTRICT|CASCADE]]
[ALTER COLUMN ]；
删除基本表：
DROP TABLE [RESTRICT|CASCADE];
索引的建立于删除：
建立索引：
CREATE [UNIQUE] [CLUSTER] INDEX
ON ([] [,[]]…);
例如：
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
修改索引：
ALTER INDEX RENAME TO ；
例如：
ALTER INDEX SC弄RENAME TO SCSno;
删除索引：
DROP INDEX ;
例如：
DROP INDEX Stusname;
数据查询：
SELECT [ALL | DISTINCT][,]…
FROM[,…] | ()[AS]
[WHERE ]
[GROUP BY [HAVING ]]
[ORDER BY [ASC | DESC]];
单表查询：
SELECT Sno,Sname
FROM Student;
SELECT Sname,2014-Sage
FROM Student;
SELECT Sname,’Year of Birth:’,2014-Sage,LOWER(Sdept)
FROM Student;
SELECT Sname,’Year of Birth:’,2014-Sage BIRTHDAY,
LOWER(Sdept) DEPARTMENT
FROM Student; /*起别名*/
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN (‘CS’,’MA’,’IS’);
SELECT Sname,Ssex
FROM Student
WHERE Sname LIKE ‘_刘%’;
SELECT Sname,Sno,Sex
FROM Student
WHERE Cname LIKE ‘DB\_Design’ ESCAPE ‘\‘;
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
SELECT Sname
FROM Student
WHERE Sdept=’CS’ AND Sage<20;
SELECT Sno,Grade
FROM SC
WHERE Cno=’3’
ORDER BY Grade DESC;
多表连接：
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
嵌套查询：
SELECT Sname
FROM Student /*外层查询或父查询*/
WHERE Sno IN
(SELECT Sno
FROM SC /*内层查询或子查询*/
WHERE Cno=’2’);
SELECT Sno,Cno
FROM SC x
WHERE Grade >=(SELECT ABG(Grade)
FROM SC y /*起别名,又称为元组变量*/
WHERE y.Sno=x.Sno);
数据更新：
数据插入：
INSERT INTO [([,]…)]
VALUES([,]…);
例如：
INSERT
INTO SC(Sno,Cno)
VALUES(‘201215128’,’1’)；
插入子查询结果：
INSERT INTO [([,]…)]
子查询;
例如：
INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;
修改数据：
UPDATE
SET = [,=]…
[WHERE]；
例如：
UPDATE Student
SET Sage=22
WHERE Sno=’201215121’;
带子查询的修改语句：
UPDATE SC
SET Grade = 0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept=’CS’);
删除数据：
DELETE
FROM
[WHERE ]；
视图：
建立视图：
CREATE VIEW [(,[,]…)]
AS
[WITH CHECK OPTION]
例如：
CREATE VIEW IS_Studnet
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=’IS’;
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept = ‘IS’
WITH CHECK OPTION; /*定义IS_Student视图时加上了 WITH CHECK OPTION 子句，以后对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上Sdept=’IS’的条件。*/
删除视图：
DROP VIEW [CASCADE];
例如：
DROP VIEW BT_S;
旧城等待，
你的名字
还没有评论，来说两句吧...