数据库整理

数据库：

是存储和管理数据的仓库 每个数据库都有一个或多个不同的API（接口）用于创建，访 问，管理，搜索和复制所保存的数据。

现在我们使用关系型数据库管理系统（RDBMS）

关系数据库管理系统( Relational Database Management System）

特点：

1.数据以表格的形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多的行和列组成一张表单

5.若干的表单组成database

 

安装服务器 mysql 客户端 sqlyog

关系型数据库：都是以表格的形式取存储数据 分为：行 字节

oracle

mysql

sql server

db2

 

非关系类型：nosql

mongodb

数据库不允许相同序号录入 最终会以文件的形式存储

引擎：

可插入式引擎，不同引擎 设置存储管理不同

mysql：

默认端口号3306

默认字符集utf8

启动数据库：

如果没有默认启动 可以在计算机管理里 服务查看 右键启动，

1 ：dos 命令也可以net start mysql来启动，默认自带一个客户端，直接输入密码 也可操作数据库

doc命令，mysql-uroot-p 输入密码 也可链接

或 mysql -h 输入IP地址 -uroot-p 输入密码 也可链接

2：使用MySQL Command Line Client 登录

修改root 密码的语句:

alter user root@localhost identified with mysql_native_password by '654321';

mysqladmin -uroot -p123456 password 不用进入mysql内部

 

SQL:结构化查询语言：四大块

DML：数据操作语言 ——用于操作数据库中所包含的数据

命令：INSERT, 插入

UPDATE , 修改

DELETE，删除

DDL：数据定义语言——用于创建和删除数据库对象等操作

命令： CREATE, 创建

DROP , 删除

ALTER，修改

DQL：数据查询语言——用来对数据库中的数据进行查询

命令： SELECT ，查寻

DCL：数据控制语言——用老控制数据库组件的存取许可，存取权限等

命令： GRANT, 管理 授权

COMMIT，提交

ROLLBACK，回滚

也有算数运算符 + , -, *, / , %

赋值运算符: =

逻辑运算符: and or not

比较运算符 = ,> ,< ,>=, <= , != ,<>不等于

 

 

 

 

SQLyog管理工具：

 

MySQL数据类型：

数值类型：

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	（非常小的数据）小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	（较小的数据）大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	（中等大小的数据）大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	（标准整数）大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	（较大的整数）极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	（字符串形式的浮点数）小数值

UNSIGNED属性： 表示为无符号数 没有负数，有符号的是有正数有负数

ZEROFILL属性：宽度（位数）不足以0填充 宽度：以几位去显示

若某数值字段指定了ZEROFILL属性，将自动添加UNSIGNED属性（不会有负数）

字符串类型：

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR(M) 固定长度可以用 ，定义多长就是多长

VARCHAR(M) 不确定长度，根据内容决定长度

日期类型：

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD年月日	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS 年月日时间秒	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

若某日期字段默认值为当前日期，一般设置为TIMESTAMP类型

 

字段的约束和属性：

非空约束 NOT NULL 字段不允许为空

默认约束 DEFAULT 赋予某字段默认值

唯一约束 UNIQUE KEY(UK) 设置字段的值是唯一的，不可以重复，允许为空 但只能有一个

空值

主键约束 PRIMARY KEY(PK) 设置该字段为表的主键 可唯一表示该表记录/一张表只能有一个

主键

外键约束 FOREIGN KEY(FK) 用于在两表之间建立关系，需要指定引用主表的哪一字段

自动增长 AUTO_INCREMENT 默认该列表为自增字段 默认每条自增1 通常用于设置主键

 

 

常用mysql命令

• 创建数据库
• 外键
• 增删改查表
• 权限

 

注释：COMMENT

 

MySQL有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：

　　MyISAM、InnoDB、MERGE、MEMORY（HEAP）、BDB（BerkeleyDB）、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

常用的两种：

区别： innoDB 偏向增删改 myisam 偏向查询

 

事务处理： 支持 不支持

（事务是在同时操作1个一上数据的时候 同时成功或失败 就要设置事务）

 

数据行锁定： 支持 不支持

（当多个人进行数据操作的时候，把要修改的数据锁定，其他人无法同时修改 ）

 

外键约束： 支持 不支持

（主要约束给从表插入数据时 外键的值主键也要存在）

 

全文索引： 不支持 支持

（索引指通过目录查询 全文指有大量文本时 ）

 

表空间大小 ： 较大，约两倍 较小

（表的存储空间）

 

查看默认引擎：

show variables like 'storage_engine%';

 

修改数据库默认引擎 ：修改my.ini配置文件

default-storage-engine=InnoDB---可改为其它存储

 

创建表时设置引擎：

语法 CREATE TABLE 表名（。。。代码）ENGINE=存储引擎

MyISAM类型表文件：

*.frm 表结构定义文件

*.myd 数据文件

*.myi 索引文件

 

InnoDB类型表文件：

*.frm 表结构定义文件

ibadata1 文件

 

存储位置：

因操作系统因人而异，可查my.ini

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

innodb_data_home_dir="D:/MySQL Datafiles

 

DML语句

插入单条数据记录：

语法 INSERT INTO 表名[(字段名列表)] VALUES (值列表)；

（数值类型都不加单引号 其他都加单引号，字段名省略默认插入所有字段）

插入数据示例：

INSERT INTO 'type1' VALUE(12) ,(1234),(123456) ;

# 表名中 插入 值 3 个记录

插入多条：

INSERT INTO 表名[(字段名列表)] VALUES (值列表1),(值列表2),(值列表3)；

示例：

INSERT INTO STUDENT（字段名） VALUES(3,'123',’李静‘， 2 ‘111233333333’，‘地址’‘时间日期’，‘邮箱’，‘身份证号码’)；如果是自动增长 写NULL

 

从学生表提取姓名手机号两列数据到通讯库表中：（相当于备份）

create table 通讯录表 （select ' 数列'，‘数列’FROM '学生表'）；（表必须不存在，如新表已存在将会报错）

在已有的表中引用其他表中的数据 进行添加：（也属于备份）

insert into xxx select 数列1，数列2 from 表名；（表必须存在）

 

更新数据记录：

Update表名 set 字段1=值1，字段N=值n [where条件]；改谁的条件就写谁的，如果不写条件会改变所有的

update xxx set sex=‘女’where sex='男‘

条件为空 is null 不是空的 is not null

 

删除数据记录：

1 DELETE FROM 表名 [ where 条件] ; 不写条件会删除所有表记录

2 TRUNCATE TABLE 表名; 会删除所有表记录

truncate 语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行比DELETE快

 

 

 

自己创建数据库;

语法：CREATE DATABASE 数据库名；（分号结束/不区分大小写)

Query OK,1 row affected <0.00 sec>

#执行成功，操作影响的行数，<操作执行的时间>

创建数据库时后面可加 charset'utf8'; 让其支持中文

创建数据库 设定表结构 每个字段都要指定类型，规定长度，规定是否为空，必须按照字段类型存储

查看所有的数据库：

show databases ;

删除数据库：

语法：DROP DATABASE数据库名；

Query OK, 0 rows affected (0.02 sec)

#执行成功，操作影响的行数，<操作执行的时间>

查看my.ini配置文件，可查看更改配置 （MySQL装在哪就去哪找）

 

创建表：

语法：CREATE TABLE [ IF NOT EXISTS ] 表名（字段1 数据类型 [ 字段属性 约束 ] [ 索引][注释]...

字段n 数据类型 [ 字段属性 约束 ] [ 索引 ] [ 注释 ]）[表类型] [表字符集] [注释]；

示例：

CREATE TABLE 'student'( 'studentNo' INT(4) PRIMARY KEY, 'name' CHAR(10)....;

[ ]为可选部分，

IF NOT EXISTS如果不存在,

多字段都用逗号分割，

保留字用撇号括起来

单行注释#

多行注释/* */

 

示例：

CREATE TABLE type ( 'sid' INT (4) ZEROFILL) ;

# 表名（‘字段名’ 类型 （宽度）以0填充）

 

查看所有表：

SHOW TABLES ；

查看表是否存在

USE XX ; SHOW TABLES ；

查看表定义

DESCRIBE 表名； 或 DESC 表名；

（避免DOS窗口乱码，可执行 SET NAMES GBK；）

 

查看表：要先进入数据库：

use mysql ;

查看数据库里有什么表：

show tables;

看表里面的结构：

desc 表名字 ；

可以查看表的具体内容：

select * from user \G；（*号查所有）加 \G 会变竖列可读性好

删除表：

DROP TABLE [ IF EXISTS ] 表名；[如果表名存在]

示例：

USE XXX；

DROP TABLE IF EXISTS 'XXX'；（删除之前验证表是否存在 ）

修改表：

修改表名：

ALTER TABLE 旧表名 RENAME [TO] 新表名；

修改字段：

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [ 属性]；

修改数据类型不能为空:

Alter table xxx modify sexe enum(' f ','m') not null ; enum （‘F’ ，M’ ）字段

modify 只是修改字段的数据类型

Chenge： 全部改 名字 类型

语法：

Chenge 要修改的字段名 新的字段名 类型

添加字段：

ALTER TABLE 表名 ADD 字段名 数据类型 [属性]；

添加枚举字段:

Alter table xxx add sex enum(' f ' , ' m ');

添加主键：

ALTER TABLE 表名 ADD CONSTRAINT主键名 PRIMARY KEY 表名（主键字段）；

添加外键：

ALTER TABLE 表名 ADD CONSTRAINT外键名 FOREIGN KEY (外键字段) REFERENCES 关联 表名（关联字段）；

删除字段：

ALTER TABLE 表名 DROP 字段名；

 

查询：HELP 查询内容（一般在DOC命令下使用）

语法：

select * from ；查询所有

或 select 表名 From 表名 ；查询某个， 可加where 条件

使用AS命名列、查询-列别名：

1 SELECT `studentNo` AS 学生编号,`studentName` AS 学生姓名,

`address `AS 学生地址

2 SELECT `firstName` + '.' + `lastName` AS 姓名

FROM `employees`;

注意：

1. + 连接的数据类型必须兼容

2. 如果 + 连接字符型数据，结果为字符串数据的连接

3. 如果 + 连接数值型数据，结果为数值的和

查询空行：

示例：

SELECT `studentName` FROM `student` WHERE `email` IS NULL;

原来有数据，但数据被清除的列如何查询 ：使用常量列

示例：

SELECT `studentName` AS 姓名，`address` AS 地址，

’北京信息中心’ AS 学校名称

FROM `student`;

 

排序：

ORDER BY 排序的列名； ASC 默认正序 DESC 倒序

按设定顺序查询：

SELECT * FROM XXX ORDER BY REGISTER_DATE ;

分组统计：

GROUP BY ：

按照名字进行分组查询 ：

SELECT NAME ,COUNT(*)as lieming FROM XXX GROUP BY NAME;

count( )计数，可以给count as 起个新字段名字

按照日期进行分组查询：

SELECT REGISTER_DATE ,COUNT(*) FROM XXX GROUP BY REGISTER_DATE;

按照名字的 age总和进行分组查询：

SELECT NAME ,SUM (age) FROM XXX GROUB BY NAME ;

sum( 按照什么)求和：

（With rollup 求加起来的总数 ,COALESCE 可以设置一个代替null的名称：）

 

按照名字的 age总和进行分组查询，并求出一个总和，在给加起来的总和起一个名字sum_name，代替null：

SELECT COALESCE (name ,' sum_name ' SUM (age) FROM XXX GROUB BY NAME WITH ROLLUP ;

 

可以使用OFFSET 指定SELECT 语句开始查询的数据偏移量，默认情况下偏移量为0

可以用LIMIT属性 设定返回的记录数:

查前两条数据

SELECT * FROM XXX LIMIT 2；

第一条之后查两条数据，

SELECT * FROM XXX LIMIT 2 OFFSET 1;

(OFFSET不能单独用，要和LIMIT一起用)

加WHERE 条件 查询：

SELECT * FROM XXX WHERE ID>3 and age<20;

按照日期查询:

SELECT * FROM XXX WHERE REGISTER_DATE LIKE '2018-03%' ；

 

 

修改表格本身：

Alter table xxx drop REGISTER_DATE; 修改删除字段

Alter table xxx drop name; 删除name字段

Alter table xxx add phone int(11) not null ;添加字段

Alter table xxx add sex enum(' f ' , ' m '); 添加枚举字段

Alter table xxx modify sexe enum(' f ','m') not null ; 修改数据类型不能为空， enum （‘F’ ，M’ ）字段 modify 只是修改字段的数据类型

Chenge： 全部改 名字 类型

语法：

Chenge 要修改的字段名 新的字段名 类型

 

查看所有的进程：

ps - ef

并筛选出MYSQL：

ps - ef | grep mysql （筛选 mysql）

可看到 mysql进程号

删掉mysql进程：

kill 进程号

如果没有启动：

mysqld_safe& 可以启动

想关闭 mysql服务器：

mysqladmin -u root -p shutdown

添加用户：

use mysql ;进入数据库，

查看已有用户：

select 字段 from 表名；

插入用户：

grant all on test .* to 'xxx'@'%' identified by '密码xxx';

#授权 所有 在 test数据库下 .*所有的表 ‘xxx用户’ @从 %所有机器访问 给它的密码 by ‘设置的密码’

/*

grant 授权

all 所有

on 在哪里

test = 要设置的数据库名

. * 数据库里所有的表

‘ 用户 ’ 添加的用户

@ 从哪访问

% 所有

identified 密码

by'xxx' 设置的密码

*/

常用函数：

函数名	作用
COUNT()	返回某字段的行数
MAX()	返回某字段的最大值
MIN()	返回某字段的最小值
SUM()	返回某字段的和

字符串函数：

函数名	作用	举例
CONCAT(str1, str1...strn)	字符串连接	SELECT CONCAT('My','S','QL'); 返回：MySQL
INSERT(str, pos,len, newstr)	字符串替换	SELECT INSERT( '这是SQL Server数据库', 3,10,'MySQL'); 返回：这是MySQL数据库
LOWER(str)	将字符串转为小写	SELECT LOWER('MySQL'); 返回：mysql
UPPER(str)	将字符串转为大写	SELECT UPPER('MySQL'); 返回：MYSQL
SUBSTRING (str,num,len)	字符串截取	SELECT SUBSTRING( 'JavaMySQLOracle',5,5); 返回：MySQL

数学函数：

CEIL(x)	返回大于或等于数值x的最小整数	SELECT CEIL(2.3)返回：3
FLOOR(x)	返回小于或等于数值x的最大整数	SELECT FLOOR(2.3) 返回：2
RAND()	返回0~1间的随机数	SELECT RAND()返回：0.5525468583708134