Scribd
Upload
4 views5 pages

L2 MySQL Inserting, Retrieving Data, Not Null and Default

The document provides SQL commands for creating and manipulating tables, specifically for 'cats' and 'people'. It includes examples of inserting single and multiple records, using NOT NULL and DEFAULT constraints, and querying data. Additionally, it demonstrates how to drop tables and view their structure and contents.

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
4 views5 pages

L2 MySQL Inserting, Retrieving Data, Not Null and Default

The document provides SQL commands for creating and manipulating tables, specifically for 'cats' and 'people'. It includes examples of inserting single and multiple records, using NOT NULL and DEFAULT constraints, and querying data. Additionally, it demonstrates how to drop tables and view their structure and contents.

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

-- Create the cats table

​ CREATE TABLE cats (


​ name VARCHAR(50),
​ age INT
​ );
Insert a cat:

​ INSERT INTO cats (name, age)


​ VALUES ('Blue', 5);
And another:

​ INSERT INTO cats (name, age)


​ VALUES ('Jenkins', 7);

___________________________________________________________________

To view all rows in our table:

SELECT * FROM cats;

________________________________________________________________________

-- Single insert (switching order of name and age)

​ INSERT INTO cats (age, name)


​ VALUES
​ (2, 'Beth');
​
-- Multiple Insert:

​ INSERT INTO cats (name, age)


​ VALUES
​ ('Meatball', 5),
​ ('Turkey', 1),
​ ('Potato Face', 15);
Insert exercise

Create a people database having the following columns


●​ first_name - 20 char limit
●​ last_name - 20 char limit
●​ age

Insert first person

Insert second person

Multiple inserts

Solution

​ CREATE TABLE people


​ (
​ first_name VARCHAR(20),
​ last_name VARCHAR(20),
​ age INT
​ );
​
​ INSERT INTO people(first_name, last_name, age)
​ VALUES ('Tina', 'Belcher', 13);
​
​ INSERT INTO people(age, last_name, first_name)
​ VALUES (42, 'Belcher', 'Bob');4
​
​ INSERT INTO people(first_name, last_name, age)
​ VALUES
​ ('Linda', 'Belcher', 45),
​ ('Phillip', 'Frond', 38),
​ ('Calvin', 'Fischoeder', 70);
DROP TABLE people;

SELECT * FROM people;

SHOW TABLES;

__________________________________________________________
NOT NULL constraint

By default table columns allow null values i.e. while inserting if we we skip the value for
a particular column that cell will be empty (null).

​ CREATE TABLE cats (


​ name VARCHAR(50),
​ age INT
​ );

​ INSERT INTO cats (name, age)


​ VALUES
​ ('Meatball', 5),
​ ('Turkey', 1);

​ INSERT INTO cats (name)


​ VALUES ('Potato Face');
Age has not been entered for the third cat. So, the age will be null for ‘Potato
Face’.

​ ​

To ensure that a particular column should not accept null values


we use the NOT NULL constraint.

​ ​ CREATE TABLE cats (


​ ​ ​ name VARCHAR(50),
​ ​ ​ age INT Not Null
);

_______________________________________________________

DEFAULT constraint

Define a table with a DEFAULT name specified:

​ CREATE TABLE cats3 (


​ name VARCHAR(20) DEFAULT 'no name provided',
​ age INT DEFAULT 99
​ );
Notice the change when you describe the table:

DESC cats3;

Insert a cat without a name:

INSERT INTO cats3(age) VALUES(13);

Or a nameless, ageless cat:


INSERT INTO cats3() VALUES();

Combine NOT NULL and DEFAULT:

​ CREATE TABLE cats4 (


​ name VARCHAR(20) NOT NULL DEFAULT 'unnamed',
​ age INT NOT NULL DEFAULT 99
​ );

You might also like