YMvwtptG

11. Log into mysql:
2	mysql -u [username] -p;(will prompt for password)
3  
4  data types (INTEGER,FLOAT, DECIMAL(i, j), CHAR(n), VARCHAR(n))
5  DATE:
6  • Made up of year-month-day in the format yyyy-mm-dd
7  TIME:
8  • Made up of hour:minute:second in the format hh:mm:ss
9  TIME(i):
10  • Made up of hour:minute:second plus i additional digits specifying fractions of a second
11  • format is hh:mm:ss:ii...i
12  TIMESTAMP:
13  • Has both DATE and TIME components
14  
152. Show all databases:
16	SHOW DATABASES;
17  
183. Access database:
19	mysql -u [username] -p [database](will prompt for password)
20  
214. Create new database:
22	CREATE DATABASE [database_name];
23      An error occurs if database exists, Check if the database is existing
24      DROP DATABASE IF EXISTS [database_name];
25      CREATE DATABASE [database_name];
26      
27      CREATE DATABASE IF NOT EXISTS Department;
28  
295. Select database:
30	USE [database_name];
31  
326. Determine what database is in use:
33	 SELECT DATABASE();
34  
357. Show all tables:
36	 SHOW TABLES;
37  
388. Show table structure:
39	 DESCRIBE [table_name];
40  
419. List all indexes on a table:
42	show index from [table];
43  
4410. Create new table with columns:
45    CREATE TABLE DEPARTMENT             
46  ( DNAME VARCHAR(10) NOT NULL,
47    DNUMBER INTEGER NOT NULL,
48    MGRSSN CHAR(9),
49    MGRSTARTDATE CHAR(9) );
50
5111. Adding a column:
52	ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);
53    The new attribute will have NULLs in all the tuples of the relation right after the command is executed; hence, the
54    NOT NULL constraint is not allowed for such an attribute
55
5612. Adding a column with an unique, auto-incrementing ID:
57	ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT 	PRIMARY KEY;
58
5913. Inserting a record:
60  (Insert a tuple for a new EMPLOYEE for whom we only know the FNAME, LNAME, and SSN attributes)
61	INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)
62  VALUES ('Richard', 'Marini', '653298653')
63  
64  (Attribute values should be listed in the same order)
65  INSERT INTO EMPLOYEE
66  VALUES ('Richard','K','Marini', '653298653','30-DEC-92', '98 Oak Forest, Katy,TX', 'M',37000,'987654321', 4);
67
6814. MySQL function for datetime input:
69	NOW()
70
7115. Selecting records:
72	SELECT * FROM [table_name];
73  
74  SELECT <attribute list>
75  FROM <table list>
76  WHERE <condition>
77  
78  SELECT BDATE, ADDRESS
79  FROM EMPLOYEE
80  WHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith'
81
8216. Explain records:
83	EXPLAIN SELECT * FROM [table];
84
8517. Selecting parts of records:
86	SELECT [column], [another-column] FROM [table];
87
8818. Counting records:
89	SELECT COUNT([column]) FROM [table];
90
9119. Counting and selecting grouped records:
92	SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY 	[column];
93
9420. Selecting specific records:
95	SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)
96
9721. Select records containing [value]:
98	SELECT * FROM [table] WHERE [column] LIKE '%[value]%';
99
10022. Select records starting with [value]:
101	SELECT * FROM [table] WHERE [column] LIKE '[value]%';
102
10323. Select records starting with val and ending with ue:
104	SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';
105
10624. Select a range:
107	SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];
108
10925. Select with custom order and only limit:
110	SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT 	[value]; (Order: DESC, ASC)
111
11226. Updating records:
113	UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];
114  
115  UPDATE PROJECT
116  SET PLOCATION = ‘Galle', DNUM = 5
117  WHERE PNUMBER=10
118
11927. Deleting records:
120	DELETE FROM [table] WHERE [column] = [value];
121
12228. Delete all records from a table (without dropping the table itself):
123	DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns 	like an id column.)
124
12529. Delete all records without deleting schema in a table:
126	truncate table [table];
127
12830. Removing table columns:
129	ALTER TABLE [table] DROP COLUMN [column];
130
13131. Deleting tables:
132	DROP TABLE [table];
133
13432. Deleting databases:
135	DROP DATABASE [database];
136
13733. Custom column output names:
138	SELECT [column] AS [custom-column] FROM [table];
139
14034. Export a database dump :
141	mysqldump -u [username] -p [database] > db_backup.sql
142
14335. Use --lock-tables=false option for locked tables.
144
14536. Import a database dump:
146	mysql -u [username] -p -h localhost [database] < db_backup.sql
147
14837. Logout:	exit;
149
15038. To eliminate duplicate tuples in a query result
151  SELECT DISTINCT SALARY
152  FROM EMPLOYEE
153--------------------------------------------------------------------------------------------------
154Aggregate functions
155
1561. Select but without duplicates:
157	SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 	2015-01-01 00:00:00
158
1592. Calculate total number of records:
160	SELECT SUM([column]) FROM [table];
161
1623. Count total number of [column] and group by [category-column]:
163	SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];
164
1654. Get largest value in [column]:
166	SELECT MAX([column]) FROM [table];
167
1685. Get smallest value:
169	SELECT MIN([column]) FROM [table];
170
1716. Get average value:
172	SELECT AVG([column]) FROM [table];
173
1747. Get rounded average value and group by [category-column]:
175	SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY 	[category-column];
176  
177-----------------------------------------------------------------------------------------------
178Users functions
179
1801. List all users:
181	SELECT User,Host FROM mysql.user;
182
1832. Create new user:
184	CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
185
1863. Grant ALL access to user for * tables:
187	GRANT ALL ON database.* TO 'user'@'localhost';
188	
189-----------------------------------------------------------------------------------------------
190MySQL Mathematical Functions
191
1921. Count rows per group	COUNT(column | *)
193
1942. Average value of group	AVG(column)
195
1963. Minumum value of group	MIN(column)
197
1984. Maximum value of group	MAX(column)
199
2005. Sum values in a group	SUM(column)
201
2026. Absolute value	abs(number)
203
2047. Rounding numbers	round(number)
205
2068. Largest integer not greater	floor(number)
207
2089. Smallest integer not smaller	ceiling(number)
209
21010. Square root	sqrt(number)
211
21211. nth power	pow(base,exponent)
213
21412. random number n, 0<n < 1	rand()
215
21613. sin (similar cos, etc.)	sin(number)
217
218------------------------------------------------------------------------------------
219MySQL String Functions
220
2211. Compare strings	strcmp(string1,string2)
222
2232. Convert to lower case	lower(string)
224
2253. Convert to upper case	upper(string)
226
2274. Left-trim whitespace (similar right)	ltrim(string)
228
2295. Substring of string	substring(string,index1,index2)
230
2316. Encrypt password	password(string)
232
2337. Encode string	encode(string,key)
234
2358. Decode string	decode(string,key)
236
2379. Get date	curdate()
238
23910. Get time	curtime()
240
24111. Extract day name from date string	dayname(string)
242
24312. Extract day number from date string	dayofweek(string)
244
24513.Extract month from date string	monthname(string)
246
247----------------------------------------------------------------------------------------
248
249#reset root password
250
251sudo /etc/init.d/mysql stop
252sudo mysqld_safe --skip-grant-tables &
253mysql -u root
254mysql> use mysql;
255mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
256mysql> flush privileges;
257mysql> quit
258
259sudo /etc/init.d/mysql stop
260sudo /etc/init.d/mysql start