表操作和其它常用对象
一、数据操作语句(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;