数据库基础

数据库

–1,概述

数据库database简称db , 就是用来存储数据和管理数据 的仓库 .
分类: 关系型数据库 : 指存放的数据之间是有紧密关系的 ,如: Mysql Oracle
非关系型数据库 : 指存放的数据之间关系松散 , 如: Redis

–2,安装

安装 服务器端 和 客户端
1, 服务器端: 用来存储了数据
重点步骤: 设置字符集utf-8避免中文乱码.
使用端口号: 默认是3306
使用的服务名称: Mysql
设置密码: root
检查:找到开始菜单中的Mariadb – mysql client – 输入密码root回车
2, 客户端: 用来连接服务器, 进而操作数据
DOS窗口:
a, 输入密码: 就可以连接服务器(找到开始菜单中的Mariadb – mysql client – 输入密码root回车)
b, 输入dos命令: win+r打开运行对话框 – 输入cmd – 确定 – 输入命令:mysql -uroot -proot – 回车

登陆成功可以查看版本，也可以通过sqlyog输入

select version() from dual;

3.查询MySQL位置
DOM窗口登陆MySQL，输入

show variables like "%char%";

sqlyog输入

select @@basedir as basePath from dual

可视化工具: sqlyog

–3,数据库结构

1, 数据库 : 创建库,删除库,修改库,查询库 – 增删改查
2, 表: 指定字段,行(一条一条的记录)和列(每条记录的相关数据) – 增删改查
3, 数据/记录 : 存储 和 管理数据 – 增删改查

SQL语言

1,概述

是简称,全称是结构化查询语言. 专门用来对数据库进行操作

2,数据库的操作

1,查询所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cgb2107            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

2,创建数据库(不能同名)

mysql> create database cgb2108;
Query OK, 1 row affected (0.00 sec)
mysql> create database cgb210801 default character set utf8;  #指定字符集,避免了中文乱码
Query OK, 1 row affected (0.00 sec)

3,删除数据库

mysql> drop database cgb2108;  #谨慎操作
Query OK, 0 rows affected (0.00 sec)

4,使用数据库

mysql> use cgb210801;  #使用指定的数据库
Database changed

3,表的操作

1,查询所有表

mysql> show tables;  #查询所有表
Empty set (0.00 sec)

2,创建表

语法: create table 表名(字段名称 字段类型(字段长度),字段2,字段3....)
mysql> create table tb_door(
    -> id int(11),
    -> door_name varchar(100),
    -> tel varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

3,修改表

语法： alter table  表名    添加字段  字段名称 字段类型(字段长度)
mysql> alter table student add column address varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student; #查看表的结构
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(10)  | YES  |     | NULL    |       |
| age     | int(5)       | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

4,删除表

mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)

4,记录

1,查询所有记录

语法:  查询  所有  从  表名
mysql> select * from tb_door;
Empty set (0.00 sec)

2,向表中插入记录

语法:  插入数据    表名    值(字段1的值,字段2的值,字段3的值)
注意:表里有几个字段values需要提供几个值+值的顺序和字段顺序一致
mysql> insert into tb_door values(10,'test',"010-666");
mysql> insert into tb_door values(10,'test',"020-1234");
mysql> insert into tb_door values(20,'first',010-888);

3,修改记录

语法:  更新    表名   设置  字段名=字段的新值
mysql> update tb_door set   tel='010-857';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

4,删除记录

语法: delete from 表名
mysql> delete from tb_door;  #删除了表里的所有记录
Query OK, 3 rows affected (0.00 sec)

数据类型

命名规则

• 字段名必须以字母开头，尽量不要使用拼音
• 长度不能超过30个字符（不同数据库，不同版本会有不同）
• 不能使用SQL的保留字，如where,order,group
• 只能使用如下字符az、AZ、0~9、$ 等
• Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
• 多个单词用下划线隔开，而非java语言的驼峰规则

字符

• char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
• varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
• 大文本: 大量文字（不推荐使用，尽量使用varchar替代）
  以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

数字

• tinyint,int整数类型
• float,double小数类型
• numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
• decimal和numeric表示精确的整数数字

日期

• date 包含年月日
• time时分秒
• datetime包含年月日和时分秒
• timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

图片

• blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

使用Sqlyog工具

–1,创建库

右键 – 创建库 – 输入数据库的名字/选字符集utf8 – 创建

–2,创建表

选中Tables – 右键 – 新建表 – 填写字段名称/字段类型/字段长度 – 创建 – 输入表名 – 确定

–3,创建记录

可以自己写SQL语句来执行,也可以直接利用工具在表中双击录入数据并保存

字段约束

–1,主键约束 primary key

哪个字段添加了主键约束,哪个字段就是一张表里的唯一的主键.
约束了字段的值,必须唯一且不能为空
主键自动递增策略: 主键的值交给了数据库去管理,数据库会查到当前记录中的最大值+1.

CREATE TABLE a(
  id INT PRIMARY KEY 
)
#1.主键约束,给字段添加PRIMARY KEY 
#特点是:字段值必须唯一 + 值不能为null
INSERT INTO a VALUES(10)#成功
INSERT INTO a VALUES(10)#不成功,因为10已经存在
INSERT INTO a VALUES(NULL)#不成功,不能插入null
#2.主键自增策略:是指主键的值不需要程序员数,
#交给数据库自增,给主键添加 AUTO_INCREMENT
CREATE TABLE b(
  id INT PRIMARY KEY AUTO_INCREMENT
)

–2,非空约束 not null

哪个字段添加了非空约束,哪个字段的值不能为null

#3.非空约束,给字段添加 not null
#特点是:字段的值必须不能为null
CREATE TABLE c(
  id INT PRIMARY KEY AUTO_INCREMENT,
  pwd VARCHAR(10) NOT NULL
)
#第一个null是id的值,id是主键自动递增就不用程序员赋值了,
#数据库会自动+1
INSERT INTO c VALUES(NULL,'123456')#成功,pwd有值
INSERT INTO c VALUES(NULL,NULL)#不成功,pwd不能是null

–3,唯一约束 unique

哪个字段添加了唯一约束,哪个字段的值就必须唯一

#4.唯一约束,给字段添加unique
#特点是:值必须唯一
CREATE TABLE d(
 id INT PRIMARY KEY AUTO_INCREMENT,
 nick VARCHAR(100) UNIQUE
)

–4,外键约束 foreign key

DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
 
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
);
 
CREATE TABLE tb_user_address (
 
user_id INT PRIMARY KEY NOT NULL,
 
address VARCHAR(200),
 
foreign key(user_id) REFERENCES tb_user(id)
 
);
 
DESC tb_user;
 
tb_user_address中user_id字段录入tb_user表不存在的主键值，将报错

–5,默认约束 default

默认值

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
createdTime DATE DEFAULT NOW()
 
);
 
DESC tb_user;

–6,检查约束 check

很少使用，了解即可，录入age超过200将报错

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据

CREATE TABLE tb_user (

id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键

NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引

sex CHAR(2) DEFAULT ‘男’, #默认值

phone CHAR(18),

age INT,

CHECK (age>0 AND age<=200),

createdTime DATE DEFAULT NOW()

);

DESC tb_user;

测试

#字段约束:非空约束/唯一约束/主键约束/默认约束..
#1.默认约束:给字段使用default添加默认值
CREATE TABLE e(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex VARCHAR(10) DEFAULT '男' #默认约束,设置默认值
)
#2.检查约束:给字段使用check添加合法性的检查
CREATE TABLE f(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age<100)#检查约束,age录入不合法数据时无法保存
)
#3.外键约束:防止了冗余的数据,通过外键来描述两张表的关系
#特点是:当子表中添加数据时,子表的主键值 必须 取自主表!
       #当主表删除数据时,子表没有相关的记录
CREATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20),
 PASSWORD VARCHAR(20)
)
CREATE TABLE tb_user_addr(
 user_id INT PRIMARY KEY,
 address VARCHAR(100) ,
 #描述和tb_user表的关系,外键
#语法:foreign key(本表的主键名) references 对方表名(对方表的主键)
 FOREIGN KEY(user_id) REFERENCES tb_user(id) #创建外键
)

基础函数

概述

类似于java中的方法,也有()作为标记,主要也是用来提高SQL的效率
可能有些函数的使用需要传递参数
包括: ???

lower_数据转小写

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

upper_数据转大写

select upper(dname) from dept --数据转大写

length_数据的长度

select length(dname) from dept --数据的长度

substr_截取

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat_拼接数据

select dname,concat(dname,'123') X from dept --拼接数据

replace_替换

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

ifnull_替换null

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

round & ceil & floor_取整函数

round四舍五入，ceil向上取整，floor向下取整

–直接四舍五入取整

select comm,round(comm) from emp

–四舍五入并保留一位小数

select comm,round(comm,1) from emp

–ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

uuid

SELECT UUID()

返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

now_时间函数

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

year & month & day_时间截取函数

–hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

转义字符

'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'ab'cd' -- 单引号是一个SQL语句的特殊字符

select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符

测试

#查询部门表中的部门名称
#select 部门名称 from 表名 
SELECT * FROM dept
SELECT dname FROM dept #查询时使用字段名代替了*
SELECT dname,loc FROM dept #查询多个字段的值时用逗号隔开
#基础函数
#upper(a)把a的值变大写,lower(a)把a的值变小写
SELECT dname,UPPER(dname),LOWER('ABC'),LOWER(dname) FROM dept
#length(a)把a的值求长度,一个字母/数字长度为1,一个汉字长度为3(utf8)
SELECT ename,LENGTH(ename),job,LENGTH(job) FROM emp
#substr(a,b,c)-a是字段名b是截取的开始位置c是截取的长度
SELECT ename,SUBSTR(ename,2),SUBSTR(ename,2,3) FROM emp 
#concat(a,b,c)-a是字段名b是想要拼接的内容c是想要拼接的内容
SELECT ename,CONCAT(ename,123,'abc') FROM emp
#replace(a,b,c)-a是字段名b是要被替换的字符c是新数据
SELECT dname,REPLACE(dname,'o','666') FROM dept
#ifnull(a,b)-a是字段名b是要把null替换成的值
SELECT comm,IFNULL(comm,1000) FROM emp
#对小数的处理:round四舍五入取整 ceil向上取整 floor向下取整
SELECT comm,ROUND(comm),CEIL(comm),FLOOR(comm) FROM emp
#对日期的处理:now获取当前系统时间year获取年month获取月day获取天
SELECT NOW(),YEAR(NOW()),MONTH(NOW()),DAY(NOW())
#对日期的处理:HOUR获取时MINUTE获取分SECOND获取秒
SELECT NOW(),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())

条件查询

distinct_去重

使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;

SELECT DISTINCT loc FROM dept;

where

注意:where中不能使用列别名!!

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或

select name, sal from emp where sal in(1400,1600,1800);

select name, sal from emp where sal not in(1400,1600,1800);

like_类似

通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

null_

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

between and_范围

SELECT * FROM emp

select * from emp where sal<3000 and sal>10000

select * from emp where sal<=3000 and sal>=10000--等效

select * from emp where sal between 3000 and 10000--等效

limit_截取

分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

order by_排序

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

测试

#常见的业务中:增删改查CRUD,其中查询需求最多
#条件查询:
#1.distinct把数据去重
SELECT DISTINCT loc FROM dept
#2.where用来引导判断条件
#select ? from ? where ?
#练习1:查询部门编号=40的数据
SELECT * FROM dept WHERE deptno=40
SELECT * FROM dept WHERE deptno=40000
#练习2:查询部门编号>10的数据
SELECT * FROM dept WHERE deptno>10
SELECT * FROM dept WHERE 1=1 #条件永远成立
#练习3:查询部门编号>10并且地址在二区的数据
SELECT * FROM dept WHERE deptno>10 AND loc='二区'
#练习4:查询部门编号>10或者地址在二区的数据
SELECT * FROM dept WHERE deptno>10 OR loc='二区'
#练习5:查询部门编号=10的或者=20的或者=30的数据
SELECT * FROM dept WHERE 
		deptno=10 OR deptno=20 OR deptno=30
#同上的需求,被简化--in子句
SELECT * FROM dept WHERE deptno IN(10,30)#查deptno=10 or deptno=30的
SELECT * FROM dept WHERE deptno NOT IN(10,20,30)

#3.like模糊查询 %是通配符,通配0~n个字符
#练习1:查询按照员工名字以l开头员工信息
SELECT * FROM emp WHERE ename LIKE 'l%' #以l开头
#练习2:查询按照员工名字包含o 的员工信息
SELECT * FROM emp WHERE ename LIKE '%o%' #中间包含o
#练习3:查询按照员工名字以a结尾 的员工信息
SELECT * FROM emp WHERE ename LIKE '%a' #以a结尾 

#4.null数据的处理
#练习1:查询没有奖金的员工信息
SELECT * FROM emp WHERE comm IS NULL
#练习2:查询有奖金的员工信息
SELECT * FROM emp WHERE comm IS NOT NULL

#5.between...and在区间范围内的数据
#练习1:查询工资范围在5000~10000内的员工信息
SELECT * FROM emp WHERE sal>5000 AND sal<10000 #(5000,10000)
SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000 #[5000,10000]

聚合函数

–1,概述

把一列的所有值聚合起来,然后做数据分析
常见的聚合函数: count() max() min() avg() sum()

count_统计数量

select count(*) from emp --底层优化了

select count(1) from emp --效果和*一样

select count(comm) from emp --慢,只统计非NULL的

max / min_求大小

select max(sal) from emp --求字段的最大值

select max(sal) sal,max(comm) comm from emp

select min(sal) min from emp --获取最小值

select min(sal) min,max(sal) max from emp --最小值最大值

SELECT ename,MAX(sal) FROM emp group by ename --分组

sum / avg_求和，平均数

select count(*) from emp --总记录数

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

–2,测试

#1.聚合函数:把一列的所有值聚合在一起
SELECT sal FROM emp#查询员工工资
SELECT MAX(sal) FROM emp #查询最高工资
SELECT MIN(sal) FROM emp #查询最低工资
#SUM查询工资总和,AVG平均工资
SELECT SUM(sal),AVG(sal) FROM emp
#count()统计总记录数
SELECT COUNT(sal) FROM emp
SELECT COUNT(comm) FROM emp #不统计null元素
SELECT COUNT(*) FROM emp #低效
SELECT COUNT(1) FROM emp #高效

三,分组

–1,概述

就是把数据,按照一些维度分成组,然后再把这一组数据继续分析

group by

#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

SELECT deptno,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno #按照deptno分组

SELECT job,MAX(sal),AVG(sal) FROM emp

GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno,job #deptno和job都满足的

having

#平均工资小于8000的部门

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp

GROUP BY deptno #按deptno分组

HAVING COUNT(deptno)>1 #次数多的

–2,测试

#1,分组:group by,把数据按照维度分组,后,数据分析
  #口诀:
  #什么时候要分组?查询时出现了混合列
  #按照什么分组?按照非聚合列分组
#练习1:统计每个岗位的平均薪资
SELECT job,AVG(sal) FROM emp GROUP BY job 
#练习2:统计每个部门的员工人数
SELECT deptno,COUNT(1) FROM emp GROUP BY deptno
#练习3:统计每年入职的人数
SELECT YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate)
#练习4:统计每个部门的最高薪
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno

#2.分组后过滤having
#练习1:统计每个部门的最高薪,只要>10000的记录
SELECT MAX(sal) a,deptno FROM emp 
GROUP BY deptno #按照非聚合列分组
HAVING a>10000 #group by后的过滤必须用having
#练习2:统计每年入职的人数,只要人数>1的记录
SELECT COUNT(1),YEAR(hiredate) a FROM emp
#where COUNT(1)>1 
#where里不能用别名,不能出现聚合函数,比having高效
GROUP BY a # 按照非聚合列a分组
HAVING COUNT(1)>1 # 按照人数b过滤
#having和where能互换吗?效率谁高?

扩展

char和varchar有什么区别？

char为定长字符串，char(n)，n最大为255

varchar为不定长字符串，varchar(n)，n最大长度为65535

char(10)和varchar(10)存储abc，那它们有什么差别呢？

char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

datetime和timestamp有什么区别？

数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。

date 是 年与日

time是 时分秒

datetime年月日时分秒，存储和显示是一样的

timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数

中文乱码

如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names gbk;

设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。

那为何会造成乱码呢？

Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;

但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。

我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。

注释

/* 很多注释内容 */

#行注释内容

– 行注释内容，这个使用较多

主键、外键、唯一索引的区别？

Primary Key 主键约束，自动创建唯一索引
Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写
Unique Index 唯一索引，唯一值但不是主键
对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

drop、delete和truncate之间的区别？

drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数