风君子博客

  • 首页
  • 业界
  • 前端
  • 运维
  • 建站
  • 软件
  • 生活
  • 后端
  • 创投
  • 运营
  • 程序人生
    • 影视
    • 游戏
    • 句子
    • bootstrap4教程
    • 资源
    • 日期计算器
  • 其他
    • 说说
    • 关于本站
  1. 首页
  2. 后端
  3. 正文

mysql学习笔记整理

2018年8月29日 939点热度 0人点赞 0条评论

作者:Poetry 转自:http://blog.poetries.top/2016/05/26/mysql%E7%AC%94%E8%AE%B0%E6%95%B4%E7%90%86/

 

第一部分:DML--数据库操作语言

常用表管理语句:

set names gbk;--设置字符编码
查看所有数据库:show databases;
查看所有表:show tables
查看表结构:desc 表名/视图名
use 表名;--选择表
查看建表过程:show create table 表名
查看建视图过程:show create view 视图
查看所有详细表信息:show table status\G(让结果显示好看一些)
查看某张表详细信息:show table status where name='goods(表名)'\G
删除表:drop table 表名
删除视图:drop view 视图名;
删除列:alter table drop column 指定列
改表名:rename table oldName to newName
更新表:update 表名 set 字段
插入数据:insert into 表名 value()
清空数据:truncate 表名;(相当于删除表在重建)
写错语句退出:\c
让结果显示好看一些:\G

insert:

insert into 表名 插入列与值要严格对应

数字不必加单引号 字符串必须加单引号

例子:insert into test(age,name)values(10,'小明');

update操作:

例子:update user set age=8 where name=lianying;(注意where条件不加会影响所有行,需要小心)

delete操作:

不可能针对某一列删除 要删必须一行
delete from 表名 where 添加

delete from user where uid=1;(必须加上添加,否则全部数据删除)

select查找:

select * from 表名(全部查出)
select uid,name from user where uid>10;
select * from user where uid=11;

select查询模型(重要):

select * from 表名 where 1(where是一个表达式 为真则取出来 为假不取)

把列看成变量,既然是变量就能参与运,。这个过程称为广义投影(比如:取出两列参与运算) 也可以带到函数里面计算

查询练习:

子查询可以查出一个栏目下所有字栏目的商品
模糊查询:where 字段 like '%A%' --%代表任意字符 _代表单一字符

注意:NULL:查询方法: select * from test where name is (not)null

group分组与统计函数:

统计函数:
max()
count()
avg()
min()
sum()

在sql中重复要用的语句:用as名字新变量,以便调用

select goods_id,goods_name,(market_price-shop_price) as save from goods

having -- 对查询出来的结果集进行筛选
where 不能写在having后面

order by :(默认升序asc) 降序asc(排序很浪费资源)
select goods_id,goods_name,shop_price from goods order by shop_price desc;

limit用法:(做分页类能用到)

限制取出条目(limit有两个参数 :偏移量 取出的条目)

select goods_id,goods_name,shop_price
-> from goods
-> order by shop_price desc
-> limit 0,3;

子句的查询陷阱:

5种语句有严格的顺序,where ,group by,having,order by,limit
不能颠倒顺序

例子: #语句有严格的顺序

mysql> select id,sum(num)
-> from
-> (select * from a union select * from b) as temp
-> group by id
-> having sum(num)>10
-> order by sum(num) desc
-> limit 0,1;

子查询:

where字查询:(内层的查询结果作为外层的比较条件)

静态的:select goods_id,goods_name from goods where goods_id=32;
动态的:select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);

#取出每个栏目下最新的商品:
select goods_id,cat_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);

from子查询:
#每个栏目下最新的商品:
mysql> select goods_id,goods_name from (select * from goods where 1 order by cat_id desc) as tmp
-> group by cat_id;

exists子查询:

#查询栏目下是否有商品
mysql> select * from category
-> where exists(select * from goods where goods.cat_id=category.cat_id)

内连接查询(重要):

内连接是左右连接结果的交集

select xxx from
table1 inner jion table2 on table1.xx=table2.xx

mysql> select boy.hid,boy.bname,girl.hid,girl.gname
-> from
-> boy inner join girl on boy.hid=girl.hid;

左连接特点:

以左表的数据为标准,去找右表的数据,查不到的为NULL

#左连接
mysql> select boy.hid,boy.bname,girl.hid,girl.gname
-> from
-> boy left join girl on boy.hid=girl.hid;

#右连接
mysql> select boy.hid,boy.bname,girl.hid,girl.gname
-> from
-> boy right join girl on boy.hid=girl.hid;

mysql> select goods_id,cat_name,goods_name,shop_price
-> from
-> goods left join category on goods.cat_id= category.cat_id
-> where goods.cat_id=4;

union查询:
把2条或多条的额查询结果,合并成1个结果集

sql1 N行
sql2 M行
sql1 union sql2,N+M行

union语句必须满足一个条件:各语句取出的列数要相同

union语句中不用写order by 因为sql合并后得到总的结果集可以order by 字句order by失去意义

场景:2条语句,各自的where非常复杂,可以简化成简单的条件在union

注意:使用union时,完全相等的行将会被合并
合并是比较耗时的操作,一般不让union合并,使用union all 可以避免合并 对速度有提升

mysql> select * from a
-> union all #union all 可以避免重复语句合并
-> select * from b;

mysql> select goods_id,cat_id,goods_name,shop_price from goods where cat_id=2
-> union
-> select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4;

第二部分:DDL数据库定义语言(建表)

create table 表名 (
列1 列类型 [列属性 默认值]
列2 列类型 [列属性 默认值]
...
);
engine = 存储引擎
chartset = 字符集

建表过程:声明表头的过程,也就是声明列的过程

选择合理的列类型 合理的列宽度(即放下内容 又不浪费磁盘空间)

列选什么类型的列 列给什么样的属性

数值型--整形,浮点型,定点型

字符串型--char varchar text

日期时间类型--2012-12-13 14.25.36

整形列:

类型: 字节: 最小值: 最大值:

bigint -- 8字节 -9223372036854775808 18446744073709551615
int -- 4字节 --2147483648 4294967295
mediunint -- 3字节 -8388608 8388607
smallint -- 2字节 -32768 32767
tinyint -- 1字节 -128 127

整行列的可选参数:

unsigned 无符号,列的值从0开始不为负
zerofill M(宽度)适合用于 学号 编码等固定宽度的数字,可以用0填充至固定宽度

学号:1--0001

注意:zerofill属性默认决定是unsigned

浮点列与定点列:

float(M,D)M是精度总位数 D代表小数点后面的位数
double

float/double 范围区别和decimal相比:浮点数存储有精度的损失

decimal 定点型更精确

字符型列:

char(M)--char(10)只能存10个字符

char型:如果不够M个字符,内部会用空格补齐,取出时在把右侧空格删掉
注意:这意味着 右侧本身有空格将会丢失

varchar(M)--用多少占多少--自动扩展
varchar不会丢失空格

速度上:定长char快一些 在一定范围内用char定长寻址快 速度快
M比较短20个以内用char

text:存大段文本
blob:是二进制类型 用来存图像信息 音频等二进制信息
blob意义在于防止因为字符集的问题导致信息丢失

enum枚举类型:是定义好 值就在某几个枚举范围内
gender emum('男','女') insert 只能选其中之一

日期时间类型:
year:存年份
date:存年份日期2016-18
time:存时分秒
datetime:年月日时分秒

mysql> create table t8(
-> ya year,
-> dt date,
-> tm time,
-> dttm datetime);
-> insert into t8 (ya,dt,tm) values(2015,'2015-12-18','18:28:36');

列的默认值:

1、NULL查询不方便
2、NULL索引效率不高
3、实际中避免列的值为NULL

如何避免:声明列NOT NULL default默认值

mysql> create table t10(
-> id int not null default 0,
-> name char(10) not null default ''
-> );

主键与自增:

主键primary key 此列不重复,能区分每一行

primary key,auto_increment(一般那两个一起出现)

注意:一张表列只能有一列为auto_increment 且此列必须加索引(index key)

优化:
定长(char)与变长(varchar)分离
常用与不常用列分离

能提高表的查询效率

列的删除与增加:(列的增删改)

alter table 表名 add 列名 列类型 列属性 默认在表的最后
alter table 表名 drop column 指定列--删除列
alter table 表名 add列名 列类型 列属性 [after 指定列的后面]

alter table 表名 change height(要修改的) shengao(被修改后的) smallint
alter table 表名 modify 列名 要改成的新的属性

#alter table t12 modify shengao bigint;

视图:(存储的都是语句)
view被称为虚拟表,view是sql语句的查询结果(物理表的一个映射结果,物理表一改变,视图表也改变)

view好处:
1、权限控制可用:
比如某几个列允许用户查询,其他不允许
可通过视图开放其中一列或几列,起到权限控制作用

2、简化复杂的查询

3、视图能更新?
如果视图的每一行是与物理表一一对应的可以
view的行是由物理表多行经过计算得到的结果,view不可以更新

视图的algorithm:

对于检查查询形成的view,在对view查询时,如order by where
可以把建视图语句+查视图的语句===合并成==>查物理的语句
这种视图的算法叫merger(合并)

引擎的概念:

mysql 5.0以上默认的引擎是innoDB 一般建表时指定引擎

myisam引擎存储的数据可以直接考出来拿去用
innDB要把数据导出来

myisam和innDB引擎区别:

mysiam innDB

批量插入的速度: 高 低
存储限制: 没有 64TB

字符集与乱码问题:

字符集、校对集(排序规则)、乱码

文字本来的字符集与展示的字符集不一致导致

客户端编码设置:set names gbk/utf8;
表设置编码:create table ()charset utf8;
服务器端utf8/gbk 都可
网页的话:mate:charset=utf8;

索引:

索引是数据的目录,能快速定位行数据的位置
索引提高了查询的速度,降低了增删改的速度,并非越多越好
一般在查询频率的列上加,而且在重复低列上加效果好

key 普通索引
unique key 唯一键
primary key 主键索引
fulltext 全文索引(在中文环境下几乎无效,一般用第三方解决方案:如sphinx)

索引长度:建索引时,可以只索引列的前一部分的内容比如:前十个字符 key email(email(10));

多列索引:就是把2列或者多列的值,看成一个整体,然后键索引

冗余索引:在某个列上可能存在多个索引

索引操作:

查看索引:show index from goods\G
删除索引:alter table 表名 drop index 索引名
或者:drop index 索引名 on 表名

添加:alter table 表名 add [index \unqiue]索引名(列名)

添加主键索引:alter table 表名 add primary key 列名
删除主键索引:alter table 表名 drop primary key

常用函数:

一、数学函数

abs(x) 返回x的绝对值
bin(x) 返回x的二进制(oct返回八进制,hex返回十六进制)
ceiling(x) 返回大于x的最小整数值
exp(x) 返回值e(自然对数的底)的x次方
floor(x) 返回小于x的最大整数值
greatest(x1,x2,...,xn)返回集合中最大的值
least(x1,x2,...,xn) 返回集合中最小的值
ln(x) 返回x的自然对数
log(x,y)返回x的以y为底的对数
mod(x,y) 返回x/y的模(余数)
pi()返回pi的值(圆周率)
rand()返回0到1内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
round(x,y)返回参数x的四舍五入的有y位小数的值
sign(x) 返回代表数字x的符号的值
sqrt(x) 返回一个数的平方根
truncate(x,y) 返回数字x截短为y位小数的结果

二、聚合函数(常用于group by从句的select查询中)

avg(col)返回指定列的平均值
count(col)返回指定列中非null值的个数
min(col)返回指定列的最小值
max(col)返回指定列的最大值
sum(col)返回指定列的所有值之和
group_concat(col) 返回由属于一组的列值连接组合而成的结果

三、字符串函数

ascii(char)返回字符的ascii码值
bit_length(str)返回字符串的比特长度
concat(s1,s2...,sn)将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(s)返回字符串str中的字符数
ltrim(str) 从字符串str中切掉开头的空格
position(substr,str) 返回子串substr在字符串str中第一次出现的位置
quote(str) 用反斜杠转义str中的单引号
repeat(str,srchstr,rplcstr)返回字符串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中所有字符转变为大写后的结果

四、日期和时间函数

curdate()或current_date() 返回当前的日期
curtime()或current_time() 返回当前的时间
date_add(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:selectdate_add(current_date,interval 6 month);
date_format(date,fmt) 依照指定的fmt格式格式化日期date值
date_sub(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:selectdate_sub(current_date,interval 6 month);
dayofweek(date) 返回date所代表的一星期中的第几天(1~7)
dayofmonth(date) 返回date是一个月的第几天(1~31)
dayofyear(date) 返回date是一年的第几天(1~366)
dayname(date) 返回date的星期名,如:select dayname(current_date);
from_unixtime(ts,fmt) 根据指定的fmt格式,格式化unix时间戳ts
hour(time) 返回time的小时值(0~23)
minute(time) 返回time的分钟值(0~59)
month(date) 返回date的月份值(1~12)
monthname(date) 返回date的月份名,如:select monthname(current_date);
now() 返回当前的日期和时间
quarter(date) 返回date在一年中的季度(1~4),如select quarter(current_date);
week(date) 返回日期date为一年中第几周(0~53)
year(date) 返回日期date的年份(1000~9999)

一些示例:

获取当前系统时间:select from_unixtime(unix_timestamp());
select extract(year_month from current_date);
select extract(day_second from current_date);
select extract(hour_minute from current_date);
返回两个日期值之间的差值(月数):select period_diff(200302,199802);
在mysql中计算年龄:
select date_format(from_days(to_days(now())-to_days(birthday)),'%y')+0 as age from employee;
这样,如果brithday是未来的年月日的话,计算结果为0。
下面的sql语句计算员工的绝对年龄,即当birthday是未来的日期时,将得到负值。
select date_format(now(), '%y') - date_format(birthday, '%y') -(date_format(now(), '00-%m-%d') 100,'true','false');
if()函数在只有两种可能结果时才适合使用。然而,在现实世界中,我们可能发现在条件测试中会需要多个分支。在这种情况下,mysql提供了case函数,它和php及perl语言的switch-case条件例程一样。
case函数的格式有些复杂,通常如下所示:
case [expression to be evaluated]
when [val 1] then [result 1]
when [val 2] then [result 2]
when [val 3] then [result 3]
......
when [val n] then [result n]
else [default result]
end

这里,第一个参数是要被判断的值或表达式,接下来的是一系列的when-then块,每一块的第一个参数指定要比较的值,如果为真,就返回结果。所有的when-then块将以else块结束,当end结束了所有外部的case块时,如果前面的每一个块都不匹配就会返回else块指定的默认结果。如果没有指定else块,而且所有的when-then比较都不是真,mysql将会返回null。
case函数还有另外一种句法,有时使用起来非常方便,如下:
case
when [conditional test 1] then [result 1]
when [conditional test 2] then [result 2]
else [default result]
end

这种条件下,返回的结果取决于相应的条件测试是否为真。

示例:

mysql>select case 'green'
when 'red' then 'stop'
when 'green' then 'go' end;
select case 9 when 1 then 'a' when 2 then 'b' else 'n/a' end;
select case when (2+2)=4 then 'ok' when(2+2)<>4 then 'not ok' end asstatus;
select name,if((isactive = 1),'已激活','未激活') as result fromuserlogininfo;
select fname,lname,(math+sci+lit) as total,
case when (math+sci+lit) < 50 then 'd'
when (math+sci+lit) between 50 and 150 then 'c'
when (math+sci+lit) between 151 and 250 then 'b'
else 'a' end
as grade from marks;
select if(encrypt('sue','ts')=upass,'allow','deny') as loginresultfrom users where uname = 'sue';#一个登陆验证

七、格式化函数

date_format(date,fmt) 依照字符串fmt格式化日期date值
format(x,y) 把x格式化为以逗号隔开的数字序列,y是结果的小数位数
inet_aton(ip) 返回ip地址的数字表示
inet_ntoa(num) 返回数字所代表的ip地址
time_format(time,fmt) 依照字符串fmt格式化时间time值
其中最简单的是format()函数,它可以把大的数值格式化为以逗号间隔的易读的序列。

示例:

select format(34234.34323432,3);
select date_format(now(),'%w,%d %m %y %r');
select date_format(now(),'%y-%m-%d');
select date_format(19990330,'%y-%m-%d');
select date_format(now(),'%h:%i %p');
select inet_aton('10.122.89.47');
select inet_ntoa(175790383);

八、类型转化函数

为了进行数据类型转化,mysql提供了cast()函数,它可以把一个值转化为指定的数据类型。类型有: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
found_rows() 返回最后一个select查询进行检索的总行数
user()或system_user() 返回当前登陆用户名
version() 返回mysql服务器的版本

示例:

select database(),version(),user();
selectbenchmark(9999999,log(rand()*pi()));#该例中,mysql计算log(rand()*pi())表达式9999999次。

标签: mysql
最后更新:2018年8月29日

风君子

独自遨游何稽首 揭天掀地慰生平

点赞
< 上一篇
下一篇 >

猜你喜欢

  • 万里数据库:网传“受 MySQL 制裁或断供”为不实信息

  • 安装zabbix时,报错Unsupported charset or collation for tables:

  • destoon搬家更换服务器后登陆后台提示mysql的table is read only解决方法

  • 腾讯云数据库MySQL 8.0正式上线 事关游戏、网购等场景

  • 黑名单、白名单完全不能用了!MySQL、Twitter等纷纷删除

  • MySQL 删除 master、黑名单白名单等术语

  • MySQL的InnoDB引擎中事务日志ib_logfile0和ib_logfile1详解

  • 浅谈mongodb和mysql的区别和具体应用场景

  • MySQL 数据库规范

  • typecho更换服务器后进后台提示500 数据库错误Database Query Error 解决方法

  • mysql中Table is read only的解决方法小结

  • 2019年3 月数据库排行:MySQL 指数持续大涨,PostgreSQL 下跌

文章评论

取消回复

风君子

独自遨游何稽首 揭天掀地慰生平

最新 热点 随机
最新 热点 随机
光峰科技2022半年报:核心器件业务和C端自有品牌业务增长强劲 小米 10 至尊纪念版、Redmi Note 9 等 8 款机型 10 月底停止 MIUI 开发版内测公测 长安深蓝及供应商响应限电号召暂时停产, 尽可能保障 SL03 月底交付 LG 在欧洲发布新款 Ultra PC 笔记本,搭载锐龙 5000U 处理器 詹姆斯・韦伯望远镜竟然运行 JavaScript 脚本,还是 2003 年的老版本 Shazam:一个苹果 iPhone 用户可能不会注意的功能,今天迎来了 20 周年纪念日 微星推出新款 ITX 主板,内置英特尔 i5-11260H 消息称曹操出行最快明年申请在港 IPO 上市,官方回应:不予置评 米哈游旗下《崩坏:星穹铁道》将亮相科隆游戏展 动画电影《小黄人大眼萌:神偷奶爸前传》今日内地上映,结局修改
工行孩子王baby年费可以免吗?得看你消费能力够不够信用卡透支额度是什么 要怎么计算工行星级评定标准 这些知识你必须掌握统信 UOS 宣布成为国内首个 50 万 + 操作系统生态适配组合索尼宣布《如龙》系列 8 款游戏将于今年年内加入 PS+ 会员游戏库建行龙卡喜马拉雅fm信用卡制卡要多久?阿里云盘 iOS 版 3.9.0 更新:新增共享相簿,新版传图识字,iPad 支持扫码登录联想晒拯救者 Y70 手机背面照:7.99mm 轻薄机身,CNC 金属中框罗技宣布与腾讯合作,年内推出云游戏掌机2018交通银行小白卡刷卡礼是什么?满额就送
黑暗之魂3黑暗剑最强变质属性图文数据详解 Win10专业版麦克风设置没有增强功能解决方法 同为5nm旗舰 华为Mate40 Pro遇上iPhone 12 Pro:来看拆解对比 QQ浏览器看小说怎么屏蔽广告 广发突然降额怎么补救 车泡水了保险赔吗 商业保险报销需要什么材料 MIUI13纯净模式怎么关闭 12亿人用微信一年“扫码”8.58万亿元,约占我国GDP 9% 齐鲁银行无忧贷介绍
标签聚合
华为 特斯拉 投资理财 快科技 贷款 微软 iphone 电动汽车 额度 信用卡 理财知识 银行卡 马斯克 小米 三星 利息 手机 芯片 秘籍 美国 支付宝 汽车 借款人 腾讯 IT资讯 银行 京东 苹果 股票 科技
注意事项 病虫 办法 品种 常见 水培 新买 家庭 处理 怎么 作文 什么时候 家里 回事 阳台 大全 原因 作用 什么 冬天 叶子发黄 防治 区别 适合 发黄 盆景 吊兰 虫害 有哪些 开花 栀子花 养花 植物 老舍 时间 君子兰 治方 图片 功效 绿萝 叶子 兰花 肥料 室内 怎么办 盆栽 烂根 方法 多久 花语
Tags
酒 胡萝卜 食物 私房菜 有什么好处 水果 能吃 鸡蛋 身体 会胖吗 副作用 吃什么 相克 好处 饮食 作用 土豆食谱 方法 牛肉 豆腐 糖 热量 茄子 好吃 营养 皮肤过敏 区别 菜谱 牛肉食谱 功效 怎么做 营养价值 豆腐食谱 维生素 土豆 热菜 食用方法 常吃 健康 排骨 汤类 高血压 牛奶 糖尿病人 做法 吃法 下饭菜 海鲜 一起吃 排骨食谱
最近评论
youyou 发布于 3 周前(07月30日) 站长,文章底部相关文章使用的是哪款插件,方便提供下吗,可发邮箱,谢谢!
youyou 发布于 3 周前(07月30日) 你好,咨询下你的文章后面的相关文章采用的是哪个插件,谢谢!
www 发布于 1 个月前(07月19日) 听!我知道你们不会相信这一点,但我得到了这个 Minecraft 升级代码并且它被赎回了!你也可以从...
鲜盖 发布于 2 个月前(06月24日) 感谢你的博客页!完美解决了困扰我的滚动条问题!
仍搜 发布于 2 个月前(06月07日) 你好换个友情链接吧
九九 发布于 4 个月前(05月06日) 也可能是 IP2LOCATION 数据
风君子 发布于 8 个月前(12月20日) 直播这么赚钱的吗?
wanif 发布于 8 个月前(12月18日) 采用你提供的pe制作u盘后无法识别mac硬盘,如何解决?
robotunl 发布于 8 个月前(12月18日) 可以用了,感谢
robotunl 发布于 8 个月前(12月17日) DISM++链接下不了了啊,能再发一个么。 还有怎么制作和博主一样的PE u盘。

COPYRIGHT © 2022 风君子博客 ALL RIGHTS RESERVED.

豫ICP备14018241号