MySQL

1. MySQL

2. Introduction: MySQL is a DBMS (DataBase Management System) i.e. it is a databse software. MySQL is 5.0 can be downloaded from the website http://dev.mysql.com/downloads. MySQL runs on all major platforms such as Windows, Linux, Solaris, Mac OSX etc.

3. What is SQL? Full form of SQL is Structured Query Language. SQL is a language designed for communicating with the databases. It is a basic tool for accessing data in a relational database.

4. SQL is made up of very few words. It provides a simple and efficient way to read and write the data from a database. SQL is not a proprietary language of any DBMS. Learning this one language enables us to interact with any DBMS. SQL is easy to learn. With the help of SQL we can perform very complex database operations.

5. MySQL is client-server-based databases. Client server applications are split into two parts. The server portion is a piece of software that is responsible for data access and manipulation. • Client is the piece of software with which the user interacts. • To use MySQL we need a client, an application that you use to interact with MySQL(to give commands to be executed)

6. There are three tools used as client application • MySQL command line utility • MySQL Query Browser • MySQL Administrator. • ( MySQL command line utility is a part of core MySQL installation, whereas MySQL Query Browser and MySQL Administrator come under GUI tools of MySQL)

7. Database: • Database is collection of data stored in some organized fashion. We can consider it to be similar to a filing cabinet. • We have files in the filing cabinet and related data is stored in specific files. A file is called as a table in DBMS. • Hence a database is container of number of tables.

8. Relational Database: When information can be presented in the form of tables in a database, regardless of how it is stored, the arrangement of data is called as relational database. The term relational database comes from the fact that data columns are related to each other by virtue of their values.

9. Every table in a database has a unique name that identifies it. That means no other table in that database can have the same name. • The database name and the table name together make the table name unique. This means one can not use the same table name in the same database but one can reuse the table names in different databases.

10. Tables are made up of columns. A column contains a particular piece of information. For e.g. in a table called CUST we can have a column containing customer name, customer address etc. • Each column in a table has an associated datatype. A datatype defines what type of data a column can contain.

11. We will mainly use the following datatypes 1. Numeric Datatype: Numeric datatypes store numbers. We will following two datatypes to store numbers. INT: This datatype stores integer values and supports numbers from -2147483648 to 2147483647. DECIMAL(or DEC): It stores floating point values with varying levels of precision.

12. SMALLINT: Stores integer value, supports numbers from -32768 to 32767 (or 0 to 65535 if UNSIGNED) BIGINT: Stores integer value supports numbers from -9223372036854775808 to 9223372036854775807 (or 0 to 18446744073709551615 if UNSIGNED)

13. TINYINT: Stores integer value from -128 to 127 (or 0 to 255 if unsigned). BOOLEAN ( or BOOL): This data type is used to store value 0 or 1. BOOLEAN data type is converted into TINYINT.

14. DECIMAL(or DEC): It stores floating point values with varying levels of precision. It can store maximum of 65 digits and supports up to 30 decimals. FLOAT: Stores small four byte single-precision floating point values. Precision of floating point number is between 0 to 24. DOUBLE: Stores normal eight byte double-precision floating point values. Precision is between 25 to 53.

15. 2. Date and Time datatypes • DATE: Date from 1000-01-01 to 9999-12-31 in the format YYYY-MM-DD. • TIME: Time from -838:59:59 to 838:59:59 in the format HH:MM:SS • DATETIME: A combination of DATE and TIME in the format YYYY-MM-DD HH:MM:SS. This type does not have automatic update features as TIMESTAMP.

16. TIMESTAMP: Equivalent to DATETIME but with a smaller range. Range is 1970-01-01 00:00:00 to sometime in 2037. TIMESTAMP is automatically updated to current date and time when a new row is inserted or updated. YEAR: A 2 or 4 digit year 2 digit year supports a range of 70(1970) to 69(2069), 4 digit year support a range of 1901 to 2155.

17. 3. String datatypes: • CHAR: Fixed length string from 1 to 255 characters long. Its size must be specified at the time of creating a table. If the length is not specified then MySQL assumes it to be 1. • VARCHAR: Same as CHAR but stores text of variable size length. Maximum value of width is 65,535 characters.

18. TEXT : Variable length text with maximum size of 64K MEDIUMTEXT: Same as TEXT but with maximum size of 16K. LONGTEXT: Same as TEXT but with a maximum size of 4GB.

19. Fixed length string are datatypes that are defined to accept a fixed number of characters. It will not accept more than the specified number of characters. Fixed length string i.e. CHAR can contain 1 to 255 characters. • MySQL can sort and manipulate fixed length datatypes more quickly than variable length.

20. Data in a table is stored in rows. Every row in a table should have a column(or columns) that uniquely identifies it. This column is called as the primary key. • For e.g. a table called CUST may have cust_id (unique numeric customer ID) as the primary key.

21. Any column can be defined as the primary key as long as it satisfies the following conditions. • No two rows can have same primary key value. • Every row must have a primary key value. (Primary key columns will not allow NULL values.)

22. Generally we have one primary key column for a table. But we may use multiple columns together as primary key. The rules defined for a primary key column must apply to columns that make up the primary key

23. Foreign Key: A column in one table that contains primary key values from another table is called as the foreign key. It defines relationship between two tables. For e.g. In BILL table CUST_ID can be used as the foreign key, CUST_ID is primary key in CUST table.

24. We need a default database to work with. We can create a new database with help of command • CREATE DATABASE Or • CREATE SCHEMA • The keywords in the commands are not case sensitive. • For e.g. CREATE DATABASE SHOP;

25. List of databases can be seen with the help of command SHOW DATABASES; • List of tables within a database can be obtained with the help of statement SHOW TABLES FROM SHOP; • To use a database called SHOP we give the command USE SHOP;

26. To create a table called CUST following set of SQL statements is used. CREATE TABLE CUST (CUST_ID INT NOT NULL, NAME CHAR(20) NOT NULL, CITY CHAR(20) NOT NULL, PHONE CHAR(20) NULL, PRIMARY KEY (CUST_ID));

27. To create a table called BILL following set of SQL statements is used. CREATE TABLE BILL (CUST_ID INT NOT NULL, BILLNO INT NOT NULL AUTO_INCREMENT, AMT DECIMAL(10,2) NOT NULL, PRIMARY KEY(BILLNO));

28. Only one auto_increment column will be allowed per table and it must be made primary key. If we want to use a specific value for auto_increment column then it can be entered using INSERT statement. This value will be accepted as long as it is unique. Subsequent incrementing will start using the value manually inserted.

29. The column name can not contain any comma or blank spaces or any other special symbol (except $ and _). Column name can be up to 64 characters long. Digits from 0 to 9 will be allowed within a column name. Keywords can not be used as column names. Column name may begin with a digit or $. Same rules are applicable to the table names. Column names and table names are case insensitive.

30. We can create BILL table by defining foreign key as follows CREATE TABLE BILL (CUST_ID INT NOT NULL, BILLNO INT NOT NULL AUTO_INCREMENT, AMT DECIMAL(10,2) NOT NULL, PRIMARY KEY(BILLNO), FOREIGN KEY (CUST_ID) REFERENCES CUST (CUST_ID));

31. The two tables CUST and BILL will be stored in the database SHOP. • To see the list of columns from table CUST we use the command SHOW COLUMNS FROM CUST; Columns CUST_ID and NAME in CUST must not be empty as NOT NULL is specified, whereas address and phone columns may not be filled.

32. We may use any name to store CUST_ID in the table BILL and still define it as foreign key. For e.g. if we use CNO instead of CUST_ID in BILL then we define foreign key statement as FOREIGN KEY (CNO) REFERENCES CUST (CUST_ID)); SHOW COLUMNS FROM BILL; displays columns as

34. MUL against column CUST_ID means that multiple occurrences in this column will be allowed. If foreign key is defined then we can not delete or update column CUST_ID in CUST table.

35. To look at the code used for creating a table CUST, we use statement SHOW CREATE TABLE CUST; MySQL server status information can be seen with the help of command SHOW STATUS;

36. MySQL enables us to specify default values to be used if no values are specified when a row is inserted. Default values are specified using the DEFAULT keyword in the column definition in the CREATE TABLE statement. For e.g.

37. CREATE TABLE EMP (EMP_ID INT NOT NULL, NAME CHAR(20) NOT NULL, DEPTID CHAR(10) NOT NULL, DOJ DATE NOT NULL, CITY CHAR(10) NOT NULL, PERM BOOL NOT NULL DEFAULT 1, PRIMARY KEY (EMP_ID));

38. BOOLEAN data type is usually taken as TINYINT. Value 1 is true and 0 is false. The order of NOT NULL and DEFAULT can be interchanged.

39. We may use DESCRIBE as shortcut for SHOW COLUMNS FROM For e.g. DESCRIBE CUST;

40. To add data to the columns in the table CUST we use the command INSERT as follows. • INSERT INTO CUST VALUES ('1', 'ANAND', 'MUMBAI', NULL);

41. It is important to note that values must be inserted in the order as the order of columns specified in the table CUST. One may write an entire SQL statement in a single line.

42. One can use INSERT command by changing order of the columns in the table. Order in which values are to be inserted must be specified. Also one can omit some of the columns provided the columns are defined to contain NULL values or some default values. • For e.g. INSERT INTO CUST (NAME,CUST_ID,CITY) VALUES('BEENA',3, 'PUNE');

43. We may insert values in a table without specifying the auto increment value. For e.g. In BILL table BILLNO is an auto increment column. To generate the next BILLNO we specify the other column names and their values in INSERT statement as follows. INSERT INTO BILL(CUST_ID,AMT) VALUES (4,4000);

44. We can insert two or more in a table by using a single INSERT statement For e.g. we insert two rows in BILL table as follows INSERT INTO BILL (CUST_ID,AMT) VALUES(3,5000), (5,9000);

45. ALTER STATEMENT: ALTER statement is used to change the structure of a table after it has been created. ALTER statement is used to add columns or to delete columns from the table. Also it is used to change type and width of the columns and to rename the columns.

46. For e.g. ALTER TABLE BILL ADD DT_PUR DATE NULL; A new column with name DT_PUR will be added to the table BILL. ALTER TABLE CUST ADD GENDER CHAR(1) NOT NULL;

47. To drop the column DT_PUR we can give the command ALTER TABLE BILL DROP COLUMN DT_PUR ; The keyword COLUMN in above two statements is optional.

48. We can rename a column using ALTER statement e.g. ALTER TABLE CUST CHANGE COLUMN PHONE CPHONE CHAR(20) NOT NULL ; ALTER TABLE CUST CHANGE COLUMN CPHONE PHONE CHAR(10) NOT NULL; Column name, its type and width can all be changed in a single statement.

49. It is advisable not to change data type as we may lose data.(It is advisable to specify null or not null in column definition). Keyword COLUMN is optional. To make AMT column in BILL table contain default value as 0 we use ALTER statement as follows. ALTER TABLE BILL CHANGE COLUMN AMT AMT DEC(10,2) NOT NULL DEFAULT 0;

50. We can make alterations to two or more columns at a time using ALTER TABLE statement for e.g. ALTER TABLE CUST DROP GENDER, ADD PIN CHAR(10) NULL; ALTER TABLE CUST CHANGE NAME CNAME CHAR(20) NOT NULL, CHANGE CPHONE PHONE CHAR(10) NULL;