目录
数据库
数据库操作
数据库导入、导出
-
导入：加载SQL文件，文件名：.sql*
-
导出：导出数据脚本，文件名：.sql*
查看数据库
show Databases
创建数据库
create database 数据库名
删除数据库
drop database 数据库名
使用数据库
use 数据库名
表操作
索引
创建索引
create index 索引名称 on 表名（列名）
删除索引
- 方式一
drop index 索引名 on 表名
- 方式二
alter table 表名 drop index 索引名
创建表
create table 表名(列名1 类型(长度) [约束],列名2 类型(长度) [约束], ……);
长度区别
- int类型带长度：不影响存取值，即使设定的值超出了长度的范畴，也能存，如果没有达到设定的长度，则使用空格自动填充到设定的长度
- char类型：不可变字符，设定的长度就是规定当前字段能存的数据的最大长度，若超出长度，则会报错，若没有达到长度，使用空格填充到设定的长度
- varchar：可变字符，设定的长度同样是规定当前字段能存的数据的最大长度，若超出长度，则会报错，若没有达到长度，不会使用空格填充，实际多长就是多长
删除表
drop table 表名
表约束
1.非空约束 NOT NULL
2.默认值约束 DEFAULT '男'
3.唯一约束 UNIQUE
4.主键约束 PRIMARY KEY
查看表结构
desc 表名
表的修改
修改列名
Alter table 表名 change 列名 新列名 类型
修改列类型
Alter table 表名 modify 列名 新类型;
操作语句（crud）
数据添加
insert into [表名(字段名，字段名，字段名...)][values(值，值，值...)]
数据查询
[select] [*|字段|常量|表达式|函数] [(as)] [别名] [from] [表名]
[where] [字段名] [运算符|in|not in|is|is not= > < >= <= != ] [值] [ AND| OR(&& ||)] [字段名] [运算符] [值]
查询指定列：
select 列1,列2 from 表名
- 注意：字符串、日期需要用单引号括起来；
- 语句执行顺序: from --> where–> select;
其他操作
条件查询
范围查询
where 列 between 条件1 and 条件2; //列在这个区间的值
where 列 not between 条件1 and 条件2; //不在这个区间
where !( 列 between 条件1 and 条件2 ); //同样表示不在这个区间
集合查询
where 列 in(值1,值2); //列中的数据是in后的值里面的
where 列 not in(值1,值2); //不是in中指定值的数据
null值查询
where 列 is null //查询该列是空的数据
模糊查询
%：表示0到多个字符，
where 列 like '%a'; //表示以a结尾
where 列 like 'a%'; //表示以a开头
where 列 like '%a%'; //表示数据中包含a
_：表示一个字符，可多次使用，
where 列 like '%a_'; //数据结尾第二位是a
分页显示
where [条件] limit beginIndex,pageSize;
- beginIndex:表示从第多少条数据开始 pageSize:表示每页显示的数据条数
分组
[group by] [字段，字段...] having ... //根据字段分组
-
注意：如果要对分组后的数据进行筛选，那么必须使用having关键字，条件写在having后
Where和having的区别：
-
Where: 先过滤已有的数据(数据是已经存在的),在进行分组,在聚集计算
-
Having:先分组,在对每组进行计算,根据得到结果在过滤(分组把数据算出之后，在过滤) 注意：使用having的时候，它是可以使用别名的
排序
[order by] [字段 desc|asc],[字段 desc|asc]... //根据字段排序 desc(降序) asc(升序)
数据修改
[update] [表名] [set] [字段] [=] [值] , [字段] [=] [值] ...
[where] [字段名] [运算符|in|not in|is|is not= > < >= <= != ] [_占位符%模糊|值] [and|or(&& ||)] [字段名] [运算符] [值]
数据删除
[delete] [from] [表名] [where] [字段名] [运算符|in|= > < >= <= != ] [值] [and|or(&& ||)] [字段名] [运算符] [值]
数据去重
select distinct 列名 From 表名;
- 数据去重是select（查询）业务，并不是真正的删除重复数据，而是重复数据只显示一条。
表一（X）
| A | B | C |
|---|---|---|
| 1 | 2 | 3 |
| 5 | 6 | 7 |
表二(Y)
| C | D | E |
|---|---|---|
| 3 | 4 | 5 |
| 8 | 9 | 1 |
笛卡尔积(交叉连接)
select * from 表1 ，表2
- eg：select * from X , Y
- 结果如下：
| A | B | C | C | D | E |
|---|---|---|---|---|---|
| 1 | 2 | 3 | 3 | 4 | 5 |
| 5 | 6 | 7 | 8 | 9 | 1 |
| 1 | 2 | 3 | 8 | 9 | 1 |
| 5 | 6 | 7 | 3 | 4 | 5 |
连接
自然连接 （natural join）
-
自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。
select ...from 表1 natural join 表2； -
eg： select * from X natural join Y
-
结果如下：
A B C D E 1 2 3 4 5
左(外)（outer）连（以左为尊）
- 左(外)连接是在两表进行自然连接，只把左表要舍弃的保留在结果集中，右表对应的列上填null。
left join ...on ...
-
eg：Select * from X left join Y on X.C=Y.C
-
结果如下：
A B C D E 1 2 3 4 5 5 6 7 null null
右连（以右为尊）
- 右(外)连接是在两表进行自然连接，只把右表要舍弃的保留在结果集中，左表对应的列上填null。
right join ... on ...
-
eg：Select * from X left join Y on X.C=Y.C
-
结果如下：
A B C D E 1 2 3 4 5 null null 8 9 1
内连(inner join)
- 内连接基本与自然连接相同，不同之处在于自然连接要求是同名属性列的比较，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。
select * from 表1,表2 where 表1.字段=表2.字段;
//隐式内连接,使用where条件消除笛卡尔积
select * from 表1 [inner] join 表2 on 表1.字段=表2.字段;
//显式内连接,如果是多张表，则一直在join..on后依次添加join..on即可,inner关键字可被省略
inner join ... on ...
-
eg: select * from X inner join Y on X.A = Y.E
-
结果如下：
-
A B X.C Y.C D E 5 6 7 3 4 5
全外连接（full join）
- 全外连接是在两表进行自然连接，只把左表和右表要舍弃的都保留在结果集中，相对应的列上填null。
full join ... on ...
- eg: Select * from X full join Y on X.C=Y.C
- 结果如下：
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 |
| 5 | 6 | 7 | null | null |
| null | null | 8 | 9 | 1 |
函数
聚合函数
- 求平均数 (avg)
select avg(列名) from 表名 //求表中该列的平均数
- 查个数（count）
select count(*) from 表名 //查*在表中出现的个数
- 最大（max）
select max(列名) from 表名 //求表中该列的最大值
- 最小（min）
select min(列名) from 表名 //求表中该列的最小值
- 求总和
select sum(列名) from 表名 //求表中该列的总和
数学函数
- 求绝对值(abs)
select abs(列名-数字) from 表名
- 求指数(power)
select power(列名，数字) from 表名
- 求平方根（sqrt）
select sqrt(列名) from 表名
- 求随机数(rand)
select rand
- 向上取整（ceiling）
select ceiling(列名) from 表名
- 向下取整（floor）
select floor(列名) from 表名
- 四舍五入（round）
select round(列名，数字) from 表名 //列名：被操作的数；数字：要舍入的位数
1是舍入小数点后最后一位，-1是小数点前一位 ；
比如32.15 如果第二个参数是1 则为32.2 为-1则是30
如果round()只传一个参数的话就四舍五入到小数点
- 其他函数
正弦函数sin()余弦函数cos()正切函数tan()
反正弦函数asin(1/var)反余弦函数acos(1/var)反正切函数atan(var)
两个变量的反正切atan2(var,var)余切cot()
求圆周率函数PI(),acos(-1)
弧度制转换为角度制degrees(var);
角度值转换为弧度制radians(var);
求符号sign()如果大于0返回1，如果小于0返回-1，等于0返回0
求整数余数mod(var,var)
求自然对数log(var) oracle中是ln();
求以10为底的对数log10(var);
求幂函数power(x,y)
控制流程函数
if函数：
等同于if else 的效果
select 列名，if（列名 is 条件,'结果一','结果二'） from 表名
//如果满足条件，输出结果一；反之，输出满足二
-
eg: select last_name,commission_pct if(commission_pct is null , ‘没奖金’,‘有奖金’) from employees；
user表
| id | phone | age |
|---|---|---|
| 1 | 524154 | 12 |
| 2 | 524154 | 46 |
| 3 | 524154 | 70 |
| 4 | 524154 | 84 |
case函数：
类似于switch case 的效果
方法一
case …when …then…end
select 列名，列名，case
when 列名 条件（条件运算符） 判断条件 then 结果一
when 列名 条件（条件运算符） 判断条件 （and）列名 条件（条件运算符） 判断条件 then 结果一
end as '别名'
from 表名 where 选择条件；
-
eg:select id , phone , case
when age <= 12 then 1
when age >=70 and age <=100 then 2
end
as “年龄”
from user where mobile=“524154”;
| id | phone | 年龄 |
|---|---|---|
| 1 | 524154 | 1 |
| 2 | 524154 | NULL |
| 3 | 524154 | 2 |
| 4 | 524154 | 2 |
方法二
case …when …then…else…end
select 列名，列名，case
when 列名 条件（条件运算符） 判断条件 then 结果一
when 列名 条件（条件运算符） 判断条件 （and）列名 条件（条件运算符） 判断条件 then 结果一
else 结果三
end as '别名'
from 表名 where 选择条件；
-
eg:select id , phone , case
when age <= 12 then 1
when age >=70 and age <=100 then 2
else 9
end
as “年龄”
from user where mobile=“524154”;
id phone 年龄 1 524154 1 2 524154 9 3 524154 2 4 524154 2 -
方法一与方法二的区别：
方法一中，当内容不满足所有when判断条件时，结果置空；用NULL来填充；
方法二中，当内容不满足所有when判断条件时，结果用else后接的结果统一填充。
日期函数
函数概要
日期函数
now()
先用NOW() 获取当前时间：select now() ，current_date()，current_date():获得当前日期（date）结果 ：2019-10-30
- curtime()，current_time()，current_time(): 获得当前时间（time）结果： 22:31:14
- **utc_date(), utc_time(), utc_timestamp()😗*获得当前 UTC 日期时间 UTC：世界统一时间
- current_timestamp()，current_timestamp 获得当前日期时间 结果：2019-10-31 14:08:27
- localtime()， localtime 获得本机时间 结果：2019-10-31 14:11:13
- localtimestamp ，localtimestamp() 获得本机时间 结果：2019-10-31 14:11:13 版本在v4.0.6 之后更新的功能。
日期格式化函数
语法：select date_format(now(),’%y-%m-%d’)
返回时间：19-10-31
- fromat字符串格式化相关参数：
日期选取函数
- 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒
部分英文
set @dt = '2019-10-31 14:11:13.12345 ’
- select date(@dt); 结果：**2019-10-31 **
- select time(@dt); 结果：14:11:13.12345
- select year(@dt); 结果： 2019
- select quarter(@dt); 结果： 4
- select month(@dt); 结果： **10 **
- select week(@dt); 结果： **43 **
- select day(@dt); 结果： 31
- select hour(@dt); 结果：**14 **
- select minute(@dt); 结果： **11 **
- select second(@dt); 结果：13
- select microsecond(@dt); 结果：**12345 **
extract
set @dt = '2019-10-31 14:11:13.12345 ’
- select extract(year from @dt); 结果：2019
- select extract(quarter from @dt); 结果：**4 **
- select extract(month from @dt); 结果：**10 **
- select extract(week from @dt); 结果：**43 **
- select extract(day from @dt); 结果：**31 **
- select extract(hour from @dt); 结果：14
- select extract(minute from @dt); 结果：11
- select extract(second from @dt); 结果：13
- select extract(microsecond from @dt); 结果：12345
- select extract(year_month from @dt); 结果：201910
- select extract(day_hour from @dt); 结果：3114
- select extract(day_minute from @dt); 结果：311411
- select extract(day_second from @dt); 结果：31141113
- select extract(day_microsecond from @dt); 结果：3114111312345
- select extract(hour_minute from @dt); 结果：1411
- select extract(hour_second from @dt); 结果：141113
- select extract(hour_microsecond from @dt); 结果：14111312345
- select extract(minute_second from @dt); 结果：1113
- select extract(minute_microsecond from @dt); 结果： 111312345
- select extract(second_microsecond from @dt); 结果： 1312345
Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。并且还具有选取‘day_microsecond’等功能。注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分
week相关函数
- week(), weekofyear(), dayofweek(), weekday(), yearweek()
- set @dt = ‘2008-08-08’;
- select week(@dt); – 31
- select week(@dt,3); – 32
- select weekofyear(@dt); – 32
- select dayofweek(@dt); – 6
- select weekday(@dt); – 4
- select yearweek(@dt); – 200831
week() 函数，可以有两个参数，具体可看手册。 weekofyear() 和 week() 一样，都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)
weekday() 函数和 dayofweek() 类似，都是返回“某天”在一周中的位置。不同点在于参考的标准， weekday：(0 =Monday, 1 = Tuesday, …, 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday,…, 7 = Saturday）
- yearweek() 函数，返回 year(2008) + week 位置(31)
返回星期和月份名称函数
- dayname(), monthname()
- set @dt = ‘2008-08-08’;
- select dayname(@dt); – Friday
- select monthname(@dt); – August
last_day() 函数
-
返回月份中的最后一天。
-
select last_day(‘2008-02-01’); – 2008-02-29
-
select last_day(‘2008-08-08’); – 2008-08-31
-
MySQL last_day() 函数非常有用，比如我想得到当前月份中有多少天，可以这样来计算：
-
eg：
mysql> select now(), day(last_day(now())) as days;
结果：
| 函数 | 结果 |
|---|---|
| now() | 2008-08-09 11:45:45 |
| days | 31 |
日期时间计算函数
为日期增加一个时间间隔
date_add()
- set @dt = now();
- select date_add(@dt, interval 1 day); – add 1 day
- select date_add(@dt, interval 1 hour); – add 1 hour
- select date_add(@dt, interval 1 minute); – …
- select date_add(@dt, interval 1 second);
- select date_add(@dt, interval 1 microsecond);
- select date_add(@dt, interval 1 week);
- select date_add(@dt, interval 1 month);
- select date_add(@dt, interval 1 quarter);
- select date_add(@dt, interval 1 year);
- select date_add(@dt, interval -1 day); – sub 1 day
MySQL adddate(), addtime()函数，可以用 date_add() 来替代。
eg：date_add() 实现 addtime() 功能
set @dt = ‘2008-08-09 12:12:33’;
- select date_add(@dt, interval ‘01:15:30’ hour_second);
date_add(@dt, interval ‘01:15:30’ hour_second) 结果：| 2008-08-09 13:28:03 |
-
select date_add(@dt, interval ‘1 01:15:30’ day_second);
date_add(@dt, interval ‘1 01:15:30’ day_second) 结果：2008-08-10 13:28:03
-
总结：date_add() 函数，分别为 @dt 增加了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”。建议：总是使用 date_add() 日期时间函数来替代 adddate(), addtime()。
日期、时间相减函数
detediff
- datediff(date1,date2)：两个日期相减 date1 - date2，返回天数
- eg:
select datediff(‘2008-08-08’, ‘2008-08-01’); – 7
select datediff(‘2008-08-01’, ‘2008-08-08’); – -7
timestampdiff
- 两个日期相减得出年，月的timestampdiff()函数
- timestampdiff(unit,datetime_expr1,datetime_expr2)
- eg:
select timestampdiff(year,‘2002-05-01’,‘2001-01-01’); – -1
select timestampdiff(day ,‘2002-05-01’,‘2001-01-01’); – -485
select timestampdiff(hour,‘2008-08-08 12:00:00’,‘2008-08-08 00:00:00’); – -12
select datediff(‘2008-08-08 12:00:00’, ‘2008-08-01 00:00:00’); – 7
总结:MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。
timediff
- timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值
- eg:
select timediff(‘2008-08-08 08:08:08’, ‘2008-08-08 00:00:00’); – 08:08:08
select timediff(‘08:08:08’, ‘00:00:00’); – 08:08:08
总结：timediff(time1,time2) 函数的两个参数类型必须相同。
5550
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
