Created
April 23, 2012 14:26
-
-
Save i-stos/2471240 to your computer and use it in GitHub Desktop.
MySQL: Basic MySQL Guide
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
========================================================================================================== | |
MySQL Guide - Basics | |
========================================================================================================== | |
Connect/Disconnect from MySQL Server | |
---------------------------------------------------------------------------------------------------------- | |
:~ sudo mysqld_safe //Turns on MySQL server | |
:~ mysql -h host -u root -p //Connects to MySQL server: "no need to specify host on local" | |
mysql> quit; //Disconnects from MySQL | |
:~ mysqladmin -u root -p shutdown //Shuts down MySQL server | |
---------------------------------------------------------------------------------------------------------- | |
========================================================================================================== | |
General commands | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SHOW DATABASES; //Shows databases for current user | |
mysql> CREATE DATABASE testDB; //Creates new database called testDB | |
mysql> USE testDB; //Changes database | |
mysql> SHOW TABLES; //Shows all tables in the current database | |
mysql> DESCRIBE costumer; //Displays table information | |
mysql> SELECT * FROM costumer; //Shows all rows and columns of the "costumer" table | |
mysql> SELECT DATABASE(); //Shows current database | |
---------------------------------------------------------------------------------------------------------- | |
mysql> CREATE TABLE test_table ( //Creates a... | |
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, //new... | |
-> title VARCHAR(128) NOT NULL, //table... | |
-> text TEXT NOT NULL, //with the... | |
-> date DATE NOT NULL, //specified column names... | |
-> author VARCHAR(40) //and their data types. | |
-> ); | |
---------------------------------------------------------------------------------------------------------- | |
mysql> INSERT INTO test_table (title, text, date, author) //Inserts a single row to "test_table" | |
-> | |
-> VALUES ('My title', 'My description', CURRENT_DATE, 'Billy'); | |
---------------------------------------------------------------------------------------------------------- | |
mysql> GRANT ALL ON testDB.* TO 'some_user'@'client_host'; //Grants permissions to a user for "testDB" | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SELECT user(); //Shows current logged in user | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SELECT | |
-> user() | |
-> \c //Cancels the command | |
---------------------------------------------------------------------------------------------------------- | |
========================================================================================================== | |
SELECT | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SELECT * FROM costumers WHERE name = 'John'; //Find specific value | |
mysql> SELECT name FROM costumer; //Select a specific column | |
mysql> SELECT name, lastname FROM costumer; //Select multiple columns | |
mysql> SELECT * FROM costumer ORDER BY date; //Sort rows by the date column | |
mysql> SELECT DISTINCT name FROM costumer; //Find only distinct values, no duplicates | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SELECT * FROM costumer WHERE (news_letter = 'Y') //Select when both conditions are met | |
-> AND (order_date >= '2012-01-14'); | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SELECT * FROM costumers WHERE sex='m' AND news_letter='Y' //Selects when both conditions... | |
-> ORDER BY name DESC; //are met, and sorts in... | |
//descending order by name. | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SELECT * FROM costumers WHERE sex='m' AND news_letter='Y' //Select when all 3 conditions... | |
-> AND order_date > '2012-01-12' ORDER BY name ASC; //are met, and sorts in ascending... | |
//order by name. | |
---------------------------------------------------------------------------------------------------------- | |
mysql> SELECT COUNT(*) FROM costumers; //Counts numbers of rows in "costumers" table | |
mysql> SELECT * FROM costumers WHERE name LIKE 'b%'; //Select all names that start with the letter 'b' | |
mysql> SELECT * FROM costumers WHERE name LIKE '%n'; //Select all names that end with the letter 'h' | |
mysql> SELECT * FROM costumers WHERE name LIKE '%i%'; //Select all names that contain the letter 'i' | |
mysql> SELECT * FROM costumers WHERE name LIKE '____'; //Select all names that contain exactly 4 characters | |
---------------------------------------------------------------------------------------------------------- | |
========================================================================================================== | |
ALTER TABLE mytable | |
---------------------------------------------------------------------------------------------------------- | |
mysql> ALTER TABLE costumer RENAME costumers; //Rename table to "costumers" | |
mysql> ALTER TABLE costumer RENAME TO costumers; //Alternative syntax for the same task | |
---------------------------------------------------------------------------------------------------------- | |
mysql> ALTER TABLE costumers CHANGE birth_date birth DATE; //Rename a column (data type is required) | |
mysql> ALTER TABLE costumers MODIFY order_date DATE NOT NULL; //Modify a column's data type to... DATE NOT NULL | |
mysql> ALTER TABLE costumers ADD age INT; //Add a new column as integer type | |
mysql> ALTER TABLE costumers DROP COLUMN ip; //Remove an entire column | |
---------------------------------------------------------------------------------------------------------- | |
========================================================================================================== | |
UPDATE mytable | |
---------------------------------------------------------------------------------------------------------- | |
mysql> UPDATE costumers SET news_letter='Y' WHERE name='Billy'; //Change column's value if condition is met | |
mysql> UPDATE costumer SET sex='m', news_letter='N' WHERE name='Billy'; //Change multiple column values... | |
//when condition is met. | |
---------------------------------------------------------------------------------------------------------- | |
mysql> UPDATE costumers SET age=YEAR(CURDATE()) - YEAR(birth_date); | |
//Set the age column on each row with SQL functions YEAR() and CURDATE() | |
---------------------------------------------------------------------------------------------------------- | |
========================================================================================================== | |
DELETE FROM mytable -> 'this would delete everything' | |
---------------------------------------------------------------------------------------------------------- | |
mysql> DELETE FROM costumers WHERE id=4; //Remove the 4th row in the table | |
---------------------------------------------------------------------------------------------------------- | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment