mysql之数据库管理（一）

1、连接和断开服务器

1）连接：mysql [-h host] [-u user] -p[passwd]

2）退出：quit

3）直接登陆数据库

mysql database-name

2、使用数据库

1）切换数据库

USE 数据库名；

2）创建数据库

CREATE DATABASE dbname;

3、建表

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

 -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

4、装载数据

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

windows下

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

使用load命令时数据每行按列用tab健隔开

或者使用

INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

5、查询

1）查询版本

SELECT VERSION();

2）查询用户

数据库mysql下有user 表，该表保存了用户信息；

USE mysql；

SELECT count(*) FROM user;

显示当前用户

SELECT USER();

3）显示数据库

显示所有数据库

SHOW DATABASES;

显示当前使用的数据库

SELECT DATABASE();

4）显示表

SHOW TABLES;

5）显示表结构

DESCRIBE table_name;

6、利用脚本使用数据库

文件mysql_file内容：

#!/bin/sh

mysql -u root < file_test.sql

文件file_test.sql内容：

USE menagerie;

SHOW TABLES;

SELECT name FROM pet;

7、用户变量

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

8、用户管理

1)查看默认用户

mysql>USE mysql;

mysql> SELECT HOST,USER FROM user;

+-----------+------+

| HOST | USER |

+-----------+------+

| 127.0.0.1 | root |

| ::1 | root |

| localhost | |

| localhost | root |

| rhel1 | |

| rhel1 | root |

+-----------+------+

2)默认用户密码修改

方法1：其中第一个''为user第二个''为host第三个''为待设置的密码

shell>mysql -u root

mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'');

mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd'');

方法2：使用UPDATE修改

shell> mysql -u root

mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')

 -> WHERE User = '';

mysql> FLUSH PRIVILEGES;

方法3：使用mysqladmin修改

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

3）增加新用户

GRANT和REVOKE语法

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

with_option =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

priv_type可以被指定为以下任何一种：

权限	意义
ALL [PRIVILEGES]	设置除GRANT OPTION之外的所有简单权限
ALTER	允许使用ALTER TABLE
ALTER ROUTINE	更改或取消已存储的子程序
CREATE	允许使用CREATE TABLE
CREATE ROUTINE	创建已存储的子程序
CREATE TEMPORARY TABLES	允许使用CREATE TEMPORARY TABLE
CREATE USER	允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW	允许使用CREATE VIEW
DELETE	允许使用DELETE
DROP	允许使用DROP TABLE
EXECUTE	允许用户运行已存储的子程序
FILE	允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX	允许使用CREATE INDEX和DROP INDEX
INSERT	允许使用INSERT
LOCK TABLES	允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS	允许使用SHOW FULL PROCESSLIST
REFERENCES	未被实施
RELOAD	允许使用FLUSH
REPLICATION CLIENT	允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE	用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT	允许使用SELECT
SHOW DATABASES	SHOW DATABASES显示所有数据库
SHOW VIEW	允许使用SHOW CREATE VIEW
SHUTDOWN	允许使用mysqladmin shutdown
SUPER	允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE	允许使用UPDATE
USAGE	“无权限”的同义词
GRANT OPTION	允许授予权限

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'

 -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'

 -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

shell> mysql --user=root mysql

mysql> INSERT INTO user

 -> VALUES('localhost','monty',PASSWORD('some_pass'),

 -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user

 -> VALUES('%','monty',PASSWORD('some_pass'),

 -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user SET Host='localhost',User='admin',

 -> Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host,User,Password)

 -> VALUES('localhost','dummy','');

mysql> FLUSH PRIVILEGES;