https://www.w3schools.com/sql/sql_in.
asp
SQL List All tables (sqltutorial.org)
http://localhost/phpmyadmin/
Table of Contents
1. login to the database server ................................................................................................................. 2
2. Create Database .................................................................................................................................... 2
3. Show Databases .................................................................................................................................... 2
4. Use databases ....................................................................................................................................... 2
5. Delete database .................................................................................................................................... 2
6. Create Tables ........................................................................................................................................ 2
7. SQL command to list all tables in MySQL .............................................................................................. 4
8. Describe the Tables (List its columns' definition) ................................................................................. 4
9. Delete Table(Relation) .......................................................................................................................... 4
10. TRUNCATE TABLE .............................................................................................................................. 4
11. ALTER TABLE (Used to add an attribute to one of the base relations) ............................................. 4
12. ALTER TABLE - DROP COLUMN ......................................................................................................... 4
13. ALTER TABLE - ALTER/MODIFY COLUMN .......................................................................................... 4
14. INSERT tuples in Relations ................................................................................................................ 5
15. SQL Insert Multiple Rows .................................................................................................................. 5
16. DELETE tuples from Relation............................................................................................................. 5
17. UPDATE ............................................................................................................................................. 6
1. login to the database server
>mysql -u root -p
Enter password: root
mysql>
2. Create Database
Syntax:
CREATE DATABASE <databasename>;
Example:
CREATE DATABASE COMPANY;
3. Show Databases
mysql> SHOW DATABASES;
SHOW SCHEMAS;
4. Use databases
Syntax: Use <databasename>;
USE COMPANY;
5. Delete database
Syntax: DROP DATABASE <name>;
DROP DATABASE COMPANY;
6. Create Tables
CREATE TABLE EMPLOYEE(
Ssn INT NOT NULL,
Fname varchar(25),
Mnit varchar(25),
Lname varchar(25),
Bdate date,
Address varchar(255),
Sex char(1),
Salary decimal(6,2),
Super_ssn int,
Dno INT,
PRIMARY KEY (Ssn));
--------------------------------------
CREATE TABLE DEPARTMENT (
Dname VARCHAR(10) NOT NULL,
Dnumber INT NOT NULL,
Mgr_ssn INT,
Mgr_start_date date,
PRIMARY KEY (Dnumber));
-----------------------------
CREATE TABLE PROJECT (
Pname VARCHAR(10) NOT NULL,
Pnumber INTEGER NOT NULL,
Plocation CHAR(9),
Dnum INTEGER NOT NULL,
PRIMARY KEY (Pnumber);
---------------------------
CREATE TABLE DEPENDENT (
Essn int NOT NULL,
Dependent_name varchar(25),
Sex CHAR(1),
Bdate date,
Relationship VARCHAR(25),
PRIMARY KEY (Essn,Dependent_name);
-------------------------
CREATE TABLE DEPT_LOCATIONS (
Dnumber INT NOT NULL,
Dlocation CHAR(9),
PRIMARY KEY (Dnumber);
----------------------------
CREATE TABLE WORKS_ON (
Essn INT NOT NULL,
Pno INT NOT NULL,
Hours INT,
PRIMARY KEY (Essn,Pno);
----------------------------------
- Adding the Foreign Key Constraint
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (Dno) REFERENCES DEPARTMENT (Dnumber);
Or
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber);
ON DELETE CASCADE
ON UPDATE CASCADE;
7. SQL command to list all tables in MySQL
SHOW TABLES
8. Describe the Tables (List its columns' definition)
describe EMPLOYEE;
----------------------
9. Delete Table(Relation)
The DROP TABLE command deletes a table in the database.
DROP TABLE <Table name>;
10.TRUNCATE TABLE
The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.
Example
TRUNCATE TABLE EMPLOYEE;
11.ALTER TABLE (Used to add an attribute to one of the base relations)
Syntax:
ALTER TABLE table_name
ADD column_name datatype;
Example:
ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);
12.ALTER TABLE - DROP COLUMN
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
13.ALTER TABLE - ALTER/MODIFY COLUMN
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
Or
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
14.INSERT tuples in Relations
INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', '653298653', '30-DEC-52','98 Oak Forest,Katy,TX',
'M', 37000,'987654321', 4 );
INSERT INTO EMPLOYEE (Fname, Lname, Ssn)
VALUES ('Richard', 'Marini', '653298653');
15.SQL Insert Multiple Rows
CREATE TABLE customer (
first_name VARCHAR(100),
last_name VARCHAR(100)
);
Now, we can INSERT multiple rows in SQL by repeating the list of values inside the brackets:
INSERT INTO customer (first_name, last_name) VALUES
('Kristen', 'Rowley'),
('Jed', 'Tomlinson'),
('Margie', 'Escobar'),
('Harriette', 'Mejia'),
('Francis', 'Little');
16.DELETE tuples from Relation
Delete Tuples from Relation:
DELETE FROM table
WHERE condition;
DELETE FROM EMPLOYEE WHERE Lname='Brown’;
DELETE FROM EMPLOYEE WHERE Ssn='123456789’;
DELETE FROM EMPLOYEE WHERE Dno IN
(SELECT Dnumber
FROM DEPARTMENT
WHERE Dname='Research’);
(To remove all rows in the employees table)
DELETE FROM EMPLOYEE;
17.UPDATE
UPDATE PROJECT
SET Plocation='Bellaire', Dnum = 5
WHERE Pnumber=10;
