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;
centos 数据库管理
最新推荐文章于 2023-12-05 17:33:44 发布