MySQL Basics
Connecting to a database
1. mysql -h host -u user -p
2. password
3. connect database_name
Some Useful Commands
- SHOW DATABASES; - show all existing databases
- CREATE DATABASE new-database-name; - create a new database
- SHOW TABLES; - show all table in the database
- DESCRIBE table; - give table details
- CREATE TABLE tablename (field1 data VARCHAR(100) field2 number(20)….); - create a table
- ALTER TABLE tablename ADD field VARCHAR(4) AFTER field2; - Add a new column to a table called field which has variable characters up to 4 charcaters after existing column field2
- SELECT fieldname1,fieldname2 FROM table WHERE fieldname1 LIKE 'pattern' AND fieldname2 = 'whatever'; - match records in a table
- INSERT INTO table VALUES ('xxx','yyy'); - Add entries to a table
- UPDATE table SET value = “newvalue” WHERE columname LIKE “a value”;
- TRUNCATE TABLE tablename; - delete table contants
- LOAD DATA LOCAL INFILE filename INTO TABLE table; - load data from a file into a table (open with mysql –local-infile)
- LOAD DATA LOCAL INFILE filename INTO TABLE table fields terminated by ',' enclosed by '“' lines terminated by '\n'; - load data from a CSV file into a table (open with mysql –local-infile)
- source filename.sql; - populate database from an sql file
- help - get help!
- exit - close connection
To redirect the output into a file add INTO OUTFILE “filename” to the end of a select statement.
Managing Users
- Log into mysql as root: mysql -u root -p
- Connect to the internal database: connect mysql;
- To show existing users: select User, Password from user;
- To reset a password (using password string): SET PASSWORD FOR theuser = PASSWORD('apassword');
- To delete: DROP USER theuser;
- Create user: create user auser@localhost identified by 'thepassword';
- To show the privileges of a user: show grants for theuser@localhost;
- To give access to a specific database: GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
NOTE: You need to specify @localhost if you want to connect to “localhost”
Backups
mysqldump can be used to backup databases, e.g.
mysqldump -u root -p --databases mylovelydb >> /backups/mylovelydb.dump
Still stuck? Maybe we can help. Contact us at Upwork
Never miss a thing subscribe to our newsletter
or follow us on twitter
For more super cool techie stuff check out our blog!!
Recent Changes
Contribute to this wiki
Why not help others by sharing your knowledge? Contribute something to this wiki and
join out hall of fame!
Contact us for a user name and password