Structure Query Language, Study notes of Computer science

Download Structure Query Language and more Study notes Computer science in PDF only on Docsity!

Introduction to MySQL

⚫ MySQL is a fast, easy-to-use RDBMS used for small and big business

applications.

⚫ MySQL is developed, marketed, and supported by a Swedish

Company MySQL AB.

⚫ MySQL is released under an open-source license so it is

customizable. It requires no payment for its usage.

⚫ MySQL is a very powerful software to handle RDBMS.

⚫ MySQL uses a standard form of the well-known ANSI-SQL standards

⚫ MySQL is a platform independent application which works on many

operating systems like Windows, UNIX, LINUX etc. And has

compatibility with many languages including JAVA , C++, PHP,

PERL, etc.

⚫ MySQL is a easy to install RDBMS and capable of handling large

data sets.

Data Definition Language A database scheme is defined by set of definitions, which are expressed, by a special set of commands called Data Definition Language (DDL). For example - defining relation schemas, deleting relations, creating indexes and modifying relation schemas. SQL DDL commands are: -

❖CREATE TABLE

❖ALTER TABLE

❖DROP TABLE

❖CREATE INDEX

❖ALTER INDEX

❖DROP INDEX

Data Manipulation Language

The data manipulation language (DML) handles operations such as entering rows into a table, changing data, deleting rows, and extracting data from rows and tables. It contains commands like ⚫ SELECT ⚫ INSERT ⚫ UPDATE ⚫ DELETE

DATA TYPES IN MYSQL S.No. Data Type Description 1 INT A normal sized signed or unsigned integer ranging from

• 2147483648 to 2147483647 2 FLOAT(M,D) A floating point number where M is the total length and D is number of decimals. If omitted default length is 10 , 2. 3 DECIMAL(M,D) or NUMERIC(M,D) A floating point number 4 DATE Stores date in YYYY-MM-DD format 5 DATETIME Stores date in time in YYYY-MM-DD HH:MM:SS format. 6 TIME Stores the time in HH:MM:SS 7 CHAR(M) A fixed length string format between 1 and 255 characters in length. 8 VARCHAR(M) A variable length string between 1 and 255.

CREATE TABLE Command - Creating Tables

Syntax:

CREATE TABLE <table-name>

(< column name> [],

< column name> [ ],

Example:

mysql> USE school_db;

Database changed

mysql> CREATE TABLE Student(

Rollno int,

Name varchar(15),

Gender char(1),

Marks1 decimal(3,0)

DESCRIBE COMMAND The describe statement can be used to see the structure of the table as indicated in the Create Command,

mysql> DESCRIBE Student;

| Field | Type | Null | Key |Default | Extra | +--------+------------------+------+-----+---------+---------------+ | rollno | decimal(3,0)| | | | | | name | varchar(15) | | | | | | gender | char(1) | | | | | | marks1 | int | | | | | +--------+------------------+------+-----+---------+---------------+ 4 rows in set (0.00 sec)

CONSTRAINTS

As you know that constraints ensures database integrity. Some

constraints are -

❑ Unique constraint – ensure values in a particular column to be

unique.

❑ Not Null – specifies that a column should not have null value in all

rows.

❑ Primary key constraint – declares a column as the primary key of

the table.

❑ Default constraint – assign default value for an attribute.

❑ Check constraint – ensures that values in some attributes or table

satisfy certain conditions.

❑ Foreign Key constraint – ensures that values in an attribute are from

the values of some other table’s primary.

Example – How to Specify constraints? CREATE TABLE Activity ( Regno int Foreign key references student(Regno), . . ); Or CREATE TABLE Activity ( Regno int, . . Foreign key Regno references student (Regno), );

ALTER TABLE Command The ALTER statement can add, remove or change table's column(s) or constraints. Syntax: ALTER TABLE < table_name> ADD/DROP <column_name> [datatype]; Example: ❖ Adding a new column: - mysql> ALTER TABLE student ADD games VARCHAR(5); After this command the games column will be added and a null value will be assigned for all the rows in this column

ALTER TABLE Command continued… ❖ Renaming a Column of a table Syntax ALTER TABLE

CHANGE ; ❖ Deleting Column of a Table To delete a column of a table the ALTER command can be used with Drop command. mysql> ALTER TABLE Student DROP games;

DROP TABLE To delete the table, a DROP TABLE command can be issued. Syntax DROP TABLE ; Example - Mysql>DROP TABLE activity; Query OK, 0 rows affected (0.01 sec) Now, we won't get this table in SHOW TABLES; listing mysql> SHOW TABLES; +---------------------+ | Tables in school_db | +---------------------+ | student | | sports | +---------------------+ 2 rows in set (0.00 sec)

INSERT COMMAND - INSERTING DATA INTO TABLE The rows are added to the tables using INSERT command of MySQL. Syntax: INSERT INTO < tablename>[< column list>] VALUES (, …); Example mysql> INSERT INTO student VALUES( 15016 ,’Ravi’,’M’, 80 ,’A’); Query OK, 1 row affected ( 0. 04 sec) Rows matched: 1 Changed: 1 Warnings: 0 The order of values should match the order of columns in the CREATE TABLE command of the Student table. Alternate Syntax : mysql> INSERT INTO student (rollno,name,gender,marks 1 ,grade) VALUES( 15017 ,’Manju’,’F’, 80 ,’A’); Query OK, 1 row affected ( 0. 04 sec) Rows matched: 1 Changed: 1 Warnings: 0 Here the column names are explicitly specified.

SELECT STATEMENT – RETRIEVING DATA FROM TABLES The SQL SELECT command is used to fetch data from MySQL database. Syntax:

SELECT */<Column Name(s)/<Expression(s)>

FROM

[WHERE ]

[GROUP BY ]

[HAVING ]

[ORDER BY <Column Name(s)>];