My SQL Basics Contents Logging into the My

My. SQL Basics Contents • Logging into the My. SQL 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 CSET 3300: Database Driven Web Sites

My. SQL Basics Logging in…. • Telnet to et 791. ni. utoledo. edu Microsoft Telnet> open et 791. ni. utoledo. edu login: Enter your username and password by the instructor • At the et 791 prompt. . Type mysql –u username –p et 791# mysql –u user_name –p Enter password: Enter your password again. . And you are into My. SQL mysql> CSET 3300: Database Driven Web Sites

My. SQL Basics Using a database • At the mysql prompt, Type mysql> USE database_name Database changed To create a table • At the mysql prompt, Type mysql> create table_name (field_01 integer, -> field_02 char(10)); Query OK, 0 rows affected (0. 00 sec) To List the tables • At the mysql prompt, Type mysql> show tables; CSET 3300: Database Driven Web Sites

My. SQL Basics To list the fields in a table • At the mysql prompt, Type mysql> show columns from table_name; Database changed CSET 3300: Database Driven Web Sites

My. SQL 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 , ->VALUES (data, 'data', . . . ); Specifically name the columns you want to use. • INSERT INTO table_name SET -> field_01=data, field_02='data‘. . ; Works for versions of My. SQL 3. 22. 10 or higher. CSET 3300: Database Driven Web Sites . . . )

My. SQL Basics Inserting a record… contd. • Quotes must go around text values. • Standard date format is "yyyy-mm-dd". • Standard time format is "hh: mm: ss". • 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). CSET 3300: Database Driven Web Sites

My. SQL Basics Adding Fields to the Table • One field at a time mysql> alter table_name add column field_03 -> char(20); Query OK, 1 row affected (0. 04 sec) • More than one at a time mysql> alter table_name add column field_04 -> date, add column field_05 time; Query OK, 1 row affected (0. 04 sec) Records: 1 Duplicates: 0 Warnings: 0. CSET 3300: Database Driven Web Sites

My. SQL Basics Multi-line command entry • My. SQL 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 mysql> create table_name (field_01 integer, field_02 -> char(30)); • Multiple Line Entry mysql> create table_name -> (field_01 -> integer, -> field_02 -> char(30)); CSET 3300: Database Driven Web Sites

My. SQL Basics Multi-line command entry • Don't break up words. mysql> create table_name -> (field_01 inte -> ger, -> field_02 -> char(30)); • Do not spread a field's string across multiple lines mysql> insert into table_name (field_02) -> values -> (‘Welcome to -> CSET 3300'); CSET 3300: Database Driven Web Sites

My. SQL Basics Updating existing records • Modify one field at a time (Quote marks need to go around text but not around numbers. ) mysql> update table_name set field_03='new info' where -> field_01=1; Query OK, 1 row affected (0. 00 sec) • Change multiple fields at once (Put commas between each field you're updating. ) mysql> update table_name set field_04=19991022, -> field_05=062218 where field_01=1; Query OK, 1 row affected (0. 00 sec) • Update multiple records in one stroke mysql> update table_name set field_05=152901 where -> field_04>19990101; Query OK, 3 rows affected (0. 00 sec) CSET 3300: Database Driven Web Sites

My. SQL Basics Deleting records Syntax: DELETE FROM table_name [WHERE where_definition] -> [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. CSET 3300: Database Driven Web Sites

My. SQL Basics • Time to call it quits mysql> quit CSET 3300: Database Driven Web Sites