场景

在一个内存只有 700M 左右的服务器上面使用 git push 一个带 submodule 的项目的时候,出错:


fatal: Out of memory, malloc failed (tried to allocate 79691777 bytes)

当停止一些占用内存的服务之后,发现又可以正常 push。Stackoverflow 之后,解决办法有:添加内存或添加 swap 空间。cat /proc/swaps 之后发现服务器没有设置 swap 空间。

swap 简介

计算机的内存分为物理内存和虚拟内存。物理内存,即实际内存大小(RAM);虚拟内存,是由磁盘虚拟出来的,在内存不够的时候可以起到一个代替物理内存的作用,也就是 swap。

当产生一个新进程的时候,机器会判断当前物理内存是否有空闲运行该进程,如果没有,则会根据优先级将物理内存中运行的一个或多个进程挂起,并将挂起的进程放到 swap 中等待,然后,把这个新进程放入物理内存中运行。等到物理内存中有进程结束释放空间之后,再将在 swap 中挂起等待的进程调到物理内存中运行。

当物理内存使用达到一个比例之后,机器就会使用 swap 作为临时内存使用。这个比例保存在 /proc/sys/vm/swappiness 中,大小从 0 到 100。0,表示最大限度使用内存。更改该比例的大小:


echo 60 > /proc/sys/vm/swappiness

当物理内存和 swap 内存都使用完的时候,就会出现 out of memory 之类的错误。

添加/开启/关闭 swap

创建一个 swap 文件


dd if=/dev/zero of=/root/swap1 bs=1M count=1024

执行完之后就会产生文件 /root/swap1

为了安全起见,设置一下文件的权限


chmod 600 /root/swap1

将文件变为 swap 文件


mkswap /root/swap1

开启 swap


swapon /root/swap1

该命令还可以有其它参数,如 -p 设定 swap 的优先级。

使 swap 开机生效

/etc/fstab 文件主要存放文件系统的静态信息。在文件中添加:


/root/swap1 none swap defaults 0 0

各个参数之间可以用空格或 tab 键隔开。

关闭 swap


swapoff /root/swap1

参考 unix/linux: 2 ways to add swap space

Expect, an extension to the Tcl (Tool Command Language) scripting language, and it is a scripting language to interface with programs such as FTP, telnet, fsck, ssh, and others that normally cannot be automated from a shell script.

使用情景

Expect 可以实现自动交互的功能,所以,很常用的一种情景就是为我们自动输入密码。如:ssh 登录,git push/pull 等情景。

安装

  • Ubuntu

sudo apt-get install expect
  • CentOS

sudo yum install expect

Expect 需要 Tcl 编程语言的支持,所以必须先安装 Tcl。

实例

Expect 脚本使用的是 Tcl 的语法

  • 批量更新 git 仓库的内容(git pull)

#!/usr/bin/expect set password "123456" foreach app { /root/app/ic/ic-server/ /root/app/ic/ic-client/ } { cd $app spawn git pull expect { "Username*" {send "lizs\r"} "*assword*" { send $password interact } } }

更多使用参考 Expect-wiki.

Linux Desktop Entry 文件以 .desktop 为后缀,保存了程序启动配置信息。类似 Windows 下的快捷方式。Desktop Entry 文件一般存放在 /usr/share/applications 目录下。

jetbrains-phpstorm.desktop


[Desktop Entry] Version=1.0 Type=Application Name=PhpStorm Icon=/opt/PhpStorm-2016.2/bin/webide.png Exec="/opt/PhpStorm-2016.2/bin/phpstorm.sh" %f Comment=Develop with pleasure! Categories=Development;IDE; Terminal=false StartupWMClass=jetbrains-phpstorm

配置参数

  • Version:可选,版本号

  • Type:必选,Desktop Entry 文件的类型。常见类型有 ApplicationLink

    • Application:表示当前 Desktop Entry 文件为一个应用程序

    • Link:表示当前 Desktop Entry 文件指向一个 URL

  • Encoding:可选,指定当前 Desktop Entry 文件中字符串的编码方式

  • Name:必选,应用程序名称。在 GUI 中看到的是该名称,而不是文件名称。如 jetbrains-phpstorm.desktop 看到是名称是 PhpStorm,而不是 jetbrains-phpstorm

  • Comment:可选,当前应用的描述

  • Exec:可选,只有在 Type=Application 时,才有意义。表示启动应用程序要执行的命令

  • URL:可选,只有在 Type=URL 时,才有意义。定义该 Desktop Entry 文件指向的 URL

  • Icon:可选,图标

  • Categories:可选,只有在 Type=Application时,才有意义。指定该应用程序在菜单中显示的类型

  • Terminal:可选,布尔值,指定该应用程序是否需要在 Terminal 中运行。

更多配置参数可以参考 Desktop Entry Specification

Linux 系统的服务脚本放在 /etc/init.d 目录下。所以,想要添加服务,首先需要编写可执行服务的脚本,然后放到该目录下。具体编写,可以参照 /etc/init.d/ 目录下面的已有服务。

CentOS

Redhat 提供了 chkconfig 命令来管理系统的服务:

  • chkconfig --list:所有服务列表

  • chkconfig --list serviceName:只显示 serverName 服务

  • chkconfig serviceName on:开启 serverName 服务的自动启动

  • chkconfig serviceName off:关闭 serverName 服务的自动启动

  • chkconfig --add serviceName:添加服务

  • chkconfig --del serviceName:删除服务

Ubuntu

Ubuntu 是没有 chkconfig 命令的,而是提供了另外的命令来实现管理服务:

  • sudo update-rc.d serviceName defaults:添加服务,默认添加服务为自动启动,即 /etc/rc0.d//etc/rc6.d 各级别里面都添加了服务的启动软链接。

  • sudo update-rc.d serviceName remove:删除服务,默认会删除所有级别的服务,即彻底删除服务。

如果,你想要配置各级别的启动情况,可以删除相应级别目录里面的服务软链接。

  • systemctl daemon-reload:修改过服务之后,用该命令来重新加载服务。

调用请求说明

  • 请求 URL:https://api.mch.weixin.qq.com/mmpaymkttransfers/sendredpack

  • 是否需要证书:是

  • 请求方式: POST

请求参数(必填)

| 字段名 | 字段 | 示例值 | 类型 | 说明 |

|————|——–|————|———|———|

| 随机字符串 | nonce_str | 5K8264ILTKCH16CQ2502SI8ZNMTM67VS | String(32) | 随机字符串,不长于 32 位 |

| 商户号 | mch_billno | 1234567890 | String(32) | 微信支付的商户号 |

| 公众账号 appid | wxappid | wx1234567890abcdef | String(32) | 微信公众号的 appid (在mp.weixin.qq.com申请的) |

| 商户名称 | send_name | 京东商城 | String(32) | 红包发送者名称 |

|商户订单号 | mch_billno | 1234567890201606011234567890 | String(28) | 每个订单号必须唯一,组成:mch_id+yyyymmdd+10位一天内不能够重复的数字。|

| 用户 openid | re_openid | oxTWIuGaIt6gTKsQRLau2M0yL16E | String(32) | 接受红包的用户在 wxappid 下的 openid |

| Ip 地址 | client_ip | 192.168.0.1 | String(15) | 调用接口的机器的 IP 地址 |

| 付款金额 | total_amount | 1000 | int | 红包金额,单位是分。红包金额范围:¥1 ~ ¥200 |

| 红包发放总人数 | total_num | 1 | int | 红包发放总人数 |

| 红包祝福语 | wishing | 恭喜发财 | String(128) | 红包祝福语 |

| 活动名称 | act_name | 拜年 | String(32) | 活动名称 |

| 备注 | remark | 备注 | String(256) | 备注信息 |

| 签名 | sign | 5K8264ILTKCH16CQ2502SI8ZNMTM67VS | String(32) | 生成的签名 |

数据示例:



<xml> <sign><![CDATA[E1EE61A91C8E90F299DE6AE075D60A2D]]></sign> <mch_billno><![CDATA[0010010404201411170000046545]]></mch_billno> <mch_id><![CDATA[888]]></mch_id> <wxappid><![CDATA[wxcbda96de0b165486]]></wxappid> <send_name><![CDATA[send_name]]></send_name> <re_openid><![CDATA[onqOjjmM1tad-3ROpncN-yUfa6uI]]></re_openid> <total_amount><![CDATA[1000]]></total_amount> <total_num><![CDATA[1]]></total_num> <wishing><![CDATA[恭喜发财]]></wishing> <client_ip><![CDATA[127.0.0.1]]></client_ip> <act_name><![CDATA[拜年]]></act_name> <remark><![CDATA[备注]]></remark> <nonce_str><![CDATA[50780e0cca98c8c8e814883e5caa672e]]></nonce_str> </xml>

实现步奏

  • 获取随机字符串

/** * 获取指定长度的随机字符串 * ASCII 码: a-z: 97-122; A-Z: 65-90 * @param int $length * @return string */ public function getRandom($length = 32) { $nonce_str = ''; for ($i=0; $i<$length; $i++) { $random = rand(0, 61); $c = $random < 10 ? rand(0, 9) : chr(rand(1, 26) + rand(0, 1)*32 + 64); $nonce_str .= $c; } return $nonce_str; }
  • 整合请求发送的数据

/** * 微信发送红包的请求数据(除了签名参数 sign) * @return array */ public function angPaoData() { // 活动名称 $act_name = 'act_name'; // 备注 $remark = 'remark'; $data = array( 'nonce_str' => getRandom(32), 'mch_billno' => $mch_billno.date('YmdHis').rand(1000, 9999), 'mch_id' => $mch_billno, // 商户号 'wxappid' => $wxappid, // 公众号 appid 'send_name' => $send_name, // 商户名称 're_openid' => $open_id, // 用户 openid 'total_amount' => $total_amount, // 红包金额 'total_num' => 1, // 红包发放人数 'wishing' => $wishing, // 红包祝福语 'client_ip' => $client_ip, // 当前客户端 IP 地址 'act_name' => $act_name, 'remark' => $remark, ); return $data; }
  • 签名算法

function getSign($data) { global $_W; ksort($data, SORT_STRING); $stringA = ''; // 第一步,将所有发送的参数按照 key=value 的格式组成字符串 stringA, // 并且 key 要按照 ASCII 码从小到大排序(字典序) foreach ($data as $k => $v) { if ($k && $v && $k != 'sign') { $stringA .= "{$k}={$v}&"; } } // 第二步,在 stringA 最后拼接上 key 得到 stringSignTemp 字符串, // 并对 stringSignTemp 进行 MD5 运算,再将得到的字符串所有字符转换为大写 // key 设置路径:微信商户平台(pay.weixin.qq.com)-->账户设置-->API安全-->密钥设置 $setting = uni_setting($_W['uniacid'], array('payment')); $key = $setting['payment']['wechat']['apikey']; $stringSignTemp = $stringA . "key=" . $key; return strtoupper(md5($stringSignTemp)); }
  • 发送数据的时候,不要忘记了将数据转换成指定的 xml 格式

/** * 将数据转换成符合传送要求的 xml 格式 * @param $data * @return string */ function array2xml($data) { $xml = "<xml>"; foreach ($data as $k => $v) { $xml .= "<" . $k . "><![CDATA[" . $v . "]]></" .$k . ">"; } $xml .= "</xml>"; return $xml; }
  • 获取微信支付证书文件

为了安全,一般将证书的内容保存到数据库中,使用的时候读取出来,保存到文件中去,用完之后,及时删除掉。


/** * 获取微信支付文件: * 1、apiclient_cert.pem * 2、apiclient_key.pem * 3、rootca.pem */ public function getPayFile() { // 从数据库中取出来 $sec = m('common')->getSec(); $certs = iunserializer($sec['sec']); if (is_array($certs)) { if (empty($certs['cert']) || empty($certs['key']) || empty($certs['root'])) { message('未上传完整的微信支付证书,请到【系统设置】->【支付方式】中上传!', '', 'error'); } $certfile = IA_ROOT . "/addons/sz_yi/cert/apiclient_cert.pem"; file_put_contents($certfile, $certs['cert']); $keyfile = IA_ROOT . "/addons/sz_yi/cert/apiclient_key.pem"; file_put_contents($keyfile, $certs['key']); $rootfile = IA_ROOT . "/addons/sz_yi/cert/rootca.pem"; file_put_contents($rootfile, $certs['root']); $extras['CURLOPT_SSLCERT'] = $certfile; $extras['CURLOPT_SSLKEY'] = $keyfile; $extras['CURLOPT_CAINFO'] = $rootfile; } else { message('未上传完整的微信支付证书,请到【系统设置】->【支付方式】中上传!', '', 'error'); } return @$extras ?: array(); }
  • 用 CURL 发送数据

/** * @param $url * @param $vars * @param int $second * @param array $aHeader * @return bool|mixed */ function curl_post_ssl($vars, $second=30, $aHeader=array()) { $url = 'https://api.mch.weixin.qq.com/mmpaymkttransfers/sendredpack'; $ch = curl_init($url); curl_setopt($ch, CURLOPT_TIMEOUT, $second); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false); //cert 与 key 分别属于两个.pem文件 //请确保您的libcurl版本是否支持双向认证,版本高于7.20.1 foreach ($this->getPayFile() as $k => $v) { curl_setopt($ch, constant($k), $v); } if( count($aHeader) >= 1 ){ curl_setopt($ch, CURLOPT_HTTPHEADER, $aHeader); } curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_POSTFIELDS, $vars); $data = curl_exec($ch); if($data){ curl_close($ch); return $data; } else { $error = curl_errno($ch); //echo "call faild, errorCode:$error\n"; curl_close($ch); return false; } }
  • 发送红包

/** * 发送红包 * @return bool|mixed */ public function angPaoPay() { // 红包请求数据 $data = $this->angPaoData(); $data['sign'] = $this->getSign($data); // 将数据转换成 xml 格式 $postXml = array2xml($data); $responseXml = $this->curl_post_ssl($postXml); // 为了证书安全,及时删掉 foreach ($this->getPayFile() as $file) { unlink($file); } return $responseXml; }

字符集 CHARACTE SET

字符(character)是各种文字和符号的总称,包括各国文字、标点符号、图形符号、数字等。

字符集(character set)是多个字符的集合,字符集种类较多,每个字符集包含的字符个数不同。

常见字符集:ASCII、GBK、UTF8、Unicode 等等。

MySQL 字符集转换过程

  • MySQL Server 收到请求时,将请求数据从 character_set_client 转换为 character_set_connection

  • 然后,character_set_connection 转换为 MySQL 内部操作字符集。MySQL 内部操作字符集:

    • 如果 column 指定了字符集,则使用 column 指定的字符集;

    • 如果 column 没有指定字符集,则使用 table 的字符集;

    • 如果 table 也没有指定字符集,则使用 database 的字符集;

    • 如果 database 也没有指定字符集,则使用 character_set_server 的值;

  • 最后,将操作结果从内部操作字符集转换为 character_set_results

常见问题

  • #### 乱码

    • 当 character_set_client 与事实不符的时候。比如,character_set_client 明明是 utf8 的,但是,将它当成 GBK 去转换。

    • 当 character_set_results 与客户端页面的字符集不一致的时候。

    • 当数据保存到数据库之后,改变了内部操作字符集或者 character_set_connection。比如:

数据表字符集设置为 utf8,插入时使用 MySQL 的默认设置(character_set_client、character_set_connectiong 和 character_set_results 都为 latin1)。插入之后,改变 character_set_connection 为 utf8.

插入时,转换过程:latin1 -> latin1 -> utf8。3 字节的 latin1 转换为 6 字节的utf8。

查询时,转换过程:utf8 -> utf8 -> latin1。6 字节的 utf8 原封不动返回。因此,肯定是会乱码的。

所以,如果插入数据到查询数据过程中没有更改过 MySQL 字符集,但是出现了乱码,一般情况都是页面字符集和 character_set_results 不一致造成的。

  • #### 数据丢失

字符集也有大小的,当 character_set_client < character_set connection 或者 character_set_connectino < 内部字符集(数据保存的字符集格式) 时,字符转换过程中,除了乱码,还可能会出现字符丢失。

字符集乱码还可以调回来,但是数据丢失是找不回来的。

MySQL 字符集设置

  • column

CREATE TABLE `member` ( `name` varchar CHARACTER SET utf8 ); ALTER TABLE `member` CHANGE `name` varchar(25) CHARACTER SET utf8;
  • table

CREATE TABLE `member` ( ... ) DEFAULT CHARSET utf8; ALTER TABLE `member` DEFAULT CHARSET utf8;

CHARSETCHARACTER SET 一样,CHARSET 是简写。

  • database

set character_set_database = utf8;
  • character_set_server

set character_set_server = utf8;
  • character_set_connection

set character_set_contion = utf8;
  • character_set_results

set character_set_results = utf8;

如果 character_set_clientcharacter_set_connectioncharacter_set_results 都一样时,可以用 set names 语句来代替:


set names utf8;

校对规则/校对集 COLLATE

  • 校对规则,字符集内用于比较字符的规则。

  • 一个字符集可以有多个校对规则。如,字符集 utf8 的校对规则:utf8_general_ci、utf8_bin 等等。

  • 不同的校对规则,字符集内的字符排序不一样。如,utf8 字符集用 utf8_general_ci 校对规则,A 排在 b 前面。但是如果用 utf8_bin 校对规则, b 会排在 A 前面。

  • 每个字符集有一个默认校对规则。如,utf8 默认校对规则为 utf8_general_ci。

  • 校对规则命名约定:以字符集名称开头,以 ci(大小写不敏感)、cs(大小写敏感)、或 bin(二元) 结尾。

MySQL 校对规则设置

  • column

CREATE TABLE `member` ( `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci ); ALTER TABLE `member` CHANGE COLUMN `name` CHARACTER SET utf8 COLLATE utf8_general_ci;
  • table

CREATE TABLE `member` ( ... ) DEFAULT CHARSET utf8 COLLATE utf8_general_ci; ALTER TABLE `member` DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

select 的5种子句介绍

    where:条件查询
    group by:分组
    having:筛选
    order by:排序
    limit:限制结果条数

where
    where 条件

where常用运算符:
    =:等于    <:小于    >:大于    <=:小于或等于    >=:大于或等于
    != 或 <>:不等于    in:在某集合内    between:在某范围内

where逻辑运算符:
    and 或 &&:与
    or 或 ||: 或 
    not 或 !: 非    

    in:在某集合内,是对一个集合来操作的。而between对某个范围内的。
in(值1,值2...值n)
//查询goods中id等于3、5或者6的商品
select id,name,price from goods where id in(3,5,6);
between 值1 and 值2
//查询goods中id在3-6这个范围的上面
select id,name,price from goods where id between 3 and 6;
或者:
select id,name,price from goods where id>=3 and id<=6;
优先级别:not > and > or
//查询2-4和5-7的商品
select * from goods where id>=2 and id<=4 or id>=5 and id<=7;

//not可以和in一起用
select * from goods where id not in(4,5);
等于:
select * from goods where id!=4 and id!=5;

模糊查询
    like
    %:通配任意字符
    _:通配单个字符

    注意,如果想要模糊查询,但是又没有通配符的时候,查询语句是没有报错的,

但是,查询的结果可能是空的。如,
select goods_id,goods_name from goods where goods_name like '诺基亚';
Empty set (0.00sec)
//查询结果是空的,因为good_name中有“诺基亚”的商品都是有后缀的,没有单独

的诺基亚商品,如诺基亚N85、诺基亚N96、诺基亚耳机等等。
所以,要加通配符
//查询任意字符用%,如查询有“诺基亚”的所有商品
select goods_id, goods_name from goods where goods_name like '诺基亚%'
//查询诺基亚N系列而且N后面是两个字符的商品,也就是Nxx系列。
select goods_id, goods_name from goods where goods_name like '诺基亚N__'

group by
    作用:把行按字段分组

    max:求最大
    min:求最小
    sum:求总和
    avg:求平均
    count:求总行数

//查询shop_price最大的商品的价格
select max(shop_price) from goods;
//下面的语句是没有意义的
select goods_id, goods_name max(goods_price) from goods;
    因为,goods_id和goods_name是第一列的值,而goods_price是最大值。查询出

来的结果goods_id、goods_name和goods_price是不搭配的。

//查询每个商品所积压的货款
select goods_id, goods_name, goods_number*shop_price from goods;
//查询该商店积压的总货款
select sum(goods_number*shop_price) from goods;

    我们的商品不但有goods_id号还有栏号cat_id,如果我们想查出每一栏目中价格

最贵的商品,怎么查
select cat_id, max(shop_price) from goods group by cat_id;
    如果加good_id,goodd_id也是没有意义的。
//按栏目查询最便宜的商品
select cat_id, min(shop_price) from goods group by cat_id;
//按栏目查询商品平均价格,也就是每一栏的平均价格
select cat_id, avg(shop_price) from goods group by cat_id;
//按栏目查询商品种类,也就是每个栏目下的商品种类
select cat_id, count(*) from goods group by cat_id;

//查询商品的平均价格
select avg(good_price) from goods;
//查询所有商品的数量,也就是求有多少行
select count(*) from goods;

    我们要养成一种思想:把列当成变量来看。
//查询出本店每个商品比市场价格低多少钱
select goods_id, goods_name, market_price - shop_price from goods;
    注意,显示出来的结果是会有market_price - shop_price这一列的。

//查询每个栏目下积压的货款,也就是库存*价格
select cat_id, sum(shop_price*good_number) from goods group by cat_id;
    我们可以看到查询结果有sum(shop_price*goods_number)这一列,不过列名实在是有点长,这时候,我们可以起一个别名,用as。
select cat_id, sum(shop_price*good_number) as hk from goods group by 

cat_id;
    这时候,我们查询结果看到的就是hk这一列代表积压货款。


having

//查询出本店价格比市场价格低多少钱,并且把低200元以上的商品选出来
//查询出本店价格比市场价格低多少钱,我们可以这样实现
select goods_id, goods_name, sum(market_price-goods_price) as cheap from goods;
//当你还想要把低于200元以上的商品选出来的时候,我们首先想到的是用where条件来实现,即
select goods_id, goods_name, sum(market_price-goods_price) as cheap from goods where cheap>200;
结果:ERROR 1054 <42S22>: Unkown column 'cheap' in 'where clause'
    注意,这样是不能够实习的!因为,where是对表起作用的,而不是对查询到的结果起作用。那么,我们要怎么样才能够实现:查询出本店价格比市场价格低多少钱,并且把低于200元以上的商品选出来呢?

    方法一:
select goods_id, goods_name, sum(market_price-goods_price) as cheap from goods where market_price-goods_price>200;
    这种方法可以实现,不过查询的时候计算了两次。注意,结果显示的是cheap列,而不是market_price-goods_price列。

    方法二:
    having,可以筛选查询的结果。
select goods_id, goods_name, sum(market_price-goods_price) as cheap from goods having cheap>200;

//当一条语句有where和having,where在前
//查询本店价格比市场价格低多少钱,并且把第3个栏目下比市场价格低于200元以上的商品选出来
select goods_id, cat_id, goods_name, sum(market_price-goods_price) as cheap from goods where cat_id=3 having cheap>200;

//查询积压货款超过2W元的栏目,以及该栏目积压的货款
//先查询栏目积压的货款
select cat_id, sum(goods_number*shop_price) as jyhk from goods group by cat_id;
//然后用having jyhk>2W筛选积压货款超过2W的栏目
select cat_id, sum(goods_number*shop_price) as jyhk from goods group by cat_id having jyhk>2W;

    总结:where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。


练习:设有成绩表grades如下:
姓名:张三  张三  张三  李四  李四  王五
科目:数学  语文  地理  语文  政治  政治
分数: 90    50    40    55    45    30
    查询两门及两门以上不及格同学的平均分。要求只用一个select。


//先创建表grades
create table grades (
name varchar(10) not null default '',
subject varchar(10) not null default '',
score tinyint not null default 0
);

//插入数据
insert into grades
values
('张三', '数学', 90),
('张三', '语文', 50),
('张三', '地理', 40),
('李四', '语文', 55),
('李四', '政治', 45),
('王五', '政治', 30);

mysql> select * from grades;
+--------+---------+-------+
| name   | subject | score |
+--------+---------+-------+
| 张三   | 数学    |    90 |
| 张三   | 语文    |    50 |
| 张三   | 地理    |    40 |
| 李四   | 语文    |    55 |
| 李四   | 政治    |    45 |
| 王五   | 政治    |    30 |
+--------+---------+-------+
6 rows in set (0.00 sec)

//先求每个人成绩的平均分
mysql> select name, avg(score) from grades group by name;
+--------+------------+
| name   | avg(score) |
+--------+------------+
| 张三   |    60.0000 |
| 李四   |    50.0000 |
| 王五   |    30.0000 |
+--------+------------+
3 rows in set (0.10 sec)

//再查找有两门及两门以上不及格的同学
select name, count(score<60) as fail from grades group by name having fail>=2;
+--------+------+
| name   | fail |
+--------+------+
| 张三   |    3 |
| 李四   |    2 |
+--------+------+
2 rows in set (0.04 sec)

//最后,合成一条select语句
select name, avg(score), count(score<60) as fail from grades group by name having fail>=2;
+--------+------------+------+
| name   | avg(score) | fail |
+--------+------------+------+
| 张三   |    60.0000 |    3 |
| 李四   |    50.0000 |    2 |
+--------+------------+------+
2 rows in set (0.00 sec)

    注意,这种做法看上去像是对的。其实,这是错误的做法。因为,查询结果显示张三挂了3科,其实他只挂了2科。count是计算所有行数,它不论大于还是小于60分的都计算了的。我们可以验证一下:
insert into grades
values
('赵六', '语文',99),
('赵六', '数学',98),
('赵六', '政治',97);

mysql> select * from grades;
+--------+---------+-------+
| name   | subject | score |
+--------+---------+-------+
| 张三   | 数学    |    90 |
| 张三   | 语文    |    50 |
| 张三   | 地理    |    40 |
| 李四   | 语文    |    55 |
| 李四   | 政治    |    45 |
| 王五   | 政治    |    30 |
| 赵六   | 语文    |    99 |
| 赵六   | 数学    |    98 |
| 赵六   | 政治    |    97 |
+--------+---------+-------+
9 rows in set (0.00 sec)

select name, avg(score), count(score<60) as fail from grades group by name having fail>=2;
+--------+------------+------+
| name   | avg(score) | fail |
+--------+------------+------+
| 张三   |    60.0000 |    3 |
| 李四   |    50.0000 |    2 |
| 赵六   |    98.0000 |    3 |
+--------+------------+------+
3 rows in set (0.00 sec)
    我们可以看到count(score<60)并不是计算score<60的行数,而是计算所有行的行数。因为
select name, score<60 from grades;
+--------+----------+
| name   | score<60 |
+--------+----------+
| 张三   |        0 |
| 张三   |        1 |
| 张三   |        1 |
| 李四   |        1 |
| 李四   |        1 |
| 王五   |        1 |
| 赵六   |        0 |
| 赵六   |        0 |
| 赵六   |        0 |
+--------+----------+
9 rows in set (0.00 sec)
    score<60的值不是0就是1,因为,score<60不是真就是假的。但是,无论是0还是1,score<60都有一行,而count(score<60)是计算score<60的行数。
    所以,我们可以计算score<60的值为1的行数来查看不及格的门数。
select name, avg(score), sum(score<60) as fail from grades group by name having fail>=2;
+--------+------------+------+
| name   | avg(score) | fail |
+--------+------------+------+
| 张三   |    60.0000 |    2 |
| 李四   |    50.0000 |    2 |
+--------+------------+------+
2 rows in set (0.02 sec)


order by

    order by:排序功能,按一个或多个字段对查询结果进行排序,可以是升序排序,也可以是降序排序,默认是升序排序。
select goods_id, cat_id, goods_name, shop_price from goods where cat_id=3 

order by shop_price;
    这条语句查询显示的结果是按照shop_price的值由小到大排序的(默认升序),如果我们想查询结果由降序排序,要怎么做呢?可以在后面加上字段名desc。
    desc:按降序排序; asc:按升序排序(默认也是升序排序,所以可以不加)
select goods_id, cat_id, goods_name, shop_price from goods where cat_id=3 order by shop_price desc;

//查询栏目号从低到高,而且栏目内的商品价格从高到低的排序结果
select goods_id, cat_id, goods_name, shop_price from goods where goods_id<50 order by cat_id, shop_price desc; //先按cat_id排序,再在栏目内

按shop_price从高到低排序

//按发布时间从早到晚排序(发布时间add_time是用时间戳来存储的)
select goods_id, goods_name, add_time from goods order by add_time;
    我们可以看到add_time的值是int型的,表示1970-01-01 00:00:00 到当前的秒数。

    我们可以按字段排序,查询显示的结果是符合条件所有行的数据。如果我们只是想要显示排序之后的某几行,又要怎么做呢?limit语句可以帮我们实现。
    limit [offset] [n]
    offset:偏移量,offset如果不写,则相当于0,即limit 0, n。所以,是从第offset+1个开始。如limit 3,2 表示偏移前3个,从第4个开始选择2个,即4、5
    n:取出的条目
//取出商品价格最贵的3个商品的信息
select goods_id, cat_id, goods_name,shop_price from goods order by shop_price desc limit 3;   
//取出最新发布的商品
select goods_id, cat_id, goods_name, add_time, shop_price from goods order by add_time desc limit 1;


//取出每个栏目下最贵的商品

错误一:
select goods_id, cat_id, goods_name, max(shop_price) from goods;
    这种做法查出来的是的确是shop_price最大的商品,不过shop_price不一定是和前面的goods_id, cat_id, goods_name是对应的。

错误二:
select goods_id, cat_id, goods_name, shop_price from goods group by cat_id order by shop_price desc;
    这种做法也能查出每个栏目下的一个商品来,不过这个商品不一定是每个栏目下最贵的商品,这个商品只是每个栏目下的第一个商品。仔细观察我们可以发现查询结果中shop_price列的价格是按照从大到小来排序的,这就说明了,这条语句查询的结果是把每个栏目下的第一个商品拿出来,后面的order by shop_price desc实现的是对拿出来的每个栏目下的第一个商品按照shop_price从大到小排序。

错误三:
    根据上面的做法,有的同学就会想到:如果我先实现 order by shop_price desc 再来实现group by不就行了吗。
select goods_id, cat_id, goods_name, shop_price from goods order by 

shop_price desc group by cai_id;
    这种做法也一样是不正确的,因为:
    select的5种子句:where、group by、having、order by、limit是按照顺序来使用的,不能把后面的子句放到前面来先使用。

错误四:
    有的同学可能会想,我先构造一张表goods1,表里面的数据是按照order by cat_id asc,shop_price desc顺序排列的,然后,再调用group by来取出每个栏目下的第一个商品不就行了吗。
//创建表goods1
create lzs.goods1 like goods;
//将表goods的数据按照order by cat_id,shop_price desc顺序保存到goods1
insert into goods1 select * from goods order by cat_id, shop_price desc;
//最后,用group by取出每个栏目下的第一个商品就可以了
select goods_id, cat_id, goods_name, shop_price from goods1 group by cat_id;
    这种做法也是错误的,因为在将表goods的数据按照order by cat_id asc,

shop_price desc顺序存进goods1的时候,保存进goods1的数据根本就没有按照想要的顺序排序,而是按照goods原来的顺序保存进来的。所以,最后group by取出来的数据还是不对的。

    其实,我们不用创建一张新表,因为查询的结果本来就可以在内存中当作表来使用,所以我们可以这样实现:
select * from (selcet goods_id, cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc) as goods1 group by cat_id;
    这种做法是from型子查询,但是,如果要求只用一个select,又要怎么去实现呢?


良好的理解模型:
    where 表达式:把表达式放在行中,看表达式是否为真
    列:理解成变量,可以运算
    取出结果:可以理解成一张临时表

子查询

    where型子查询:把内层查询的结果作为外层查询的比较条件
    from型子查询:把内层查询的结果当成临时表,供外层再次查询
    exists型子查询:把外层的查询结果,拿到内层,看内层的查询是否成立

//查询最新的商品(以id最大为最新)
select goods_id, cat_id, goods_name from goods order by goods_id desc limit 1;
    如果,不能用order by呢?又要怎么做?我们可以用where子查询来实现:
select goods_id, cat_id, goods_name from goods where goods_id = (select 

max(goods_id) from goods);

//查询每个栏目下最贵的商品
select goods_id, cat_id, goods_name shop_price from goods where shop_price in(select max(shop_price) from goods group by cat_id)
//上面我们用from型子查询是这样实现的:
select * from (select goods_id, cat_id, goods_name, shop_price from goods order by shop_price desc) as goods1 group by cat_id;
    from型子查询,是先select内层order by shop_price desc查询的结果,然后利用group by cat_id来取出第一个。注意,from型子查询必须要给内层查询的结果加上一个别名。
    而where型子查询,是选出每栏目下max(shop_price)的商品,如果一个栏目下有几个商品都是同价格的而且还是最贵的,这几个商品都是内层查询的结果来的,都是在in里面的值。所以,都是可以查询出来的。

//利用上面的grades表,如何用子查询查出挂科两门及两门以上同学的平均分
//不用子查询
select name, avg(score), sum(score<60) as fail from grades group by name having fail>=2;
//子查询
//先查出挂机两门及两门以上的同学
select name, sum(score<60) as fail from grades group by name having fail>=2;//不过,我们要的只是name,所以,还要选出name来:
select name from (select name, sum(score<60) as fail from grades group by name having fail>=2);
//最后根据名字查找平均分
select name, avg(score) from grades where name in(select name from (select name, sum(score<60) as fail from grades group by name having fail>=2) as tmp) group by name;
    注意,from型子查询必须要给内层查询的结果加上一个别名,所以,要as tmp

//或者
select name, avg(score) from grades where name in(select name from (select name, count(*) as fail from grades where score<60 group by name having fail>=2) as tmp) group by name;

//查询有商品的栏目(栏目表名category)
//我们先看一下,下面这条语句实现的是什么
select cat_id, cat_name from category where exists(select * form goods);
    显示的结果是:所有的cat_id,cat_name。因为,只要goods里面有内容,exists(select * from goods)就会为真,显示的结果就是所有的cat_id和cat_name。那么如果要查询有商品的栏目,我们只要将exists里面的判断设置为有商品就为真就可以了。
select cat_id, cat_name from category where exists(select * from goods where goods.cat_id=category.cat_id);
    exists(select * from goods where goods.cat_id=category.cat_id)的意思是,只要商品表goods里面有栏目表category相等的id就表示,有商品里面有该栏目的商品。


select的5中子句的总结:

    where 表达式
    表达式在哪一行成立,哪一行就取出来
    where常用运算符:=,!=/<>,<,>,<=,>=,in(),between and 
    where逻辑运算符:and、or、not
    模糊查询:like  通配符:%,任意字符;_,单个字符

    group by:
    分组,一般和统计函数配合使用
    常用的统计函数:max(),min(),avg(),sum(),count()

    having 表达式
    数据在表中,表在硬盘或者内存以文件形式存在。
    查询出的结果,也可以看成一张表,其文件一般临时存放在缓冲区。
    where,针对表文件发挥作用;
    having,针对查询结果发挥作用。

    order by:
    作用:对字段排序,可以升序asc,也可以降序desc。
    有可能一个字段排不出结果,可以选用其它字段继续排序:
    order by 字段1[asc/desc], 字段2[asc/desc] ...
    如,order by cat_id, shop_price desc

    limit:
    作用:限制条目
    limit [offset] n
    offset:偏移量,不填就是默认0
    n:限制取出的条目数量
    如,取出shop_price最贵的3个商品:
    ... order by shop_price desc limit 3;


select的子查询

    where型子查询:
    内层的查询结果作为外层查询的比较条件
    例如,查询最新商品(以goods_id最大为最新)
    select * from goods where goods_id = 最大的goods_id;
    select * from goods where goods_id = (select max(goods_id) from goods);

    from型子查询:
    把内层的查询结果供外层再次查询。
    注意,内层的查询结果看成临时表,要加'as 临时表名'

    exists型子查询:
    把外层的查询结果代入到内层,看内层是否成立。
    例如,查询有商品的栏目
    select cat_id, cat_name from category where exists(select * from goods.cat_id=category.cat_id);  
    如果select * from goods where goods.cat_id;的结果有值就说明 cat_id 有商品。


MySQL三大列类型:数值型、字符串型、日期时间类型。 一、数值型: 1、整形:(默认是有符号) tinyint:1字节,8位。无符号:0-255 有符号(0正1负):-128- 127 smallint:2字节,16位。无符号:0-2^16-1 有符号:-2^15- 2^15-1 mediumint:3字节,24位。 int:4字节,32位。 bigint:8字节,64位。 整形列的可选属性:(默认有符号) 类型(M) unsigned/zerofill M:宽度(在0填充的时候才有意义) unsigned:无符号类型(非负) zerofill:0填充(默认无符号) mysql> create table class ( -> id int primary key auto_increment, -> name varchar(10), -> age tinyint -> ) charset utf8; Query OK, 0 rows affected (0.32 sec) mysql> show tables; +---------------+ | Tables_in_lzs | +---------------+ | class | | messages | | shop | +---------------+ 3 rows in set (0.00 sec) mysql> desc class; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.03 sec) mysql> insert into class -> (name, age) -> values -> (a, 1), -> (b,2); ERROR 1054 (42S22): Unknown column 'a' in 'field list' //varchar型要用单引号括起来 mysql> insert into class -> (name, age) -> values -> ('a', 1), -> ('b',2); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from class; +----+------+------+ | id | name | age | +----+------+------+ | 1 | a | 1 | | 2 | b | 2 | +----+------+------+ 2 rows in set (0.00 sec) //增加一列,age1,类型是tinyint unsigned mysql> alter table class -> add -> age1 tinyint unsigned; Query OK, 0 rows affected (0.62 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc class; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | age1 | tinyint(3) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> delete from class where id=2; Query OK, 1 row affected (0.07 sec) mysql> select * from class; +----+------+------+------+ | id | name | age | age1 | +----+------+------+------+ | 1 | a | 1 | NULL | +----+------+------+------+ 1 row in set (0.00 sec) mysql> insert into class -> (name, age) -> values -> ('b', 2); Query OK, 1 row affected (0.06 sec) mysql> select * from class; +----+------+------+------+ | id | name | age | age1 | +----+------+------+------+ | 1 | a | 1 | NULL | | 3 | b | 2 | NULL | +----+------+------+------+ 2 rows in set (0.00 sec) mysql> update class set -> id=id-1 -> where id>1; Query OK, 1 row affected (0.17 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from class; +----+------+------+------+ | id | name | age | age1 | +----+------+------+------+ | 1 | a | 1 | NULL | | 2 | b | 2 | NULL | +----+------+------+------+ 2 rows in set (0.00 sec) mysql> insert into class -> (name, age1) -> values -> ('c', 3); Query OK, 1 row affected (0.03 sec) mysql> select * from class; +----+------+------+------+ | id | name | age | age1 | +----+------+------+------+ | 1 | a | 1 | NULL | | 2 | b | 2 | NULL | | 4 | c | NULL | 3 | +----+------+------+------+ 3 rows in set (0.00 sec) mysql> alter table class add age2 tinyint(5) zerofill; Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc class; +-------+------------------------------+------+-----+---------+ | Field | Type | Null | Key | Default | +-------+------------------------------+------+-----+---------+ | id | int(11) | NO | PRI | NULL | | name | varchar(10) | YES | | NULL | | age | tinyint(4) | YES | | NULL | | age1 | tinyint(3) unsigned | YES | | NULL | | age2 | tinyint(5) unsigned zerofill | YES | | NULL | +-------+------------------------------+------+-----+---------+ 5 rows in set (0.01 sec) mysql> insert into class -> (name, age2) -> values -> ('d', 4); Query OK, 1 row affected (0.03 sec) mysql> select * from class; +----+------+------+------+-------+ | id | name | age | age1 | age2 | +----+------+------+------+-------+ | 1 | a | 1 | NULL | NULL | | 2 | b | 2 | NULL | NULL | | 4 | c | NULL | 3 | NULL | | 5 | d | NULL | NULL | 00004 | //5位,自动填充0 +----+------+------+------+-------+ 4 rows in set (0.02 sec) mysql> alter table class add age3 tinyint(1) zerofill; Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc class; +-------+------------------------------+------+-----+---------+ | Field | Type | Null | Key | Default | +-------+------------------------------+------+-----+---------+ | id | int(11) | NO | PRI | NULL | | name | varchar(10) | YES | | NULL | | age | tinyint(4) | YES | | NULL | | age1 | tinyint(3) unsigned | YES | | NULL | | age2 | tinyint(5) unsigned zerofill | YES | | NULL | | age3 | tinyint(1) unsigned zerofill | YES | | NULL | +-------+------------------------------+------+-----+---------+ 6 rows in set (0.01 sec) mysql> insert into class -> (name, age3) -> values -> ('e', 5); Query OK, 1 row affected (0.07 sec) mysql> select * from class; +----+------+------+------+-------+------+ | id | name | age | age1 | age2 | age3 | +----+------+------+------+-------+------+ | 1 | a | 1 | NULL | NULL | NULL | | 2 | b | 2 | NULL | NULL | NULL | | 4 | c | NULL | 3 | NULL | NULL | | 5 | d | NULL | NULL | 00004 | NULL | | 6 | e | NULL | NULL | NULL | 5 | //zerofill,0填充1位 +----+------+------+------+-------+------+ 5 rows in set (0.00 sec) mysql> insert into class -> (name, age3) -> ('f', 255); //age3是tinyint(1) zerofill,0填充1位,只是填充效果,不影响数据的填充,一样是可以执行的 mysql> alter table class add age4 tinyint(1); Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc class; +-------+------------------------------+------+-----+---------+ | Field | Type | Null | Key | Default | +-------+------------------------------+------+-----+---------+ | id | int(11) | NO | PRI | NULL | | name | varchar(10) | YES | | NULL | | age | tinyint(4) | YES | | NULL | | age1 | tinyint(3) unsigned | YES | | NULL | | age2 | tinyint(5) unsigned zerofill | YES | | NULL | | age3 | tinyint(1) unsigned zerofill | YES | | NULL | | age4 | tinyint(1) | YES | | NULL | +-------+------------------------------+------+-----+---------+ 7 rows in set (0.01 sec) mysql> insert into class -> (name, age4) -> values -> ('f', 16); //tinyint(1)后面的1在0填充时才有意义 Query OK, 1 row affected (0.07 sec) mysql> select * from class; +----+------+------+------+-------+------+------+ | id | name | age | age1 | age2 | age3 | age4 | +----+------+------+------+-------+------+------+ | 1 | a | 1 | NULL | NULL | NULL | NULL | | 2 | b | 2 | NULL | NULL | NULL | NULL | | 4 | c | NULL | 3 | NULL | NULL | NULL | | 5 | d | NULL | NULL | 00004 | NULL | NULL | | 6 | e | NULL | NULL | NULL | 5 | NULL | | 7 | f | NULL | NULL | NULL | NULL | 16 | +----+------+------+------+-------+------+------+ 6 rows in set (0.00 sec) //我们可以看到很多没有填充的值都是NULL,因为Default默认是NULL值。 //我们可以设置默认值,not NULL default 某值 mysql> alter table class add age5 tinyint not null default 0; Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from class; +----+------+------+------+-------+------+------+------+ | id | name | age | age1 | age2 | age3 | age4 | age5 | +----+------+------+------+-------+------+------+------+ | 1 | a | 1 | NULL | NULL | NULL | NULL | 0 | | 2 | b | 2 | NULL | NULL | NULL | NULL | 0 | | 4 | c | NULL | 3 | NULL | NULL | NULL | 0 | | 5 | d | NULL | NULL | 00004 | NULL | NULL | 0 | | 6 | e | NULL | NULL | NULL | 5 | NULL | 0 | | 7 | f | NULL | NULL | NULL | NULL | 16 | 0 | | 8 | f | NULL | NULL | NULL | NULL | 16 | 0 | | 9 | f | NULL | NULL | NULL | 16 | NULL | 0 | +----+------+------+------+-------+------+------+------+ 8 rows in set (0.00 sec) mysql> update class set -> id=id-1 -> where id>2; Query OK, 6 rows affected (0.03 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> select * from class; +----+------+------+------+-------+------+------+------+ | id | name | age | age1 | age2 | age3 | age4 | age5 | +----+------+------+------+-------+------+------+------+ | 1 | a | 1 | NULL | NULL | NULL | NULL | 0 | | 2 | b | 2 | NULL | NULL | NULL | NULL | 0 | | 3 | c | NULL | 3 | NULL | NULL | NULL | 0 | | 4 | d | NULL | NULL | 00004 | NULL | NULL | 0 | | 5 | e | NULL | NULL | NULL | 5 | NULL | 0 | | 6 | f | NULL | NULL | NULL | NULL | 16 | 0 | | 7 | f | NULL | NULL | NULL | NULL | 16 | 0 | | 8 | f | NULL | NULL | NULL | 16 | NULL | 0 | +----+------+------+------+-------+------+------+------+ 8 rows in set (0.00 sec) 2、小数型: 浮点型:float(M,D) 定点型:decimal(M,D) //定点型更精确 M:精度(总位数,不包含点) D:标度(小数位) 同样,小数型也是有unsigned型的。 float和double容易产生误差,对精确度要求比较高时,建议使用decimal来存放,decimal在mysql内存是以字符串存储的,用于定义货币要求精确度高的数据。在数据迁移中,float(M,D)是非标准定义,最好不要这样使用。M为精度,D为标度。 float和real数据类型被称为近似的数据类型。不存储精确值.当要求精确的数字状态时,比如在财务应用程序中,在那些需要舍入的操作中,或在等值核对的操作中,就不使用这些数据类型。这时就要用integer、decimal、money或smallmone数据类型。   在 WHERE 子句搜索条件中(特别是 = 和 <> 运算符),应避免使用float或real列。最好限制使用float和real列做> 或 < 的比较。 mysql> create table goods ( -> id tinyint(3) zerofill primary key auto_increment, -> name varchar(10) not null default '', -> price float(5.2) not null default 0.00, -> decprice decimal(5.2) not null default 0.00 -> )charset utf8; Query OK, 0 rows affected (0.36 sec) mysql> desc goods; +----------+------------------------------+------+-----+---------+ | Field | Type | Null | Key | Default | +----------+------------------------------+------+-----+---------+ | id | tinyint(3) unsigned zerofill | NO | PRI | NULL | | name | varchar(10) | NO | | | | price | float | NO | | 0 | | decprice | decimal(5,0) | NO | | 0 | +----------+------------------------------+------+-----+---------+ 4 rows in set (0.05 sec) 二、字符型: char:定长类型,1字节。 char(M),0<=M<=255,当存入N个字符小于M个字符时,实占M个字符空间,会在N个字符后面加空格补齐。所以,对定长char而言,如果存入的字符最后有空格字符,取出来的时候,空格字符会被当作是填充的,空格字符就会丢失掉。但是,varchar类型不会丢掉,varchar会取存入的个数。 varchar:变长类型,2字节。 varchar(M),0<=M<=65535(以ascii字符为例,utf8是22000左右),当存入小于M个字符时,实占存入字符个数的字符空间。 text:文本类型。 text,不用加默认值(加了也是无效的).可以存放比较大的文本段,约2W-6W个字符(受字符集影响)。因此,如果不是特别大的内容,建议使用char、varchar。 mysql> alter table student -> add -> essay text not null default ''; //添加默认值会失败 ERROR 1101 (42000): BLOB/TEXT column 'essay' can not have a default value. mysql> create table student ( -> id tinyint(3) zerofill primary key auto_increment, -> firstname char(8) not null default '', -> lastname varchar(8) not null default '' -> ); Query OK, 0 rows affected (2.08 sec) mysql> desc student; +-----------+------------------------------+------+-----+---------+ | Field | Type | Null | Key | Default | +-----------+------------------------------+------+-----+---------+ | id | tinyint(3) unsigned zerofill | NO | PRI | NULL | | firstname | char(8) | NO | | | | lastname | varchar(8) | NO | | | +-----------+------------------------------+------+-----+---------+ 3 rows in set (0.38 sec) mysql> insert into student -> (firstname, lastname) -> values -> ('first', 'last'), -> ('first ', 'last '); Query OK, 2 rows affected (0.14 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +-----+-----------+----------+ | id | firstname | lastname | +-----+-----------+----------+ | 001 | first | last | | 002 | first | last | +-----+-----------+----------+ 2 rows in set (0.00 sec) mysql> select concat(firstname, '!'), concat(lastname, '!') from student +------------------------+-----------------------+ | concat(firstname, '!') | concat(lastname, '!') | +------------------------+-----------------------+ | first! | last! | | first! | last ! | +------------------------+-----------------------+ 2 rows in set (0.03 sec) 三、日期时间类型: year:年类型,1字节,一共可以表示256种年份:0000、1901-2155。(0000,表示不输人或者选择错误)。 年份一般是4位的,但是,以前有用2位来表示年份的,如97,表示1997年。但是如果输入的是10呢?1910?2010?还是2110?所以,当输入2位的时候: 00-69:表示2000-2069; 70-99:表示1970-1999 date:日期类型,典型格式:如,1991-04-02。 范围:1000-01-01- 9999-12-31 time:时间类型,典型格式: hh:mm:ss datetime:日期时间类型,典型格式:如1991-04-02 15:23:23 范围:1000-01-01 00:00:00 - 9999-12-31 23:59:59 注意:在开发中,很少用日期时间类型来表示一个需要的精确到秒的列。一般用时间戳来表示。 时间戳:用int型来存储,表示1970-01-01 00:00:00 到当前的秒数。 一般存注册时间、商品发布时间等,并不是用datetime存储,而是用时间戳。因为,datetime虽然直观,但是计算不方便。而用int型存储时间戳,方便计算,对于显示,也可以方便格式化成不同的显示样式。 在MySQL中提供了当前时间的函数:now()

DATABASE
    show databases;
    create database DBName;
    drop database DBName;
    ('Can not alter DBName')
    use DBName;

TABLE
    show tables;

    create table tableName (
    C1Name C1Type [C1Attribute] [Default Value],
    ...
    CnName CnType [CnAttribute] [Default Value]  ('the last column can not add comma')
    ) enqine enqineName charset charType;

    drop table tableName;
    rename table oldName to newName;
    desc tableName;    // check the table structure

    insert into tableName
    (column1, column2, ...) [option]
    values
    (column1, column2, ... );

    select (column) from table;

    update tableName
    set
    column1 = value1,
    ...
    columnN = valueN    // the last column can not add comma
    where
    conditions;

    delete from tableName where conditions;    ('NOTE: can not delete column. If you want to delete certain column, It is the same as updating the column with value '' ')


1、连接服务器了就可以查看有哪些数据库:
    show databases; --->注意,MySQL语句是以分号结束的!
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
information_schema:数据库的基本信息;
mysql:用户信息;如,root
performance_schema:性能优化的信息;
    上面的3个数据库是不能够随便修改的,如果更改了上面的3个数据库服务器就起不来了。

2、创建数据库:
    create database DBName;

mysql> create database lzs;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lzs                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

3、删除数据库:
    drop database DBName;

4、修改数据库名:
    能否修改数据库的名字呢? 不能!

5、选择数据库:
    use DBName; //选择相应的数据库,注意,这条语句是可以不加分号的。

mysql> use lzs;
Database changed

mysql> use lzs
Database changed

6、如何查看数据库的所以元素:
    show tables;

mysql> show tables;
Empty set (0.00 sec)

7、如何创建一张表:
    create table tablename (
    列1名称  列1类型 [列1属性] [默认值],
    ...

    列n名称  列n类型 [列n属性] [默认值](最后一列不能加逗号)
    )enqine 引擎名 charset 字符集;

    建表的时候,最好就先在记事本上先写出来,再复制过去创建。

mysql> create table class
    -> (
    -> num int,
    -> name varchar(20), //varchar(),字符串
    -> height int //注意,最后一列不能够加逗号,因为逗号是用来分开列的。
    -> );
Query OK, 0 rows affected (0.19 sec)

//创建名为shop_all的表格
//id varchar(6) not null分别对应:列名、数据类型、数据长度、是否为空值
mysql> create table shop_all(
    -> id varchar(6) not null,
    -> status varchar(16) not null,
    -> owner varchar(20) not null,
    -> owner_id varchar(18) not null,
    -> introduction varchar(500) not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| shop_all      |
+---------------+
2 rows in set (0.00 sec)

8、删除一张表:
    drop table tableName;

9、如何修改表名:
    rename table oldName to newName;

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| shop_all      |
+---------------+
2 rows in set (0.00 sec)

mysql> rename table shop_all to shop;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| shop          |
+---------------+

10、查看表结构:
    desc tableName; //describe v.描述  description n.描述
mysql> desc class;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| num    | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

mysql> desc shop;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(6)   | NO   | PRI | NULL    |       |
| status       | varchar(16)  | NO   |     | NULL    |       |
| owner        | varchar(20)  | NO   |     | NULL    |       |
| owner_id     | varchar(18)  | NO   |     | NULL    |       |
| introduction | varchar(500) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

    注意,当输入错误而且又按下了回车键Enter的时候,MySQL是没有返回改错的,

这时候我们可以结束输入";",不过这样会有报错。我们可以用"\c"来退出这条语句

。
mysql> create tabel class (     //table打错了
    -> num int,
    -> \c
mysql>


mysql> create table messages (
    -> id int,
    -> name varchar(20),
    -> sex varchar(10),
    -> height varchar(10),
    -> weight varchar(10)
    -> );
Query OK, 0 rows affected (0.46 sec)

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| messages      |
| shop          |
+---------------+
3 rows in set (0.00 sec)

11、增加数据
    //往哪张表增,增哪几列,各列是什么值。
    insert into tableName //或者insert into lzs.tablename
    (列名1,列名2,...)  //你要增加哪一列的值就加哪一列的名字
    values
    (列1值,列2值,...), //values要与列名对应,varchar型要用单引号括起来
    (列1值,列2值,...); //当要增加多列,一列之间用逗号,最后列分号结束

    insert语句允许不写列名,如果没有声明列名,则默认插入所有列。因此,如果

没有列名,values应该与全部列按顺序一一对应。
    如果某列中不想插入值,可以用插入空格来处理,即' '。

mysql> insert into messages
    -> (id, name, sex, height, weight)
    -> values
    -> (1, 'jack', 'boy', '175cm', '60kg');
Query OK, 1 row affected (0.07 sec)

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
+------+------+------+--------+--------+
1 row in set (0.00 sec)

mysql> insert into messages
    -> (id, name, sex, height, weight)
    -> values
    -> (2, 'rose', 'girl', '165cm', '50kg');
Query OK, 1 row affected (0.09 sec)

12、查看数据
    select (列) from table;

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
|    2 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+
2 rows in set (0.00 sec)

    只查看不同的值,也就是说,当表中有重复的数据时,只列出其中一个来:
    select distinct (column) from table;

TABLE: messages
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    3 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+

mysql> select name from messages;
+------+
| name | 
+------+
| jack |
| rose | 
+------+

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    3 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+


13、修改数据
    update tablename
    set
    列名1 = 新值1,
    列名2 = 新值2,
    ...
    列名n = 新值n  //注意,最后一个不要加逗号,逗号作用是用来分开数据的
    where
    条件(如,id = 2、name = 'xxx'等等);

mysql> update messages
    -> set
    -> height = '178cm',
    -> weight = '65kg'
    -> where     //条件
    -> name = 'jack';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 178cm  | 65kg   |
|    2 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+
2 rows in set (0.00 sec)

mysql> insert into messages
    -> values
    -> (3, 'Jeff', 'boy', '175cm', '63kg'),
    -> (4, 'Tom', 'boy', '180cm', '70kg'),
    -> (5, 'Tim', 'boy', '185cm', '72kg');
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 178cm  | 65kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    3 | Jeff | boy  | 175cm  | 63kg   |
|    4 | Tom  | boy  | 180cm  | 70kg   |
|    5 | Tim  | boy  | 185cm  | 72kg   |
+------+------+------+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from messages where id>2;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    3 | Jeff | boy  | 175cm  | 63kg   |
|    4 | Tom  | boy  | 180cm  | 70kg   |
|    5 | Tim  | boy  | 185cm  | 72kg   |
+------+------+------+--------+--------+
3 rows in set (0.03 sec)

mysql> select id, name, height from messages where weight = '50kg';
+------+------+--------+
| id   | name | height |
+------+------+--------+
|    2 | rose | 165cm  |
+------+------+--------+
1 row in set (0.00 sec)

14、删除数据
    delete from tablename where 条件; (from前面是没有列的)

    注意,delete只能根据条件删除行,没有删除列的。如果要删除列,那就不叫删

除了,而是将它改为null。用update就可以完成了。

mysql> delete from messages where id=3;
Query OK, 1 row affected (0.07 sec)

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 178cm  | 65kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    4 | Tom  | boy  | 180cm  | 70kg   |
|    5 | Tim  | boy  | 185cm  | 72kg   |
+------+------+------+--------+--------+
4 rows in set (0.00 sec)

MySQL 的登录名和密码保存在名为 mysql 的数据库中的user表:UserPassword

  • 首先,登录并且选择名为mysql的数据库。

mysql -u root -p mysql> use mysql;
  • 更新 user 表中的 Password

mysql> UPDATE user SET Password=PASSWORD('新密码') WHERE User='用户名';

Password 的值以加密的形式存储,password() 函数用于加密密码。

  • 刷新 MySQL 的系统权限相关表

FLUSH PRIVILEGES;

MySQL 用户数据和权限修改之后,希望在不重启 MySQL 服务的情况下直接生效,那么就需要执行这个命令。通常,在修改 root 账号的设置后,怕重启后无法再登录进来,FLUSH PRIVILEGES 之后就可以看设置是否生效,而不必冒太大风险。