MySQL数据库基本语法

数据库

数据库介绍

数据库：用于存储数据的仓库

早起对于数据的存储方式：纸、办公软件(world、excel)

目前常见的关系型数据库：MySQL、Oracle、SQLServer(只能在微软的系统上使用)

目前的存储方式：数据库

MySQL优点：

1、方便存储

2、数据安全

3、可以多人参与员工使用

4、数据备份

MySQL：轻量级、简单易上手、开源免费

MySQL缺点：功能比较少、可操作的数据量在10万条数据内ok，超出十万性能会下降

Oracle数据库：功能强大、可操作的数据量大，缺点是商用收费

数据库引擎

事务：提交、回滚等

常见的数据库引擎有四种：

InnoDB：是事务性数据库的首选引擎，MySQL的默认引擎

MyISAM：不支持事务，拥有较高的插入和查询速度

Memory

Archive

MySQL数据库

安装数据库

1、选择安装服务

2、数据的名称及端口号(不用改动)

3、输入密码后全程下一步安装即可

4、配置mysql环境

5、测试：打开cmd输入mysql -u root -p 后输入密码，出现MySQL即为安装成功

卸载数据库

1、计算机删除应用程序

2、找到c盘删除MySQL相关文件

3、打开注册表

4、删除MySQL相关的文件

5、重启电脑(建议)

数据库操作

show databases 查看数据库

create database 数据库名 创建一个数据库

use 数据库名 选择数据库

select database() 查看当前所在的数据库

drop database 数据库名 删除数据库

ERROR 1007 (HY000): Can’t create database ‘hongzehu’; database exists 该数据库已存在

数据类型

1、数字类型

tinyint<smallint<int<long 上述四种类型都是用于修饰整数的，区别是范围不同，是包含关系

tinyint的范围-128~127 int的总长度为10位数

float<double<decimal修饰小数的，区别是精度不同

2、字符类型

多个字符组成字符串

char：代表字符类型，只能是一个字母或数字或一个中文，长度固定，优点是效率高，缺点是浪费空间资源

varchar：代表字符串类型，长度不固定，优点是节约空间，缺点是效率低

单个字母或数字占一个字符的位置，一个中文占两个字符的位置

3、日期类型

date 日期：年月日

time 时间：时分秒

year 年

datetime 年月日时分秒

timestamp 年月日时分秒(时间戳)

datetime需要用户给定时间，如果不给定时间，则默认值为null

timestamp用户可以不指定时间，如果不给定时间的话，默认是当前的系统时间

表结构操作

语法：

create table 表名(
字段名(自定义) 数据类型 [约束],
字段名(自定义) 数据类型 [约束],
字段名(自定义) 数据类型 [约束]
);

//创建一个员工信息表
create table t_emp(
eno int,
ename varchar(20),
sex varchar(5),
phone varchar(11),
address varchar(50)
);

show tables; 查看当前数据库中的所有表

desc 表名; 查看表结构

drop table 表名; 删除表

语法：alter table 表名 add 新增的列名 数据类型 [约束]; 新增字段(列)

示例：alter table t_emp add idCard varchar(18);

语法：alter table 表名 modify 字段名 数据类型; 修改数据类型

示例：alter table t_emp modify idCard long;

语法：alter table 表名 change 旧列名 新列名 数据类型; 修改列名

示例：alter table t_emp change idCard card long;

语法：alter table 表名 drop [column] 列名; 删除列名

示例：alter table t_emp drop column card;

语法：rename table 表名 to 新表名; 修改表名

示例：rename table t_emp to emp;

错误定位

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘(18)’
at line 1

数据操作

查看数据库编码

show variables like '%char%';

修改数据库编码

set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;

添加数据

//语法1：insert into 表名(字段名,...) values(值,...);值的类型和顺序要和字段的类型及顺序保持一致
insert into t_emp(eno,ename,phone) values(1,'Juni','15737156507');
//语法2：insert into 表名 values(值,...);值的类型和顺序要和创建表时的字段类型和顺序保持一致
insert into t_emp values(3,'李猛哥','女','1676449798','郑州市南三环');
//语法3：insert into 表名 values(),(),(),...;批量添加
insert into t_emp values(4,'董传强','男','1635476489','洼浏村'),(5,'王康','男','179789854','洼浏村'),(6,'郝亚杰','男','132598798','布袋里');

简单的查看数据

select * from t_emp;
select eno,ename,sex,phone,address from t_emp;
//*代表通配符，需要先去匹配表当中都有哪些字段，然后再匹配需要查看的字段
//直接写字段名，直接去表中匹配需要查看的字段
//开发的时候不允许使用*，会影响性能

修改数据

//语法：update 表名 set 字段名=值,... where 条件;
update t_emp set sex='男';
update t_emp set sex='女' where eno=3;
update t_emp set ename='jay',address='美国XXX' where eno=2;

删除数据

//语法：delete from 表名 where 条件;
delete from t_emp;//清空数据
delete from t_emp where eno=2;

排序

//语法：select 语句 order by 字段名,... asc(升序)/desc(降序);
select * from t_emp order by eno desc;

练习

1、修改表-添加一个年龄字段，需求：查询所有的信息，按照年龄从高到低排序，如果年龄相同就按照序号从低到高排序

2、创建人物信息表和职业信息表

数据库设计规范

数据库三范式

第一范式：数据达到原子性、不可再分

第二范式：每行数据要具有唯一性

第三范式：独立性，消除传递依赖

t_emp(员工信息表)

eno(编号)	ename(姓名)	sex(性别)	phone(手机号)	address(家庭住址)	deptno(部门编号)

1、创建表
create table t_person(
eno int,
ename varchar(20),
sex varchar(2),
phone varchar(11),
address varchar(50),
deptno int,
salary double
);
2、添加数据
insert into t_person values(1,'孙悟空','男','110','花果山水帘洞',10,8888.88);
insert into t_person values(2,'猪八戒','男','120','高老庄',10,7777.77);
insert into t_person values(3,'沙悟净','男','130','流沙河',10,6666.66);
insert into t_person values(4,'牛魔王','男','140','火焰山',20,6888.88);
insert into t_person values(5,'罗刹女','女','150','火焰山',20,3000.66);
insert into t_person values(6,'玉面狐','女','160','火焰山',20,3500);
insert into t_person values(7,'白骨精','女','170','白骨洞',30,3535);
insert into t_person values(8,'蜘蛛精','女','190','盘丝洞',30,4200);
insert into t_person values(9,'百花仙子','女','101','天宫',30,7000);

t_dept(部门信息表)

deptno(部门编号)	dname(部门名称)	loc(部门地址)
10	妖怪大道	妖怪路
20	芭蕉洞	火焰山
30	花柳楼	女儿国

create table t_dept(
deptno int,
dname varchar(20),
loc varchar(20)
);

insert into t_dept values(10,'妖怪大道','妖怪路');
insert into t_dept values(20,'芭蕉洞','火焰山');
insert into t_dept values(30,'花柳楼','女儿国');

要求：每张表中至少有10条数据

聚合函数

sum() 求和(列的和)

max() 求列中的最大值

min() 求列中的最小值

avg() 求列的平均值

count() 求总行数

count函数在求总行数时，为null的不计入总数
select count(*) from 表名; 结果是以该表中行数最多的列的行数为主
select count(eno) from t_emp; 以eno的总条数为结果
select count(1) from t_emp; 类似于*的功能

ifnull(参数一,参数二);

判断指定列的值是否为空，如果为空以什么来代替，参数一代表列，参数二代表要替换的值

非空查找

关键词：is null

需求：删除数据为空的行

delete from t_emp where eno is null;

模糊查询

关键字：like

特殊字符：%任意字符 _任意的单个字符，%可以是任意个字符 _ 一个下划线代表一个任意字符

分组查询

概念：指定以某一个特点为依据进行划分

关键字：group by ,having

需求：查询该公司员工中男女的数量分配

select sex,count(*) from t_emp group by sex;

需求：查询各部门薪资的总和

select deptno,sum(salary) from t_emp group by deptno;

需求：查询部门薪资总和中大于12000的

select deptno,sum(salary) from t_emp group by deptno having sum(salary)>12000;

注意：

where和having的区别：where是直接筛选数据的，having是针对group by分组后的数据进行二次筛选的

where是跟在表的后面使用的，having是需要结合group by使用的，having很耗性能

别名

别名只是在显示的时候使用的，是不影响表本身的数据/也可以指代当前的字段名进行使用

关键词：as [可省略]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NhbXCEPw-1618996645477)(./asset/19.png)]

分页查询

概念：将多条数据分开(分成不同的页面)展示，方便用户查看

关键词：limit (方言)只能在mysql中使用

语法：select 语句 limit 参数一,参数二; 参数一代表查询的起始下标，参数二代表要查询的条数

注意：在数据库中数据的起始下标从0开始算

其它关键字

between…and… 代表在…和…之间，表示区间

in/not in 在指定的值当中的符合的数据

约束

概念：定义规则

常见的约束：

非空约束(必填项)：字段值不能为空，not null

删除not null（非空）约束：alter table 表名 modify  列名 int null;

唯一约束：字段值不能重复，unique

删除unique（唯一）约束：alter table 表名 drop index 列名;

主键：建议每张表中都应该有一个主键(非空+唯一)，primary key

删除primary key（主键）约束：alter table 表名 drop primary key;

主键自增长：通常主键的类型为数字，自增长就是没添加一条数据，该主键的值会自动加一，auto_increment

删除auto_increment（自增长）约束：alter table 表名  modify 列名 int;

创建表时添加对应的约束

外键约束：多张表之间的约束关系(子表受父表中的数据所约束)

语法：constraint 外键名 foreign key (当前表要作为外键的字段名) references 主表(关联字段);

删除foreign key(外键)约束：alter table 表名 drop foreign key 外键名;

查看表的源码结构：show create table 表名;

删除外键约束：alter table 表名 drop foreign key 外键名;

存在外键约束时可以设置级联更新：ON UPDATE CASCADE

示例：alter table t_person add constraint fk_deptno foreign key(deptno) references t_dept(deptno) on update cascade;

多表查询

概念：多张表或一张表多次使用成为多表查询

子查询

需求：查询最高薪资对应的员工信息

//1、先查出最高的薪资是多少
select max(salary) from t_emp;
//2、以最高薪资为条件查出对应的员工信息
select * from t_emp where salary=(select max(salary) from t_emp);

如果是使用的= != > < >= <=等等，那么要求是后面只能跟单个结果

如果子查询的结果是多个值，那就使用in/not in

需求：查询薪资在5K~7K之间的员工的信息

//1、先查询到薪资在5K到7K之间的员工的编号
select eno from t_emp where salary between 5000 and 7000;
//2、根据编号查询员工的信息
select * from t_emp where eno in(select eno from t_emp where salary between 5000 and 7000);

连接查询

左连接：以左边表为主，查询出左边表中符合条件的全部数据以及右边表中符合条件的数据，缺少的数据以null代替

语法：select … from 表 left join 表 on 条件;

需求：查询部门对应的员工信息

select d.*,e.* from t_dept d,t_emp e where d.deptno=e.deptno;
select d.*,e.* from t_dept d left join t_emp e on d.deptno=e.deptno;

右连接：以右边表为主，查询出右边表中的全部数据及左边表中符合条件的数据，不足的以null代替

语法：select … from 表 right join 表 on 条件;

需求：查询员工信息及对应的部门信息

select d.*,e.* from t_dept d right join t_emp e on e.deptno=d.deptno;

合并结果集

注意：合并的结果集要求被合并的字段是相同的类型及数据

union：合并多张表的数据，会去重复

union all ：合并多张表的数据，不去重复

数据导入和导出

导出 要求先退出MySQL环境

mysqldump -u root -p 数据库名 [表名] > 导出的地址

注意：后面不要加分号(因为还要输入密码)

导入 要求先进入MySQL环境

注意：到数据库之前，需要先创建并选择一个数据库

视图

概念：就是一张虚拟的表

语法：create view 视图名 as select语句;

需求示例：甲乙双方合作，甲方需要乙方团队的人员部分信息，那么该信息是只有部分且只能查看

需求：将员工1,6,9的基本信息(不包括部门及薪资信息)给到甲方

create view v_emp1 as select ename,sex,phone,address from t_emp where eno in(1,6,9);

视图和原表中的数据如果是一对一可推导的情况下是可以互通的

带有函数的名称是不能作为字段名直接使用的

如果视图中的数据是无法推导原表中数据的变化的，那就不能进行修改(增删改)的动作

问题：到底能否根据视图中的数据修改原表中的数据呢？

回答：视图中修改的数据可以推导出原表中数据的变化那就可以修改，如果推导不出来那就不能改

问题：什么样的情况下回导致通过视图无法推导原表中的数据呢？

回答：当视图中的数据用到了group by 、函数、distinct等等

优点：安全(可以设置权限和字段)、方便

常见函数

字符串函数：concat(arg0,arg1,…) 字符串拼接，可拼接无限个字符

例如：select concat(‘hello’,‘world’); 结果是：helloworld

目的：将不同的字符以指定的形式拼接到一块展示出来

日期函数：

now() 获取当前的系统时间

unix_timestamp() 获取当前的系统时间对应的毫秒值

from_unixtime(arg0,arg1)

arg0：代表要进行转换的毫秒值

arg1：代表要进行转换的格式

示例：

select from_unixtime(unix_timestamp(),'%Y年%m月%d日 %H:%i:%s');

聚合函数和函数的区别

聚合函数代表中对数据的操作，函数代表某一个特定的功能，对已有的数据根据功能进行操作

(聚合函数是直接操作数据的，函数是操作获取的数据的)

通常情况不建议在数据库中使用函数，会影响数据库性能，所以大多数对数据的处理都是由开发语言来处理的

键（key）值（value）对

姓名 张三 年龄 17 性别 男 用户名 admin 密码 root

字段称之为key，具体的数据称之为值

数据库优化

在进行数据库使用时，随着数据库中数据的增大，进行数据查询时会造成查询时间的增多，为了提升用户的体验需要进行数据库相关的优化，使数据库的执行效率与速度得到提升

配置优化

(1)硬件配置

对数据库服务器进行配置更新使其用拥有更好的执行效率（花钱）

(2)软件配置

mysql主要对my.ini中配置进行修改，查询缓存、数据库引擎、连接数。。。。

sql语句优化

在进行sql书写，对可以完成相同查询功能的不同sql进行效率筛选

1、减少计算的使用

mysql数据库主要用于存取数据，虽然提供了计算的功能，但是计算会耗费额外的资源

2、减少连接查询

form进行数据准备时进行连接查询需要多个表中数据进行连接获取之后才能继续进行查询操作

3、减少排序的使用

排序是最耗费资源关键字，因为进行排序时需要将所有数据依次进行排序比较，如果必须使用排序，那么在排序前一定尽量多的将不必要的数据筛选过滤掉

4、减少select * 的使用

在从结果集获取数据列时，尽量只获取需要的列，因为列的获取也是会消耗资源，但是如果查询的数据所有列都需要，那么使用select * 与 select字段效率基本相同

5、减少子查询的使用

子查询可以理解为执行多条sql语句，如果可以使用连接查询替换

6、尽量将筛选条件书写在连接查询中

尽量在数据筛选时就将有效数据获取。where进行条件筛选时会将每行数据依次判断

7、减少不确定条件的使用

or not in in 这些不确定条件会使数据进行条件筛选时逐条过滤，并且mysql中对于这类语法性能较低，这些不确定条件会使索引失效

8、对于字段添加索引

索引会使对指定字段的条件筛选速率得到提升

9、使用union all代替union

union会进行过滤，将所有数据进行对比过滤掉重复数据，会大大消耗资源，所以最好使用union all全部连接，在多条sql语句中通过条件保证没有重复数据

10、尽早过滤

进行条件筛选时优先将添加索引、数字型字段优先进行过滤

11、避免类型转换

mysql中提供了数据类型的转换，但是会耗费数据库额外的资源，而且有时如果传入数据不能转换为指定类型还会造成数据添加或操作的失败，有时也会造成结果的不统一

12、对sql进行总体把握

对于sql优化不要先优化执行效率最低的，先优化使用频率最高的，对于所有sql需要进行掌握

13、表结构的优化

数据库三范式

(1)数据原子性：数据中每行数据中每列不可再分并且存在唯一表示列

(2)主键关联性：除主键外所有字段与主键存在关联

(2)非主键非关联性：除主键外所有字段与其他字段不存在关联

工具的使用

练习题

MySQL经典练习题

/**
Java为什么可以在多个平台运行？
jre:jre中包含了虚拟机，运行java程序
jdk:jdk中包含了jre，同时包含了开发所需要使用的工具包，开发及运行java程序
下载jdk时，会根据所使用的系统不同下载不同版本的jdk
每个平台都对应一个虚拟机(JVM)，jdk中包含了jre
命名规范：不能数字开头，不能以特殊字符开头
*/
public class HelloWorld{
    //主函数，是程序的入口
    public static void main(String[] args){
        //在控制台打印输出 println 换行 print 不换行
        System.out.println("hello world!!!");
        System.out.println("hello world!!!");
    }
}