This is a list of handy MySQL commands that I use from time to time. At the bottom are functions, statements and clauses you can use with MySQL. <\/p>\n
Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.
\nThe description of the command is listed above the code description.<\/p>\n
To login (from unix shell) use -h only if needed. Create a database on the sql server. List all databases on the sql server. Switch to a database. To see all the tables in the db. To see database’s field formats. To delete a db. To delete a table. Show all data in a table. Returns the columns and column information pertaining to the designated table. Show certain selected rows with the value “whatever”. Show all records containing the name “Bob” AND the phone number ‘3444444’. Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the phone_number field. Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’. Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’ limit to records 1 through 5. Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a. Show unique records. Show selected records sorted in an ascending (asc) or descending (desc). Return number of rows. Sum column. Join tables on common columns. Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs. Change a users password from unix shell. Change a users password from MySQL prompt. Login as root. Set the password. Update privs. Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server. Set a root password if there is on root password. Update a root password. Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs. Delete a row(s) from a table. Update database permissions\/privilages. Delete a column. Add a new column to db. Change column name. Make a unique column so you get no dupelicates. Make a column bigger. Delete unique from table. Load a CSV file into a table. Dump all databases for backup. Backup file is sql commands to recreate all db’s. Dump one database for backup. Dump a table from a database. Restore database (or database table) from backup. Create Table Example 1. Create Table Example 2. Help and Show Commands mysql> \\? mysql> show tables from db_name This is a list of handy MySQL commands that I use from time to time. At the bottom are functions, statements and clauses you can use with MySQL. Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL. The description… <\/p>\n
\n
\n# [mysql dir]\/bin\/mysql -h hostname -u root -p<\/code><\/p>\n
\nmysql> create database [databasename];<\/code><\/p>\n
\nmysql> show databases;<\/code><\/p>\n
\nmysql> use [db name];<\/code><\/p>\n
\nmysql> show tables;<\/code><\/p>\n
\nmysql> describe [table \u201c\u201d not found \/]
\n;<\/code><\/p>\n
\nmysql> drop database [database name];<\/code><\/p>\n
\nmysql> drop table [table \u201c\u201d not found \/]
\n;<\/code><\/p>\n
\nmysql> SELECT * FROM [table \u201c\u201d not found \/]
\n;<\/code><\/p>\n
\nmysql> show columns from [table \u201c\u201d not found \/]
\n;<\/code><\/p>\n
\nmysql> SELECT * FROM [table \u201c\u201d not found \/]
\n WHERE [field name] = \"whatever\";<\/code><\/p>\n
\nmysql> SELECT * FROM [table \u201c\u201d not found \/]
\n WHERE name = \"Bob\" AND phone_number = '3444444';<\/code><\/p>\n
\nmysql> SELECT * FROM [table \u201c\u201d not found \/]
\n WHERE name != \"Bob\" AND phone_number = '3444444' order by phone_number;<\/code><\/p>\n
\nmysql> SELECT * FROM [table \u201c\u201d not found \/]
\n WHERE name like \"Bob%\" AND phone_number = '3444444';<\/code><\/p>\n
\nmysql> SELECT * FROM [table \u201c\u201d not found \/]
\n WHERE name like \"Bob%\" AND phone_number = '3444444' limit 1,5;<\/code><\/p>\n
\nmysql> SELECT * FROM [table \u201c\u201d not found \/]
\n WHERE rec RLIKE \"^a\";<\/code><\/p>\n
\nmysql> SELECT DISTINCT [column name] FROM [table \u201c\u201d not found \/]
\n;<\/code><\/p>\n
\nmysql> SELECT [col1],[col2] FROM [table \u201c\u201d not found \/]
\n ORDER BY [col2] DESC;<\/code><\/p>\n
\nmysql> SELECT COUNT(*) FROM [table \u201c\u201d not found \/]
\n;<\/code><\/p>\n
\nmysql> SELECT SUM(*) FROM [table \u201c\u201d not found \/]
\n;<\/code><\/p>\n
\nmysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;<\/code><\/p>\n
\n
\n# mysql -u root -p
\nmysql> use mysql;
\nmysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
\nmysql> flush privileges;<\/code><\/p>\n
\n
\n# [mysql dir]\/bin\/mysqladmin -u username -h hostname.blah.org -p password 'new-password'<\/code><\/p>\n
\n
\n# mysql -u root -p
\nmysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
\nmysql> flush privileges;<\/code><\/p>\n
\n
\n# \/etc\/init.d\/mysql stop
\n# mysqld_safe --skip-grant-tables &
\n# mysql -u root
\nmysql> use mysql;
\nmysql> update user set password=PASSWORD(\"newrootpassword\") where User='root';
\nmysql> flush privileges;
\nmysql> quit
\n# \/etc\/init.d\/mysql stop
\n# \/etc\/init.d\/mysql start<\/code><\/p>\n
\n# mysqladmin -u root password newpassword<\/code><\/p>\n
\n# mysqladmin -u root -p oldpassword newpassword<\/code><\/p>\n
\nUpdate privs.
\n# mysql -u root -p
\nmysql> use mysql;
\nmysql> grant usage on *.* to bob@localhost identified by 'passwd';
\nmysql> flush privileges;<\/code><\/p>\n
\n
\n# mysql -u root -p
\nmysql> use mysql;
\nmysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
\nmysql> flush privileges;
\n<\/code>
\nor
\n
\nmysql> grant all privileges on databasename.* to username@localhost;
\nmysql> flush privileges;<\/code>
\nTo update info already in a table.
\n
\nmysql> UPDATE [table \u201c\u201d not found \/]
\n SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';<\/code><\/p>\n
\n
\nmysql> DELETE from [table \u201c\u201d not found \/]
\n where [field name] = 'whatever';<\/code><\/p>\n
\n
\nmysql> flush privileges;<\/code><\/p>\n
\n
\nmysql> alter table [table \u201c\u201d not found \/]
\n drop column [column name];<\/code><\/p>\n
\nmysql> alter table [table \u201c\u201d not found \/]
\n add column [new column name] varchar (20);<\/code><\/p>\n
\nmysql> alter table [table \u201c\u201d not found \/]
\n change [old column name] [new column name] varchar (50);<\/code><\/p>\n
\nmysql> alter table [table \u201c\u201d not found \/]
\n add unique ([column name]);<\/code><\/p>\n
\n
\nmysql> alter table [table \u201c\u201d not found \/]
\n modify [column name] VARCHAR(3);<\/code><\/p>\n
\nmysql> alter table [table \u201c\u201d not found \/]
\n drop index [colmn name];<\/code><\/p>\n
\nmysql> LOAD DATA INFILE '\/tmp\/filename.csv' replace INTO TABLE [table \u201c\u201d not found \/]
\n FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' (field1,field2,field3);<\/code><\/p>\n
\n# [mysql dir]\/bin\/mysqldump -u root -ppassword --opt >\/tmp\/alldatabases.sql<\/code><\/p>\n
\n
\n# [mysql dir]\/bin\/mysqldump -u username -ppassword --databases databasename >\/tmp\/databasename.sql<\/code><\/p>\n
\n
\n# [mysql dir]\/bin\/mysqldump -c -u username -ppassword databasename tablename > \/tmp\/databasename.tablename.sql<\/code><\/p>\n
\n
\n# [mysql dir]\/bin\/mysql -u username -ppassword databasename < \/tmp\/databasename.sql<\/code><\/p>\n
\n<\/code>
\nmysql> CREATE TABLE [table \u201c\u201d not found \/]
\n (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));<\/code><\/p>\n
\n
\nmysql> create table [table \u201c\u201d not found \/]
\n (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
\n<\/code><\/p>\n
\n
\n$ mysql --help | less
\n$ mysqld --help
\n$ mysqlshow --help | less
\n$ mysqldump --help | less
\n$ mysqlshow\t- show all databases.
\n$ mysqlshow db_name - all tables in particular database.
\n$ mysqlshow db_name BA* - all tables which start from BA letters.<\/p>\n
\nmysql> use db_name;
\nmysql> show databases;
\nmysql> show databases like 'ba%'
\nmysql> show tables;
\nmysql> describe table_name;
\nmysql> select user(), now(), version(), database();
\n+---------------+---------------------+----------------+------------+
\n| user() | now() | version() | database() |
\n+---------------+---------------------+----------------+------------+
\n| ana@localhost | 2003-01-05 21:24:27 | 4.0.1-alpha-nt | test |
\n+---------------+---------------------+----------------+------------+<\/p>\n
\nmysql> show tables from db_name like '__ab%'
\nmysql> show columns from table_name
\nmysql> show columns from table_name from db_name
\nmysql> show grants for user_name
\nmysql> show index from table_name
\nmysql> show index from table_name from db_name
\nmysql> show processlist
\nmysql> show status
\nmysql> show table status from db_name
\nmysql> show variables
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"