Mysql基本数据操作

一.mysql中的逻辑对象

mysqld(process_id(threads)+memory+datadir)-->库－－＞表－－＞记录（由行与列组成）

什么是关系型数据库：
表与表发生关系，叫关系型

如何操作数据库中的数据（使用SQL语句）：
    动作：
        1、查询动作（最多的动作）
        2、修改动作
        3、删除动作
        4、增加动作

SQL(国际标准化,结构查询语言)
如果操作数据库中的数据：使用结构化查询语句（SQL）

mysql=国际标准化＋mysql官方的自定义标准
pl/sql=国际标准化＋oracle官方的自定义标准
Tsql=国际标准化＋microsoft官方的自定义标准


SQL的种类：
数据定义语言:DDL        create drop alter            －－定义表和库的结构
数据操作语言:DML        insert delete update select    －－对表中的数据进行增删改查的操作
数据控制语言:DCL            grant revoke            －－针对不同数据对象进行授权（全库/单库/表/列）

二.数据类型

2.1 浮点数

　FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

小(单精度)浮点数。允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。

M是小数纵位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。

如果指定UNSIGNED，不允许负值。

使用浮点数可能会遇到意想不到的问题，因为在MySQL中的所有计算用双精度完成。参见A.5.7节，“解决与不匹配行有关的问题”。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

普通大小(双精度)浮点数。允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308。这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。

M是小数总位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。双精度浮点数精确到大约15位小数位。

如果指定UNSIGNED，不允许负值。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

压缩的“严格”定点数。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。小数点和(负数)的‘-’符号不包括在M中。如果D是0，则值没有小数点或分数部分。DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。如果D被省略， 默认是0。如果M被省略， 默认是10。

2.2 字符串类型(普通字符串、比特位字符串、二进制字符串)

　　CHAR(50)        --定长字符串
     varchar(50)        --可伸缩长度的字符串


这是CHAR(1)的同义词。

　　[NATIONAL] VARCHAR(M) [BINARY]        --可伸缩的字符串

变长字符串。M 表示最大列长度。M的范围是0到65,535。(VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定。最大有效长度是65,532字节）。



VARCHAR是字符VARYING的简写。

BINARY属性是指定列的字符集的二元 校对规则的简写。排序和比较基于数值字符值。

VARCHAR保存时用一个字节或两个字节长的前缀+数据。如果VARCHAR列声明的长度大于255，长度前缀是两个字节。


BINARY(M)

BINARY类型类似于CHAR类型，但保存二进制字节字符串而不是非二进制字符串。

VARBINARY(M)

VARBINARY类型类似于VARCHAR类型，但保存二进制字节字符串而不是非二进制字符串。

TINYBLOB

最大长度为255(281)字节的BLOB列。

TINYTEXT

最大长度为255(281)字符的TEXT列。

BLOB[(M)]

最大长度为65,535(2161)字节的BLOB列。

可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但足以容纳M字节长的值的BLOB类型。

TEXT[(M)]

最大长度为65,535(2161)字符的TEXT列。

可以给出可选长度M。则MySQL将列创建为最小的但足以容纳M字符长的值的TEXT类型。

MEDIUMBLOB

最大长度为16,777,215(2241)字节的BLOB列。

MEDIUMTEXT

最大长度为16,777,215(2241)字符的TEXT列。

LONGBLOB

最大长度为4,294,967,295或4GB(2321)字节的BLOB列。LONGBLOB列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

 LONGTEXT

最大长度为4,294,967,295或4GB(2321)字符的TEXT列。LONGTEXT列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

ENUM('value1','value2',...)

枚举类型。只能有一个值的字符串，从值列'value1'，'value2'，...，NULL中或特殊 ''错误值中选出。ENUM列最多可以有65,535个截然不同的值。ENUM值在内部用整数表示。

SET('value1','value2',...)

一个设置。字符串对象可以有零个或多个值，每个值必须来自列值'value1'，'value2'，...SET列最多可以有64个成员。SET值在内部用整数表示。


    1、整数列 tinyint smallint   MEDIUMINT int bigint
    2、小数列    float double decimal
    3、文本列 tinytext medu longtext(4G)
    4、字符串列 char varchar binary  varbinary blob longblob
    5、日期时间 year date time datetime timestamp
    6、列特殊属性：自动增长列／主键列／是否允许为空／列的默认值／是否允许重复

2.3 整数

　create table t1(id tinyint(2), name varchar(50))

TINYINT[(M)] [UNSIGNED] [ZEROFILL] [not null] [comment]

很小的整数。带符号的范围是-128到127。无符号的范围是0到255。

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。

INT[(M)] [UNSIGNED] [ZEROFILL]

普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

这是INT的同义词。

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。

2.4 日期和时间类型概述

　　 DATE

　　　　日期。支持的范围为'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式显示DATE值，但允许使用字符串或数字为DATE列分配值。

　　DATETIME

　　日期和时间的组合。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。

　　TIMESTAMP[(M)]

　　时间戳。范围是'1970-01-01 00:00:00'到2038年。

　　TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。

　　TIME

　　时间。范围是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式显示TIME值，但允许使用字符串或数字为TIME列分配值。

　　YEAR[(2|4)]

　　两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，表示从1970年到2069年。MySQL以YYYY 格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值。

三.DDL操作

3.1 create(database/table/user):

　　库（其实对磁盘上的物理文件就是一个文件夹）：　

--创建库
--default charset utf8,指定编码为utf8
create database  IF NOT EXISTS db1  default charset utf8;

--查看库是如果建立的
show create database db1;

--查询系统默认语言编码
show variables like '%char%'; 

--修改一个已经存在数据库的语言编码
alter database db01 default charset gbk;

--只能查询最后一次warning的信息
 show warnings; 

　　表：二维表（由行和列来组成的表，表必须存在于某个库中）

--使用库
use db1;

--创建表，格式如下
create table 表名(
    列名  类型  是否可以为空，
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
--例 ：
create table db1.t1(id int auto_increment,name varchar(50), gender char(6),salary decimal(10,2), dept_id int,primary key(id));

--查看表结构
desc t1;

--查询表所有内容
select * from db1.t1;

--查看表是如何创建的
show create table db1.t1;

　　创建用户

--创建一个用户
create user user01@'localhost' identified by '123';

--设置用户的密码
set password for user01@'localhost'=password('123');

--验证用户是否创建成功
select user from mysql.user where user='user01';

--查看用户权限
show grants; 

3.2 drop:    --删除数据库对象（表／库／用户）

删除表/库

--删除表
drop table t1; 

--验证表是否删除成功
show tables; 

--清空表，只清空表中数据，不会删除表结构
truncate table t1;

--删除库
drop database db1;

--验证库是否删除成功
show databases;

　　删除用户

--删除用户
drop user user01@'localhost';

--验证用户是否删除成功
select user from mysql.user where user='user01';

--删除一个匿名用户
drop user ''@'localhost';

--重命名
rename user u01@'localhost' to u001@'localhost';

　　3.3 alter（修改已经存在数据库对象（库/表/列）的属性）

--修改数据库语言编码
ALTER DATABASE db1 DEFAULT CHARACTER SET gbk;    

　　修改表的属性：

--表中添加一列数据
alter table t1 add gender char(6);

--添加外键
alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
--例 ：
alter table t1 add constraint fk_t1_depart_id foreign key t1(dept_id) references depart(id);

--删除外键
alter table 表名 drop foreign key 外键名称

--添加主键
alter table 表名 add primary key(列名);
--例：
alter table depart add primary key(id) auto_increment;

--表id列自动增加，自动增加列必须是主键
alter table depart modify id int auto_increment;

--删除主键
alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

--在name列后面增加一列叫addr
ALTER TABLE t1 add addr char(50) AFTER name;

--删除addr这个列
alter table t1 drop addr;

--修改列名和数据类型
alter table t1 change id ID bigint;

--修改列的数据类型
alter table t1 modify ID int;

--重命名表
alter table t1 rename t01;    

--查看数据库有哪些存储引擎
show engines;

--修改表的存储引擎
alter table t1 engine MyISAM;

--查看修改存储引擎是否成功
show create table t1;

--修改表的语言编码
alter table t1 DEFAULT CHARSET=utf8;

四. DML操作    --操作表中的记录

　　4.1 insert 插入记录

--向指定列插入数据
insert into depart set id=1,name='sales';

--插入多条记录
insert into depart(name) values('maket'),('IT'),('HR');

insert into 表 (列名,列名...) select (列名,列名...) from 表
--例：
--查出前4张表，插入到当前表（注意表结构要一致）
insert into db1.t1 select * from db1.t2 where id<5; 

　　4.2 update    更新/修改记录

--更新表数据
update t1 set salary=9000 where name='ellen';

--更改root用户对应在localhost登录的密码
update mysql.user set Password='redhat' where user='root';
update mysql.user set Password=password('redhat') where user='root';

--刷新权限表（重新到磁盘上把mysql库中跟权限相关的重载一次）
flush privileges;

　　4.3 delete/truncate 删

--删除mysql中的匿名用户
delete from mysql.user where user='';

--删除整个表,一行一行删除,所有的操作都会被记录至事务日志中
delete from t2;  

--指定条件删除
delete from t2 where id=1; 

--一次性全部删除,不会被记录至事务日志中.
truncate t2; 

　　4.4 查 select

通配符：
    %    匹配0个或任意多个字符
    _    匹配一个字符
    ＝    精确匹配
    like    模糊匹配
    regex(^  .  .* .....)        使用正则表达式来匹配
排序：
    order by    排序
            asc        升序排列结果
            desc    降序排列结果
    group by    聚合
    
    distinct    去除重复的行

例：表信息如下

--查询表中所有的列对应的值（全表扫描）
select * from db02.t1;

--查询表中指定列
select host,password,user from mysql.user;

--给列取别名，增加可读性
select host as 主机名,password as 密码,user as 用户名 from mysql.user;

--模糊匹配
select * from t1 where name like '%i%';

--精确匹配
 select * from t1 where name='wang5'; 

--条件匹配
select * from t1 where english < 60;

 --支持正则表达式
select * from t1 where name regexp '.*[0-9]?.*' ;  

--任意数字一次或一次以上
select * from t1 where name regexp '.*[0-9]+.*' ; 

select user,password,host from mysql.user where host regexp '([0-9]{1,3}.){3}([0-9]{1,3})';

排序

--降序排列
select * from t1 order by math desc;

--升序排列
select * from t1 order by math asc;

　　去除重复行

select distinct id from t1 ;

　　聚合group by

select  * from t1 group by name


select * from t1 group by id having id <= 2;

特别的：group by 必须在where之后，order by之前

　　分页函数

--显示前10行
select * from t1 limit 10; 

--显示11至20行
select * from t1 limit 10,10;

--显示第1行 
select * from t1 limit 1;

--起始位置，偏移量  第二行的下2行
select * from t1 limit 4,2;     -- 显示5,6行

--显示英语前三名
select * from t1 order by english desc limit 3;   

　　and or not    逻辑运算

--and两个条件同时满足
select * from t1 where math >= 60 and english >= 60;

--or 其中一个条件满足
select * from t1 where math >= 60 or english >= 60;

--not 不满足
select * from t1 where math >= 60 and not  english >= 60;

--两个条件之间
select * from 表 where id between 5 and 16;

   sum() avg() max() min() count() 内置函数

--sum求合
select name,sum(math),sum(english) from t1;

--avg求平均值
select name,avg(math),avg(english) from t1;

--最大值
select name,max(english) from t1;

--最小值
select name,min(english) from t1;

--统计个数
select count(*) from t1;

　　表的连接:纵向连接/内连接/左连接/右连接

左连接（以左表为标准连接右表）:
letft join=left [outer] join

表结构

select * from t1 left join depart on t1.dept_id=depart.id;

右连接（以右表为标准连接左表）:
right join= right outer join

select * from t1 right join depart on t1.dept_id=depart.id;

内连接:取多表之间的交集（自动忽略空值）

select * from t1 inner join depart on t1.dept_id=depart.id;

　　纵向连接:

--组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

五.DDL（grant/revoke）

　　5.1 grant

权限分类：
USAGE                            --无权限,只有登录数据库,只可以使用test或test_*数据库
ALL                                --所有权限
select/update/delete/super/slave/reload    --指定的权限
with grant option                    --允许把自己的权限授予其它用户(此用户拥有建立账号的权限)

权限级别：
    1、*.*            －－全库、全表（mysql.user）
    2、mysql.*        －－mysql库下所有表（某库中的所有表）(mysql.db)
    3、mysql.user        －－mysql库中user表（单表）(mysql.table_priv)
    4、mysql.user.host    －－mysql库中user表的host列(mysql.columns_priv)

用户权限：
    username@localhost
        1、username            --你被授予了什么权限
        2、username/password    --账号密码
        3、localhost            --表示你可以在哪里登录（这里表示只能本机通过socket登录）
    
        user@localhost        －－表示user只能在本地通过socket登录服务器
        user@192.168.0.1        －－表示user用户只能在192.168.0.1登录数据库服务器
        user@192.168.0.0/255.555.255.0
                            －－指定某个子网的主机可以登录数据库
        user@%                －－表示user用户能在所有的机器上登录数据库服务器

例

新建user02用户， 要求user02只能在本机登录而且对DB这个库的所有表拥有完全控制的权限:
mysql> grant all on DB.* to user02@'localhost' identified by '123';
mysql> select user,password,host from mysql.user;  --查看用户创建是否成功

更改user02在所有机器上都能登录数据库.
mysql> update mysql.user set host='%' where user='user02'; 
mysql> flush privileges;



让user02可以把自己的权限授予其它用户:
mysql> grant ALL on DB.* to user02@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)


查看权限:
mysql> show grants;    --查看自己的权限
mysql> show grants for user02@'%';    --查看指定用户的权限 


权限的作用范围(*.* database.* database.table)
mysql> grant select,update on DB.t3 to user04@'localhost' identified by '123'; 

列的授权：
mysql> grant update (user) on mysql.user to user03@'%';
mysql> grant update (user,host,password) on mysql.user to user03@'%';

权限的保存位置：
mysql.user            --所有mysql用户的账号和密码，以及对用户对全库全表权限（*.*）
mysql.db                --非mysql库的授权都保存在此(db.*)
mysql.table_priv        --某库某表的授权(db.table)
mysql.columns_priv        --某库某表某列的授权(db.table.col1)
mysql.procs_priv        --某库存储过程的授权

5.2 revoke取消权限

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';    --撤消指定的权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... --撤消所有的权限

mysql> drop user user02;        --撤消所有权限后把用户一并删除

忘记root密码的操作:

mysql的用户验证逻辑：
mysqluser-->mysqld(3306/scoket)-->(host/user/password)-->mysql.user


1、关闭正在运行的mysql服务器
2、重新启mysql，但启动的过程让mysql跳过用户验证（mysql.user）,然后改密码
3、安全关闭mysql,然后再启动，这样密码就被重置了。
# service mysql stop 或 pkill -9 mysql
# cd /opt/mysql-glibc
# /opt/mysql-glibc/bin/mysqld_safe --defaults-file=/data2/my.cnf --skip-grant-tables &
mysql> update mysql.user set password=password('147258369') where user='root';
# mysqladmin shutdown
# /opt/mysql-glibc/bin/mysqld_safe --defaults-file=/data2/my.cnf &