| MySQL Command-Line |
| What | How | Example(s) |
| Running MySQL | mysql -uusername -ppassword | mysql -ucusack2RO -pegbdf5s |
| Importing | mysql -uusername -ppassword < filename | mysql -usomeDB -pblah < myNewDB.sql |
Dumping
(Saving) | mysqldump -uusername -ppassword database [tables] > filename | mysqldump -ume -pblah myDB > My.sql
mysqldump -ume -pblah myDB table1
table2 > my.sql |
|
|
| Common MySQL Column Types |
| Purpose | Data Type | Example |
| Integers | int(M) | int(5) |
| Floating-point (real) numbers | float(M,D) | float(12,3) |
| Double-precision floating-point | double(M,D) | double(20,3) |
| Dates and times | timestamp(M) | timestamp(8) (for YYYYMMDD)
timestamp(12) (for YYYYMMDDHHMMSS) |
| Fixed-length strings | char(M) | char(10) |
| Variable-length strings | varchar(M) | varchar(20) |
| A large amount of text | blob | blob |
| Values chosen from a list | enum('value1',value2',...) | enum('apples','oranges','bananas') |
|
| M is maximum to display, and D is precision to the right of the decimal. |
|
| MySQL Mathematical Functions |
| What | How |
| Count rows per group | COUNT(column | *) |
| Average value of group | AVG(column) |
| Minumum value of group | MIN(column) |
| Maximum value of group | MAX(column) |
| Sum values in a group | SUM(column) |
| Absolute value | abs(number) |
| Rounding numbers | round(number) |
| Largest integer not greater | floor(number) |
| Smallest integer not smaller | ceiling(number) |
| Square root | sqrt(number) |
| nth power | pow(base,exponent) |
| random number n, 0<n < 1 | rand() |
| sin (similar cos, etc.) | sin(number) |
|
| MySQL String Functions |
| What | How |
| Compare strings | strcmp(string1,string2) |
| Convert to lower case | lower(string) |
| Convert to upper case | upper(string) |
| Left-trim whitespace (similar right) | ltrim(string) |
| Substring of string | substring(string,index1,index2) |
| Encrypt password | password(string) |
| Encode string | encode(string,key) |
| Decode string | decode(string,key) |
| Get date | curdate() |
| Get time | curtime() |
| Extract day name from date string | dayname(string) |
| Extract day number from date string | dayofweek(string) |
| Extract month from date string | monthname(string) |
|
| Basic MySQL Commands |
| What | How | Example(s) |
| List all databases | SHOW DATABASES; | SHOW DATABASES; |
| Create database | CREATE DATABASE database; | CREATE DATABASE PhoneDB; |
| Use a database | USE database; | USE PhonDB; |
| List tables in the database | SHOW TABLES; | SHOW TABLES; |
| Show the structure of a table | DESCRIBE table;
SHOW COLUMNS FROM table; | DESCRIBE Animals;
SHOW COLUMNS FROM Animals; |
| Delete a database (Careful!) | DROP DATABASE database; | DROP DATABASE PhoneDB; |
|
| SQL Commands: Modifying |
| What | How | Example(s) |
| Create table | CREATE TABLE table (
column1 type [[NOT] NULL]
[AUTO_INCREMENT],
column2 type [[NOT] NULL]
[AUTO_INCREMENT],
...
other options,
PRIMARY KEY (column(s)) ); | CREATE TABLE Students (
LastName varchar(30) NOT NULL,
FirstName varchar(30) NOT NULL,
StudentID int NOT NULL,
Major varchar(20),
Dorm varchar(20),
PRIMARY KEY (StudentID) ); |
| Insert data | INSERT INTO table VALUES
(list of values);
INSERT INTO table SET
column1=value1,
column2=value2,
...
columnk=valuek;
INSERT INTO table (column1,column2,...)
VALUES (value1,value2...); | INSERT INTO Students VALUES
('Smith','John',123456789,'Math','Selleck');
INSERT INTO Students SET
FirstName='John',
LastName='Smith',
StudentID=123456789,
Major='Math';
INSERT INTO Students
(StudentID,FirstName,LastName)
VALUES (123456789,'John','Smith'); |
| Insert/Select | INSERT INTO table (column1,column2,...)
SELECT statement;
(See below) | INSERT INTO Students
(StudentID,FirstName,LastName)
SELECT StudentID,FirstName,LastName
FROM OtherStudentTable;
WHERE LastName like '%son'; |
| Delete data | DELETE FROM table
[WHERE condition(s)];
(Omit WHERE to delete all data) | DELETE FROM Students
WHERE LastName='Smith';
DELETE FROM Students
WHERE LastName like '%Smith%';
AND FirstName='John';
DELETE FROM Students; |
| Updating Data | UPDATE table SET
column1=value1,
column2=value2,
...
columnk=valuek
[WHERE condition(s)]; | UPDATE Students SET
LastName='Jones' WHERE
StudentID=987654321;
UPDATE Students SET
LastName='Jones', Major='Theatre'
WHERE StudentID=987654321 OR
(MAJOR='Art' AND FirstName='Pete'); |
| Insert column | ALTER TABLE table ADD COLUMN
column type options; | ALTER TABLE Students ADD COLUMN
Hometown varchar(20); |
| Delete column | ALTER TABLE table
DROP COLUMN column; | ALTER TABLE Students
DROP COLUMN Dorm; |
| Delete table (Careful!) | DROP TABLE [IF EXISTS] table; | DROP TABLE Animals; |
|
| SQL Commands: Querying |
| What | How | Example(s) |
| All columns | SELECT * FROM table; | SELECT * FROM Students; |
| Some columns | SELECT column1,column2,... FROM table; | SELECT LastName, FirstName FROM Students; |
Some rows/
columns | SELECT column1,column2,...
FROM table
[WHERE condition(s)]; | SELECT LastName,FirstName
FROM Students
WHERE StudentID LIKE '%123%'; |
| No Repeats | SELECT [DISTINCT] column(s)
FROM table; | SELECT DISTINCT LastName
FROM Students; |
| Ordering | SELECT column1,column2,...
FROM table
[ORDER BY column(s) [DESC]]; | SELECT LastName,FirstName
FROM Students
ORDER BY LastName, FirstName DESC; |
Column
Aliases | SELECT column1 [AS alias1],
column2 [AS alias2], ...
FROM table1; | SELECT LastName,FirstName AS First
FROM Students; |
| Grouping | SELECT column1,column2,...
FROM table
[GROUP BY column(s)]; | SELECT LastName,COUNT(*)
FROM Students
GROUP BY LastName; |
| Group Filtering | SELECT column1,column2,...
FROM table
[GROUP BY column(s)]
[HAVING condition(s)]; | SELECT LastName,COUNT(*)
FROM Students
GROUP BY LastName
HAVING LastName like '%son'; |
| Joins | SELECT column1,column2,...
FROM table1,table2,...
[WHERE condition(s)]; | SELECT LastName,Points
FROM Students,Assignments
WHERE AssignmentID=12 AND
Students.StudentID=Assignments.StudentID; |
Table
Aliases | SELECT column1,column2,...
FROM table1 [alias1],
table2 [alias2],...
[WHERE condition(s)]; | SELECT LastName,Points
FROM Students S,Assignments A
WHERE S.StudentID=A.StudentID AND
A.AssignmentID=12; |
| Everything | SELECT [DISTINCT]
column1 [AS alias1],
column2 [AS alias2], ...
FROM table1 [alias1],
table2 [alias2],...
[WHERE condition(s)]
[GROUP BY column(s)]
[HAVING condition(s)]
[ORDER BY column(s) [DESC]]; | SELECT Points, COUNT(*) AS Cnt
FROM Students S,Assignments A
WHERE S.StudentID=A.StudentID AND
A.AssignmentID=12
GROUP BY Points
HAVING Points > 10
ORDER BY Cnt, Points DES |
|
Create DB:
CREATE DATABASE `database_name` CHARACTER SET utf8 COLLATE utf8_general_ci;
Create new user all privilege
CREATE USER 'user'@'host' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON * . * TO 'user'@'host';
FLUSH PRIVILEGES;
Create new user some privilege
CREATE USER 'user'@'host' IDENTIFIED BY 'PASSWORD';
GRANT ALTER,CREATE,DELETE,INSERT,INDEX,SELECT,UPDATE ON * . * TO 'user'@'host';
FLUSH PRIVILEGES;
GRANT PRIVILEGES
GRANT SELECT ON database.* TO user@'localhost';
If you intend to create a brand new user, then run this:
GRANT SELECT ON database.* TO user@'localhost' IDENTIFIED BY 'password';
To enable more options, you would separate them with a comma. So to enable SELECT, INSERT, and DELETE your syntax would look like this:
GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';
Once you have given the desired privileges for your user, you will need to run this command within the MySQL command prompt:
FLUSH PRIVILEGES;
To see a list of the privileges that have been granted to a specific user:
select * from mysql.user where User='username';
This is a list of privileges that you can grant:
| Privilege | Meaning |
| ALL [PRIVILEGES] | Sets all simple privileges except GRANT OPTION |
| ALTER | Enables use of ALTER TABLE |
| CREATE | Enables use of CREATE TABLE |
| CREATE TEMPORARY TABLES | Enables use of CREATE TEMPORARY TABLE |
| DELETE | Enables use of DELETE |
| DROP | Enables use of DROP TABLE |
| EXECUTE | Not implemented |
| FILE | Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE |
| INDEX | Enables use of CREATE INDEX and DROP INDEX |
| INSERT | Enables use of INSERT |
| LOCK TABLES | Enables use of LOCK TABLES on tables for which you have the SELECT privilege |
| PROCESS | Enables the user to see all processes with SHOW PROCESSLIST |
| REFERENCES | Not implemented |
| RELOAD | Enables use of FLUSH |
| REPLICATION CLIENT | Enables the user to ask where slave or master servers are |
| REPLICATION SLAVE | Needed for replication slaves (to read binary log events from the master) |
| SELECT | Enables use of SELECT |
| SHOW DATABASES | SHOW DATABASES shows all databases |
| SHUTDOWN | Enables use of MySQLadmin shutdown |
| SUPER | Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached |
| UPDATE | Enables use of UPDATE |
| USAGE | Synonym for privileges |
| GRANT OPTION | Enables privileges to be granted |
mysql -u your_user your_database <c:\path\to\your_script.sql >stdout_output.txt 2>stderr_output.txt
a
Không có nhận xét nào:
Đăng nhận xét