一. MySQL密码的恢复方法之一

如果忘记了MySQL的root密码，可以用以下方法重新设置：

1. KILL掉系统里的MySQL进程；

killall -TERM mysqld

2. 用以下命令启动MySQL，以不检查权限的方式启动；

safe_mysqld --skip-grant-tables &

3. 然后用空密码方式使用root用户登录 MySQL；

mysql -u root

4. 修改root用户的密码；

mysql> update mysql.user set password=PASSWORD('新密码') where User='root';

mysql> flush privileges;

mysql> quit

重新启动MySQL，就可以使用新密码登录了。

CREATE DATABASE test2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

多表链接

g

创建以下两张表

select * from emp;

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

| id | name | sal | qq | d_id |

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

| 1 | zorro | 5000 | 123456 | 110 |

| 2 | tom | 3000 | 234567 | 120 |

| 3 | jerry | 4000 | 345678 | 100 |

| 4 | blues | 4500 | 456789 | 110 |

| 5 | shrek | 6000 | 156789 | 100 |

| 6 | seker | 8000 | 256789 | 120 |

| 7 | eric | 6000 | 4567890 | 120 |

| 8 | robin | 5000 | 765432 | 110 |

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

select * from dpt;

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

| d_id | d_name |

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

| 100 | hr |

| 110 | yw |

| 120 | kf |

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

等值联接或者内部联接 (没有条件出现笛卡尔集)(类似于{a,b}{1,2,3})

select id,name,sal,d_name from emp,dpt;

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

| id | name | sal | d_name |

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

| 1 | zorro | 5000 | hr |

| 1 | zorro | 5000 | yw |

| 1 | zorro | 5000 | kf |

| 2 | tom | 3000 | hr |

| 2 | tom | 3000 | yw |

| 2 | tom | 3000 | kf |

| 3 | jerry | 4000 | hr |

| 3 | jerry | 4000 | yw |

| 3 | jerry | 4000 | kf |

| 4 | blues | 4500 | hr |

| 4 | blues | 4500 | yw |

| 4 | blues | 4500 | kf |

| 5 | shrek | 6000 | hr |

| 5 | shrek | 6000 | yw |

| 5 | shrek | 6000 | kf |

| 6 | seker | 8000 | hr |

| 6 | seker | 8000 | yw |

| 6 | seker | 8000 | kf |

| 7 | eric | 6000 | hr |

| 7 | eric | 6000 | yw |

| 7 | eric | 6000 | kf |

| 8 | robin | 5000 | hr |

| 8 | robin | 5000 | yw |

| 8 | robin | 5000 | kf |

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

去掉笛卡尔集

select id,name,sal,d_name from emp,dpt where emp.d_id=dpt.d_id;

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

| id | name | sal | d_name |

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

| 1 | zorro | 5000 | yw |

| 2 | tom | 3000 | kf |

| 3 | jerry | 4000 | hr |

| 4 | blues | 4500 | yw |

| 5 | shrek | 6000 | hr |

| 6 | seker | 8000 | kf |

| 7 | eric | 6000 | kf |

| 8 | robin | 5000 | yw |

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

另外一种写法

select id,name,sal,d_name from emp inner join dpt on emp.d_id=dpt.d_id;

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

| id | name | sal | d_name |

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

| 1 | zorro | 5000 | yw |

| 2 | tom | 3000 | kf |

| 3 | jerry | 4000 | hr |

| 4 | blues | 4500 | yw |

| 5 | shrek | 6000 | hr |

| 6 | seker | 8000 | kf |

| 7 | eric | 6000 | kf |

| 8 | robin | 5000 | yw |

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

自连接

select * from emp;

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

| id | name | sal | qq | d_id |

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

| 1 | zorro | 5000 | 123456 | 110 |

| 2 | tom | 3000 | 234567 | 120 |

| 3 | jerry | 4000 | 345678 | 100 |

| 4 | blues | 4500 | 456789 | 110 |

| 5 | shrek | 6000 | 156789 | 100 |

| 6 | seker | 8000 | 256789 | 120 |

| 7 | eric | 6000 | 4567890 | 120 |

| 8 | robin | 5000 | 765432 | 110 |

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

查询每个部门最高薪水的员工

select emp.id,emp.name,a.msal,emp.d_id from emp,(select d_id,max(sal) as msal from emp group by d_id) a where emp.d_id=a.d_id and emp.sal=a.msal;

显示部门名称

select emp.id,emp.name,a.msal,emp.d_id,a.d_name from emp,(select emp.d_id,d_name,max(sal) as msal from emp,dpt where emp.d_id=dpt.d_id group by emp.d_id) a where emp.d_id=a.d_id and emp.sal=a.msal;

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

| id | name | msal | d_id | d_name |

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

| 1 | zorro | 5000 | 110 | yw |

| 5 | shrek | 6000 | 100 | hr |

| 6 | seker | 8000 | 120 | kf |

| 8 | robin | 5000 | 110 | yw |

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

外部联接

在dpt表里添加以行 130 cw 做一下实验

select * from dpt;

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

| d_id | d_name |

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

| 100 | hr |

| 110 | yw |

| 120 | kf |

| 130 | cw |

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

select id,name,sal,d_name from emp right outer join dpt on emp.d_id=dpt.d_id;

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

| id | name | sal | d_name |

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

| 3 | jerry | 4000 | hr |

| 5 | shrek | 6000 | hr |

| 1 | zorro | 5000 | yw |

| 4 | blues | 4500 | yw |

| 8 | robin | 5000 | yw |

| 2 | tom | 3000 | kf |

| 6 | seker | 8000 | kf |

| 7 | eric | 6000 | kf |

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

select id,name,sal,d_name from emp left outer join dpt on emp.d_id=dpt.d_id;

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

| id | name | sal | d_name |

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

| 3 | jerry | 4000 | hr |

| 5 | shrek | 6000 | hr |

| 1 | zorro | 5000 | yw |

| 4 | blues | 4500 | yw |

| 8 | robin | 5000 | yw |

| 2 | tom | 3000 | kf |

| 6 | seker | 8000 | kf |

| 7 | eric | 6000 | kf |

| NULL | NULL | NULL | cw |

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

---

搜索存储引擎

mysql视图

单表视图

create view new_emp as select * from emp;

create view new_emp1 as select uid,name,money from emp;

修改视图=修改源表

多表视图

create table tt1(id int,name char(10),did int);

create table tt2(did int,dname char(10));

多表查询

select tt1.id,tt1.name,tt2.did,tt2.dname from tt1,tt2 where tt1.did=tt2.did;

create view kk as select tt1.id,tt1.name,tt2.did,tt2.dname from tt1,tt2 where tt1.did=tt2.did;

create view kkk as select tt1.name,tt2.dname from tt1,tt2 where tt1.did=tt2.did;

视图对引擎没有要求，可以修改删除，不可以添加，只有表结构*.frm文件。视图相当于存储那条 创建视图的语句

存储引擎是什么?

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据

MYISAM引擎：有 .frm表结构，.MYD数据，*.MYI索引数据

MRG_MYISAM

MEMORY（内存引擎）只有表结构 .frm，数据存储在内存中，重启会丢失。用于把数据存于内存中，快速读写运算。

创建MYISAM引擎表：不支持外键、行级锁、事物，如：update一行数据时，整个表都会锁定。

InnoDB引擎：支持事物、外键、行级锁，粒度更小，修改一行时，就锁定一行。

事物：把语句分到一组，一组语句同时执行或者同时不执行，然后提交或者事物回滚commit||rollback。

mysql> create table t4(id int,name char(10)) engine=myisam;

创建MEMORY引擎表

mysql> create table t3(id int,name char(10)) engine=memory;

创建MRG_MYISAM引擎表

create table t10(id int,name char(10)) engine=myisam;

create table t20(id int,name char(10)) engine=myisam;

create table t30(id int,name char(10)) union=(t10,t20)engine=mrg_myisam;

create table t40(id int,name char(10)) union=(t10,t20) INSERT_METHOD=first|no|last engine=mrg_myisam;

MyISAM

MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名 为.MYD （MYData）。索引文件的扩展名是.MYI （MYIndex）。 MyISAM文件的格式是平台无关的，这意味着你可以将数据和索引文件从一个intel服务器上拷贝到一台PowerPC或者Sun SPARC上，而不会出任何问题。

主要区别：

MyISAM是非事务安全型的，而InnoDB是事务安全型的。

MyISAM锁的粒度是表级，而InnoDB支持行级锁定。

MyISAM支持全文类型索引，而InnoDB不支持全文索引。

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表(alter table tablename type=innodb)。

mysql的配置文件:/etc/my.cnf /usr/my.cnf /usr/local/mysql/my.cnf

独占表空间和共享表空间

innodb_file_per_table=1 独立

innodb_file_per_table=0 共享

1.外键索引

外键myisam引擎不支持只能用innodb引擎

create table dpmnt(id int not null,name char(10) not null,primary key(id)) type = INNODB;

desc dpmnt;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | NO | PRI | NULL | |

| name | char(10) | NO | | NULL | |

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

建立外键

create table emp (id int not null, name char(10) not null,fk_dpmnt int not null ,primary key(id),index (fk_dpmnt),foreign key (fk_dpmnt) references dpmnt(id)) type=innodb;

desc emp;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | NO | PRI | NULL | |

| name | char(10) | NO | | NULL | |

| fk_dpmnt | int(11) | NO | MUL | NULL | |

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

insert into dpmnt values(1,hr);

insert into dpmnt values(2,'yw');

insert into emp values(10,'zhb',3);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (fk_dpmnt) REFERENCES dpmnt (id))

fk_dpmnt字段的数据必须得是dpmnt表里有的不然报错...

即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误。这是删除外键的方法。

alter table emp drop foreign key emp_ibfk_1;

删除外键

添加外键

alter table emp add CONSTRAINT fk_1 foreign key(did) references dpt(did);

create table dpt(did int primary key,dname char(10))

create table emp(id int,name char(10),did int,foreign key (did) references dpt(did) );

insert into emp values(1,'robin',10);

insert into emp values(2,'zorro',30);

alter table emp drop foreign key emp_ibfk_1;

alter table emp add foreign key (did) references dpt(did);

alter table emp add CONSTRAINT emp_dpt_fk foreign key (did) references dpt(did);

级联删除,级联更新

mysql> create table emp(id int,name char(10),did int,foreign key (did) references dpt(did) ON DELETE CASCADE on update cascade);

2.表级锁和行级锁

行级锁：对正在被修改的行进行锁定，其它的用户可以访问被锁定的行以外的行。表级锁：锁定整个表，限其他用户对表的访问

3.事物的支持

事务处理是一种机制,用来管理成批执行的sql语句,以保证数据库不包含不完整的操作结果,他们或者为整体执行完成,或者完全不执行(如果没有错误发生整组语句提交到数据库,如果发生错误,则进行回退,以保证数据的安全)

事务 transaction 指定一组sql语句

回退 rollback 撤销指定的sql语句(只能回退insert delete update语句)

提交 commit 提交未存储的sql语句

保留点 savepoint 事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)

create table t11(id int,name char(10)) engine=innodb;

start transaction;

delete from t11;

select * from t11;

Empty set (0.00 sec)

rollback;

select * from t11;

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

| id | name |

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

| 1 | zorro |

| 2 | zorro |

| 2 | zorro |

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

提交

start transaction;

insert into t11 values(3,'tom');

select * from t11;

commit;

保留点

start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t11 values(5,'haha');

Query OK, 1 row affected (0.01 sec)

mysql> savepoint insert1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;

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

| id | name |

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

| 1 | tom |

| 2 | jerry |

| 3 | zorro |

| 4 | seker |

| 5 | haha |

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

5 rows in set (0.00 sec)

mysql> delete from t11 where id=4;

Query OK, 1 row affected (0.00 sec)

mysql> savepoint delete1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;

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

| id | name |

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

| 1 | tom |

| 2 | jerry |

| 3 | zorro |

| 5 | haha |

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

4 rows in set (0.00 sec)

mysql> delete from t11 where id=1;

Query OK, 1 row affected (0.00 sec)

mysql> savepoint delete2;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;

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

| id | name |

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

| 2 | jerry |

| 3 | zorro |

| 5 | haha |

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

3 rows in set (0.01 sec)

mysql> rollback to delete1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;

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

| id | name |

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

| 1 | tom |

| 2 | jerry |

| 3 | zorro |

| 5 | haha |

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

4 rows in set (0.00 sec)

create table t5 (id int,name char(10)) union=(t3,t4) INSERT_METHOD=first engine=mrg_myisam;

create table dpt(did int primary key,dname char(10)) engine=innodb;

create table emp(id int primary key,name char(30),did int,foreign key(did) references dpt(did)) engine=innodb;

alter table emp drop foreign key emp_ibfk_1;

删除外键

添加外键

alter table emp add CONSTRAINT fk_1 foreign key(did) references dpt(did);

---

用户权限

创建用户并设置密码

create user zorro identified by '123';

create user zorro@localhost identified by '123';

create user zorro@'%' identified by '123';

查询

select user from mysql.user;

修改用户名

rename user zorro to robin;

select user from mysql.user;

删除

drop user robin;

修改用户密码

set password for 'zorro'@'%'= password('123'); password()函数加密

set password = password('123');修改当前用户

root密码丢失

重置root口令

shell> mysqld_safe --skip-grant-tables --skip-networking &

mysql>update mysql.user set password=password('123') where host='localhost' and user='root'

查询用户权限

show grants for zorro G

*************************** 1. row ***************************

Grants for zorro@%: GRANT USAGE ON . TO 'zorro'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

USAGE表示没有任何权限

连接测试

mysql -u zorro -p123

ERROR 1045 (28000): Access denied for user 'zorro'@'localhost' (using password: YES)

失败

权限

MySQL存取控制包含2个阶段：

阶段1：服务器检查是否允许你连接。

阶段2：假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。例如，如果你从数据库表中选择(select)行或从数据库删除表，服务器确定你对表有SELECT权限或对数据库有DROP权限。

授权grant

命令格式

grant 权限 on 库.表 to 用户@主机 [密码]

//远程所有权

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '111111' WITH GRANT OPTION;

grant select on hr.* to zorro@'localhost';

show grants for zorro G

*************************** 1. row ***************************

Grants for zorro@%: GRANT USAGE ON . TO 'zorro'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

*************************** 2. row ***************************

Grants for zorro@%: GRANT SELECT ON hr.* TO 'zorro'@'%'

移除权限revoke

命令格式

revoke 权限 on 库.表 from 用户@主机;

revoke select on hr.* from zorro'localhost';

远程主机授权

grant all on hr.* to zorro@'192.168.1.129' identified by '123';

grant all on hr.* to zorro@'%' identified by '123';

grant和revoke可在几个层次上控制访问权限

整个服务器 grant all 和 revoke all

整个数据库 on databases.*

grant select,insert on hr.* to robin@'localhost' identified by '123';

特定的表 on database.table;

grant select,insert on hr.tt to tom@'localhost' identified by '123';

其他方法：

mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy',password());

mysql> FLUSH PRIVILEGES;

练习:

1.创建帐号zorro 允许从本机和任意位置登录

create user zorro@'%';

create user zorro@'%' identified by '123';

create user zorro@'localhost' identified by '123';

2.修改zorro名字为king

rename user zorro@'%' to king@'%';

rename user zorro@'localhost' to king@'localhost';

3.设置king用户的密码位123

set password for king@'localhost'=password('123');

set password for king@'%'=password('123');

4.以king帐号登录到mysql数据库 设置密码位abc

set password=password('123');

重置root密码

1.停止mysql(pkill mysql)

2./usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &

3.update mysql.user set password=password('123') where user='root' and host='localhost';

4.停止mysql (pkill mysql)

5./usr/local/mysql/bin/mysqld_safe --user=mysql &

6.正常登录

跳过授权

vim /etc/my.cnf

[mysqld]

skip-grant-tables

用户信息mysql.user存储所有用户信息,权限信息分布不同的表中

abc1 abc2 abc3 abc4

grant all on . to abc1@localhost identified by '123';

abc1 权限保存在 mysql.user

grant all on db.* to abc2@localhost identified by '123';

abc2 权限保存在 mysql.db

grant all on db.test20 to abc3@localhost identified by '123';

abc3 权限保存在 mysql.tables_priv

grant select(name) on db.test20 to abc4@localhost identified by '123';

abc4 权限保存在 mysql.columns_priv

create user robin; 添加帐号

set password for robin=password('123'); 设置密码

create user zorro identified by '123'; 创建帐号同时设置密码

rename user zorro to newzorro; 修改帐号名字

drop user newzorro; 删除帐号

set password=password('123'); 设置当前帐号密码

root密码丢失

实验环境

删除数据目录

重新

管理密码为空(直接登录)

重置root密码

/usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &

--skip-grant-tables 跳过授权表不进行验证.

mysql> update mysql.user set password=password('123') where user='root' and host='localhost'; 更新密码

pkill mysql

service mysqldd restart

授权

1.是否能连接数据库 localhost %

2.验证帐号密码

1.能否连接数据库

第一部分 本地来源

第二部分 远程来源

create user zorro@'%' identified by '123';

select user,password,host from mysql.user;

create user zorro@'localhost' identified by '123';

2,授权

grant all on db.* to zorro@'localhost'; db库所有表具有所有权限

grant select,insert on db.t5 to robin@'localhost' identified by '123'; 授权同时创建帐号

回收权限revoke all on db.* from zorro@'localhost';

. mysql.user

db.* mysql.db

db.t5 mysql.tables_priv

db.t5(id) mysql.columns_priv

grant all on . to abc1@localhost identified by '123';

abc1 权限保存在 mysql.user

grant all on db.* to abc2@localhost identified by '123';

abc2 权限保存在 mysql.db

grant all on db.test20 to abc3@localhost identified by '123';

abc3 权限保存在 mysql.tables_priv

grant select(name) on db.test20 to abc4@localhost identified by '123';

abc4 权限保存在 mysql.columns_priv

笔记补充：

数据迁移：1，跨平台 linux到 Windows --- myisam引擎 2 跨不同数据库

InnoDB引擎不支持 MRG_MYISAM， *.ibd有两种表空间：共享表空间和独立表空间

共享表空间：把整个数据库中的所有innodb引擎的表对应数据都放到一个文件中 ibddata1。独立磁盘，日志分离。但是每个表不可以独立备份（物理备份），不能空间压缩（有些空间被释放但是不可以再利用）。

独立表空间：有独立空间，每个表独立的表空间文件。独立备份，空间收缩

vim /etc/my.cnf

[mysqld] 给进程mysql

innodb-file-per-table = 0 共享 1独立（）默认

以前的不变