MySQL 常用命令与常用语句

Published on 2015 - 05 - 08

安装依赖包

yum install gcc gcc-c++ make automake cmake conf autoconf ncurses-devel libtool libtool-devel libxml2-devel libtool libtool-ltdl-devel autoconf automake bison zlib zlib-devel

初始化MySQL

mysql_secure_installation

登录

mysql -u $yourusername -p -h $IP

创建数据库

create database $hr;

查看数据库列表

show databases;

创建数据表

create [temporary] table [if not exists] tbl_name (create_definition,...) [table_options] [opartition_options]
常用数据类型如下
TINYINT(n)      8位整数型
SMALLINT(n)     16位整数型
MEDIUMINT(n)    32位整数型
INT(n)          32位整数型
BIGINT(n)       64位整数型
FLOAT(n,d)      单精度浮点数
DOUBLE(n,d)     双精度浮点数
DATE            日期格式
TIME            时间格式
char(n)         固定长度字串
varchar(n)      非定长字串
BIT             二进制数据
BLOB            非定长二进制数据
常用属性如下
NOT NULL        要求数据库为非空值
AUTO_INCREMENT  用户插入新的数据后对应整数数据列自动加1
PRIMARY KEY     创建主索引列
KEY             普通索引列
DEFAULT CARSET  设置默认字符集
ENGINE          设置默认数据库存储引擎
创建数据表示例:
mysql> create table employees (
     > employee_id INT NOT NULL AUTO_INCREMENT,
     > first_name char(20) NOT NULL,
     > last_name char(20) NOT NULL,
     > e_mail varchar(50),
     > telephone INT(15),
     > departement varchar(20)
     > hire_date date,
     > PRIMARY KEY (employee_id)
     > KEY (department))
     > ENGINE=innodb DEFAULT CHARSET=UTF8;
mysql> CREATE TABLE test (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
mysql> CREATE TABLE new_user SELECT * FROM mysql.user;
mysql> show tables;
查看表的结构
DESCRIBE $databasename;
修改数据库属性
ALTER DATABASE $hr DEFAULT CHARACTER SET=UTF8;
ALTER DATABASE $hr DEFAULT COLLATE=utf8_general_ci;
修改数据表结构 如:添加删除的列,创建删除的索引,修改数据类型等
例:创建数据表test1后,通过alter语句修改数据表的相关信息
    CREATE TABLE $test1 (id INT, name CHAR(20);
    ALTER TABLE $test1 RENAME $test2;
    ALTER TABLE $test2 ADD date TIMESTAMP;
    ALTER TABLE $test2 ADD note CHAR(50);
    ALTER TABLE $test2 ADD  INDEX (date);
    ALTER TABLE $test2 MODIFY id TINYINT NOT NULL, CHANGE name $first_name CHAR(20);
    ALTER TABLE $test2 ADD PRIMARY KEY (id);
    ALTER TABLE $test2 DROP COLUMN note;
删除数据表
drop table $hr;
删除特定表中的索引
drop index 'primary' $on user;
drop index 'index' on $tbl_name
修改一个或多个数据表名
mysql> RENAME TABLE test1 TO temp,
     > test3 TO test1
     > temp TO test3
删除数据库
drop database $hr;

数据库操作语言

INSERT 插入一行新的数据
mysql> INSERT INTO employees (employee_id,first_name,last_name,
     > e_mail,telephone,department,hire_date)
     > VALUES
     > (001,"eric","william","test@gmail.com",01012341234,"tech",20110112);
mysql> SELECT * FROM hr.employees; 
UPDATE 更新表中现有的内容
例:
    更新employees,讲hire_date的值增加1,last_name 的值为hope
UPDATE employees SET hire_date=hire_date + 1;
UPDATE hr.employees SET last_name="hope" WHERE employee_id=2;
加载/导入 文件到数据表
echo "003 ellis jim example@gmail.com 01065103488 sale 20130102" > 123
LOAD DATA INFILE '/tmp/123' INTO TABLE hr.employees;
echo "004,berry,john,exapmle@gmail.com 01065103488 admin 20130102" > 1234
LOAD DATA INFILE 'tmp/1234' INTO TABLE hr.employees FIELDS TERMINATED BY '.';
DELETE 删除数据,并返回删除记录
例:
    mysql> USE hr;
    mysql> DELETE FROM employees WHERE employee_id5;
    mysql> DELETE FROM employees WHERE first_name= "eric";
    msyql> DELETE FROM a1,a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;
SELECT查询
SELECT * FROM employees;    #查看数据表中所有的数据
SELECT employee_id,first_name,department FROM employees; #查看三个列中的数据
SELECT AVG(score) FROM test;  #查看test表中AVG列的平均值
SELECT COUNT(employee_id) FROM employees;   #统计employee_id列的数据个数
SELECT DISTINCT(e_mail) FROM employees;     #去除重复的记录
SELECT first_name FROM employees WHERE last_name="william"; #查询lastname为william的所有记录,并且显示出相应的firstname
SELECT * FROM employees ORDER BY hire_date DESC;    #查询所有记录,基于hire_date列降序排列(DESC为降序AES为升序)
SELECT * FROM employees LIMIT 2;    #限制仅显示数据记录中的前两行记录;
创建用户并赋予权限
GRANT ALL ON hr.* to 'tomcat'@'localhost' IDENTIFIED BY 'pass';
    //创建tomcat账户,仅可以从本机连接到MySQL数据库,密码为pass,拥有“所有权限”
GRANT SELECT ON hr.* to 'tomcat'@'192.168.0.88' IDENTIFIED BY 'pass';
    //创建tomcat账户,仅可从192.168.0.88访问MySQL服务器,密码为pass,仅有查询权限;
GRANT RELOAD,PROCESS ON *.* to 'admin'#'localhost';
    //创建admin账户,赋予对所有数据库的管理权限,该帐号不需要密码,即可从本机(仅本机)连接MySQL;
GRANT ALL ON hr.* TO 'JACOB'@'%' IDENTIFIED BY 'pass';
    //创建jacob账户,赋予对hr数据库中的所有权限,密码为pass,可以从任何地方连接到MySQL服务器;
SHOW GRANTS FOR jerry@172.16.0.253
    //查看账户权限信息
刷新配置
FLUSH PRIVILEGES;
直接修改权限数据表 mysql.user
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd') WHERE User = 'root';
    //修改root的权限
通过mysqladmin设置密码
mysqladmin -u root -p password 'centos'
通过SET PASSWORD设置密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('$newpwd')
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('$newpwd') 远程主机

MySQL备份与还原

使用mysqldump
mysqldump --all-databases > bak.sql
    //备份所有Mysql数据库
mysqldump --databases db1 db2 db3 > bak.sql
    //备份指定数据库(当备份一个数据库的时候 --databases可以省略 但是略有区别,不带--data还原的时候需要手动创建数据库)
mysql < bak.sql
    //还原所有数据库
mysql db4 < bak.sql
    //还原指定数据库

用户类

insert into mysql.user(Host,User,Password)values('localhost','rich',password('123456')); 
//在本机创建用户rich,密码123456
用户授权只有备份权限
grant replication slave  on *.* to 'osyunweidbbak'@'192.168.1.1' identified by '123456' with grant option; 
#授权用户osyunweidbbak只能从192.168.1.1这个IP访问主服务器上面的数据库,并且只具有数据库备份的权限