数据库从入门到精通01

数据库应用

概念

什么是数据库

简而言之，就是存储数据，管理数据的仓库。

常见的数据库分为：

• 关系型数据库， Oracle、MySQL、SQLServer、Access
• 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
  
  
  

关系型和非关系型

既然干不掉，很多传统项目的还是围绕关系型数据库的居多，所以我们先来学习关系型数据库，目前最流行的关系型数据库是MySQL。

关系型数据库

Mysql数据库

1. mysql服务端，它来处理具体数据维护，保存磁盘
2. mysql客户端，CRUD新增，修改，删除，查询

MySQL数据存放在哪里？

在MySQL的配置文件my.ini中会进行默认配置

MySQL服务端

mysql-5.5.27-winx64.msi

Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8

注意：配置完，mysql开始执行，最后一步出错有时仍可以使用，使用SQLyog工具测试，如不行，再执行安装程序，选择remove，删除，然后重新安装。同时注意必须是管理员权限。

MySQL客户端1：DOS窗口

mysql -uroot -proot

语法：mysql.exe执行文件

代表参数

-u 用户名，紧接着写的

-p 密码，紧接着写的

MySQL客户端2：可视化工具

数据库的结构

数据库结构

SQL语句

定义

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ “S-Q-L”)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。

分类

• DML（Data Manipulation Language）数据操纵语言

如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

• DDL（Data Definition Language）数据库定义语言

如：create table之类

• DCL（Data Control Language）数据库控制语言

如：grant、deny、revoke等，只有管理员才有相应的权限

• DQL（Data Query Language）数据库查询语言
  如: select 语法
  
  

注意：SQL不区分大小写

数据库常用操作

建库

• 创建数据库，数据库名称：cgb2022

create database cgb2022 DEFAULT CHARACTER SET utf8; 

删库

• 删除名称是cgb2022的数据库

drop database cgb2022; 

查看所有数据库

• 查看所有数据库

show databases; 

表的常用操作

使用数据库：use cgb2022; 

表设计

门店表： tb_door

订单详情表： tb_order_detail

创建表

• 创建tb_door表，有id,door_name,tel字段

create table tb_door( id int primary key auto_increment, door_name varchar(100), tel varchar(50) ); 

修改表

• 添加列

alter table tb_door add column money NUMERIC(7,2) 

删除表

• 删除名称是tb_door的表

drop table tb_door; 

查看所有表

• 查看所有表

show tables; 

查看表结构/设计表

• 查看tb_door表结构

desc tb_door; 

表记录的常用操作

插入记录

• 向tb_door表中插入2条记录

insert into tb_door values(null,'永和大王1店',666); insert into tb_door values(null,' 永和大王2店',888); 

查询记录

• 查询tb_door表中的所有记录

SELECT * FROM tb_door; 

修改记录

• 修改tb_door表中id为1的记录

update tb_door set tel=555 where id=1; 

删除记录

• 删除tb_door表中id为2的数据

Delete from tb_door where id=2; 

排序

• 将tb_door表记录按照tel排序

Select * from tb_door order by tel desc; 

记录总数

• 查询tb_door表中的总记录数

Select count(*) from tb_door; 

数据类型

命名规则

• 字段名必须以字母开头，尽量不要使用拼音
• 长度不能超过30个字符（不同数据库，不同版本会有不同）
• 不能使用SQL的保留字，如where,order,group
• 只能使用如下字符a_z、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。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

准备数据

部门表 dept

CREATE TABLE dept( deptno int primary key auto_increment , dname VARCHAR(20), loc VARCHAR(13) ); INSERT INTO dept VALUES(null,'accounting','一区'); INSERT INTO dept VALUES(null,'research','二区'); INSERT INTO dept VALUES(null,'operations','二区'); 

员工表 emp

Mysql:

CREATE TABLE emp( empno int primary key auto_increment, ename VARCHAR(10), job VARCHAR(10), mgr int, hiredate DATE, sal double, comm NUMERIC(7,2), deptno int ); INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1); INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2); INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2); INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2); INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2); 

字段约束

主键约束

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

添加主键约束，例如将id设置为主键：

主键自增策略 当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

create table abc( id int primary key auto_increment ); insert into abc values(null); insert into abc values(null); insert into abc values(null); select * from abc; 

非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

添加非空约束，例如为password添加非空约束：

create table user( id int primary key auto_increment, password varchar(50) not null ); show tables; insert into user values(null,null);//不符合非空约束 insert into user values(null,123;);//OK 

唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

添加唯一约束，例如为username添加唯一约束及非空约束：

create table test( id int primary key auto_increment, username varchar(50) unique--唯一约束 ); show tables; insert into test values(null,'lisi'); insert into test values(null,'lisi');--username的值要唯一,重复会报错的 select * from test;