MySQL学习总结

比较好用的两个链接

1.MySQL常用语句 - CSDN博客 https://blog.csdn.net/zsx157326/article/details/77802217

2.Mysql常用技巧总结 - CSDN博客 https://blog.csdn.net/z369702770/article/details/70236782

 

MySQL常用语句

首先需要了解两个概念：字符集和校验规则

create_specification: [default] character set charset_name //指定字符集 |[default] collate collation_name //指定数据库字符集的比较方式（校验规则）

 

字符集：就是一套表示字符的符号和这些符号的底层编码

校验规则：是在字符集内用于比较字符的一套规则，譬如，我们要比较A和a的大小，传统的ASCII码的底层编码方式来校验的话就是a>A,,假设我们有另外一种校验规则，是先取其相反数，然后再比较大小，那么显然的得到a<A

 

最后：一种字符集可能有多种校验方式

常用语句：

第一部分 操作数据库

创建数据库    creat database mydb1;

创建数据库并设置编码格式是gbk.如果不指定的话就是默认utf-8，这个在安装的时候设置了 creat database mydb2 character set gbk;

设置校验规则 create database mydb3 character set gbk collate gbk_chinese_ci;

查看当前数据库服务器中的所有数据库 show database;

查看前面创建的mydb2数据库的定义信息show create database mydb2;

删除前面创建的mydb3数据 drop database mydb3;

把mydb2的字符集修改为utf-8  alter database mydb2 set utf-8;

查看当前使用的数据库 select database();

切换数据库 use mydb2;

注释：敲完两个短线之后要敲一个空格 select *from student; -- 注释

• 操作数据库表

常用数据类型

整型 int  ：4个字节

浮点型 double :例如double(5,2)表示最多5位，其中两位为小数，即最大值为999.99

可变长度字符串类型 varchar :  varchar(10)’aaa’占3位

日期时间类型 datetime : yyyy-MM-dd hh:mm:ss

固定长度字符串类型 char(10) ‘aaa’占10位

大文本字符串类型 text

字节类型 blob

日期类型 date 格式为yyyy-MM-dd

时间类型 time 格式为 hh:mm:ss

时间戳类型 timestamp  格式为：  yyyy-MM-dd hh:mm:ss  会自动赋值

注意

字符串类型和日期类型必须用单引号括起来

创建表   creat table 表名(

            字段名 类型（长度） 【约束】

             字段名 类型（长度） 【约束】

);

关于约束

主键约束 primary key, 要求被修饰的字段：唯一或非空

唯一约束 unique，要求被修饰的字段：唯一

非空约束 not null  要求被修饰的字段：非空

查看表

查看数据库中的所有表： show tables;

查看表结构： desc 表名;

查看表的创建细节： show create table 表名;

删除表 drop table 表名;

修改表

添加列  alter table 表名 add 列名 类型（长度）【约束】;

修改列的类型长度及约束  alter table 表名 modify 列名 类型（长度）【约束】

修改表列名  alter table 表名 change 旧列名 新列名 类型（长度）【约束】

删除列 alter table 表名 drop 列名

修改表的字符集 alter table 表名 character set 字符集；

修改表名 rename table 表名 to 新表名;

 

对表的增删改

插入记录：insert

insert into 表(列名1，列名2，列名3..) values(值1，值2，值3..);向表中插入某些列insert into 表 values(值1，值2，值3..); --向表中插入所有列

列名与列值的类型，个数，顺序要一一对应。可以把列名当做Java中的形参，把列值当做实参。

值不要超出列定义的长度。值如果是字符串或者日期需要加单引号

更新记录：update

update 表名 set 字段名=值,字段名=值...; --这个会修改所有的数据，把一列的值都变了 update 表名 set 字段名=值,字段名=值... where 条件;

删除记录：delete

delete from 表名 --删除表中所有记录

delete from 表名 where 条件

truncate table 表名;

delete删除表中的数据，是一条一条删除，不清空auto_increment记录数；删除后的数据如果在一个事务中还可以找回。

truncate删除是把表直接drop掉，重新建表，auto_increment将置为零。删除的数据不能找回。执行速度比delete快。

 

第三部分 数据查询语言DQL

简单查询

查询所有列select * from stu;

查询指定列select sid,sname,age from stu;

 

去掉重复值

用来去除重复数据，是对整个结果集(结果集就是查出来的那些数据)进行数据重复抑制的，

而不是针对某一列。select distinct Department,SubCompany from Employee;

 

计算字段

字段间计算select age*salary,name from employee;

运算查询select pname,price+10 from product;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL

 select *,sal+ifnull(comm,0) from emp;

排序

select * from stu order by age asc; -- 升序排序，也可以不加asc，默认为升序

select * from stu order by age desc; --降序

select * from emp order by sal desc,empno asc;-- 按月薪降序排序，如果月薪相同时，按编号升序排序。只有在前一个条件相同时，后一个条件才会起作用。

聚合函数

聚合函数是用来做纵向运算的函数

count():统计指定列不为null的记录行数

max():计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算

min():计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算

sum():计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0。求和的时候忽略null，如果都是null，则算出来的结果为null。

avg():计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0.

分组查询

当需要分组查询时需要使用group by子句，例如查询每个部分的工资和，就需要使用部门来分组。

注：凡是和聚合函数同时出现的列名，则一定要写在group by之后

having子句

where是在分组前对数据进行过滤，having是在分组后对数据进行过滤。

having后面可以使用聚合函数，where不可以使用聚合函数。

实例：

select deptno,count(*) from emp where sal>1500 group by deptno;

select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;

 

查询语句的执行顺序

查询语句书写顺序：select-from-where-group by-having-order by-limit

查询语句执行顺序：from-where-group by-having-select-order by-limit

from决定从哪儿获取数据，where，group by，having决定决定显示那几行，select决定显示的列，order by对列进行排序，limit觉得获取哪些数据。

 

 

字符串相关操作

1、从左开始截取字符串

left（str, length）

说明：left（被截取字段，截取长度）

例：select left（content,200）as abstract from my_content_t

2、从右开始截取字符串

right（str, length）

说明：right（被截取字段，截取长度）

例：select right（content,200）as abstract from my_content_t

3、截取字符串

substring（str, pos）

substring（str, pos, length）

说明：substring（被截取字段，从第几位开始截取）

substring（被截取字段，从第几位开始截取，截取长度）

例：select substring（content,5）as abstract from my_content_t

select substring（content,5,200）as abstract from my_content_t

（注：如果位数是负数 如-5则是从后倒数位数，到字符串结束或截取的长度）

4、按关键字截取字符串

substring_index（str,delim,count）

说明：substring_index（被截取字段，关键字，关键字出现的次数）

例：select substring_index（"blog.csdn.net"，"."，2）as abstract from my_content_t

结果：blog.csdn

（注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束）

函数简介：

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

不带有len 参数的格式从字符串str返回一个子字符串，起始于位置pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置pos。 使用FROM的格式为标准SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos字符，而不是字符串的开头位置。在以下格式的函数中可以对pos使用一个负值。

 

MySQL字符串大小写转换

 

deMySQL 字符串大小写转化函数有两对： lower(), uppper()和 lcase(), ucase()

 

mysql>select lower('DDD');

结果：ddd 

mysql> select upper('ddd');  

结果：DDD

mysql> select lcase('DDD');  

结果：ddd 

mysql> select ucase('ddd');

结果：DDD

通常情况下，我选择 lower(), upper() 来转换字符串大小写，因为这和其他数据库中函数相兼容。