MySQL Commands

MySQL Account Permissions:

• MySQL uses account permissions to specify who can do what. Anyone using a valid account can connect to the MySQL server, but he or she can only do those things that are allowed by the permissions for the account.
• Permissions can be granted for particular databases, tables, or columns.
• Permissions can be granted or removed individually or all at once.

ALL	All permissions
ALTER	Can alter the structure of tables.
CREATE	Can create new databases, tables or indexes.
DELETE	Can delete rows in tables.
DROP	Can drop databases or tables.
FILE	Can read and write files on the server. (File access on server host.)
GRANT	Can change the permissions on a MySQL account.
INSERT	Can insert new rows into tables.
REVOKE	Can remove permissions on a MySQL account.
SELECT	Can read data from tables.
SHUTDOWN	Can shut down the MySQL server.
UPDATE	Can change data in a table.
USAGE	No permissions.

Database and table queries:

ALTER TABLE tableName (followed by specified changes)

• Examples:

ALTER TABLE tblMembers MODIFY lastName VARCHAR(50)

ALTER TABLE tblMembers CHANGE fName firstName VARCHAR(20)

• Changes you can make with the ALTER query:

ADD columnName definition	Adds a column; definition includes the data type and optional definitions.
ALTER columnName SET DEFAULT value	Changes the default value for a column.
ALTER columnName DROP DEFAULT	Removes the default value fro a column.
CHANGE columnName newColumnName definition	Changes the definition of a column and renames the column; definition includes the data type and optional definitions.
DROP columnName	Deletes a column, including all the data in the column. The data can’t be recovered.
MODIFY columnName definition	Changes the definition of a column; definition includes the data type and optional definitions.
RENAME newTableName	Renames a table.

CREATE DATABASE databaseName

• Creates a new, empty database. Once a database has been created, you can add tables to it.

CREATE TABLE tableName (

columnName definition,

columnName definition,

PRIMARY KEY(columnName) )

• Example:

CREATE TABLE tableName (

loginName VARCHAR(20) NOT NULL,

password VARCHAR(20) NOT NULL,

createDate DATE NOT NULL,

lastName VARCHAR(25),

fnameName VARCHAR(20),

Email VARCHAR(50),

addr VARCHAR(50),

city VARCHAR(30),

state CHAR(2),

zip VARCHAR(10),

hPhone CHAR(12),

wPhone CHAR(12),

cPhone CHAR(12),

PRIMARY KEY(loginName) )

• The information for each column is separated from the information for the next column by a comma.
• The entire list of column names with definitions is enclosed in parentheses.
• Each column name is followed by its data type and any other definitions required:
1. MySQL Data Types:

CHAR (length)	Fixed-length character string.
VARCHAR (length)	Variable-length character string with length being the longest string that can be stored. Length must be between 1 and 255.
TEXT	Variable-length character string with a maximum length of 64K.
INT (length)	Integer range: -2147483648 to +2147483647. If length is 4, only numbers from -999 to 9999 can be displayed , even though higher numbers are stored.
BIGINT	A larger integer. The signed range is -9223372036854775808 to 9223372036854775808.
DECIMAL (length, dec)	Decimal number: length is number of characters, dec is the maximum number of decimal places. Ex: 12.34 has a length of 5 and dec value of 2.
DATE	Date value of year, month and day; displayed as YYYY-MM-DD.
TIME	Time value of hours, minutes and seconds; displayed as HH:MM:SS.
DATETIME	Date and time stored together; displayed as YYYY-MM-DD HH:MM:SS.
ENUM (“val1”, “val2”, …)	Only the values listed can be stored. A maximum of 65,535 values can be listed. Ex: ENUM (“Yes”, “No”)
SERIAL	A shortcut name for: BIGINT UNSIGNED NOT NULL AUTO_INCREMENT.

2. Definitions:

NOT NULL	This column must have a value; it can’t be empty.
DEFAULT value	The value is stored in the column when the row is created if no other value is given for this column.
AUTO_INCREMENT	This definition will create a sequence number. As each row is added, the value of this column increases by one integer. You can override the auto number by assigning a specific value.
UNSIGNED	Used for numeric values that will never be negative numbers. Ex: An unsigned integer can have the range of 0 to 4294967295.

• The PRIMARY KEY can contain a combination of columns (loginName, loginTime).

DESCRIBE tableName

• See the structure of the specified table.

DROP DATABASE databaseName

• Use this command carefully since it’s irreversible. The database will be gone forever, including all stored data.

DROP TABLE tableName

• Use this command carefully since it’s irreversible. The table will be gone forever, including all stored data.

SHOW DATABASES

SHOW TABLES

• See the tables that have been added to a database.

Adding data into databases:

INSERT INTO tableName (columnNmae, columnName, … , columnName)

VALUES (value, value, … , value)

· Example:

INSERT INTO tblMembers (loginName, password, createDate, lastName, fristName, fax)

VALUES (“nester”, “password”, “2007-Dec-2”, “Smith”, “Bill”, “”)

• The following rules apply to the INSERT query:

1. Values must be listed in the same order in which the column names are listed.

2. A partial column list is allowed. Columns not listed are given their default value or left blank.

3. A column list is not required. If no columns are listed, MySQL will look for values for all the columns, in the order which they appear in the table.

4. The column list and value list must be the same length. If the list of columns is longer or shorter than the list of values, you get an error message.

5. If the NOT NULL definition is assigned to a column, and no value is entered for that column, you will receive an error since MySQL will not allow this.

LOAD DATA INFILE “path/fileName” INTO TABLE tableName

LOAD DATA LOCAL INFILE “path/fileName” INTO TABLE tableName

• Note: To use LOAD DAT INFILE query, the user must have the FILE privilege on the server host.
• The LOAD SQL Query reads data from a text file. By Default, MySQL looks for a tab character to separate the fields and the end of line is expected to be the end of the row.
• The LOCAL command is used to load text file data from a local computer.

1. The path/filename must include a path to the file. Ex: “c:/data/datafile1.txt”

2. If an error message is received, ensure LOCAL is enabled.

• Optional phrases if you want to change a default delimiter:

FIELDS TERMINATED BY ‘character’

FIELDS ENCLOSED BY ‘character’

LINES TERMINATED BY ‘character’

Ex: LOAD DATA INFILE “path/fileName” INTO TABLE tableName

FIELDS TERMINATED BY ‘,’

Retrieving data in databases:

SELECT * FROM tableName

• This query retrieves all the information from the table. The asterisk ( * ) is a wildcard meaning all the columns.
1. You can request only the information (the columns) that you need to answer your question.

SELECT columnName, columnName, … FROM tableName

2. You can request information in a particular order.
3. You can request information from selected objects (rows) in your table.
• You can nest a SELECT query inside another query. This nested query is called a subquery. You can use a subquery in SELECT, INSERT, UPDATE, or DELETE queries or in SET clauses.
• Examples:

SELECT lastName,firstName FROM tblMembers

• Retrieves all the last names and first names stored in the table.

SELECT price,price*1.08 FROM tblSales

• Retrieves the price and price with the sales tax rate of 8% stored in the table.

SELECT price,price*1.08 AS priceWithTax FROM tblSales

• Retrieves the price and price with the sales tax rate of 8% stored in the table. The second column is given the name priceWithTax, thus the query retrieves two columns of data: price and priceWithTax.

• When you want to know something about the column you can use the following SQL Functions:

AVG (columnName)	Returns the average of all the values in columnName.
COUNT (columnName)	Returns the number of rows in which columnName is not blank.
MAX (columnName)	Returns the largest value in ColumnName.
MIN (columnName)	Returns the smallest value in ColumnName.
SUM (columnName)	Returns the sum of all the values in ColumnName.
SQRT (columnName)	Returns the square root of each value in the ColumnName.
DAYNAME (columnName)	Returns the week day name for each value in ColumnName.

• Example:

SELECT MAX(price) FROM tblSales

SELECT * FROM tableName WHERE expression AND|OR expression AND|OR expression …

• Examples of expressions:
1. zip=”12345”, zip > “50000”, Zip >= “50000”, zip < “50000”, zip <= “50000”
2. zip BETWEEN “20000” AND “30000”
3. zip IN (“90210”, “30445”)
1. Selects only the rows where the zip code is the selected values.
4. zip NOT IN (“90210”, “30445”)
1. Selects only the rows where the zip code is any zip except the selected values.
5. zip LIKE “9%”
1. Selects all rows where the zip code begins with 9.
6. zip NOT LIKE “9%”
1. Selects all rows where the zip code does not begin with 9.
• Example:

SELECT lastName,firstName FROM tblMembers

WHERE lastName LIKE “B%”

AND city = “Santa Barbara”

AND (phone LIKE “%8%” OR fax LIKE “%8%”

• WHERE: Allows you to request information from database objects with certain characteristics.
• LIMIT: Allows you to limit the number of rows from which information is retrieved.

LIMIT startNumber,numberOfRows

1. If the startNumber is not specified, 1 becomes the default value.

2. Example:

SELECT * FROM tblMembers WHERE state=”TX” LIMIT 3

(Used to select only the first 3 members who live is TX.)

• DISTINCT: Allows you to request information from only one row of identical rows.
• ORDER BY: Sort retrieved data.

Example:

SELECT DISTINCT petType FROM tblPet ORDER BY petType

Combining information from tables:

SELECT query UNION ALL SELECT query

• UNION: Rows are retrieved from one or more tables and stored together, one after the other, in a dingle result.
1. You can combine as many SELECT queries as you need. A SELECT query can include any valid SELECT format, including WHERE and LIMIT clauses, and so on.
2. Rules for the queries:
1. All the SELECT queries must select the same number of columns.
2. The columns selected in the queries must contain the same type of data.
3. The result set will contain all the rows from the first query followed by all the rows from the second query and so on. The column names used in the result set are the column names from the first SELECT query.
• Example:

SELECT lastName,firstName FROM tblMembers UNION ALL

SELECT lastName,firstName FROM tblOldMembers

1. Note: The inclusion of ALL can create duplicate records if contained on both tables. To eliminate duplicate records, don’t include ALL in your query.

• Same Example with records sorted:

(SELECT lastName,firstName FROM tblMembers UNION ALL

SELECT lastName,firstName FROM tblOldMembers)

ORDER BY lastName

1. Note: Using the parentheses as above, the ORDER BY query will apply to the entire result set.

• JOIN: The tables are combined side by side, and the information is retrieved from both tables.
1. Tables are combined by matching data in a column – the column that they have in common.
1. For example, if one table has two columns (memberID and height) and the second table has two columns (memberID and weight) , a join results in a table with four columns: memberID (from 1^st table), height, memberID (from 2^nd Table), and weight.
2. The two common types of joins:
1. Inner join: The results table produced by an inner join contains only rows that existed in both tables.

Example:

SELECT columnNameList FROM table1, table2

WHERE table1.col2 = table2.col2

2. Outer join: The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for rows that did not exist in the second table.

Example: If table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain only one row: the row for Sally. However, an outer join would contain two rows – a row for Joe and a row for Sally – even though the row for Joe would have a blank field for weight.

3. The two kinds of outer joins control which table sets the rows and which column match: a LEFT JOIN and RIGHT JOIN.

Example:

SELECT columnNameList FROM table1 LEFT JOIN table2

ON table1.col2 = table2.col2

SELECT columnNameList FROM table1 RIGHT JOIN table2

ON table1.col2 = table2.col2

4. Example to list all the login names in tblMembers that are not in tblLogin:

SELECT loginName FROM tblMembers LEFT JOIN tblLogin

ON tblMembers.loginName=tblLogin.loginName

WHERE tblLogin.loginName IS NULL

Updating information:

UPDATE tableName SET columnName=value, columnName=value, …

WHERE clause

• Changing information in an existing row is updating the information.
• In the SET clause, you list the columns to be updated and the new values to be inserted. Without the WHERE clause, the values of the column(s) would be changed in all rows.
• Example, Updating an address in tblMembers:

UPDATE tblMembers SET addr=”1234 Main St.”, hPhone=”123-456-7890”

WHERE loginName=”jsmith”

Removing information:

DELETE FROM tableName WHERE clause

• Used to delete a row from a table.
• CAUTION: Failure to use the WHERE clause will delete all the data in the table – permanently!