mysql常用命令

mysql -h hostname -u user -p password  连接mysql

SELECT User, Host FROM mysql.user; 从user表中选出user 和host

 SET PASSWORD FOR 'user'@'localhost' =PASSWORD ('abc')

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 创建用户可以从host登录，名称为username,密码为password;

SHOW GRANTS FOR 'username'@'%'; 显示用户的在各个DB上的权限

GRANT SELECT ON db1.book TO 'kerry'@'%' IDENTIFIED BY 'beck123'; 为kerry 开通在db1中，book表上从任何主机登录都有浏览权限，密码为beck123

GRANT ALL ON *.* TO 'kerry'@'localhost' IDENTIFIED BY 'beck123'; 赋予keryy从localhost登录时在所有表上的所有权限

REVOKE ALL ON *.* FROM 'user'@'localhost';(*.* 覆盖了数据库中的所有表)

DELETE FROM mysql.user where User='kerry';删除kerry

DROP USER 'username'@'host'; 删除’username‘@’host‘ 这条记录

FLUSH PRIVILEGES;重新读取权限表，必须拥有RELOAD权限

SELECT * FROM mysql.`db` where user='user4'\G; //查看某个用户所有的权限

（关于用户权限，这篇文章讲的不错：http://www.jb51.net/article/31850.htm）

SELECT user();查看当前用户

CREATE DATABASE bookstore; (数据库与表名对大小写是否敏感取决于操作系统)

USE bookstore;

CREATE TABLE books (book_id INT, title ARCHAR(50) , author VARCHAR(50));

DESCRIBE books;

ALTER TABLE books

CHANGE COLUMN book_id book_id INT AUTO_INCREMENT PRIMARY KEY,

CHANGE COLUMN author author_id INT,

ADD COLUMN description TEXT,

ADD COLUMN genre ENUM ('novel', 'poetry','drama'),

ADD COLUMN isbn VARCHAR (20),

DROP COLUMN tmp; //删除列

alter table 表名 modify 字段名 字段类型 after 字段 //改变列的位置

alter table 表名 modify 字段名 字段类型 first //移动到第一列

 

SHOW DATABASES;

SHOW TABLES;

SHOW TABLES FROM mysql;

SHOW GRANT [FOR 'user'@'host'];

INSERT INTO authors (author_last, author_first) VALUES ('Greene','Graham')  [,('Greene2','Graham2')];

ALTER TABLE books CHANGE COLUMN isbn isbn varchar(20) UNIQUE;

ALTER TABLE questions ADD [CONSTRAINT] FOREIGN KEY (creator) REFERENCES users (id);

REPLACE INTO authors (author_last, author_first) VALUES ('Greene','Graham'); --如果key存在，则先删除原来的key， 插入新的记录

INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;--从一个表中选取记录插入到另一个表中

select auto_increment from information_schema.tables where table_schema='db_name' and table_name='table_name';//查看某个表的auto_increment的当前值;

alter table db_name.table_name auto_increment=1000;//将auto_increment的值设为max(表中当前最大索引+1,设定值)

 alter table StockStatusEntry add primary key (code, strategy); //创建联合主键

SET FOREIGN_KEY_CHECKS = 0;

delete from table_name where id>10;

SET FOREIGN_KEY_CHECKS = 1;// handle the error of "Cannot delete or update a parent row: a foreign key constraint fails"

set unique_checks=0; 
set unique_checks=1; // 类似的，关闭和打开唯一性检查

 

mysqldump -u 用户名 -p 数据库名 > 导出的文件名 

mysql>source 导入的文件名; //数据库的备份与恢复

 

show variables like 'secure_file_priv'; //导出路径权限，可以通过将其设置为空来关掉这个选项。 也可以在/etc/mysql/my.conf 的[mysqld]下加入一行secure_file_priv='';

select * from mysql.user where user = 'user' \G; //查看用户权限，这个区别于select * from mysql.db，会更全面一些，因为有写权限不是db 层面的，而是全局的，使用mysql.db 就不能看到这些权限，例如file process等等。

grant file on *.* to 'user'@'%';//对于全局权限，必须使用*.*，不能指定某个数据库或表。当然也可以操作mysql.user 表，但是更危险。

SELECT * FROM table_name INTO OUTFILE '/var/lib/mysql-files/city-state-mapping' FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY "" LINES TERMINATED BY '\n'; //导出为csv文件

load data local infile 'e://input1.csv' into table test1 fields terminated by ','lines terminated by '/n' (first_name,last_name,email); //从csv文件导入

 

1. show character set;// show charset;

2. show collation;

3.配置编码：http://blog.csdn.net/chenxingzhen001/article/details/7567812 修改/etc/MySQL/my.cnf.

4. alter database school/ table Students charset utf8 collate utf8_general_ci;

5. show variables like 'char%';

6.  alter table table_name add index code_index (code(10)) using btree;

7. show indexes from table_name;

8. show create table table_name; --显示创建表的信息，包括使用的引擎，编码方式。

9. show table status from db_name;--显示数据库表中信息

10. show columns from table_name like 'name%';<=> describe table_name;

 

11.show variables like 'auto%'; set autocommit=0;--为了支持事务，需要将autocommit设置为0;

12. begin;

13. commit;

14. rollback;

15. handler table_name open as table_handler;

16. handler table_handler read first | next [limit 10];

 

17. select * from table_name limit 3,4;

18. select code from stock group by code having avg(open)>100;

19. show [count(*)] errors;--show warnings;

20. select year, month,count(*) from calendar goup by year, month with rollup;--带有汇总的分组

21. mysql> update Students s join Parents p on s.parent=p.number set s.address='Tangwangzhen' ,p.comment='搬家' where s.name='wangwu';--多表update

 

 

22. select * from Students procedure analyse();

23. select sql_cache|sql_no_cache count(*) from Students;

24. check table Students quick|fast|..;

25. show processlist;

26. show open tables from db;--查看表的锁的使用

27. show status like 'Query%';

28. select get_lock('mylock',10);--试图获取'mylock'锁，超时时间是10秒，这个锁是跨session的。

29. select is_free_lock('mylock');

30. select is_used_lock('mylock');

31. select release_lock('mylock');

 

32. select case job when 'farmer' then '农民' else job end as ocu from Parents;

32. select case job when 'farmer'>job then '农民' else job end as ocu from Parents;

33.  select day(@time), month(adddate(@time, interval 3 month)) ;

 

34.  delimiter | create procedure tt (in field varchar(255)) begin select field from Parents; end | delimiter ; call tt('name');

35. udf. udf的定义是跨session的，服务器重启udf也不会消失。

function和procedure的区别，procedure是可以call的，但是function只能select;

create function hello (identity int, f varchar(20)) returns varchar(20) reads sql data return (select name from student where id = identity);

drop function hello; 

show create function hello;

delimiter |

create procedure recreate(num int) begin declare idx int default 1; truncate table inc; while idx <= num do insert into inc values(idx, now()); set idx = idx + 1; end while; end;|

delimiter ;

create event my_event2 on schedule every 1 minute starts '2020-01-11 11:36:00' do call recreate(30) ;

delimiter $$
create function check_gender(val int) returns varchar(10) NO SQL BEGIN if val = 1 then return 'male' ; elseif val = 2 then return 'female' ; else return 'unknown' ; end if ;  end $$

set @gender = ''

create procedure check_gender(val int, out gender varchar(20)) BEGIN  if val = 1 then set gender='male' ; elseif val = 2 then set gender = 'female' ; else set gender = 'unknown' ; end if ; end $$

call check_gender(2, @gender) $$
select @gender $$

show function status where Db!='sys';--查看所有自定义函数；

show procedure status where Db!='sys';--查看所有自定义函数；

select * from information_schema.routines; --查看所有Procedure/Function

show events where Db!='sys'; 

show triggers; 

36. 触发器：每个表只允许对一个触发器时间和触发器事件进行组合。一个表不能有两个Before insert触发器，但是可以有一个Before insert和after insert触发器，只有增加、删除和修改支持触发器操作。但是指定Insert，会生效于insert ,load data 和replace，指定delete，会生效于delete和replace。

37. Event

 

自定义变量的使用：

SET @potter = 

(SELECT author_id FROM authors WHERE author_last=‘Rowling’);

DELETE FROM books WHERE author_id =@potter

 

 

忘记密码：

1.对于老的版本：/usr/bin/mysqld_safe --skip-grant-tables，可以不用输入密码就能进入cli;对于5.7.4以上的版本，应该在/etc/my.cnf 的[mysqld]下面加一行skip-grant-tables，然后service mysqld restart然后mysql就能直接进入cli;

2. mysql> update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';

    mysql> flush privileges;

    mysql> quit;

3.还原修改，mysql -uroot -p重新登录，并设置密码：

    mysql>SET PASSWORD = PASSWORD('newpasswd');