MySQL数据库增删改查基础操作(超长详解)

目录

1库的操作

显示数据库：

创建一个库

使用数据库

删除数据库的名

2表操作：

显示表

创建表

查看表

删除表名

新增

查出表的所有行和列；

实例：

别名：

去重：

排序：

限制查找的条数：（limit）

not null（指定某列不能储存null值）

unique （保证某列的每行必须有唯一的值，不能重复的）

default：默认值约束，指定插入数据，如果为空，指定默认值。

primary key ：主键约束

聚合查询：

联合查询：

还有一种方法是 join  on

外连接分为左外连接和右外连接：

自连接：

子查询：

合并查询：

数据库的基本操作

1库的操作

显示数据库：

show databases;

创建一个库

create database 数据库名 charset 字符集；

使用数据库

use 数据库名；

删除数据库的名

drop database 数据库名；

2表操作：

显示表

show tables;

创建表

create table 表名 （列名 类型，列名 类型）；

查看表

desc 表名；

删除表名

drop table 表名；

新增

insert into 表名 values （值，值）;

insert into 表名(列名，列名) values （值，值）;

查出表的所有行和列；

select *from 表名；

实例：

创建一个学生表：里面有id，姓名，语文，数学，英语，成绩。

create table exam_result

( id int, name varchar(20), chinese decimal(3,1), math decimal(3,1), english decimal(3,1) );

然后插入测试数据：

insert into  exam_result (id,name, chinese, math, english) VALUES (1,'唐三藏', 67, 98, 56), (2,'孙悟空', 87.5, 78, 77), (3,'猪悟能', 88, 98.5, 90), (4,'曹孟德', 82, 84, 67), (5,'刘玄德', 55.5, 85, 45), (6,'孙权', 70, 73, 78.5), (7,'宋公明', 75, 65, 30);

别名：

select id ,name chinese+math +english as total from 表；

去重：

使用distinct 关键字

比如去重数学成绩

select distinct math from 表名；

排序：

order  by（升序）

select id ,math ,chinese from 表名 order by chinese;

升序，如果降序则需在后面加上desc；

基本查询：

比如查询英语不及格的同学：

select id ,english from 表名 where english<60;

如果查完进行排序：

select id ,english from 表名 where english<60 order by english;

查找总成绩小于200的学生：

select name,chinese + math +english from exam_result where chinese +english +math <200;

查找语文大于80 和英语大于80的学生；

select name,chinese,english from exam_result where chinese>80 and english>80;

and的优先级大于or。

查找数学成绩在（58，59，98，99）的学生（in）：

select name,math from exam_result where math in(58,59,98,99);

查找名字中带有孙的name（%）：

select name from exam_result where name like '孙%';（孙在前）

select name from exam_result where name like '%孙%';（含孙的）

显示一个（_）:

select name from exam_result where name like '孙_';

查找名字是否为空的：

 select * from exam_result where name <=> null;

限制查找的条数：（limit）

 select * from exam_result limit 3;

3之后再查找3个：

select * from exam_result limit 3 offset 3;（从最后的3开始查找）；

修改：让孙悟空的成绩数学成绩加10；

update exam_result set math=math +10 where name ='孙悟空';

not null（指定某列不能储存null值）

create table student8(id int not null);

unique （保证某列的每行必须有唯一的值，不能重复的）

create table student9(id int not null,name varchar(20) unique);

default：默认值约束，指定插入数据，如果为空，指定默认值。

create table student10(id int default '1',name varchar(20) unique);

primary key ：主键约束

指定id为主键

 create table student11(id int not null primary key,name varchar(20) unique);

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大 值+1。

foreign key （外键约束）

创建班级表

create table  classes ( id int primary key auto_increment, name varchar(20), `desc` varchar(100) );

创建学生表

create table student (   id INT PRIMARY KEY auto_increment,   sn ); INT UNIQUE,   name VARCHAR(20) DEFAULT 'unkown',   qq_mail VARCHAR(20), classes_id int, FOREIGN KEY (classes_id) REFERENCES classes(id)

插入和查询结合（可以把一个表查询出的结果，插入到另一个表中）：

insert into school1 select *from school;

聚合查询：

 1，select count(*) from fu;（查询行数）

count遇到null会跳过

2，select sum(salary) from fu;（求和）

3，select avg(salary) from fu;（求平均值）

4，select role ,count(id) from fu group by role;（分组进行查查询）

实例（比如分组查平均值）：

select role,avg(salary) from fu group by role;

先插入一段数据：

insert into student(sn, name, qq_mail, classes_id) values
    ->  ('09982','黑旋风李逵','xuanfeng@qq.com',1),
    ->  ('00835','菩提老祖',null,1),
    ->  ('00391','白素贞',null,1),
    ->  ('00031','许仙','xuxian@qq.com',1),
    ->  ('00054','不想毕业',null,1),
    ->  ('51234','好好说话','say@qq.com',2),
    ->  ('83223','tellme',null,2),
    ->  ('09527','老外学中文','foreigner@qq.com',2);

insert into course(name) values
    ->  ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
    -> (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
    -> (60, 2, 1),(59.5, 2, 5),
    -> (33, 3, 1),(68, 3, 3),(99, 3, 5),
    -> (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
    -> (81, 5, 1),(37, 5, 5),
    -> (56, 6, 2),(43, 6, 4),(79, 6, 6),
    ->  (80, 7, 2),(92, 7, 6);

insert into classes(name,doing) values
    -> ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
    ->  ('中文系2019级3班','学习了中国传统文学'),
    ->  ('自动化2019级5班','学习了机械自动化');

例子：查找许仙的成绩

联合查询：

1，先分析用到哪个表

select *from student,score;

2,再寻找对应条件，减少重复数据

select *from student,score where student_id =score.student_id;

3，再减少重复数据，当学生的名字为许仙时

 select *from student,score where student.classes_id =score.student_id and student.name ='许仙';

4，再减少范围，只查找范围内的

select student.name ,score.score from student,score where student.classes_id =score.student_id and student.name='许仙';

还有一种方法是 join  on

（join前后加的是两张表on后面加的是条件）

 select student.name,score.score from student join score on student.classes_id=sc
ore.student_id and student.name='许仙';

外连接分为左外连接和右外连接：

（先创建两个表）

左：

select *from student left join score on student.id =score.id;

右：

select *from student right join score on student.id =score.id;

如果修改一个表的数据，再进行查找会变成null

update score set score.id = 4 where score.id =3;

当进行右外连接的时候，id为3 的一行会变成null

自连接：

一个表的数据自己进行连接

select *from score as s1,score as s2;

select *from score as s1,score as s2 where s1.id=s2.id;

子查询：

指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询：返回一行记录的子查询

select *from score where score.id != (select *from score where score.score);

多行子查询：返回多行记录的子查询

in关键字（查询多行数据用的in）

合并查询：

union（可以多个表进行查询）

 selct *from course where id<3 union select *from course where name='英文';