Scribd
Upload
11 views

MySQL queries

The document provides a series of MySQL commands for managing a database named 'southwind', including creating and deleting the database and tables. It demonstrates how to create a 'products' table, insert rows, and perform basic queries such as selecting and deleting data. Additionally, it covers showing the current database and table structures.

Uploaded by

dnyaneshcr
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
11 views

MySQL queries

The document provides a series of MySQL commands for managing a database named 'southwind', including creating and deleting the database and tables. It demonstrates how to create a 'products' table, insert rows, and perform basic queries such as selecting and deleting data. Additionally, it covers showing the current database and table structures.

Uploaded by

dnyaneshcr
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/ 3

www.javabykiran.

com Mob:8888809416

MySQL Queries
mysql> DROP DATABASE southwind;

mysql> CREATE DATABASE IF NOT EXISTS southwind;

mysql> DROP DATABASE IF EXISTS southwind;

SHOW CREATE DATABASE


mysql> SHOW CREATE DATABASE southwind \G
Setting the Default Database
mysql>USE southwind;

Creating and Deleting a Table


-- Show the current (default) database
mysql> SELECT DATABASE();

-- Show all the tables in the current database.


mysql> SHOW TABLES;

-- Create the table "products".


mysql> CREATE TABLE IF NOT EXISTS products (
productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
productCode CHAR(3) NOT NULL DEFAULT '',
name VARCHAR(30) NOT NULL DEFAULT '',
quantity INT UNSIGNED NOT NULL DEFAULT 0,
price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
PRIMARY KEY (productID)
);

-- Show all the tables to confirm that the "products" table has been
created
mysql> SHOW TABLES;

-- Describe the fields (columns) of the "products" table


mysql> DESCRIBE products;

-- Show the complete CREATE TABLE statement used by MySQL to create


this table
1
www.javabykiran.com Mob:8888809416

mysql> SHOW CREATE TABLE products \G

Inserting Rows
-- Insert a row with all the column values
mysql> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000,
1.23);

-- Insert multiple rows in one command


-- Inserting NULL to the auto_increment column results in max_value + 1
mysql> INSERT INTO products VALUES
(NULL, 'PEN', 'Pen Blue', 8000, 1.25),
(NULL, 'PEN', 'Pen Black', 2000, 1.25);

-- Insert value to selected columns


-- Missing value for the auto_increment column also results in max_value
+1
mysql> INSERT INTO products (productCode, name, quantity, price)
VALUES
('PEC', 'Pencil 2B', 10000, 0.48),
('PEC', 'Pencil 2H', 8000, 0.49);

-- Missing columns get their default values


mysql> INSERT INTO products (productCode, name) VALUES ('PEC',
'Pencil HB');

-- 2nd column (productCode) is defined to be NOT NULL


mysql> INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);

--show table
mysql> SELECT * FROM products;

-- Remove the specific row


mysql> DELETE FROM products WHERE productID = 1006;

-- List all rows for the specified columns


mysql> SELECT name, price FROM products;

SELECT without Table


You can also issue SELECT without a table. For example, you
can SELECT an expression or evaluate a built-in function.
2
www.javabykiran.com Mob:8888809416

You might also like