18 Feb
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”
- Login as root user
# 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
- Login as root user
# 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 | IF | 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 |
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.
Latest posts by Rajeev Achra (see all)
- Jquery webcam plugin - June 19, 2016
- How To Add and Delete Users on a CentOSServer - June 5, 2016
- How To Set Up vsftpd on CentOS 6 - June 5, 2016