vlambda博客
学习文章列表

mysql(一)数据库基础

本期内容大纲:

1.mysql数据类型

2.mysql常用关键字

3.mysql常用函数

4.mysql常用sql

5.福利派送


1.mysql数据类型

mysql的数据类型主要包括以下五大类:

整数类型:BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、BIG INT

浮点数类型:FLOAT、DOUBLE、DECIMAL

字符串类型:CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型:Date、DateTime、TimeStamp、Time、Year

其他数据类型:BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

2.mysql常用关键字

(1)排序 order by

desc为降序,asc为升序,order by 的字段后不加约束则默认升序。如果要按照多个条件排序,则字段间用英文逗号隔开即可,每个字段都需要根据条件来确定加或不加desc,即每个字段后的desc只决定它所跟的那个字段的排序。

示例:select * from 表名 条件语句 order by 字段 desc/asc;

(2)分组 group by

MySQL5.7以上版本中,默认的sql配置模式是ONLY_FULL_GROUP_BY,该配置启用的是“严格ANSIsql”规则,该规则要求在group by的时候,没有聚合的列必须全部包含在group by的字段中。也就是说,在语句执行的时候会先分组,根据查询的字段(select的字段)从分组的内容中取出,所以查询的字段理论上应全部在group by的分组条件内,一种情况例外:查询字段中如果含有聚合函数的字段不用包含在group by中。否则,会报错。

示例:select * from 表名 条件语句 group by 分组字段 条件语句;

(3)去重 destinct

示例:select distinct 选择的字段 from 表名 条件语句;

(4)having 和 where

二者都是在条件从句中使用,区别有以下两点:

位置区别having用在group by之后,where用在group by之前。having引导的条件从句可以让我们筛选成组后的各种数据;where引导的条件从句则在聚合前先筛选记录,作用在group by 和having 之前。

用法区别:聚合函数,即汇总、统计函数,常见的有avg, count, sum, min, max等。having后只能跟聚合函数,where后不能跟聚合函数。

(5)联合查询 union

用于连接两个查询语句,并使结果一同呈现。只要两个结果集的列数相同就可以使用,即使字段类型不同,也可以。使用时,直接用union写在两个查询语句之间即可。

示例:elect语句1 union select语句2;

(6)取指定数量的数据 limit

limit 用于查询指定行数的记录,在limit语法中,m指记录开始的index,从0开始计数;n指从m条开始,取n条。

示例:select * from 表名 条件语句 limit m,n;

(7)主键 primary key

一般用于创建表或修改表时设置主键,若需要设置多个主键,直接在primary key后的括号中增加即可。

示例:alter table 表名 add primary key (主键1, 主键2, 主键3);

(8)外键 foreign key

一般用于创建表或修改表时设置外键。

示例:alter table 表名 add constraint foreign key (外键名) references 外表表名(外表的主键名);

3.mysql常用函数

MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。

数学函数

(1)ABS(x):返回x的绝对值

(2)PI():返回圆周率π,默认显示6位小数

(3)SQRT(x):返回非负数的x的二次方根

(4)MOD(x,y):返回x被y除后的余数

(5)CEIL(x)、CEILING(x):返回不小于x的最小整数

(6)FLOOR(x):返回不大于x的最大整数

(7)ROUND(x)、ROUND(x,y):前者返回最接近于x的整数,即对x进行四舍五入;后者返回最接近x的数,其值保留到小数点后面y位,若y为负值,则将保留到x到小数点左边y位

(8)SIGN(x):返回参数x的符号,-1表示负数,0表示0,1表示正数

(9)POW(x,y)和、POWER(x,y):返回x的y次乘方的值

(10)EXP(x):返回e的x乘方后的值

(11)LOG(x):返回x的自然对数,x相对于基数e的对数

(12)LOG10(x):返回x的基数为10的对数

(13)RADIANS(x):返回x由角度转化为弧度的值

(14)DEGREES(x):返回x由弧度转化为角度的值

(15)SIN(x)、ASIN(x):前者返回x的正弦,其中x为给定的弧度值;后者返回x的反正弦值,x为正弦

(16)COS(x)、ACOS(x):前者返回x的余弦,其中x为给定的弧度值;后者返回x的反余弦值,x为余弦

(17)TAN(x)、ATAN(x):前者返回x的正切,其中x为给定的弧度值;后者返回x的反正切值,x为正切

(18)COT(x):返回给定弧度值x的余切

字符串函数

(1)CHAR_LENGTH(str):计算字符串字符个数

(2)CONCAT(s1,s2,...):返回连接参数产生的字符串,一个或多个待拼接的内容,任意一个为NULL则返回值为NULL

(3)CONCAT_WS(x,s1,s2,...):返回多个字符串拼接之后的字符串,每个字符串之间有一个x

(4)INSERT(s1,x,len,s2):返回字符串s1,其子字符串起始于位置x,被字符串s2取代len个字符

(5)LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str):前两者将str中的字母全部转换成小写,后两者将字符串中的字母全部转换成大写

(6)LEFT(s,n)、RIGHT(s,n):前者返回字符串s从最左边开始的n个字符,后者返回字符串s从最右边开始的n个字符

(7)LPAD(s1,len,s2)、RPAD(s1,len,s2):前者返回s1,其左边由字符串s2填补到len字符长度,假如s1的长度大于len,则返回值被缩短至len字符;前者返回s1,其右边由字符串s2填补到len字符长度,假如s1的长度大于len,则返回值被缩短至len字符

(8)LTRIM(s)、RTRIM(s):前者返回字符串s,其左边所有空格被删除;后者返回字符串s,其右边所有空格被删除

(9)TRIM(s):返回字符串s删除了两边空格之后的字符串

(10)TRIM(s1 FROM s):删除字符串s两端所有子字符串s1,未指定s1的情况下则默认删除空格

(11)REPEAT(s,n):返回一个由重复字符串s组成的字符串,字符串s的数目等于n

(12)SPACE(n):返回一个由n个空格组成的字符串

(13)REPLACE(s,s1,s2):返回一个字符串,用字符串s2替代字符串s中所有的字符串s1

(14)STRCMP(s1,s2):若s1和s2中所有的字符串都相同,则返回0;根据当前分类次序,第一个参数小于第二个则返回-1,其他情况返回1

(15)SUBSTRING(s,n,len)、MID(s,n,len):两个函数作用相同,从字符串s中返回一个第n个字符开始、长度为len的字符串

(16)LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1):三个函数作用相同,返回子字符串str1在字符串str中的开始位置(从第几个字符开始)

(17)REVERSE(s):将字符串s反转

(18)ELT(N,str1,str2,str3,str4,...):返回第N个字符串

日期和时间函数

(1)CURDATE()、CURRENT_DATE():将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回,具体格式根据函数用在字符串或是数字语境中而定

(2)CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE():这四个函数作用相同,返回当前日期和时间值,格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS",具体格式根据函数用在字符串或数字语境中而定

(3)UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date):前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数,后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数

(4)FROM_UNIXTIME(date):和UNIX_TIMESTAMP互为反函数,把UNIX时间戳转换为普通格式的时间

(5)UTC_DATE()和UTC_TIME():前者返回当前UTC(世界标准时间)日期值,其格式为"YYYY-MM-DD"或"YYYYMMDD",后者返回当前UTC时间值,其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中

(6)MONTH(date)和MONTHNAME(date):前者返回指定日期中的月份,后者返回指定日期中的月份的名称

(7)DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d):DAYNAME(d)返回d对应的工作日的英文名称,如Sunday、Monday等;DAYOFWEEK(d)返回的对应一周中的索引,1表示周日、2表示周一;WEEKDAY(d)表示d对应的工作日索引,0表示周一,1表示周二

(8)WEEK(d)、WEEKOFYEAD(d):前者计算日期d是一年中的第几周,后者计算某一天位于一年中的第几周

(9)DAYOFYEAR(d)、DAYOFMONTH(d):前者返回d是一年中的第几天,后者返回d是一月中的第几天

(10)YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time):YEAR(date)返回指定日期对应的年份,范围是1970~2069;QUARTER(date)返回date对应一年中的季度,范围是1~4;MINUTE(time)返回time对应的分钟数,范围是0~59;SECOND(time)返回制定时间的秒值

(11)EXTRACE(type FROM date):从日期中提取一部分,type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

(12)TIME_TO_SEC(time):返回以转换为秒的time参数,转换公式为"3600小时 + 60分钟 + 秒"

(13)SEC_TO_TIME():和TIME_TO_SEC(time)互为反函数,将秒值转换为时间格式

(14)DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type):返回将起始时间加上expr type之后的时间,比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒

(15)DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type):返回将起始时间减去expr type之后的时间

(16)ADDTIME(date,expr)、SUBTIME(date,expr):前者进行date的时间加操作,后者进行date的时间减操作

条件判断函数

(1)IF(expr,v1,v2):如果expr是TRUE则返回v1,否则返回v2

(2)IFNULL(v1,v2):如果v1不为NULL,则返回v1,否则返回v2

(3)CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END:如果expr等于某个vn,则返回对应位置THEN后面的结果,如果与所有值都不想等,则返回ELSE后面的rn

系统信息函数

(1)VERSION():查看MySQL版本号

(2)CONNECTION_ID():查看当前用户的连接数

(3)USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER():查看当前被MySQL服务器验证的用户名和主机的组合,一般这几个函数的返回值是相同的

(4)CHARSET(str):查看字符串str使用的字符集

(5)COLLATION():查看字符串排列方式

加密函数

(1)PASSWORD(str):从原明文密码str计算并返回加密后的字符串密码,注意这个函数的加密是单向的(不可逆),因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用

(2)MD5(str):为字符串算出一个MD5 128比特校验和,改值以32位十六进制数字的二进制字符串形式返回

(3)ENCODE(str, pswd_str):使用pswd_str作为密码,加密str

(4)DECODE(crypt_str,pswd_str):使用pswd_str作为密码,解密加密字符串crypt_str,crypt_str是由ENCODE函数返回的字符串

格式化函数

(1)FORMAT(x,n):将数字x格式化,并以四舍五入的方式保留小数点后n位,结果以字符串形式返回

(2)CONV(N,from_base,to_base):不同进制数之间的转换,返回值为数值N的字符串表示,由from_base进制转换为to_base进制

(5)BENCHMARK(count,expr):重复执行count次表达式expr,它可以用于计算MySQL处理表达式的速度,结果值通常是0(0只是表示很快,并不是没有速度)。另一个作用是用它在MySQL客户端内部报告语句执行的时间

(6)CONVERT(str USING charset):使用字符集charset表示字符串str

4.mysql常用sql

(1)查看有哪些数据库

show databases;

(2)创建数据库

建一个名为Testdb的数据库:create database Testdb;

有些时候,为了防止中文乱码,创建数据库的时候需要指定编码格式:create database mydb character set utf8mb4;

(3)使用某个数据库

使用mydb这个数据库,或者进入mydb这个数据库:use mydb;

(4)删除数据库

删除Testdb这个数据库:drop database Testdb;

(5)查看有哪些数据表

进入某个数据库之后,想查看有哪些数据表:

use mydb;show tables;

(6)创建数据表

建表语句格式为:

create table <tablename>(    

<字段名称> <数据类型>,    

<字段名称> <数据类型>,  

<字段名称> <数据类型>,  

 ......

);

说明:每个字段以逗号分隔,最后一个字段不加逗号。

示例:创建一个字典表

CREATE TABLE `sys_dict`  (
 `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `dict_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '字典名称',
 `dict_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '字典编码',
 `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '描述',
 `del_flag` int(1) DEFAULT NULL COMMENT '删除状态',
 `create_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) DEFAULT NULL COMMENT '更新时间',
  `type` int(1) UNSIGNED ZEROFILL DEFAULT 0 COMMENT '字典类型0为string,1为number',
   PRIMARY KEY (`id`) USING BTREE,
   UNIQUE INDEX `indextable_dict_code`(`dict_code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

(7)查看表结构

查看表结构的命令为:

describe sys_dict;

该命令会显示表stuinfo的基本结构,例如有哪些字段,每个字段是什么类型,谁是主键等。

(8)修改数据表

在指定位置增加一个字段,例如,在某个字段前/后增加一个字段,SQL语句的格式为:

alter table <tablename> add <column> varchar(20) before/after <column>;

修改某个字段的数据类型,SQL语句如下:

alter table <tablename> MODIFY <column> VARCHAR(100);

(9)重命名表

重命名表通过alter+rename来实现,SQL语句格式为:

alter table <tablename> rename <new_tablename> ;

(10)删除数据表

删除数据表通过drop进行(表结构和数据都删除),SQL语句格式为:

drop table <tablename>;

(11)删除数据记录

删除记录通过delete子句进行,SQL语句格式为:

delete from <tablename> where 字段名=某个值;

注意:删除数据前一般先进行备份,另外执行删除数据语句时一定要带上有效条件,防止误删数据。

(12)插入数据记录

插入记录通过insert into进行,SQL语句格式为:

insert into <tablename>(字段1,字段2,......) values(字段1的值,字段2的值,......);

如果需要一次性插入多条记录,SQL语句格式为:

insert into <tablename>(字段1,字段2,......) values

(字段1的值,字段2的值,......),

(字段1的值,字段2的值,......),

(字段1的值,字段2的值,......);

注意:如果设置了主键,插入记录的主键字段是不能重复的,也就是不能插入重复的记录。

(13)查询数据记录

查询记录通过select子句进行,如果想查询符合某个条件的记录,就要用到where子句了,SQL格式为:

select * from <tablename> where 字段名=某个值;

(14)修改数据记录

修改记录通过update子句进行,SQL语句格式为:

update <tablename> set 字段1=某个值 where 字段2=某个值;

5.福利派送

你还在为通货膨胀工资缩水而烦恼吗?

你还在为不知道怎么打理零花钱而着急吗?

你还在为每月月光而焦虑吗?

你还在为不知道怎么管理家庭钱财而迷茫吗?

你还在为不知道怎么理财投资而苦恼吗?

你想获得非工资收入吗?

你想早日实现财务自由吗?

。。。。。。

隆重推荐以下《小白理财训练营》课程,赶快扫码学习,掌握知识和技能后终身受益~

总结:

本期内容主要介绍mysql基础知识,在实际开发中应该要熟练使用。本期内容到此结束,下期见~