#Mysql tutorial code#

SELECT id AS i, first_name AS fn, last_name AS ln, pet_food_cost AS pfc, pet_food_bags AS pfb FROM testtable ДЄ new user can be created with CREATE USER IDENTIFIED BY 'newuserpassword'. SELECT id, first_name, last_name, pet_food_cost, pet_food_bags, pet_food_cost*pet_food_bags AS total_cost FROM testtable WHERE id >= 2 SELECT id, first_name, last_name, pet_food_cost, pet_food_bags FROM testtable WHERE id >= 2 Note that the column created with As does not get added to the table. The command is SELECT columnnames, desiredcolumn1+desiredcolumn2 AS tempcolumn FROM tablename WHERE columnname = desiredvalue where WHERE is optional as always. You can use other mathematical operations such as addition (+), subtraction (-), multiplication (*), and division (/). The As statement below will create a temporary column named total_cost that calculates the total cost of the pet food. I have added another column named pet_food_bags that states the number of pet food bags that were bought by the customer. The As statement is very useful when one is trying to create columns from a combination of other columns or trying to abbreviate column names. Note that the last column of the table does not have a comma before ending the command with ) on the next line. The pet_favorite_food column does not have the NOT NULL statement and by default can be empty NULL.

#Mysql tutorial code#

The code below uses DECUMAL(4,2) which indicates a number value of AAAA.BB for the column pet_food_cost. The column pet_food_cost uses the DECIMAL(A,B) data type where A is the number of digits to the left of the decimal and B is the number of digits to the right of the decimal. The column pet_type uses the enumerated ENUM('list','of','choices') data type which requires the data entered to be from the list of choices given. The has_pet column is a BIT data type which can be 1 for true or 0 for false. I have made these two columns NOT NULL even though they can be NULL. The username and email must be UNIQUE but can be NULL unlike the PRIMARY KEY. The number used is 2 since we know the abbreviations for states are 2 characters such as CA, NM, and NY. The column state uses the CHAR(#) data type with the # being the number of characters allowed. The date_of_birth column is a DATE data type with the format YYYY-MM-DD. The column first_name and last_name have the data type VARCHAR(#) which is a variable length string with the number # being the maximum number of characters. No other row can have the same id and it cannot be empty. The option PRIMARY KEY makes id the unique identifier for each row. I have made the column id an integer INT that must be zero or positive UNSIGNED and cannot be empty NOT NULL. Below is a list of the columns I have created for the table testtable and their description. The data type of the column and any other information for that column is stated after the column name. The left most input in each new line is the column name (i.e. Each new column is separated by a comma, before going to the next line. You must specify the column name and the data type of that column in order to create the column. You can input all the SQL code on one line but it is easier to differentiate each new column by entering them on a new line. The data columns of the table are created within the parentheses of CREATE TABLE tablename(). The command SHOW tables lists the tables in the current database. I have created a table named testtable in order to start inputting data. A table is the object that holds data in a database and can be created by using CREATE TABLE tablename().