MySql单表与多表操作基础

                                                           MySql单表

​ 数据库概述:保存数据的仓库 我们可以向数据库里保存、获取、修改、删除数据，在实际的web应用开发过程中，需要通过Java程序来操作数据库。数据库的本质是一个文件系统

​ 常见数据库: Oracle SQLServer DB2 Sybase MySql

(一) SQL语言(面试题)

​ SQL :Structure Query Language 结构化查询语言 由数据库提供,是用来操作数据库的语言

​ DDL :Database Definition Language 数据定义语言,用来定义database,table 常见命令有 :create,alter,drop

​ DML:Data Manupulation Language，数据操作语言，用来插入数据、修改数据、删除数据。常见的命令：insert, update , delete

​ DQL: Data Query Language 数据查询语言 用来查询数据的 常见命令:select

DCL: Data Controll Language 数据控制语言 给数据库管理员(DBA)使用的,用来控制数据库的用户,角色,权限等等

 

(二) MySql服务管理:

启动服务:

​ 以管理员身份运行cmd,然后输入命令: net start mysql

关闭服务:

​ 以管理员身份运行cmd,然后输入命令: net stop mysql

(三) 数据库database的基本操作:

​ mysql -u用户名 -p密码 [-h服务器的IP地址 -p端口号]:登录mysql(登录本机IP可不加)

​ create database 数据库名称;创建数据库

​ show databases;查看所有数据库

​ use 数据库名称:切换数据库

​ select database();查看当前数据库

​ drop database 数据库名称;删除数据库

mysql常见数据类型

​

数据类型	描述
int	整数类型,对应Java的int类型
double(m,d)	m表示该小数的最大位数,d表示小数点后的位数 如:double(5,2)最大可存999.99 对应Java 中的double类型
char(m)	固定长度的字符串。字符串在保存里始终占m个字符。m表示最多可以保存多少个字段。char(5)，保存一个”a“。 最多只能保存255个字符。对应Java的String e
carchar(m)	可变长度的字符串。字符串多长就占多少个字符的位置。m表示最多可以保存多少个字 符。最多可以保存65535个字符。对应Java的String*
data	日期类型。对应Java的类型java.sql.Date
datetime	日期时间类型。日期格式都是 yyyy-MM-dd HH:mm:ss。日期范围1000年到9999年。对应Java的类型是java.sql.Timestamp;
timestamp	时间戳。日期格式都是 yyyy-MM-dd HH:mm:ss。 日期范围1970年1月1日到2038年左右。对应Java的类型是java.sql.Timestamp;

​

(三) MySql的约束

​ 是数据库里某一个字段的值进行限制，如果不符合条件，就不允许插入或者修改

主键约束：primary key

​ 被主键约束的字段，值非空唯一。建表原则：每张表都要有一个主键。一张表有仅仅有一个主键

​ 表里数据的唯一性标识

​ 如果是把一个数字类型的字段，设置成了主键的话，通常可以设置为主键值自增。

​ 自增:primary key auto_increment

唯一性约束：unique

​ 如果字段加上了唯一性约束，这个字段的值不能重复

非空约束：not null

如果字段加上了非空约束，这个字段的值不能为空

默认值约束: default 默认值

​ 如果字段加上了默认值约束，如果字段没有值，就会采用默认值

外键约束

(四) MySql的table的操作:

​ create table 表名称(字段1 字段类型 [约束],字段2 字段类型 [约束],.....字段n 字段类型 [约束]);创建table表格

​ show tables;列出所有table表格

​ desc 表名称;查看表结构

​ rename table 表名称 to 新表名称; 重命名表

​ alter table 表名称 add 字段名称 字段类型 [约束];添加字段

​ alter table 表名称 modify 字段名称 字段类型 [约束];修改字段类型

​ alter table 表名称 change 原字段名 新字段名 字段类型 [约束];修改字段名称

​ alter table 表名称 drop 字段名;删除字段

​ drop table 表名称;删除table

(五) 数据库的操作

一丶插入数据

​ 向表里所有字段插入数据

​ insert into 表名称 values (字段1的值, 字段2的值,… 字段n的值);

注意：表里有几个字段，就必须有几个值；值的顺序必须要和字段的顺序一样；数值要符合字段的精度要求

​ 向表里指定字段插入数据

​ insert into 表名称 (字段1, 字段2,… 字段n) values (字段1的值, 字段2的值,… 字段n的值);

​ 注意：写了几个字段，就必须有几个值；值和顺序要和字段的顺序一样；数值要符合字段的精度要求

​ 在SQL语句里，可以以字符串的形式插入任何类型字段的数据。

二丶修改数据

​ update 表名称 set 字段1=值, 字段2=值,… [where 条件]

三丶删除数据

​ delete from 表名称 [where 条件]

​ 注意!删除数据时必须严格where条件以免误删数据,造成不必要的损失

四丶查询数据

• 基本查询

  ​ select * from 表名称; 查询所有字段

  ​ select 字段1, 字段2, … from  表名称; 查询指定字段

  ​ select 字段1, 字段2+500, 字段3-300,… from 表名称; 查询并运算

  ​ select 字段1,  ifnull(字段2, 为null时的取值), 字段3,… from  表名称; 查询并处理null值

  ​ select 字段1 as 别名, ifnull(字段2, 为null时的取值)+500 as 别名,… from 表名称;   #as可以省略不写

• 去重查询

  ​ select distinct 字段1, 字段2,…  from  表名称;

• 条件查询

基本查询 where 条件

​ 条件的写法：

​ 比较运算符: >, <, >=, <=, =, <>(不等于)

​ 范围运算：字段名 between 最小值 and 最大值

​ 集合查询：字段名 in (值1,值2,值3,…)

​ 模糊查询：字段名 like ‘字段%’ %表示任意个任意字符，_表示一个任意字符

​ not(条件)：舍弃符合这个条件的数据

​ 查询空值：字段名 is null

​ 查询非空值: 字段名 is not null

​ 条件的连接符：

​ and：条件必须都符合

​ or：符合任意一个条件即可

​ 多个条件时，最好使用括号分隔开 如：工资大于8000的女性员工 和 年龄小于20的男性员工

​ 例如:select * from employee where (salary > 8000 and gender = '女') or (age < 20 and gender = '男');

• 排序查询

  ​ 基本查询: [where 条件] order by 排序字段 排序规则, 排序字段 排序规则, …;

  ​ 排序规则 ：升序排列asc， 降序排列desc

  例如:select * from employee order by salary desc, age asc, id desc;

• 聚合查询

  聚合函数：

  ​ count(*)：统计个数 count(字段)

  ​ sum(字段)：计算这个字段所有值的和

  ​ avg(字段)：计算平均值

  ​ max(字段)：求最大值

  ​ min(字段)：求最小值

  ​ 聚合函数会忽略null值

• 分组查询

  ​ select 分组字段, 聚合函数,… from 表 [where 条件] group by 分组字段 [having 分组后的过滤] order by 排序字段 排序规则

   

  例如:select dept, count(), sum(salary), max(age), min(salary), avg(salary) from employee group by dept having count()>5 order by count(*) asc;

   

  分组查询的执行过程：

  ​ 先对基本表进行where过滤，得到过滤后的数据

  ​ 对过滤后的数据按照分组字段进行分组，然后分组统计，得到分组后的结果（虚拟表）

  ​ 对分组后的结果（虚拟表）进行having过滤

  ​ 对having过滤后的结果进行排序显示出来

   

  where和having的区别：

  ​ where是对基本表进行过滤的，having是对分组后的结果（虚拟表）进行过滤的

  ​ where条件先执行，分组后才会having的过滤执行

  ​ where条件里写基本表字段的一些条件，having里写分组后结果的字段进行过滤，或者 是聚合函数过滤

(六) 数据库的备份和恢复

1.使用可视化客户端工具备份和恢复

2.使用cmd命令行备份和恢复：

备份：mysqldump -u用户名 -p密码 [-h服务器ip地址 -P端口号] 数据库名>D:\bak.sql(备份路径及文件名称)

恢复：mysql -u用户名 -p密码 [-h服务器ip地址 -P端口号] 数据库名<D:\bak.sql

(七) MySql乱码问题的处理

临时解决方案：

​ 登录MySql之后，执行一条命令：set names gbk; 之后再执行SQL语句就正常了

但是这个设置，仅在这一次连接里是有效的,下次数据库连接任然会失效

永久解决方案:

​ 找到MySql安装目录下的my.ini文件

搜索[mysql]后边的default-character-set=utf8 把值换成gbk，然后保存文件

​ 注意:修改时可能会有修改权限问题 如果需要权限 则要通过使用管理身份运行记事本,在记事本中打开该文件,修改并保存;

 

需要重启MySql服务，修改的配置文件才会生效

 

                                    MySql多表

pid(主键)	pname(商品名称)	price(商品价格)	number(库存)	商品分类
1	格力空调	2899	20	大型家电
2	容声冰箱	2099	40	大型家电
3	iPhoneX	9489	59	手机数码
4	西瓜	6	340	水果

 

存在的问题

​ 如果某一天需要对端口分类进行修改的话，就需要逐条分别进行修改才可以。影响维护的效率

解决的方案：拆表

​ 分类信息表（主表）category：

cid(主键)	cname
1	大型家电
2	手机数码
3	水果

商品表（从表）product：

pid(主键)	pname	price	number	category_id(外键)
1	格力空调	2899	20	1
2	荣声冰箱	2099	40	1
3	iPhoneX	9499	50	2
4	西瓜	6	500	3

如果某一天，删除了“大型家电”的分类信息，那么在商品表里依然有对应的“大型家电”数据存在，但是这些商品已经找不到对应的分类信息了。数据不完整了，存在了脏数据。

1. 删除主表的数据时，需要先去从表里检查一下，是否有对应的数据存在：如果存在，不允许删除；否则可以删除 "

   2.插入从表的数据时，需要先去主表里查一下，主表是否有对应的数据存在：如果存在，才可以插入；否则不允许插入

外键和外键约束

​ 外键约束的作用: 维护多表的数据一致性和完整性

​ 如果一个字段加了外键约束，那么这个字段的值，就必须是来自于主表的主键的值

设置外键约束

​ 给商品表product的categroy_id加上外键约束，约束这个字段的值必须来自于主表category的主键cid：

​ alter table product add foreign key(category_id) references category(cid);

语法：alter table从表 add [constraint 外键名称] foreign key(外键字段) refreences主表(主键);

外键约束实例:

先创建主表category

插入主表数据:

insert into category(cname) values ('大型家电');

​ insert into category(cname) values('手机数码');

​ insert into category(cname) values('水果');

再创建从表product

插入从表数据:

insert into product (pname, price, number ,category_id) values('格力空调', 2999, 20, 1);

​ insert into product (pname, price, number ,category_id) values('荣声冰箱', 2099, 40, 1);

​ insert into product (pname, price, number ,category_id) values('iPhoneX', 9499, 50, 2);

insert into product (pname, price, number ,category_id) values('西瓜', 3, 500, 3);

 

#没有外键约束的情况：主表数据和从表数据可以进行任意的增、删、改

​ 删除主表的数据：大型家电。可以删除掉，product表里产生了脏数据

​ delete from category where cname = '大型家电';

​ 插入从表的数据：床单被罩。可以插入，但是主表里没有对象的分类数据存在，插入的就是脏数据

insert into product (pname, price, number ,category_id) values ('床单被罩', 100, 50, 4);

​

清除两张表中的数据:

​ delete from product;

​ delete from category;

​ 再给从表添加外键约束

alter table product add foreign key(category_id) references category(cid);

正常插入数据：先插入主表数据，再插入从表数据

insert into category(cname) values ('大型家电');

​ insert into category(cname) values('手机数码');

​ insert into category(cname) values('水果');

 

​ insert into product (pname, price, number ,category_id) values ('格力空调', 2899, 20, 7);

​ insert into product (pname, price, number ,category_id) values ('荣声冰箱', 2099, 40, 7);

​ insert into product (pname, price, number ,category_id) values ('iPhoneX', 9499, 50, 8);

​ insert into product (pname, price, number ,category_id) values('西瓜', 6, 500, 9);

删除主表的数据：大型家电。删除失败，因为有外键约束：从表里有这一分类的数据存在

​ delete from category where cname = '大型家电';

插入从表的数据：订单被罩。插入失败，因为有外键约束：主表里没有id为9的数据

​ insert into product (pname, price, number ,category_id) values ('订单被罩', 100, 50, 11);

 

 

注意:字段 int primary key auto_increment,此约束键下 字段的值为自增数,图中略有不同 查询必须按category_id=cid

一、多表关系和多表设计

一对一

​ 一对一的多表，通常是可以合并成一张表的。如果以下情况，可以考虑拆表：

​ 业务区分更加明确，可以拆表。比如：用户信息表和帐户信息表

​ 存取效率考虑，可以拆表。比如：把常用的字段放在一张表里，不常用的字段放在另外一张表里

一对多

​ 比如：班级和学生，分类和商品，用户和订单

​ 建表原则：在从表（多的一方）建立外键，指向主表（一的一方）的主键

实例：

一对多建表：用户和订单

先建主表：用户表

create table user(

       uid int primary key auto_increment,

​       username varchar(20),

       password varchar(50)

);

再建从表：订单表

create table orders(

       oid int primary key auto_increment,

​       total double (9,2),

​       createtime datetime,

​       uid int

);

​ 一对多建表时，需要给从表建立外键，指向主表的主键。以维护表数据的一致性和完整性

​         alter tlable orders add foreing kry(uid) references user (uid);

多对多:

​ 比如：学生和课程，订单和商品表

​ 建表原则：建立一个中间关系表，以维护多对多的关系

​ 实例:

​ 多对多建表：订单和商品

​ 订单表:

create table orders(

       oid int primary key auto_increment,

       total double(9,2),

       createtime datetime,

       uid int

​ );

商品表:

create table product(

       pid int primary key auto_increment,

       pname varchar (50),

       price double (9,2),

       number int ,

       category_id int

​ );

订单和商品的中间关系表，在建表时直接添加外键约束

pid对应product表的pid

oid对应orders表的oid

create table pro_orders(

poid int primary key auto_increment,

​ pid int,

​ oid int,

​ foreign key (pid) references product(pid),

foreign key (oid) references orders(oid)

);

二、多表查询

​ 从多表中查询有关联的数据叫多表查询。

迪卡尔积：多表之间进行排列组合得到的数据，里边有有效数据，也有无效数据。

(一)内连接查询

从多表中查询必定有关联的数据

隐式内连接查询

​       select * from 表1, 表2 where 表1和表2的关联条件 and 过滤条件

显式外连接查询

        select * from 表1 inner join 表2 on 关联条件 where 过滤条件

(二)外连接查询

左外连接查询

查询出来左表的全部数据，如果右表有相关联的数据，就一并显示出来；如果右表没有相关联的数据，就显示成null

    语法：select * from 左表 left [outer] join 右表 on 关联条件 where 过滤条件

右外连接查询

查询出来右表的全部数据，如果左表有相关联的数据，就一并显示出来；如果左表没有相关联的数据，就显示成null

    语法：select * from 左表 right [outer] join 右表 on 关联条件 where 过滤条件

(三)子查询

​ 单行单列子查询—子查询结果是一个值

​ 单行单列子查询: 查询订单号为2的用户信息

先从订单表里查询订单号为2的uid

​        select uid from orders where oid = 2;

​ 根据uid从user表里查询用户信息

​        select * from user where uid = 3;

把两条语句合并：

​        select * from user where uid = (select uid from orders where oid = 2);

多行单列子查询—子查询结果是一个集合

多行单列子查询：查询2018年下过订单的用户信息

先从订单表里查询2018年下过订单的uid的值

​        select uid from orders where createtime between '2018-1-1' and '2018-12-31';

根据得到的uid的值，去user表里查询对应的用户信息

       ​ select * from user where uid in(1, 3);

把两条语句合并：

​        select* from user here uid in(select uid from orders where createtime between '2018-1-1' and '2018-12-31');

​ 多行多列子查询—子查询结果是一张虚拟表

​ 把子查询的结果当成是一张表，拿这张表和其它表进行关联查询，就是多行多列子查询

多行多列的子查询：查询2018年下过订单的用户信息和订单信息

查下过订单的用户信息和订单信息

       ​ select * from user u, orders o where u.uid = o.uid

​ 如果orders表里的所有数据只剩下2018年的订单信息，再和用户表关联得到的就是2018年下过订单的用户信息和订单信息

​ 查询2018年的订单信息，得到一个虚拟表t

       select * from orders where createtime between '2018-1-1' and '2018-12-31';

​ 虚拟表t里是2018年的订单信息表，和user表关联查询，得到2018年的订单信息和用户信息

       ​ select * from(select * from orders where createtime between '2018-1-1' and '2018-12-31') t, user u where t.uid = u.uid;

 

以上语句也可以使用其它查询方式得到相同的结果，会其中一种即可：

select * from user u, orders o where u.uid = o.uid and o.createtime > '2018-1-1';