MySQL数据库应用

---

前言

数据库介绍:
数据作为软件正常运行的支撑，数据库即为存储数据的仓库
作用: 学习如何对数据进行增删改查

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、DBMS

DataBaseManagementSystem数据库管理系统(俗称数据库软件)
常见的DBMS包括:
MySQL/MariaDB: 开源数据库, 是Oracle公司的产品, 08年被Sun公司收购,09年Sun公司被Oracle收购, 市占率排名第一
Oracle:是Oracle公司的产品, 闭源数据库, 性能最高价格最贵, 市占率排名第二
SQLServer: 微软公司产品, 闭源数据库
DB2: IBM公司产品, 闭源数据库
SQLite: 轻量级数据库,安装包几十k
SQL（Structured Query Language） 结构化查询语言, 此语言是用于程序员和数据库软件进行交流的语言

二、使用步骤

1.如何连接数据库软件

连接数据库软件的几种方式:
1.通过命令行连接本机上的数据库软件
2.通过三方的可视化软件连接数据库软件
3.通过Java代码连接数据库软件

2.通过命令行连接数据库

从开始菜单中找到MariaDB或MySQL文件夹然后找到里面的MySQL Client 打开后输入密码 然后回车 显示 Welcome…说明连接成功
如果是Linux或mac OS 打开终端输入: mysql -uroot -p 回车后再输入密码 回车
退出连接指令: exit 回车。

3.数据库中如何保存数据

要想在数据库软件中保存数据需要先建库再建表

三、数据库相关的SQL语句

• 查询所有数据库
  格式: show databases;
• 创建数据库
  默认字符集格式: create database 数据库名;
  指定字符集格式: create database 数据库名 charset=utf8/gbk;
  举例:
  create database db1;
  create database db2 charset=utf8;
  create database db3 charset=gbk;
• 查询数据库信息
  格式: show create database 数据库名;
  举例:
  show create database db1;
• 删除数据库
  格式: drop database 数据库名;
  举例:
  drop database db3;
• 使用数据库，对表和数据操作之前必须使用了某一个数据库 否则会报错
  格式: use 数据库名;
  举例:
  use db1;

四、表相关的SQL语句

执行表相关的SQL语句之前必须使用了某个数据库 否则会报以下错误

use db1;

• 创建表
  格式: create table 表名(字段1名 类型, 字段2名 类型,…) charset=utf8/gbk;
  举例:
  create table student(name varchar(30),chineses int,english int )charset=utf8;
• 查询所有表
  格式: show tables;
• 查看表信息
  格式: show create table 表名;
  举例:
  show create table student;
• 查看表字段
  格式:desc 表名;
  举例:
  desc student;
• 删除表
  格式: drop table 表名;
  举例:
  drop table student;
• 修改表名
  格式: rename table 原名 to 新名;
  举例:
  rename table person to p;
• 添加表字段
  最后面添加，格式: alter table 表名 add 字段名 类型;
  最前面添加格式: alter table 表名 add 字段名 类型 first;
  在xxx字段后面添加: alter table 表名 add 字段名 类型 after xxx;
  举例:
  create table hero(name varchar(20));
  alter table hero add age int;
  alter table hero add id int first;
  alter table hero add gender varchar(2) after name;
• 删除表字段
  格式: alter table 表名 drop 字段名;
  举例:
  alter table hero drop age;
• 修改表字段
  格式: alter table 表名 change 原名 新名 新类型;
  举例:
  alter table hero change gender age int;

五、数据相关SQL语句

执行数据相关的SQL必须使用了某个数据库并且在这个数据库里面创建了某张表,才能往这个表里面添加数据。例：
create database newdb1 charset=utf8;
use newdb1;
create table person(name varchar(10),age int)charset=utf8;

• 插入数据（增）
  全表插入格式: insert into 表名 values(值1,值2,…);
  指定字段插入格式: insert into 表名(字段1名,字段2名) values(值1,值2);
  批量插入:在values后面写多组值通过逗号分隔
  举例：
  insert into person values(“刘备”,50),(“关羽”,40),(“张飞”,30);
• 查询数据（查）
  格式: select 字段信息 from 表名 where 条件;
  举例:
  select name from person where age>30;
  select * from person where name=“刘备”;
• 修改数据（改）
  格式: update 表名 set 字段名=值,字段名=值 where 条件;
  举例:
  update person set name=“关二爷”,age=60 where name=“关羽”;
• 删除数据（删）
  格式: delete from 表名 where 条件;
  举例:
  delete from person where age>50;

数据类型

• 整数: int(m)和bigint(m) , m代表显示长度 m=5 存18 查询到的是00018 , 是用来补零的，不是代表存储数据的长度, 而且需要和zerofill关键字结合使用
  例：create table t1(age int(5) zerofill);
• 浮点数:double(m,d) m代表总长度,d代表小数长度 58.123 m=5 d=3
  例：create table t2(price double(5,3));
• 字符串
  char(m): 固定长度 m=5 存"abc" 占5, 在长度固定时执行效率略高, 最大值255
  varchar(m): 可变长度, m=5 存"abc" 占3, 更节省空间, 最大值65535 ,建议保存255以内的数据
  text(m): 可变长度, 最大值65535, 建议保存长度较大的数据时使用
• 时间和日期相关
  date: 只能保存年月日
  time: 只能保存时分秒
  datetime: 保存年月日时分秒, 默认值为null, 最大值9999-12-31
  例：create table t3(t1 date,t2 time,t3 datetime);

主键约束primary key

约束: 创建表时给表字段添加的限制条件
主键: 表示数据唯一性的字段称为主键
主键约束: 限制主键字段的值唯一且非空
举例:
create table t4(id int primary key,name varchar(20));
insert into t4 values(1,‘aaa’);
insert into t4 values(2,‘bbb’);
insert into t4 values(1,‘ccc’); //报错 提示以下信息表示数据重复

自增 auto_increment

自增规则: 从历史最大值+1
create table t5(id int primary key auto_increment,name varchar(20));

值判断

比较运算符 > < >= <= = !=和<>

逻辑判断

and,or,not

区间判断

between x and y 两者之间 包含x和y

枚举查询

in(x,y,z)

去重distinct

查询员工表中出现了哪几种不同的工作
select distinct job from emp;

模糊查询like

• %: 代表0或多个未知字符
• _: 代表1个未知字符
• 举例:
  x开头 x%
  x结尾 %x
  包含x %x%
  第二个字符是x _x%
  x开头y结尾 x%y

排序 order by

格式: order by 排序的字段名 asc升序(默认)/desc降序;
查询每个员工的姓名,工资和部门id 按照部门id升序排序,如果部门id一致则按照工资降序排序
select name,sal,dept_id from emp order by dept_id,sal desc;

分页查询limit

格式: limit 跳过的条数,请求的条数(每页的条数)
跳过的条数=(请求的页数-1)*每页的条数
举例:
第1页的5条数据 limit 0,5
第2页的5条数据 limit 5,5

聚合函数

将查询到的多条数据进行统计
查询统计方式包括:

• 求平均值
• 最大值
• 最小值
• 求和
• 计数
  平均值:avg(字段名)
  查询1号部门的平均工资
  select avg(sal) from emp where dept_id=1;
  最大值:max(字段名)
  查询1号部门的最高工资
  select max(sal) from emp where dept_id=1;
  最小值:min(字段名)
  查询1号部门的最低工资
  select min(sal) from emp where dept_id=1;
  求和: sum(字段名)
  查询1号部门的工资总和
  select sum(sal) from emp where dept_id=1;
  计数: count()
  查询程序员的数量
  select count() from emp where job=‘程序员’;

分组查询group by

分组查询可以将某个字段相同值的数据划分为一组,然后以组为单位进行统计查询
查询每个部门的平均工资
select dept_id,avg(sal) from emp group by dept_id;
查询平均工资最高的部门id和平均工资
select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;

having

• where后面只能写普通字段条件,不能写聚合函数条件
• having后面是专门用来写聚合函数条件, having要和分组查询结合使用不要单独使用,having写在group by的后面
  查询每个部门的平均工资,只查询出平均工资大于2000
  select dept_id,avg(sal) a from emp group by dept_id having a>2000 ;

数值计算+ - * / %

查询每个员工的姓名,工资和年终奖(5个月的工资)
select name,sal,5*sal 年终奖 from emp;
给每个3号部门的员工涨薪5块钱
update emp set sal=sal+5 where dept_id=3;

关联查询

同时查询存在管理关系的多张表的数据时使用的查询方式
包含三种查询方式:

• 等值连接
  格式: select * from A,B where A.x=B.x
• 内连接
  格式: select * from A join B on A.x=B.x
• 外连接
  外连接查询到的是一张表的全部和另外一张表的交集
  查询所有员工姓名和对应的部门信息
  select e.name,d.* from emp e left join dept d on e.dept_id=d.id;

---