mysql客户端使用、SQL语言介绍、MYSQL数据库使用、数据类型、数据增删改查

mysql客户端使用

mysql使用模式：
交互式模式：
可运行命令有两类：
1.客户端命令：
    \h, help
    \u，use
    \s，status
    !，system
2.服务器端命令：
    SQL语句， 需要语句结束符；

脚本模式：
mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
mysql> source /path/from/somefile.sql

mysql客户端选项：
-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e “SQL“ 执行SQL命令
-V, --version 显示版本
-v --verbose 显示详细信息
–print-defaults 获取程序默认使用的配置

服务器监听的两种socket地址：
ip socket: 监听在tcp的3306端口，支持远程通信
unix sock: 监听在sock文件上，仅支持本机通信
如：/var/lib/mysql/mysql.sock)
host为localhost,127.0.0.1时自动使用unix sock

SQL语言介绍

在数据库系统中，SQL语句不区分大小写
SQL语句可单行或多行书写，以“;”结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性
注释：

SQL标准：
/*注释内容*/ 多行注释
-- 注释内容 单行注释，注意有空格
MySQL注释：
#

SQL语句分类：
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER

DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE

DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK

DQL：Data Query Language 数据查询语言
SELECT

SQL语句构成：
Keyword组成clause
多条clause组成语句

示例：
SELECT * SELECT子句
FROM products FROM子句
WHERE price>400 WHERE子句
说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字

MYSQL数据库使用

关系型数据库的常见组件
数据库：database
表：table
行：row
列：column
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划

命名规则：
必须以字母开头
可包括数字和三个特殊字符（# _ $）
不要使用MySQL的保留字
同一database(Schema)下的对象不能同名

数据库操作
创建数据库同时设置字符集和排序规则：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] ‘DB_NAME’;
CHARACTER SET 'character set name’COLLATE ‘collate name’

修改数据库：
ALTER DATABASE DB_NAME character set utf8;

删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] ‘DB_NAME’;

查看支持所有字符集：SHOW CHARACTER SET;

查看支持所有排序规则：SHOW COLLATION;

获取命令使用帮助：
HELP KEYWORD;

查看数据库列表：
SHOW DATABASES;

获取帮助：HELP CREATE TABLE;

创建表
命令：CREATE TABLE
1.直接创建
2.通过查询现存表创建；新表会被直接插入查询而来的数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options]
[partition_options] select_statement 

示例：
CREATE TABLE test2 select * from test;

3.通过复制现存的表的表结构创建，但不复制数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name }

示例：
CREATE TABLE test2 LIKE test;

Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2
type2 修饰符, …)
字段信息
col type1
PRIMARY KEY(col1,…)
INDEX(col1, …)
UNIQUE KEY(col1, …)
示例：

CREATE TABLE 'test'(id int unsigned auto_increment primary key,name varchar(20) not null,gender char(1) ENUM('m','f') default 'm',mobile char(44));
修饰符不写not null默认是null

表选项：
ENGINE [=] engine_name
SHOW ENGINES;查看支持的engine类型

ROW_FORMAT [=]
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
修改表
ALTER TABLE ‘tbl_name’
字段：
添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]

删除字段：drop

修改字段：
alter（默认值）, change（字段名）, modify（字段属性）

索引:
添加索引：add index
删除索引：drop index

示例：

ALTER TABLE test RENAME test2;
test的表名修改为test2

ALTER TABLE test2 ADD phone varchar(11) AFTER name;
在test2表中name字段后面添加phone字段

ALTER TABLE test2 MODIFY phone int;
修改phone字段类型为int

ALTER TABLE test2 CHANGE COLUMN phone mobile char(11);
修改phone字段为mobile

ALTER TABLE test2 DROP COLUMN mobile;
删除mobile列

ALTER TABLE test2 character set utf8;
修改表字符集，一般不这么用

查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;

其他命令：
查看所有的引擎：SHOW ENGINES
查看表：SHOW TABLES [FROM db_name]
查看表结构：DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
删除表：DROP TABLE [IF EXISTS] tb_name
查看表创建命令：SHOW CREATE TABLE tbl_name
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name’
查看库中所有表状态：SHOW TABLE STATUS FROM db_name

数据类型

数据类型：
数据长什么样
数据需要多少空间来存放
系统内置数据类型和用户定义数据类型

MySql支持多种列类型：
数值类型
日期/时间类型
字符串(字符)类型

选择正确的数据类型对于获得高性能至关重要，三大原则：
更小的通常更好，尽量使用可正确存储数据的最小数据类型
简单就好，简单数据类型的操作通常需要更少的CPU周期
尽量避免NULL，包含为NULL的列，对MySQL更难优化

整数型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(±9.22*10的18次方)
加unsigned，不使用符号，此时最大值翻倍，如：tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

BOOLEAN
BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

浮点型(float和double)，近似值
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

定点数
在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

字符串(char,varchar,_text)
char(n) 固定长度，最多255个字符
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合

char和varchar：
char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此

char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节

char类型的字符串检索速度要比varchar类型的快

varchar和text：
varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
text类型不能有默认值
varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

二进制数据：BLOB
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，
而Blob是以二进制方式存储，不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集，BLOB不用指定字符集

日期时间类型
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数
据类型的字段可以存放这条记录最后被修改的时间

修饰符
所有类型：
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
UNIQUE KEY 唯一键
CHARACTER SET name 指定一个字符集

数值型
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号

数据增删改查

插入数据
INSERT INTO：
一次插入一行或多行数据

简化写法：
INSERT tbl_name [(col1,…)] VALUES (val1,…), (val21,…)

示例：

test表中有3个字段，ID，name，age，其中ID是主键，可以自动增长
可以选填数据，不选默认全部添加
INSERT test(name,age) values("张三",20);
INSERT test values(2,"李四",21);

也可以同时插入多条，
INSERT test(name,age) values("小明",22),("小王",21);

其他写法：
INSERT test set name='张三',age=20;
*字符串需要加引号，数字不用

查询A表的结果插入B表
INSERT test2(name2,age2) select name,age from test;
*test1表的字段名称可以跟test表的不一样，但是数据类型必须相同

修改数据
UPDATE：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]
注意：一定要有限制条件，否则将修改所有行的指定字段

限制条件：
WHERE
LIMIT

使用mysql的时候建议加上安全选项：-U|–safe-updates| --i-am-a-dummy
也可以在客户端配置中添加safe-updates
示例：

UPDATE test set age=30 where id=1;

删除数据
DELETE:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]
可先排序再指定删除的行数

注意：一定要有限制条件，否则将清空表中的所有数据
限制条件：
WHERE
LIMIT

TRUNCATE TABLE tbl_name; 清空表

DELETE from test where id=1;

查询数据
SELECT命令
字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, …

WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, …)
IS NULL
IS NOT NULL

DISTINCT 去除重复列
SELECT DISTINCT gender FROM students;

LIKE:
% 任意长度的任意字符
_ 任意单个字符

RLIKE：正则表达式，索引失效，不建议使用

REGEXP：匹配字符串可用正则表达式书写模式，同上

逻辑操作符：
NOT
AND
OR
XOR

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件

ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制

对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读
示例：

name		age
zhangsan	20
lisi		21
wangwu		22
xiaoming	20
xiaohong	19

查询age大于20的所有数据
select * from test where age>20;

查询age大于20的name
select name from test where age>20;

查询name字段中包含an的所有数据
select * from test where name like '%an%';

查询age后合并重复的结果
select distinct age from test;
查询结果：
age
20
21
22
19

查询city字段为空的数据
select * from test where city is null;

分组查询示例：

name		age		city
zhangsan	20		beijing
lisi		21		beijing
wangwu		22		shanghai
xiaoming	20		shanghai
xiaohong	19		shanghai
x1			22		guangzhou
x2			23		guangzhou

查询城市的平均age并分组
select city,avg(age) from test group by city;
查询结果：
city		age
beijing		20.5000
shanghai	20.3333
guangzhou	22.5000

查询城市的平均age并分组，然后过滤出beijing的结果
select city,avg(age) from test group by city having city='beijing';
查询结果：
city		age
beijing		20.5000

如果要先过滤再分组统计要这样写：
select city,avg(age) from test where city='beijing' group by city;
查询结果：
city		age
beijing		20.5000

查询平均年龄后过滤平均值
select city,avg(age) as av from test group by city having av>22;
查询结果：
city		age
guangzhou	22.5000

排序示例：

默认排序是升序
select * from test order by age;
查询结果：
name		age		city
xiaohong	19		shanghai
zhangsan	20		beijing
xiaoming	20		shanghai
lisi		21		beijing
wangwu		22		shanghai
x1			22		guangzhou
x2			23		guangzhou

降序
select * from test order by age desc;
查询结果：
x2			23		guangzhou
wangwu		22		shanghai
x1			22		guangzhou
lisi		21		beijing
zhangsan	20		beijing
xiaoming	20		shanghai
xiaohong	19		shanghai

如果碰到数字类型的字段是空的，排序要把空排最后，在字段上加-
select * from test order by -age;

分组查询完后排序
select city,avg(age) as av from test group by city order by av;
查询结果：
city		age
shanghai	20.3333
beijing		20.5000
guangzhou	22.5000

排序后取前2个
select city,avg(age) as av from test group by city order by av limit 2;
查询结果：
city		age
shanghai	20.3333
beijing		20.5000

跳过第一个取后面2个
select city,avg(age) as av from test group by city order by av limit 1,2;
查询结果：
city		age
beijing		20.5000
guangzhou	22.5000

查询年龄是20 21 22岁的
select * from test where age in(20,21,22);
查询结果：
name		age		city
wangwu		22		shanghai
x1			22		guangzhou
lisi		21		beijing
zhangsan	20		beijing
xiaoming	20		shanghai