MySQL omissions in software test learning

function

-- function
-- Empty operation
select trim('  ss  ');
-- Rounding
select round(-1.5);     -- The result is-2
select round(1.5);      -- The result is 2   
-- Get 0 at random-10 Integer
select round(rand()*10);
-- Randomly take records from a table
select *,rand() from student order by rand() limit 1;
-- select Medium rand()Omission
select *from student order by rand() limit 1;
-- Process control statement case
select 
case 3 
when 1	then 'one'
when 2 then 'two'
else 'zero'
end as result;

-- Change address according to gender
select name Full name ,concat(left(trim(name),1),
case sex
when 'male' then 'Sir'
when 'female' then 'Miss'
end) as call
from student WHERE name like '__';

-- Create functions to implement
create function title(name varchar(2),sex varchar(1)) returns varchar (3)
BEGIN
return concat(left(trim(name),1),
case sex
when 'male' then 'Sir'
when 'female' then 'Miss'
end);
end

-- Query the created function or stored procedure
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='title';

-- Using the created function
select name Full name ,title(name,sex) as call
from student WHERE name like '__';

The results are as follows:

Name and address
 Miss Zhen mi
 Miss mink
 Mr. Zuo Ci
 Mr. Yu Ji Yu
 Mr. Jiang Wei

[problem record]

In command mode, input represents the end of sql statement and immediate execution, and syntax error occurs when creating function

The solution is to replace the separator with other characters and change it back after execution

delimiter $$

create procedure gg()
begin
if exists(select column_name from information_schema.columns where  
table_schema='test' and table_name='t_user' andn column_name='point')
then
select 'tt';
end if;
end$$

delimiter ;

stored procedure

Create a stored procedure to add a primary key and a new column respectively, and delete the reconstruction if any

-- Determine whether a column exists
SELECT * FROM information_schema.columns 
WHERE table_name = 'test' 
AND column_name = 'id'
-- Determine whether there is a primary key
select * PrimaryNum 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t 
where t.TABLE_NAME ='test'

Stored procedure for creating primary key

-- The stored procedure implements the creation of a primary key. If there is a primary key, it is deleted and rebuilt
drop procedure if exists add_primary;
create procedure add_primary(t_name varchar(25),c_name varchar(25)) 
BEGIN
-- Determine whether there is a primary key. Delete if there is one
IF EXISTS(
select * 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t 
where t.TABLE_NAME = t_name
)
THEN            
      set @sqlStr = CONCAT('ALTER TABLE ',t_name,' drop primary key;');
            PREPARE stmt FROM @sqlStr;  
            EXECUTE stmt;
END if;
-- Add primary key
set @sqlStr = concat('alter table ',t_name,' add primary key(',c_name,')');
PREPARE stmt FROM @sqlStr;  
EXECUTE stmt;
END;

-- Call stored procedure
call add_primary('test','id');

Create stored procedure for new column

-- The stored procedure implementation adds a new column, and deletes the rebuild if it exists
drop procedure if exists add_collum;
create procedure add_collum(t_name varchar(25),c_name varchar(25),c_type varchar(50)) 
BEGIN
-- Determine whether there is a column, delete if there is one
IF EXISTS(
select * FROM information_schema.columns 
WHERE table_name = t_name 
AND column_name = c_name
)
THEN            
      set @sqlStr = CONCAT('ALTER TABLE ',t_name,' drop ',c_name);
            PREPARE stmt FROM @sqlStr;  
            EXECUTE stmt;
END if;
-- Add new column
set @sqlStr = concat('alter table ',t_name,' add ',c_name,' ',c_type);
PREPARE stmt FROM @sqlStr;  
EXECUTE stmt;
END;

-- Call stored procedure
call add_collum('test','topic','varchar(30)');

 

Tags: Database Stored Procedure SQL

Posted on Tue, 03 Dec 2019 05:54:44 -0500 by hesyar