Skip to main content

MySQL practical Tutorials part 2- inserting data into tables, CRUD, multiple inserts, warnings, primary key, not null, auto increment

 CODE: Inserting Data

Inserting Data


The "formula":


INSERT INTO table_name(column_name) VALUES (data);

For example:


INSERT INTO cats(name, age) VALUES ('Jetson', 7);

SELECT * FROM cats; 


INSERT INTO table_name 

            (column_name, column_name) 

VALUES      (value, value), 

            (value, value), 

            (value, value);

INSERT Challenge Solution Code

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');

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; 


MySQL Warnings Code

DESC cats; 


Try Inserting a cat with a super long name:


INSERT INTO cats(name, age)

VALUES('This is some text blah blah blah blah blah text text text something about cats lalalalal meowwwwwwwwwww', 10);

Then view the warning:


SHOW WARNINGS; 


Try inserting a cat with incorrect data types:


INSERT INTO cats(name, age) VALUES('Lima', 'dsfasdfdas'); 


Then view the warning:


SHOW WARNINGS; 



NULL and NOT NULL Code

Try inserting a cat without an age:


INSERT INTO cats(name) VALUES('Alabama'); 


SELECT * FROM cats; 


Try inserting a nameless and ageless cat:


INSERT INTO cats() VALUES(); 



Define a new cats2 table with NOT NULL constraints:




CREATE TABLE cats2

  (

    name VARCHAR(100) NOT NULL,

    age INT NOT NULL

  );

DESC cats2; 


Now try inserting an ageless cat:


INSERT INTO cats2(name) VALUES('Texas'); 


View the new warnings:


SHOW WARNINGS; 


SELECT * FROM cats2; 


Do the same for a nameless cat:


INSERT INTO cats2(age) VALUES(7); 


SHOW WARNINGS; 


CODE: Setting Default Values 

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

  );

  

Notice The Difference:


INSERT INTO cats() VALUES();

 

SELECT * FROM cats;

 

INSERT INTO cats3() VALUES();

 

SELECT * FROM cats3;

 

INSERT INTO cats3(name, age) VALUES('Montana', NULL);

 

SELECT * FROM cats3;

 

INSERT INTO cats4(name, age) VALUES('Cali', NULL);



 CODE: Primary Keys

Define a table with a PRIMARY KEY constraint:


CREATE TABLE unique_cats

  (

    cat_id INT NOT NULL,

    name VARCHAR(100),

    age INT,

    PRIMARY KEY (cat_id)

  );

DESC unique_cats; 


Insert some new cats:


INSERT INTO unique_cats(cat_id, name, age) VALUES(1, 'Fred', 23);

 

INSERT INTO unique_cats(cat_id, name, age) VALUES(2, 'Louise', 3);

 

INSERT INTO unique_cats(cat_id, name, age) VALUES(1, 'James', 3);

Notice what happens:


SELECT * FROM unique_cats; 


Adding in AUTO_INCREMENT:


CREATE TABLE unique_cats2 (

    cat_id INT NOT NULL AUTO_INCREMENT,

    name VARCHAR(100),

    age INT,

    PRIMARY KEY (cat_id)

);

INSERT a couple new cats:


INSERT INTO unique_cats2(name, age) VALUES('Skippy', 4);

INSERT INTO unique_cats2(name, age) VALUES('Jiff', 3);

INSERT INTO unique_cats2(name, age) VALUES('Jiff', 3);

INSERT INTO unique_cats2(name, age) VALUES('Jiff', 3);

INSERT INTO unique_cats2(name, age) VALUES('Skippy', 4);

Notice the difference:


SELECT * FROM unique_cats2; 


Table Constraints Exercise Solution

Defining The employees table:


CREATE TABLE employees (

    id INT AUTO_INCREMENT NOT NULL,

    first_name VARCHAR(255) NOT NULL,

    last_name VARCHAR(255) NOT NULL,

    middle_name VARCHAR(255),

    age INT NOT NULL,

    current_status VARCHAR(255) NOT NULL DEFAULT 'employed',

    PRIMARY KEY(id)

);

Another way of defining a primary key:




CREATE TABLE employees (

    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

    first_name VARCHAR(255) NOT NULL,

    last_name VARCHAR(255) NOT NULL,

    middle_name VARCHAR(255),

    age INT NOT NULL,

    current_status VARCHAR(255) NOT NULL DEFAULT 'employed'

);

A test INSERT:


INSERT INTO employees(first_name, last_name, age) VALUES

('Dora', 'Smith', 58);


INSERT INTO cats(name, age) VALUES(‘Taco’, 14); 


CODE: Preparing Our Data

Let's drop the existing cats table:


DROP TABLE cats; 


Recreate a new cats table:


CREATE TABLE cats 

  ( 

     cat_id INT NOT NULL AUTO_INCREMENT, 

     name   VARCHAR(100), 

     breed  VARCHAR(100), 

     age    INT, 

     PRIMARY KEY (cat_id) 

  ); 

DESC cats; 


And finally insert some new 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);

Various Simple SELECT statements:

SELECT * FROM cats; 


SELECT name FROM cats; 


SELECT age FROM cats; 


SELECT cat_id FROM cats; 


SELECT name, age FROM cats; 


SELECT cat_id, name, age FROM cats; 


SELECT age, breed, name, cat_id FROM cats; 


SELECT cat_id, name, age, breed FROM cats; 


CODE: Introduction to WHERE

Select by age:


SELECT * FROM cats WHERE age=4; 


Select by name:


SELECT * FROM cats WHERE name='Egg'; 


Notice how it deals with case:


SELECT * FROM cats WHERE name='egG';


CODE: Select Challenges 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; 


CODE: Introduction to Aliases


SELECT cat_id AS id, name FROM cats;

 

SELECT name AS 'cat name', breed AS 'kitty breed' FROM cats;

 

DESC cats;



CODE: Updating Data

Change tabby cats to shorthair:


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


Another update:


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


CODE: Update Challenges Solution

SELECT * FROM cats WHERE name='Jackson';

 

UPDATE cats SET name='Jack' WHERE name='Jackson';

 

SELECT * FROM cats WHERE name='Jackson';

 

SELECT * FROM cats WHERE name='Jack';

 

SELECT * FROM cats WHERE name='Ringo';

 

UPDATE cats SET breed='British Shorthair' WHERE name='Ringo';

 

SELECT * FROM cats WHERE name='Ringo';

 

SELECT * FROM cats;

 

SELECT * FROM cats WHERE breed='Maine Coon';

 

UPDATE cats SET age=12 WHERE breed='Maine Coon';

 

SELECT * FROM cats WHERE breed='Maine Coon';



CODE: DELETING DATA

DELETE FROM cats WHERE name='Egg';

 

SELECT * FROM cats;

 

SELECT * FROM cats WHERE name='egg';

 

DELETE FROM cats WHERE name='egg';

 

SELECT * FROM cats;

 

DELETE FROM cats;


SELECT * FROM cats WHERE age=4;

 

DELETE FROM cats WHERE age=4;

 

SELECT * FROM cats WHERE age=4;

 

SELECT * FROM cats;

 

SELECT *  FROM cats WHERE cat_id=age;

 

DELETE FROM cats WHERE cat_id=age;

 

DELETE FROM cats;

 

SELECT * FROM cats;


CODE: CRUD Exercise Create Solution

SELECT database();

 

CREATE DATABASE shirts_db;

 

use shirts_db;

 

SELECT database();

 

CREATE TABLE shirts

  (

    shirt_id INT NOT NULL AUTO_INCREMENT,

    article VARCHAR(100),

    color VARCHAR(100),

    shirt_size VARCHAR(100),

    last_worn INT,

    PRIMARY KEY(shirt_id)

  );

 

DESC shirts;

 

INSERT INTO shirts(article, color, shirt_size, last_worn) VALUES

('t-shirt', 'white', 'S', 10),

('t-shirt', 'green', 'S', 200),

('polo shirt', 'black', 'M', 10),

('tank top', 'blue', 'S', 50),

('t-shirt', 'pink', 'S', 0),

('polo shirt', 'red', 'M', 5),

('tank top', 'white', 'S', 200),

('tank top', 'blue', 'M', 15);

 

SELECT * FROM shirts;

 

INSERT INTO shirts(color, article, shirt_size, last_worn) 

VALUES('purple', 'polo shirt', 'medium', 50);

 

SELECT * FROM shirts;



CODE: CRUD Exercise Read Solution

SELECT article, color FROM shirts;

 

SELECT * FROM shirts WHERE shirt_size='M';

 

SELECT article, color, shirt_size, last_worn FROM shirts WHERE shirt_size='M';


CODE: CRUD Exercise Update Solution

SELECT * FROM shirts WHERE article='polo shirt';

 

UPDATE shirts SET shirt_size='L' WHERE article='polo shirt';

 

SELECT * FROM shirts WHERE article='polo shirt';

 

SELECT * FROM shirts;

 

SELECT * FROM shirts WHERE last_worn=15;

 

UPDATE shirts SET last_worn=0 WHERE last_worn=15;

 

SELECT * FROM shirts WHERE last_worn=15;

 

SELECT * FROM shirts WHERE last_worn=0;

 

SELECT * FROM shirts WHERE color='white';

 

UPDATE shirts SET color='off white', shirt_size='XS' WHERE color='white';

 

SELECT * FROM shirts WHERE color='white';

 

SELECT * FROM shirts WHERE color='off white';

 

SELECT * FROM shirts;


 CODE: CRUD Exercise Delete Solution

SELECT * FROM shirts;

 

SELECT * FROM shirts WHERE last_worn=200;

 

DELETE FROM shirts WHERE last_worn=200;

 

SELECT * FROM shirts WHERE article='tank top';

 

DELETE FROM shirts WHERE article='tank top';

 

SELECT * FROM shirts WHERE article='tank top';

 

SELECT * FROM shirts;

 

DELETE FROM shirts;

 

SELECT * FROM shirts;

 

DROP TABLE shirts;

 

show tables;

 

DESC shirts;

CREATE TABLE cats

    (

        cat_id INT NOT NULL AUTO_INCREMENT,

        name VARCHAR(100),

        age INT,

        PRIMARY KEY(cat_id)

    );

DESC cats;

Labels:
Location: Ahmednagar, Maharashtra, India

Comments

Post a Comment

Popular posts from this blog

Add, remove, search an item in listview in C#

Below is the C# code which will help you to add, remove and search operations on listview control in C#. Below is the design view of the project: Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace Treeview_control_demo {     public partial class Form2 : Form     {         public Form2()         {             InitializeComponent();             listView1.View = View.Details;                   }         private void button1_Click(object sender, EventArgs e)         {             if (textBox1.Text.Trim().Length == 0)...
Post a Comment
Read more

display files and directories in Listview

Below is the C# code which displays all files and directories in listview control with their file size and creation date. If it is file then it also displays the extension of the file e.g. .txt, .jpg etc Below is the design view of the project: Listview to display files and directories with size and date created Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.IO; namespace search_in_listview {     public partial class Form1 : Form     {         public Form1()         {             InitializeComponent();                   }         private void button1_Click(object sender, EventArgs ...
Post a Comment
Read more

Add worklog in Jira using Python

 Below is the Python code to add the worklog in Jira. You need to install a request library for this. Here is the code: import requests from requests.auth import HTTPBasicAuth import json url = "https://your jira address here/rest/api/2/issue/ticket_number/worklog" auth = HTTPBasicAuth("username", "jira access token") headers = {     "Accept": "application/json",     "Content-Type": "application/json" } payload = json.dumps({     "comment": {         "content": [             {                 "content": [                     {                         "text": "This is for QA Testing",                         "type": "text"                     } ...
Post a Comment
Read more