MySQL Basics

1
MySQL Basics

• Contents
• Logging into the MySQL server
• Using a Database and displaying its structure
• Putting data into a table
• Adding fields
• Multi-line command entry
• Updating existing records
• Deleting records
• Time to call it quits

2
MySQL Basics

• Logging in.
• Telnet to et791.ni.utoledo.edu
• Microsoft Telnetgt open et791.ni.utoledo.edu ?
• login
• Enter your username and password by the
  instructor
• At the et791 prompt.. Type mysql u username p
• et791 mysql u user_name p
  ?
• Enter password
• Enter your password again.. And you are into
  MySQL
• mysqlgt

3
MySQL Basics

• Using a database
• At the mysql prompt, Type
• mysqlgt USE database_name
  ?
• Database changed
• To create a table
• At the mysql prompt, Type
• mysqlgt create table table_name (field_01
  integer, ?
• -gt field_02 char(10))
  ?
• Query OK, 0 rows affected (0.00 sec)
• To List the tables
• At the mysql prompt, Type
• mysqlgt show tables ?

4
MySQL Basics

• To list the fields in a table
• At the mysql prompt, Type
• mysqlgt show columns from table_name ?
• Database changed

5
MySQL Basics

• Putting data into a table
• (Inserting a record)
• To insert a record into a table, use the INSERT
  statement
• INSERT INTO table_name VALUES(data, 'data',
  ...)
• include values for all the columns, whether you
  need them or not.
• INSERT INTO table_name(field_01, field_02 ,
  ...)
• -gtVALUES (data, 'data',...)
• Specifically name the columns you want to use.
• INSERT INTO table_name SET
• -gt field_01data, field_02'data..
• Works for versions of MySQL 3.22.10 or higher.

6
MySQL Basics

• Inserting a record contd.
• Quotes must go around text values.
• Standard date format is "yyyy-mm-dd".
• Standard time format is "hhmmss".
• Quotes are required around the standard date and
  time formats, noted above.
• Dates may also be entered as "yyyymmdd" and times
  as "hhmmss". If entered in this format, values
  don't need to be quoted.
• Numeric values do not need to be quoted. This
  holds true regardless of the data type a column
  is formatted to contain (e.g. text, date, time,
  integer).

7
MySQL Basics

• Adding Fields to the Table
• One field at a time
• mysqlgt alter table table_name add column
  field_03
• -gt char(20)
• Query OK, 1 row affected (0.04 sec)
• More than one at a time
• mysqlgt alter table table_name add column
  field_04
• -gt date,add column field_05 time
• Query OK, 1 row affected (0.04 sec)
• Records 1 Duplicates 0 Warnings 0.

8
MySQL Basics

• Multi-line command entry
• MySQL command line interface allows you to put a
  statement on one line or spread it across
  multiple lines.
• Enter a semicolon "" to close out the SQL
  statement. Once the semicolon is typed in and you
  hit enter, the statement is executed.
• Single Line Entry
• mysqlgt create table table_name (field_01
  integer,field_02
• -gt char(30))
• Multiple Line Entry
• mysqlgt create table table_name
• -gt (field_01
• -gt integer,
• -gt field_02
• -gt char(30))

9
MySQL Basics

• Multi-line command entry
• Don't break up words.
• mysqlgt create table table_name
• -gt (field_01 inte
• -gt ger,
• -gt field_02
• -gt char(30))
• Do not spread a field's string across multiple
  lines
• mysqlgt insert into table_name (field_02)
• -gt values
• -gt (Welcome to
• -gt CSET 3300')

10
MySQL Basics

• Updating existing records
• Modify one field at a time (Quote marks need to
  go around text but not around numbers.)
• mysqlgt update table_name set field_03'new
  info' where
• -gt field_011
• Query OK, 1 row affected (0.00 sec)
• Change multiple fields at once (Put commas
  between each field you're updating.)
• mysqlgt update table_name set field_0419991022,
• -gt field_05062218 where field_011
• Query OK, 1 row affected (0.00 sec)
• Update multiple records in one stroke
• mysqlgt update table_name set field_05152901
  where
• -gt field_04gt19990101
• Query OK, 3 rows affected (0.00 sec)

11
MySQL Basics

• Deleting records
• Syntax
• DELETE FROM table_name WHERE where_definition
• -gt ORDER BY ... LIMIT rows
• DELETE deletes rows from table_name that satisfy
  the condition given by where_definition, and
  returns the number of records deleted.
• DELETE FROM table_name
• WHERE user jim'
• ORDER BY score
• LIMIT 1
• This will delete the oldest entry (by score)
  where the row matches the WHERE clause.

12
MySQL Basics

• Time to call it quits
• mysqlgt quit