LAMPsig MySQL Class :: Class #2

LAMPsig Meetings

3rd Saturday of the Month 1-3pm
Tom Bradley Youth and Family Center
5213 West Pico Blvd.
Los Angeles, CA 90019

Monday, May 9. 2005

LAMPsig MySQL Class :: Class #2

Class #2 of the MySQL class is scheduled for this Monday, May 9nd at 7:00pm at CalTek and will cover the various clients used to access a MySQL server.

Due to space limitations the class is now full so no more students are currently being accepted.

*Complete syllabus for Class #2 are continued in the further reading section.*

_Instructor:_
*Steve Glasser :: steve_AT_fpig_(dot)_net*

_Class Contact:_
*Sharon Lake :: sharon_AT_linuxchixla_(dot)_org*

_Lab Help:_
*Solomon Chang :: skevin521_AT_yahoo_(dot)_com*

*_CLASS #2 SYLLABUS_*

Class 2 – Using the MySQL client. (Chapter 3 / MySQL-M 3.1, 4, 25)
_10% exam material_

Invoking Command-Line Client Programs from Shell Prompt
1. short vs. long
  1. shell> mysql -V or -h
  2. shell> mysql --version or --host
2. options followed by values
  1. --host=myhost.example.com
  2. -h myhost.example.com
  3. -hmyhost.example.com
3. default values and changing default values through my.cnf
  1. Windows my.ini /my.cnf
  2. *nix global /etc/my.cnf, local /home/user/.my.cnf
Connection Parameter Options
1. Host (default value localhost)
  1. --host=host_name / -h host_name / -hhost_name
2. Port (default value 3306)
  1. --port=port_number / -P port_number
3. Socket (default value *nix /tmp/mysql.sock, Windows pipe name 'MySQL')
  1. --socket=socket_name / -S socket_name
    
    Windows NT servers --enable-named-pipe option
4. User (default value *nix system login name, Windows 'ODBC')
  1. --user=user_name / -u user_name / -uuser_name
5. Password (no default value)
  1. --password=password_value or -ppassword_value
    
    Note spacing (-ppassword_value)
    OK to omit the password_value in initial connection parameter as with either span STYLE="font-weight: medium">–password or -p flag a password prompt will appear
    If you omit the password option then your MySQL account must be specifically set up to allow you to connect without a password.
6. Compress
  1. --compress / -C
7. Using Custom Option Files
  1. --defaults-file=file_location
    
    Must be first option after the command name
    For an option specified multiple times in the same or separate option file(s), the option found last takes precedence.
    Options on the command line take precedence over options found in options files
Selecting Default (or Current) Database
1. Naming on command line when connecting
  1. shell> mysql --user=username --password database_name
    shell> mysql --user=lroot --password world
  2. shell> mysqldump --user=username --password database_name > database_name.sql
    shell> mysqldump --user=root --password world > world.sql
2. Specifying with sql statements
  1. mysql> SELECT * FROM database_name.database_table;
3. Select or Change default database
  1. mysql> USE database_name;
    mysql> SELECT * FROM database_table;
MySQL Client Program
1. Interactive mode
  1. useful for day-to-day, one time queries, etc.
2. Batch mode
  1. running prewritten queries, complex queries that are difficult to type, or to be run automatically by scheduler without user intervention.
Using MySQL Client Interactively
1. Connect to server and database
  1. shell> mysql -u root -p -h localhost world
  2. A mysql> prompt will appear ready to accept sql statements
    
    mysql> is primary prompt
    -> generic secondary prompt: waiting for next line of statement or statement terminator
    '> specific secondary prompt: waiting for end of single-quoted string
    “> specific secondary prompt: waiting for end of double-quoted string
    `> specific secondary prompt: waiting for end of backtick-quoted identifier.
  3. Canceling a statement with clear query \c
  4. sql statement need to be terminated in order for the MySQL server to execute.
    
    The terminators ; and \g are interchangeable
    
    mysql> SELECT * FROM world;
    mysql> SELECT * FROM world\g
    
    The terminator \G (capital G) displays query results with each column value on a separate line
    
    mysql> SELECT * FROM world\G
2. Executing statements from text or source file. If statement in source file end with an error, mysql ignores remainder of file. To execute entire file regardless of statement errors use the --force or -f option. A source file can contain other execute other source files, but careful of endless source loops.
  1. mysql> SOURCE source_path/file_name;
  2. shell> mysql database_name < source_path/file_name
MySQL output
1. Interactive Mode displays with bars and dashes in tabular format
2. Batch Mode produces tab-delimited output between data values
3. Output options
  1. --batch or -B to display with tabs even if used interactively
    
    --raw or -r option to suppress newline and carriage return escape-sequences like \n or \r
  2. --table or -t to display tabular format in table even if used in batch mode
  3. --html or -H to produce output in HTML format
  4. --xml or -X for XML format
Summary of Client Commands.
1. Long form is case insensitive, short form case sensitive.
2. Long form can only be executed at the mysql> primary prompt unless you invoke with --named-commands options. Short form can be executed on any input line
  1. mysql> sql statements: SELECT,INSERT, UPDATE, and DELETE
  2. mysql> QUIT or \q
  3. mysql> SOURCE or \.
  4. mysql> STATUS or \s
  5. mysql> HELP or \h
  6. mysql> CLEAR or \c
Using --safe-updates option (--i-am-a-dummy)
1. Limits UPDATE and DELETE statement to those either containing a WHERE clause or a LIMIT clause.
2. Restricts returned rows to 1,000 unless the statement has a LIMIT clause.
3. Multiple-table SELECT statements are allowed only if MySQL will examine no more than 1,000,000 rows to process the query.
mysqlimport
mysqldump
Reloading mysqldump output
Checking tables with mysqlcheck and myisamchk
MySQL Connectivity Drivers
1. MySQL Connector/ODBC
2. MySQL Connector/J

Posted by Sharon Lake in Classes: MySQL at 19:00 | Comments (0) | Trackbacks (0)
View as PDF: This entry | This month | Full blog

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

No comments

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: BBCode format allowed
	Remember Information? Subscribe to this entry