数据库: SHOW DATABASES;
创建数据库: CREATE DATABASE IF NOT EXISTS people;
切换数据库: USE people;
删除数据库: DROP DATABASE IF EXISTS people;
查看当前数据库库信息: SHOW CREATE DATABASE people;
修改数据库的选项信息: ALTER DATABASE people;
2.数据表操作
显示数据库里所有数据表的信息: SHOW TABLE STATUS FROM people;
显示全部数据表: SHOW TABLES;
单张表：show tables from df
清空数据表: TRUNCATE df;
表检测: CHECK TABLE df;
表优化: OPTIMIZE TABLE df;
表修复: REPAIR TABLE df;
表分析: ANALYZE TABLE df;
分析表 键状态是否正确: ANALYZE TABLE orders;
检查表是否存在错误: check TABLE orders,orderitems QUICK;# QUICK只进行快速扫描
优化表OPTIMIZE TABLE,消除删除和更新造成的磁盘碎片,从而减少空间的浪费:OPTIMIZE TABLE orders;
查询表结构: DESC df;DESCRIBE df; EXPLAIN df;SHOW COLUMNS FROM df;
复制表: CREATE TABLE de LIKE df; SELECT * INTO IN 'hw' FROM df;
修改表名: RENAME TABLE de TO people.dh;（可将表移动到另一个数据库）
修改表字段: ALTER TABLE df ADD/DROP/CHANGE
拼接字段:SELECT CONCAT(us,'(',tim,')') FROM df ORDER BY us ASC;result=>us(tim)
添加主键约束：alter TABLE 表名 ADD CONSTRAINT 主键 （形如：PK_表名） PRIMARY KEY 表名(主键字段);
添加外键约束：alter TABLE 从表 ADD CONSTRAINT 外键（形如：FK_从表_主表） FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段);
删除主键约束：alter TABLE 表名 DROP PRIMARY KEY;
删除外键约束：alter TABLE 表名 DROP FOREIGN KEY 外键（区分大小写）;
-- 添加外键约束
CREATE TABLE stu(sid INT PRIMARY KEY,NAME VARCHAR(50) NOT NULL);
-- 添加外键约束方式一
CREATE TABLE score1(score DOUBLE,sid INT,CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid));
-- 添加外键约束方式二(若表已存在，可用这种)
CREATE TABLE score1(score DOUBLE,sid INT);
ALTER TABLE score1 ADD CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES stu(sid)
三、索引
CREATE UNIQUE INDEX qw ON df(us); #创建不重复索引
ALTER TABLE df ADD UNIQUE INDEX wq(id); #添加索引
SHOW INDEX FROM df;#检索索引
DROP INDEX qw ON people.df; #删除索引
ALTER TABLE df DROP INDEX wq; #删除索引
四、视图
CREATE VIEW shitu AS SELECT us FROM df; 创建视图
ALTER VIEW shitu AS SELECT us FROM df WHERE us='gf'; 修改视图
SELECT * FROM shitu; 查看视图结果
DROP VIEW IF EXISTS shitu;删除视图
创建、打开、关闭游标 # 定义名为ordernumbers的游标，检索所有订单
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- open the cursor 打开游标
open ordernumbers;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
-- 使用游标数据
# 例1：检索 当前行 的order_num列，对数据不做实际处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE o INT;
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- open the cursor 打开游标
open ordernumbers;
-- get order number 获得订单号
FETCH ordernumbers INTO o;
/*fetch检索 当前行 的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中。
对检索出的数据不做任何处理。*/
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 例2：循环检索数据，从第一行到最后一行，对数据不做实际处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1;
-- SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
-- open the cursor 打开游标
open ordernumbers;
-- loop through all rows 遍历所有行
REPEAT
-- get order number 获得订单号
FETCH ordernumbers INTO o;
-- FETCH在REPEAT内，因此它反复执行直到done为真
-- end of loop
UNTIL done END REPEAT;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 例3：循环检索数据，从第一行到最后一行，对取出的数据进行某种实际的处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- declare the cursor 声明游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- create a table to store the results 新建表以保存数据
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT,total DECIMAL(8,2));
-- open the cursor 打开游标
OPEN ordernumbers;
-- loop through all rows 遍历所有行
REPEAT
-- get order number 获取订单号
FETCH ordernumbers INTO o;
-- get the total for this order 计算订单金额
CALL ordertotal(o,1,t); # 参见23章代码，已创建可使用
-- insert order and total into ordertotals 将订单号、金额插入表ordertotals内
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
-- end of loop
UNTIL done END REPEAT;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 调用存储过程 precessorders()
CALL processorders();
# 输出结果
SELECT * FROM ordertotals;
七、触发器
１、MySQL触发器的创建语法：
CREATE[DEFINER = { 'user' | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body
2、MySQL创建语法中的关键词解释：
字段 含义 可能的值
DEFINER= 可选参数，指定创建者， DEFINER='root@%'
默认为当前登录用户（CURRENT_USER）；
该触发器将以此参数指定的用户执行， DEFINER=CURRENT_USER
所以需要考虑权限问题；
trigger_name 触发器名称，最好由表名+触发事件关键词+触发时间关键词组成；
trigger_time 触发时间，在某个事件之前还是之后；BEFORE、AFTER
INSERT：插入操作触发器，INSERT、LOAD DATA、REPLACE时触发；
UPDATE：更新操作触发器，UPDATE操作时触发；
trigger_event 触发事件，如插入时触发、删除时触发；DELETE：删除操作触发器，DELETE、REPLACE操作时触发；
INSERT、UPDATE、DELETE
table_name 触发操作时间的表名；
可选参数，如果定义了多个具有相同触发事件和触法时间的触发器时（
如：BEFORE UPDATE），默认触发顺序与触发器的创建顺序一致，可以
trigger_order 使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。
FOLLOWS：当前创建触发器在现有触发器之后激活；FOLLOWS、PRECEDES
PRECEDES：当前创建触发器在现有触发器之前激活；
trigger_body 触发执行的SQL语句内容，一般以begin开头，end结尾 BEGIN .. END
触发执行语句内容（trigger_body）中的OLD，NEW
触发执行语句内容（trigger_body）中的OLD，NEW：在trigger_body中，
我们可以使用NEW表示将要插入的新行（相当于MS SQL的INSERTED），
OLD表示将要删除的旧行（相当于MS SQL的DELETED）。
通过OLD，NEW中获取它们的字段内容，方便在触发操作中使用，
下面是对应事件是否支持OLD、NEW的对应关系：
事件 OLD NEW
INSERT × √
DELETE √ ×
UPDATE √ √
由于UPDATE相当于删除旧行（OLD），然后插入新行（NEW），所以UPDATE同时支持OLD、NEW；
DELIMITER $
... --触发器创建语句；
$ --提交创建语句；
DELIMITER ;
select * FROM information_schema.triggers;
SHOW TRIGGERS; #查看触发器
-- 通过information_schema.triggers表查看触发器：
select * FROM information_schema.triggers;
-- mysql 查看当前数据库的触发器
SHOW TRIGGERS;
-- mysql 查看指定数据库"people"的触发器
SHOW TRIGGERS FROM people;
创建测试表
DROP TABLE IF EXISTS tb;
CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT);
CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT);
创建触发器
DELIMITER $
#drop trigger if exists df_names$ 删除前先判断触发器是否存在
CREATE DEFINER =CURRENT_USER
TRIGGER df_names
BEFORE INSERT ON tb
AFTER UPDATE ON bt
FOR EACH ROW
BEGIN
#set new.ct=new.id*5;
#SET @ct=12; 变量
#SET @pass='hjfd';
IF old.type=1 THEN
UPDATE bt SET ct=old.ct WHERE fid=old.id;
ELSE IF old.type=2 THEN
UPDATE bt SET pass=old.pass WHERE fid=old.id;
END$
DELIMITER;
测试
INSERT INTO tb(id) VALUES(4);
SELECT *FROM tb;
八、存储过程
-- 创建存储过程
# 返回产品平均价格的存储过程
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
select AVG(prod_price) AS priceaverage FROM products;
END //
DELIMITER ;
# 调用上述存储过程
CALL productpricing();
-- 删除存储过程,请注意:没有使用后面的()，只给出存储过程名。
DROP PROCEDURE productpricing;
-- 使用参数 out
# 重新定义存储过程productpricing
DELIMITER //
CREATE PROCEDURE productpricing(OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2))
BEGIN
select MIN(prod_price) INTO pl FROM products;
SELECT MAX(prod_price) INTO ph FROM products;
select AVG(prod_price) INTO pa FROM products;
END //
DELIMITER ;
# 为调用上述存储过程，必须指定3个变量名
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
# 显示检索出的产品平均价格
SELECT @priceaverage;
# 获得3个值
SELECT @pricehigh,@pricelow,@priceaverage;
-- 使用参数 in 和 out
# 使用IN和OUT参数,存储过程ordertotal接受订单号并返回该订单的合计
DELIMITER //
CREATE PROCEDURE ordertotal(
in onumber INT, # onumber定义为IN，因为订单号被传入存储过程
OUT ototal DECIMAL(8,2) # ototal为OUT，因为要从存储过程返回合计
)
BEGIN
select SUM(item_price*quantity) FROM orderitems
WHERE order_num = onumber
INTO ototal;
END //
DELIMITER ;
# 给ordertotal传递两个参数；
# 第一个参数为订单号，第二个参数为包含计算出来的合计的变量名
CALL ordertotal(20005,@total);
# 显示此合计
SELECT @total;
# 得到另一个订单的合计显示
CALL ordertotal(20009,@total);
SELECT @total;
-- 建立智能存储过程
# 获得与以前一样的订单合计，但只针对某些顾客对合计增加营业税
-- Name:ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
DELIMITER //
CREATE PROCEDURE ordertotal(
in onumber INT,
in taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'obtain order total, optionally adding tax'
BEGIN
-- declare variable for total 定义局部变量total
DECLARE total DECIMAL(8,2);
-- declare tax percentage 定义局部变量税率
DECLARE taxrate INT DEFAULT 6;
-- get the order total 获得订单合计
SELECT SUM(item_price * quantity)
FROM orderitems
WHERE order_num = onumber INTO total;
-- is this taxable? 是否要增加营业税？
if taxable THEN
-- Yes,so add taxrate to the total 给订单合计增加税率
select total+(total/100*taxrate) INTO total;
end IF;
-- and finally,save to out variable 最后，传递给输出变量
SELECT total INTO ototal;
END //
DELIMITER ;
# 调用上述存储过程，不加税
CALL ordertotal(20005,0,@total);
SELECT @total;
# 调用上述存储过程，加税
CALL ordertotal(20005,1,@total);
SELECT @total;
# 显示用来创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE ordertotal;
# 获得包括何时、由谁创建等详细信息的存储过程列表
# 该语句列出所有存储过程
SHOW PROCEDURE STATUS;
# 过滤模式
SHOW PROCEDURE STATUS LIKE 'ordertotal';
九、事务
SET AUTOCOMMIT=off ;禁用或启用事务的自动提交模式 off ON
SET SESSION AUTOCOMMIT = OFF；禁用或启用事务的session自动提交模式 off ON
SHOW VARIABLES LIKE '%auto%'; -- 查看变量状态
执行DML语句是其实就是开启一个事务
只能回滚insert、delete和update语句
对于create、drop、alter这些无法回滚事务只对DML有效果
rollback，或者commit后事务就结束了
自动提交模式用于决定新事务如何及何时启动
START TRANSACTION; 启用自动提交模式下显式地启动事务
COMMIT和ROLLBACK; 禁用自动提交模式显式地提交或回滚
-- 事务 transaction 指一组sql语句
-- 回退 rollback 指撤销指定sql语句的过程
-- 提交 commit 指将未存储的sql语句结果写入数据库表
-- 保留点 savepoint 指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）
-- 控制事务处理
# 开始事务及回退
SELECT * FROM ordertotals; # 查看ordertotals表显示不为空
START TRANSACTION; # 开始事务处理
DELETE FROM ordertotals; # 删除ordertotals表中所有行
SELECT * FROM ordertotals; # 查看ordertotals表显示 为空
ROLLBACK; # rollback语句回退
SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表显示不为空
# commit 提交
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT; # 仅在上述两条语句不出错时写出更改
# savepoint 保留点
# 创建保留点
SAVEPOINT delete1;
# 回退到保留点
ROLLBACK TO delete1;
# 释放保留点
RELEASE SAVEPOINT delete1;
-- 更改默认的提交行为
SET autocommit = 0; # 设置autocommit为0（假）指示MySQL不自动提交更改
创建账户:CREATE USER IF NOT EXISTS 'hw'@'localhost' IDENTIFIED BY '5201314'; #创建用户hw,密码5201314
给该用户授予所有权限并可授权给其它用户:GRANT ALL PRIVILEGES ON people.df TO 'hw'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
重命名用户名: RENAME USER 'hw' TO 'gh'; 必须将localhost改为%
如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 "%"
授权给其它用户 WITH GRANT OPTION
privileges包括：
alter：修改数据库的表
create：创建新的数据库或表
delete：删除表数据
drop：删除数据库/表
index：创建/删除索引
insert：添加表数据
select：查询表数据
update：更新表数据
all：允许任何操作
usage：只允许登录
刷新权限,使新创建的用户能够使用: FLUSH PRIVILEGES;
收回用户权限: REVOKE ALL PRIVILEGES ON people.df FROM 'root'@'localhost';
删除用户: DROP USER IF EXISTS 'hw'@'localhost';
设置指定用户的密码:SET PASSWORD FOR'hw'@'localhost' = PASSWORD('123321');
UPDATE USER SET PASSWORD = PASSWORD('123321') WHERE USER = 'hw';
设置密码: SET PASSWORD = PASSWORD('123321');
十二、其他
SHOW STATUS;显示广泛的服务器状态信息
SHOW PROCEDURE STATUS;
SHOW GRANTS;显示授予用户的安全权限
SHOW ERRORS;显示服务器的错误信息
SHOW WARNINGS;显示服务器的警告信息
SHOW PROCESSLIST;显示哪些线程正在运行
SHOW VARIABLES;显示系统变量信息
SELECT DATABASE(); 查看当前数据库
SELECT NOW(), USER(), VERSION():显示当前时间、用户名、数据库版本
SHOW ENGINES 引擎名 {LOGS|STATUS}:显示存储引擎的日志和状态信息
SHOW VARIABLES LIKE 'character%'; SHOW VARIABLES LIKE 'collation%'; 确定所用系统的字符集和校对
SHOW VARIABLES LIKE 'character_set_client%'; 客户端向服务器发送数据时使用的编码
SHOW VARIABLES LIKE 'character_set_results%'; 服务器端将结果返回给客户端所使用的编码
SHOW VARIABLES LIKE 'character_set_connection%'; 连接层编码
SHOW CHARACTER SET;查看所支持的字符集完整列表
SHOW COLLATION;查看所支持校对的完整列表,以及它们适用的字符集
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
SET NAMES GBK; -- 相当于完成以上三个设置
创建window服务:sc CREATE mysql binPath= mysqld_bin_path
数据文件目录:DATA DIRECTORY='目录'
索引文件目录:INDEX DIRECTORY = '目录'
数据库
发布于：2021-09-29 20:43
|
阅读数：510
|
评论：0
QQ好友和群
QQ空间
