mysql常用命令

来源：互联网发布：淘宝美工助理是干嘛的编辑：程序博客网时间：2024/04/29 07:05

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

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

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

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

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 'zhangsh'@'localhost';(*.* 覆盖了数据库中的所有表)

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

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

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

（关于用户权限，这篇文章讲的不错：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);

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;

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

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

自定义变量的使用：

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');

0 0