mysql数据库的增删改查

• 一对多关系表
• 多对多关系

一对多关系表

1

SHOW CREATE DATABASE test;
SHOW databases;
CREATE DATABASE test DEFAULT CHARSET utf8;
#drop database test;#删除表text

2选择数据库test

use test

2建表

CREATE TABLE book (
  book_id int PRIMARY KEY  AUTO_INCREMENT,
  book_name varchar(60) NOT NULL,
  book_author varchar(60) NOT NULL,
  book_shelf int DEFAULT 0,
  book_addtime datetime NOT NULL
);

CREATE TABLE book_shelf
(
 shelf_id int PRIMARY KEY AUTO_INCREMENT , 
 shelf_manager varchar(60) NOT NULL, 
 shelf_position varchar(60) NOT NULL, 
 record_time datetime NOT NULL
);

3插入

INSERT INTO book_shelf (shelf_manager, shelf_position, record_time)
 VALUES ('manager1','A123', NOW()), ('manager1','A124', NOW()),('manager1','A125', NOW()) ;

2.

SELECT * FROM book_shelf;

3.

INSERT INTO book (book_name, book_author, book_shelf, book_addtime)
VALUES ('life', 'yu', 1, NOW()), ('倾城之恋','张爱玲', 2, NOW());

4.

SELECT book_name, book_author, shelf_manager, shelf_position FROM book, book_shelf
 WHERE shelf_id = book.book_shelf AND book_id = 1;

多对多关系

1建立数据库map

1. 建立

SHOW CREATE DATABASE map;
SHOW databases;
CREATE DATABASE map DEFAULT CHARSET utf8;

2. 选择数据库

use map

2建表

1. 学生表

CREATE TABLE student
(
 stu_id int PRIMARY KEY AUTO_INCREMENT, 
 stu_name varchar(60) NOT NULL, 
 stu_class varchar(60) NOT NULL, 
 stu_phone varchar(60) NOT NULL, 
 record_time datetime NOT NULL
);

2. 课程表

CREATE TABLE course
(
 course_id int PRIMARY KEY AUTO_INCREMENT, 
 course_name varchar(60) NOT NULL, 
 teacher_id int DEFAULT 0, 
 record_time datetime NOT NULL
);

3. 教师

CREATE TABLE teacher
(
 teacher_id int PRIMARY KEY AUTO_INCREMENT, 
 teacher_name varchar(60),
 record_time datetime NOT NULL
);

3学生和课程多对多

1. 建立学生和课程多对多的表

CREATE TABLE course_student
(
 course_id int, 
 stu_id int, 
 PRIMARY KEY(course_id, stu_id)
);

2. 插入

INSERT INTO student (stu_name, stu_class, stu_phone, record_time)
 VALUES ('aaa', '001', '12345', NOW()),('bbb', '001', '12346', NOW());

3. 查询学生表

SELECT * FROM student;

4. 插入课程

INSERT INTO course (course_name, teacher_id, record_time)
 VALUES ('c', 1, NOW()), ('android',2, NOW()),('computer', 3, NOW());

5. 查询学生和老师

6. 插入

INSERT INTO course_student (course_id, stu_id) VALUES (1,1),(1, 2),(2,1), (3, 1);

7. 选择课程名和学生名

SELECT course_name,stu_name FROM course, student, course_student
WHERE student.stu_id = 1 AND course_student.stu_id = student.stu_id 
AND course_student.course_id = course.course_id;