表操作和其它常用对象
一、数据操作语句（DML）
Data Manliplation LANGUAGE 数据操作语句，主要作用是操纵数据(增、删、改)
1、事务
- 事务（transaction）是由一个或多个SQL语句组成，要么全部执行成功，要么全部不执行或回滚。只要有一条执行失败，所有的执行都不会提交到数据库
- MySQL只有使用了innodb数据库引擎的库或表才支持事务
- 在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT 操作
- 要显式地开启一个事务可使用命令BEGIN；或 START TRANSACTION；或者执行命令SET AUTOCOMMIT=0；设置当前会话不自动
- commit提交
- commit;为提交
事务的特性：
- 事务的4大特性(ACID ):
- 原子性(Atomicity )：一个事务中的所有操作，要么全部完成，要么全部不完成。
- 一致性(Consistency )：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
- 隔离性(Isolation )：防止多个事务并发执行时由于交叉执行而导致数据的不一致。
- 持久性(Durability )：事务处理结束后，对数据的修改就是永久的。
**事务：**在mysql中，所有的sql语句被执行以后，就生成了一个事务，如果使用commit可以将事务提交给服务器，如果没有提交，只是在本地会生效。
– 事务一旦提交，就不能回滚，只能在没有提交之前回滚。
– 查看是否自动提交事务。1表示自动提交,0表示不自动提交,需要手动commit
SELECT @@autocommit;– 将数据提交设置为手动提交 为1是自动提交
SET @@autocommit=0;– 可以begin;或 start TRANSACTION; 开启一个不自动提交的事务。后面可以手动commit提交
begin;
UPDATE emp set sal=2000 WHERE ename='smith';
SELECT * FROM emp;
commit;#提交更改,然后关闭事务。
rollback;#注意,已经提交的事务,不能rollback
start TRANSACTION; #和begin;效果一样,开启一个不自动提交的事务
UPDATE emp set sal=1000 WHERE ename='james';
SELECT * FROM emp;
rollback;#撤销更改,然后关闭事务。
2、INSERT 语句
INSERT 语句的两种格式：
- INSERT INTO 表名(列1，列2，… 列n) VALUES (值1，值2，…值n) – 往指定列中插入数据
注意：值和列必须一一对应- INSERT INTO 表名 VALUES (值1，值2，…值n) – 往所有列中插入数据
注意：VALUES里面的值必须跟表中的所有列一一对应
注：表名后面没有指定列名,则values里面的值必须和表所有的列顺序一一对应填写。
如果表名后面指定了列名,则values里面的值,个数及顺序都要和指定的列名对应。没有写的列会默认填入默认值。如果建表的时候没指定默认值,则默认为空null
如果要指定值为空,可以填null,要填入默认值,则写default
-- 给部门表添加一条记录,部门编号50,部门名称DEVELOP,部门地点CQ
#表名后面没有指定列名,则values里面的值必须和表所有的列顺序一一对应填写。
SELECT * FROM dept;
INSERT INTO dept VALUES(50, 'DEVELOP', 'CQ');
INSERT INTO dept(deptno, dname) VALUES(60, 'TESTING');
-- 给emp添加一个数据
SELECT * FROM emp;
INSERT INTO emp values(6666, '小沈阳儿', 'TALKER',null,'2020-11-16', 3000,0,DEFAULT);
-- 一次添加多条记录
INSERT into dept VALUES(80,'测试部','重庆'),(90,'研发部','上海'),(100,'产品部','北京');
SELECT * FROM dept
-- 从其他表查询数据,添加到指定表中
-- INSERT INTO 表名 SELECT
-- 注意：这种写法，必须要求接受表已经存在，且查询列和接受表的列要一一对应（数量、数据类型、顺序）
SELECT * FROM bonus;
INSERT INTO bonus
SELECT ename, job, sal, comm FROM emp WHERE comm is not null;
3、UPDATE 语句
-- 给smith工资涨到1200。注意,如果不写where条件,整个表这一列都会被更改。
UPDATE emp
SET sal = 1300
WHERE ename = 'smith';
commit; -- 提交
rollback; -- 回滚
SELECT * FROM emp;
-- 修改多列,用逗号隔开.
-- smith表现出色，老本决定将他升职为经理，加薪到3000
update emp
set job = 'MANAGER', sal = 3000
WHERE ename = 'smith';
-- 20部门年度表现优秀,所有人工资加200
UPDATE emp
set sal = sal + 200
WHERE deptno = 20
4、DELETE 语句
– update/delete 都可以在where 子句后面做子查询
– delete使用where子句可以删除指定数据**注：**delete不跟where时，会删除所有表中的数据，但是并不会破坏表结构
delete FROM 表名 会清空表中所有数据，但表还在，结构也还在
-- delete使用where子句可以删除指定数据
DELETE FROM emp
WHERE deptno =
(SELECT deptno FROM dept WHERE dname like 'sales%');
DELETE FROM bonus;
SELECT * FROM bonus;
INSERT INTO bonus
SELECT ename, job, sal, comm FROM emp;
5、TRUNCATE 语句（DDL）
truncate删除整张表数据,效率比delete高,不能用where指定
truncate不是DML语句。是DDL语句。运行truncate后直接提交。
truncate与delete区别：
– truncate 会删除整张表的数据内容，执行效率比delete要高，但是不会破坏表结构；不需要提交，也不能回滚
– delete 删除时会将表中数据，一行一行进行逐行删除，删除时还会将删除内容写入日志，因此可以从日志中找回被删除数据。在事务提交之前，删除语句可回滚，同时，delete需要提交才会生效。
TRUNCATE TABLE bonus;
二、表的管理（DDL语句）
1、创建、删除库
-- 创建一个库
# create DATABASE 创建数据库
create DATABASE test;
-- drop DATABASE test;
-- 切换到一个库
# use 数据库名 切换到指定数据库
use test;
2、创建表
表的命名规则：
- 必须以字母开头
- 可以包含字母，数字和下划线（_），最好有实际的含义
- 不能是系统的关键字
可指定：
- 表名
- 列名，列的数据类型，列的大小，默认值等
查看表的一些相关信息
DESCRIBE test1;
DESC test1;查看建表语句
SHOW CREATE TABLE test1;查看表是否设置严格限制
select @@sql_mode;
-- 创建表
CREATE TABLE a(
id int,
name varchar(10)
);
-- 插入数据
INSERT INTO a VALUES(1,'testaaa');
-- 删除一个库
DROP DATABASE test;
-- 切换到testdb库
use testdb;
-- 获取当前时间的时间戳
SELECT unix_timestamp(now())
-- 创建表test1
create TABLE test1(
testid int comment 'id号',# comment后面是备注信息
testname varchar(20),
sex char(1) comment '性别',
age tinyint UNSIGNED comment '年龄', # tinyint后面加上unsigned表示无符号类型,取值范围0-255
score FLOAT(5,2) comment '成绩', # FLOAT(5.2) 5位数，其中2位小数
birthday date
);
-- 查看建表语句
SHOW CREATE TABLE test1;
# 建表语句会读取某些sql的配置信息，因此跟我们的sql不完全相同。
CREATE TABLE `test1` (
`testid` int(11) DEFAULT NULL COMMENT 'id号',
-- int(11)这里的11不是数值范围长度,而是显示的时候的宽度
`testname` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
-- COLLATE utf8mb4_unicode_ci 表示用order by排序时,的排序规则
`sex` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
`age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
`score` float(5,2) DEFAULT NULL COMMENT '成绩',
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- 默认存储引擎为innodb
3、常见数据类型
1）整数类型：
整数类型分为有符号类型和无符号类型，无符号类型最小值为0
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gDJ7kwNh-1608090410120)(https://s3.ax1x.com/2020/12/13/rerjFf.png)]
2）小数类型:
小数类型有浮点数和定点数，浮点型精度较低可能结果有偏差
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FmBeV9EA-1608090410122)(https://s3.ax1x.com/2020/12/13/resstf.png)]
3）字符类型
char(n)定义长度为n个字符，存储空间固定长度，n的最大值是255,不足长度用空格填充。
varchar(n)定义最大长度为n个字符，存储空间可变长度，n的最大值需要换算,把字符换算成字节后,最大不超过65535字节
区别：
char(n)如果存储的数据不到n个字符，会使用空格补齐到n个字符
varchar(n)按照实际的长度存储数据
其他字符类型：
TEXT 长文本数据,存储较多内容的文字,比varchar可以存储得更多。
BLOB 二进制形式的数据,可以保存图片。
4）日期和时间类型：
date :日期格式:2020-01-02
time :时间格式:20:24:56
datetime :日期和时间 格式:2020-01-02 20:24:56year:年份 格式: 2020
timestamp:时间戳，如果这一行记录被修改，并且不指定timestamp这个字段的值。则时间戳会更新到最新的修改时间。
格式和datetime一样: 2020-01-02 20:24:56
插入值注意点：
- 如果插入的数字小数位数精度超出范围，会自动四舍五入
- 数字可以直接被看作是字符,插入到表中
- 如果插入值的类型不符合字段类型格式及范围,会提示相应错误
INSERT INTO test1 VALUES(1,'张三','男',18,90.5,'2000-01-01');
SELECT * FROM test1;
-- 插入字符串到int类型字段,报错。
-- 注意：数值型字符串，就是加了引号的数字，可以被识别为int
INSERT INTO test1 VALUES('a','张三','男',18,90.5,'2000-01-01');
-- 当字符串内容就是一个数字时,会自动转为数字,然后保存成功
INSERT INTO test1 VALUES('12','李四','男',18,90.5,'2000-01-01');
-- 当字段插入内容超过指定长度,报错
INSERT INTO test1 VALUES(2, '王五', '男性', 30, 90, '1788-01-01');
-- 小数超过指定位数,四舍五入到指定位数
INSERT INTO test1 VALUES(3,'王五','男',18,90.55555555,'2000-01-01');
-- 整数部分超过长度,报错
INSERT INTO test1 VALUES(3,'王五','男',18,1000,'2000-01-01');
– date类型,插入now(),会取日期部分来保存到数据库中
– 或者使用日期指定格式，也可以将日期信息插入 ‘YYYY-MM-DD’
SELECT now();
INSERT INTO test1 VALUES(4,'赵四儿','男',18, 90, now());
-- 查看是否启用了严格模式STRICT_TRANS_TABLES
SELECT @@sql_mode;
4、给列定义默认值
– 创建test2表,指定一些列的默认值
create TABLE test2(
testid int comment 'id号', # comment后面是备注信息
testname varchar(20),
sex char(1) DEFAULT '男' comment '性别',
age tinyint UNSIGNED DEFAULT 18 comment '年龄', #tinyint后面加上unsigned表示无符号类型,取值范围0-255
score FLOAT(5,2) DEFAULT 60 comment '成绩',
birthday date
);
SELECT * from test2;
desc test2;
– 默认值如果不填入才会有，如果填入其他值，会覆盖默认；可以使用default来替代默认值
– 注意：null也会覆盖默认值
INSERT into test2 values (1, '张三丰', null, null, null, null);
– 当没有指定的列,那么都是填默认值,没有默认值才填入null
INSERT INTO test2(testname) VALUES('李四');
– 插入有默认值的列，可以使用default来代表默认值
INSERT into test2(testid, testname, sex, age, score) VALUES (2, '李狗蛋', DEFAULT, DEFAULT, DEFAULT);
INSERT into dept VALUES(null,'test','cq'); #deptno会自动增加1
5、自增值
在定义时，类型后加上
auto_increment**注：**每张表只能设置一个字段为自增长字段，自增列必须是主键或外键或唯一约束
– 创建表test3,设置一个自增列
CREATE table test3(
id int auto_increment PRIMARY KEY, # primary key 是主键的意思
name varchar(20)
);
SELECT * from test3
INSERT INTO test3 VALUES(null,'aaa'); # 自增列如果设置值为null，也会有自增值出现
INSERT INTO test3 VALUES(default,'bbb'); # default也会出现自增值
INSERT INTO test3 VALUES(50,'ccc'); # 插入的值如果属于自增值后续的值，则会被采用
INSERT INTO test3(name) VALUES('ddd'); # 自增值跟上一行的值有关联
6、使用create…as select…创建表
- create table表名as select …; 将查询结果直接创建成—张表
- 表名后面可以加上括号，定义列名，但是列名必须跟查询结果中的列对应
- 表名后面不能定义数据类型
- 通常情况下，直接在查询语句中给列起别名，作为最终表的列名
- – create table 表名 as SELECT 查询语句; 这种写法可以把查询结果插入到表中，并不需要新建表
– 从另一个表查询数据,创建一个新表
CREATE TABLE dept20
AS
SELECT empno, ename, sal, sal*12 年薪, deptno FROM testdb.emp # 数据名.表名 可以帮我们找到指定的表
WHERE deptno = 20;
7、修改表结构
- 使用ALTER命令修改表的列属性
- 可以添加/删除列，修改列名/数据类型，添加/修改默认值…
- 直接生效，不需要提交也不能回滚
注意：
- 创建表之前需要充分考虑各种情况，尽量谨慎的设计表结构
- 创建表之后，不推荐再修改结构，尤其是当表中有数据之后，可能对数据造成破坏
- 如果确实万不得已，需要使用alter语句修改表结构，要注意不要对数据造成破坏
SELECT * FROM dept20
-- 给dept20表加一列，叫做电话号码，数字11位
-- after 可以将列 排到指定列后面
alter TABLE dept20 ADD tel INT(11) AFTER ename;
-- alter table 表名 drop 列名 可以删除指定列
alter TABLE dept20 DROP tel;
SELECT * FROM test2;
desc test2;
8、修改表
-- 添加一列,班级class。添加到第一列。
ALTER TABLE test2 ADD class varchar(10) FIRST; # FIRST 会把列放到第一位
-- 修改testname 为 sname 并且数据长度30位
-- change可以改变列的名字和数据类型及长度
ALTER TABLE test2 CHANGE testname sname VARCHAR(30);
-- 如果只修改数据类型，可以使用modify
-- 修改xuehao的类型为varchar
ALTER TABLE test2 MODIFY xuehao varchar(8);
-- 修改性别默认值为女
ALTER TABLE test2 ALTER sex SET DEFAULT '女';
-- 查看表信息
DESC test2;
-- 删除默认值
-- 将默认值设置为空，其实也是一种删除默认值的方法
ALTER TABLE test2 ALTER sex DROP DEFAULT;
ALTER TABLE test2 ALTER age SET DEFAULT null;
-- 删除一张表
DROP TABLE dept20; # 整张表被删除，所有索引被删除，不能回退
-- 修改表名
RENAME TABLE test2 TO test0;
-- 通过alter table修改表名
ALTER TABLE test0 RENAME to test2;
DROP删除表：
DROP TABLE 表名;
- 整张表被删除
- 所有索引被删除
- 不能回退
TRUNCATE删除表：
truncate table 表名;
- truncate是站在表的层面删除所有数据，快速清空表
- truncate执行效率高于delete，不能删除指定的行，只能删除全部 truncate属于ddl语句，直接提交，不能回滚。
/*
mysql中有几种删除方式，分别有什么区别？DELETE:
DML语句，可以指定删除内容，也可以删除整张表的数据，删除时是逐行删除，并且会将删除信息写入日志，需要提交才能生效，提交之前可以回滚。不破坏表结构，执行效率最低
TRUNCATE:
DDL语句，不需要提交，也不能回滚。一次性清空表中所有数据，不能指定删除，不会破坏表结构，执行效率比delete更快
DROP:
DDL语句，不需要提交，也不能回滚，一次性删除表中所有数据，并且会删除其他所有表对象(索引、视图、触发器、…)，会对表产生破坏性伤害，删完以后，表直接消失。
*/
三、约束
什么是约束：
- 约束就是对列中数据的要求或者规范，不满足条件的数据不能放入表中
- 当表中数据有相互依赖性时,可保护数据不被删除
- 约束的类型：
- 主键约束——primary key，表示该列为主键，取值必须唯一且不能为空
- 外键约束——foreign key，表示该列为外键，对应另一张表的主键，取值要么为空要么为参照主键里存在的值（
foreign key(本表列名) references 表名(列名)）- 非空约束——not null，表示该列不能为空
- 唯一约束——unique，表示该列取值不能重复
- 检查约束——check，规定取值范围。mysql不支持
约束概况：
- 两种方式创建约束：
- 在建表的同时创建
- 建表后创建
- 可定义列级或表级约束
1、建表时创建约束
– 创建表test4,设置约束。主键、外键、非空、唯一。
CREATE TABLE test4(
testid int PRIMARY KEY, #主键
testname varchar(20) NOT NULL, #非空
deptno int,
phone char(11) UNIQUE, #唯一
FOREIGN KEY (deptno) REFERENCES testdb.dept(deptno) #指定外键为本表的deptno列。参照dept表的deptno,这是表级约束的写法
);
# 注意：外键约束是最难以记忆的，需要大家回家多敲两边记到 脑子里面去。
desc test4;
INSERT INTO test4 VALUES(1,'张三',10,'13111111111');
SELECT * FROM test4
-- 主键不能重复,且不能为空
INSERT INTO test4 VALUES(1,'李四',20,'13222222222');
INSERT INTO test4 VALUES(null,'李四',20,'13222222222');
-- 设置了not null的列,不能插入空
INSERT INTO test4 VALUES(2,null,20,'13222222222'); # Column 'testname' cannot be null
-- 设置了unique的列,不能重复
INSERT INTO test4 VALUES(2,'李四',20,'13111111111');
SELECT * from test4
SELECT * FROM dept;
-- 外键的取值必须是参照的主键里面已经有了的值。或者可以为空
INSERT INTO test4 VALUES(2,'李四',70,'13222222222');
INSERT INTO test4 VALUES(3,'李四',null,'13222222221');
INSERT INTO test4 VALUES(4,'李四',99,'13222222223'); # 外键取值必须参照其主键的值，也可以为空
2、建表后增加约束
– 创建一个没有约束的表test5
CREATE TABLE test5(
testid int,
testname varchar(20),
deptno int,
phone char(11)
);
desc test5;
-- 添加主键
ALTER TABLE test5 ADD PRIMARY KEY(testid);
# 删除单一主键约束，然后建立一个复合主键
ALTER TABLE test5 DROP PRIMARY key;
ALTER TABLE test5 ADD PRIMARY KEY(testid,testname); #可以添加多列为一个主键
desc test5;
-- 添加外键
ALTER TABLE test5 ADD FOREIGN key (deptno) REFERENCES testdb.dept(deptno);
-- 添加非空约束
ALTER TABLE test5 MODIFY testname VARCHAR(20) NOT NULL;
-- 添加唯一约束
ALTER TABLE test5 ADD UNIQUE(phone);
-- 删除主键约束
ALTER TABLE test5 DROP PRIMARY KEY;
-- 删除外键约束。需要写上外键的约束名
-- 注意：外键删除需要外键的名字，可以在表上点击右键-> 设计表->外键->外键名 获取到
ALTER TABLE test5 DROP FOREIGN KEY test5_ibfk_1;
-- 删除非空约束
-- 把非空约束设置为空就好了
ALTER TABLE test5 MODIFY testname varchar(20) NULL;
-- 删除唯一约束
-- alter table 表名 drop index 唯一约束列名; 非常特殊的一个，请记住
ALTER TABLE test5 DROP INDEX phone;
四、数据库对象
1、视图
- 视图(view),不保存数据，保存的是一个查询语句，可以理解为一个查询语句的快捷方式。
- 通过视图可以快速的访问一些数据。原查询语句所查询的表叫做基表。
- 对视图的操作相当于是对基表中数据的操作。
- 视图可以简化复杂的查询语句
**注：**创建视图时，如果有重名的视图，不写or replace会报错，写上 or replace会替换
CREATE OR REPLACE view dept_emp
as
SELECT e.*, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;
-- 把视图当表来查询
SELECT * FROM dept_emp;
-- 修改视图dept_emp里面的数据,实际修改的是emp表的数据
UPDATE dept_emp
set sal = 900
WHERE ename = 'smith'; # 对视图的修改，就会修改基表的数据
SELECT * FROM emp WHERE ename = 'smith';
-- 修改视图保存的查询语句
-- 视图可以被replace关键字给替换掉内容，因此一定要注意。
CREATE or REPLACE VIEW dept_emp
as
SELECT * FROM testdb.emp WHERE deptno=10;
-- 删除视图
DROP VIEW dept_emp;
2、索引
- 索引(index),相当于是针对数据创建的目录，可以提高查询效率
- 经常用来作为查询条件(where)、分组(group by)、排序(order by)的列
- 创建索引，提高查询和其他dml语句执行的效率
- 索引创建成功后，如果用到相关的列来进行查询，索引会自动生效,提高查询效率
- 如果列中的数据发生变化，索引会自动更新，调整它的内容，所以索引也会造成系统的负担，数据变更也会去更新索引的内容，不经常使用的列不要盲目的创建索引
MySQL目前主要几种索引类型：
- 普通索引–最基本的索引，提升查询速度,但不会约束列的值。
- 唯一索引–索引列的值必须唯一，但允许有空值。
- 主键索引–索引列的值必须唯一，不允许空值。
– 索引 把某列单独保存起来,提升查询速度
-- 创建一个索引,索引名定为ind1
CREATE INDEX ind1 on emp(ename);
-- 查看emp表有哪些索引
SHOW INDEX FROM emp;
-- 使用explain查看这个查询是否使用了索引
explain SELECT * FROM emp WHERE sal>2000;
explain SELECT * FROM emp WHERE ename='allen';
-- 指定多列为一个索引
CREATE INDEX ind2 on emp(deptno,job);
-- 多列索引,查询时,只有使用了第一列作为查询条件时,才会使用这个索引来提升查询速度。
EXPLAIN SELECT * FROM emp WHERE job='clerk'; #只使用第二列作为查询条件,不会使用索引
EXPLAIN SELECT * FROM emp WHERE job='clerk' and deptno=20; #同时使用两列作为条件,会使用索引
EXPLAIN SELECT * FROM emp WHERE deptno=20; #只使用第一列作为条件,也会使用索引。
– 在建表时，使用：
INDEX 索引名(列名) 可以建立索引
– 在建表的时候,创建索引
CREATE TABLE test7 (
id int PRIMARY KEY,
title char(10) ,
content varchar(100) ,
INDEX ind1 (title) #这里关键字用index或key都可以
);
SHOW INDEX FROM test7;
-- 使用alter table来创建索引
ALTER TABLE test7 ADD INDEX ind2(content);
-- drop index删除索引
-- 注意：删除时要的是索引名字，因此可以通过 “show index from 表名” 来可查看
DROP INDEX ind1 on test7;
-- alter table来删除索引
ALTER TABLE test7 drop index ind2;
-- 删除主键,就会自动删除主键索引
ALTER TABLE test7 DROP PRIMARY KEY;
3、存储过程
存储过程(Stored Procedure )是一种在数据库中使用sql编写的程序，以便外部程序直接调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数来调用执行。
CALL 存储过程名(参数);MySQL存储过程的参数用在存储过程的定义，有三种参数类型IN,OUT,INOUT,形式如:
CREATE PROCEDURE存储过程名(IIN OUT [INOUT ]参数名数据类型...])
BGEIN
语句;
END;
– 创建一个自动添加部门的存储过程。只要输入要创建的条数就可以了。
delimiter $ #修改结束符号为$号
CREATE PROCEDURE adddept(IN n int) #调用时,需要传入一个int类型的值
BEGIN
DECLARE i int DEFAULT 1; #定义一个变量i,初始值1
while i<=n DO
INSERT INTO dept(dname,loc) VALUES('testdname','testloc');
set i = i+1;
end while;
END$
delimiter ; #把结束符号修改回;号, 否则系统要读取到$才会结束
-- 调用存储过程
call adddept(20);
SELECT * from dept;
-- 删除存储过程
DROP PROCEDURE adddept;
4、自定义函数
– 使用自定义的函数
– FUNCTION函数 和 PROCEDURE存储过程 的区别：
– 函数可以直接在查询语句中调用，并且函数必须有返回值
– 存储过程需要使用 call 存储过程名字调用，不一定有返回值
-- 创建一个函数,给输入的字符串。左右各加5个星号
CREATE function addxinghao(str varchar(20)) RETURNS varchar(30)
BEGIN
declare str2 varchar(30);
set str2=concat('*****',str,'*****');
return str2;
END;
SELECT addxinghao(ename), addxinghao(job) FROM emp;
-- 删除函数
DROP FUNCTION addxinghao;
5、触发器
在数据库中可以设置触发器。当对指定表的数据进行增、删、改时，执行一些sql语句
– 创建一个触发器
-- 当从emp表删除一个员工时,将员工的ename,job,sal,comm信息写入到bonus表中
CREATE TRIGGER t1 AFTER DELETE
on emp FOR each row
BEGIN
INSERT INTO bonus VALUES (old.ename,old.job,old.sal,old.comm);
END;
-- 查看有哪些触发器
SHOW TRIGGERS;
-- 删除数据时,会自动触发触发器里面的语句
DELETE FROM emp WHERE ename='smith';
SELECT * from bonus;
-- 删除触发器：
DROP TRIGGER t1;
五、用户与权限管理
- MySQL用户要看两个字段,用户名和host地址。默认用迁为root,host为localhost。只能在当前电脑登录使用。
- MySQL用户名，密码，host地址及操作权限等信息保存在自带的mysql库的user表中。
- MySQL 5.7以前用户的密码字段为password字段，Mysql5.7以后，密码保存在authentication_string字段中。
- 相同的用户名， host不同，则认为是不同的用户。其密码、操作权限等都可以设置为不同。“%”百分号表示任意地址都可以登录。
1、用户管理
- 登录MySQL数据库
- CMD窗口中执行:
mysql -u root -p回车，然后输入密码，回车，即可登录- 选项说明:
- -h 主机名或IP地址(本机登录可省略此选项)
- -P 端口号(端口号没改,默认3306,则可省略此选项)
- -u 账号
- -p 密码(密码内容不要直接输,敲回车后输入)
- 使用create user创建用户
CREATE USER 'test' @'%IDENTIFIED BY '123456';- 使用grant赋予用户权限,ALL PRIVILEGES表示所有权限，表示所有库和表
GRANT ALL PRIVILEGES ON*.* TO"test'@"%';- 使用grant赋予用户权限时指定密码，如果用户不存在,则创建用户
GRANT ALL ON*.* TO 'root'@'%'IDENTIFIED BY '123456";- 使权限设置生效:
FLUSH PRIVILEGES;- 使用alter user修改自己的密码
ALTER USER USER() IDENTIFIED BY '123456';- 使用alter user修改别的用户的密码（管理员执行)
ALTER USER 'test' @'%'IDENTIFIED BY '123456";- 使用set password修改自己的密码
SET PASSWORD= '123456';- 使用set password修改别的用户的密码(管理员执行)
SET PASsWORD FOR'test'@1%'= '654321';- 删除用户
DROP USER 'test'@1%";- 查看用户表的host,用户名,密码字段。
SELECT host,user,authentication_string FROM mysql.user;
- -h 主机名或IP地址(本机登录可省略此选项) - -P 端口号(端口号没改,默认3306,则可省略此选项) - -u 账号 - -p 密码(密码内容不要直接输,敲回车后输入)
- 使用create user创建用户
CREATE USER 'test' @'%IDENTIFIED BY '123456';- 使用grant赋予用户权限,ALL PRIVILEGES表示所有权限，表示所有库和表
GRANT ALL PRIVILEGES ON*.* TO"test'@"%';- 使用grant赋予用户权限时指定密码，如果用户不存在,则创建用户
GRANT ALL ON*.* TO 'root'@'%'IDENTIFIED BY '123456";- 使权限设置生效:
FLUSH PRIVILEGES;- 使用alter user修改自己的密码
ALTER USER USER() IDENTIFIED BY '123456';- 使用alter user修改别的用户的密码（管理员执行)
ALTER USER 'test' @'%'IDENTIFIED BY '123456";- 使用set password修改自己的密码
SET PASSWORD= '123456';- 使用set password修改别的用户的密码(管理员执行)
SET PASsWORD FOR'test'@1%'= '654321';- 删除用户
DROP USER 'test'@1%";- 查看用户表的host,用户名,密码字段。
SELECT host,user,authentication_string FROM mysql.user;
873
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
