SHARE
TWEET

Untitled

a guest Sep 22nd, 2019 88 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # MySQL code sheet
  2.  
  3. ## Show Users
  4.  
  5. ```sql
  6. SELECT User, Host FROM mysql.user;
  7. ```
  8.  
  9. ## Create User
  10.  
  11. ```sql
  12. CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';
  13. ```
  14.  
  15. ## Grant All Priveleges On All Databases
  16.  
  17. ```sql
  18. GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';
  19. FLUSH PRIVILEGES;
  20. ```
  21.  
  22. ## Show Grants
  23.  
  24. ```sql
  25. SHOW GRANTS FOR 'someuser'@'localhost';
  26. ```
  27.  
  28. ## Remove Grants
  29.  
  30. ```sql
  31. REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'localhost';
  32. ```
  33.  
  34. ## Delete User
  35.  
  36. ```sql
  37. DROP USER 'someuser'@'localhost';
  38. ```
  39.  
  40. ## Exit
  41.  
  42. ```sql
  43. exit;
  44. ```
  45.  
  46. ## Show Databases
  47.  
  48. ```sql
  49. SHOW DATABASES
  50. ```
  51.  
  52. ## Create Database
  53.  
  54. ```sql
  55. CREATE DATABASE acme;
  56. ```
  57.  
  58. ## Delete Database
  59.  
  60. ```sql
  61. DROP DATABASE acme;
  62. ```
  63.  
  64. ## Select Database
  65.  
  66. ```sql
  67. USE acme;
  68. ```
  69.  
  70. ## Create Table
  71.  
  72. ```sql
  73. CREATE TABLE users(
  74. id INT AUTO_INCREMENT,
  75.    first_name VARCHAR(100),
  76.    last_name VARCHAR(100),
  77.    email VARCHAR(50),
  78.    password VARCHAR(20),
  79.    location VARCHAR(100),
  80.    dept VARCHAR(100),
  81.    is_admin TINYINT(1),
  82.    register_date DATETIME,
  83.    PRIMARY KEY(id)
  84. );
  85. ```
  86.  
  87. ## Delete / Drop Table
  88.  
  89. ```sql
  90. DROP TABLE tablename;
  91. ```
  92.  
  93. ## Show Tables
  94.  
  95. ```sql
  96. SHOW TABLES;
  97. ```
  98.  
  99. ## Insert Row / Record
  100.  
  101. ```sql
  102. INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('Brad', 'Traversy', 'brad@gmail.com', '123456','Massachusetts', 'development', 1, now());
  103. ```
  104.  
  105. ## Insert Multiple Rows
  106.  
  107. ```sql
  108. INSERT INTO users (first_name, last_name, email, password, location, dept,  is_admin, register_date) values ('Fred', 'Smith', 'fred@gmail.com', '123456', 'New York', 'design', 0, now()), ('Sara', 'Watson', 'sara@gmail.com', '123456', 'New York', 'design', 0, now()),('Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, now()),('Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, now()),('Tom', 'Spears', 'tom@yahoo.com', '123456', 'Massachusetts', 'sales', 0, now());
  109. ```
  110.  
  111. ## Select
  112.  
  113. ```sql
  114. SELECT * FROM users;
  115. SELECT first_name, last_name FROM users;
  116. ```
  117.  
  118. ## Where Clause
  119.  
  120. ```sql
  121. SELECT * FROM users WHERE location='Massachusetts';
  122. SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';
  123. SELECT * FROM users WHERE is_admin = 1;
  124. SELECT * FROM users WHERE is_admin > 0;
  125. ```
  126.  
  127. ## Delete Row
  128.  
  129. ```sql
  130. DELETE FROM users WHERE id = 6;
  131. ```
  132.  
  133. ## Update Row
  134.  
  135. ```sql
  136. UPDATE users SET email = 'freddy@gmail.com' WHERE id = 2;
  137. ```
  138.  
  139. ## Add New Column
  140.  
  141. ```sql
  142. ALTER TABLE users ADD age VARCHAR(3);
  143. ```
  144.  
  145. ## Modify Column
  146.  
  147. ```sql
  148. ALTER TABLE users MODIFY COLUMN age INT(3);
  149. ```
  150.  
  151. ## Order By (Sort)
  152.  
  153. ```sql
  154. SELECT * FROM users ORDER BY last_name ASC;
  155. SELECT * FROM users ORDER BY last_name DESC;
  156. ```
  157.  
  158. ## Concatenate Columns
  159.  
  160. ```sql
  161. SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;
  162. ```
  163.  
  164. ## Select Distinct Rows
  165.  
  166. ```sql
  167. SELECT DISTINCT location FROM users;
  168. ```
  169.  
  170. ## Between (Select Range)
  171.  
  172. ```sql
  173. SELECT * FROM users WHERE age BETWEEN 20 AND 25;
  174. ```
  175.  
  176. ## Like (Searching)
  177.  
  178. ```sql
  179. SELECT * FROM users WHERE dept LIKE 'd%';
  180. SELECT * FROM users WHERE dept LIKE 'dev%';
  181. SELECT * FROM users WHERE dept LIKE '%t';
  182. SELECT * FROM users WHERE dept LIKE '%e%';
  183. ```
  184.  
  185. ## Not Like
  186.  
  187. ```sql
  188. SELECT * FROM users WHERE dept NOT LIKE 'd%';
  189. ```
  190.  
  191. ## IN
  192.  
  193. ```sql
  194. SELECT * FROM users WHERE dept IN ('design', 'sales');
  195. ```
  196.  
  197. ## Create & Remove Index
  198.  
  199. ```sql
  200. CREATE INDEX LIndex On users(location);
  201. DROP INDEX LIndex ON users;
  202. ```
  203.  
  204. ## New Table With Foreign Key (Posts)
  205.  
  206. ```sql
  207. CREATE TABLE posts(
  208. id INT AUTO_INCREMENT,
  209.    user_id INT,
  210.    title VARCHAR(100),
  211.    body TEXT,
  212.    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  213.    PRIMARY KEY(id),
  214.    FOREIGN KEY (user_id) REFERENCES users(id)
  215. );
  216. ```
  217.  
  218. ## Add Data to Posts Table
  219.  
  220. ```sql
  221. INSERT INTO posts(user_id, title, body) VALUES (1, 'Post One', 'This is post one'),(3, 'Post Two', 'This is post two'),(1, 'Post Three', 'This is post three'),(2, 'Post Four', 'This is post four'),(5, 'Post Five', 'This is post five'),(4, 'Post Six', 'This is post six'),(2, 'Post Seven', 'This is post seven'),(1, 'Post Eight', 'This is post eight'),(3, 'Post Nine', 'This is post none'),(4, 'Post Ten', 'This is post ten');
  222. ```
  223.  
  224. ## INNER JOIN
  225.  
  226. ```sql
  227. SELECT
  228.   users.first_name,
  229.   users.last_name,
  230.   posts.title,
  231.   posts.publish_date
  232. FROM users
  233. INNER JOIN posts
  234. ON users.id = posts.user_id
  235. ORDER BY posts.title;
  236. ```
  237.  
  238. ## New Table With 2 Foriegn Keys
  239.  
  240. ```sql
  241. CREATE TABLE comments(
  242.     id INT AUTO_INCREMENT,
  243.     post_id INT,
  244.     user_id INT,
  245.     body TEXT,
  246.     publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  247.     PRIMARY KEY(id),
  248.     FOREIGN KEY(user_id) references users(id),
  249.     FOREIGN KEY(post_id) references posts(id)
  250. );
  251. ```
  252.  
  253. ## Add Data to Comments Table
  254.  
  255. ```sql
  256. INSERT INTO comments(post_id, user_id, body) VALUES (1, 3, 'This is comment one'),(2, 1, 'This is comment two'),(5, 3, 'This is comment three'),(2, 4, 'This is comment four'),(1, 2, 'This is comment five'),(3, 1, 'This is comment six'),(3, 2, 'This is comment six'),(5, 4, 'This is comment seven'),(2, 3, 'This is comment seven');
  257. ```
  258.  
  259. ## Left Join
  260.  
  261. ```sql
  262. SELECT
  263. comments.body,
  264. posts.title
  265. FROM comments
  266. LEFT JOIN posts ON posts.id = comments.post_id
  267. ORDER BY posts.title;
  268. ```
  269.  
  270. ## Join Multiple Tables
  271.  
  272. ```sql
  273. SELECT
  274. comments.body,
  275. posts.title,
  276. users.first_name,
  277. users.last_name
  278. FROM comments
  279. INNER JOIN posts on posts.id = comments.post_id
  280. INNER JOIN users on users.id = comments.user_id
  281. ORDER BY posts.title;
  282. ```
  283.  
  284. ## Aggregate Functions
  285.  
  286. ```sql
  287. SELECT COUNT(id) FROM users;
  288. SELECT MAX(age) FROM users;
  289. SELECT MIN(age) FROM users;
  290. SELECT SUM(age) FROM users;
  291. SELECT UCASE(first_name), LCASE(last_name) FROM users;
  292. ```
  293.  
  294. ## Group By
  295.  
  296. ```sql
  297. SELECT age, COUNT(age) FROM users GROUP BY age;
  298. SELECT age, COUNT(age) FROM users WHERE age > 20 GROUP BY age;
  299. SELECT age, COUNT(age) FROM users GROUP BY age HAVING count(age) >=2;
  300. ```
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