Mysql数据库基础知识（1）

Mysql数据库基础知识（1）

包括（数据库相关概念，SQL语句，SQL通用语法，数据类型，函数等知识。）

一.数据库相关概念

1.基础知识

·数据库 存储数据的仓库，数据是有组织的进行存储 DataBase(DB)

·数据库管理系统 操纵和管理数据库的大型软件DataBase ManagementSystem(DBMS)

·SOL 操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准Structured Query Language(SOL)

2.关系型数据库(RDBMS)

概念:建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点:

·1.使用表存储数据，格式统一，便于维护

·2.使用SQL语言操作，标准统一，使用方便

二. SQL

1.SQL通用语法

·1.SQL语句可以单行或多行书写，以分号结尾。

·2.SQL语句可以使用空格/缩进来增强语句的可读性。

·3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写，

·4. 注释:

·单行注释:--注释内容 或#注释内容(MySQL特有)

·多行注释: /* 注释内容 */

2.数据类型

·整型

MySQL数据类型 含义（有符号）

tinyint(m) 1个字节 范围(-128~127)

smallint(m) 2个字节 范围(-32768~32767)

mediumint(m) 3个字节 范围(-8388608~8388607)

int(m) 4个字节 范围(-2147483648~2147483647)

bigint(m) 8个字节 范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

·浮点型(float和double)

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位

double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

·定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

·字符串(char,varchar,_text)

char(n) 固定长度，最多255个字符

varchar(n) 可变长度，最多65535个字符

（char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，而varchar有几个放几个，若存入字符数小于n效率更高）

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

·二进制数据(blob)

tinyblob 可变长度，最多255个字符

blob可变长度，最多65535个字符

mediumblob 可变长度，最多2的24次方-1个字符

longblob 可变长度，最多2的32次方-1个字符

·BLOB和text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。

·_BLOB存储的数据只能整体读出。

·_TEXT可以指定字符集，_BLO不用指定字符集。

·日期时间类型

date 日期 '2008-12-2'

time 时间 '12:25:36'

datetime 日期时间 '2008-12-2 22:06:44'

year  年‘2024‘

timestamp  混合日期和时间值，时间戳    '2008-12-2 22:06:44'（注意它的范围：1970-01-01 00:00:01至2038-01-19 03:14:07）

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

3. SQL分类

·DDL Data Definition Language 数据定义语言，用来定义数据库对象(数据库，表，字段)

·DML Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改

·DQL Data Query Language 数据查询语言，用来查询数据库中表的记录

·DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

4. DDL语句

（1）DDL-数据库操作

·查询

·查询所有数据库SHOW DATABASES ;

·查询当前数据库SELECT DATABASE() ;

·创建

CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];

·排序规则命名约定：以其相关的字符集名开始，中间包括一个语言名，

并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。

例如，系统使用utf8字符集，若使用utf8_bin校对规则执行SQL查询时区分大小写，

使用utf8_general_ci不区分大小写(默认的utf8字符集对应的校对规则是utf8_general_ci

·删除

DROP DATABASE「IF EXISTS]数据库名；

·使用

USE 数据库名;

（2）DDL-表操作-查询

·查询当前数据库所有表 ：SHOW TABLES

·查询表结构 ：DESC 表名;

·查询指定表的建表语句 ：SHOW CREATE TABLE 表名,

（3）DDL-表操作-创建

CREATE TABLE 表名(

字段1字段1类型[COMMENT 字段1注释]，（注释用单引号括起来）

字段2 字段2类型[COMMENT 字段2注释]，

字段3 字段3类型[COMMENT 字段3注释]，

···

字段n 字段n类型[COMMENT 字段n注释]

)[COMMENT 表注释];

注意:[..]为可选参数，最后一个字段后面没有逗号

（4）DDL-表操作-修改

·修改数据类型 ：ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

·修改字段名和字段类型 ：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];

·删除字段 ：ALTER TABLE 表名 DROP 字段名,

·修改表名 ：ALTER TABLE 表名 RENAME TO 新表名

（5）DDL-表操作-删除

·删除表 ：DROP TABLE[IF EXISTS]表名

·删除指定表，并重新创建该表 ：TRUNCATE TABLE 表名

注意:在删除表时，表中的全部数据也会被删除.

5. DML语句

（1）DML-介绍

·DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

·添加数据(INSERT)

·修改数据(UPDATE

·删除数据(DELETE)

（2）DML-添加数据

·给指定字段添加数据 ：INSERT INTO 表名 (字段名1,字段名2,..) VALUES (值1,值2,….);

·给全部字段添加数据 ：INSERT INTO 表名 VALUES (值1,值2, ….);

·批量添加数据：

··INSERT INTO 表名 (字段名1,字段名2,..) VALUES (值1,值2,..),(值1,值2,.),(值1,值2,.);

··INSERT INTO 表名 VALUES (值1,值2,….),(值1,值2,..),(值1,值2,..);

注意:

·插入数据时，指定的字段顺序需要与值的顺序是一一对应的

·字符串和日期型数据应该包含在引号中。

·插入的数据大小，应该在字段的规定范围内。

（3）DML-修改数据

·UPDATE 表名 SET 字段名1=值1,字段名2=值2,…[WHERE 条件];

·注意:修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

（4）DML-删除数据

·DELETE FROM 表名 [WHERE 条件]

·注意:

·· DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

··DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。

6. DQL语句

（1）DQL-介绍：

DQL英文全称是Data QueryLanguage(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

·查询关键字:SELECT

（2）DQL-基本查询

·查询多个字段

··SELECT 字段1,字段2,字段3.. FROM 表名;

··SELECT * FROM 表名:

·设置别名 ：SELECT 字段1 [AS 别名1],字段2[AS 别名2]..FROM 表名；

·去除重复记录 ：SELECT DISTINCT 字段列表 FROM 表名；

（3）DOL-条件查询

·语法 ：SELECT 字段列表 FROM 表名 WHERE 条件列表；

·条件：

··>,>=,<,<=,=

··>或!=   不等于

··BETWEEN ... AND ...   在某个范围之内(含最小、最大值)

··IN(...) 在in之后的列表中的值，多选一

··LIKE 占位符    模糊匹配( _ 匹配单个字符, % 匹配任意个字符)

··IS NULL 是NULL

··AND或&&    并且(多个条件同时成立)

··OR 或|| 或者(多个条件任意一个成立)

··NOT 或! 非，不是

（4）DQL-聚合函数

·介绍：将一列数据作为一个整体，进行纵向计算。

·常见聚合函数

··count统计数量

··max最大值

··min最小值

··avg平均值

··sum求和

·语法 ：SELECT 聚合函数(字段列表 )FROM 表名

如：select count(*)from emp;

注意:null值不参与所有聚合函数运算。

（5）DQL-分组查询

·语法 ：SELECT 字段列表 FROM 表名[WHERE 条件]GROUP BY 分组字段名[HAVING 分组后过滤条件];

·where与having区别

··执行时机不同:whre是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。

··判断条件不同:where不能对聚合函数进行判断，而having可以。

    ·注意

··执行顺序: where >聚合函数>having。

··分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

（6）DOL-排序查询

·语法 ：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

·排序方式

··ASC:升序(默认值)

··DESC:降序

··注意:如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

（7）DQL-分页查询

·语法 ：SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

·注意：

··起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数，

··分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

··如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

（8）顺序：

·编写顺序：

SELECT字段列表

FROM表名列表

WHERE条件列表

GROUP BY分组字段列表

HAVING分组后条件列表

ORDER BY排序字段列表

LIMIT分页参数

·执行顺序：

FROM表名列表

WHERE条件列表

GROUP BY分组字段列表

HAVING分组后条件列表

SELECT字段列表（聚合函数）

ORDER BY排序字段列表

LIMIT分页参数

7. DCL语句

（1）DCL-介绍

DCL英文全称是Data ControlLanguage(数据控制语言)，用来管理数据库用户、控制数据库的访问 权限

（2）DCL-管理用户

·查询用户：

··USE mysql;

··SELECT * FROM user;

·创建用户 ：CREATE USER'用户名'@'主机名’IDENTIFIED BY'密码”

·修改用户密码：ALTER USER'用户名'@'主机名’IDENTIFIED WITH mysqlnative_password BY '新密码’;

·删除用户 ：DROP USER'用户名'@'主机名'；

·注意:

··主机名可以使用 % 通配，

··这类SQL开发人员操作的比较少，主要是DBA(Database Administrator

数据库管理员)使用

（3）DCL-权限控制

·MySQL中定义了很多种权限，但是常用的就以下几种:

ALL, ALL PRIVILEGES 所有权限

SELECT 查询数据

INSERT 插入数据

UPDATE 修改数据

DELETE 删除数据

ALTER 修改表

DROP 删除数据库/表/视图

CREATE 创建数据库/表

(4)DCL-权限控制

·查询权限 :SHOW GRANTS FOR ‘用户名'@'主机名

·授予权限 ：GRANT 权限列表 ON 数据库名.表名 TO'用户名'@'主机名’;

·撤销权限 ：REVOKE 权限列表 ON 数据库名.表名 FROM'用户名'@'主机名’

·注意:

··多个权限之间，使用逗号分隔

··授权时，数据库名和表名可以使用*进行通配，代表所有

三.函数

1.函数 是指一段可以直接被另一段程序调用的程序或代码。

2. 字符串函数

·CONCAT(S1,S2....Sn) 字符串拼接，将S1，S2，...Sn拼接成一个字符串

·LOWER(str) 将字符串str全部转为小写

·UPPER(Str) 将字符串str全部转为大写

·LPAD(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度

·RPAD(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

·TRIM(str) 去掉字符串头部和尾部的空格

·SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串(索引值从1开始)

·格式：SELECT函数(参数);

3. 数值函数

·CEIL(x) 向上取整

·FLOOR(x) 向下取整

·MOD(x,y) 返回x/y的模

·RAND() 返回0~1内的随机数

·ROUND(x,y) 求参数x的四舍五入的值，保留y位小数

·格式：SELECT函数(参数);

4. 日期函数

·CURDATE() 返回当前日期

·CURTIME() 返回当前时间

·NOW() 返回当前日期和时间

·YEAR(date) 获取指定date的年份

·MONTH(date) 获取指定date的月份

·DAY(date) 获取指定date的日期      如：select DAY(now());

·DATE ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值 如：select date_add(now(),INTERVAL 7O MONTH );

·DATEDIFF(date1,date2) 返回起始时间date1 和 结束时间date2之间的天数 如：select datediff('2021-10-01','2021-12-01');

5. 流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

·IF(value ,t,f) 如果value为true，则返回t，否则返回f

·IFNULL(value1,value2) 如果value1不为空，返回value1，否则返回value2

·CASE WHEN [val1 ] THEN [res1]... ELSE[ default] END 如果val1为true，返回res1，…否则返回default默认值

·CASE [ expr ]WHEN [val1 ]THEN [res1] ... ELSE[ default] END如果expr的值等于val1，返回res1，…否则返回default默认值（when和then可以写多个，不用隔开，如：case workaddress when'北京'then'一线城市'when'上海'then'一线城市'else'二线城市'end）