mysql的入门学习

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