[go: up one dir, main page]
Scribd
Upload
5 views5 pages

l4 Mysql Crud Basics

The document explains CRUD operations (Create, Read, Update, Delete) in the context of a 'cats' database. It provides SQL commands for creating a table, inserting data, querying with SELECT statements, using WHERE clauses, and updating records. Additionally, it discusses the use of aliases for renaming columns in query results.

Uploaded by

rajsinghcool0708
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views5 pages

l4 Mysql Crud Basics

The document explains CRUD operations (Create, Read, Update, Delete) in the context of a 'cats' database. It provides SQL commands for creating a table, inserting data, querying with SELECT statements, using WHERE clauses, and updating records. Additionally, it discusses the use of aliases for renaming columns in query results.

Uploaded by

rajsinghcool0708
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

CRUD is an acronym for Create, Read, Update and Delete.

________________________________________________________

Create table and insert data for queries

-- Drop the current cats table (if you have one)

DROP TABLE cats;

-- Create the new cats table:

​ CREATE TABLE cats (


​ cat_id INT AUTO_INCREMENT,
​ name VARCHAR(100),
​ breed VARCHAR(100),
​ age INT,
​ PRIMARY KEY (cat_id)
​ );
-- Insert some cats:

​ INSERT INTO cats(name, breed, age)


​ VALUES ('Ringo', 'Tabby', 4),
​ ('Cindy', 'Maine Coon', 10),
​ ('Dumbledore', 'Maine Coon', 11),
​ ('Egg', 'Persian', 4),
​ ('Misty', 'Tabby', 13),
​ ('George Michael', 'Ragdoll', 9),
​ ('Jackson', 'Sphynx', 7);

____________________________________________________________

Read

-- To get all the columns:

SELECT * FROM cats;

-- To only get the age column:


SELECT age FROM cats;

-- To select multiple specific columns:

SELECT name, breed FROM cats;

_______________________________________________________

WHERE Clause

-- Use where to specify a condition:

SELECT * FROM cats WHERE age = 4;

SELECT name FROM cats WHERE age = 4;

SELECT * FROM cats WHERE name ='Egg';

_______________________________________________________

Exercise
Breed = ‘Tabby’
Solution

SELECT cat_id FROM cats;

SELECT name, breed FROM cats;

SELECT name, age FROM cats WHERE breed='Tabby';

SELECT cat_id, age FROM cats WHERE cat_id=age;

SELECT * FROM cats WHERE cat_id=age;

_______________________________________________________________

Aliases (Renaming the column in the results table, not


the actual table)

-- Use 'AS' to alias a column in your results (it doesn't actually change the name of the
column in the table)

SELECT cat_id AS id, name FROM cats;

_____________________________________________________________

UPDATE

Change tabby cats to shorthair:

UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';

Another update:

UPDATE cats SET age=14 WHERE name='Misty';

If the where clause is not used in the above queries, all the
values in the column (being set) will be set to that particular
value.
To prevent accidental updation of the entire column, the values
should be first checked with a select statement before being
updated.

For example

SELECT * FROM cats WHERE breed='Tabby';

This will show the result of the selection. If the resultant


rows are the ones where we actually want to update the breen,
then we can run the updated query.

UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';

__________________________________________________________

You might also like