原理、库表管理、DQL-CSDN博客

本文链接：https://blog.csdn.net/weixin_52956870/article/details/127561813

数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。数据库是长期储存在计算机内、有组织的、可共享的数据集合。

摘要由CSDN通过智能技术生成

一、原理定义概念

数据库定义

数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。

数据库是长期储存在计算机内、有组织的、可共享的数据集合。

数据的分类：

非结构化数据：

数据相对来讲没有固定的特点，这种数据非常不便于检索

但是生活中很多都是非结构化数据

半结构化数据 :

数据之间有着相同的存储结构属性：值

每一条数据的属性可以不同

张三：

三号学生

李四：

结构化数据 :

创建在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的

数据。

数据之间有着相同的存储结构属性值

同一个结构模型中每一条数据的属性都是相同的，但是值有可能不同，这些数据非常便于存储和管理。

于是针对这些数据，专门设计一套数据存储的软件，现在我们说的数据库主要用来存储这些数据

问题

我们所有的数据库都放在软件中

我们要获取到数据，就需要使用软件给我们提供的访问数据的接口

最痛苦的就是程序员，我们就要为存储数据学习不同的数据库

于是有人开始倡导：使用统一的操作数据库的方式

SQL(Structured Query Language)结构化查询语言

全世界所有的结构化数据库都适用

SQL分类

DDL：数据库定义语言（定义数据库的一些组件表索引视图自增序列...）

DML：数据库操作语言（添加删除修改） CRUD

DQL：数据库查询语言查询

DCL：数据库控制语言（权限用户管理...） DBA工程师

SQL语言属于第四代语言，而java c++ 才属于第三代

二、数据库管理

安装上数据库之后，就需要开始学习指令了，通过指令让MySQL去做出一些文件操作。

如果将数据库管理系统与之前的文件管理做类比的话：

数据库管理系统	文件管理
数据库	文件夹
数据表	文件夹下的excel文件

接下来，我们先学习数据库（文件夹）相关操作的指令。

2.1 内置客户端操作

当连接上MySQL之后，执行如下指令（一般称为SQL语句），就可以对MySQL的数据进行操作。

查看当前所有的数据库： show databases;

创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

create database bigdata19;

create database bigdata19 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

删除数据库：drop database 数据库名;

进入数据（进入文件）：use 数据库;

示例：

# 1.登录MySQL
[root@master ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

# 2.查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.19 sec)

# 3. 创建数据库:  create database 数据库名 default charset 编码 collate 排序规则;
mysql> create database bigdata19 default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.10 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bigdata19          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 4. 删除数据库
mysql> drop database bigdata19;
Query OK, 0 rows affected (0.14 sec)

# 5. 查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

# 6. 进入数据库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

# 7. 进入mysql数据库（文件夹），查看此数据库下的所有表。
mysql>  show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 8. 退出
mysql>exit;

三、数据表管理

3.1 内置客户端操作

数据表常见操作的指令：

进入数据库 use 数据库;，查看当前所有表：show tables;

创建表结构

-- 注意：今后工作中，表名不要以中文起名字，一律使用英文
--尤其是今后你们毕业后从事大数据开发中的有一个项目类别叫做数据中台（数据仓库）
create table 表名(
    列名  类型,
    列名  类型,
    列名  类型,
  列名  类型
)default charset=utf8;

  
  主键一般用于表示当前这条数据的ID编号（类似于人的身份证），需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。
  
  ```sql
  create table tb5(
  	id int not null auto_increment primary key,	-- 不允许为空 & 主键 & 自增
      name varchar(16) not null,   		-- 不允许为空
      email varchar(32) null,      		-- 允许为空（默认）
    age int default 3            		-- 插入数据时，如果不给age列设置值，默认值：3
  )default charset=utf8;

注意：一个表中只能有一个自增列【自增列，一般都是主键】。

删除表 drop table 表名;

清空表 delete from 表名; 或 truncate table 表名;（速度快、无法回滚撤销等）

修改表

- 添加列

alter table 表名 add 列名 类型;
alter table 表名 add 列名 类型 DEFAULT 默认值;
alter table 表名 add 列名 类型 not null default 默认值;
alter table 表名 add 列名 类型 not null primary key auto_increment;

- 删除列

alter table 表名 drop column 列名;

- 修改列类型

alter table 表名 modify column 列名 类型;

- 修改列类型 + 名称

alter table 表名 change 原列名 新列名 新类型;

- 修改列默认值

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

- 删除列默认值

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

- 添加主键

alter table 表名 add primary key(列名);

- 删除主键

alter table 表名 drop primary key;

常见列类型

create table 表(
	id int,
    name varchar(16)
)default charset=utf8;

- int[(m)][unsigned][zerofill]

int				表示有符号，取值范围：-2147483648 ～ 2147483647
int unsigned	表示无符号，取值范围：0 ～ 4294967295
int(5)zerofill	仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

- tinyint[(m)] [unsigned] [zerofill]

有符号，取值范围：-128 ～ 127.
无符号，取值范围：0 ～ 255

- bigint[(m)][unsigned][zerofill]

有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
无符号，取值范围：0  ～  18446744073709551615

- decimal[(m[,d])] [unsigned] [zerofill]

准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

例如：
create table L2(
	id int not null primary key auto_increment,
	salary decimal(8,2)
)default charset=utf8;

- FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。

- DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

- char(m)

定长字符串，m代表字符串的长度，最多可容纳255个字符。

定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：yes，底层也会占用5个字符；如果超出m长度限制（默认MySQL是严格模式，所以会报错）。
    如果在配置文件中加入如下配置，
        sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。。

注意：默认底层存储是固定的长度（不够则用空格补齐），但是查询数据时，会自动将空白去除。 如果想要保留空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
查看模式sql-mode，执行命令：show variables  like 'sql_mode';

一般适用于：固定长度的内容。

create table L3(
    id int not null primary key auto_increment,
    name varchar(5),
    depart char(3)
)default charset=utf8;

insert into L3(name,depart) values("alexsb","sbalex");

- varchar(m)

变长字符串，m代表字符串的长度，最多可容纳65535个字节。

变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。
    如果在配置文件中加入如下配置，
        sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    保存并重启mysql服务，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。

例如：
create table L3(
    id int not null primary key auto_increment,
    name varchar(5),
    depart char(3)
)default charset=utf8;

- text

text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

一般情况下，长文本会用text类型。例如：文章、新闻等。

- mediumtext

A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

- longtext

A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)

- datetime

YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

- timestamp

YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）

- date

YYYY-MM-DD（1000-01-01/9999-12-31）

- time

HH:MM:SS（'-838:59:59'/'838:59:59'）

MySQL还有很多其他的数据类型，例如：set、enum、TinyBlob、Blob、MediumBlob、LongBlob 等，详细见官方文档：https://dev.mysql.com/doc/refman/5.7/en/data-types.html，上述就是关于数据表的一些基本操作。

四、SQL-DQL

概念

关系模型中常用的概念：

关系：可以理解为一张二维表，每个关系都具有一个关系名，就是通常说的表名

元组：可以理解为二维表中的一行，在数据库中经常被称为记录

属性：可以理解为二维表中的一列，在数据库中经常被称为字段

域：属性的取值范围，也就是数据库中某一列的取值限制

关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成

关系模式：指对关系的描述。其格式为：关系名(属性1，属性2， ... ... ，属性N)，在数据库中成为表结构

scott用例表

emp 员工信息表

表名	emp

字段名	中文	类型	是否可以为空	默认值	其他说明
empno	雇员编号	int(11)	否		主键
ename	雇员名称	varchar(255)	是
job	岗位工种	varchar(255)	是
mgr	上级	int(11)	是
hiredate	雇佣日期	date	是
sal	工资	decimal	是
comm	奖金\|津贴	decimal	是
deptno	部门编号	int(11)	是
备注

dept 部门信息表

表名	dept
主键	deptno
字段名	中文	类型	是否可以为空	默认值	其他说明
deptno	部门编号	int(11)	否		主键
dname	部门名称	varchar (255)	是
loc	地址	varchar2(255)	是
备注

salgrade 薪资区间表

表名	salgrade
主键
字段名	中文	类型	是否可以为空	默认值	其他说明
grade	等级	int(11)
losal	最低	int(11)
hisal	最高	int(11)

DQL语句

格式

select 列名*N from 表名 where 查询条件1 and/or 查询条件2 group by 列 Having 分组条件 Order by 排序

规则

sql在书写的时候除了查询条件之外，大小写都可以

select * from user where uname ='zs';

SELECT * FROM USER WHERE UNAME = 'zs';

保持大小写风格

去公司之后需要看其他员工SQL大小写

-- 属于SQL语句的注释

所有的查询条件为字符串时，需要用'xxx'进行修饰，否则就会当做列名去处理

select查询列和别名

--查询所有员工信息(*通配符，默认查询所有的列)
select * from emp;

--查询员工的姓名
select ename from emp;

--查询员工的薪资
select sal from emp;

--查询员工的姓名和薪资
select ename , sal from emp;
select ename sal from emp;
select ename sal comm from emp;

--查询员工的姓名和薪资，推荐使用单引号
select ename '员工姓名', sal "薪资" from emp;

--查询到的数据可以直接进行运算
select ename ,sal ,sal * 12 from emp;
select ename ,sal ,comm ,(sal+comm) * 12 from emp;

select的条件查询

普通条件查询 => < != <> >= <=

--查询员工编号为7369的员工
select ename,sal from emp where empno = 7369;

--查询员工姓名叫做SMITH
select ename,deptno,job from emp where ename = 'SMITH';
select ename,deptno,job from emp where ename = 'smith';

--查询薪资大于2000的员工姓名
select ename from emp where sal > 2000;

--查询工作为SALESMAN
select * from emp where job = 'SALESMAN';

--查询部门在20的员工
select * from emp where deptno = 20;

--查询薪资不等于2000的员工
select * from emp where sal != 2000;
select * from emp where sal != 2000;

in 在某个范围中查找

--查询 员工编号为 7369 7788 7881的员工信息
select * from emp where empno in (7369,7788,7881);

--查询 员工编号除了 7369 7788 7881之外的所有员工信息
select * from emp where empno not in(7369,7788,7881);

--查询除了10,20部门之外的所有员工
select * from emp where deptno not in (10,20);

null值查询

--查询不发放津贴的员工信息
select * from emp where comm is null;

--查询发放津贴的员工信息
select * from emp where comm is not null;

范围比较

--查询薪资范围在1000-4000之间的员工信息 [1000.4000]
select * from emp where sal between 1000 and 4000;

模糊查询 % _

--查询名字中有S的员工
select * from emp where ename like '%S%';

--查询名字最后一个字符是S
select * from emp where ename like '%S';

--查询名字第一个字符是S
select * from emp where ename like 'S%';

--查询名字第二个字符是A
select * from emp where ename like '_A%';

--查询名字中有%的员工
select * from emp where ename like '%\%%';
--查询名字第8 188个字符是A，这是需要一些特殊的手段-》函数
-- % 代表任意字符的任意次数 _任意字符的一次

多条件联合查询 and or

--and 必须前后同时满足条件才能返回结果
--or前后有一个满足条件就能返回结果

--查询在20部门并且薪资大于2000的员工
select * from emp where deptno =20 and sal >2000;

--查询在20部门或者薪资大于2000的员工
select * from emp where deptno = 20 or sal >2000;

--查询不在20部门并且薪资小于2000的员工
select * from emp where deptno <> 20 and sal <2000;

select结果排序 order by

使用asc是升序排列（默认），使用desc可以降序排序

单列

--按照薪资进行排序（默认升序）
select * from emp order by sal;

--按照薪资进行排序（降序）
select * from emp order by sal desc;

--按照薪资进行排序（升序）
select * from emp order by sal asc;

--按照津贴进行排序(null排在最前面)
select * from emp order by comm;

多列

--多个排序的列
select * from emp order by deptno,sal;

--多个排序的列（部门升序 薪资降序）
select * from emp order by deptno,sal desc;

--多个排序的列（工作，薪资）
select * from emp order by job,sal;

select结果分页

--每次查询前N行
SELECT
 * 
FROM
 emp 
 LIMIT 4;
 
--查询第N页,每页显示M个
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
select * from emp limit (n-1)*M,M;

--查询薪资大于1000的逆序排列，然后显示前5条记录
select * from emp where sal >1000 order by sal desc limit 0,5 ;

SQL语句错误

select enane from emp

1054 - Unknown column 'enane' in 'field list'

将来出现错误的时候，直接将错误编号输入到百度查询即可