daily pastebin goal
36%
SHARE
TWEET

Untitled

a guest May 17th, 2018 76 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
Top