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)