centos 数据库管理

centos 数据库管理
1，用户管理 2，数据库引擎管理 3，授权管理 4，数据库管理 5，数据表管理 6，视图管理 7，function函数 8，存储过程 9，触发器
一，用户管理
1.    创建用户
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
使用 help create user 查看创建用户帮助
默认权限为：USAGE也就是只有进入数据库的权限
2.    删除用户
DROP USER 'USERNAME'@'HOST‘ 
3.    用户重命名
RENAME USER old_user_name TO new_user_name; 
4.    更改密码
#方法1 SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password'); 
#方法2 UPDATE mysql.user SET password=PASSWORD('password') WHERE clause; 
#此方法需要执行下面指令才能生效： FLUSH PRIVILEGES;
5.    忘记管理员密码的解决办法：
 1.  启动mysqld进程时，为其使用如下选项（或者放在my.cnf的mysqld配置文件中）： 
--skip-grant-tables 
--skip-networking 
2.  使用UPDATE命令修改管理员密码 
3. 关闭mysqld进程，移除上述两个选项，重启mysqld
二，数据库引擎管理
1.    MyISAM引擎特点
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制
高并发
崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎
2.    MyISAM引擎文件 
tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件
3.    InnoDB引擎特点 
行级锁 
支持事务，适合处理大量短期事务 
读写阻塞与事务隔离级别相关 
可缓存数据和索引 
支持聚簇索引 
崩溃恢复性更好 
支持MVCC高并发 
从MySQL5.5后支持全文索引 
从MySQL5.5.5开始为默认的数据库引擎
4.    InnoDB数据库文件 
所有InnoDB表的数据和索引放置于同一个表空间中 
表空间文件：datadir定义的目录下 
数据文件：ibddata1, ibddata2, ... 
每个表单独使用一个表空间存储表的数据和索引 
两类文件放在对应每个数据库独立目录中 
数据文件(存储数据和索引)：tb_name.ibd 
表格式定义：tb_name.frm 
启用：innodb_file_per_table=ON (MariaDB 5.5以后版是默认值) 
5.    管理存储引擎
查看支持的存储引擎
show engines;  
 查看当前默认的存储引擎
show variables like  '%storage_engine%';  
设置默认的存储引擎
vi /etc/my.conf  
[mysqld]
default_storage_engine= InnoDB
查询库中所有表各自使用的引擎
show table status from db_name;   
查看库中指定表的存储引擎 
show table status like ' tb_name '; 
show create table tb_name; 
设置表的存储引擎
CREATE TABLE tb_name(... ) ENGINE=InnoDB; 
ALTER TABLE tb_name ENGINE=InnoDB;
三，授权管理
1.    数据库授权
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION]; 
WITH GRANT OPTION 是表示，用户能否把自己的权限，复制给别的用户
GRANT 是能够连授权和创建用户一起做的，但是新版本已经不建议这么做了，建议先创建用户，后授权
2.    取消授权
REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;
3.    查看指定用户获得的授权
SHOW GRANTS FOR 'user'@'host'; 
4.    注意：
MariaDB服务进程启动时会读取mysql库中所有授权表至内存
 (1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权 表，使之生效 
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES; 
四，数据库管理
1.    查询数据库
show create database db1;
2.    修改数据库
ALTER DATABASE DB_NAME character set utf8; 
3.    删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
4.    查看数据库列表
SHOW DATABASES;
5.    查看数据表结构
desc students
五，数据表管理
1.    创建表三种写法
通过查询现存表创建；新表会被直接插入查询而来的数据
create table aa select * from aa
通过复制现存的表的表结构创建，但不复制数据 
create table bb like 
正常创建表
CREATE TABLE employee (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name)); 
创建表帮助
help create table 
2.    查询表
show tables ;显示表的列表
show tables from mysql 显示mysql 的列表
desc 表名，查看表结构
SHOW CREATE TABLE tbl_name  查看表创建命令
show table status like ‘students’\G 查看表状态  竖方向显示
六，视图管理
view  视图，相当于把查询结果保存在一个虚拟表里
1.    创建视图
 create view v_student  as select * from students；
2.    查看视图
SHOW CREATE VIEW view_name #只能看视图定义 
SHOW CREATE TABLE view_name # 可以查看表和视图 
3.    删除视图
drop view v_student;
4.    注意：视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表 限制
七，function函数
1.    函数：使用select 来调用
2.    系统函数参考： https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html 
3.    自定义函数：user-defined function UDF，保存在mysql.proc表中 
4.    创建一个有参数的函数
因为有可能一行写不完，分号回车，就提交了，所以暂时定义// 为完整输入提交，最后别忘了定义回来
DELIMITER // 
CREATE FUNCTION 
deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20) 
BEGIN 
DELETE FROM students WHERE stuid = id; 
RETURN (SELECT COUNT(*) FROM students); 
END// 
DELIMITER ; 
5.    创建一个无参数的函数
MariaDB [hellodb]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
MariaDB [hellodb]> 
在MySQL中创建函数时出现这种错误的解决方法：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
set global log_bin_trust_function_creators=TRUE;
 
MariaDB [hellodb]> set global log_bin_trust_function_creators=TRUE;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [hellodb]> create function xiapi() returns varchar(20) return "hah";
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [hellodb]> select xiapi();
+---------+
| xiapi() |
+---------+
| hah     |
+---------+
1 row in set (0.00 sec)
6.    函数定义变量
局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一 行定义
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值] 
DECLARE a, b SMALLINT UNSIGNED; 
SET a = x, b = y;  使用set 为变量赋值
也可以使用select赋值
DECLARE x int; 
SELECT COUNT(*) FROM tdb_name INTO x; 
八，procedure 存储过程
1.    存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接 调用,省去了编译的过程
提高了运行速度 
同时降低网络数据传输量 
存储过程与自定义函数的区别 
存储过程实现的过程要复杂一些,而函数的针对性较强 
存储过程可以有多个返回值,而自定义函数只有一个返回值 
存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用 
2.    创建存储过程
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) routime_body proc_parameter : [IN|OUT|INOUT] parameter_name type 
说明：其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name 表示参数名称；type表示参数的类型
3.    创建无参存储过程 
delimiter // 
CREATE PROCEDURE showTime() 
BEGIN 
SELECT now(); 
END// 
delimiter ; 
CALL showTime; 
4.    创建含参存储过程：只有一个IN参数 
delimiter // 
CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED) 
BEGIN SELECT * FROM students WHERE stuid = id; 
END// 
delimiter ; 
call selectById(2); 
5.    创建含参存储过程:包含IN参数和OUT参数 
delimiter //
CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN 
DELETE FROM students WHERE stuid >= id; 
SELECT row_count() into num; 
END// delimiter ; 
call deleteById(2,@Line); 
SELECT @Line; 
#说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行 数值的用户变量@Line,select @Line;输出被影响行数
注意这个输出的变量，在函数结束后，是不会取消掉的。
6.    查询存储过程列表
SHOW PROCEDURE STATUS; 
7.    查询存储过程定义
SHOW CREATE PROCEDURE sp_name
8.    调用存储过程
CALL sp_name ([ proc_parameter [,proc_parameter ...]]) 
说明:当无参时,可以省略"()",当有参数时,不可省略"()”
9.    删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
九，trigger 触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
1.    创建触发器
trigger_name：触发器的名称 
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发 
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件 
tbl_name：该触发器作用在表名
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body 
2.    创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少 
CREATE TABLE student_info ( 
stu_id INT(11) NOT NULL AUTO_INCREMENT , 
stu_name VARCHAR(255) DEFAULT NULL, 
PRIMARY KEY (stu_id) ); 
CREATE TABLE student_count ( student_count INT(11) DEFAULT 0 ); 
INSERT INTO student_count VALUES(0); 
 
CREATE TRIGGER trigger_student_count_insert 
AFTER INSERT 
ON student_info FOR EACH ROW 
UPDATE student_count SET student_count=student_count+1; 
 
CREATE TRIGGER trigger_student_count_delete 
AFTER DELETE ON 
student_info FOR EACH ROW 
UPDATE student_count SET student_count=student_count-1; 
3.    查看触发器
MariaDB [hellodb]> show triggers\G;
*************************** 1. row ***************************
             Trigger: tri_insert
               Event: INSERT
               Table: xiapi
           Statement: update youyou set count=count+1
              Timing: AFTER
             Created: 2019-11-23 11:18:11.34
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: tri_delete
               Event: DELETE
               Table: xiapi
           Statement: update youyou set count=count-1
              Timing: AFTER
             Created: 2019-11-23 11:26:15.49
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
 
ERROR: No query specified
 
MariaDB [hellodb]> 
4.    删除触发器
drop trigger trigger_name;