mysql常用命令
young / / / 阅读量

登录

登录MySQL,如果连接远程数据库,需要用-h指定hosnme

mysql -h host -u'user' -p'password'

用户

通过GRANT命令创建用户

当数据库存在用户的时候GRANT会对用户进行授权,但当数据库不存在该用户的时候,就会创建相应的用户并进行授权。(给主机为192.168.10.1的用户john分配可对所有数据库的所有表进行所有操作的权限,并设定口令为123。)

grant all privileges on *.* to john@192.168.10.1 identified by '123';

注意:ALL并不包括GRANT OPTION权限(也不包括proxy权限),如果需要使本用户可以给其它用户授权,需在上面语句中加上 WITH GRANT OPTION

查看权限

mysql>show grants for你的用户;
mysql>show grants for root@'localhost';
mysql>show create database dbname;  这个可以看到创建数据库时用到的一些参数。
mysql>show create table tickets;    可以看到创建表时用到的一些参数

撤销权限

mysql>revoke all on *.* from 'root'@'%';

##修改密码:

# 修改指定用户密码 5.7后 password 字段2变更为 authentication_string
update mysql.user set password=password('123') where user='root' and host='localhost';

删除用户

mysql>drop user 'root'@'localhost';

数据库和表操作

列出所有数据库

mysql>show database;

切换数据库

mysql>use '数据库名';

列出所有表

mysql>show tables;

查看数据表结构

mysql>desc 表名;
mysql>show columns from 表名;
mysql>describe 表名;
mysql>show create table 表名;

修改表字段类型和长度

mysql> alter table 表名 modify column 字段名 类型;
例如
数据库中address表 city字段是varchar(30)
修改类型可以用(谨慎修改类型,可能会导致原有数据出错)
mysql> alter table address modify column city char(30);
修改长度可以用(修改长度,要保证不短于已有数据,以保证原有数据不出错)
mysql> alter table address modify column city varchar(50);

删除数据库和数据表

mysql>drop database 数据库名;
mysql>drop table 数据表名;

创建数据库并制定编码字符集

mysql>CREATE DATABASE dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- CHARACTER SET:指定数据库采用的字符集,utf8不能写成utf-8
-- COLLATE:指定数据库字符集的排序规则,utf8的默认排序规则为utf8_general_ci(通过show character set查看)

查看数据库编码

mysql>show variables like '%char%';

修改数据库编码

mysql>alter database dbtest CHARACTER SET GBK COLLATE gbk_chinese_ci;

设置默认字符集

set character_set_server = utf8;
-- 服务器的默认字符集。使用这个语句可以修改成功,但重启服务后会失效。根本的办法是修改配置MYSQL文件MY.INI,character_set_server=utf8,配置到mysqld字段下。

复制表

复制 A 数据库 A1 表到 B数据库中

use B;
DROP TABLE IF EXISTS B.A1 ;
CREATE TABLE A1 LIKE A.A1;
INSERT A1 SELECT * FROM A.A1;

修改表名

RENAME TABLE old TO new;

旧表( old)必须存在,而新表( new)一定不存在。

创建索引

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
  • <索引名>:指定索引名。一个表可以创建多个索引,但每个索引在该表中的名称是唯一的。
  • <表名>:指定要创建索引的表名。
  • <列名>:指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
  • <长度>:可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小,节省索引列所占的空间。在某些情况下,只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节(MyISAM 和 InnoDB 表的最大上限为 1000 个字节),如果索引列的长度超过了这个上限,就只能用列的前缀进行索引。另外,BLOB 或 TEXT 类型的列也必须使用前缀索引。
  • ASC|DESC:可选项。ASC指定索引按照升序来排列,DESC指定索引按照降序来排列,默认为ASC

查看索引

SHOW INDEX FROM <表名> [ FROM <数据库名>]
  • <表名>:要显示索引的表。
  • <数据库名>:要显示的表所在的数据库。

查询是否锁表

show OPEN TABLES ;

查询进程

show processlist ;

查询到相对应的进程,然后杀死进程

kill id;

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

解锁表

UNLOCK TABLES;

数据操作

“查”——查询数据

查询数值型数据:

SELECT * FROM tb_name WHERE sum > 100; 查询谓词:>,=,,!=,!>,!,=

查询字符串

SELECT * FROM tb_stu WHERE sname = '小刘'
SELECT * FROM tb_stu WHERE sname like '刘%'
SELECT * FROM tb_stu WHERE sname like '%程序员'
SELECT * FROM tb_stu WHERE sname like '%PHP%'

查询日期型数据

SELECT * FROM tb_stu WHERE date = '2011-04-08'
注:不同数据库对日期型数据存在差异:

查询逻辑型数据

SELECT * FROM tb_name WHERE type = 'T'
SELECT * FROM tb_name WHERE type = 'F'
逻辑运算符:and or not

查询非空数据

SELECT * FROM tb_name WHERE address '' order by addtime desc
注:相当于PHP中的!=

利用变量查询数值型数据

SELECT * FROM tb_name WHERE id = '$_POST[text]'
注:利用变量查询数据时,传入SQL的变量不必用引号括起来,因为PHP中的字符串与数值型数据进行连接时,程序会自动将数值型数据转变成字符串,然后与要连接的字符串进行连接

利用变量查询字符串数据(%%模糊匹配)

SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%'
完全匹配的方法"%%"表示可以出现在任何位置

查询前n条记录(limit)

SELECT * FROM tb_name LIMIT 0,$N;
limit语句与其他语句,如order by等语句联合使用,会使用SQL语句千变万化,使程序非常灵活

查询后n条记录

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n

查询从指定位置开始的n条记录

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n
注意:数据的id是从0开始的

查询统计结果中的前n条记录

SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num

查询指定时间段的数据(BEETWEEN AND)

SELECT 要查找的字段 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 终止值
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

按月查询统计数据

SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;
注:SQL语言中提供了如下函数,利用这些函数可以很方便地实现按年、月、日进行查询
year(data):返回data表达式中的公元年分所对应的数值
month(data):返回data表达式中的月分所对应的数值
day(data):返回data表达式中的日期所对应的数值

查询大于指定条件的记录

SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;

查询结果不显示重复记录

SELECT DISTINCT 字段名 FROM 表名 WHERE 查询条件
注:SQL语句中的DISTINCT必须与WHERE子句联合使用,否则输出的信息不会有变化 ,且字段不能用*代替 
NOT与谓词进行组合条件的查询
(1)NOT BERWEEN … AND … 对介于起始值和终止值间的数据时行查询 可改成 终止值
(2)IS NOT NULL 对非空值进行查询
(3)IS NULL 对空值进行查询
(4)NOT IN 该式根据使用的关键字是包含在列表内还是排除在列表外,指定表达式的搜索,搜索表达式可以是常量或列名,而列名可以是一组常量,但更多情况下是子查询

显示数据表中重复的记录和记录条数

SELECT name,age,count(*) ,age FROM tb_stu WHERE age = '19' group by date

对数据进行降序/升序查询

SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 DESC 降序
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 ASC 升序
注:对字段进行排序时若不指定排序方式,则默认为ASC升序

对数据进行多条件查询

SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段1 ASC 字段2 DESC …
注意:对查询信息进行多条件排序是为了共同限制记录的输出,一般情况下,由于不是单一条件限制,所以在输出效果上有一些差别。

对统计结果进行排序

函数SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可实现对字段的求和,函数中为ALL时为所有该字段所有记录求和,若为DISTINCT则为该字段所有不重复记录的字段求和
如:
SELECT name,SUM(price) AS sumprice FROM tb_price GROUP BY name
SELECT * FROM tb_name ORDER BY mount DESC,price ASC

单列数据分组统计

SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC
注:当分组语句group by排序语句order by同时出现在SQL语句中时,要将分组语句书写在排序语句的前面,否则会出现错误

多列数据分组统计

多列数据分组统计与单列数据分组统计类似
SELECT *,SUM(字段1*字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC
SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC
注:group by语句后面一般为不是聚合函数的数列,即不是要分组的列

多表分组统计

SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;

将查询结果导出

SELECT 字段名 FROM tb_stu WHERE 条件 into outfile '文件名';

增”——添加数据

为表中所有字段添加数据

INSERT 语句中指定所有字段名

语法:INSERT INTO 表名(字段名1,字段名2,…)
         VALUES(值1,值2,…);
举例:INSERT INTO student(id,name,grade)
     VALUES(1,'zhangshan',98);

INSERT语句中不指定字段名

若不指定字段名,则添加的值的顺序应和字段在表中的顺序完全一致。
语法:INSERT INTO 表名 VALUES(值11,值2,…);
举例:INSERT INTO student
     VALUES (2,'lisi',62);

为表的指定字段添加数据

为指定字段添加数据,即只向部分字段添加值,而其他字段的值为表定义时的默认值。
语法:INSERT INTO 表名(字段1,字段2,…)
     VALUES(值1,值2,…)
举例:INSERT INTO student(id,name)
     VALUES(3,'wangwu');

INSERT语句的其他写法

语法:INSERT INTO 表名
    SET 字段名1=值1[,字段名2=值2,…]
举例:INSERT INTO student
    SET id=4,name='zhaoliu',grade=72;

同时添加多条数据

语法:INSERT INTO 表名[(字段名1,字段名2,…)]
   VALUES (值1,值2,…),(值1,值2,…),
   …
   (值1,值2,…)
举例:INSERT INTO student VALUES
    (5,‘lilei’,99),
    (6,'hanmeimei',87),
    (8,'poly',76);

“删”——删除数据

语法:DELETE FROM 表名 [WHERE 条件表达式

删除表

drop tables 表名;

删除数据库

drop database 数据库名;

删除部分数据

即删除指定的部分数据,需要使用WHERE子句来指定删除记录的条件。
举例:删除student表中的id值为7的记录
命令:DELETE  FROM student WHERE id=7;

删除全部数据

若 DELETE 语句中没有使用WHERE语句,则会将表中所有记录都删除。
语法:DELETE FROM 表名
举例:删除student表中的所有记录
命令:DELETE FROM student;

“改”——更新数据

更新数据指对表中现存的数据进行修改。
语法:UPDATE 表名 SET 字段名1=值1,[ 字段名2=值2,…] [ WHERE 条件表达式 ]

UPDATE 更新部分数据

指更新指定表中的指定记录,使用WHERE 子句来指定。
举例:将student表中id值为1=记录,将其name字段的值改为‘caocao’,grade字段的值改为50。
命令:UPDATE student SET name=‘caocao’,grade=50 WHERE id=1;

UPDATE替换指定字段中指定字符串:

UPDATE 表名 SET 指定字段 = replace(指定字段, '要替换的字符串', '想要的字符串') WHERE 条件;

UPDATE 更新全部数据

 在UPDATE 语句中若不使用WHERE 子句,则会将表中所有记录的指定字段都进行更新。
 举例:更新student表中全部记录,将grade字段都更新为80
命令:UPDATE student SET grade=80;

将数据库test导出到mysql.test文件,后者是一个文本文件

[root@localhost]# mysqldump -u root -p123456 --databases dbname > mysql.dbname
就是把数据库dbname导出到文件mysql.dbname中

导入数据:

在数据库中执行sql文件

use dbname;
source db.sql;
支付宝捐赠
请使用支付宝扫一扫进行捐赠
微信捐赠
请使用微信扫一扫进行赞赏
有 0 篇文章