Database language records

1. query the log records of one hundred tables. Implementation Using Stored Procedures and cursors

Declare @ tablename varchar (32)
Declare @ SQL varchar (8000)
-- Set @ SQL = 'select * From user_table1'
Declare mycursor cursor for -- Define a cursor
Select distinct (table_name) from frank_test.information_schema.columns -- where table_name <> 'user _ table1'
Open mycursor; -- open the cursor.
Fetch next from mycursor into @ tablename -- remove cursor for operation
While @ fetch_status = 0
Begin
Exec ('select * from' + @ tablename) -- Note that a space is added to the side of the single quotation mark before and after the plus sign.
-- Exec ('select * From user_table1 Union all select * from'
+ @ Tablename)
-- Set @ SQL = @ SQL + 'Union all select * from' + @ tablename
Fetch next from mycursor into @ tablename
End
-- Exec (@ SQL)
Close mycursor; -- close the cursor
Deallocate mycursor -- release cursor

Ii. sql2005 language. A sequence is added before the search results.

Select row_number () over (order by user_uname DESC) as xunhao ,*
From user_table1

 

3. insert data into the table cyclically

Declare @ I int
Set @ I = 1
While @ I <1000000
Begin
Insert into info values ('Justin '+ STR (@ I), 'shenzhen' + STR (@ I ))
Set @ I = @ I + 1
End

Drop table if exists 'ratingvod ';
Create Table 'ratingvod '(
'Cid' varchar (16) default null,
'Uid' varchar (16) default null,
'Rating' varchar (16) default null,
'Feedback _ time' varchar (16) default null,
'Portal _ id' varchar (255) default null,
'Portal _ type' varchar (16) default null,
'Terminal _ type' varchar (30) default null,
'Terminal _ OS 'varchar (30) default null,
'Location' varchar (30) default null,
'Token' varchar (30) default null,
'Channel' varchar (30) default null,
'Schedule _ Code' varchar (30) default null,
'Adv _ Code' varchar (30) default null
-- Primary key ('cid ')
) Engine = InnoDB default charset = utf8;
-- Datetime int (12)

Delete from ratingvod;

Drop procedure if exists dowhile;
Create procedure dowhile ()
Begin
Declare I int default 1;
Declare J int default 1;
Start transaction;
While I & lt; 101 do
While J & lt; 101 do
Insert into ratingvod (CID, uid, rating, feedback_time, portal_id, portal_type, terminal_type, terminal_ OS, location, Token, channel, schedule_code, adv_code)
Values (Concat ('vod ', I), Concat ('user', I), I % 5 + 1, '20170101', 'portal _ id1 ', 'portal _ type1', 'stb', 'android', 'location1', 'unknow: unknow: unknow', 'v1 V1 ', 'scode0', 'acode0 ');

Set J = J + 1;
End while;
Set I = I + 1;
Set j = 1;
End while;
Commit;
End;

Delimiter;

Call dowhile ();

-- Select count (CID) from ratingvod;
Select * From ratingvod;

Insert into tablefortest (a, B) values (1, 2), (3, 4), (5, 6)

If an organization is created in the user's organizational structure, how many users can be added to each organization? Thinking ......