db / 未分类 · 2016年8月14日

mysql study之常用操作-tag

有个哥说介绍一些mysql常用操作，于是有了此文
补充
RDBS， Relation data base system(关系型数据库系统)，数据以二维表形式存储，每行(row)代表同一行数据，为记录
每列表示记录中的某个属性，为字段也
不能用 kill -9 因为会自动重启 mysql service
root@debian:/# mysqladmin -pevan shutdown
root@debian:/h# ps -ef | grep mysqld
root 9950 5490 0 19:11 pts/0 00:00:00 grep mysqld
root@debian:# mysqladmin -pevan status
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’
Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!
rm -f /var/run/mysqld/mysqld.sock
root@debian:# mysqladmin -pevan status
Uptime: 19 Threads: 1 Questions: 2 Slow
p628 /etc/init.d/mysqld
mysql> show variables like ‘%buffer%’;
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+——————————+———–+
mysqladmin
#change the root password is 123456
mysqladmin -uroot -p password 123456
evan@debian:~/bakkali/software$ mysqladmin -uroot -p processlist
Enter password:
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 53 | root | localhost | | Sleep | 118 | | |
| 54 | root | localhost | | Query | 1 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+
26.5 p639
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
mysql> describe user;
+————————+———————————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————————+———————————–+——+—–+———+——-+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum(‘N’,’Y’) | NO | | N | |
| Insert_priv | enum(‘N’,’Y’) | NO | | N | |
| Update_priv | enum(‘N’,’Y’) | NO | | N | |
#创建表 CREATE TABLE IF NOT EXISTS employees ( # EMPLOYEE_ID varchar(10) not null, #// EMPLOYEE_ID 字段的类型为varchar(10),字段值不允许为空 FIRST_NAME varchar(10) not null, LAST_NAME varchar(10) not null, EMALL varchar(50) not null, HIRE_DATE date, #date 类型 JOB_ID int not null, SALARY int default 0, #//SALARY 字段默认值为0 MANAGER_ID int , DEPARTMENT_ID int not null, primary key (EMPLOYEE_ID) ) #// 主键为EMPLOYEE_ID #// 使用数据库引擎为MyISAM ENGINE=MyISAM DEFAULT CHARSET=utf8;
mysql> describe employees;
+—————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————+————-+——+—–+———+——-+
| EMPLOYEE_ID | varchar(10) | NO | PRI | NULL | |
| FIRST_NAME | varchar(10) | NO | | NULL | |
| LAST_NAME | varchar(10) | NO | | NULL | |
| EMALL | varchar(50) | NO | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| SALARY | int(11) | YES | | 0 | |
| MANAGER_ID | int(11) | YES | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+—————+————-+——+—–+———+——-+
9 rows in set (0.00 sec)
#######################
修改新密码
use mysql; update user set password=PASSWORD('evan') where user='root'; # ##最好要加这一行 flush privileges;
shell>mysqladmin -uroot -p password 　新密码
#　然后在提示符后输入老密码
增加新用户
格式:grant select on 数据库.* to 用户名@登录主机 identified by ‘密码’
举例:
例 1:增加一个用户 test1 密码为 abc,让他可以在任何主机上登录,并对所有数据库有
查询、插入、修改、删除的权限。首先用以 root 用户连入 MySQL,然后键入以下命令:
mysql> grant select,insert,update,delete on *.* to test1@localhost identified by 'abc';
Query OK, 0 rows affected
#或者
mysql> grant all privileges on *.* to test1@localhost identified by 'abc';
flush privileges;
删除用户
delete from user where user=’用户名’ and host=’localhost’;
delete from user where user='test2' and host='localhost'; flush privileges;
查看当前使用的数据库
select database();
表操作
备注:操作之前使用“use <数据库名>”应连接某个数据库。
建表
命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);
create table mm( id int(4) not null primary key auto_increment, name char(20) not null, sex int(4) not null default '0', degree double(16,2)) #ENGINE=InnoDB; ENGINE=MyISAM DEFAULT CHARSET=utf8;
获取表结构
命令: desc 表名，或者show columns from 表名
mysql> describe my; mysql> desc my; show columns from my;
删除表
drop table
插入数据
命令:insert into <表名> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )]
mysql> insert into my values(1,'tom',0,96); mysql> insert into my(name,sex,degree) values('evan',1,90);
查询表中的数据
mysql> select * from my;
+—-+——+—–+——–+
| id | name | sex | degree |
+—-+——+—–+——–+
| 1 | tom | 0 | 96.00 |
| 2 | evan | 1 | 90.00 |
+—-+——+—–+——–+
#前 2 行数据
select * from my limit 0,2; select * from my order by id limit 0,2;
删除表中数据
命令:delete from 表名 where 表达式
delete from my where id=2;
修改表中数据
命令:update 表名 set 字段=新值,… where 条件
update my set name=’eva’ where id=1;
mysql> select * from my order by id limit 0,2;
+—-+——+—–+——–+
| id | name | sex | degree |
+—-+——+—–+——–+
| 1 | eva | 0 | 96.00 |
+—-+——+—–+——–+
在表中增加字段
命令:alter table 表名 add 字段类型其他;
例如:在表 MyClass 中添加了一个字段 pass,类型为 int(4),默认值为 0
mysql> alter table my add pass int(4) default '0';
更改表名
命令:rename table 原表名 to 新表名;
rename table my to mm;
更新字段内容
命令:update 表名 set 字段名 = 新内容
update 表名 set 字段名 = replace(字段名, ‘旧内容’, ‘新内容’);
update my set name='nick' where name='eva'; update my set name=replace(name,'jkjk','evan');
数据库导入导出
将数据库 com 中的 my 导出到文件中
mysqldump -uroot -pevan com my > my.sql
数据库 mydb 的结构导出到 e:\MySQL\mydb_stru.sql 文件中
mysqldump -uroot -pevan com --add-drop-table >com.stru.sql
只导出数据不导出数据结构
mysqldump -u [数据库用户名] -p -t [要备份的数据库名称]>[备份文件的保存路径]
mysqldump -uroot -pevan -t com >com0.sql
#here
导出数据库中的Events
格式:mysqldump -u [数据库用户名] -p -E [数据库用户名]>[备份文件的保存路径]
导出数据库中的存储过程和函数
格式:mysqldump -u [数据库用户名] -p -R [数据库用户名]>[备份文件的保存路径]
从外部文件导入数据库中
1)使用“source”命令
2)使用 “<”　小于符号 *************************************** ##20160819 26.5.4 改变表结果 ALTER TABLE 数据表名更改1 [,更改2 …] #添加TELPHONE 字段 ALTER TABLE employees add TELPHONE char(20); #更改字段 change alter table employees change TELPHONE TEL char(20); ##更改字段类型 modify alter table employees modify TEL varchar(20); ##删除字段 drop alter table employees drop tel; #表改名 rename alter table employees rename to emp; 26.5.5 复制数据表 CREATE TABLE 新表名 LIKE 源表名 create table employees2 like employees; #只复制了表结构，要复制结构和数据的为 create table emp3 select * from emp; 26.5.6 删除数据表 DROP TABLE tablename; 26.6 数据管理 26.6.1查询数据 SELECT * | 字段列表 FROM 数据表 WHERE 条件; 1.所以数据 select * from emp; 2.某些字段的数据 mysql> select EMPLOYEE_ID, last_name,salary from emp;
+————-+————+——–+
| EMPLOYEE_ID | last_name | salary |
+————-+————+——–+
| kyct | kyct@g.com | 9 |
+————-+————+——–+
3,满足某些条件的数据
mysql> select EMPLOYEE_ID, last_name,salary from emp where salary < 10000; +————-+————+——–+ | EMPLOYEE_ID | last_name | salary | +————-+————+——–+ | kyct | kyct@g.com | 9 | +————-+————+——–+ 4.查询数据的总数 mysql> select count(*) from emp;
+———-+
| count(*) |
+———-+
| 1 |
+———-+
26.6.2 插入
INSERT INTO 表名(字段1, 字段2, …) VALUES (值1,值2，…);
INSERT INTO 表名(字段1, 字段2, …) SELECT 字段1, 字段2, …) FROM 源表;
mysql> desc emp;
+—————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————+————-+——+—–+———+——-+
| EMPLOYEE_ID | varchar(10) | NO | PRI | NULL | |
| FIRST_NAME | varchar(10) | NO | | NULL | |
| LAST_NAME | varchar(10) | NO | | NULL | |
| EMALL | varchar(50) | NO | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| SALARY | int(11) | YES | | 0 | |
| MANAGER_ID | int(11) | YES | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+—————+————-+——+—–+———+——-+
mysql> insert into emp values (‘kyct’,’kyct’,’kyct@g.com’,7,’2016-08-20′,3,9,8,9);
insert into emp (employee_id,LAST_NAME,job_id) values (10888,’jim’,5);
2. 插入其它有的数据
mysql> create table emp2 like emp;
Query OK, 0 rows affected (0.34 sec)
mysql> select * from emp2;
mysql> insert into emp2 select * from emp where employee_id=10888;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> select * from emp2;
+————-+————+———–+——-+———–+——–+——–+————+—————+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID |
+————-+————+———–+——-+———–+——–+——–+————+—————+
| 10888 | | jim | | NULL | 5 | 0 | NULL | 0 |
+————-+————+———–+——-+———–+——–+——–+————+—————+
26.6.3 更新数据
UPDATE 表名 SET 字段1 = 值1 [,字段2 = 值2 …] WHERE 查询条件;
mysql> select * from emp2;
+————-+————+———–+——-+———–+——–+——–+————+—————+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID |
+————-+————+———–+——-+———–+——–+——–+————+—————+
| 10888 | | jim | | NULL | 5 | 0 | NULL | 0 |
+————-+————+———–+——-+———–+——–+——–+————+—————+
mysql> update emp2 set emall=’jim@gamil.com’,salary=2000 where last_name=’jim’;
mysql> select * from emp2;
+————-+————+———–+—————+———–+——–+——–+————+—————+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID |
+————-+————+———–+—————+———–+——–+——–+————+—————+
| 10888 | | jim | jim@gamil.com | NULL | 5 | 2000 | NULL | 0 |
+————-+————+———–+—————+———–+——–+——–+————+—————+
26.6.4 删除数据
DELETE FROM 表名 WHERE 查询条件;
delete from emp where last_name=’jim’;
26.7 索引管理
26.7.1 创建索引
mysql> create table depar (
-> depa_id char(10) not null,
-> depa_name varchar(50) not null,
-> mana_id char(10),
-> index ind_depa01 (depa_id) //创建索引ind_depa01
-> );
#上面为创建表depar的同时，在 depa_id 字段创建一个名为 ind_depa01 的索引
如果表已存在 CREATE INDEX
CREATE [UNIQUE] INDEX 索引名 ON 表名 (字段1 [,字段2 …]);
create unique index ind_depa02 on dep(EMPLOYEE_ID);
#表emp 的 EMPLOYEE_ID 字段上，创建一个名为 ind_depa02 的唯一索引
create unique index ind_depa02 on emp(EMPLOYEE_ID);
26.7.2 删除索引
DROP INDEX 索引名 ON 表名
26.8 用户和权限管理
mysql> select host,user,password,select_priv from user;
+———–+——————+——————————————-+————-+
| host | user | password | select_priv |
+———–+——————+——————————————-+————-+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y |
| debian | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y |
| 127.0.0.1 | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y |
| ::1 | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y |
| localhost | debian-sys-maint | *E7BF66CEE2E00177E47E00EDE25DAFADF8AADF17 | Y |
+———–+——————+——————————————-+————-+
26.8.2 用户管理
1.添加
#add user 记得要刷新权限表
insert into user (host,user,password) values(‘%’,’test’,password(‘123456’));
flush privileges;
2.更新口令
mysql> update user set password=password(‘1234′) where user=’test’;
mysql> flush privileges;
delete from user where user=’test’;
26.8.3 用户授权
GRANT 权限[(字段)] ON 数据库名.表名 TO 用户名@域名或者ip 地址 [ IDENTIFIED BY ‘口令’ ] [ WITH GRANT OPTION
];
1.授权数据库中所有的对象的权限
hr库中所有的表的查询权限授给 evan
grant select on hr.* to evan;
2.授权数据库中个别对象的权限
#库hr中数据表emp 的查询和更新权限授给 test用户
mysql> grant select on hr.emp to test;
mysql> grant update on hr.emp to test;
3.控制访问的主机
grant all on dbname.tablename to user@’ip’;
4.
授test 用户 hr.emp 表的删除权限，并允许test 把该项权限授其它的用户
grant delete on hr.emp to test with grant option;
5 创建用户
grant select on evan.emp to sam identified by ‘123’;
mysql> select host,user,password from user;
+———–+——————+——————————————-+
| host | user | password |
+———–+——————+——————————————-+
| % | test | *A4B6157319038724E3560894F7F932C8886EBFCF |
| % | evan | |
| % | sam | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+———–+——————+——————————————-+
CREATE USER evan@localhost IDENTIFIED BY ‘1234’;
26.8.4 回收权限
REVOKE 权限 [( 字段)] ON dbname.tablename from user@ip
revoke select on hr.* from test;
mysql创建用户以及授权
http://www.linuxchina.net/?p=1344
mysql授权查看
http://www.linuxchina.net/?p=950
26.9 mysql 备份恢复 (5种 mysqldump,mysqlhocopy,sql,binlog,直接备份数据文件 )

26.9.1 mysqldump

1. 导出指定的表，不指定将导出库中的所有表
mysqldump [options] dbname [table]
2.导出一个多个指定数据库中的所有表
mysqldump [options] –database dbname1 [dbname2,…]
3.导出系统中所有数据库
mysqldump [options] –all-databases
常用的选项
–databases,-B 指定要导出的库
-default-character-set=charset_name 默认字符集
–ignore-table=dbname.tablename #忽略的表，不导出
–lock-all-tables, -X 导出前，锁定所以的数据表，保持系统数据的一致
–lock-tables, -l 锁定当前导出的数据表 #这个用处应该比较多
–log-error=filnename 保存错误和警告日志到文件
–no-create-db, -n 只导出数据，不导出CREATE TABLE 语句
–no-data, -d 只导出表结构，不导出数据
–routines, -R 导出存储过程和函数
1.备份表
mysqldump -uroot -p dbname table >table.sql
2.整个库
mysqldump -uroot -p –database dbname > db.sql
3.只导出表结构
mysqldump -uroot -p –no-data dbname table >table.sql
4.恢复数据
mysql -uroot -p dbanne < file.sql
或者 source
****************************************************************

26.9.2 mysqlhotcopy 感觉不好用
其实是使用lock tables cp scp 来完成备份的只能备份myisam存储引擎

1.备份
2.恢复直接是 cp -Rf src targer

26.9.3 使用SQL语句备份和恢复

BACKUP TABLE ，SELECT NITO OUTFILE

26.9.4 二进制日志(binlog)

恢复
mysqlbinlog -d games_s25 --start-datetime="2016-07-25 02:05:00" --stop-datetime="2016-07-25 04:40:00" "mysql-bin.000201" | mysql -uroot -ppassword

26.9.5 直接备份数据文件 ,flush tablename with read lock;#会比较好

MySQL基本操作
http://dufei.blog.51cto.com/382644/88646

您可能还喜欢...