mysql常用命令

创表、索引

INDEX普通索引:一个表中可以有多个INDEX字段,字段值可以重复,可赋予null值,经常用来查询条件的字段设置INDEX字段

UNIQUE唯一索引:一个表中可以有多UNIQUE字段,对应的字段不可重复,当将其字段值修改为不允许为NULL,则此字段限制与主键相同

primary key主键:对应的字段值不逊于有重复,且不允许赋予NULL,如需多个字段都作为主键,称为复合主键,必须一起创建,通常与AUTO_INCREMENT(自动+1,常用于ID号)连用.

foreign key外键:让当前的字段值在另外一个表中字段值的范围内选择,存储引擎必须是INNODB,字段类型要一样,被参照字段必须是索引类型的一种(primary key)

创表时增加索引:

create table 表名(

字段名1 字段类型(宽度) 约束条件,

字段名2 字段类型(宽度) 约束条件,

.. ..

index(字段名)

#unique(字段名)

#primary key(字段名)

#foreign key(表A的字段名(表A的主键))

references 表B(字段名)

on update cascade

on delete cascade

);

创表后增加索引:

create index 索引名 on 表名(字段名);

create unique index 索引名 on 表名(字段名);

alter table 表名 add primary key(字段名);

alter table 表名 add foreign key 表A(字段名) references 表B(字段名) on update cascade on delete cascade;

删除指定表的索引字段

drop index 索引名 on 表名;(删除unique一样)

alter table 表名 drop primary key;(如有自增属性,必须先去掉)

alter table 表名 drop forreign key 约束条件;(约束条件可通过show create table 表名\G;查看)

############################################################################################################

存储引擎:

mysql>show engines #查看支持的存储引擎

mysql>show variables like "default_storage_engine"; #查看当前的存储引擎

mysql>set default_storage_engine=新引擎名; #修改存储引擎

默认存储引擎可通过修改/etc/my.cnf配置文件实现

############################################################################################################

数据导入/导出、表插入/更新/删除、查询及匹配条件

导入

mysql>show variables like "%secure%"; #查看默认安全文件位置

mysql>load data infile '文件路径' into table 表名 fields terminated by "分隔符" lines terminated by '\n换行符';

导出查询结果

mysql>select * from 库.表 where 条件 into outfile '文件路径' fields terminated by '分隔符' lines terminated by '\n换行符';

插入表记录

mysql>insert into 表名(字段1,..字段N) values(字段1值,..字段N值),(字段1值,..字段N值);字符类型值需要引号括起来

更新表记录

mysql>update 表名 set 字段1=字段1值 where 条件;若不使用where限定条件,会更新所有记录

删除表记录

mysql>delete from 表名 where 条件; #仅删除符合条件的记录

mysql>delete from 表名; #删除所有的表记录

查询表记录

mysql>select 字段1,字段N from 库.表 where 条件;

条件表达式

数值比较跟平时比较一样. '!='不等于的意思;between..and:在..和..之间

逻辑比较:

or,and,! 或,与,非

in .. ,not in .. 在..范围内,不在什么范围内

is null,is not null 字段值为空,字段值不为空

distinct 不显示重复的值

运算与平时一样..... + - * / %(取余)

模糊查询

通配符_匹配单个字符、%匹配0～N个字符

mysql>select * from 表名 where 字段名 like '通配字符'；

正则表达式

mysql>select * from 表名 where 字段名 regexp '正则表达式'；

嵌套查询（where字查询）

mysql>select 字段名列表 from 表A where 条件（select 字段名列表 from 表A/B where 条件）；

常用函数

-avg（）：平均值 -sum（）：求和 -min（）：最小值 -max（）：最大值 -count（）：记录的个数

查询结果排序

order by 字段名 [asc/desc] #升序,降序

group by 字段名 [having 条件表达式] #分组查询

limit n,m #显示n行开始,n后的m行

mysql>select 字段名列表 from 表名 order by 字段名 [asc/desc] limit 2; #升序/降序 限制前2行

mysql>select 字段名列表 from 表名 group by 字段名

表的复制及改名

mysql>create table 新表名 select * from 旧表

复制结构及改名

mysql>create table 新表名 select * from 旧表 where false;

mysql>alter table 旧表名 rename to 旧表名

多表查询:

mysql>select 字段1,字段N from 表A,表B where 条件;

连接查询

左连接查询

mysql>select 字段名列表 from 表A left join 表B on 条件表达式;

右连接查询

mysql>select 字段名列表 from 表A right join 表B on 条件表达式;

############################################################################################################

重置密码、修改权限

#mysqladmin -uuser -poldpass password 'newpass';

mysql> update mysql.user set authentication_string=PASSWORD('newpass')

->where user='root' and host='localhost';

重置mysql管理密码

vim /etc/my.cnf

skip_grant_tables=1

systemctl restart mysqld

#mysql -uroot -p

mysql>update mysql.user set authentication_string=PASSWORD('newpass')

->where user='root' and host='localhost';

注释掉/etc/my.cnf里面的skip_grant_tables=1

############################################################################################################

权限:权限:all代表所有权限

登陆位置: '%' 代表所有位置

'192.168.1.% or192.168.1.0/24' 匹配一个网段

'%.example.com' 匹配一个DNS区域

'192.168.1.1 or host'匹配单个主机

with grant option:让新添加的用户有授权的权限

mysql>grant 权限列表 on 库.表 to '用户名'@'登陆地址' identified by '密码' with grant option; #设置登陆权限:

mysql>grant 权限,权限(字段) on 库.表 to '用户名'@'登陆地址' identified by '密码'; #设置登陆和用户权限

mysql>select * from mysql.db where user='用户名'; #查看单库权限

mysql>select * from mysql.tables_priv where user='用户名'; #查看表权限

mysql>select * from mysql.colunms_priv where user='用户名'; #查看用户字段权限

mysql>select * from mysql.user where user='用户名'; #查看用户全库权限

mysql>show grants for '用户名'@'登陆地址'; #查看用户拥有的权限

mysql>show grants #用户查看自己拥有的权限

mysql>set password=password("新密码") #用户自己修改密码

mysql>set password for 用户名@"客户端地址"=password("新密码"); #管理员修改用户密码

mysql>revoke 权限列表 on 库.表 from '用户名'@'登陆地址'; #撤销用户权限

mysql>drop user '用户名'@'登陆地址' #删除用户

############################################################################################################

Mysql管理工具phpMyAdmin安装方法

yum包:httpd mariadb php php-mysql

rpm包:php-mbstring-5.3.3-26.el6.x86_64.rpm 安装时可添加-nodeps忽略依赖关系

修改配置文件/etc/httpd/conf/httpd.conf

ServerName localhost.localdomain

DirectoryIndex index.php index.html

phpMyAdmin套件下载地址:www.phpmyadmin.net,下载支持多语言的源码程序包phpMyAdmin-4.1.2-all-languages.zip。

解压缩,放在/var/www/html/下

cp config.sample.inc.php config.inc.php

修改config.inc.php

$cfg[‘blowfish_secret’]=’tarena’; //在单引号里随意添加字符,如果不修改这项，会报错【配置文件现在需要绝密的短语密码（blowfish_sec)

$cfg['Servers'][$i]['host'] = '192.168.4.10';

客户端即可访问.

truncate table 表名； 删除表数据

Linux下

均在控制台下操作。

导入数据库：

前提：数据库和数据表要存在（已经被创建）

(1)将数据表 test_user.sql 导入到test 数据库的test_user 表中

[root@test ~]# mysql -uroot -p test < /www/web/test/test_user.sql

(2) 将数据库 test.sql 导入到 test 数据库test 中

[root@test ~]# mysql -uroot -p test < /www/web/test/test.sql

(3)source命令不在控制台下，要进入mysql下操作

mysql> use test;

mysql>source /www/web/test/test.sql

导出数据库：

(1) 将数据库 test 导出到/www/web/test/test.sql

[root@test ~]# mysqldump -uroot -p test > /www/web/test/test.sql

回车后提示输入密码

(2) 将数据库 test 中的 user 数据表 导出到 /www/web/test/user.sql

[root@test ~]# mysqldump -uroot -p test user < /www/web/test/user.sql