数据库基本概念&MySQL基本操作详解

时间：2023-01-01 17:07:46

本人小白，大部份内容来自于网络或书本，再加一点自己的见解总结，在此向各位巨人致敬

数据

数据的定义：
1. 数据是对客观事实的符号表示
2. 在计算机领域数据通常是指由能被计算机识别与处理的数值、字符等符号构成的集合

数据库管理系统（DBMS）的分类

  1. 关系型数据库：支持关系模型，其关系模型由关系数据结构、关系操作集合、完整性约束三部分组成。常见的有oracle，mysql，SQLserver等
  2. 非关系型数据库：常见的有redis，MongoDB等
  3. 两者的区别：

关系型数据的运算

笛卡尔积：笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员
数据库基本概念&MySQL基本操作详解

  1. 选择（where）：选择是对关系的水平分解运算，通俗一点就是提取行
    σf（R）={t|t∈R∧F（t）=TRUE}
    解释：选择t，t属于R且F(t)为真。（即在R中选取满足f条件的元组）
    select * from R where f(t)==true;
  2. 投影（select）：投影是对关系的垂直分解运算，即选择关系集中的属性子集，通俗一点就是提取列。
    πA（R） = { t[A] | t∈R }
    select A from R

  3. 连接：连接也称为θ连接，它是从两个关系的笛卡儿积中选取属性间满足一定条件的元组。

    1. 等值连接和自然连接（元组行参照坐标）
      • 等值连接：广义上它是从笛卡尔集中选取R1中某指定列和R2中某指定列相等的元组。
        select * from R1,R2 where R1.A=R2.B;
      • 自然连接：特殊的等值连接，要求用于连接的属性相同。
        select * from R1,R2 where R1.A=R2.A;
    2. 外链接
        -

数据库的组成

  1. 数据库系统
  2. 数据库
  3. 数据表
  4. 数据表字段
  5. 数据

mysql安装

参见，免安装版MySQL相关配置

SQL（结构化查询语言）

SQL分类：DDL数据定义语言，DML数据操作语言，DQL数据查询语言，DCL数据控制语言

DDL数据定义语言：用来操作数据库和数据库对象

1. show databases; :查看数据库系统里的所在数据库

2. create database 数据库名； ：建立一个数据库

3. use 数据库名； ：使用一个数据库

4. show tables； ：查看数据库里面的所有数据表

5. create table 表名（字段 字段类型，。。。） ：创建表

6. drop database 数据库名；：删除数据库

7. drop table 表名； ：删除数据库表

8. desc 表名；：查看表结构字段

9. alter table 表名 modify 字段名 字段类型；： 修改表字段类型

10. alter table 表名 change 原字段名 修改字段名 字段类型；：修改表字段

11. alter table 表名 add 字段名 类型 after 字段；：添加字段到自定字段后

12. alter table 表名 rename 新表名； ：修改表名

DML数据操作语言：用来修改表中的数据

1. insert into 表名 values（对应值），（值）；：插入一条或多条数据

2. insert into 表名（字段1，字段2，。。。）values（值1，值2，。。。）；：插入指定字段数据到表中

3. delete from 表名 where 字段=值；：从表中删除一条数据

4. update 表名 set 字段1=值1，字段2=值2，。。。where 字段=值；：修改

DQL数据查询语言

1. select * from 表名；：查询表里所有数据

2. select 字段1，字段2，。。。 from 表名； ：查询指定字段

3. where条件查询：
    1. 关系型查询：<> <= >= != 
    2. 逻辑 ：or and
    3. 区间： in() ,between a and b
    4. 模糊： like '%条件%'；

4. 规则：
    1. group by 字段
    2. order by 字段  ASC：升序（默认），DESC：降序。
    3. limit 0，5

DCL数据控制语句：用来定义访问权限和安全级别

1. 创建用户：create user '用户名'@'主机名' identified by '密码';

2.  授权：grant all on 数据名.表名 to '用户名'@'主机名';

3. 取消权限：revoke all on *.* from '用户名'@'主机名';

4. 删除用户：drop user '用户名'@'主机名';

5. 查看用户的权限：show grants [for '用户名'@'主机名']  
*注：如果for后面的内容不写，默认查看当前用户，写的话查看指定用户的权限

> 说明：
        all：所有的权限
        *：代表所有的库或表
        %：模糊查询

TCL事务控制语句：用于维护数据的一致性

1. set autocommit=0;/begin;     开启事务
2. rollback;          回滚
3. commit;       手动提交事务

数据类型

number类型：

类型 字节数 范围（有符号） 无符号 描述
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的值 小数值


字符型：

类型 大小 描述
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字节 极大文本数据


DATA类型：

类型 大小 范围 格式 描述
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 混合日期和时间值，时间戳

约束条件&关键字

名称 描述
PRIMARY KEY (主键)
NOT NULL (非空)
AUTO_INCREMENT (自增长)
UNIQUE (值唯一)
FOREING KEY (外键)
DEFAULT (默认值)
zerofill 使用0去填充（如int(4),1的表示0001）
unsigned 无符号（限定大于零）
comment 解释说明（类似于注释）


案例：

CREATE TABLE yuesu(
 id int(4) PRIMARY KEY AUTO_INCREMENT,#主键约束，设置自增
 name VARCHAR(8) NOT NULL,#不为空
 shoolId int(4) UNIQUE ,#设置唯一约束
 bronData DATE DEFAULT '1990-01-01',#设置默认值
 card INT(11),
CONSTRAINT key_1 FOREIGN KEY (card) REFERENCES te(id)#设置外键约束
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000;#设置引擎，默认字符集，还可以给自增赋初始值

搜索引擎

  1. InnoDB：支持事务，采用的是行锁，对于数据的增删改操作效率高，包括了对事务处理和外来键的支持

  2. MyISAM：不支持事务，采用的是表锁，对于数据的查询效率高，常用于查询操作比较多的web项目

  3. 两者的区别：

    1. MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，常被认为用于小型项目

    2. InnoDB提供事务支持事务，外部键等高级数据库功能

  4. 对引擎的操作：

    1. show engines; 查看数据库系统支持的存储引擎

    2. show variables like ‘storage_engine%’; 查看当前系统默认的存储引擎

    3. 修改默认引擎-my.ini

    4. 设置表引擎参看上一案例

函数

数学函数：

函数 描述
ABS(x) 返回x的绝对值
BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
CEILING(x) 返回大于x的最小整数值
EXP(x) 返回值e（自然对数的底）的x次方
FLOOR(x) 返回小于x的最大整数值
GREATEST(x1,x2,…,xn) 返回集合中最大的值
LEAST(x1,x2,…,xn) 返回集合中最小的值
LN(x) 返回x的自然对数
LOG(x,y) 返回x的以y为底的对数
MOD(x,y) 返回x/y的模（余数）
PI() 返回pi的值（圆周率）
RAND() 返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
SIGN(x) 返回代表数字x的符号的值
SQRT(x) 返回一个数的平方根
TRUNCATE(x,y) 返回数字x截短为y位小数的结果

聚合函数（常用于统计一个列，常通过group by）

函数名 描述
AVG(col) 返回指定列的平均值
COUNT(col) 返回指定列中非NULL值的个数
MIN(col) 返回指定列的最小值
MAX(col) 返回指定列的最大值
SUM(col) 返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

字符串函数

函数名 描述
ASCII(char) 返回字符的ASCII码值
BIT_LENGTH(str) 返回字符串的比特长度
CONCAT(s1,s2…,sn) 将s1,s2…,sn连接成字符串
CONCAT_WS(sep,s1,s2…,sn) 将s1,s2…,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x) 返回字符串str中最左边的x个字符
LENGTH(s) 返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr) 返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2) 比较字符串s1和s2
TRIM(str) 去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

DATA函数

函数名 描述
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DATE_ADD(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)

加密函数

函数名 描述
AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,key) 使用key作为密钥解密加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
MD5() 计算字符串str的MD5校验和
PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
SHA() 计算字符串str的安全散列算法(SHA)校验和
标签：

相关文章