mysql创建表跟数据_MySQL：创建数据库和数据表操作

1．使用SQL创建数据库，数据库名：studentDB,如果数据库studentDB已存在，那么，先删除再创建。

DROP DATABASE IF EXISTS StudentDB ;-----如果存在数据库StudentDB则删除数据库StudentDB；

CREATE DATABASE StudentDB ----创建数据库StudentDB

CHARACTER SET utf8 ;---数据库字符集。设置数据库的默认编码为utf8，utf8中间不要"-"；

2．在数据库studentDB中，使用SQL创建student、course和score等3个表，表结构分别如下：

(1)表 1：学生表(t_student)

(2)表2：课程表(t_course)

(3)表 3：成绩表(t_score)

--(1)表1：学生表(t_student)

DROP TABLE t_student IF EXISTS t_student ;

CREATE TABLE t_student(

studentNo NVARCHAR(11) NOT NULL,

studentName NVARCHAR(20) NOT NULL,

loginPwd VARCHAR(20) NOT NULL,

sex CHAR(2) NOT NULL,

birthday DATETIME

);

--(2)课程表(t_course)

CREATE TABLE t_course(

courseID INT AUTO_INCREMENT PRIMARY KEY,---一般会配置自增长属性的字段作为主键

courseName NVARCHAR(20) NOT NULL,

clasHour INT NOT NULL,

credit INT NOT NULL

);

--(3)表3：成绩表(t_score)

CREATE TABLE t_score(

studentNo NVARCHAR(11) NOT NULL,

courseID INT NOT NULL,

score INT NOT NULL

);

3. 数据约束操作

(1)为t_student表(第一个字段)添加主键约束。

ALTER TABLE t_student

ADD CONSTRAINT pk_studentNo PRIMARY KEY(studentNo);

(2)为t_course表(第一个字段)添加主键，其在创建表时已经指定第一个字段为主键约束。

(3)为t_score表(第一和第二个字段)分别添加主键约束。

ALTER TABLE t_score

ADD CONSTRAINT pk_sNO_cID PRIMARY KEY(studentNo,courseID);

(4)为t_score表添加外键约束。

ALTER TABLE t_score

ADD CONSTRAINT fk_stuNo FOREIGN KEY(studentNo) REFERENCES t_student(studentNo);

ALTER TABLE t_score

ADD CONSTRAINT fk_courseID FOREIGN KEY(courseID) REFERENCES t_course(courseID);

(5)为t_student表的sex列添加默认约束，默认值为’男’。

ALTER TABLE t_student

ADD CONSTRAINT df_sex DEFAULT('男') FOR sex ;

(6)为t_course表的courseName添加唯一键约束，要求课程名是唯一的。

ALTER TABLE t_course

ADD CONSTRAINT uq_courseName UNIQUE(courseName) ;

(7)为t_score表的score列添加检查约束，要求成绩在[0，100]之间。

ALTER TABLE t_course

ADD CONSTRAINT ck_score CHECK(score>=0 AND score<=100) ;

4.数据更新操作

(1)插入数据

t_student表数据：

INSERT INTO t_student

VALUES ('13601260101','丁亮','123456','男','1993-2-24'),

('13601260102','李连杰','123456','男','1993-3-25'),

('13601260103','李刚','123456','男','1993-5-25'),

('13601260104','凌洋','123456','女','1993-9-15'),

('13601260105','刘雪琼','123456','女','1992-12-25') ;

t_course表数据：

INSERT INTO t_course(courseName,clasHour,credit)

VALUES('使用SQL Server管理和查询数据',56,3),

('网站前台开发技术',56,2),

('面向对象程序设计(Java)',88,5) ;

t_score表数据：

INSERT INTO t_score

VALUES ('13601260101','1',75),

('13601260102','1',65),

('13601260103','1',85),

('13601260104','1',95),

('13601260105','1',55),

('13601260101','2',77),

('13601260102','2',69),

('13601260103','2',45),

('13601260104','2',95),

('13601260105','2',85),

('13601260101','3',90),

('13601260102','3',60),

('13601260103','3',75),

('13601260104','3',95),

('13601260105','3',55) ;

(2)使用SQL更新学生表，将‘丁亮’的学生名字，修改为“丁文亮”。

UPDATE t_student

SET studentName='丁文亮'----修改结果

WHERE studentName='丁亮' ;----修改条件

(3)使用SQL删除成绩表中的“13601260104”的学生成绩记录。

DELETE FROM t_score

WHERE studentNo='13601260104' ;

5.数据查询操作

(1)找出姓李的学生信息，字段名用别名命名

SELECT studentNo AS 学号, studentName AS 姓名, sex AS 性别, birthday AS 出生日期

FROM t_student

WHERE studentName LIKE '李%' ;----模糊查询：单个用'_'，多个用'%'

(2)求每门课的最高分、最低分、平均分

SELECT t_course.courseID AS 课程编号, MAX(score) AS 最高分, MIN(score) AS 最低分, AVG(score) AS 平均分

FROM t_course, t_score

WHERE t_course.courseID = t_score.courseID

GROUP BY 课程编号 ;-----分组

(3)查询不及格的学生姓名，课程名称，考试成绩。

SELECT studentName AS 姓名, courseName AS 课程名, score AS 分数

FROM t_student AS st,t_course AS c, t_score AS sc

WHERE st.studentNo=sc.studentNo AND c.courseID=sc.courseID AND score<60 ;

(4)查找没有考试成绩的学生的信息

SELECT studentNo AS 学号, studentName AS 姓名, sex AS 性别, birthday AS 出生日期

FROM t_student

WHERE studentNo NOT IN (

SELECT DISTINCT studentNo

FROM t_score

);