SQL 5: Alter: Rewriting the Past

To SELECT the last two characters

Use RIGHT() and LEFT() to select a specified number of characters from a column. CHAR() and VARCHAR() are known for text strings.

RENAME

Let's use DESCRIBE to see how this table is constructed. This shows us if the columns has a primary key and what type of data is being stored in each column. Renaming the column names of an existing table with data: ALTER TABLE projekts RENAME TO project_list Use RENAME to change the name of your table

String Functions

Lets you modify copies of the content of string columns when they are returned from a query. The original values remain untouched: SELECT UPPER (your_string) SELECT LOWER (your_string) SELECT REVERSE (your_string); it reverses the order of letters in your string. SELECT LTRIM(your_string) and SELECT RTRIM(your_string) returns your string with extra spaces removed from before (to the left of) or after (to the right of) a string. SELECT LENGTH(your_string) returns a count of how many characters are in your string. SELECT LENGTH ('San Antonio, TX'); is 14 SELECT SUBSTRING(your_string, start_position, length) gives you part of your_string, starting at the letter in the start_position. Length is how much of the string you get back. SELECT SUBSTRING ('San Antonio, TX', 5, 3); is ton IMPORTANT: string functions do NOT change the data stored in your table; they simply return the altered strings as a result of your query. The string functions can be used in combination with SELECT, UPDATE & DELETE. For example: UPDATE my_contacts SET state = RIGHT ('Chester, NJ', 2);

SELECT-UPDATE-DELETE combination

SET the column name with a SUBSTRING function

A single statement to ALTER the table.

The only time it's easy to change the column order is when I'm adding in a new column

Keywords

AFTER BEFORE LAST FIRST, SECOND, THIRD BEFORE and FIRST orders would not work with MySQL. So instead, I will utilize FIRST and AFTER. You can change the order of your columns using the keywords. With some RDBMSs, you can only change the order of columns in a table when you add them to a table.

How to separate the city and state in one column into two columns?

ALTER TABLE my_contacts ADD COLUMN city VARCHAR(50), ADD COLUMN state CHAR(2); Text values and values stored in CHAR or VARCHAR columns are known as strings. Then use the string function to grab the data from the old table to the new. The statement below shows the syntax for changing the value of every row in a column. In place of newvalue, you can put a value or another column name.

CHANGE COLUMN

ALTER TABLE project_list CHANGE COLUMN descriptionofproj proj_desc VARCHAR (100), CHANGE COLUMN contractoronjob con_name VARCHAR (30); For column names, you may want to label your columns in abbreviations. For example: proj_id, start_date, proj_desc, est_cost, con_name, con_phone You can use CHANGE when you want to change both the name and the data type of a column

ALTER and CHANGE:

ALTER TABLE project_list CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY ('proj_id'); To change the data type of a column and change /keep the name of an existing column: ALTER TABLE myTable CHANGE COLUMN myColumn myColumn NEWTYPE; However, there is a simpler way with the MODIFY keyword.

ALTER with MODIFY

ALTER TABLE project_list MODIFY COLUMN proj_desc VARCHAR (20); The name of the column is being modified to VARCHAR for a total amount of 20 text strings. This will ensure the data will not be truncated! Use MODIFY when you wish to change only the data type. You cannot change the order of the columns but you enter enter a new column with data. In your query, you can select the ORDER BY of the result instead of putting in order of your columns.

When do you use the DROP PRIMARY KEY?

ALTER TABLE your_table DROP PRIMARY KEY; When you would like to alter the primary key, you have already set the primary key in one colunm and you have changed your mind.

ALTER

If you already have a table with values entered, you can alter your table to add another column. ALTER TABLE my_contacts ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (contact_id); This statement is altering the table called my_contact by adding a column labeled contact_id. This column will not have empty values because the database system will create automatic numeric values in the column as the primary key of the table. ALTER TABLE my_contacts ADD COLUMN phone number VARCHAR (10); After the primary key has been determined, the technician has added a column for the phone number in the data type VARCHAR in 10 text strings. ALTER TABLE my_contacts ADD COLUMN phone number VARCHAR (10) AFTER first_name; The technician has added the column phone number after the first_name in the table. Note, when adding a column please indicate the data type. The keyword AFTER is optional in adding a new column. If not indicated, then the statement would automatically add this column at the end of the table.

What occurs when you change your column?

If you change the data type to something you new you may lose data. The SQL software will tell you that have an error in your statement if the data type is not compatible with the old data type. If they are compatible data types, then your data may be truncated for character text strings and numeric values. VARCHAR (10) CHAR (1)

UPDATE

The statement changes the values of every row in the column. UPDATE my_contacts SET state = RIGHT (location, 2); RIGHT (location, 2) is the substring function that grabs the last two characters from the location column.

Commands for Altering Data

To alter an existing table: CHANGE both the name and data type of an existing column * MODIFY the data type or position of an existing column * ADD a column to your table --you pick the data type DROP a column from your table * * Possible loss of data may occur, no guarantees offered. If you change a column of one data type to a different one, you risk losing your data.

To SELECT everything in front of the comma

Use SUBSTRING_INDEX() to grab part of the column, or substring. This one will find everything in front of a specific character or string. So we can put our comma in quotes, and SUBSTRING_INDEX() will select everything in front of it.

How can you add AUTO_INCREMENT to an existing table?

You can add it to a column that doesn't have it like this ALTER TABLE your_table CHANGE your_id your_id INT(11) NOT NULL AUTO_INCREMENT; It is important to keep in mind that you can only have one AUTO_INCREMENT field per table, it has to be an INTEGER data type and it can't contain NULL. And you can remove it like this: ALTER TABLE your_table CHANGE your_idyour_id INT(11) NOT NULL;