环境Mysql
1.关于日期
# 查询当前日期
select curdate(); 2020-02-09
# 获取日期里的年、月、日
select YEAR('2020-02-09'); 2020
select MONTH('2020-02-09'); 2
select DAY('2020-02-09'); 9
# 计算两个日期的差别
select username,created,CURDATE(), TIMESTAMPDIFF(YEAR,created,CURDATE()) AS age from tb_user;TIMESTAMPDIFF(YEAR, 日期1 , 日期2 ) 计算这两个日期差几年
TIMESTAMPDIFF(MONTH, 日期1 ,日期2 ) 计算这两个日期差几月
TIMESTAMPDIFF(DAY, 日期1 , 日期2 ) 计算这两个日期差几天
2. 几个关键字命令
# null 和 not null
作为查询条件
select * from tb_user where salt is null;
select * from tb_user where salt is not null;# in 和 not in
in是查询出集合里有的数据
not in 是查询出所有不在集合里的数据
# exists 和 not exists
select * from A where exists(select a_id from B)
查询出子查询里有的记录
exists的效率比in高
# union 和 union all
这两个关键字是把两个查询合起来,查询的列要相同
它俩的区别是,union会把两个结果集去重,union不会去重
# case when语句
SELECT *, CASE
WHEN price > 200000 THEN
"高档产品"
WHEN price < 100000 THEN
"低档产品"
WHEN price > 100000
AND price < 200000 THEN
"中档产品"
END AS LEVEL
FROM
tb_sku# 模糊查询 like
select * from a where name like '%abc%' 包含abc
select * from a where name like '%abc' 以abc结尾
select * from a where name like 'abc%' 以abc开头
3.统计相关
#最大值、最小值
select MAX(id) as maxid from tb_user; #找出某一列的最大最小值
select MIN(id) as maxid from tb_user;
#找出id为最小值的整条记录
方法1 select * from tb_user where id=(select MIN(id) as maxid from tb_user)
方法2 select * from tb_user ORDER BY id asc LIMIT 1
方法3 select s1.* from tb_user s1 left join tb_user s2 on s1.id > s2.id where s2.username is null #自己关联自己
#求出某一列的最大值,并根据某个字段进行分组
select spu_id,MAX(price) as price from tb_sku GROUP BY spu_id
#count函数
count的列如果有null值,是不会统计在内的
#sum函数
求某一列的合
select SUM(price) from tb_sku
#ifnull函数、numeric函数
#一个完整的统计查询,注意关键词使用的顺序
SELECT SUM(price)
FROM tb_sku
WHERE id <> 0 #<>就是不等于的意思
GROUP BY spu_id
HAVING SUM(price) > 300000 #having就是sum的条件
ORDER BY SUM(price) DESC
4.多表查询
#笛卡尔积
select * from tb_user,tb_category;
笛卡尔积就是,表1里每个元素对应表2里每个元素,出来的数据。 表1 * 表2。 结果数量==表1数量*表2数量。
连接查询就是把笛卡尔积进行再过滤。
#内连接
select * from tb_spu a INNER JOIN tb_sku b ON a.id = b.spu_id
查询出符合ON条件的数据。
#左外连接
select * from tb_sku a LEFT JOIN tb_spu b ON a.spu_id = b.id
查询出符合ON条件的数据和左表全部数据,对应不上的用null填充。
#右外连接
select * from tb_spu a RIGHT JOIN tb_sku b ON a.id = b.spu_id
和左外连接差不多
#全外连接
就是左外和右外的合并,mysql不支持。
5.数据库、表相关修改命令
#删除、添加或修改表字段
ALTER TABLE 表名 DROP 字段名; 给表删除一个字段
ALTER TABLE 表名 ADD 字段名;给表添加一个字段
修改就是先删除再添加
#修改字段类型和名称
ALTER TABLE 表名 MODIFY 字段名 CHAR(10) 把某字段修改类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型; 修改字段名
#修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
#创建数据库
CREATE DATABASE 【数据库名】 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
#删除数据库
drop database 【数据库名】;
6.mysql常用函数
#字符串相关
CONCAT(s1,s2...sn) 把这几个字符串拼接成一个字符串
LEFT(s,n) 返回s的前n个字符组成的字符串 RIGHT(s,n)
LTRIM(s) 去掉左边的空格 RTRIM(s) TRIM(s)
LOWER(s) 把s所有字母变成小写的 UCASE(s) UPPER(s) 大写
REVERSE(s) 把s顺序颠倒
SPEACE(n) 返回n个空格
SUBSTR(s,start,length) 截取字符串s,从第几个开始,截取多长
#数字相关
PI() 返回圆周率
POW(x,y) 返回x的y次方
RAND() 返回0-1的随机数
TRUNCATE(x,y) 给x保留小数点后y为,不会四舍五入 ROUND会
#日期类型
ADDDATE(d,n) 计算日期d,增加n天的结果
ADDTIME(d,n) 增加n秒
DATEDIFF(d1,d2) 计算两个时间相隔的天数
#高级函数
7. mysql的数据类型
1)数字类型
类型 | 大小(字节) | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
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 967295) | |
BIGINT | 8 | |||
FLOAT | 4 | 浮点 | ||
DOUBLE | 8 | |||
DECIMAL | DECIMAL(M,D) 如果M>D,就是M+2,否则是D+2 |
2)日期类型
类型 | 大小(字节) | 范围 | 格式 | 用途 |
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MMDD | |
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-MMDD HH:MM:SS | |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨03:14:07 | YYYYMMDD HHMMSS |
3)字符串类型
类型 | 大小(字节) | 用途 |
CHAR | 0-255 | 定长字符串 |
VARCHAR | 0-65535 | 变长字符串 |
TINYBLOB | 0-255 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 | 短文本字符串 |
BLOB | 0-65535 | 二进制形式的长文本数据 |
TEXT | 0-65535 | 长文本数据 |
MEDIUMBLOB | 0-16777215 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16777215 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 | 极大文本数据 |
4)汉字
1.一个汉字的长度和编码有关
utf-8:一个汉字占3个字节
gbk: 一个汉字占2个字节
8.索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度(查询)。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。搜索时,通过索引字段在索引表里找到主键,就可以通过主键找到记录了。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
主键就是索引的一种。
1)普通索引
#创建索引
ALTER table tableName ADD INDEX indexName(columnName)
create index id on B(A_ID);
#创建表时指定索引
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length)));
查看一下show create table tb_user就会知道这张表的索引了
# 删除索引
DROP INDEX [indexName] ON mytable;
# 显示索引信息
SHOW INDEX FROM tb_user;
9.事务
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。事务用来管理 insert,update,delete 语句
一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,在中间某个环节不会结束。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Readuncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
# 事务控制语句:
BEGIN或START TRANSACTION;显式地开启一个事务;
COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改成为永久性的;
ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
SAVEPOINT identifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
RELEASE SAVEPOINT identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
ROLLBACK TO identifier;把事务回滚到标记点;
SET TRANSACTION;用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READUNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
# MYSQL 事务处理主要有两种方法:
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交