2016-08-22
敬叶 初稿

基本概念

数据结构

数据类型

MySQL基本数据类型如下所示：

  • 整数: tinyint、smallint、mediumint、int、bigint
  • 浮点数: float、double、real、decimal
  • 日期和时间: date、time、datetime、timestamp、year
  • 字符串: char、varchar
  • 文本: tinytext、text、mediumtext、longtext
  • 二进制: tinyblob、blob、mediumblob、longblob
类型 字节 范围
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)
类型 长度
char(n) 固定长度，最多255个字符
varchar(n) 固定长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
char(n)
存入时用空格补齐，查询时去掉空格，所以存储字符串末尾不能有空格。

关键字：

NULL
数据列可包含NULL值
NOT NULL
数据列不允许包含NULL值
DEFAULT
默认值
PRIMARY KEY
主键
AUTO_INCREMENT
自动递增，适用于整数类型
UNSIGNED
无符号
CHARACTER SET name
指定字符集

基本操作

增删查改

连接数据库
mysql -h IP -u NAME -p                  # 登录MySQL
mysql -h IP -u NAME -D DB -p            # 登录MySQL，指定使用的数据库
创建数据库
create database DB [OPTIONS];           -- 创建数据库
create table TABLE(DESC);               -- 创建表
create table TAB_NEW like TAB_OLD       -- 创建表
create table TAB_NEW as select C1,C2 from TAB_OLD definition only

如果表的描述信息很长，可以将其保存到一个文件，通过如下方式创建： 

mysql -D DB -h IP -u NAME -p < createtable.sql
删除数据库
delete from TABLE;                      -- 清除表中数据，保持increment id
truncate TABLE;                         -- 清除表中数据，清除increment id

drop table TABLE;                       -- 删除整张表
drop database DB;                       -- 删除整个数据库
修改数据库

修改表项： 

insert [into] TABLE [(C1, C2, ...)] values (V1, V2, ...);
update TABLE set C1=V1[, C2=V2...] [COND];
update students set age=age+1;          -- 将所有人年龄增加1
delete from TABLE [COND];               -- 删除数据
load data local infile 'FILE' into table TABLE;

修改表结构： 

alter table TABLE add C1 TYPE;          -- 添加列，可以用after C0指定插入位置
alter table TABLE change C1 C2 TYPE;    -- 修改列
alter table TABLE modify C TYPE;        -- 修改列
alter table TABLE drop C;               -- 删除列
alter table TABLE1 rename TABLE2;       -- 重命名表
alter table TABLE add primary key(C);   -- 添加主键
alter table TABLE drop primary key(C);  -- 删除主键
create [unique] index IDXNAME on TABLE(C1,C2);
drop index IDXNAME;
create view VNAME as select CMD;
drop view VNAME;
查询数据库
show databases;                         -- 查看有哪些数据库
use DB;                                 -- 使用一个数据库
show tables;                            -- 查看数据库下所有的表
show tables from DB;                    -- 直接查看某个数据库下的表
show columns from DB.TABLE;             -- 查看指定表的结构
show columns from TABLE from DB;        -- 查看指定表的结构
select C1[ , C2 ...] from TABLE [COND]; -- 查询满足条件的指定列数据
select * from TABLE [COND];             -- 查询表中所有内容

查询条件的写法： 

where C=V                               -- 支持所有比较运算：=,>,<,>=,<=,!=
where C1<V1 and C2>V2;                  -- 支持and/or组合查询
where C is [not] null
where C like "%王%"
where C in "str"

基本查询示例： 

select * from TABLE where C like '%micky%'
select * from TABLE order by C1,C2
select count as TOTAL_COUNT from TABLE  -- 总数
select sum(C) as SUM_VAL from TABLE     -- 总和

账户管理

create user 'USER'@'localhost' identified by 'PASSWORD';
grant [PERM] on [DB].[TABLE] to '[USER]'@'localhost';
grant all privileges on *.* to 'USER'@'localhost';
flush privileges;

drop user 'USER'@'localhost';
mysqladmin -u NAME -p password NEWPSWD  # 修改密码，会提示输入旧密码

用户权限：

ALL PRIVILEGES
对数据库的所有权限
CREATE
允许创建表和数据库
DROP
允许删除表和数据库
DELETE
允许删除表中的行
INSERT
允许插入行到表中
SELECT
允许读数据库
UPDATE
允许更新表中的行
GRANT OPTION
允许对其他用户的权限进行管理

数据备份

mysqldump -u user -p dbname > dbname.sql -- 备份数据库
mysqldump -u user -p dbname tblname > dbname-tblname.sql -- 备份表

use dbname                              -- 导入备份数据库
set names utf8;
source dbname.sql