Mysql 常用操作

一、mysql 5.7修改root密码

如果 MySQL 正在运行
首先杀之：

killall -TERM mysqld。

运行

mysqld_safe --skip-grant-tables &

如果此时不想被远程连接：

mysqld_safe --skip-grant-tables --skip-networking &

使用mysql连接server

mysql

更改密码

update mysql.user set authentication_string=password('1122qqWW') where user='root' and Host = 'localhost';

特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了而是将加密后的用户密码存储于authentication_string字段

mysql> flush privileges; 
mysql> quit;

修改完毕。重启

killall -TERM mysqld
service mysql start

二、创建用户

创建一个只能在本机登陆的用户

create user '用户名'@'localhost' identified by '用户的密码';

创建一个只能由特定远程主机使用的帐户

create user '用户名'@'远程IP地址' identified by '用户的密码';

创建一个本机和远程主机都能使用的账户

create user 'unlimituser'@'%' identified by '用户的密码';

三、mysql 赋给用户远程权限 grant all privileges on

登陆
在本机上使用命令行登陆,并切换到mysql库

mysql -uroot -p
use mysql;

更改表
要查看user表中user=’root’的对应情况，根据实际需求来更改表信息

select host,user from user where user='root';
# 如果上面查询到有信息,但host为localhost或其他值，就需要根据实际需求来更新表信息

update user set host='%' where user='root'
# 如果想任何的IP地址都可以使用root用户来远程登陆（注意如果上面的查询语句，查询出来有多条，就需要做一些处理，比如多加一个条件如 where user='root' and host='localhost'或先删除其他人记录只保存一条user='root'的记录）

更改权限
使用grant all privileges on来更改用户对应某些库的远程权限
语法模板

grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码' with grant option;
flush privileges;

　　#库名:要远程访问的数据库名称,所有的数据库使用“*”
　　#表名:要远程访问的数据库下的表的名称，所有的表使用“*”
　　#用户名:要赋给远程访问权限的用户名称
　　#IP地址:可以远程访问的电脑的IP地址，所有的地址使用“%”
　　#密码:要赋给远程访问权限的用户对应使用的密码

示例：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'lxh' WITH GRANT OPTION; # 所有的地址都可以使用root用户，密码为lxh远程访问所有的数据库
flush privileges;
GRANT ALL PRIVILEGES ON testdb.* TO 'lxh'@'172.16.52.225' IDENTIFIED BY 'lxh' WITH GRANT OPTION; # IP为172.16.52.225的电脑可以使用lxh用户，密码为lxh远程访问数据库testdb的所有表

注意

需要注意mysql的配置文件中的bindaddress 的参数和skip-networking 配置
bindaddress : 设定哪些ip地址被配置，使得mysql服务器只回应哪些ip地址的请求),最好注释掉该参数或设置成为127.0.0.1以外的值
skip-networking : 如果设置了该参数项，将导致所有TCP/IP端口没有被监听,也就是说出了本机，其他客户端都无法用网络连接到本mysql服务器，所以应该注释掉该参数

四、常用SQL语句

　　我将由外到里进行编写（数据库到表再到数据）

　　库层：

　　1、SHOW DATABASES;

　　2、CREATE DATABASE dbname;

　　3、DROP DABABASE dbname;

　　4、ALTER DATABASE dbname set=新字符集 collate=新校对集;

　　5、USE dbname;

　　6、SHOW CREATE DATABASE dbname;

　　表层：

　　1、创建表形式：

　　CREATE TABLE tabname

　　(

　　字段名1，数据类型 [列级别约束条件] [默认值],

　　　　　…

　　　　[表级别约束条件]

　　);

　　 a、使用主键约束PRIMARY KEY

　　两个地方添加：1、列约束条件位置(PRIMARY KEY) 2、表级别约束条件位置([CONSTRAINT <约束名>] PRIMARY KEY [字段名])

　　 b、使用唯一性约束UNIQUE

　　同上两个地方添加：1、UNIQUE 2、[CONSTRAINT <约束名>] UNIQUE(<字段名>)

　　与主键的区别，两者同样要求唯一，但是UNIQUE可以是NULL，主键不可以。且一个表中PRIMARY KEY只能是一个，UNIQUE可以存在多个。

　　 c、使用外键约束(InnoDB) FOREIGN KEY

　　一个地方添加：1、表级别约束条件位置添加 [CONSTRAINT <外键名>] FOREIGN KEY 字段名1[,字段名2,…] REFERENCES <主表名> 主键列 [,主键列2,….]

　　注意：两个表之间有了外键的联系，不能直接删除主表，否则会出错！若必须要删除主表，可以先删除外键，再来删除主表。

　　d、非空约束 NOT NULL

　　一个地发添加：1、列约束条件位置 NOT NULL

　　 e、使用默认约束 DEFAULT

　　一个位置添加：1、列约束条件位置 DEFAULT 默认值

　　 f、设置表的属性值自动增加 AUTO_INCREMENT

　　一个位置添加：1、列约束条件位置 AUTO_INCREMENT

　　注意：此时的列必须是主键或者唯一键！

　　索引问题：以下是普通索引、全文索引、空间索引。上面创建约束的同时也是创建相应的索引，主键索引、唯一索引等等。

　　 g、普通索引 INDEX/KEY

　　一个位置添加：1、列约束条件位置 INDEX(<字段名>);

　　 h、全文索引(MyISAM) FULLTEXT

　　一个位置添加：1、列约束条件位置 FULLTEXT KEY [‘名字’] (‘字段名’);

　　 i、创建空间索引(MyISAM) SPATIAL

　　一个位置添加：1、列约束条件位置 SPATIAL KEY [‘名字’] (‘字段名’);

　　2、DESCRIBE/DESC tabname;

　　3、SHOW CREATE TABLE tabname;

　　4、DROP TABLE [IF EXISTS] <表名>;

　　5、ALTER TABLE <表名> ENGINE=<更改后的存储引擎>;

　　6、ALTER TABLE <旧表名> RENAME [TO] <新表名>;

　　7、ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

　　8、ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

　　9、ALTER TABLE <表名> ADD <新字段名> <新字段类型> [约束条件] [FIRST|AFTER 已存在的字段名];

　　10、ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>；

　　11、ALTER TABLE <表名>DROP <字段名>;

　　#在已有的表上创建索引

　　12、ALTER TABLE <表名> ADD INDEX [索引名] (字段名);

　　13、ALTER TABLE <表名> ADD PRIMARY KEY (字段名);

　　14、ALTER TABLE <表名> ADD FOREIGN KEY (字段名) REFERENCES <主表名> (主键字段);

　　15、ALTER TABLE <表名> ADD UNIQUE KEY (字段名);

　　16、ALTER TABLE <表名> ADD FULLTEXT KEY(字段名);

　　17、ALTER TABLE <表名> ADD SPATIAL KEY (字段名);

　　18、ALTER TABLE <表名> ALTER <字段名> SET DEFAULT 默认值;

　　#删除索引

　　19、ALTER TABLE <表名> ALTER <字段名> DROP DEFAULT;

　　20、ALTER TABLE <表名> DROP PRIMARY KEY;

　　21、ALTER TABLE <表名> DROP FOREIGN KEY 外键名;

　　22、ALTER TABLE <表名> DROP INDEX 索引名;

　　23、DROP INDEX index_name ON tabname;

　　数据层：

　　1、插入数据

　　INSERT INTO tabname (column_list) VALUES (value_list);

　　INSERT INTO tabname (column_list) VALUES (value_list1),(value_list2),….,(value_listn);

　　INSERT INTO tabname SET 字段名1=值1,字段名2=值2,…..,字段名n=值n;

　　2、更新数据

　　UPDATE tabname SET column_name1=value1,column_name2=value2,…column_namen=valuen WHERE (condition);

　　3、删除数据

　　DELETE FROM tabname[WHERE <condition>];

　　TRUNCATE [TABLE] tabname;

　　DELETE与TRUNCATE的区别：TRUNCATE直接删除整个表，再创建一个一模一样的表。

　　4、数据查询

　　(1)、SELECT {*|<字段列表>}

　　[

　　FROM <表1>,<表2>,…,<表n>

　　WHERE <表达式>

　　GROUP BY <分组字段>

　　HAVING <expression>

　　ORDER BY <排序字段>

　　LIMIT <num>

　　]

　　(2)、SELECT [字段1,字段2,…,字段n] from [表|视图] WHERE [查询条件];

　　(3)、连接查询

　　a、内连接查询 SELECT tab1.* tab2.* FROM tab1 INNER JOIN tab2 ON <condition>；

　　b、左连接查询 SELECT tab1.* tab2.* FROM tab1 LEFT [OUTER] JOIN tab2 ON <condition>；

　　c、右连接查询 SELECT tab1.* tab2.* FROM tab1 RIGHT [OUTER] JOIN tab2 ON <condition>；

　　(4)、合并查询结果

　　 SELECT column,… FROM tab1

　　 UNION [ALL]

　　 SELECT column,…FROM tab2;