Welcome to http://www.marssoft.de/
 
Tuesday, 23rd July 2019 02:48:10 (GMT+1) 

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
guides:mysqlcommands [2006/03/22 14:43]
mario
guides:mysqlcommands [2014/04/02 22:39] (current)
Line 2: Line 2:
  
 |**Description**|**Command**| |**Description**|**Command**|
-|To login (from unix shell) use -h only if needed.|[mysql dir]/​bin/​mysql -h hostname -u root -p| +|To login (from unix shell) use -h only if needed.|%%[mysql dir]/​bin/​mysql -h hostname -u root -p%%
-|Create a database on the sql server.|create database [databasename];​| +|Create a database on the sql server.|%%create database [databasename];​%%
-|List all databases on the sql server.|show databases;​| +|List all databases on the sql server.|%%show databases;%%
-|Switch to a database.|use [db name];| +|Switch to a database.|%%use [db name];%%
-|To see all the tables in the db.|show tables;| +|To see all the tables in the db.|%%show tables;%%
-|To see database'​s field formats.|describe [table name];| +|To see database'​s field formats.|%%describe [table name];%%
-|To delete a db.|drop database [database name];| +|To delete a db.|%%drop database [database name];%%
-|To delete a table.|drop table [table name];| +|To delete a table.|%%drop table [table name];%%
-|Show all data in a table.|SELECT * FROM [table name];| +|Show all data in a table.|%%SELECT * FROM [table name];%%| 
-|Returns the columns and column information pertaining to the designated table.|show columns from [table name];| +|To add a user '​custom',​ password '​obscure',​ allowing him/her common tasks on database '​bankaccount.*'​ but only from host '​localhost'​.|%%GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON bankaccount.* TO '​custom'​@'​localhost'​ IDENTIFIED BY '​obscure';​%%| 
-|Show certain selected rows with the value "​whatever"​.| SELECT * FROM [table name] WHERE [field name] = "​whatever";​| +|Same, but allowing him/her all tasks on all databases and from all hosts.|%%GRANT ALL PRIVILEGES ON *.* TO '​custom'​@'​%'​ IDENTIFIED BY '​obscure';​%%
-|Show all records containing the name "​Bob"​ AND the phone number '​3444444'​.|SELECT * FROM [table name] WHERE name = "​Bob"​ AND phone_number = '​3444444';​| +|Returns the columns and column information pertaining to the designated table.|%%show columns from [table name];%%
-|Show all records not containing the name "​Bob"​ AND the phone number '​3444444'​ order by the phone_number field.|SELECT * FROM [table name] WHERE name != "​Bob"​ AND phone_number = '​3444444'​ order by phone_number;​| +|Show certain selected rows with the value "​whatever"​.|%% SELECT * FROM [table name] WHERE [field name] = "​whatever";​%%
-|Show all records starting with the letters '​bob'​ AND the phone number '​3444444'​.|SELECT * FROM [table name] WHERE name like "​Bob%"​ AND phone_number = '​3444444';​|+|Show all records containing the name "​Bob"​ AND the phone number '​3444444'​.|%%SELECT * FROM [table name] WHERE name = "​Bob"​ AND phone_number = '​3444444';​%%
 +|Show all records not containing the name "​Bob"​ AND the phone number '​3444444'​ order by the phone_number field.|%%SELECT * FROM [table name] WHERE name != "​Bob"​ AND phone_number = '​3444444'​ order by phone_number;​%%
 +|Show all records starting with the letters '​bob'​ AND the phone number '​3444444'​.|%%SELECT * FROM [table name] WHERE name like "​Bob%"​ AND phone_number = '​3444444';​%%|
 |Use a regular expression to find records. Use "​REGEXP BINARY"​ to force case-sensitivity. This finds any record beginning with a.|%%SELECT * FROM [table name] WHERE rec RLIKE "​^a$";​%%| |Use a regular expression to find records. Use "​REGEXP BINARY"​ to force case-sensitivity. This finds any record beginning with a.|%%SELECT * FROM [table name] WHERE rec RLIKE "​^a$";​%%|
-|Show unique records.|SELECT DISTINCT [column name] FROM [table name];| +|Show unique records.|%%SELECT DISTINCT [column name] FROM [table name];%%
-|Show selected records sorted in an ascending (asc) or descending (desc).|SELECT [col1],​[col2] FROM [table name] ORDER BY [col2] DESC;| +|Show selected records sorted in an ascending (asc) or descending (desc).|%%SELECT [col1], [col2] FROM [table name] ORDER BY [col2] DESC;%%
-|Count rows.|SELECT COUNT(*) FROM [table name];| +|Count rows.|%%SELECT COUNT(*) FROM [table name];%%
-|Join tables on common columns.|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;| +|Join tables on common columns.|%%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;%%
-|Switch to the mysql db. Create a new user.|INSERT INTO [table name] (Host,​User,​Password) VALUES('​%','​user',​PASSWORD('​password'​));​| +|Switch to the mysql db. Create a new user.|%%INSERT INTO [table name] (Host, User, Password) VALUES('​%',​ '​user',​ PASSWORD('​password'​));​%%
-|Change a users password (from unix shell).|[mysql dir]/​bin/​mysqladmin -u root -h hostname.blah.org -p password '​new-password'​| +|Change a users password (from unix shell).|%%[mysql dir]/​bin/​mysqladmin -u root -h hostname.blah.org -p password '​new-password'​%%
-|Change a users password (from the MySQL prompt).|SET PASSWORD FOR '​user'​@'​hostname'​ = PASSWORD('​passwordhere'​);​| +|Change a users password (from the MySQL prompt).|%%SET PASSWORD FOR '​user'​@'​hostname'​ = PASSWORD('​passwordhere'​);​%%
-|Switch to mysql db. Give user privilages for a db.|INSERT INTO [table name] (Host, Db, User, Select_priv,​ Insert_priv,​ Update_priv,​ Delete_priv,​ Create_priv,​ Drop_priv) VALUES ('​%',​ '​db',​ '​user',​ '​Y',​ '​Y',​ '​Y',​ '​Y',​ '​Y',​ '​N'​);​| +|Switch to mysql db. Give user privilages for a db.|%%INSERT INTO [table name] (Host, Db, User, Select_priv,​ Insert_priv,​ Update_priv,​ Delete_priv,​ Create_priv,​ Drop_priv) VALUES ('​%',​ '​db',​ '​user',​ '​Y',​ '​Y',​ '​Y',​ '​Y',​ '​Y',​ '​N'​);​%%
-|To update info already in a table.|UPDATE [table name] SET Select_priv = '​Y',​ Insert_priv = '​Y',​ Update_priv = '​Y'​ where [field name] = '​user';​| +|To update info already in a table.|%%UPDATE [table name] SET Select_priv = '​Y',​ Insert_priv = '​Y',​ Update_priv = '​Y'​ where [field name] = '​user';​%%
-|Delete a row(s) from a table.|DELETE from [table name] where [field name] = '​whatever';​| +|Delete a row(s) from a table.|%%DELETE from [table name] where [field name] = '​whatever';​%%
-|Update database permissions/​privilages.|FLUSH PRIVILEGES;​| +|Update database permissions/​privilages.|%%FLUSH PRIVILEGES;%%
-|Delete a column.|alter table [table name] drop column [column name];| +|Delete a column.|%%alter table [table name] drop column [column name];%%
-|Add a new column to db.|alter table [table name] add column [new column name] varchar (20);| +|Add a new column to db.|%%alter table [table name] add column [new column name] varchar (20);%%
-|Change column name.|alter table [table name] change [old column name] [new column name] varchar (50);| +|Change column name.|%%alter table [table name] change [old column name] [new column name] varchar (50);%%
-|Make a unique column so you get no dupes.|alter table [table name] add unique ([column name]);| +|Make a unique column so you get no dupes.|%%alter table [table name] add unique ([column name]);%%
-|Make a column bigger.|alter table [table name] modify [column name] VARCHAR(3);​| +|Make a column bigger.|%%alter table [table name] modify [column name] VARCHAR(3);%%
-|Delete unique from table.|alter table [table name] drop index [colmn name];| +|Delete unique from table.|%%alter table [table name] drop index [colmn name];%%
-|Load a CSV file into a table.|LOAD DATA INFILE '/​tmp/​filename.csv'​ replace INTO TABLE [table name] FIELDS TERMINATED BY ','​ LINES TERMINATED BY '​\n'​ (field1,​field2,​field3);​| +|Load a CSV file into a table.|%%LOAD DATA INFILE '/​tmp/​filename.csv'​ replace INTO TABLE [table name] FIELDS TERMINATED BY ', ' LINES TERMINATED BY '​\n'​ (field1, field2, field3);%%
-|Dump all databases for backup. Backup file is sql commands to recreate all db'​s.|[mysql dir]/​bin/​mysqldump --user=root --password='​blah'​ --all-databases > /​tmp/​sql-01_backup.sql| +|Dump all databases for backup. Backup file is sql commands to recreate all db's.|%%[mysql dir]/​bin/​mysqldump --user=root --password='​blah'​ --all-databases > /​tmp/​sql-01_backup.sql%%
-|Create Table Example 1.|CREATE TABLE [table name] (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));​| +|Create Table Example 1.|%%CREATE TABLE [table name] (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));​%%
-|Create Table Example 2.|create table [table name] (personid int(50) not null auto_increment primary key, firstname varchar(35),​ middlename varchar(50),​ lastname varchar(50) default '​bato'​);​|+|Create Table Example 2.|%%create table [table name] (personid int(50) not null auto_increment primary key, firstname varchar(35),​ middlename varchar(50),​ lastname varchar(50) default '​bato'​);​%%|
guides/mysqlcommands.txt · Last modified: 2014/04/02 22:39 (external edit)