一、ddl、dml、dcl常用语句
1、ddl(data definition language)数据库定义语言
(1)数据库模式定义
#创建数据库 create database if exsites db_name; #选定数据库 use db_name; #删除数据库 drop database if exists db_name; #修改数据库 alter database db_name set ...; #展示所创建的数据库 show databases;
(2)表定义
#创建表 create table test_table ( s_id int not null auto_increment, s_name char(50) not null default "hanmei", s_age int not null, primary key(s_id), index index_name(s_name) ); #删除表 drop table if exists test_table; #展示表结构 desc test_table;
2、dml(data manipulation language)数据库操作语言
insert into test_table(s_age) values(18); insert into test_table set s_age=19; #插入部分列值数据 inert ...select...; #case...when 匹配条件 select s_name as name,s_sex case when 'f' then ‘女’ else '男' end as sex from test_table; #使用内置函数 select count(*) from customers; select max(cust_id) from customers; select min(cust_id) from customers; select sum(cust_id) from customers; select avg(cust_id) from customers; #交叉连接(笛卡尔积) select * from tb1 cross join tb2; #内连接 #---左外连接 select * from stu_info inner join stu_score on stu_info.sno=stu_score.sno; select stu_info.sno,stu_info.sname,stu_score.sscore from stu_info left join stu_score on stu_info.sno=stu_score.sno; #---右外连接 select stu_info.sno,stu_info.sname,stu_score.sscore from stu_score right join stu_info on stu_score.sno=stu_info.sno; #比较运算符 select * from customers where cust_id!=2; select * from customers where cust_id<>2; #逻辑运算符 #---and 与 select * from customers where cust_id>2 and cust_sex=1; #---or 或 select * from customers where cust_id>2 or cust_sex=1; #两者之间 范围 select * from customers where cust_id between 2 and 4; select * from customers where cust_id>=2 and cust_id<=4; #in select * from customers where cust_id in(2,4); select * from customers where cust_id=2 or cust_id=4; #子查询 select * from stu_info where sno in(select sno from stu_score); #分组查询 select ssex,count(*)from stu_info group by ssex; select saddress,ssex,count(*) from stu_info group by saddress,ssex; select saddress,ssex,count(*) from stu_info group by saddress,ssex with rollup; #having 筛选---过滤分组后的数据 select saddress,ssex ,count(*) from stu_info group by saddress,ssex having count(*)>1;
3、dcl(data control language)数据库控制语言
安全与访问控制 -- 查看 mysql 数据库的使用者账号 select user from mysql.user; -- 密码加密 select password(456); -- 创建用户 create user 'zhangsan'@'localhost' identified by '123', 'lisi'@'localhost' identified by password '*531e182e2f72080ab0740fe2f2d68 9dbe0146e04'; -- 删除用户账号 drop user lisi@localhost; -- 重命名 rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost'; -- 修改密码 set password for 'wangwu'@'localhost'='*6b4f89a54e2d27ecd7e8da05b4ab8fd9d1d8b119'; -- 设置权限 grant select n test1.customers o 'wangwu'@'localhost'; -- 创建两个用户 grant select,update on test1.customers to 'liming'@'localhost' identified by '123', 'huang'@'localhost' identified by '789'; --执行所有数据库操作的权限 grant all on test1.* to 'wangwu'@'localhost'; -- 添加用户的权限 grant create user on *.*to 'wangwu'@'localhost'; -- 权限转移 grant select,update on test1.customers to 'zhou'@'localhost' identified by '123' with grant option; -- 权限撤回 revoke select on test1.customers from 'zhou'@'localhost';
in 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量);
out 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量);
inout 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量);
mysql> delimiter $$ mysql> create procedure proc_add_stu(
-> in sno integer, -> out sid int -> ) mysql> begin #存储过程开始 -> insert into student(s_no) values(sno); -> select last_insert_id() into sid; #将选定列的值直接存储到局部变量中 -> end $$ #存储过程结束 mysql> delimiter; #将语句的结束符号恢复为分号 mysql> call pro_add_stu('0001');
mysql> delimiter $$ mysql> create procedure in_proce(in p_in int) -> begin -> select p_in; -> set p_in=0; #局部变量赋值(begin...和end之间) -> select p_in; -> end$$ mysql> delimiter ; mysql> set @p_in=1; #全局变量@p_in赋值 mysql> call in_param(@p_in); #将全局变量@p_in的值作为参数传递给局部变量p_in +------+ | p_in | +------+ | 1 | +------+ +------+ | p_in | +------+ | 0 | +------+ mysql> select @p_in; #输出全局变量@p_in的结果 +-------+ | @p_in | +-------+ | 1 | +-------+
以上可以看出,p_in 在存储过程中被修改,但并不影响 @p_id 的值,因为前者为局部变量、后者为全局变量。
mysql> delimiter // mysql> create procedure out_proce(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> // mysql> delimiter ; mysql> set @p_out=1; mysql> call out_proce(@p_out); +-------+ | p_out | +-------+ | null | +-------+ #因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
+-------+ | p_out | +-------+ | 2 | +-------+ mysql> select @p_out; #输出全局变量(用户变量)结果 +--------+ | @p_out | +--------+ | 2 | +--------+ #调用了out_proce存储过程,输出参数,改变了p_out变量的值
mysql> delimiter $$ mysql> create procedure inout_proce(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end -> $$ mysql> delimiter ; mysql> set @p_inout=1; mysql> call inout_proce(@p_inout); +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ mysql> select @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+ #调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
变量作用域
内部的变量在其作用域范围内享有更高的优先权,当执行到 end。变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储过程外再也不能找到这个申明的变量,但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。
mysql > delimiter // mysql > create procedure proc3() -> begin -> declare x1 varchar(5) default 'outer'; -> begin -> declare x1 varchar(5) default 'inner'; -> select x1; -> end; -> select x1; -> end; -> // mysql > delimiter ;
条件语句
mysql > delimiter // mysql > create procedure proc2(in parameter int) -> begin -> declare var int; -> set var=parameter+1; -> if var=0 then -> insert into t values(17); -> end if; -> if parameter=0 then -> update t set s1=s1+1; -> else -> update t set s1=s1+2; -> end if; -> end; -> // mysql > delimiter ;
循环语句
mysql > delimiter // mysql > create procedure proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > delimiter ;
create procedure p1() begin declare id int; declare name varchar(15); -- 声明游标 declare mc cursor for select * from class; -- 打开游标 open mc; -- 获取结果 fetch mc into id,name; -- 这里是为了显示获取结果 select id,name; -- 关闭游标 close mc; end;
#删除已经存在的存储函数 drop function if exists func_stu; #创建存储函数(声明返回类型为varchar(50)) create function func_stu(in_id int) returns varchar(50) begin declare o_name varchar(50); #声明局部变量 select name into o_name from tb_stu where id = in_id; #tb_stu指事先创建好的数据库 return o_name; end;
select func_stu(1);
drop function if exists func_stu;
5、修改存储函数
alter function func_name [characteristic ...] characteristic: comment 'string' | language sql | { contains sql | no sql | reads sql data | modifies sql data } | sql security { definer | invoker }
感谢阅读,如需转载,请注明出处,谢谢!https://www.cnblogs.com/huyangshu-fs/p/11669708.html
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
小白安装登录mysql-8.0.19-winx64的教程图解(新手必看)
Navicat连接MySQL时报10060、1045错误及my.ini位置问题
网友评论