前言:

在学习数据库的时候,我们使用的是MySQL8.0.xxx的版本,同时使用的是Navicat Premium Lite170这个软件来帮助我们学习数据库.

MySQL数据库和数据库之间是什么关系呢?

大家可以参考下这个图,总的来说MySQL是一个软件,和office,QQ这些是一个等级的,他们有不同的功能,QQ是用来聊天......而MySQL是用来管理数据库的.

一.库的操作

1.查看数据库

我们可以在Navicat 中使用下面的代码来查看MySQL中当前有哪些数据库.

查看数据库的语法:

show databases;

运行结果如下图:

执行完当前代码可以看到当前有9个数据库.

其中information_schema,mysql,performance_schema,sys这四个数据库是系统库,记录这MySQL自身的一些信息和配置,建议不去动,修改之后可能会导致MySQL启动不了

sakila,world这两个数据库也是MySQL自带的数据库,是测试库

其他的三个都是作者在后面练习时创建的.

2.创建数据库

创建数据库的语法:

在这里提一嘴,MySQL是大小写不敏感的,也就是说代码不区分大小写,就像上面的查看数据库,用SHOW DATABASES;也是一样可以生效.

接下来先带大家了解最基础的创建数据库的语法.

1.只创建数据库,不添加其他的任何额外限制:

creata database test1;

效果:

语法:create database + 想要创建的数据库名字;

来查看下创建后的数据库

就已经包含了我们创建的test1数据库了.

2.添加if not exists,代表如果不存在才创建

语法: create database if not exists + 想要创建的数据库名字;

代码:

create database if not exists test1;

在外面没有添加if not exists之前,我们如果创建一个名字相同的数据库,MySQL就会报错

在添加之后就不会报错,但是因为这个数据库已经存在,也不会重复创建.

3.定义数据库采用的字符集编码和数据库字符集的校验规则

语法:create database if not exists + 数据库名字 + character set + 字符集编码 + collate + 校验规则

创建一个名为test1的数据库,字符集编码是utf8mb4,校验规则是utf8mb4_0900_ai_ci,如果存在则不创建.

代码:

create database if not exists test1 character set utf8mb4 collate utf8mb4_0900_ai_ci;

在这里,我们可以通过show charset;show collation;这两个代码来看数据库支持的字符集编码和排序规则.

下面是部分支持的字符集编码.

下面是部分排序规则.

MySQL8.0之后默认的排序规则就是utf8mb4_0900_ai_ci.

还剩下一个数据库是否加密,留着以后学习到更多知识再来了解.

3.查看数据库创建时候的语法

当我们忘记了在创建数据库的时候设置了哪些参数的时候,我们就可以查询当时创建数据库写的代码.

语法:show create database + 数据库名称;

show create database test1;

效果如下:

3.修改数据库

在对数据库进行修改操作前,我们要确定修改的是哪一个数据库.

我们可以用 use + 数据库名字来选择使用的数据库.

语法: 

use test1;

修改数据库语法规则:

修改数据库主要是修改校验规则,字符集编码.

语法:alter database 数据库名字 character set 修改后的字符集编码;

例如,将刚刚创建的test1的数据库字符集改成gbk.

alter database test1 character set gbk;

查看改后的字符集编码

可以看到修改后的test1已经变成gbk字符集编码了

4.删除数据库

语法:alter database 数据库名字;

删除后我们再查询下有哪些数据库

查询中已经没有test1了,说明删除成功.

在这里说明:删除数据库是一个危险操作!!!

二.数据类型

和Java一样,SQL作为一个编程语言也有数据类型.

下面给大家介绍下常用的数据类型.

常用的也就几个,bigint,decimal,varchar,datetime.

这一块很简单,大家了解并会使用就行.

三.表的操作

1.查看所有表

在我们选定使用的数据库之后,想要查看当前使用的数据库有什么.就可以用下面的代码来查看当前数据库中有哪些表.

代码:

show tables;

先创建一个新的数据库,里面没有任何表.

使用新建的数据库,然后查询数据库内的表,显示一个表都没有.

2.创建表

下面是创建表的语法:

我们现在只需要掌握简单的创建表,约束这些会在后面给大家讲解.

语法: create table if not exists + 表名字(

列名字 + 数据类型 comment '注解内容',

列名字 + 数据类型 comment '注解内容',

......

列名字 + 数据类型 comment '注解内容'

);

具体的我会在下面用例子来为大家讲解.

例如现在要创建一个学生表,里面有学生id,学生姓名,学生性别,学生排行这四列.

代码:

create table if not exists student(
id bigint comment '学生id',
`name` varchar(50) comment '学生姓名',
gender bigint comment '学生性别,1代表男,0代表女',
class bigint comment '学生班级'
);

这里可能有人好奇,为什么name要加``这个符号,

这是因为name 在mysql中是关键词,加上``后表示在表中这是创建列的名字

在创建完表之后,我们可以用 show tables 来查看当前有哪些表

现在在数据库中就已经有一个我们刚刚创建的student表了.

在这里我们没有设置这个表的字符集,排序规则和存储引擎

如果我们想要指定的话,可以这么写:

create table if not exists student(
id bigint comment '学生id',
`name` varchar(50) comment '学生姓名',
gender bigint comment '学生性别,1代表男,0代表女',
class bigint comment '学生班级'
)engine = MyISAM character set utf8mb4 collate utf8mb4_0900_ai_ci;

3.查看表结构

当我们需要查看一个表里面有哪些列,这些列都是什么数据类型,有什么限制.

就可以用 desc + 表名字

4.修改表

语法:

接下来我会用例子来帮助大家逐步了解这个语法.

1.向表中添加一列

语法: alter table + 想要修改的表名 + add 列名 + 数据类型 + 位置

例如我想在student 表中添加一个数据类型是date的学生生日列,排在gener列后面.

代码:

alter table student add birthday date after gender;

现在再来查询一下表结构,已经成功添加这一列了.

在添加的时候还可以用comment '学生生日'来添加注释

alter table student add birthday date comment '学生生日' after gender;

2.修改某列长度

语法:alter table 表名 modify 列名 + 数据类型 + 位置.

例如现在想要修改name名字的长度,将varchar(50)改为varchar(100).并放在birthday后面

代码:

alter table student modify name varchar(100) comment '学生姓名' after birthday;

查询表结构:

3.重命名某列

语法: alter table 表名 rename column 旧列名 to 新列名.

例子: 现在想将name 改为 studentname.

代码:

alter table student rename column name to studentname;

执行代码,符合预期.

4.删除某个字段

语法: alter table 表名 drop 列名.

例如删除gender列

代码:

alter table student drop gender;

执行代码效果:

5.修改表名

语法: alter table 表名 rename to 新表名;

例子:将student表名改为newStudent

代码:

alter table student rename to newStudent;

效果:

同时,在这里我们可以看出,即使我将表中S修改为大写,但是在查询表的时候,还是现实小写的s.

说明在Windows系统中mysql是不区分大小写的,也就是说newstudent和newStudent是同一个表.

5.删除表

语法:drop table if exists 表名;

例子: 删除newstudent表

代码:

drop table if exists newstudent;

效果:

执行完成后再查看数据库中表就没有newstudent 表了.

注意:删除表是一个危险操作!!!

四.对于表数据的增删改查操作

1.新增

语法:

先创建一个user表来便于举例子

create table if not exists user(
id bigint comment'编号',
name varchar(32) comment '姓名'
);

1.单行数据全列插入

例子:

插入编号为1,张三,编号为2,李四这两个人

insert into user values (1,'张三');
insert into user values (2,'李四');

通过select * from 表名这个语法我们可以看到表中的数据,这个接下来就要讲.

2.单行数据指定列插入

例子:插入一个编号为3,没有姓名的人,

插入一个姓名为无名氏,没有编号的人

插入一个编号为10,姓名叫猴子的人.

insert into user(id) values(3);
insert into user(name) values('无名氏');
insert into user(id,name) values(10,'猴子');

执行效果:

当我们只指定了一个列的值的时候,其他没有指定的值就为null,我们可以在创建表的时候做一些约束,来防止出现Null,这个问题我们会在后面提到.

3.多行数据指定列插入

例子:

现在想要往user表中一次性插入多个数据,比如要插入(11,'李白')(12,'王维')(13,'杜甫')

代码:

insert into user(id,name) values(11,'李白'),(12,'王维'),(13,'杜甫');

效果:

2.检索

语法:

这里我们先构造一个新的表,来用于我们做各种检索例子

-- 创建表结构
CREATE TABLE exam (
  id BIGINT,
  name VARCHAR(20) COMMENT '同学姓名',
  chinese FLOAT COMMENT '语文成绩',
  math FLOAT COMMENT '数学成绩',
  english FLOAT COMMENT '英语成绩'
);
-- 插入测试数据
INSERT INTO exam (id, name, chinese, math, english) VALUES
(1, '唐三藏', 67, 98, 56),
(2, '孙悟空', 87, 78, 77),
(3, '猪悟能', 88, 98, 90),
(4, '曹孟德', 82, 84, 67),
(5, '刘玄德', 55, 85, 45),
(6, '孙权', 70, 73, 78),
(7, '宋公明', 75, 65, 30);

查看表中数据

一.select查询

1.全列查询

语法: select * from 表名;

例子:查询exam表中的所有数据

代码:

select * from exam;

效果:

2.指定列查询

语法:select 列名,列名,列名...from 表名;

例子: 查询exam表中的所有id ,name,math

代码:

select id,name ,math from exam;

效果:

3.查询字段为表达式

查询的是常量表达式:

查询的是常量的运算

查询的表达式中运算

例子:查询id,name.语文成绩＋10分

代码:

select id,name,chinese + 10 from exam;

效果:

如果要查所有的总分

4.查询结果为指定别名

例子: 将总分这一列命名为total

select id ,name ,chinese + math + english total from exam;

select id ,name ,chinese + math + english as total from exam;

这两种写法都行,直接在表达式后面加空格然后写入别名,或者加as+别名.

5.查询结果去重

查看表,我们可以知道有两个人的数学成绩相同,如果我们只想保留一个,需要加入distinct关键词

同时需要注意的是,只有当所有列的值都相同的时候,我们才能判定重复

举个简单的例子,现在需要将数学重复的去掉

这里显然就一个数学成绩为98的.

如果同时查询id 和姓名的话

这里就不会只显示一个98,因为虽然数学成绩相同,但是id不同,也就不算是重复.

二.where条件查询

语法:

mysql中的比较运算符和逻辑运算符:

1.基本查询

查询英语成绩低于60分的同学

代码;

select id,name ,english from exam where english < 60;

查询语文成绩高于英语成绩的同学

代码:

select id,name,english,chinese from exam where chinese > english;

查询总分在200以下的同学

代码:

select id,name ,chinese + math + english as 总分 from exam where chinese + math + english < 200;

2.and和or查询

查询英语成绩大于80,并且语文成绩大于80的同学

代码:

select id,name,english,chinese from exam where english>80 and chinese > 80;

查询英语成绩大于80,或者语文成绩大于80的同学

代码:

select id,name,english,chinese from exam where english>80 or chinese > 80;

3.范围查询

查询语文成绩在80到90分之间的同学

select id,name ,chinese from exam where chinese between 80 and 90;
select id ,name ,chinese from exam where chinese >=80 and chinese <= 90;

这两个代码都能做到相同的结果

查询数学成绩是78,79,98,99的同学

这两个代码都能做到相同结果

select id,name ,math from exam where math in (78,79,98,99)
select id ,name ,math from exam where math = 78 or math = 79 or math  = 98 or math = 99;

4.模糊查询

tip:这里我们要知道一个符号%

%表示可以占据多个字符,_表示占据一个字符

查询所有姓孙的同学.

代码:

select name from exam where name like '孙%';

如果我们只想查询孙后面只有一个字的同学,可以使用_

想查询有孙后面有两个字的同学,可以用两个_

5.null查询

这里我们先新增一条数据

insert exam(id,name,math,chinese,english) values(8,'张飞',27,0,null);

查询英语成绩为空的同学

select * from exam where english <=> null;
select * from exam where english is null;

注意:

1.比较是否为空不要去使用=,!=

2.null 比任何值都小,并且null与任何值相运算都是null

三.Order by排序

语法:

例子:按照英语成绩升序排序

代码:

select * from exam order by english asc;

同时我们也可以观察到,按照英语排序时候,null是作为最小值在第一个的.

查询所有英语成绩不为null的同学,并按照语文成绩升序排序

select * from exam where english is not null order by chinese asc;

四.分页查询

语法:

查询前5条数据

select * from exam limit 5;

按照语文成绩排名后查询前五条数据

select * from exam order by chinese limit 5;

从第二条数据开始查询3条数据

select * from exam limit 2,3;

3.修改

语法:

语法: update 表名 set 表达式;

可以用上面的各种限制条件.

例子:将总成绩到倒数前三的同学的数学成绩加30分

update exam  set math = math + 30 order by english + math + chinese asc limit 3;

在执行前我们可以先把原来的表内容查看一下

然后运行代码,符合预期

注意:

1.在以原值为基础上操作的时候,不能使用math+=30这样的语法,mysql不支持

2.在不做限制条件的情况下更改数值是对全表进行更改,谨慎操作!!!

4.删除

语法:

例子:

删除孙悟空的考试成绩

delete from exam where name = '孙悟空';

删除表中所有数据:

先准备一个测试表abcd,然后删除表abcd

create table if not exists abcd(
id bigint,
name varchar(32)
);
insert into abcd values(1,'呵呵'),(2,'嘿嘿');
select * from abcd;

进行删除操作:

delete from abcd;

符合预期.

注意:删除表是一个危险操作!!!谨慎使用!!!

五.截断表(将表恢复到刚创建的时候)

语法:

例子:

先准备一个测试表

create table if not exists test_1(
id bigint key AUTO_INCREMENT,
name varchar(32)
);
insert into test_1(name) values('C'),('B'),('A');
select * from test_1;

auto_ increment表示id会自增,也就是说插入一条数据,下一条数据的Id会自动加1

我们查看一下表,可以看到auto_increment =4,这就表明当我们插入下一条数据的时候,Id为4

此时如果我们对这个表进行delete操作,只会清空表内的数据,对这个值不会有影响

只有对表进行truncate,才能把这个数值置零

注意:

1.truncate只能对全表进行操作,不能对部分数据进行操作

2.truncate操作因为不用对数据进行操作,所以比delete更快

3,会重置auto_increment项

六.插入查询结果

创建一个测试表

# 创建测试表，并构造数据
CREATE TABLE t_recored (id int, name varchar(20));

# 插入测试数据
INSERT INTO t_recored VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

# 查看结果
select * from t_recored;

现在有一个要求,删除表中重复数据,只保留一份重复的数据

一般来说,我们不会真正的对这个表进行修改,所以此时我们需要做的就是新建一个一样的表,然后将数据转移过去

create table newtable like t_recored ;
select * from newtable;
insert into newtable select distinct * from t_recored;
select * from newtable;

七.聚合函数

这一块很简单,都是上面例子的汇总和函数的使用,大家看例子基本上能弄懂.

注意: null不计入统计!!!

例子:统计表中有多少条数据

select count(*) from exam;

例子:统计有多少人参加数学考试

select count(math) from exam;

例子:统计多少人参加英语考试

select count(english) from exam;

注意:值为null的话不计入统计里面!!!

原表数值:

例子:统计有多少人语文小于50分

select count(chinese) from exam where chinese < 50;

例子:统计学生总分

select sum(math + english + chinese) from exam;

例子;统计学生平均分

select avg(chinese +math + english) from exam;

例子:查询英语最高分

select max(english) from exam;

例子:查询大于70分以上的数学最低分

select min(math) from exam where math > 70;

八.分组查询

语法:

我们先创建一个测试表来帮助我们更好的举例:

drop table if exists emp;
create table emp (
    id bigint primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary decimal(10, 2) not null
);

insert into emp values (1, '马云', '老板', 1500000.00);
insert into emp values (2, '马化腾', '老板', 1800000.00);
insert into emp values (3, '鑫哥', '讲师', 10000.00);
insert into emp values (4, '博哥', '讲师', 12000.00);
insert into emp values (5, '平姐', '学管', 9000.00);
insert into emp values (6, '莹姐', '学管', 8000.00);
insert into emp values (7, '孙悟空', '游戏角色', 956.8);
insert into emp values (8, '猪悟能', '游戏角色', 700.5);
insert into emp values (9, '沙和尚', '游戏角色', 333.3);

select * from emp;

例1:统计每个角色的人物个数

代码:

select role,count(role) from emp group by role;

效果:

例2:统计每个角色的最高,最低,平均工资

代码:

select role , avg(salary),max(salary),min(salary) from emp group by role;

我们还可以通过round(x,y)来控制保留的小数点个数.

1.having子句

在使用group by 对数据进行分组之后,我们不能使用where来过滤数据,可以使用having来过滤分组后的数据

例子:显示平均工资低于1500的角色和他的平均工资

代码:

select role,avg(salary) from emp group by role having avg(salary) < 1500;

这里我们就可以用having来过滤分组之后的结果.

2.having和where的区别:

where表示对表中真实数据的过滤.

having表示对分组结果的过滤.

九.内置函数

tip;由于这些函数实践中有用的只有部分,所以只举部分例子,剩下的大家可以自己尝试如何使用

1.日期函数

例1:返回当前日期

例2:返回当前日期和时间:

例3:提取指定时间的日期

2.字符串处理函数

这个用的不多,了解即可

3.数学函数

自行了解即可

4.其他常用函数

例1:检查数据库版本

例2:检查当前数据库