MySQL的基本增删改查

1.基本语法

• 连接数据库：mysql -u root -p
• 查看数据库：show databases;
• 创建数据库：create database 库名 default charset=utf8mb4;
• 删除数据库：drop database 库名;
• 打开数据库：use 库名;
• 查看表：show tables;
• 创建表：create table 表名(字段名1类型)engine=innodb default charset=utf8mb4;
• 查看表结构：desc 表名;
• 查看建表语句：show create table 表名；
• 插入：insert into 表名(字段1,字段2) values(值1,值2,值3);
• 查询：select * from 表名;
• 修改：update 表名 set 字段=某个值 where 条件;
• 删除：delete from 表名 where 字段=某个值;
• 退出：exit;

2.使用案例

-创建数据表
--年级表
create table grade(
grade_id int unsigned not null primary key auto_increment,
grade_name varchar(255)
)engine=innodb default charset=utf8mb4;
--成绩表
create table score(
score_id int unsigned not null primary key auto_increment,
student_id int unsigned,
subject_id varchar(255) unsigned,
score_score int unsigned
)engine=innodb default charset=utf8mb4;
--学生表
create table student(
student_id int unsigned primary key not null auto_increment,
studeng_name varchar(255),
password char(11),
gender varchar(11),
grade_id int unsigned not null,
phone char(11),
address varchar(255),
birthday date,
email varchar(255) 
)engine=innodb default charset=utf8mb4;
--科目表
create table subject(
subject_id varchar(255) unsigned,
subject_name varchar(255),
study_time int unsigned,
grade_id int unsigned
) engine=innodb default charset=utf8mb4;
--创建年级表
insert into grade values(null,'初期'),(null,'中期'),(null,'后期');
--创建成绩表
insert into score values
(null,'S1101001',1,100),(null,'S1101002',2,59),(null,'S1101003',3,69),
(null,'S1101004',3,79),(null,'S1101005',8,88),(null,'S1101006',8,95);
--创建学生表
insert into student values
('S1101001','金蝶','123456','女',1,'13805853696','山东','1989-01-01','123@qq.com'),
('S1101002','凌一','123456','男',1,'13805863696','北京','1989-03-01','124@qq.com'),
('S1101003','凌二','123456','女',2,'13805873696','上海','1990-01-01','125@qq.com'),
('S1101004','金猪','123456','男',2,'13805883696','山东','1991-01-01','126@qq.com'),
('S1101005','凌三','123456','女',3,'13805893696','北京','1992-01-01','127@qq.com'),
('S1101006','金狗','123456','男',3,'13805803696','浙江','1995-01-01','128@qq.com');
--创建科目表
insert into subject values
(1,'基于.NET平台的软件系统分层开发',55,1),(2,'python',48,1),
(3,'设计MySchool数据库',57,2),(8,'面向对象程序设计',52,3);
--四张表
+----------+------------+
| grade_id | grade_name |
+----------+------------+
|        1 | 初期       |
|        2 | 中期       |
|        3 | 后期       |
+----------+------------+
+----------+------------+------------+-------------+
| score_id | student_id | subject_id | score_score |
+----------+------------+------------+-------------+
|        1 | S1101001   |          1 |         100 |
|        2 | S1101002   |          2 |          59 |
|        3 | S1101003   |          3 |          69 |
|        4 | S1101004   |          3 |          79 |
|        5 | S1101005   |          8 |          88 |
|        6 | S1101006   |          8 |          95 |
+----------+------------+------------+-------------+
+------------+--------------+----------+--------+----------+-------------+---------+------------+------------+
| student_id | student_name | password | gender | grade_id | phone       | address | birthday   | email      |
+------------+--------------+----------+--------+----------+-------------+---------+------------+------------+
| S1101001   | 金蝶         | 123456   | 女     |        1 | 13805853696 | 山东    | 1989-01-01 | 123@qq.com |
| S1101002   | 凌一         | 123456   | 男     |        1 | 13805863696 | 北京    | 1989-03-01 | 124@qq.com |
| S1101003   | 凌二         | 123456   | 女     |        2 | 13805873696 | 上海    | 1990-01-01 | 125@qq.com |
| S1101004   | 金猪         | 123456   | 男     |        2 | 13805883696 | 山东    | 1991-01-01 | 126@qq.com |
| S1101005   | 凌三         | 123456   | 女     |        3 | 13805893696 | 北京    | 1992-01-01 | 127@qq.com |
| S1101006   | 金狗         | 123456   | 男     |        3 | 13805803696 | 浙江    | 1995-01-01 | 128@qq.com |
+------------+--------------+----------+--------+----------+-------------+---------+------------+------------+
+------------+---------------------------------------------+------------+----------+
| subject_id | subject_name                                | study_time | grade_id |
+------------+---------------------------------------------+------------+----------+
|          1 | 基于.NET平台的软件系统分层开发                |         55 |        1 |
|          2 | python                                      |         48 |        1 |
|          3 | 设计MySchool数据库                           |         57 |        2 |
|          8 | 面向对象程序设计                              |         52 |        3 |
+------------+---------------------------------------------+------------+----------+
1. grade 表增加一个阶段，“就业期”
insert into grade values(null,'就业期');
2.将第三阶段的学生的 gradeid 改为就业期的 id
update grade,(select grade.grade_id from grade where grade.grade_name='就业期') as s set grade.grade_id=s.grade_id where grade.grade_id=3;
3.查询所有得了 100 分的学号
select student_id from score where score_score=100;
4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1）
select student.* from student where datediff(student.birthday,'1989-01-01')<=365;
5.查询学生姓名为“金蝶”的全部信息
select student.* from student where student_name='金蝶';
6.查询 subjectid 为 8 的科目考试未及格（60 分）的学号和成绩
select student_id,score_score from score where subject_id=8 and score_score<60;
7.查询第 3 阶段课时大于 50 的课程全部信息
select subject.* from subject where grade_id=3 and study_time>50;
8.查询 S1101001 学生的考试信息
select score.* from score where student_id='S1101001';
9.查询所有第二阶段的女生信息
select student.* from student where grade_id=2 and gender='女';
10.“基于.NET 平台的软件系统分层开发”需要多少课时
select study_time from subject where subject_name='基于.NET平台的软件系统分层开发';
11.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用
in)
select study_time from subject where subject_name in('设计MySchool数据库','面向对象程序设计');
12 查询所有地址在山东的学生信息
select student.* from student where address='山东';
13 查询所有姓凌的单名同学
select student.* from student where student_name like '凌_';
14.查询 gradeid 为 1 的学生信息，按出生日期升序排序
select student.* from student where grade_id=1 order by birthday;
15.查询 subjectid 为 3 的考试的成绩信息，用降序排序
select score.* from score where subject_id=3 order by score_score desc;
16.查询 gradeid 为 2 的课程中课时最多的课程信息
select subject.* from subject where grade_id=2 and study_time=(select max(study_time) from subject);
17.查询北京的学生有多少个
select count(address) from student where address='北京';
18.查询有多少个科目学时小于 50
select count(subject_id) from subject where study_time<50;
19.查询 gradeid 为 2 的阶段总课时是多少
select sum(study_time) from subject where grade_id=2;
20.查询 subjectid 为 8 的课程学生平均分
select avg(score_score) from score where subject_id=8;
21.查询 gradeid 为 3 的课程中最多的学时和最少的学时
select max(study_time) as '最多学时',min(study_time) as '最少学时' from subject where grade_id=3;
22.查询每个科目有多少人次考试
select subject_id,count(student_id) as "考试人数" from score group by subject_id;
23.每个阶段课程的平均课时
select grade_id,avg(study_time) as "平均课时" from subject group by grade_id;
24.查询每个阶段的男生和女生个数（group by 两列）
select distinct grade_id,count(*) as '男生个数',count(*) as '女生个数' from student group by grade_id,gender;