部分 语法Mysql

##1.创建数据库
CREATE DATABASE S2230MySchool
##2.创建数据表
CREATE TABLE Student
(
stuNo INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
stuName VARCHAR(32) NOT NULL,
stuGradeid INT ,
stuGender INT,
stuBirthday DATETIME,
stuPhone VARCHAR(11),
stuEmail VARCHAR(128),
stuAddress VARCHAR(128)
)
##3.伪造记录
INSERT INTO student(stuName,stuGradeid,stuGender,stuBirthday,stuPhone,stuEmail,stuAddress)
VALUES('微冷的雨',1,1,'2018-6-3 16:07:36','15201069845','yymqqc@126.com','北京某地')

INSERT INTO student(stuName,stuGradeid,stuGender,stuBirthday,stuPhone,stuEmail,stuAddress)
VALUES('微热的翔',1,1,'2018-6-3 16:07:36','13403713096','yymqqc@163.com','天津某地')

INSERT INTO student(stuName,stuGradeid,stuGender,stuBirthday,stuPhone,stuEmail,stuAddress)
VALUES('张宇',1,1,'2018-6-3 16:07:36','110','zy_vip@163.com','日本某地'),
('张宇女票',1,1,'2018-6-3 16:07:36','110','zy_girlfriendvip@126.com','东京某地')

SELECT * FROM Student

##Grade 年级表
CREATE TABLE grade
(
gid INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
gname VARCHAR(32)
)

##Subject 科目表 反引号 '' "" ``
CREATE TABLE SUBJECT
(
subjectid INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
subjectname VARCHAR(32),
gradeId INT,
classHour INT
)
##Result 成绩表
CREATE TABLE result
(
id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Studentno INT,
subjectid INT,
studentresult INT,
examdate DATETIME
)
##我在学生表中创建一个外键
ALTER TABLE student
ADD CONSTRAINT FK_Grade_Student_GradeId FOREIGN KEY (stuGradeid) REFERENCES grade(gid)

SELECT * FROM grade
ALTER TABLE grade
DROP INDEX gname;

##默认值约束
##添加

ALTER TABLE student2
ALTER COLUMN stuAddress SET DEFAULT '北京'

SELECT *FROM student2

##删除
ALTER TABLE student2
ALTER COLUMN stuAddress DROP DEFAULT

##1.现在student2表中加一个stuage的字段
ALTER TABLE student2 ADD stuage INT
##2.给stuage添加一个检查约束，验证是否可以成功
ALTER TABLE student2 ADD CONSTRAINT ck_stuage CHECK(stuage>=18)
##3.验证stuage字段的检查约束是否成功
INSERT INTO student2(stuName,stuGradeid,stuage)
VALUES('张三', 1,17)
##4.删除检查约束是不成功的
ALTER TABLE student2
DROP CONSTRAINT ck_stuage

##删除外键约束

ALTER TABLE student2
DROP FOREIGN KEY FK_Grade_Student_GradeId

##
ALTER TABLE student2 ADD CONSTRAINT FK_Grade_Student_GradeId FOREIGN KEY (stuGradeid) REFERENCES `grade`(gid)

##添加字段
ALTER TABLE student ADD stuAge INT NOT NULL;

##修改字段(变化 修改)
ALTER TABLE student CHANGE stuAge sAge VARCHAR(100) NOT NULL;

##修改表名 重命名（修改）
ALTER TABLE student RENAME student2;

##删除字段 删除
ALTER TABLE student2 DROP sAge;

##mysql中的方案
CREATE TABLE gradebak
(SELECT *FROM grade)

 

SELECT *FROM gradebak

##在已有的备份表中放入N条记录
INSERT INTO gradebak
SELECT *FROM grade

SELECT * FROM tabale_studentName 2;

##分页操作
SELECT * FROM
(SELECT *,ROE_NUMBER()over(ORDER BY studentno ASC)AS muid FROM student)AS temp
WHERE muid BETWEEN 7 ang 9;

SELECT studentname AS 姓名 FROM student ORDER BY 姓名