Mysql数据库学习笔记

数据库概述

数据库，就是存储数据的仓库。根据数据库底层结构的不同，数据库可以分为很多种类，比如早期的层次式数据库、网络型数据库，以及现在流行的关系型数据库和非关系型数据库。
关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织

优点：

1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。

非关系型数据库的分类和比较：

1、文档型

2、key-value型

3、列式数据库

4、图形数据库

Mysql数据库

mysql服务器：就是mysql服务器软件，将服务器软件安装在计算机上，可以对外提供服务，比如存取数据。
mysql服务器可以理解成是一个超大的仓库，其中包含了很多小仓库（数据库）
数据库：数据库就是一个存储数据的仓库，通常情况下，一个网站中的所有数据会存放在一个数据库中。
数据表/表：一张表用于存储一类数据，比如：商品信息可以存储在商品表中，用户信息可以存储在用户表中。
Java中使用类（class）表示一类数据
因此数据库中的表(user表)对应Java中的类（User类）
表记录：用于存储某一个具体的信息，
Java中使用对象表示某一个具体的信息，
因此数据表中的表记录，对应Java中的对象
所以，将来我们可以将java中的对象以一条表记录的形式存放在数据库中
也可以将查询出来的一条表记录，封装到Java对象中。

Mysql常见数据类型

数值类型

在mysql中的数值类型有很多中，和Java中的数值类型一一对应
tinyint–>byte
smallint–>short
int–>int
double–>double

字符串类型

1、char(n): 定长字符串 n的范围是 0 < n <=255 个字符

name char(10)：其中括号中的10表示最大长度，当实际存入的数据长度小于最大长度时，后面剩余的空间会用空格补全。所以，char类型可能会造成空间的浪费。但char类型存储效率比varchar略高。
idcard char(18)
char类型适合存储长度固定的数据，比如身份证号，比如学生编号等，这样既不会有空间的浪费，效率又比varchar高。

2、varchar(n)：不定长字符串 n的范围：

如果是mysql5.0之前的版本： 0<n<=255 个字符
如果是mysql5.0之后的版本： 0<n<=65535 个字节
name varchar(10)：其中括号中的10表示最大长度，当实际存入的数据长度小于最大长度时，后面剩余的空间还可以留给别的数据使用！因此，varchar类型在存储数据时，不会有空间的浪费，但是效率略比char低。
varchar类型适合存储长度不固定的数据，虽然比char类型效率低，但是不会有空间浪费。

日期类型

date：年月日
time：时分秒
datetime：年月日 时分秒
timestamp：是一个时间戳，格式上（显示的）是一个年月日 时分秒时间串，实际上存储的是1970年1月1日但另外一个日期之间的毫秒值
(1) 相同点：都是时间类型，显示的格式都是年月日 时分秒
(2)不同点：
a) datetime范围是 1000~9999 年
timestamp范围是 1970~2038年
b）datetime存储的值就是一个时间字符串
timestamp存储是一个时间毫秒值
c）datetime类型的列，如果不指定值，值为null
timestamp类型的列，如果不指定值，可以设置默认值为当前时间

字段(列)约束

创建表时, 除了要给每个列指定对应的数据类型, 有时也需要给列添加约束。常见的约束有：主键约束、唯一约束、非空约束、外键约束。

1、主键(primary key)

主键是数据表中，一行记录的唯一标识。比如学生的编号，人的身份证号, 例如:
创建user表,指定id为主键, 作为用户的唯一标识

create table stu(
	id int primary key,
	...
);

主键的特点： 唯一且不能为空！！！

当主键为数值时，为了方便维护，可以设置主键为自增（auto_increment）
示例：创建user表,指定id为主键, 并指定主键自增

create table user(
	id int primary key auto_increament,
	...
);

自增的作用是, 后期往表中插入记录时, 自增的列可以不用插入值(插入null即可), 数据库会自动插入值(1,2,3…), 例如：

insert into stu values(null, '王','男', '1993-1-10');
insert into stu values(null, '刘','男', '1995-6-15');

提示：如果建表时没有指定主键自增, 也可以后期修改id为主键自增。

2、唯一(unique)

保证所约束的列必须是唯一的，即不能重复出现，例如：用户注册时，保存的用户名不可以重复。
示例：创建user表, 指定用户名不能重复

create table user(
	id int primary key auto_increament,
	username varchar(50) unique,
	...
);

唯一约束特点是不能重复！！(允许为空)

3、非空(not null)

保证所约束的列必须是不为空的，即在插入记录时，该列必须要赋值，例如：用户注册时，保存的密码不能为空。
创建user表, 指定密码不能为空

create table user(
	id int primary key auto_increament,
	username varchar(50) unique,
	password varchar(50) not null,
	...
);

非空约束的特点是： 不能为空值， 即插入数据时该列必须得有值！！

4、外键约束

外键是用于表和表之间关系的列

MySQL建库、建表

查看数据库、查看数据表

1、查看mysql服务器中所有数据库

show databases;

2、进入某一数据库

use test;

提示，查看已进入的库：select database();

3、查看当前库中的所有表

show tables;

提示：mysql数据库不区分大小写

创建数据库、创建数据表

1、创建数据库

– 删除mydb1库(如果存在)

drop database if exists mydb1;

– 重新创建mydb1库

create database mydb1 charset utf8;

– 查看、进入mydb1库

show databases;
use mydb1;

提示：创建库时，要记得指定编码

2、创建数据表

– 删除stu学生表(如果存在)

drop table if exists stu;

– 创建stu学生表

create table stu(
	id int,				-- 学生编号
	name varchar(20),	-- 学生姓名
	gender char(1),	-- 学生性别
	birthday date,		-- 出生年月
	score double		-- 考试成绩
);

– 查看表结构

desc stu;

提示：# 和 – 是sql语句的注释符号。例如：
– 这是注释内容(要注意的是–和注释之间要有空格)
#这是注释内容

更新表记录（insert/update/delete）

insert–插入表记录

插入记录的语法：

insert into 表名(列1,列2...)  values(值1, 值2...);

– insert用于向指定的表中插入哪些列, 以及给予哪些值
如果要为所有的列插入值, 可以省略表名后面的列列表，例如：

insert into 表名 values(值1, 值2...);

注意: (1)只能在给所有列插入值时, 才可以省略列列表, 否则会报错
(2)在插入值时, 如果没有省略列列表, 那么列列表和值列表要一一对应
(3)在插入值时, 如果省略了列列表, 那么值列表中值的顺序要和声明列的顺序一致
(4)在插入字符串和日期时, 字符串和日期要用单引号引起来。

– 例如: 往学生表(stu)中插入记录

insert into stu(id,name,gender,birthday,score) values(1, '王','男', '1993-1-10', 78);
insert into stu values(2, '齐雷','男', '1995-4-8', 78);

– 或为指定列插入数据

insert into stu(id, name, gender) values(3, '刘', '女');

常见问题（FAQ）：在CMD中插入记录时如果报如下错误，

因为这里存在中文的乱码问题。
解决方式：先设置编码GBK “set names gbk”, 再插入记录
因此，在通过CMD访问数据库时，最好一登录就设置 “set names gbk”，即可避免在插入数据时或者查询数据时数据乱码

update–修改表记录

– 修改stu表中王的成绩，加10分特长分。

update stu set score=score+10 where name='王';

– 查询stu表中王的信息

select * from stu where name='王';

若没有where子句, 则默认修改所有员工
– 修改stu表中所有学生的成绩，加10分特长分。

update stu set score=score+10;
-- update stu set score=ifnull(score, 0)+10;

注意: null值和任何值计算结果还是null, 因此, 可以通过ifnull函数将null置为零对待.
4.3 delete–删除表记录
– 删除stu表中的王信息

delete from stu where name='王';

若没有where子句, 则默认删除所有记录
– 删除emp表中的所有信息

delete from stu;

查询表记录（select）

基础查询

– 查询emp表中的所有员工，显示员工姓名、薪资、奖金

select name,sal,bonus from emp;

– 查询emp表中的所有员工，显示所有列。

select * from emp;

提示: distinct关键字，用于剔除指定列中的重复值，例如：
– 查询emp表中的所有部门，剔除重复的部门

select distinct dept from emp; 

SQL查询

什么是SQL语言?

SQL：结构化查询语言，是一个通用的操作关系型数据库的语言。也就是说，学会SQL语言，不仅可以操作mysql数据库，还可以操作比如Oracle、SQLServer等关系型数据库。
由于数据库厂商会自己定义一些特有的SQL语句，这些SQL语句是不通用的。称之为数据库的“方言”。

SQL查询

Mysql中常见的函数

1、ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值。
2、count( 列 | * ) – 对某一列或者对所有列进行统计行数.
3、max( 列 | * )/min( 列 | * ) 对某一列求最大值或最小值
4、sum( 列 | * ) – 对某一列求和
5、avg( 列 | * ) – 对某一列求平均值
6、year( date ) – 获取日期中的年份
7、month( date ) – 获取日期中的月份
8、day( date ) – 获取日期中的天数
9、curdate() – 获取当前日期,格式为年月日
10、curtime() – 获取当前时间,格式为时分秒
11、sysdate() – 获取当前日期+时间,格式为年月日 时分秒

指定列别名

在查询时，可以为列指定别名，指定方式为，在列名后面添加as进行指定，例如：

select 列1 as 别名1, 列2 as 别名2,...
from 表名

另外，as也可以省略不写：

select 列1 别名1, 列2 别名2,...
from 表名

Where子句中的运算符

对表中的所有记录进行筛选、过滤使用where子句
下面的运算符可以在 WHERE 子句中使用：

6 外键和表关系

外键约束

在现实生活中,有很多数据间往往是有关系, 比如:员工和部门之间存在一定的关系；学生和老师之间也存在一定的关系。通常情况下，我们会在其中的一张表中，添加一个列，来保存另外一张表的主键，以此来保存两张表数据之间的对应关系。
外键：就是一个列，用于保存两张表之间的对应关系。

Create table emp(
		id int primary key auto_increment,
		name varchar(50),
		dept_id int,
		foreign key(dept_id) references dept(id)   #添加外键约束
);

表关系

1、一对多或多对一

针对一对多或者多对一的关系，可以在多的一方，添加列保存一的一方的主键，以此来保存两张表数据之间的对应关系。

2、一对一

针对一对一的关系，可以在任意一方添加列，保存另一方的主键，以此来保存两张表之间的对应关系。

3、多对多

针对多对多的关系，不能在任何一张表中添加列来保存对应关系，我们可以再创建一张第三方的表，来保存两张表的主键，以此来保存两张表的对应关系！