以下完全为本人自学mysql的个人笔记，共享与此。
一、名词解释
数据库database/DB
用于存放信息的仓库，构造简单，遵守以下规则：
1、数据库里的数据集合都存放在数据表table中
2、数据表由数据行row和数据列column构成
3、一个数据行就是数据表里的一条记录record
4、记录record可以包含多个信息项，数据表里的每一个数据列都对应一个信息项
管理系统management system/MS
用来对数据进行插入，检索，修改，删除等操作的软件
关系relational/R
把分别放在两个数据表里的信息相互匹配起来，通过寻找共同元素实现，能够方便结合不同数据表的信息
SQL
Structured Query Language结构化查询语言
MySQL&mysql
指的是一个完整的MySQLRDBMS,而mysql则是一个特定的客户程序的名字
二、体系结构
mysql采用C&S结构
服务器程序mysqld，存放在数据库的服务器上，负载处理请求;
客户端程序mysql，发出请求（SQL查询命令）
优点：
1、并发请求,由服务端处理先后顺序。
2、远程登录。
3、权限管理，服务端判断登录者访问数据权限
三、命令
（一）用户管理
1、登录
mysql -p -u root #指定用户root登录，-p提示输入口令 /初始时默认没有密码
2.查询user表
（1）查询mysql.user表：主机，用户，密码 #SELECT xx, xx, xx FROM xx;
mysql> SELECT Host, User, Password FROM mysql.user;
+-----------+------+----------+
| Host | User | Password |
+-----------+------+----------+
| localhost | root | | #允许localhost;local;127.0.0.1;::1主机以root用户登录
| linux | root | | #增加方式： mysql -h i.simon.net -u root
| 127.0.0.1 | root | | #+ 允许i.simon.net主机以root用户登录
| ::1 | root | |
| localhost | | | #user空白项，允许localhost,linux匿名登录(无用户)
| linux | | | #+ mysql -h i.simon.net 新增匿名登录主机
+-----------+------+----------+
6 rows in set (0.00 sec)
mysql> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | #localhost不会被解析成IP地址，直接通过UNIXsocket连接
| linux | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| 127.0.0.1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | #会通过TCP/IP协议连接，并且只能在本机访问；
| ::1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | #::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
| localhost | | |
| % | mary | | #"%"表示该用户可从任意一台主机均连接mysql服务器，%匹配所有
|192.168.1.%| Mary | | #表示该用户只能从192.168.1.0网段主机连接mysql服务器
+-----------+------+-------------------------------------------+
（2）查询mysql.user表中Password为空的数据 #SELECT xx, xx, FROM xx WHERE XX = "XX";
mysql> SELECT Host, User FROM mysql.user WHERE Password = "";
+-----------+------+
| Host | User |
+-----------+------+
| localhost | root |
| linux | root |
| 127.0.0.1 | root |
| ::1 | root |
| localhost | |
| linux | |
+-----------+------+
6 rows in set (0.00 sec)
3.用户设置密码
SET
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
查询：mysql> SELECT Host, User, Password FROM mysql.user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| linux | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| linux | | |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)
UPDATE #可设置多个用户
UPDATE mysql.user SET Password=PASSWORD('mysql') WHERE User='root';
mysql> FLUSH PRIVILEGES; #更新权限表，而SET不需要
查询结果：mysql> SELECT Host, User, Password FROM mysql.user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| linux | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| 127.0.0.1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| ::1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| localhost | | |
| linux | | |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)
4.创建用户
CREATE USER 'mary'@'localhost' IDENTIFIED by 'xxxxx'; //identified by 会将纯文本密码加密作为散列值存储;
5.删除用户
DORP USER ''@'localhost';
6.重命名用户
RENAME USER 'mary'@'localhost' TO 'bob'@'localhost';
7.查询用户权限
SHOW GRANTS FOR ' '@'localhost';
mysql> show grants for ' '@'localhost'
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec) #没超级权限
mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec) #拥有超级权限
8.撤销账户权限
REVOKE privileges ON *.* FROM ''@'localhost';
9.对账户授权GRANT
格式：
GRANT privileges(columns)
ON what
TO account [IDENTIFIED BY 'password']
[REQUIRE encryption requirements]
[WITH grant or resource management options];
#privileges,授予账户的权限;SELECT权限则运行发出SELECT操作,多项权限用逗号隔开.
#columns,权限将作用于哪些数据列;如需要列举多个数据列用逗号隔开;可选项,数据列名单必须紧跟在每项相关权限后面.
#what,权限级别。最高是全局级,作用于所有数据库和数据表。可被设定为数据库级,数据表级,数据列级,存储例程级
#account,被授予权限的账户。格式'username'@'hostname'
#password,账户口令.可选,如果账户已经存在并有一个口令则不必写出.如果为现有账户的权限是给出IDENTIFIED BY时替换新口令.
#REQUIRE子句,可选.对必须经过由SSL进行安全连接的账户进行设置
#WIEH子句,可选.授予GRANT OPTION权限,这个权限允许账户把自己的权限转授给其他用户;还可以设置资源管理选项
授权前思考问题：
1、账户有什么权限级别，对象是？
2、是否需要安全连接
3、是否授予一些管理员权限
4、是否现在用户的资源占有量
设置权限时必须给出一下信息：
1，要授予的权限
2，被授予访问权限的数据库或表(访问对象)
3，用户名
grant和revoke可以在几个层次上控制访问权限
1，整个服务器，使用 grant ALL 和revoke ALL on *.*
2，整个数据库，使用on database.* // on *若有指定为默认数据库，若没有知道数据库on *为全局权限
3，特点表，使用on database.table
4，特定的列
5，特定的存储过程
具体例子：
grant普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利
grant select on testdb.* to common_user@’%’
grant insert on testdb.* to common_user@’%’
grant update on testdb.* to common_user@’%’
grant delete on testdb.* to common_user@’%’
#或者，用一条 MySQL 命令来替代：
grant select, insert, update, delete on testdb.* to common_user@’%’
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on testdb.* to developer@’192.168.0.%’;
grant alter on testdb.* to developer@’192.168.0.%’;
grant drop on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 外键权限。
grant references on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 临时表权限。
grant create temporary tables on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 索引权限。
grant index on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 视图、查看视图源代码 权限。
grant create view on testdb.* to developer@’192.168.0.%’;
grant show view on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 存储过程、函数 权限。
grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status
grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure
grant execute on testdb.* to developer@’192.168.0.%’;
grant 普通 DBA 管理某个 MySQL 数据库的权限。
grant all privileges on testdb to dba@’localhost’ #其中，关键字 “privileges” 可以省略。
grant 高级 DBA 管理 MySQL 中所有数据库的权限。
grant all on *.* to dba@’localhost’
MySQL grant 权限，分别可以作用在多个层次上。
1. grant 作用在整个 MySQL 服务器上：
grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
2. grant 作用在单个数据库上：
grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
3. grant 作用在单个数据表上：
grant select, insert, update, delete on testdb.orders to dba@localhost;
4. grant 作用在表中的列上：
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
5. grant 作用在存储过程、函数上：
grant execute on procedure testdb.pr_add to ’dba’@’localhost’
grant execute on function testdb.fn_add to ’dba’@’localhost’
注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES。
(二)基本操作(SQL语句)
1.SELECT查看
注意结束符";"\g""\G"的区别
mysql> SELECT NOW(), USER(), VERSION();#查看时间,用户,版本
+---------------------+----------------+-----------+
| NOW() | USER() | VERSION() |
+---------------------+----------------+-----------+
| 2016-01-14 01:59:50 | root@localhost | 5.6.28 |
+---------------------+----------------+-----------+
1 row in set (0.05 sec)
mysql> SELECT NOW(), USER(), VERSION()\g
+---------------------+----------------+-----------+
| NOW() | USER() | VERSION() |
+---------------------+----------------+-----------+
| 2016-01-14 01:59:54 | root@localhost | 5.6.28 |
+---------------------+----------------+-----------+
1 row in set (0.00 sec)
mysql> SELECT NOW(), USER(), VERSION()\G
*************************** 1. row ***************************
NOW(): 2016-01-14 01:59:57
USER(): root@localhost
VERSION(): 5.6.28
1 row in set (0.00 sec)
mysql> select host, user, password from mysql.user;#查看user表
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| linux | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| 127.0.0.1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| ::1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| localhost | | |
| % | mary | |
| localhost | Mary | |
+-----------+------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> select host, user, password from mysql.user\G
*************************** 1. row ***************************
host: localhost
user: root
password: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
*************************** 2. row ***************************
host: linux
user: root
password: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
*************************** 3. row ***************************
host: 127.0.0.1
user: root
password: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
*************************** 4. row ***************************
host: ::1
user: root
password: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
*************************** 5. row ***************************
host: localhost
user:
password:
*************************** 6. row ***************************
host: %
user: mary
password:
*************************** 7. row ***************************
host: localhost
user: Mary
password:
7 rows in set (0.00 sec)
2.数据库相关命令
1）创建数据库
mysql> CREATE DATABASE sampdb; #创建数据库后没有自动选择为当前默认数据库
2）查看选择的数据库：
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL | #NULL，没有选择数据库
+------------+
1 row in set (0.00 sec)
3）选择数据库：
mysql> USE DATABASE_Name;
$ mysql -p -u root DATABASE_Name;
4）查看所有数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | #mysql安装过程自动建立
| mysql | #mysql安装过程自动建立
| performance_schema | #mysql安装过程自动建立
| sampdb |
| test | #mysql安装过程自动建立
+--------------------+
5 rows in set (0.00 sec)
5）删除数据库
mysql>drop database database_name;
3.创建数据表(在数据库里)CREATE TABLE
（1）创建数据表
create table table_name(数据列名字，数据类型，属性);
数据类型：
VARCHAR(N):用于保存字符串，长度对多为N个字符
DATE:用于保存日期值 #''无法字符无法转换为日期若不写就填NULL
INE:表示这个数据列将用来保持整数值
UNSIGNED:不允许出现负数
属性：
NULL:表示无数据，可以为空
NOT NULL:表示不得为空
AUTO_INCREMENT:特殊属性，表示数据列存放的是唯一序列编号
创建方式:
1)创建方式手动输入
$ mysql DATABASE_Name
>mysql create table xxx
>mysql use DATEBASE_Name;
>mysql create table xxx ;
例子：
mysql> create table president
-> (
-> last_name VARCHAR(15) NOT NULL,
-> first_name VARCHAR(15) NOT NULL,
-> suffix VARCHAR(5) NULL,
-> city VARCHAR(20) NOT NULL,
-> state VARCHAR(2) NOT NULL,
-> birth DATE NOT NULL,
-> death DATE NULL
-> );
Query OK, 0 rows affected (0.36 sec)
2)重定向
$ mysql < create_table_name.sql
例子：
$ mysql -u root -p sampdb < create_membertable.sql $ cat create_membertable.sql
create table member
(
member_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (member_id), #表示对member_id数据列创建索引以加快查找速度，同时要求该列的值唯一
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
suffix VARCHAR(5) NULL,
expiration DATE NULL,
email VARCHAR(100) NULL,
street VARCHAR(50) NULL,
city VARCHAR(50) NULL,
state VARCHAR(2) NULL,
zip VARCHAR(10) NULL,
phone VARCHAR(20) NULL,
interests VARCHAR(255) NULL
);
(2)查看数据表结构：
describe/desc/explain table_name;
show columns from table_name;
show fields from table_name;
mysql> describe president;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| last_name | varchar(15) | NO | | NULL | |
| first_name | varchar(15) | NO | | NULL | |
| suffix | varchar(5) | YES | | NULL | |
| city | varchar(20) | NO | | NULL | |
| state | varchar(2) | NO | | NULL | |
| birth | date | NO | | NULL | |
| death | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.04 sec)
mysql> describe member;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| member_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| last_name | varchar(20) | NO | | NULL | |
| first_name | varchar(20) | NO | | NULL | |
| suffix | varchar(5) | YES | | NULL | |
| expiration | date | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| street | varchar(50) | YES | | NULL | |
| city | varchar(50) | YES | | NULL | |
| state | varchar(2) | YES | | NULL | |
| zip | varchar(10) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| interests | varchar(255) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
(3)查看数据库中的所有数据表
mysql> show tables;
+------------------+
| Tables_in_sampdb |
+------------------+
| member |
| president |
| president2 |
+------------------+
3 rows in set (0.00 sec)
（4）删除数据表
mysql> drop table table_name;
4.修改数据表数据(插入,检索,修改,删除等) #有联系检索 P50
(1)添加数据
INSERT插入
全部数据赋值：
插入单行：INSERT INTO table_name VALUES(value1,value2,...);
插入多行：INSERT INTO table_name VALUES(...),(...),...;
单独赋值：
插入单行：INSERT INTO table_name (item1,item2) VALUES(value1,value2);
插入多行：INSERT INTO table_name (item1,item2) VALUES(value1,value2),(value11,value22),...;
重定向数据
命令行：
$ mysql -u root -p database_name < insert_tabledata.sql
mysql中source:
mysql> source insert_tabledata.sql
mysql中LOAD DATA:(纯数值才可用)
$ mysql --local-infile database_name
mysql> LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE table_name; #使用绝对路径，LOCAL是客户程序去读文件发送到服务中加载(若忽略则数据文件是要保持在服务器主机上)
(2)检索信息 #不区分大小写
格式：
SELECT what to retrieve
FROM table or tables
WEHRE conditions that data must satisfy;
例子：
mysql> SELECT * FROM table_name;
#把table_name这个表的数据的显示出来
#+ "*"匹配所有
指定检索条件WHERE：
数值对比: mysql> SELECT * FROM score WHERE score > 95;
字符串对比: mysql> SELECT XXX FROM XXX WHERE XXX='XXX';
日期对比：mysql> SELECT XXX FROM XXX WHERE DATE < '1750-1-1';
组合对比：mysql> SELECT XXX FROM XXX WHERE DATE < '1750-1-1' AND (XXX='xxx' OR XXX='xxx');
#WHERE表达式中允许算术运算符,比较运算符,逻辑运算符和括号.
NULL值:
可以用=<>!=等条件测试，必须用IS NULL或者IS NOT NULL或者<=>来判断.
mysql> SELECT XXX FROM table_name WHERE XXX IS NULL;
mysql> SELECT XXX FROM table_name WHERE XXX <=> NULL;
接下来语句使用数据表：
mysql> select * from president2;
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| y2 | y1 | | GZ | GD | 1920-01-03 | 2000-01-01 |
| x2 | x1 | | GZ | GD | 1949-01-01 | 2005-01-01 |
| z2 | z1 | | CS | HN | 1920-06-10 | 1980-05-05 |
| w2 | w1 | | GZ | GD | 1949-01-01 | 2005-07-12 |
| a2 | a1 | NULL | CS | HN | 1920-06-10 | NULL |
| b2 | b1 | NULL | GZ | GD | 1949-11-01 | 2005-06-20 |
| D2 | D1 | NULL | SH | SH | 1910-01-10 | NULL |
| c2 | c1 | NULL | BJ | BJ | 1999-05-06 | 2008-06-20 |
| ccc | ccc | NULL | WH | HB | 1991-04-01 | NULL |
| xcw | wcx | NULL | CZ | SX | 1998-06-18 | NULL |
+-----------+------------+--------+------+-------+------------+------------+
10 rows in set (0.00 sec)
对查询结果排序:
背景：mysql默认对数据处理为，但一行数据插入后被删除，会出现一个空行，然后再插入表的数据会自动填满空洞
升序ORDER BY:
mysql> SELECT x1 FROM table_name ORDER BY x1;
降序ORDER BY XX DESC:
mysql> SELECT x1 FROM table_name ORDER BY x1 DESC;
NULL值分类：IF(XX is null,0,1)
先把death为null拍到前面，在birth降序
mysql> select * from president2 where birth > '1900-1-1' and ( state='GD' or city='CS') order by if(death is null,0,1), birth desc;
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| a2 | a1 | NULL | CS | HN | 1920-06-10 | NULL |
| b2 | b1 | NULL | GZ | GD | 1949-11-01 | 2005-06-20 |
| x2 | x1 | | GZ | GD | 1949-01-01 | 2005-01-01 |
| w2 | w1 | | GZ | GD | 1949-01-01 | 2005-07-12 |
| z2 | z1 | | CS | HN | 1920-06-10 | 1980-05-05 |
| y2 | y1 | | GZ | GD | 1920-01-03 | 2000-01-01 |
+-----------+------------+--------+------+-------+------------+------------+
6 rows in set (0.00 sec)
限制显示个数limit N
mysql> select * from president2 where birth > '1900-1-1' and ( state='GD' or city='CS') order by if(death is null,0,1), birth desc limit 3;
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| a2 | a1 | NULL | CS | HN | 1920-06-10 | NULL |
| b2 | b1 | NULL | GZ | GD | 1949-11-01 | 2005-06-20 |
| x2 | x1 | | GZ | GD | 1949-01-01 | 2005-01-01 |
+-----------+------------+--------+------+-------+------------+------------+
3 rows in set (0.00 sec)
抽取特定项目limit N1 N2 #显示跳过前N1个数据后的N2个数据
mysql> select * from president2 where birth > '1900-1-1' and ( state='GD' or city='CS') order by if(death is null,0,1), birth desc limit 3, 2;
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| w2 | w1 | | GZ | GD | 1949-01-01 | 2005-07-12 |
| z2 | z1 | | CS | HN | 1920-06-10 | 1980-05-05 |
+-----------+------------+--------+------+-------+------------+------------+
2 rows in set (0.00 sec)
合并项并命名 concat() as #可直接省略as
mysql> select concat(first_name,' ',last_name) AS Name,concat(city,', ',state) AS 'birth place' from president2;
+-------+-------------+
| Name | birth place |
+-------+-------------+
| y1 y2 | GZ, GD |
| x1 x2 | GZ, GD |
| z1 z2 | CS, HN |
| w1 w2 | GZ, GD |
| a1 a2 | CS, HN |
| b1 b2 | GZ, GD |
| D1 D2 | SH, SH |
| c1 c2 | BJ, BJ |
+-------+-------------+
8 rows in set (0.05 sec)
日期操作：
1)使用英文月份匹配，monthname(),dayofmonth()
mysql> select concat(first_name,' ',last_name) as Name, birth from president2 where monthname(birth)='june' and dayofmonth(birth)=10;
+-------+------------+
| Name | birth |
+-------+------------+
| z1 z2 | 1920-06-10 |
| a1 a2 | 1920-06-10 |
+-------+------------+
2 rows in set (0.07 sec)
2)使用数字直接匹配month()
mysql> select concat(first_name,' ',last_name) as Name, birth from president2 where month(birth)= 6 and dayofmonth(birth)=10;
+-------+------------+
| Name | birth |
+-------+------------+
| z1 z2 | 1920-06-10 |
| a1 a2 | 1920-06-10 |
+-------+------------+
2 rows in set (0.00 sec)
#特定函数CURDATE()表示今天，例:month(birth)=month(curdate())
3)计算市场函数timestampdiff()
mysql> select concat(first_name,' ',last_name) as Name, birth,death, TIMESTAMPDIFF(YEAR, birth, death) as age from president2
-> where death is not null;
+-------+------------+------------+------+
| Name | birth | death | age |
+-------+------------+------------+------+
| y1 y2 | 1920-01-03 | 2000-01-01 | 79 |
| x1 x2 | 1949-01-01 | 2005-01-01 | 56 |
| z1 z2 | 1920-06-10 | 1980-05-05 | 59 |
| w1 w2 | 1949-01-01 | 2005-07-12 | 56 |
| b1 b2 | 1949-11-01 | 2005-06-20 | 55 |
| c1 c2 | 1999-05-06 | 2008-06-20 | 9 |
+-------+------------+------------+------+
6 rows in set (0.00 sec)
模式匹配：
1)%匹配任意字符
以特定字符开头,x%
mysql> select last_name, first_name from president2 where last_name like 'c%';
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| c2 | c1 |
| ccc | ccc |
+-----------+------------+
2 rows in set (0.00 sec)
包含特定字符,%xxx%
mysql> select last_name, first_name from president2 where last_name like '%c%';
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| c2 | c1 |
| ccc | ccc |
| xcw | wcx |
+-----------+------------+
3 rows in set (0.00 sec)
2)匹配特地字符数, _代表匹配一个任意字符
mysql> select last_name, first_name from president2 where last_name like '___';
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| ccc | ccc |
| xcw | wcx |
+-----------+------------+
2 rows in set (0.00 sec)
变量赋值 # @var := value或 @var = value
mysql> select @birth := birth from president2 where last_name='x2' and first_name='x1';
+-----------------+
| @birth := birth |
+-----------------+
| 1949-01-01 |
+-----------------+
1 row in set (0.00 sec)
mysql> select last_name, first_name, birth from president2
-> where birth < @birth order by birth;
+-----------+------------+------------+
| last_name | first_name | birth |
+-----------+------------+------------+
| D2 | D1 | 1910-01-10 |
| y2 | y1 | 1920-01-03 |
| z2 | z1 | 1920-06-10 |
| a2 | a1 | 1920-06-10 |
+-----------+------------+------------+
4 rows in set (0.00 sec)
统计count(*)
1)特定数量统计
mysql> select count(*) as count from president2 where city = 'GZ'; #统计city项为GZ的数量
+----------+
| count |
+----------+
| 4 |
+----------+
1 row in set (0.07 sec)
2)分类统计并排序
mysql> select city, count(*) as count from president2 group by city order by count desc;
+------+-------+
| city | count |
+------+-------+
| GZ | 4 |
| CS | 2 |
| SH | 1 |
| BJ | 1 |
| WH | 1 |
| CZ | 1 |
+------+-------+
6 rows in set (0.00 sec)
3)利用统计结果筛选having #having与where区别是having可调用前面的结果进行比较
mysql> select city, count(*) as count from president2 group by city having count >=2 order by count desc;
+------+-------+
| city | count |
+------+-------+
| GZ | 4 |
| CS | 2 |
+------+-------+
2 rows in set (0.00 sec)
4)统计并汇总with rollup
mysql> select city, count(*) as count from president2 group by city with rollup;
+------+-------+
| city | count |
+------+-------+
| BJ | 1 |
| CS | 2 |
| CZ | 1 |
| GZ | 4 |
| SH | 1 |
| WH | 1 |
| NULL | 10 |
+------+-------+
7 rows in set (0.00 sec)
嵌套查询(子查询)
mysql> select last_name, first_name, birth from president2 where birth < (select birth from president2
-> where last_name='y2' and first_name='y1')
-> order by birth;
+-----------+------------+------------+
| last_name | first_name | birth |
+-----------+------------+------------+
| D2 | D1 | 1910-01-10 |
+-----------+------------+------------+
1 row in set (0.00 sec)
综合用法1：
mysql> select state as state, #第一列
->avg(timestampdiff(YEAR, birth, death)) as age #第二列为寿命平均值
->from president2 where death is not null #death不为空
->group by state #以state分类统计
->order by age; #以年龄分类
+-------+---------+
| state | age |
+-------+---------+
| BJ | 9.0000 |
| HN | 59.0000 |
| GD | 61.5000 |
+-------+---------+
3 rows in set (0.00 sec)
(3)多个数据表处理
格式: JOIN USING()
1)简单的例子
#WHERE
mysql> select * from score, student where score.id=student.id;
+----+-------+----+------+
| id | score | id | name |
+----+-------+----+------+
| 1 | 6 | 1 | z3 |
| 2 | 75 | 2 | l4 |
| 3 | 90 | 3 | l5 |
+----+-------+----+------+
3 rows in set (0.00 sec)
#JOIN ON
mysql> select * from score join student on score.id=student.id;
+----+-------+----+------+
| id | score | id | name |
+----+-------+----+------+
| 1 | 6 | 1 | z3 |
| 2 | 75 | 2 | l4 |
| 3 | 90 | 3 | l5 |
+----+-------+----+------+
3 rows in set (0.00 sec)
#JOIN USING()
mysql> select * from score join student using(id);
+----+-------+------+
| id | score | name |
+----+-------+------+
| 1 | 6 | z3 |
| 2 | 75 | l4 |
| 3 | 90 | l5 |
+----+-------+------+
3 rows in set (0.00 sec)
2)第二个例子#注重严格性
#使用到的表:
mysql> select * from grade_event1;
+----------+------------+----------+
| event_id | date | category |
+----------+------------+----------+
| 1 | 2008-09-03 | Q |
| 2 | 2008-09-06 | Q |
| 3 | 2008-09-09 | T |
| 4 | 2008-09-16 | Q |
| 5 | 2008-09-23 | Q |
| 6 | 2008-10-01 | T |
+----------+------------+----------+
6 rows in set (0.00 sec)
mysql> select * from score1;
+--------+----------+-------+
| name | event_id | score |
+--------+----------+-------+
| billy | 5 | 15 |
| missy | 5 | 14 |
| johnny | 5 | 17 |
| jenny | 5 | 19 |
| billy | 6 | 67 |
| missy | 6 | 73 |
| johnny | 6 | 82 |
| jenny | 6 | 79 |
+--------+----------+-------+
8 rows in set (0.00 sec)
#实例
mysql> select score1.name, grade_event1.date, score1.score, grade_event1.category
->from score1 inner join grade_event1
on score1.event_id = grade_event1.event_id
where grade_event1.date = '2008-09-23';
+--------+------------+-------+----------+
| name | date | score | category |
+--------+------------+-------+----------+
| billy | 2008-09-23 | 15 | Q |
| missy | 2008-09-23 | 14 | Q |
| johnny | 2008-09-23 | 17 | Q |
| jenny | 2008-09-23 | 19 | Q |
+--------+------------+-------+----------+
4 rows in set (0.00 sec)
mysql> select score1.name, grade_event1.date, score1.score, grade_event1.category
-> from score1 inner join grade_event1
-> using(event_id)
-> where grade_event1.date = '2008-09-23';
+--------+------------+-------+----------+
| name | date | score | category |
+--------+------------+-------+----------+
| billy | 2008-09-23 | 15 | Q |
| missy | 2008-09-23 | 14 | Q |
| johnny | 2008-09-23 | 17 | Q |
| jenny | 2008-09-23 | 19 | Q |
+--------+------------+-------+----------+
4 rows in set (0.00 sec)
3)同一表内的结连 #需要用到别名来区分同一个表
(4)删除数据行DELETE
格式:
mysql> DELETE FROM table_name WHERE which rows to delete;
例子:
#删除前
mysql> select * from president2 where first_name='ccc' and last_name='ccc';
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| ccc | ccc | NULL | WH | HB | 1991-04-01 | 2015-01-01 |
+-----------+------------+--------+------+-------+------------+------------+
1 row in set (0.00 sec)
#删除
mysql> delete from president2 where last_name='ccc' and first_name='ccc';
Query OK, 1 row affected (0.00 sec)
#删除后
mysql> select * from president2 where first_name='ccc' and last_name='ccc';
Empty set (0.00 sec)
(5)更新数据行UPDATE
格式：mysql> UPDATE table_name
-> SET which columns to change
-> WHERE which rows to update;
例子：
#修改前
mysql> select * from president2 where first_name='ccc' and last_name='ccc';
+-----------+------------+--------+------+-------+------------+-------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+-------+
| ccc | ccc | NULL | WH | HB | 1991-04-01 | NULL |
+-----------+------------+--------+------+-------+------------+-------+
1 row in set (0.00 sec)
#修改
mysql> update president2 set death='2015-1-1' where last_name='ccc' and first_name='ccc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#修改后
mysql> select * from president2 where first_name='ccc' and last_name='ccc';
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| ccc | ccc | NULL | WH | HB | 1991-04-01 | 2015-01-01 |
+-----------+------------+--------+------+-------+------------+------------+
1 row in set (0.00 sec)
#还原
mysql> update president2 set death=null where last_name='ccc' and first_name='ccc';
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| ccc | ccc | NULL | WH | HB | 1991-04-01 | NULL |
+-----------+------------+--------+------+-------+------------+------------+
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
阅读(1203) | 评论(0) | 转发(0) |
