一、 用户管理操作
1:创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
example:
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'dog2'@'localhost' IDENTIFIED BY '';
PS:username - 你将创建的用户名,
host - 指定该用户在哪个主机上可以登陆,此处的"localhost",是指该用户只能在本地登录,不能在另外一台机器上远程登录,如果想远程登录的话,将"localhost"改为"%",表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;
password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。
2:用户授权:
GRANT privileges ON databasename.tablename TO 'username'@'host'
PS:
privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;
databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*.
例子:
GRANT SELECT, INSERT ON mq.* TO 'dog'@'localhost';
3:创建用户并授权
mysql> grant all privileges on mq.* to test@localhost identified by '1234';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges;Query OK, 0 rows affected (0.01 sec
PS:必须执行:
**flush privileges; **
**否则登录时提示:ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES ) **
4:设置并更改密码
命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
例子: SET PASSWORD FOR 'dog2'@'localhost' = PASSWORD("dog");
ps:
假如你在给用户'dog'@'localhost''授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'dog'@'localhost',
则在使用REVOKE SELECT ON . FROM 'dog'@'localhost';命令并不能撤销该用户对test数据库中user表的SELECT 操作.
相反,如果授权使用的是GRANT SELECT ON . TO 'dog'@'localhost';则REVOKE SELECT ON test.user FROM 'dog'@'localhost';
命令也不能撤销该用户对test数据库中user表的Select 权限.
具体信息可以用命令SHOW GRANTS FOR 'dog'@'localhost'; 查看.
5、撤销用户权限
- 命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
- 说明: privilege, databasename, tablename - 同授权部分.
- 例子: REVOKE SELECT ON mq.* FROM 'dog2'@'localhost';
6、删除用户
命令: DROP USER 'username'@'host';
7、查看用户权限:
mysql> show grants for dog@localhost;
|+---------------------------------------------+|
Grants for dog@localhost
|+---------------------------------------------+|
GRANT USAGE ON *.* TO 'dog'@'localhost' ||
GRANT INSERT ON `mq`.* TO 'dog'@'localhost'
|+--------------------------------------------
-+2 rows in set (0.00 sec)
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'dog2'@'localhost' IDENTIFIED BY '';
8.查询所有存在的用户:
select user from mysql.user;
二:库操作:
1、列出所有存在的数据库:
SHOW DATABASES;
2、创建数据库:
CREATE DATABASE test;
3、删除数据库:
DROP DATABASE test;
4、使用数据库:
USE test;
5:修改数据库名称:
RENAME DATABASE db_name TO new_db_name (有数据丢失风险)
6:拷贝数据库:
#mysqldump db1 -u root -ppassword --add-drop-table | mysql newdb -u root -ppassword
远程拷贝:
#mysqldump db1 -uroot -ppassword --add-drop-table | mysql -h 192.168.1.22 newdb -uroo
7:查看当前所用数据库:
select database();
三:表操作:
1、创建表:
CREATE TABLE `students` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `class_id` bigint(20) NOT NULL, `name` varchar(100) NOT NULL, `gender` varchar(1) NOT NULL, `score` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
CREATE TABLE `cc` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `status` varchar(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
2、查询存在的表:
mysql> SHOW TABLES; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | class_id | bigint(20) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | gender | varchar(1) | NO | | NULL | | | score | int(11) | NO | | NULL | | +----------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
3:查看表结构:
mysql> DESC students;
4:查看创建表时候使用的命令:
mysql> SHOW CREATE TABLE students; +----------+-------------------------------------------------------+ | students | CREATE TABLE `students` ( | | | `id` bigint(20) NOT NULL AUTO_INCREMENT, | | | `class_id` bigint(20) NOT NULL, | | | `name` varchar(100) NOT NULL, | | | `gender` varchar(1) NOT NULL, | | | `score` int(11) NOT NULL, | | | PRIMARY KEY (`id`) | | | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
5:删除表:
mysql> DROP TABLE students;
6:修改表->插入新的列:
给students表新增一列birth,使用:
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
7:修改表->更改列名称:
修改birth列,例如把列名改为birthday,类型改为VARCHAR(20):
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
8:修改表-》删除列:
ALTER TABLE students DROP COLUMN birthday;
9:插入数据:
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就先删除原记录,再插入新记录。此时,可以使用REPLACE语句,这样就不必先查询,再决定是否先删除再插入:
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在,REPLACE语句将插入新记录,否则,当前id=1的记录将被删除,然后再插入新记录。
10:插入或者更新数据:
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句:
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
若id=1的记录不存在,INSERT语句将插入新记录,否则,当前id=1的记录将被更新,更新的字段由UPDATE指定。
11:插入或者忽略
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就啥事也不干直接忽略,此时,可以使用INSERT IGNORE INTO ...语句:
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在,INSERT语句将插入新记录,否则,不执行任何操作。
12:快照(复制表)
如果想要对一个表进行快照,即复制一份当前表的数据到一个新表,可以结合CREATE TABLE和SELECT:
-- 对class_id=1的记录进行快照,并存储为新表students_of_class1: CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
13:强制使用指定索引
在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。例如:
> SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
指定索引的前提是索引idx_class_id必须存在。
四:数据查询:
1:基本查询
SELECT * FROM <表名>
2:条件查询:
SELECT * FROM students WHERE score >= 80;
3:条件查询:。。。。。
五:登录
mysql -h ip dbname -u user -p
example:
mysql -h 192.168.11.11 Med -u whd -p