mySql学习记录——三、mysql查询语句

一、mysql数据类型

1.数值类型
1.1.MySQL 支持所有标准 SQL 数值数据类型。
1.2.这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数
1.3.据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
1.4.关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
1.5.BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
1.6.作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	用途	大小	范围（有符号）	范围（无符号）
tinyint	小整数值	1Bytes	(-128, 127)	(0, 255)
smallint	大整数值	2Bytes	(-32768, 32767)	(0, 65635)
mediumint	大整数值	3Bytes	(-8 388 608, 8 388 608)	(0, 16 777 215)
int或integer	大整数值	4Bytes	(-2 147 483 648, 2147483648)	(0, 4294967275)
bigint	极大整数值	1Bytes	(-128, 127)	(0, 255)
float	单精度 浮点数值	4Bytes	(-3402823466E+38, -1.175494351E-38) ,0,(1.175494351E-38,3.402823466351E+38)	(0, (1.075494351E-38,3.402823466E+38))
double	双精度 浮点数值	8Bytes	(-1.7976931348623157E+308, -2.2250738585072014E-308),0,(2.2250738585072014E-308),0,(2.2250738585072014E-308,1.797693134868315E+308)	(0, 255)
decimal	小数值	对DECIMAL,如果M>D，为M+2否则D+2	依赖于M和D	依赖于M和D

2.日期和时间类型
2.1.表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
2.2.每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
2.3.TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	用途	大小	范围	格式
date	日期值	3Bytes	1000-01-01/9999-12-31	YYYY-MM-DD
time	时间值或持续时间	3Bytes	‘-838:5959’/‘838:59:59’	HH:MM:SS
year	年份值	1Bytes	1901/2155	YYYY
datetime	混合日期和时间值	8Bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH;MM;SS
timestamp	混合日期和时间值，时间戳	4Bytes	1970-01-01 00:00:00/2038	YYYYMMDD HHMMSS

3.字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	用途	大小
char	定长字符串	0-255 Bytes
varchar	变长字符串	0-65535 Bytes
tinyblob	不超过255个字符的二进制字符串	0-255 Bytes
tinytext	短文本字符串	0-255 Bytes
blob	二进制形式的长文本数据	0-65535 Bytes
text	长文本数据	0-65353 Bytes
mediumblob	二进制形式的中等长度文本数据	0-16777215 Bytes
mediumtext	中等长度文本数据	0-16777215 Bytes
longblob	二进制形式的极大文本数据	0-4294967295 Bytes
longtext	极大文本数据	0-4294967295 Bytes

二、 mysql查询操作

1.可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
2.使用 AND 或者 OR 指定一个或多个条件。
3.WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
4.WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

搜索方法

select * from 表 where 条件

//搜索
SELECT * FROM `user` WHERE real_name = ''
//模糊搜索
SELECT * FROM `user` WHERE real_name LIKE '%杨%'
//多个要求
SELECT * FROM 表名 WHERE 参数 = '' AND 参数 = ''
//选表查询
SELECT * FROM 库名.表名 WHERE 参数 = '安徽'

多表联查

select 输出内容
from 表 a表别名（最好简单点）
left join 表 b表别名（最好简单点） on a.xx(表参数) = b.xx(表别名)
(分组输出)group by a表别名,xx

//多表查询（左、右、内连接（映射关系））
SELECT
 c.xx AS '',
 count(p.xx) AS ''
FROM
 表名 c
LEFT JOIN 表名 p ON c.xx = p.xx
LEFT JOIN  表名 t on p.xx= t.xx
WHERE
c.xx= 1 
and c.add_time BETWEEN '2022-04-01 00:00:00' and '2022-05-24 00:00:00'
AND p.xx = 1 and t.xx = 1
GROUP BY c.xx

细节

a、连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid
b、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

查询添加参数
as：取别名 select x.id as ’名称‘ where …
group by(必须在where之后，order by之前)：分组，存在映射关系，分（列）组展示某参数
limit/limit X offset X：限制，找出之间的信息 (select * from 表 limit 5;)
between：找出之间的目标（select * from 表名 where c.时间 BETWEEN ‘2022-04-01 00:00:00’ and ‘2022-05-24 00:00:00’）
in：查询某行 例子：select * from 表 where id in (11,22,33)
on：配合左链、右链、内链接使用，同上（多表查询）
like(%/_前后皆可以)：模糊查询 （select * from 表 where name like ‘ale%’）%多个字符串, （select * from 表 where name like ‘ale_’）_单个字符串
聚集函数查询
COUNT：返回指定列不为空的记录总数
SUM:：对指定的列进行求和计算(例如工资发放等)
MIN, MAX：MIN(e1)返回e1表达式指定的列中最小值； MAX(e1)返回e1表达式指定的列中最大值；
desc/desc：(select * from 表 order by 列1 desc,列2 asc) - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
AVG： 对表达式指定的列，求平均值。
MEDIAN：首先，根据e1表达式指定的列，对值进行排序； 若排序后，总记录为奇数，则返回排序队列中，位于中间的值； 若排序后，总记录为偶数，则对位于排序队列中，中间两个值进行求平均，返回这个平均值；
***RANK(RANK( ) OVER ([ PARTITION BY column1 ] ORDER BY column2 [ASC|DESC]))***😓(找出某参数的序号)：
（select rank(1500) within group (order by salary) as “rank number” from employees）
FIRST、LAST：

语法：    agg_function（e1） KEEP (DENSE_RANK FIRST ORDER BY e2 [NULLS {FIRST|LAST}]) [OVER PARTITION BY e3 ]
            agg_function（e1） KEEP (DENSE_RANK LAST  ORDER BY e2 [NULLS {FIRST|LAST}]) [OVER PARTITION BY e3 ]                  
        参数：    agg_function为一个聚合函数，可以为 MIN、MAX、SUM、AVG、COUNT、VARIANCE或STDDEV
            e2指定以哪个字段为依据，进行排序；
            e3指定以哪个字段为依据，进行分类（分组）；
            当指定OVER PARTITION BY子句后，针对分类后的每个类单独排序；
            DENSE_RANK为排序后的记录分配序号，并且序号为连续的。
            NULLS {FIRST|LAST}指定排序字段e1的值若为空，则拍在序列前边（NULLS FIRST）或者后边（NULLS LAST）
            DENSE_RANK后的FIRST/LAST确定选取通过DENSE_RANK排好序后的序列中，序号最小/最大的记录。序号相同时，返回多条记录
            当序号相同，返回多条记录时，agg_function(e1)聚合函数继续对这多条记录的e1字段做聚合操作。
        作用：    如果agg_function为min(e1)，获取排序后的FIRST或LAST的多条记录中，某字段e1的最小值
            该字段不是排序关键字段e2
        实例：
        已知员工表有薪水字段，奖金字段。要求获取薪水最低的员工中，奖金最高的员工的记录。
        已知表内容如下：
        SQL> select * from employees order by salary;
         EMP_ID     EMP_NAME           SALARY  COMMISSION
        ---------- ---------------------------- ------------  ------------
        10001      ZhangSan              500        200
        10002      LiSi                  500        300
        10003      WangWu                500        100
        10004      MaLiu                 2000       500
        10005      NiuQi                 2500       200
        10006      ShangDuo              2500       300
        10007      BaiQi                 2500       400
        
        SQL> select max(commission) keep(dense_rank first order by salary asc) as commission from employees;
        COMMISSION
        ----------
               300
        首先，按salary排序后，获取薪水最低的记录，分别为员工10001、10002、10003三条记录。
        聚合函数max(commission)对3条记录获取奖金最高的为员工10002，奖金为300。

三、 mysql插入操作

MySQL 表中使用 INSERT INTO SQL语句来插入数据。
插入方法

INSERT INTO user (user_name,user_pwd,user_phone,user_time) VALUES (?, ?, ?, ?)

INSERT INTO `user` (user_name,user_pwd,user_phone,user_time) VALUES (?, ?, ?, ?)

四、 mysql更新操作

update 表 set name ＝ ‘alex’ where id>1

update 表 set name ＝ 'alex' where id>1

五、 mysql删除操作

delete from 表 where id＝1 and name＝’alex’

delete from 表
delete from 表 where id＝1 and name＝'alex'

//欢迎补充