MYSQL basic for command line Part Two -

Ok lets start with Part Two…In part one you got enough information how to start with SQL command line. So keep walking and try out other major basic stuff of SQL. I am providing very basics, continue your try… dig out and kick out…

Allow the user “Rajeev” to connect to the server from localhost using the password “passwd”

# mysql -u root -p

Switch to mysql database

mysql> use mysql;

give privileges

mysql> grant usage on *.* to Rajeev@localhost identified by ‘passwd’;

Update privileges

mysql> flush privileges;

Give user privileges for a db

# mysql -u root -p

Switch to mysql database

mysql> use mysql;

Grant privileges

mysql> 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’);

Update privileges

mysql> flush privileges;

OR you can use

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;

To update info already in a table

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

Delete a row(s) from a table

mysql> DELETE from [table name] where [field name] = ‘whatever’;

Update database permissions/privileges

mysql> flush privileges;

Delete a column

mysql> alter table [table name] drop column [column name];

Add a new column to db

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no duplicates

mysql> alter table [table name] add unique ([column name]);

Make a column bigger

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table

mysql> 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 -u root -ppassword –opt >/tmp/alldatabases.sql

Dump one database for backup

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

Dump a table from a database

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table

Example One

mysql> 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));

Example Two

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

MYSQL Statements and clauses

ALTER DATABASE ALTER TABLE	ALTER VIEW	ANALYZE TABLE	BACKUP TABLE
CACHE INDEX	CHANGE MASTER TO	CHECK TABLE	CHECKSUM TABLE
COMMIT	CREATE DATABASE	CREATE INDEX	CREATE TABLE
CREATE VIEW	DELETE	DESCRIBE	DO
DROP DATABASE	DROP INDEX	DROP TABLE	DROP USER
DROP VIEW	EXPLAIN	FLUSH	GRANT
HANDLER	INSERT	JOIN	KILL
LOAD DATA FROM MASTER	LOAD DATA INFILE	LOAD INDEX INTO CACHE	LOAD TABLE…FROM MASTER
LOCK TABLES	OPTIMIZE TABLE	PURGE MASTER LOGS	RENAME TABLE
REPAIR TABLE	REPLACE	RESET	RESET MASTER
RESET SLAVE	RESTORE TABLE	REVOKE	ROLLBACK
ROLLBACK TO SAVEPOINT	SAVEPOINT	SELECT	SET
SET PASSWORD	SET SQL_LOG_BIN	SET TRANSACTION	SHOW BINLOG EVENTS
SHOW CHARACTER SET	SHOW COLLATION	SHOW COLUMNS	SHOW CREATE DATABASE
SHOW CREATE TABLE	SHOW CREATE VIEW	SHOW DATABASES	SHOW ENGINES
SHOW ERRORS	SHOW GRANTS	SHOW INDEX	SHOW INNODB STATUS
SHOW LOGS	SHOW MASTER LOGS	SHOW MASTER STATUS	SHOW PRIVILEGES
SHOW PROCESSLIST	SHOW SLAVE HOSTS	SHOW SLAVE STATUS	SHOW STATUS
SHOW TABLE STATUS	SHOW TABLES	SHOW VARIABLES	SHOW WARNINGS
START SLAVE	START TRANSACTION	STOP SLAVE	TRUNCATE TABLE
UNION	UNLOCK TABLES	USE

String Functions

AES_DECRYPT	AES_ENCRYPT	ASCII	BIN
BINARY	BIT_LENGTH	CHAR	CHAR_LENGTH
CHARACTER_LENGTH	COMPRESS	CONCAT	CONCAT_WS
CONV	DECODE	DES_DECRYPT	DES_ENCRYPT
ELT	ENCODE	ENCRYPT	EXPORT_SET
FIELD	FIND_IN_SET	HEX	INET_ATON
INET_NTOA	INSERT	INSTR	LCASE
LEFT	LENGTH	LOAD_FILE	LOCATE
LOWER	LPAD	LTRIM	MAKE_SET
MATCH AGAINST	MD5	MID	OCT
OCTET_LENGTH	OLD_PASSWORD	ORD	PASSWORD
POSITION	QUOTE	REPEAT	REPLACE
REVERSE	RIGHT	RPAD	RTRIM
SHA	SHA1	SOUNDEX	SPACE
STRCMP	SUBSTRING	SUBSTRING_INDEX	TRIM
UCASE	UNCOMPRESS	UNCOMPRESSED_LENGTH	UNHEX
UPPER

Date and Time Functions

ADDDATE	ADDTIME	CONVERT_TZ	CURDATE
CURRENT_DATE	CURRENT_TIME	CURRENT_TIMESTAMP	CURTIME
DATE	DATE_ADD	DATE_FORMAT	DATE_SUB
DATEDIFF	DAY	DAYNAME	DAYOFMONTH
DAYOFWEEK	DAYOFYEAR	EXTRACT	FROM_DAYS
FROM_UNIXTIME	GET_FORMAT	HOUR	LAST_DAY
LOCALTIME	LOCALTIMESTAMP	MAKEDATE	MAKETIME
MICROSECOND	MINUTE	MONTH	MONTHNAME
NOW	PERIOD_ADD	PERIOD_DIFF	QUARTER
SEC_TO_TIME	SECOND	STR_TO_DATE	SUBDATE
SUBTIME	SYSDATE	TIME	TIMEDIFF
TIMESTAMP	TIMESTAMPDIFF	TIMESTAMPADD	TIME_FORMAT
TIME_TO_SEC	TO_DAYS	UNIX_TIMESTAMP	UTC_DATE
UTC_TIME	UTC_TIMESTAMP	WEEK	WEEKDAY
WEEKOFYEAR	YEAR	YEARWEEK

Mathematical and Aggregate Functions

ABS	ACOS	ASIN	ATAN
ATAN2	AVG	BIT_AND	BIT_OR
BIT_XOR	CEIL	CEILING	COS
COT	COUNT	CRC32	DEGREES
EXP	FLOOR	FORMAT	GREATEST
GROUP_CONCAT	LEAST	LN	LOG
LOG2	LOG10	MAX	MIN
MOD	PI	POW	POWER
RADIANS	RAND	ROUND	SIGN
SIN	SQRT	STD	STDDEV
SUM	TAN	TRUNCATE	VARIANCE

Flow Control Functions

CASE

IFNULL

NULLIF

Command-Line Utilities

comp_err	isamchk	make_binary_distribution	msql2mysql
my_print_defaults	myisamchk	myisamlog	myisampack
mysqlaccess	mysqladmin	mysqlbinlog	mysqlbug
mysqlcheck	mysqldump	mysqldumpslow	mysqlhotcopy
mysqlimport	mysqlshow	perror

Perl API – using functions and methods built into the Perl DBI with MySQL

available_drivers	begin_work	bind_col	bind_columns
bind_param	bind_param_array	bind_param_inout	can
clone	column_info	commit	connect
connect_cached	data_sources	disconnect	do
dump_results	err	errstr	execute
execute_array	execute_for_fetch	fetch	fetchall_arrayref
fetchall_hashref	fetchrow_array	fetchrow_arrayref	fetchrow_hashref
finish	foreign_key_info	func	get_info
installed_versions	last_insert_id	looks_like_number	neat
neat_list	parse_dsn	parse_trace_flag	parse_trace_flags
ping	prepare	prepare_cached	primary_key
primary_key_info	quote	quote_identifier	rollback
rows	selectall_arrayref	selectall_hashref	selectcol_arrayref
selectrow_array	selectrow_arrayref	selectrow_hashref	set_err
state	table_info	table_info_all	tables
trace	trace_msg	type_info	type_info_all
Attributes for Handles

PHP API – using functions built into PHP with MySQL

mysql_affected_rows	mysql_change_user	mysql_client_encoding	mysql_close
mysql_connect	mysql_create_db	mysql_data_seek	mysql_db_name
mysql_db_query	mysql_drop_db	mysql_errno	mysql_error
mysql_escape_string	mysql_fetch_array	mysql_fetch_assoc	mysql_fetch_field
mysql_fetch_lengths	mysql_fetch_object	mysql_fetch_row	mysql_field_flags
mysql_field_len	mysql_field_name	mysql_field_seek	mysql_field_table
mysql_field_type	mysql_free_result	mysql_get_client_info	mysql_get_host_info
mysql_get_proto_info	mysql_get_server_info	mysql_info	mysql_insert_id
mysql_list_dbs	mysql_list_fields	mysql_list_processes	mysql_list_tables
mysql_num_fields	mysql_num_rows	mysql_pconnect	mysql_ping
mysql_query	mysql_real_escape_string	mysql_result	mysql_select_db
mysql_stat	mysql_tablename	mysql_thread_id	mysql_unbuffered_query

Author
Recent Posts

Follow me

Rajeev Achra

I am a software engineer who specializes in Internet applications. I have worked with a wide variety of technologies and programming languages to open source LAMP environments. I have more than 6 years of object-oriented programming experience and am highly proficient in ActionScript, PHP, MYSQL, JavaScript, Jquery and a multitude of other technologies used in modern web applications.

Follow me

Type To Search

Type To Search

Type To Search

MYSQL basic for command line Part Two

MYSQL basic for command line Part One

Convert Folder to Zip using PHP class

Related Posts

Mysql match first n characters

Mysql command line import-export single table

Leave a Reply Cancel reply

Start Your Journey to Better Knowledge

+91 98286 51234