mysql : 连接数据库必须安装mysql工具
-u ：指定用户
-p : 登录密码
-h : 指定登录位置，要登陆哪个IP地址上的数据库。
-P : 指定登录时候数据库的端口。
只要是处理用户的语句，都刷新下：flush privileges;
数据库存储引擎及特点：
1、MyISAM。
特点：
1)不支持事务
2)表级锁定形式，数据在更新时锁定整个表
3)数据库在读写过程中相互阻塞
会在数据写入的过程中阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
4)可以通过 key_buffer_size 来设置缓存索引，提高访问性能，减少磁盘IO的压力，但缓存只会缓存索引文件，不会缓存数据
5)采用MySIAM存储引擎数据单独写入或读取，速度过程较快而且占用资源相对较少
6)MyISAM存储引擎不支持外键约束，只支持全文索引。
7)每个MyISAM在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名之处文件类型：
.frm 文件存储表定义
.MYD 文件存储数据(MYData)
.MYI 文件存储索引文件(MYIndex)
场景：
公司业务不需要支持事务
对数据业务一致性要求不是非常搞的业务
使用读写并发访问相对较低的业务
数据修改相对较少的业务
服务器硬件资源相对比较差
2、InnoDB
特点：
1)支持事务：支持4个事务隔离级别
2)行级锁定，但是全表扫描仍然会是表级锁定
3)读写阻塞与事务隔离级别相关
4)具有非常高效的缓存特性：能缓存索引，也能缓存数据
5)表与主键以簇的方式存储
6)支持分区、表空间，类似oracle数据库
7)支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引
8)对硬件资源要求比较高
场景：
业务需要事务的支持
行级锁对高并发有很好的适应能力，但需要确保查询是通过索引来完成
业务数据更新较为频繁，如：论坛，微博
业务数据一致性要求较高，如：银行业务
硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力。
设置密码后登录-单例登录：
mysql -uroot -p -h localhost -P 3306
#远程连接时候 -h -P 必须指定。
远程登录时：
mysql -uzhang -p'111111' -h
192.168.88.81 -P 3307
# -p后跟的密码不能有空格、
设置密码登录-多实例登录：
mysql -uroot -p123123 -S /data/3306/mysql.sock
#登录实例3306端口数据库，用户：root。密码：123123
#需要注意的:
在设置多实例密码后需要修改启动脚本中的密码。
创建数据库普通用户：
create user zsan@'localhost' identified by '123123';
#用户zsan可以通过本地localhost登录到数据库。可以是IP，网段(192.168.88.0、24)，或 %：表示任意远端地址都可以登录。
flush privileges;
#刷新
登录时，mysql.user中的用户信息是验证的磁盘上的，所以在修改后只是在内存中，需要刷新写入到磁盘才会生效。
设置密码:
mysqladmin -u root -S /data/3306/mysql.sock password '123123'
#多实例：为mysql实例3306 设置密码。
mysqladmin -uroot password '123123'
#单例MySQL设置密码。
修改密码：
mysqladmin -uroot -p'123123' password 'newpwd123'
#单实例修改密码。
mysqladmin -uroot -p'123123' password 'newpwd123' -S /data/3306/mysql.sock password
#多实例密码修改
修改密码SQL语句：
set password for 'zsan'@'localhost'=password('123');
#修改普通用户密码
update mysql.user set passowrd=password('newpwd123') where user='root';
flush privileges;
#刷新：没刷新是在内存当中的，刷新后是写到磁盘文件中的。
# 数据库登录查询的是磁盘的文件。所以当修改用户密码后不刷新使用新密码是无法正常登录的。
创建数据库：
create database db_test;
#创建数据库，使用的是默认的字符集。
create database db_test default character set gbk collate gbk_chinese_ci;
#创建数据库时，指定字符集：gbk
create database db_test default character set utf8 collate utf8_general_ci;
#创建数据库时，指定字符集：utf8
数据库编译安装时指定的默认字符集：
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
#如果编译时，没有指定字符集默认的是拉丁的字符集。
需要注意的是：数据库字符集必须和操作系统等字符集一致才可避免出现乱码等现象
经常被读取数据的表用：MyISAM存储引擎
创建数据库表：
先进入到对应的数据库中：
create table t1 (id int unsigned not null auto_increment, name char(16) not null default ' ', info char(200) null, primary key (id));
#unsigned:只能为正数。 auto_increment:递增。
案例：
create table 'subject_comment_manager'(
'subject_comment_manager_id' bigint(12) not null auto_increment comment '主键',
'subject_type' tinyint(2) not null comment '素材类型',
'subject_primary_key' varchar(255) not null comment '素材的主键',
'subject_title' varchar(255) not null comment '素材的名称',
'edit_user_nick' varchar(64) default null comment '修改人',
'edit_user_time' timestamp null default null comment '修改时间',
'edit_comment' varchar(255) default null comment '修改的理由',
'state' tinyint(1) not null default '1' comment '0代表关系，1代表正常',
primary key ('subject_comment_manager_id'),
key 'idx_primarykey' ('subject_prmary_key' (32)), #括号内的32表示对前32个字符做
前缀索引。
key 'idx_subject_title' ('subject_title' (32)),
key 'index_nick_type' ('edit_user_nick' (32),'subject_type') #
联合索引，实际中没有
)
engine=InnoDB auto_increment=1 default charset=utf8;
创建数据库表时，指定存储引擎：
create talbe tbl_name(id int) engine=myisam;
#表的存储引擎指定为：MyISAM
#或者修改配置文件： default-storage-engine = InnoDB
#默认使用的存储引擎就是InnoDB
创建数据库表时，指定字符集：
create database db_name_utf8 default character set utf8 collate utf8_general_ci;
给数据库表添加主键：
alter table student change id id int paimary key auto_increment;
#给表添加主键
给数据库表添加普通索引：普通索引可以在多列上创建，索引类型最多的是：BTREE索引。
alter table student add index index_name(name);
#给student表在name字段上添加普通索引
alter table student drop index index_name;
#删除普通索引，index_name：是普通索引的名字。
创建唯一索引：(非主键)
create unique index index_age on student(age);
#一般来讲是用来约束表内容的。插入的是必须要唯一，
删除索引：
drop index index_name_dept on student;
# index_name_dept：是联合索引的索引名。
show index from student\G;
#查看索引。默认的索引类型是:BTREE
create index index_name_dept on student(name,dept);
#创建联合索引，允许列上有自己的索引
# Seq_in_index:2 表示为联合索引，1普通索引
给存在的表添加一个新的字段
alter table tbl_name add tbl_time date;
#默认情况下是写在所以字段最后的 tbl_name:表名 tbl_time:要添加的字段名
alter table tbl_name add tbl_time date first;
#将新添加的字段放到表的第一列
alter table tbl_name add tbl_nian year after tbl_time;
#在tbl_time字段后，添加新字段：tbl_nian 字段。
alter table 'etiantian' add 'firstphoto_url' varchar(255) default null comment '这是备注：第一张图片URL'；
#增加1个字段
alter table 'basic' add 'adhtml_top' varchar(1024) default null comment '顶部广告' , add 'adhtml_right' varchar(1024) default null comment '右侧广告'
#增加2个字段
alter table ett_ambiguity
change ambiguity_state ambiguity_state tinyint comment '状态：1正常，0失效';
#修改表字段
alter table 'ett_photo'
modify column 'photo_description' varchar(512) character set utf8 collate utf8_general_ci not null comment '描述' after photo_title;
#修改表字段
修改字段类型
alter table tbl_name modify age char(4) after name;
#
修改字段类型
修改表字段名称
alter table tbl_name change old_name new_name char(4) after name;
#
修改字段名称
修改数据库表名：
alter table tbl_oldname rename tbl_newname;
#将数据库中的 tbl_oldname 重命名为：tbl_newname
rename table tbl_oldname to tbl_newname;
#修改数据库表名。
修改数据库字符集：
alter database character set utf8;
修改数据库表字符集：
alter table tb_name character set utf8;
这样只是修改的是库和表本身的字符集，并没有修改其内容的字符集，只对新创建的表或记录生效。
已经有记录的字符的调整，必须先将数据导出，经过修改字符集后重新导入后才可以完成
修改数据库默认编码：
alter database [your db name] charset [your character setting]
如果是大数据量的表要修改，不可使用alter来修改。
导出表结构：
mysqldump -uroot -p123123 --default-character-set-utf8 -d dbname > aldb.sql
#--default-character-set=utf8 表示：UTF8字符集进行连接
#-d 只导表结构
show variables;
#查看数据库的一些变量
show status;
show global status;
#查看数据库使用状态
修改变量参数：
set global key_buffer_size=16
show variables like '%key_fuffer%';
#修改后查看是否修改成功。然后在配置文件中加入需要修改的参数，即可达到不重启服务参数即可生效
查看数据库连接情况:
show processlist;
show full processlist;
#这个查询出来的如果执行了多次，一条语句还在，那么这条SQL语句就是慢查询语句了，需要优化，创建索引等。
查询连接的状态：连接过数据库多少次：
mysql -uroot -p123123 -e "show global status;" | grep sel
#状态计数器
# 输出：Com_select ：显示的是查询过数据库多少次，自身也算一次查询次数。
mysql -uroot -p123123 -e "show global status;" | grep insert
#Com_insert：记录的是插入的计数，没插入一条数据，增加1.
show status; #查看当前会话的数据库状态信息
show global status; #查看整个数据库运行状态信息，很重要，并分析做好监控
show processlist; #查看正在执行的SQL语句，看不全。如果一条语句长时间没有结束，则这条语句是一条慢查询，需要优化查询。
show full processlist; #查看正在执行的完整SQL语句，圈。
set global key_buff_size = 1024*1024*32 #不重启数据库调整数据库参数，直接生效，重启后失效。集合修改配置文件，可以达到不重启MySQL达到修改参数生效。
show variables; #产看数据库的参数信息，例如：my.cnf里参数的生效情况
查看数据库表默认存储引擎：
show engines\G;
#查看支持的数据库存储引擎，以及支持的存户引擎。
修改数据库表的存储引擎：
alter table db_name.tbl_name engine=innodb;
alter table db_name.tbl_name engine=myisam;
#必须以 数据库.表名 的格式修改数据库表存储引擎。否则不成功
查看数据库表的存储引擎：
show table status from db_name where name='tbl_name';
#查看数据库中某个表的存储引擎。db_name:数据库名，tbl_name:数据库表名、
查看
MySQL字符规则：
show character set;
查看
MySQL当前的字符集：
show variables like 'character_set%';
查看当前所使用的数据库：
select database();
#返回当前正在use的数据库，没有则返回 : NULL
查看某些数据库：
show database like '%test%';
#查看名字包含test的数据库
查看当前数据库版本信息：
select version();
#返回当前数据库的版本信息
查看登录当前数据库的用户：
select user();
#返回当前的用户及主机
查看当前数据库时间：
select now();
#返回数据库当前的时间：2017-10-07 05:37:06
查看数据库某个表字段信息：
describe(desc) mysql.user;
show columns from mysql.user;
#查看mysql数据库中user表的字段信息。
查看数据库字段信息：
show create database db_test;
#查看数据库 db_test 的字符集信息等。
查看当前数据库中的表：
show tables;
#返回当前数据库中素有的数据表
查看数据表信息，排序：
select id,name from student order by asc;
#升序，默认就是升序
select id,name from student order by desc;
#降序显示。
多表查询：
select student.Sno,student,Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno;
#student.Sno:学号
#student.Sname:学生姓名
#course.Cname:课程表中的课程名
#SC.Grade:选科表中的成绩
##联表查询必须由外键，要有一个冗余的一个列，即：2个表中有相同的列，并且这个列是唯一的
表查询时查看是否有索引可以遵循：
explain select * from student where name='lisi'\G;
插入数据到数据库表：
insert into t2 (id,name,info) values (1,'lisi','xinxi');
#字符串必须用引号引起来，否则执行不成功。纯数字的不用引。
inset into t2 values (2,'wwu','xinxineirong');
#必须要注意值的顺序问题，必须跟字段一以对应
insert into t2 values (3,'lili','neirongxinxi'),(4,'tintt','xinxirong');
#插入多条数据信息。
insert into t1(id) select id from t2;
#
需要注意导入的字段必须在被导入的表中能够接收才可以
#将t2中的数据导入到t1表中：
相当于复制表的操作。
删除数据库：
drop database db_name;
#删除 db_name 数据库。
删除数据库中的表：
user db_name;
drop table tbl_name;
#先进入到对应的数据库，然后再执行要删除的表。
删除表中的数据：
delete from t2 where id=4;
#删除t2表中，id=4的一条数据
给存在的表添加一个新的字段
alter table tbl_name add tbl_time date;
#默认情况下是写在所以字段最后的
alter table tbl_name add tbl_time date first;
#将新添加的字段放到表的第一列
alter table tbl_name add tbl_nian year after tbl_time;
#在tbl_time字段后，加入：tbl_nian 字段。
删除已经存在的表字段：
alter table tbl_namedrop tbl_time;
#删除 tbl_name 的 tbl_time 字段。
修改数据库表名：
alter table tbl_oldname rename tbl_newname;
#将数据库中的 tbl_oldname 重命名为：tbl_newname
修改数据库表中的一条记录：
update tbl_name set info='infoxinxi' where id=5;
#修改表：tbl_name中 id=5的一条记录，info字段信息修改为：infoxinxi
update tbl_name set name='nicheng' where id between 4 and 10;
#修改表 tbl_name 中的id在4到10之间的，name的值为：nicheng
修改数据库表，字段类型：
alter table tbl_name modify name char(50);
#将 tbl_name 表的 name字段 改为char(50)。
但是,在修改表的某一个字段的属性或类型的时候，不能光写更改的类型，不修改的也需要写：
alter table t2 modify name char(50) notnull default '';
修改数据库表的存储引擎：
alter table db_name.tbl_name engine=innodb;
alter table db_name.tbl_name engine=myisam;
#必须以 数据库.表名 的格式修改数据库表存储引擎。否则不成功
修改表的字段名字：
alter table tbl_name change info infomation char(200) null;
#跟更换字段类型时注意的是一样的，也需要将不修改的属性也需要写上。重新定义。
# 将字段：info更改为：infomation
授权数据库用户：
grant: 授权命令
all privileges : 对应权限
on dbname.* : 目标：库和表，一般是某个数据库中的所有表
to username@localhost : 用户名和客户端主机
identified by 'passwd' : 用户密码
#上面的命令是：授权 localhost 主机上通过用户 username 管理 dbname 数据库的所有权限，密码为：passwod
生产环境授权用户最小，不是：all privileges
web连接授权：
grant select,insert,update,delete,create,drop on blog.* 'user1'@'192.168.88.%' identified by '123123';
在生成数据库表后，在用 revoke 将create、drop权限收回：
revoke create on blog.* from 'user1'@'192.168.88.%';
revoke drop on blog.* from 'user1'@'192.168.88.%';
#因为这2个权限：create drop 都是针对内容的操作。表结构，库，对表的修改都应该由数据库管理员在后台进行修改。
grant all on db_testa.* to 'user1'@'localhost' indentified by '123123';
#授权all(所有权限)在 db_testa 数据库里面的任意表，用户user1可以访问数据库 db_testa 下的所有表。
#注意授权的位置，也就是在哪个数据库可以有对应的操作。也就是在数据库 db_testa 中可以做任何操作。
grant create,select on *.* to 'user2'@'localhost' identified by '123123';
#授权user2用户可以在本地以123123密码登录，可以在所有数据库下执行：create,select操作
百分号 : % 匹配：
grant all on *.* to 'test'@'192.168.88.%' identified by '123123';
#授权某个网段登录到数据库
子网掩码配置：
grant all on *.* to 'test'@'192.168.88.0/255.255.255.0' identified by '123123';
连接数据库：
mysql -utest -p123123 -h 192.168.88.103;
#使用创建的数据库用户：test,登录数据库
192.168.88.0/24 是不可以正常登录的。
授权： 192.168.88.0/255.255.255.0 授权后是可以正常登录的。
操作用户的语句： flush privileges;
刷新一下。
如果登录多实例的数据库时，必须指定端口号才可以正常登录到数据库：
mysql -utest -p123123 -h 192.168.88.103 -S /data/3307/mysql.sock -P 3307
grant all privileges on *.* to system@'localhost' identified by '123123' with grant option;
#这样的授权与root是等价的，出于对数据库安全，删除root用户，删除空密码用户等信息。
#先授权等价root的用户，然后再删除不安全用户。mysql.user 表中的用户信息。
取消授权：
revoke select on *.* from 'user2'@'localhost';
#用户user2在本地访问数据库的时候，取消一个 select 的权限。
#取消多个时，逗号分割： 取消权限1,取消权限2
取消某个用户权限时，一定要跟授权时指定的数据库要对应上，否则是撤销不成功的。
mysql -uroot -p123123 -e "show grants for user1@localhost;" | grep grant | tial -1 | tr ',' '\n' > all.txt
#将user1的权限重定向到 all.txt 文件中。tr：将某个字符替换
查看用户授权：
show grants for 'user2'@'localhost';
#查看用户 user2 在本地可以执行的操作
授权用户重命名：
rename user 'lisi'@'localhost' to 'wwu'@'localhost';
#将本地登录的用户lisi 重命名为：wwu
删除授权用户：
drop user 'lisi'@'localhost';
#删除不用的用户：lisi
如果drop删除不了(一般是特殊字符或大写,可能主机名大写的时候)，可以用下面方式删除:
delete from mysql.user where user='root' and host='localhost';
flush privileges;
忘记密码时，跳过授权表验证登录数据库：
首先停止数据库
1、单例的MySQL跳过验证数据表：
mysqld_safe --skip-grant-tables --user=mysql &
停止时必须用：
mysqladmin -uroot -p'123123' shutdown
#不是多实例的停库方式。
1-1、修改主配置文件 my.cnf、添加跳过密码验证选项
[mysqld] 模块下添加参数：skip-grant-tables
# skip：跳过。grant:授权。
update mysql.user set password=password('123123') where user='root';
#必须用update，否则是没办法执行的
#删除主配置文件中的：skip-grant-tables . .../mysqld reload 重新加载主配置文件
2、多实例中跳过验证数据表：--skip-grant-tables放在后面
mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables &
登录对应的实例：
mysql -S /data/3306/mysql.sock
停止跳过验证数据表的MySQL进程：
mysqladmin -S /data/3306/mysql.sock shutdown
刷新：
flush privileges;
修改MySQL数据库中默认的不安全的数据库及用户：
1、删除mysql.user 中空密码的用户。
2、直接添加额外授权管理员，将mysql.user中的所有用户删除掉：
先授权再删除所有用户。
grant all privileges on *.* to system@'localhost' identified by '123123' with grant option;
#这样的授权与root是等价的。
delete from mysql.user;
3269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
