Mysql commands



  • Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.

    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.

    mysql> create database [databasename];

    List all databases on the sql server.

    mysql> show databases;

    Switch to a database.

    mysql> use [db name];

    To see all the tables in the db.

    mysql> show tables;

    To see database’s field formats.

    mysql> describe [table name];

    To delete a db.

    mysql> drop database [database name];

    To delete a table.

    mysql> drop table [table name];

    Show all data in a table.

    mysql> SELECT * FROM [table name];

    Returns the columns and column information pertaining to the designated table.

    mysql> show columns from [table name];

    Show certain selected rows with the value “whatever”.

    mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

    Show all records containing the name “Bob” AND the phone number ‘3444444’.

    mysql> 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.

    mysql> 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’.

    mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;

    Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’ limit to records 1 through 5.

    mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’ limit 1,5;

    Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

    mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

    Show unique records.

    mysql> SELECT DISTINCT [column name] FROM [table name];

    Show selected records sorted in an ascending (asc) or descending (desc).

    mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

    Return number of rows.

    mysql> SELECT COUNT(*) FROM [table name];

    Sum column.

    mysql> SELECT SUM(*) FROM [table name];

    Join tables on common columns.

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

    Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

    # mysql -u root -p
    mysql> use mysql;
    mysql> INSERT INTO user (Host,User,Password) VALUES(’%’,‘username’,PASSWORD(‘password’));
    mysql> flush privileges;

    Change a users password from unix shell.

    # [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

    Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

    # mysql -u root -p
    mysql> SET PASSWORD FOR ‘user’@‘hostnaDISCLAIMER: All statements posted here are In my opinion ,not investment advice to buy or sell. Do your own DD me’ = PASSWORD(‘passwordhere’);
    mysql> flush privileges;

    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.

    # /etc/init.d/mysql stop
    # mysqld_safe --skip-grant-tables &
    # mysql -u root
    mysql> use mysql;
    mysql> update user set password=PASSWORD(“newrootpassword”) where User=‘root’;
    mysql> flush privileges;
    mysql> quit
    # /etc/init.d/mysql stop
    # /etc/init.d/mysql start

    Set a root password if there is on root password.

    # mysqladmin -u root password newpassword

    Update a root password.

    # mysqladmin -u root -p oldpassword newpassword

    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. Update privs.

    # mysql -u root -p
    mysql> use mysql;
    mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
    mysql> flush privileges;

    Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

    # mysql -u root -p
    mysql> use mysql;
    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’);
    mysql> flush privileges;

    or

    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/privilages.

    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 dupes.

    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 1.

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

    Create Table Example 2.

    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

Log in to reply
 



Tmux Commands

screen and tmux

A comparison of the features (or more-so just a table of notes for accessing some of those features) for GNU screen and BSD-licensed tmux.

The formatting here is simple enough to understand (I would hope). ^ means ctrl+, so ^x is ctrl+x. M- means meta (generally left-alt or escape)+, so M-x is left-alt+x

It should be noted that this is no where near a full feature-set of either group. This - being a cheat-sheet - is just to point out the most very basic features to get you on the road.

Trust the developers and manpage writers more than me. This document is originally from 2009 when tmux was still new - since then both of these programs have had many updates and features added (not all of which have been dutifully noted here).

Action tmux screen
start a new session tmux OR
tmux new OR
tmux new-session
screen
re-attach a detached session tmux attach OR
tmux attach-session
screen-r
re-attach an attached session (detaching it from elsewhere) tmux attach -d OR
tmux attach-session -d
screen -dr
re-attach an attached session (keeping it attached elsewhere) tmux attach OR
tmux attach-session
screen -x
detach from currently attached session ^b d OR
^b :detach
^a ^d OR
^a :detach
rename-window to newname ^b , <newname> OR
^b :rename-window <newn>
^a A <newname>
list windows ^b w ^a w
list windows in chooseable menu ^a "
go to window # ^b # ^a #
go to last-active window ^b l ^a ^a
go to next window ^b n ^a n
go to previous window ^b p ^a p
see keybindings ^b ? ^a ?
list sessions ^b s OR
tmux ls OR
tmux list-sessions
screen -ls
toggle visual bell ^a ^g
create another window ^b c ^a c
exit current shell/window ^d ^d
split window/pane horizontally ^b " ^a S
split window/pane vertically ^b % ^a |
switch to other pane ^b o ^a <tab>
kill the current pane ^b x OR (logout/^D)
collapse the current pane/split (but leave processes running) ^a X
cycle location of panes ^b ^o
swap current pane with previous ^b {
swap current pane with next ^b }
show time ^b t
show numeric values of panes ^b q
toggle zoom-state of current pane (maximize/return current pane) ^b z
break the current pane out of its window (to form new window) ^b !
re-arrange current panels within same window (different layouts) ^b [space]
Kill the current window (and all panes within) ^b killw [target-window]
  • To try spamarrest for yourself visit the following link:

    Spamarrest email privacy

    read more
  • @SusanJaneW Spamarrest email easy setup

    INCOMING SERVER Server mail.spamarrest.com Server Type POP or IMAP Username Your Spam Arrest Username Password Your Spam Arrest Password``` <<--------------------------------------------------------------->> OUTGOING SERVER Server smtp.spamarrest.com Username* Your Spam Arrest Username Password* Your Spam Arrest Password Server Port 25, 587, or 2525

    read more
});