用户管理与高级SQL语句（数据库管理与高可用）

1.表（Table ）

数据库中的表与我们日常生活中使用的表格类似，它也是由行（Row） 和列（Column）组成的。列由同类的信息组成，每列又称为一个字段，每列的标题称为字段名。行包括了若干列信息项。一行数据称为一个或一条记录，它表达有一定意义的信息组合。一个数据库表由一条或多条记录组成，没有记录的表称为空表。每个表中通常都有一个主关键字，用于惟一地确定一条记录。

2.索引（Index）

索引是根据指定的数据库表列建立起来的顺序。它提供了快速访问数据的途径，并且可监督表的数据，使其索引所指向的列中的数据不重复。

3.视图（View）

视图看上去同表似乎一模一样，具有一组命名的字段和数据项，但它其实是一个虚拟的表，在数据库中并不实际存。在视图是由查询数据库表产生的，它限制了用户能看到和修改的数据。由此可见，视图可以用来控制用户对数据的访问，并能简化数据的显示，即通过视图只显示那些需要的数据信息。

4.图表（Diagram）

图表其实就是数据库表之间的关系示意图。利用它可以编辑表与表之间的关系。

5.缺省值（Default）

缺省值是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据项赋予事先设定好的值。

6.规则（Rule）

规则是对数据库表中数据信息的限制。它限定的是表的列。

7.触发器（Trigger）

触发器是一个用户定义的SQL事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。

8.存储过程（Stored Procedure）

存储过程是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的SQL 程序。

9.用户（User）

所谓用户就是有权限访问数据库的人。

这个命令直接进入指定的库；直接进入mysql库；

mysql -uroot -ppwd123 mysql

创建用户：

并制定登录的主机和验证密码；

create user 'zhangsan'@'localhost' identified by 'pwd123';

更为标准的创建用户还是create；grant主要还是授权命令；

create只创建出来，grant创建并授权；

然后更新下配置：

flush privileges;

尝试登录，此时的‘zhangsan’可以登录但对所有库及表没有访问的权限；

然后给该用户授权：

权限视情况而定，以下权限给的和root权限是一样的；

.grant all on . to 'zhangsan'@'localhost' identified by 'pwd123';

然后更新配置：

注意：

第一个语句是为'zhangsan'设置了权限及登录主机；即验证密码；

第二个语句是为'zhangsan'更改了密码；权限及授权的库，表，不会变；只是简单的修改了密码；

第三个语句是为'zhangsan'更改了'登录主机信息'；会再创建一个名为'zhangsan'的用户，且远程登录密码为‘pwd123’

主要区分@后的内容，如果内容相同会直接修改，如果内容不同，会创建出新的用户，将其设置的权限等信息也赋予该用户；

如果删除表中的一个行的内容；用delete；

如果清空一个表用truncate；

如果删的对象，用drop；

如何删除一个用户；

drop user 'zhangsan'@'localhost';

此时就近一步的验证刚才的说法；如果要删除用户，要确定允许在哪里登录的用户，而此‘zhangsan’非彼‘zhangsan’

第二种删法，用删除文件的命令进行删除；

如果此时‘zhangsan’有多个允许登录的地方，就会全部删除掉；因为加了where语句；只要‘user’等于‘zhangsan’就会被删除掉；

如果想指定限定‘zhangsan’被删除；需要再指定条件；用‘and’符连接；

delete from user where user='zhangsan' and host='localhost';

如何给root设置密码：在有密码的情况下；

mysqladmin -uroot -ppwd123 password '123456'

会提示警告不安全，但是是可行的！

如果登录进去了想改密码：

可以使用update命令修改root行；密码列的值：

update mysql.user set authentication_string=password('pwd123') where user='root' and host='localhost'; 加两个限定条件；user=root && host=localhost

以下语句直接修改当前登陆者的密码：命令更为简洁；（专用于修改密码的语句）（且不调用加密函数，也会加密；）

set password=password('123456');

再使用set命令为别人改密码：

set password for 'zhangsan'@'localhost'=password('pwd123');

如果忘记密码了，而且数据库运行了一段时间，怎么处理？

使用mysql守护进程的方式免密进入修改密码；不用再调整配置文件中的参数了。

先关闭mysqld服务，然后使用守护进程的方式启动mysqld然后进行登录，就可以登录进去了，再修改密码后，重启mysqld服务即可；

授权；使用grant命令来给用户授权；

grant select,update,insert on *.* to 'lisi'@'localhost' identified by 'pwd123';

如何查询用户的权限；及撤销权限；

show grants for 'lisi'@'localhost';

撤销权限：

revoke select on . from 'lisi'@'localhost';

再次查看‘lisi’的权限；

此时就没了select权限，如果想撤销其他权限，在加‘，’号，继续写要撤销的权限；

因为mysql是基于c/s架构的，因此可以在客户端登录服务器端；

先在客户端mysql客户端的程序；mysql；无需启动，因为只是个应用程序，而不是个服务，所有无需启动；因此，管理的是服务，而不是程序；

但是服务器不允许登录；此时需要给服务器设置远程登录的权限；

注意远程登录的语句：mysql -ulisi -ppwd123 -h 192.168.10.101 -P 3306

指定用户 指定密码 指定登录的主机IP地址 指定服务的端口

允许所有远程用户（除localhost）使用账号为‘lisi’密码为‘123456’登录我的服务器；且授予该用户缩圈权限；及所有库下的所有表；

此时再用客户端登录即可；

使用高级语句前先创建环境；将以下语句粘贴到mysql中；创建两个表；

create database auth;

use auth

create table t1(id int(10), name char(20),level int(10));

insert into t1 value(10,'sagou',42);

insert into t1 value(8,'senoku',45);

insert into t1 value(15,'useless',47);

insert into t1 value(27,'guess',52);

insert into t1 value(199,'useless',48);

insert into t1 value(272,'Theshy',36);

insert into t1 value(298,'leslieF',40);

insert into t1 value(30,'shirley',58);

insert into t1 value(190,'zhangsan',48);

insert into t1 value(271,'lisi',52);

insert into t1 value(299,'wangwu',52);

insert into t1 value(31,'zhaoliu',58);

create table t2(id int(10), name char(20),level int(10));

insert into t2 value(10,'sagou',42);

insert into t2 value(8,'senoku',45);

insert into t2 value(15,'useless',47);

insert into t2 value(27,'guess',52);

insert into t2 value(199,'useless',48);

insert into t2 value(272,'Theshy',36);

insert into t2 value(298,'leslieF',40);

insert into t2 value(30,'shirley',58);

insert into t2 value(190,'zhangsan',48);

insert into t2 value(271,'lisi',52);

insert into t2 value(299,'wangwu',52);

insert into t2 value(31,'zhaoliu',58);

然后使用创建的auth库；

查询两个表；

使用select命令查询更为精准的列；

查询t1中的name列；

查询t1中的name列及level列；

中间用'，'隔开；

还可以使用select命令来附加条件；结果更加准确；

显示t1表中的name列，且'level'列的值大于等于50才显示；

以及根据它们的大小，来进行排列；或降序排列；

select name,level from t1 where level>=50 order by level asc;

升序：asc；默认就是升序，可以不写；

降序：

select name,level from t1 where level>=50 order by level desc;

不仅可以按照数字大小进行排序；还可以按照名字排序；即26个英文字母的顺序；

降序：

升序：

以及统计命令：

统计auth.t1；name列有多少行；

分组查询：还可以加附加条件：

level（等级）>=45的；

level>=45的，每个等级有多少人；

可以理解为；先查询level>=45的有几个人，然后再统计符合条件的各个等级分别有多少人；

分组查询排序：

以level排序；默认是升序；

以level排序，但降序；

限制查询结构；limit命令

查询前三行；

还可以对限制的内容进行排序；

只不过排序的指令要放在限制显示的指令的前面；

以level等级降序显示前五行；

以level等级升序显示前五行；

显示中间行：

此时显示的是从第三行往后的五行内容；

显示表中列的别名；方便用户查看；

顺序还可以变换；

单独查询一个字符串的信息；

模糊查询；不知道字符串的全称；可以用’%‘通配；

以‘li’开头的都会显示出来；

还可以通配前面的内容；以’si‘结尾的；

以及通配中间字符；只知道前面和后面的；

甚至还可以加两个’%‘；进行通配；

只记得前面有个'l'；中间有个'i'

%通配的是多个字符；而下划线——通配的是单个字符；

此下语句中存在两个下划线符号，进行通配；

进行子查询；嵌套；

select里面再嵌套(select)

显示id；且level>=45；即：前面查询的取值来自后面限制的范围内进行查询；

跨表添加：

将t1表中的内容放置到t2表里面一份；如果再使用该命令会再放置一份；

insert into t2 select * from t1 ;

实现连接查询：

将以下语句粘贴到数据库中；

CREATE TABLE `a_t1` (

`a_id` int(11) DEFAULT NULL,

`a_name` varchar(32) DEFAULT NULL,

`a_level` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b_t1` (

`b_id` int(11) DEFAULT NULL,

`b_name` varchar(32) DEFAULT NULL,

`b_level` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into a_t1(a_id, a_name, a_level) values(1, 'aaaa', 10);

insert into a_t1(a_id, a_name, a_level) values(2, 'bbbb', 20);

insert into a_t1(a_id, a_name, a_level) values(3, 'cccc', 30);

insert into a_t1(a_id, a_name, a_level) values(4, 'dddd', 40);

insert into b_t1(b_id, b_name, b_level) values(2, 'bbbb', 20);

insert into b_t1(b_id, b_name, b_level) values(3, 'cccc', 30);

insert into b_t1(b_id, b_name, b_level) values(5, 'eeee', 50);

insert into b_t1(b_id, b_name, b_level) values(6, 'ffff', 60);

然后查询两个表中的内容；

内连接：

查询a表中与b表id相同的部分显示出来；

select a_id,a_name,a_level from a_t1 inner join b_t1 on a_id=b_id;

左外连接：

select a_id,a_name,a_level from a_t1 left join b_t1 on a_id=b_id;

右外连接：

select * from a_t1 right join b_t1 on a_id=b_id;

补充：

存储过程：（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

可以直接调用存储过程的名字；实现多个sql语句的应用；

写存储过程中的语句要带‘；’结尾；先改变数据库的结束符号；避免冲突；

存储过程名为aaa；相关语句根据自身需求进行修改；最后以$$结尾；即可！

如何调用存储过程？？？

首先把结束符号再改为‘；’号

使用call命令调用存储过程；