mysql语句学习
1. 建表语句
#创建数据库
DATABASE testSql;
#使用数据库
USE testSql;
#创建学生表
CREATE TABLE Student (
sid INT (25) NOT NULL PRIMARY KEY auto_increment COMMENT ‘编号’,
sname VARCHAR (25) COMMENT ‘学生名称’,
sage INT (4) COMMENT ‘学生年龄’
) #插入数据
INSERT INTO Student (sname, sage)
VALUES
(“小明”, 25);
INSERT INTO Student (sname, sage)
VALUES
(“小红”, 24);
INSERT INTO Student (sname, sage)
VALUES
(“小文”, 23);
#创建老师表
CREATE TABLE Teacher (
tid INT (25) NOT NULL PRIMARY KEY auto_increment COMMENT ‘教师表的编号’,
tname VARCHAR (25) COMMENT ‘教师名称’
) COMMENT ‘教师表’ #教师表插入数据
INSERT INTO Teacher (tname)
VALUES
(“郝老师”);
INSERT INTO Teacher (tname)
VALUES
(“李老师”);
INSERT INTO Teacher (tname)
VALUES
(“黄老师”);
#创建课程表
CREATE TABLE Course (
cid INT (25) NOT NULL PRIMARY KEY auto_increment COMMENT ‘课程编号’,
cname VARCHAR (25) COMMENT ‘课程名称’,
tid INT (25) COMMENT ‘外键’,
CONSTRAINT fk_teacherId FOREIGN KEY (tid) REFERENCES Teacher (tid)
) COMMENT ‘课程表’ #课程表插入数据
INSERT INTO Course (cname, tid)
VALUES
(‘语文’, 1);
INSERT INTO Course (cname, tid)
VALUES
(‘数学’, 2);
INSERT INTO Course (cname, tid)
VALUES
(‘java’, 3);
INSERT INTO Course (cname, tid)
VALUES
(‘java’, 1);
#创建成绩表 double类型的不需要指定大小
CREATE TABLE SC (
sid INT (25) NOT NULL PRIMARY KEY auto_increment COMMENT ‘成绩表编号’,
cid INT (25) COMMENT ‘课程表编号’,
score DOUBLE COMMENT ‘分数’,
CONSTRAINT fk_cid FOREIGN KEY (cid) REFERENCES Course (cid)
) COMMENT ‘成绩表’ #成绩表添加数据
INSERT INTO SC (cid, score)
VALUES
(1, 98);
INSERT INTO SC (cid, score)
VALUES
(2, 95);
INSERT INTO SC (cid, score)
VALUES
(3, 100);
2.查询语句
#选择数据
USE mysqlstudy;
#查询student表中学号为081101的学生的情况
SELECT
*
FROM
s