儒冠多误身
个人学习笔记
MySQL个人学习笔记
问题总结
1. 报错
- error 1064，语法错误
- error 1366(HY000)，客户端没有声明字符集
2. 设置字符集
-
如果服务器、连接器、客户端都使用的gbk字符集，可简写为：
set names gbk; -
set character_set_client = gbk;#声明客户端是gbk set character_set_connection = utf8;#声明连接器为utf8 set character_set_results = gbk;#声明返回值是gbk
3. 跳出执行
\c，不执行该段代码
4. 破解数据库密码
- 通过任务管理器或者服务管理，关掉mysqld（服务进程）
- 通过命令行 + 特殊参数开启mysql
Mysqld --skip-grant-tables - 此时，mysqld服务进程已经打开，并且，不需要权限检查
mysql -uroot无密码登录服务器- 修改权限表
use mysql;update user set Password = password('123456') where user = 'root';flush privileges;
入门语句
-
连接服务器
mysql -uusername -p -
查看所有库
show databases; -
创建库
create database demo charset utf8; -
删除库
drop database demo; -
选择库
use demo -
查看表
show tables; -
创建简单表
create table stu( id int, name varchar(20) )engine myisam charset utf8; -
删除表
drop table stu -
改表名
rename table stu to newstu -
插入数据
insert into newstu values <1,'zhangsan'>, <2,'lisi'>, <3,'wangwu'>; -
清空表
truncate newstu;- truncate，相当于删除表后，重建一张同结果的表-全新的表；
delete from class;- delete，删除表的所有行，如果有自增列，
增删改查
创建表
create table class(
id int primary key auto_increment,
sname varchar(10) not null default '',
gender char(1) not null default '',
company char(40) not null default '',
salary decimal(6,2) not null default 0.00,
fanbu smallint not null default 0
)engine myisam charset utf8;
查看表结构
desc stu;
show create table stu;#查看stu表创建语句
增 insert
- 往哪张表添加行
- 添加哪几列
- 分别是什么值
insert into class
(id,sname,gender,salary,fanbu)
values
(1,'张三','男','8888.67','234');
# 插入部分列
insert into class
(sname,gender,salary)
values
('王五','男',8888.68);
# 插入所有列，可不声明待插入列
insert into class
values
(3,'李四','女','新浪',5678.99,200);
- 注意，插入所有列时，不要因为id为自增，就忽略列与值的一一对应。
- 填上id
- 增加多行
insert into class
(sname,company,salary)
values
('刘备','皇叔',15.28),
('孙策','江东集团',56.34),
('曹操','枭雄',88.56);
select * from stu;
改 update set where
- 改哪张表
- 改哪几列
- 改为什么值
- 再哪些行生效
update class
set
fanbu = fanbu + 200
where id = 6；
## 多条件
update class
set
fanbu = 500
where gender='男' and salary > 8000;
-
where expression
-
where +表达式
-
只要表达式为真，则改行就发挥作用
-
update class set salary = salary + 1000 where 1; #全部改，因为1恒为真
-
删 delete from where
- 删哪个表
- 删哪几行: where expression
delete from class
where salart > 88888 and gender = '女';
查 select
-
select * from table1,table2;#两表的笛卡尔积（集合相乘）
查询模型
- 列就是变量，再一行上，列的值都在变化
- where条件是表达式，再哪一行上表达式为真，哪一行就取出来
- 查询结果集，在结构上可以当成表看
建表
数值型
整型
| 占据空间 | 存储范围 | |
|---|---|---|
| tinyint | 1字节 | -128~127； 0~255 |
| smallint | 2字节 | -215~215 -1 0~216-1 |
| mediuint | 3字节 | -223~223-1 0~224 |
| int | 4字节 | -231~231-1 0~232 |
| bigint | 8字节 | -263~263-1 0~264 |
int系列的参数：(M) unsigned zerofill
- 不加特殊说明，默认为是有符号；
- 设置为无符号，
col_1 smallint unsigned - (m) zerofill，
col_2 int(5) zerofill- (m)，表示数据的宽度
- 要与zerofill同时使用
create table demo_1(
sname varchar(20) not null default '',
age tinyint not null default 0
)engine myisam charset utf8;
alter table demo_1 add score tinyint unsigned not null default 0;# unsigned
alter table demo_1 add snum smallint(5) zerofill not null default 0;# <m> 必须和zerofill一起使用
浮点型：float(M,D)，会有损精度
- float(M,D),decimal(M,D)
- M叫”精度“—>浮点数的“总位数”，而D是“标度” —>小数点后几位；
float(6,2)总6位，小数点后2位。
create table salary1(
sname varchar(20) not null default '',
salary float(8,2),
bouns float(6,2) unsigned not null default 0.00
)engine myisam charset utf8;
定点数：decimal(M,D)
- 把整数部分和小数部分，分开存储；
- 比float精确；
字符串型
Char(M)定长字符
- char(M)，定长字符串，存储M个长度、字符
- M:[0,255]
- 长度不够的 在尾部用 空格 补齐
- 末尾若要存储空格，无法读取
Varchar(M)变长字符
- varchar(M)，变长字符串，存储[0,M]个长度
- M：[0,65535]
- 实际占用空间：i字符 + （1~2）个字节
- varchar 存储：列内容前有1~2个字节，标志该列内容的长度
Text文本类型
- 搜索速度较慢
- 可存储较大文本
- Text不加默认值，加了也没用
- 不是特别大的内容，建议用char,varchar。
Blob：二进制类型
- blob是二进制类型，用来存储图形、音频等信息。
- 防止因为字符集问题，导致信息丢失。
日期时间类型，记得加引号
| 类型 | 说明 | 标准格式 | 范围 | 存储 |
|---|---|---|---|---|
| date | 日期 | YYYY-MM-DD | 1000-01-01 9999-12-31 | 3字节 |
| time | 时间 | HH:MM:SS | -838:59:59 838:59:59 | 3字节 |
| datetime | 日期时间 | YYYY-mm-dd HH:ii:ss | 1000-01-01 00:00:00 9999-12-31 23:59:59 | 8字节 |
| year | 年份类型 | YYYY和YY(不推荐) | 1901~2155 | 1字节 |
| TimeStamp | 时间戳 | 4字节 |
Date日期
alter table demo add birth date not null default '0000-00-00';
Time时间
alter table demo add sign time not null default '00:00:00';
Datetime 时间时间类型
alter table demo add logintime datetime not null default '0000-00-00 00:00:00';
Year 年类型
alter table demo add sj year not null default '0000';
TimeStamp时间戳
create table demo_1(
ts timestamp default CURRENT_TIMESTAMP;
)engine myisam charset utf8;
建表
初步建表
create table member(
id int unsigned not null primary key auto_increment,
username char(20) not null default '',
gender char(1) not null default '',
weight tinyint unsigned not null default 0,
birth date not null default '0000-00-00 00',
salary decimal(8,2) not null default 0.00,
lastlogin int unsigned not null default 0,
intro varchar(1500)
)engine myisam charset utf8;
优化
- 对上表的优化
- username char(20)列，虽然浪费存储空间，但提高查询速度，可以接受。
- intro varchar(1500) ，考虑到个人简介，更新频率并不高，推荐把intro列单独拿出来，另放一张表里
create table member(
id int unsigned not null auto_increment primary key,
username char(20) not null default '',
gender char(1) not null default '',
weight tinyint unsigned not null default 0,
birth date not null default '0000-00-00 00',
salary decimal(8,2) not null default 0.00,
lastlogin int unsigned not null default 0
)engine myisam charset utf8;
- 优化思路
- 优化：时间 与 空间 的取舍
- 把 频繁用到的信息，优先考虑效率，存储到一张表中
- 不常用的信息 和 比较占据空间的信息，存储到辅表中。
修改表
增加列
【加的列在表的最后】
alter table 表名 add 列名称 列类型 列参数;
alter table demo add col_1 int unsigned;
【指定添加的位置】
alter table 表名 add 列名称 列类型 列参数 after 某列;
alter table demo add gender char(1) not null default '' after id;
【放在最前面】
alter table 表名 add 列名称 列类型 列参数 first;
alter table demo add col_f int unsigned not null default 0 first;
删除列
alter table 表名 drop 某列；
alter table demo drop col_f;
修改列
- 修改列类型
alter table modify
alter table 表名 modify 某列 列新类型 新参数;
alter table demo modify gender char(4) not null default '';
- 修改列名
change
alter table 表名 change 某列 新列名 列类型 参数;
alter table demo change id uid int unsigned;
综合练习
# 创建表
create table test(
id int unsigned not null auto_increment primary key,
username char(20) not null default '',
gender char(1) not null default '',
salary decimal(8,2) not null default 0.00,
weight tinyint unsigned
)engine myisam charset utf8;
# 修改列
alter table test add col_2 int unsigned not null after username;
alter table test modify col_2 int unsigned not null default 0;
alter table test add col_3 char(3) not null default '' first;
alter table test change col_3 nid int unsigned not null default 0;
alter table test drop col_1;
# 修改列的元素值
update test set salary = salary*1.1 where salary < 20000;
update test set username = '路飞', gender = 1 where id = 6 and salary > 50000;
# 删除列中的元素
select from test
where salary >90000 and id > 20;
查
select *,col1,distinct(col2),count(distinct col)
from table1
where 1
group by col1
having
order by col2
limit 5 offset 3;
- 把列当做变量看
- 5种子句
- where 查询条件
- group by 分组
- having 筛选
- order by 排序
- limit offset
优先级
- 括号()
- not
- and
- or
where 查询
- where 对表上的数据发挥作用
- 对结果列 不发挥作用
- 结果列不在from表中
- in () 散点集合
- 括号内 区分大小写
- not in ()
- between and 区间
- 包括边界值 []
- and or
- 括号
- where 条件1 and (条件2 or 条件3) and 条件4
- like 模糊匹配
- 通配符
- % 任意字符
- _ 单一字符
- 通配符
NULL
-
null是一种类型，比较时只能用
- where col is NULL;
- where clo1 is not null;
-
null效率不高，影响索引效率
-
create table demo( id int unsigned not null auto_increment primary key, salary float(8,2) unsigned not null default 0.00 )engine myisam charset utf8;
-
group分组
-
类似Python的groupby【+】统计函数
- 分了组 +统计函数 配合使用 更有意义
-
select city,count(1) from table1 group by city order by count(salary) desc； # 按城市分组，统计职位数，按职位数排序-降序create table result( name varchar(20) default null, subject varchar(20) default null, score tinyint(4) default null )engine myisam charset utf8; insert into reselt values ('张三','语文',90), ('张三','数学',59), ('张三','政治',30), ('李四','语文',36), ('王五','语文',80), ('王五','数学',90), ('王五','政治',100), ('李四','数学',50) select name,avg(score),sum(score<60) as g from result group by name having g >1;
having 筛选
- 可对 结果集，筛选。
order by 排序
- 对 结果集，排序。
order by col desc降序order by col asc升序，默认不写- 多列排序
order by 1 desc, 2 asc, col3 desc; order by 2对结果集中的各列，可用数字代替具体的列，以1代表第一列，类推。
limit 限制条目
-
limit [offset,] N- offset：偏移量
- N：取出条目
- offset，如果不写，则相当于，limit 0, N
-
limit 2,3 limit 3 offset 2 # 偏移2行，取第3~第5
子查询
- 注意select * from （select * from table1 where 1）,内层的sql语句，别用*！！！
where型子查询
- 把内层的查询结果作为外层查询的比较条件
- 典型题：查询最大商品、最贵商品
- where 列 = (内层sql)，内层sql返回的只能是单行、单列
- where 列 in (内层sql)，内层sql返回的只能是单列，可多行
select companyid,companyname from demo
where companyid = (select max(companyid) from demo);
select companyid,companyname,city from demo
where companyid in (
select max(companyid) from demo group by city)
# 各个城市下companyid最大的公司的名称信息
from型子查询
- 把内层的查询结果当成临时表，供外层sql再次查询
- 典型题：查询每个栏目下的最新、最贵商品
select * from (
select companyid,city,education,bottom from demo
) as tmp #临时表
group by
exists型子查询
- 把外层sql的结果，拿到内层sql取测试，如果内层sql成立，则该行取出。
select * from table1 as t1
where exists (select * from table2 as t2 where t1.col_name = t2.col_name)
limit 5
多表连接
# 创建表
create table matchs(
matchid int unsigned not null default 0,
hostteamid int unsigned not null default 0,
matchresult varchar(20) not null default '',
matchtime DATE
)engine myisam charset utf8;
alter table matchs add guestteamid int UNSIGNED not null default 0 after hostteamid;
create table team (
teamid int unsigned not null default 0,
teamname VARCHAR(20) not null default ''
)engine myisam charset utf8;
insert into matchs
values
(1,1,2,'2:0','2006-05-21'),
(2,2,3,'1:2','2006-06-21'),
(3,3,1,'2:5','2006-06-25'),
(4,2,1,'3:2','2006-07-21');
insert into team
VALUES
(1,'国安'),
(2,'申花'),
(3,'传智联队')
update team set teamname = '公益联队' where teamid = 3;
两表做 全相乘
-
从行角度看，两表每一行，两两相乘
-
从列角度看，结果集中的列，是两表的列名的相加
-
select * from table1,table2; select table1.col1,table2.col1,table2.col3,table1.col2 from table1,table2 where table1.col1 = table2.col1; # 两表 全相乘 效率低、耗内存 -
ambiguous，模糊不清
- 表明.列名
join连接
-
出于移植时，兼容性的考虑，尽量用左连接。
-
MySQL不支持，外连接
-
两次left join
-
注意 as 的表名不一
-
select t1.teamname,matchresult,t2.teamname,matchtime from matchs m left join team t1 on m.hostteamid = t1.teamid left join team t2 on m.guestteamid = t2.teamid where matchtime between '2006-06-01' and '2006-07-01' order by hostteamid
-
拼接 union/union all
-
union 合并的是结果集
- 需要两个结果集的字段数量相等才能用union
-
从单张表查询，用union
- 让结果集列名【一致】：对列名as为统一别名
select col2 as a,col3 as b from table1 where col >a union select col5 as a,col6 as b from table1 where col <b; -
从两张表查询，用union
select cola as aa,colb as bb from table1 where col = 1 union select colc as aa,cold as bb from table2 where col = 2; -
思考
-
当结果集的列名不一致，直接用union时：
- 列名以第一个sql语句列名为准
-
当取出的两个结果集有重复，有相同的行，用union：
- 这种情况比较常见，默认会去重
-
不想去重union all
-
select * from table1 union all select * from table2
-
-
使用条件
- 只要结果集列数相同 就可以使用 union
- 列的类型不一，也能union，但拼接意义不大
-
union后的结果集，再操作
- order by：
(sql1 order by col) union (sql2 order by col) order by col- 外层order by 是针对合并后的结果集，排的序
- 内层order by 语句，在执行期间，就被MySQL的代码分析器给优化掉了。
- limit：
(sql1 order by col limit 3) union (sql2 order by col limit 2)- 因为有limit，所以order by 影响返回值，内层order by 发挥作用
- 针对是否影响结果集，MySQL判断是否优化掉内层的order by。
- order by：
-
-
练习题
create table a( id char(1), num int) engine myisam charset utf8; create table b( id char(1), num int) engine myisam charset utf8; insert into a values ('a',5),('b',10),('c',15),('d',20); insert into b VALUES ('b',3),('c',88),('d',99),('e',88); # 先拼接，再分组求和 # 用 union all SELECT id,sum(num) from (select id,num FROM a union ALL SELECT * from b) as tmp GROUP BY id;
函数
注意
-
mysql函数肯定要影响查询速度
- 应该在建表时，通过合理的表结构，减少函数的使用
-
如果确实要用函数，以时间函数为例
- 在mysql中用data_format，在python中用pd.to_datetime(df[‘col’],format = )，优先放在业务逻辑层，即python中处理。
-
在查询时使用了函数，最大的一个坏处，以data_format(A)为例
-
则A列的索引将无法使用
-
如果你针对某列查询，而此列，用上了函数来判断，则此列将不再使用索引
-
select name,email from table1 where right(position('@',email)) = 'qq.com'; -
本来email列是有索引的，可以加快查询速度，但因为你使用的并不是email列，而是函数处理后的email列的返回值，因此结果集中email列的查询就非常缓慢了。
-
-
总结：在where条件中，对某列使用了函数，则此列的索引不发挥作用。
-
字符串函数
-
substring(str,pos)截取字符串
-
substring(str,pos)
-
substring(str from pos)
-
select col1,col2,substring(col2,3) from table1; select col1,col2,substring(col2 from 3) from table1; # 对col2列，从第3个字符开始，往后截取 -
substring(str,pos,len)
- substring(str from pos for len)
-
select col1,col2 substring(col2,3,5) from table1; select col1,col2 substring(col2 from 3 len5) from table1;
-
-
concat 连接字符串
-
concat(col1,col2,……)
-
select col1,col2,concat(col1,substring(col2,3)) from table1; # 连接col1和 col2中第3个和之后的字符 update table1 set col_new = concat(col1,substring(col2,3));
| ascii(char) | 返回字符的ASCII码值 |
| bit_length(str) | 返回字符串的比特长度 |
| concat(s1,s2,……sn) | 连接成字符串 |
| concat_ws(sep,s1,s2,……sn) | 将s1,s2……sn连接成字符串，并用sep字符间隔 |
| insert(str,x,y,instr) | 将字符串str从第x位置开始，y个字符长度的子串替换为字符串instr，返回结果 |
| find_in_set(str,list) | 分析逗号分隔的list列表，如果发现str，返回str在list中的位置 |
| lcase(str)或lower(str) | 返回str的所有字符小写结果 |
| left(str,x) | 返回字符串str中最左边的x个字符 |
| length(str) | 返回字符串str的字符数 |
| ltrim(str) | 从字符串str中切掉开头的空格 |
| locate(‘子字符’,col) | 子字符 在字符串中的位置 |
| POSITION(substr IN str) | 返回子串substr再字符串中第一次出现的位置 |
| quoter(str) | 用反斜杠转义str中的单引号 |
| repeat(str,x) | 返回字符串str重复x次的结果 |
| reverse(str) | 返回点到字符串str的结果 |
| right(str,x) | 返回字符串str中最右边x个字符 |
| rtrim(str) | 返回字符串str尾部的空格 |
| strcmp(s1,s2) | 比较字符串s1和s2 |
| trim(str) | 去除字符串首部和尾部的所有空格 |
| ucase(str)或upper(str) | 返回字符串str的所有字符大写结果 |
数学函数
| abs(x) | 返回x的绝对值 |
| bin(x) | 返回x的二进制 |
| oct(x) | 返回x的八进制 |
| hex(x) | 返回想的十六进制 |
| ceiling(x) | 返回大于x的最小整数值，向上取整 |
| floor(x) | 返回小于x的最大整数值，向下取整 |
| exp(x) | 返回值e(自然对数的底)的x次方 |
| greatest(x1,x2,……) | 返回集合中最大值 |
| least(x1,x2,……) | 返回集合在最小值 |
| ln(x) | 返回x的自然对数 |
| log(x,y) | 返回x的以y为底的对数 |
| mod(x,y) | 返回x/y的模（余数） |
| pi() | 返回pi的值（圆周率） |
| rand() | 生成[0,1]之间的随机值 |
| rand(int) | 生成重复的随机值 |
| round(x,y) | 返回参数x的四舍五入的有y位小数的值 |
| sign(x) | 返回代表数字x的符号的值 |
| sqrt(x) | 返回一个数的平方根 |
| truncate(x,y) | 返回数字x截短为y位小数的结果 |
select floor(666 * 0.88);#打八折，不要零头
update demo set col_number = floor(col_number)*10
where col_number between 30 and 50;
# 将 [30,50]之间的数字，变为30,40,50
# 随机
select rand(1);#生成随机值，且再次运行其产生的结果不变
select rand();#生成随机值，再次运行，其结果改变
select floor(rand()*10 + 5) # 随机生成5~15之间
聚合函数
- max(col) 最大值
- min(col) 最小值
- sum(col) 求总和
- avg(col) 求均值
- count()非null值的个数
- 【绝对行数】所有行数，包括NULL
| myisam | innodb | |
|---|---|---|
| count(1) count(2) count(常数) | 查询绝对行数 | 查询绝对行数 |
| count(*) | 查询绝对行数 | 效率低 |
| count(col_name) | 查询该列不为null的行数 | 查询该列不为null的行数 |
- group_concat(col) 返回由属于一组的列值连接组合而成的结果
select group_concat(col,'') from
# 一般用在分组后，将分组后的列的元素，用‘,’拼接在一起
# 默认用‘，’拼接在一起
日期和时间函数
| curdate() 或 current_date() | 返回当前的日期 | |
| curtime() 或 current_time() | 返回当前的时间 | |
| date_add(date,interval int keyword) | 返回日期date加上间隔时间int的结果（int必须按照关键字进行格式化） | |
| date_format(date,fmt) | 依照指定的fmt格式格式化日期date值 | |
| date_sub(date,interval int keyword) | 返回日期date加上间隔时间int的结果（int必须按照关键字进行格式化） | |
| dayofweek(date) | 返回date是一星期的第几天（1~7） | |
| dayofmonth(date) | 返回date是一个月的第几天（1~31） | |
| dayofyear(date) | 返回date是一年的第几天(1~366) | |
| dayname(date) | 返回date的星期名 |
-
-
select date_add(current_date,interval 6 month); #加上6个月 select date_sub(current_date,interval 6 month); #往前数6个月 select dayname(current_date);
from_unixtime(t,fmt) 根据指定的fmt格式，格式化unix时间戳ts hour(time) 返回time的小时值（0~23） minute(time) 返回time的分钟值（0~59） month(date) 返回date的月份值(1~12) monthname(date) 返回date的月份名，英文 now() 返回当前的日期和时间 quarter(date) 返回date再一年中的季度 week(date) 返回日期date为一年中第几周（0~53） year(date) 返回日期date的年份(1000~9999) -
-
-
select monthname(current_date); select quarter(current_date);
-
获取当前系统时间
select from_unixtime(unix_timestamp())
union
select extract(year_month from current_date)
union
select extract(day_second from current_date)
union
select extract(hour_minute from current_date)
返回两个日期之间的差值（月数）
select period_diff(201307,current_date);
计算年龄
select date_format(from_days(to_days(now()) - o_days(birthday)),'%y') + 0 as age from employee;
# 如果birthday是未来的年月日的话，计算结果为0
计算员工的绝对年龄
-
birthday是未来的日期时，将得到负值
-
select date_format(now(),'%y') - date_format(birthday,'%y') - (date_format(now(),'00-%m-%d') < date_format(birthday,'00-%m-%d')) as age from employee;
格式化函数
| date_format(date,fmt) | 依照字符串fmt格式化日期date |
-
select date_format(now(),'%w,%d %m %y %r') select date_format(now(),'%y-%m-%d') # 现在的年月日 select date_format(20170830,'%Y-%m-%d') select date_format(now(),'%h:%i:%p') #现在的时间
| format(x,y) | 把x格式化为以逗号隔开的数字序列，y是结果的小数位数 |
-
把大的数值格式化为以逗号间隔的易读的序列
-
select format(34234.34323432,3)
| inet_aton(ip) | 返回ip地址的数字表示 |
| inet_ntoa(num) | |
| time_format(time,fmt) | 依照字符串fmt格式化时间time值 |
-
select inet_aton('10.122.89.47') select inet_ntoa(175790383)
类型转化函数
case()
-
把一个值转化为指定的数据类型
- binary,char,date,time,datetime,signed,unsigned
-
select cast(now() as signed integer),curdate() +0; select 'f' = binary 'f', 'f' = cast('f' as binary);
系统信息函数
| database() | 返回当前数据库名 |
| benchmark(count,expr) | 将表达式expr重复运行count次 |
| connection_id() | 返回当前客户的连接id |
| foun_rows() | 返回最后一个select 查询进行检索的总行数 |
| user() 或system_user() | 返回当前登录用户名 |
| version() | 返回mysql服务器的版本 |
select database(),version(),user();
select benchmark(99,log(rand()*pi()))
#mysql计算log(rand()*pi())表达式99次
控制流程函数
case when then end
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ……] [ELSE result] END CASE WHEN [condition] THEN result[WHEN [condition] THEN result] [ELSE result] END
-
再第一个方案的返回结果中，value = compare-value
-
而第二个方案的返回结果是第一种情况的真实结果
-
如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE部分，则返回值为NULL
-
select education,salary,case education when '本科' then 1 when '硕士' then 2 when '大专' then 0 when '博士' then 3 ELSE -1 END as '学历' from dataanalyst order by '学历' desc;
if(expr1,expr2,expr3)-三元表达式
-
如果expr1是TRUE(expr1 <> 0 and expr1<>NULL)，则if()的返回值为expr2，否则返回值为expr3
-
if()的返回值为数字值或字符串值，具体情况视语境而定
-
select education,if(bottom >8,'优先','等待') as vip from dataanalyst order BY education,vip DESC; # count(if()) select city,count(1) from demo group by city having count(if(companyfullname like '%电子商务%',1,null)>=50;count()\sum()与if（）三元表达式一起用
ifnull(expr1,expr2)
-
如果expr1不为NULL，则ifnull()返回expr1，否则返回值为expr2
-
select education,ifnull(bottom ,0) as v from dataanalyst order by v ;
nullif(expr1,expr2)
-
如果expr1 = expr2成立，返回值为NULl，否则返回值为expr1
-
select col1,nullif(col2,expr2) from table1; # 相当于 select col1,case col2 when col2 = expr2 then null else expr1 end from table1;
其他函数
| distinct(col) | 去重 |
视图view
- 在查询中，我们经常把查询结果，当成临时表来看
- view可以看做一张虚拟表，是通过某种运算得到的一个投影、映射
- 表改变，视图跟着变
- 修改视图，也会影响到表
- 只有， 视图与表，在列名上，一一对应情况：才能修改视图
创建视图
create view 视图名 as select 语句
create view tmp as
select count(education) as js
from table1
group by education;
调用-view
show tables;# 查看刚刚创建的视图tmp
select * from tmp order by js limit 3;
- 作用
- 简化查询
- 更精细的权限控制
- 数据多，分表时，可以用到
删除-视图
drop view view_name;
- windows系统中，表的文件后缀名
- demo.frm，表的结构
- tmp.frm，视图view只有.frm后缀名文件
- demo.MYD，表的数据
- demo.MYI，表的索引
- demo.frm，表的结构
algorithm 决定view是否创建临时表
-
algorithm=
- merge
- temptable
- undefined
-
algorithm = merge，并不会建立临时表，只是合并的结果
create algorithm = merge view v1 as select * from demo where salary >8000; # -
对于复杂查询，则需要建立临时表algorithm = temptable
create algorithm = temptable view v2 as select * from demo order by salary desc; select * from v2 group by education limit 3; -
如果拿不准是否创建，用algorithm = undefined，让系统做决定
create algorithm = undefined view v3 as select 语句;
乱码
字符集
- ASCII 单字节
- gb2312，双字节，只收录6763个汉字
- GBK，双字节，完全兼容gb2312，GBK的第2低位，不再局限于129~255，小于127的也能用
- 收录汉字21003个
- 符号883个
- 并提供1894个造字码位
- ANSI，代表本地字符集
- 在中国ANSI是GBK
- 在日本ANSI是JIS
- Unicode，世界通用的码表，4字节，避免高位浪费：
- Unicode的实现方式成为Unicode转换格式（Unicode TRansformation Format）UTF
- UTF-8
- Unicode与utf-8的关系
- 就像：源文件 与 压缩文件 的关系–>压缩空间，避免浪费
- utf-8占 几 个字节
- 不可能定长，否则压缩没有还有什么意义
- 1~6个字节
- 如何截取utf8（各国语言都有），无乱码？
- 从头开始，取1个字节
- 通过位运算，计算连续的1 的个数
- 如为0，则截取1个字节
- 入围N，则截取N个字节
- Unicode的实现方式成为Unicode转换格式（Unicode TRansformation Format）UTF
- 容量
- gb2312<gbk<utf-8
- gbk转utf-8？
- gbk–>Unicode–>utf-8
- 乱码的原因
- 解码时与实际编码不一致——可修复：
- 使编码与解码一致，就不乱码了
- 传输过程中，编码不一致，导致字节丢失：
- 不可修复
- 解码时与实际编码不一致——可修复：
MySQL中设置字符集
-
字符集范围设置要求
-
服务器字符集 >= conntion字符集 >= client字符集
-
如果服务器、连接器、客户端都是gbk，可简写为：
-
set names gbk;
-
-
-
客户端(client)字符集，连接器(conntion)字符集，查询结果字符集，utf8存入服务器
-
要想不乱码，需要指定客户端的编码：告诉服务器，客户端的编码是gbk
-
声明客户端是gbk
set character_set_client = gbk;
-
-
设置连接器使用utf8
-
声明连接器为utf8
set character_set_connection = utf8;
-
-
设置返回值为gbk
-
声明返回值是gbk
set character_set_results = gbk;
-
-
牵涉到数据库，想不乱码
- 正确指定客户端的编码
- 合理选择连接器的编码
- 正确指定返回内容的编码
- 举例：
- 我的网页是utf8，因此client客户端是utf8
- 我的表是utf8，因此，连接器，utf8
- 返回值也是utf8
- 因此
set names utf8;
- 举例：
存储引擎
create table demo(
……
……
)engine myisam charset utf8;
- MySQL表引擎
| 特点 | Myisam | InnoDB |
|---|---|---|
| 批量插入的速度 | 高 | 低 |
| 事务安全 | 支持 | |
| 全文索引 | 支持 | |
| 锁机制 | 表锁 | 行锁 |
| 存储限制 | 没有 | 64TB |
| B树索引 | 支持 | 支持 |
| 哈希索引 | 支持 | |
| 集群索引 | 支持 | |
| 数据缓存 | 支持 | |
| 索引缓存 | 支持 | 支持 |
| 数据可压缩 | 支持 | |
| 空间使用 | 低 | 高 |
| 内存 | 低 | 高 |
| 支持外键 | 支持 |
事务
-
事务的4个特性
-
原子性
-
2步或N步操作，逻辑上不可分割
-
要么成功，要么都不成功
-
# 使用事务 start transaction; # 开启事务 update demo set salary = salary + 1000 where uname ='zhangsan'; update demo set salary = salary - 1000 where uname = 'lisi'; commit; # 提交整个事务 # 模拟扣款失败——原子性 start transaction; # 开启事务 update demo set salary = salary + 1000 where uname ='zhangsan'; update demo set salary = salarys - 1000 where uname = 'lisi'; # 这里故意打错salarys，来模拟扣款失败 rollback; # 回滚
-
-
一致性
- 操作前后，值的变化，逻辑上成立
- 比如，tinyint来存钱，+300，溢出了，也是失败
-
隔离性
- 事务结束前，在其他客户端无法查询事务中的操作变化
-
持久性
- 一旦完成，无法撤销
-
数据加载 读入
create table orders(
orderid int primary key auto_increment,
userid int(11) UNSIGNED not null,
ispaid varchar(3) not null default '',
price decimal(15,2) not null default 0.00,
payTime TIMESTAMP
)ENGINE INNODB charset utf8;
load data local infile 'F:\order_info_utf.csv' into table demo.orders # 加载目标文件
fields TERMINATED by ','; # 设置文件分隔符
时间格式
SELECt payTime,
date(payTime), #只读取年月日
DATE_FORMAT(payTime,'%Y年%m月%d日'), # 格式的转换
DATE_ADD(payTime,INTERVAL 1 day), #加1天
DATE_ADD(payTime,INTERVAL -1 week), # 回溯1周
DATE_ADD(payTime,INTERVAL 1 month), # 加一周
datediff(payTime,curdate()) #payTime 减 今天
from orders
group by date_format(payTime,'%Y%m'), #按年月分组
month(payTime) # 按月分组
limit 5
1060
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
