· 6 years ago · Aug 07, 2019, 11:32 PM
1---MySQL Command-Line
2
3What How Example(s)
4
5Running MySQL mysql -uusername -ppassword mysql -ucusack2RO -pegbdf5s
6
7Importing mysql -uusername -ppassword < filename mysql -usomeDB -pblah < myNewDB.sql
8
9Dumping(Saving) mysqldump -uusername -ppassword database [tables] > filename mysqldump -ume -pblah myDB > My.sql
10mysqldump -ume -pblah myDB table1
11 table2 > my.sql
12
13---Common MySQL Column Types
14
15Purpose Data Type Example
16
17Integers int(M) int(5)
18
19Floating-point (real) numbers float(M,D) float(12,3)
20
21Double-precision floating-point double(M,D) double(20,3)
22
23Dates and times timestamp(M) timestamp(8) (for YYYYMMDD)
24timestamp(12) (for YYYYMMDDHHMMSS)
25
26Fixed-length strings char(M) char(10)
27
28Variable-length strings varchar(M) varchar(20)
29
30A large amount of text blob blob
31
32Values chosen from a list enum('value1',value2',...)
33enum('apples','oranges','bananas')
34________________________________________
35M is maximum to display, and D is precision to the right of the decimal.
36
37MySQL Mathematical Functions
38What How
39Count rows per group COUNT(column | *)
40Average value of group AVG(column)
41Minumum value of group MIN(column)
42Maximum value of group MAX(column)
43Sum values in a group SUM(column)
44Absolute value abs(number)
45Rounding numbers round(number)
46Largest integer not greater floor(number)
47Smallest integer not smaller ceiling(number)
48Square root sqrt(number)
49nth power pow(base,exponent)
50random number n, 0<n < 1 rand()
51sin (similar cos, etc.) sin(number)
52 MySQL String Functions
53What How
54Compare strings strcmp(string1,string2)
55Convert to lower case lower(string)
56Convert to upper case upper(string)
57Left-trim whitespace (similar right) ltrim(string)
58Substring of string substring(string,index1,index2)
59Encrypt password password(string)
60Encode string encode(string,key)
61Decode string decode(string,key)
62Get date curdate()
63Get time curtime()
64Extract day name from date string dayname(string)
65Extract day number from date string dayofweek(string)
66Extract month from date string monthname(string)
67
68Basic MySQL Commands
69What How Example(s)
70List all databases SHOW DATABASES; SHOW DATABASES;
71Create database CREATE DATABASE database; CREATE DATABASE PhoneDB;
72Use a database USE database; USE PhonDB;
73List tables in the database SHOW TABLES; SHOW TABLES;
74Show the structure of a table DESCRIBE table;
75SHOW COLUMNS FROM table; DESCRIBE Animals;
76SHOW COLUMNS FROM Animals;
77Delete a database (Careful!) DROP DATABASE database; DROP DATABASE PhoneDB;
78
79SQL Commands: Modifying
80What How Example(s)
81Create table CREATE TABLE table (
82 column1 type [[NOT] NULL]
83 [AUTO_INCREMENT],
84 column2 type [[NOT] NULL]
85 [AUTO_INCREMENT],
86 ...
87 other options,
88 PRIMARY KEY (column(s)) ); CREATE TABLE Students (
89 LastName varchar(30) NOT NULL,
90 FirstName varchar(30) NOT NULL,
91 StudentID int NOT NULL,
92 Major varchar(20),
93 Dorm varchar(20),
94 PRIMARY KEY (StudentID) );
95Insert data INSERT INTO table VALUES
96 (list of values);
97INSERT INTO table SET
98 column1=value1,
99 column2=value2,
100 ...
101 columnk=valuek;
102INSERT INTO table (column1,column2,...)
103 VALUES (value1,value2...); INSERT INTO Students VALUES
104 ('Smith','John',123456789,'Math','Selleck');
105INSERT INTO Students SET
106 FirstName='John',
107 LastName='Smith',
108 StudentID=123456789,
109 Major='Math';
110INSERT INTO Students
111 (StudentID,FirstName,LastName)
112 VALUES (123456789,'John','Smith');
113Insert/Select INSERT INTO table (column1,column2,...)
114 SELECT statement;
115 (See below) INSERT INTO Students
116 (StudentID,FirstName,LastName)
117 SELECT StudentID,FirstName,LastName
118 FROM OtherStudentTable;
119 WHERE LastName like '%son';
120Delete data DELETE FROM table
121 [WHERE condition(s)];
122
123
124
125(Omit WHERE to delete all data) DELETE FROM Students
126 WHERE LastName='Smith';
127DELETE FROM Students
128 WHERE LastName like '%Smith%';
129 AND FirstName='John';
130DELETE FROM Students;
131Updating Data UPDATE table SET
132 column1=value1,
133 column2=value2,
134 ...
135 columnk=valuek
136 [WHERE condition(s)]; UPDATE Students SET
137 LastName='Jones' WHERE
138 StudentID=987654321;
139UPDATE Students SET
140 LastName='Jones', Major='Theatre'
141 WHERE StudentID=987654321 OR
142 (MAJOR='Art' AND FirstName='Pete');
143Insert column ALTER TABLE table ADD COLUMN
144 column type options; ALTER TABLE Students ADD COLUMN
145 Hometown varchar(20);
146Delete column ALTER TABLE table
147 DROP COLUMN column; ALTER TABLE Students
148 DROP COLUMN Dorm;
149Delete table (Careful!) DROP TABLE [IF EXISTS] table; DROP TABLE Animals;
150
151SQL Commands: Querying
152What How Example(s)
153All columns SELECT * FROM table; SELECT * FROM Students;
154Some columns SELECT column1,column2,... FROM table; SELECT LastName, FirstName FROM Students;
155Some rows/
156columns SELECT column1,column2,...
157 FROM table
158 [WHERE condition(s)]; SELECT LastName,FirstName
159 FROM Students
160 WHERE StudentID LIKE '%123%';
161No Repeats SELECT [DISTINCT] column(s)
162 FROM table; SELECT DISTINCT LastName
163 FROM Students;
164Ordering SELECT column1,column2,...
165 FROM table
166 [ORDER BY column(s) [DESC]]; SELECT LastName,FirstName
167 FROM Students
168 ORDER BY LastName, FirstName DESC;
169Column
170Aliases SELECT column1 [AS alias1],
171 column2 [AS alias2], ...
172 FROM table1; SELECT LastName,FirstName AS First
173 FROM Students;
174Grouping SELECT column1,column2,...
175 FROM table
176 [GROUP BY column(s)]; SELECT LastName,COUNT(*)
177 FROM Students
178 GROUP BY LastName;
179Group Filtering SELECT column1,column2,...
180 FROM table
181 [GROUP BY column(s)]
182 [HAVING condition(s)]; SELECT LastName,COUNT(*)
183 FROM Students
184 GROUP BY LastName
185 HAVING LastName like '%son';
186Joins SELECT column1,column2,...
187 FROM table1,table2,...
188 [WHERE condition(s)]; SELECT LastName,Points
189 FROM Students,Assignments
190 WHERE AssignmentID=12 AND
191 Students.StudentID=Assignments.StudentID;
192Table
193Aliases SELECT column1,column2,...
194 FROM table1 [alias1],
195 table2 [alias2],...
196 [WHERE condition(s)]; SELECT LastName,Points
197 FROM Students S,Assignments A
198 WHERE S.StudentID=A.StudentID AND
199 A.AssignmentID=12;
200Everything SELECT [DISTINCT]
201 column1 [AS alias1],
202 column2 [AS alias2], ...
203 FROM table1 [alias1],
204 table2 [alias2],...
205 [WHERE condition(s)]
206 [GROUP BY column(s)]
207 [HAVING condition(s)]
208 [ORDER BY column(s) [DESC]]; SELECT Points, COUNT(*) AS Cnt
209 FROM Students S,Assignments A
210 WHERE S.StudentID=A.StudentID AND
211 A.AssignmentID=12
212 GROUP BY Points
213 HAVING Points > 10
214 ORDER BY Cnt, Points DESC;