Chapter 11: How to use a MySQL database?

Since the INSERT, UPDATE, and DELETE statements modify the data that's stored in a database, what are these statements sometimes referred to as?

Action queries. These statements don't return a result set. Instead, they return the number of rows that were affected by the query.

How to create a database named murach_test?

CREATE DATABASE murach_test. SQL statement

How to create a table

CREATE TABLE user ( UserID INT NOT NULL AUTO_INCREMENT, Email VARCHAR(50), FirstName VARCHAR(50), LastName VARCHAR(50), PRIMARY KEY(UserID) )

What are the two ways to interact with MySQL?

Command-line tool and MySQL Workbench

What is the syntax for the DELETE statement?

DELETE FROM table-name WHERE selection-criteria

How to drop a database?

DROP DATABASE murach_test. SQL statement

How to drop a table only if it exists?

DROP TABLE IF EXISTS user

How to drop a table? What happens if the table doesn't exist?

DROP TABLE User There will be an error.

What is DDL?

Data definition language. These statements let you create DB, create tables, drop tables, and so on.

What is DML?

Data manipulation language. These statements let you work with data in the table. They include SELECT, INSERT, UPDATE, and DELETE statements.

What is the syntax for the INSERT statement?

INSERT INTO table-name [(column-list)] VALUES (value-list)

What are the benefits of using MySQL (4)?

Inexpensive: Free and relatively inexpensive for others to use Fast: One of the fastest relational DB Easy to use: Easy to install & use compared to other relational DB Portable: runs on most modern OS

What is the difference between the inner join, left outer join, and right outer join?

Inner join is the default type of join. Rows are only included when the key o a row in the first stable matches the key of a row in the second table. In a left outer join, data for all rows in the first table are included in the table, but only the data for matching rows in the second table are included. In a right outer join, data for all rows in the second table are included in the table, but only the data for matching rows in the first table are included.

To return a result set that contains data from two tables, what clause do you use?

JOIN clause

What does the INSERT statement do?

Lets you insert one or more rows into one table of a DB. INSERT INTO User (FirstName, LastName, Email) VALUES ('John', 'Smith', '[email protected]'), ('Andrea', 'Steelman', '[email protected]') ('Joel', 'Murach', '[email protected]') Data for all columns must be defined if no default values.

What are the three main sections of the Home tab of MySQL Workbench?

MySQL Connections: stop and start the DB server and to code and run SQL statements. Models: Design databases. Shortcuts: Used to connect to MySQL documentation, utilities, bug reporting, and other links.

What is a relational database? What is a primary key? What is foreign key?

Relational database consists of one or more tables that consist of rows (records) and columns (fields). Tables are related by keys. The primary key in a table is the one that uniquely identifies each of the rows in the table. A foreign key is used to relate the rows in one table to the rows in another table.

What is SQL?

Structured Query Language (SQL)

What functionality does MySQL provide (6)?

Support for SQL. Support for multiple clients from a variety of interfaces & programming languages including Java, PHP, Python, Perl, and C. Connectivity: can provide access to data via an intranet or the Internet. Security: can protect access to your data so only authorized users can view the data. Referential integrity: InnoDB tables are used by default. Transaction processing.

What is the difference between the SELECT statement & the INSERT, UPDATE, and DELETE statements?

The SELECT statement is used to get data from one or more tables and put it in a result set, or result table. This is commonly referred to as a query. The INSERT, UPDATE, and DELETE statements don't return a result set. They return the number of rows that were affected by the query.

In a SELECT statement, state the clause for the following requirements: To specify the columns: To specify the rows: To specify the table that the data should be retrieved from: To specify how the result set should be sorted:

To specify the columns, use the SELECT clause. To specify the rows, use the WHERE clause. To specify the table that the data should be retrieved from, use the FROM clause. To specify how the result set should be sorted, use the ORDER BY clause.

What is the syntax for the UPDATE statement?

UPDATE table-name SET expression-1 [, expression-2] ... WHERE selection-criteria UPDATE User SET FirstName = 'Jack' WHERE Email = '[email protected]'

How to select a database named murach_test for use?

USE murach_test. MySQL statement

On what systems are the table and column names case-sensitive?

Unix systems