daily pastebin goal
42%
SHARE
TWEET

Untitled

a guest May 17th, 2018 83 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Common Commands
  2.  
  3. - SELECT - extracts data from a database
  4. - UPDATE - updates data in a database
  5. - DELETE - deletes data from a database
  6. - INSERT INTO - inserts new data into a database
  7. - CREATE DATABASE - creates a new database
  8. - ALTER DATABASE - modifies a database
  9. - CREATE TABLE - creates a new table
  10. - ALTER TABLE - modifies a table
  11. - DROP TABLE - d
  12.   eletes a table
  13. - CREATE INDEX - creates an index (search key)
  14. - DROP INDEX - deletes an index
  15.  
  16.  
  17.  
  18. Select
  19.  
  20.     SELECT * FROM tablename;
  21.     SELECT colum_name, colum_name FROM tablename;
  22.     SELECT DISTINCT City FROM Customers;
  23.  
  24.  
  25.  
  26. Where
  27.  
  28.     SELECT column_name,column_name
  29.     FROM table_name
  30.     WHERE column_name operator value;
  31.  
  32.  
  33.  
  34. And
  35.  
  36.     SELECT * FROM Customers
  37.     WHERE Country='Germany'
  38.     AND City='Berlin';
  39.  
  40.  
  41.  
  42. Order by
  43.  
  44.     SELECT column_name, column_name
  45.     FROM table_name
  46.     ORDER BY column_name ASC|DESC, column_name ASC|DESC;
  47.  
  48.  
  49.  
  50. Insert into
  51.  
  52. The first form does not specify the column names where the data will be inserted, only their values:
  53.  
  54.     INSERT INTO table_name
  55.     VALUES (value1,value2,value3,...);
  56.  
  57. The second form specifies both the column names and the values to be inserted:
  58.  
  59.     INSERT INTO table_name (column1,column2,column3,...)
  60.     VALUES (value1,value2,value3,...);
  61.  
  62.  
  63.  
  64. Update
  65.  
  66.     UPDATE Customers
  67.     SET City='Hamburg'
  68.     WHERE CustomerID=1;
  69.  
  70.     --multiple colums
  71.     UPDATE Customers
  72.     SET City='Hamburg',Country='China'
  73.     WHERE CustomerID=1;
  74.  
  75.     --multiple records
  76.     UPDATE Customers
  77.     SET City='Beijing'
  78.     WHERE Country='China';
  79.  
  80.  
  81.  
  82. Delete
  83.  
  84.     DELETE FROM Customers
  85.     WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
  86.  
  87.  
  88.  
  89. Sql Injection
  90.  
  91. The code on the server :
  92.  
  93.     txtUserId = getRequestString("UserId");
  94.     txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
  95.  
  96. if your input is
  97.  
  98.     '' or ''=''
  99.  
  100. will return the whole table...
  101.  
  102.  
  103.  
  104. Like
  105.  
  106.     SELECT * FROM Customers
  107.     WHERE Country LIKE '%land%';
  108.  
  109. the pattern after LIKE is called Wildcards.
  110.  
  111. You can also write NOT LIKE
  112.  
  113.     SELECT * FROM Customers
  114.     WHERE Country NOT LIKE '%land%';
  115.  
  116.  
  117.  
  118. Wildcards
  119.  
  120.   Wildcard                  Description                            
  121.   %                         A substitute for zero or more characters
  122.   _                         A substitute for a single character    
  123.   [charlist]                Sets and ranges of characters to match  
  124.   [^charlist]or[!charlist]  Matches only a character NOT specified within the brackets
  125.  
  126. Examples:
  127.  
  128.     SELECT * FROM Customers
  129.     WHERE City LIKE '[bsp]%';
  130.    
  131.     SELECT * FROM Customers
  132.     WHERE City LIKE '[a-z]%';
  133.    
  134.     SELECT * FROM Customers
  135.     WHERE City LIKE '_erli_';
  136.    
  137.     --note that sql is not case sensitive, the b% will lead to all results begin with b or B.
  138.     SELECT TOP 3 * FROM Customers
  139.     WHERE City LIKE 'b%';
  140.  
  141.  
  142.  
  143. IN
  144.  
  145.     SELECT * FROM Customers
  146.     WHERE City IN ('Paris','London');
  147.    
  148.     --note that the code above is just the same as:
  149.     SELECT * FROM Customers
  150.     WHERE City ='Paris' or City='London';
  151.  
  152.  
  153.  
  154. Between
  155.  
  156.     SELECT * FROM Products
  157.     WHERE ProductName BETWEEN 'C' AND 'M';
  158.    
  159.     SELECT * FROM Products
  160.     WHERE ProductID BETWEEN 1 AND 10;
  161.  
  162.  
  163.  
  164. Alias
  165.  
  166.     SELECT o.OrderID, o.OrderDate, c.CustomerName
  167.     FROM Customers AS c, Orders AS o
  168.     WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
  169.  
  170.  
  171.  
  172. MYSQL
  173.  
  174.     #run the .sql script from command line.
  175.     mysql -u root -p RUNOOB < new.sql
  176.    
  177.     login in the myql:
  178.     mysql -u root -p
  179.     pwd 123456
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top