mysql学习笔记(一)：数据库及表的创建和数据插入

数据库的基本操作

本文主要是记录数据库MySQL的学习，希望对大家有所帮助。sql server 大部分操作同样试用。

创建数据库及相关表

CREATE DATABASE selecttest

-- 使用该数据库

USE selecttest;

-- 创建一个学生表

CREATE TABLE student
(
	s_no VARCHAR(20) PRIMARY KEY COMMENT '学号',
	s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
	s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
	s_birthday DATETIME COMMENT'学生生日',
	s_class VARCHAR(20) COMMENT'学生所在的班级'
)

-- 创建教师表
CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);

-- 创建课程表
-- 级联

CREATE TABLE course
(
	c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
	tno VARCHAR(20) NOT NULL COMMENT '外键关联教师表',
	FOREIGN KEY (tno) REFERENCES teacher(t_no)
);


-- 成绩表
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
    c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
    sc_degree decimal,
    foreign key(s_no) references student(s_no),
    foreign key(c_no) references course(c_no),
    PRIMARY KEY(s_no,c_no)
);

查看表结构

-- 查看表结构
SHOW TABLES;
+----------------------+
| Tables_in_selecttest |
+----------------------+
| course               |
| score                |
| student              |
| teacher              |
+----------------------+
-- 查看学生表结构
DESCRIBE student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_no       | varchar(20) | NO   | PRI | NULL    |       |
| s_name     | varchar(20) | NO   |     | NULL    |       |
| s_sex      | varchar(10) | NO   |     | NULL    |       |
| s_birthday | datetime    | YES  |     | NULL    |       |
| s_class    | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

--查看teacher表结构    DESCRIBE teacher;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_no       | varchar(20) | NO   | PRI | NULL    |       |
| t_name     | varchar(20) | NO   |     | NULL    |       |
| t_sex      | varchar(20) | NO   |     | NULL    |       |
| t_birthday | datetime    | YES  |     | NULL    |       |
| t_rof      | varchar(20) | NO   |     | NULL    |       |
| t_depart   | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

--查看course表结构    DESCRIBE course;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_no   | varchar(20) | NO   | PRI | NULL    |       |
| c_name | varchar(20) | NO   |     | NULL    |       |
| t_no   | varchar(20) | NO   | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

--查看score表结构    DESCRIBE score;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no      | varchar(20)   | NO   | PRI | NULL    |       |
| c_no      | varchar(20)   | NO   | MUL | NULL    |       |
| sc_degree | decimal(10,0) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

插入数据

-- 学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

-- 教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

-- 添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

-- 添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

基本数据展示

student  SELECT * FROM student;
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+
teacher  SELECT * FROM teacher;
+------+--------+-------+---------------------+--------+------------+
| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart   |
+------+--------+-------+---------------------+--------+------------+
| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 825  | 王萍   | 女    | 1972-05-05 00:00:00 | 助教   | 计算机机系 |
| 831  | 刘冰   | 女    | 1977-08-14 00:00:00 | 助教   | 电子工程系 |
| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+------+--------+-------+---------------------+--------+------------+

score  SELECT * FROM score;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-105 |        92 |
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
| 109  | 6-166 |        81 |
+------+-------+-----------+

course    SELECT * FROM course;
+-------+------------+------+
| c_no  | c_name     | t_no |
+-------+------------+------+
| 3-105 | 计算机导论 | 825  |
| 3-245 | 操作系统   | 804  |
| 6-166 | 数字电路   | 856  |
| 9-888 | 高等数学   | 831  |
+-------+------------+------+