akash verma: SMU ASSIGNMENT OF RDBMS AND MY SQL SEM 3RD BSC IT

[ FALL 2015 ] ASSIGNMENT

RDBMS and MySQL

Q. No. 1. How to drop database, tables, and index in MySQL? Explain with the help of examples.

Answer: DROP DATABASEdrops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database. DROP SCHEMAis a synonym for

DROP
DATABASE

Important Note: When a database is dropped, user privileges on the database are not automatically dropped.

Ø IF EXISTSis used to prevent an error from occurring if the database does not exist.

Ø If you use DROP DATABASEon a symbolically linked database, both the link and the original database are deleted.

Ø DROP DATABASEreturns the number of tables that were removed. This corresponds to the number of .frmfiles removed.

Ø TheDROP DATABASEstatement removes from the given database directory those files and directories that MySQL itself may create during normal operation:

Ø All files with these extensions:

.BAK	.DAT	.HSH	.MRG
.MYD	.MYI	.TRG	.TRN
.db	.frm	.ibd	.ndb
.par

Ø The db.opt file, if it exists.

If other files or directories remain in the database directory after MySQL removes those just listed, the database directory cannot be removed. In this case, you must remove any remaining files or directories manually and issue the DROP DATABASEstatement again.

You can also drop databases with mysqladmin.

DROP INDEX Syntax

If you find you no longer have any need of an index, you can nuke it with the DROP index command.

DROP INDEXdrops the index named index_namefrom the table tbl_name. This statement is mapped to an ALTER TABLEstatement to drop the index.

Syntax:

Example: The following command deletes the username index created on uname column.

Beginning with MySQL 5.1.7, indexes on variable-width columns are dropped online; that is, dropping the indexes does not require any copying or locking of the table. This is done automatically by the server whenever it determines that it is possible to do so; you do not have to use any special SQL syntax or server options to cause it to happen.

DROP TABLE Syntax

DROP TABLEremoves one or more tables. You must have the DROPprivilege for each table. All table data and the table definition are removed, so be careful with this statement! If any of the tables named in the argument list do not exist, MySQL returns an error indicating by name which non-existing tables it was unable to drop, but it also drops all of the tables in the list that do exist.

Important Note: When a table is dropped, user privileges on the table are not automatically dropped.

Note that for a partitioned table, DROP TABLEpermanently removes the table definition, all of its partitions, and all of the data which was stored in those partitions. It also removes the partitioning definition(.par) file associated with the dropped table.

UseIF EXISTSto prevent an error from occurring for tables that do not exist. A NOTEis generated for each non-existent table when using IF EXISTS.

RESTRICTandCASCADEare allowed to make porting easier. In MySQL 5.1, they do nothing.

Note:DROP TABLEautomatically commits the current active transaction, unless you use the TEMPORARYkeyword.

The TEMPORARYkeyword has the following effects:

Ø The statement drops only TEMPORARYtables.

Ø The statement does not end an ongoing transaction.

Ø No access rights are checked. (A TEMPORARYtable is visible only to the client that created it, so no check is necessary.)

Using TEMPORARYis a good way to ensure that you do not accidentally drop a non-TEMPORARYtable.

Q. No. 2. a. Explain insert statement with example.

b. Exercise: Create table CUSTOMERS with fields – ID,name, age, address and salary and insert records in the table(insert at least five records.)

Answer:

Once a database and its tables have been created, the next step is to enter data into them. This is accomplished by means of the INSERT command, whose basic syntax is given below:

Example: Let us create a simple table called addressbookas shown below:

Now we will use the INSERT statement to put some data into the table.

The table should now contain a single record for Rob Rabbit. To verify this you can issue a simple select statement on the table as follows:

You can also use an abbreviated form of the INSERT statement, in which the field list is left unspecified. The following example, an equivalent of the previous INSERT statement is given below:

Note: When using this shorter format, the order in which values are inserted must correspond to the sequence of fields in the table, which can be easily determined by issuing the DESCRIBE command on the respective table.

b. Exercise

Following is an example, which creates a CUSTOMERS table with ID as primary key and NOT NULL are the constraints showing that these fields can not be NULL while creating records in this table:

SQL> CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25),

SALARY DECIMAL (18,2),

PRIMARY KEY (ID)

);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1,'Ramesh',32,'Ahmedabad',2000.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2,'Khilan',25,'Delhi',1500.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3,'kaushik',23,'Kota',2000.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4,'Chaitali',25,'Mumbai',6500.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5,'Hardik',27,'Bhopal',8500.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (6,'Komal',22,'MP',4500.00);

All the above statements would produce the following records in CUSTOMERS table:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|1|Ramesh|32|Ahmedabad|2000.00|

|2|Khilan|25|Delhi|1500.00|

|3| kaushik  |23|Kota|2000.00|

|4|Chaitali|25|Mumbai|6500.00|

|5|Hardik|27|Bhopal|8500.00|

|6|Komal|22| MP        |4500.00|

|7|Muffy|24|Indore|10000.00|

+----+----------+-----+-----------+----------+

Q. No.3. Explain update and replace statements with example.

Answer:

For the single-table syntax, the UPDATEstatement updates columns of existing rows in tbl_namewith new values.

TheSETclause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULTto set a column explicitly to its default value.

TheWHEREclause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BYclause is specified, the rows are updated in the order that is specified.

TheLIMITclause places a limit on the number of rows that can be updated.

Single-tableUPDATEassignments are generally evaluated from left to right.

If you set a column to the value it currently has, MySQL notices this and does not update it. If you update a column that has been declared NOT NULLby setting to NULL, the column is set to the default value appropriate for the data type and the warning count is incremented. The default value is 0for numeric types, the empty string ('') for string types, and the zero value for date and time types.

REPLACE

A subtle variation on the ON DUPLICATE KEY UPDATE is the REPLACE command, which adopts the same syntax as the INSERT command. Unlike INSERT, though, which produces an error if the record being inserted contains a duplicate value on a filed marked as UNIQUE, REPLACE replaces the entire record with new values.

Syntax 1:

Syntax 2:

Syntax 3:

REPLACEworks exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEYor a UNIQUEindex, the old row is deleted before the new row is inserted. REPLACEis a MySQL extension to the SQL standard. It either inserts, or deletes and inserts.

Values for all columns are taken from the values specified in the REPLACEstatement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.

To use REPLACE, you must have both theINSERTandDELETEprivileges for the table.

The affected-rows count makes it easy to determine whether REPLACEonly added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).

Q. No. 4. Explain all the date and time functions in detail.

Answer:This section describes the functions that can be used to manipulate temporal values.

Table 7.9: Date and Time Functions

Name	Description
ADDDATE()(v4.1.1)	Add dates
ADDTIME()(v4.1.1)	Add times
CONVERT_TZ()(v4.1.3)	Convert from one timezone to another
CURDATE()	Return the current date
CURRENT_DATE(), CURRENT_DATE	Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME	Synonyms for CURTIME()
CURRENT_TIMESTAMPS(), CURRENT_TIMESAMP	Synonyms for NOW()
CURTIME()	Return the current time
DATE_ADD	Add two dates
DATE_FORMAT()	Format date as specified
DATE_SUB	Subtract two dates
DATE()(V4.1.1)	Extract the date part of a date or datetime expressions
DATEDIFF()(v4.1.1)	Subtract two dates
DAY()(v4.1.1)	Synonym for DAYOFMONTH()
DAYNAME()(v4.1.1)	Return the name of weekday
DAYOF WEEK()	Return the day of the month (0-31)
DAY OF WEEK()	Return the weekdays index of the argument
DAYOFYEAR()	Return the day of the year (1-366)
EXTRACT	Extract part of a date
FROM_DAYS	Convert a day number to a date
FROM_UNIXTIME()	Format UNIX timestamp as a date
GET_FORMAT()(v.4.1.1)	Return a date format string
HOUR()	Exact the hour
LAST_DAY(V.4.1.1)	Return the last day of the month for argument
LOCALTIME(), LOCALTIME	Synonyms for NOW()
LOCALTIMESTAMP, LOCALTIMESSTAMP()(v4.0.6)	Synonyms for NOW()
MAKEDATE()(v.4.1.1)	Create a date from the year and day of year
MAKETIME(v4.1.1)	MAKETIME()
MICROSECOND()(v4.1.1)	Return the microsecond from argument
MINUTE()	Return the minute from the argument
MONTH()	Return the month from the date passed
MONTHNAME()(v4.1.1)	Return the name of the month
NOW()	Return the current date and time
PERIOD_ADD	Add a period to a year-month
PERIOD_DIFF()	Return the number of month between periods
QUARTER()	Return the quarter from a date argument
SEC_TO_TIME()	Converts seconds to ‘HH:MM:SS’ format
SECONDS()	Return the seconds (0-59)
STR_TO_DATE()(v4.1.1)	Converts a string to a date
SUBDATE()	A synonyms for DATE_SUB() when invoked with three arguments
SUBTIME()(v4.1.1)	Subtract times
SYSDATE()	Return the time at which the function executes
TIME_FORMAT()	Format as time
TIME_TO_SEC()	Return the time portion of the expression passed
TIMEDIFF()(v4.1.1)	Subtract time
TIMESTAMPADD()(v5.0.0)	Add an interval to a determine expression
TIMESTAMPDIFF()(v5.0.0)	Subtract an interval from a datetime expression
TO_DAYA()	Return the date argument converted to days
UNIX_TIMESAMP()	Return a UNIX timestamp
UTC_DATE()(v.4.1.1)	Return the current UTC date
UTC_TIMESTAMP()(v4.1.1)	Return the current UTC date and time
WEEK()	Return the week number
WEEKDAY()	Return the weekday index
WEEKOFYEAR()(v4.1.1)	Return the calender week of the date (0-53)
YEAR()	Return the year
YEARWEEK()	Return the year and week

Example: This uses date functions. The following query selects all rows with a date_col value from within the last 30 days:

The query also selects rows with dates that lie in the future.

Functions that expect date values usually accept datetime values and ignore the time part. Functions that expect time values usually accept datetime values and ignore the date part.

Some date functions can be used with zerodates or incomplete dates such as '2001-11-00', whereas others cannot. Functions that extract parts of dates typically work with incomplete dates and thus can return 0 when you might otherwise expect a non-zero value. For example:

Other functions expect complete dates and return NULLfor incomplete dates. These include functions that perform date arithmetic or that map parts of dates to names.

Example:

Q. No.5. Describe the procedures to add new user account in MySQL.

Answer: You can create MySQL accounts in two ways:

· By using statements intended for creating accounts, such as CREATE USERor GRANT

· By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE

The preferred method is to use account-creation statements because they are more concise and less error-prone.

The following examples show how to use the mysql client program to set up new users. These examples assume that privileges are set up according to the defaults.

This means that to make changes, you must connect to the MySQL server as the MySQL root user, and the root account must have the INSERT privilege for the mysql database and the RELOAD administrative privilege.

First, use the mysql program to connect to the server as the MySQL root user:

If you have assigned a password to the root account, you'll also need to supply a password or -p option for this mysql command and also for those later in this section.

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

The accounts created by theseGRANTstatements have the following properties:

· Two of the accounts have a username of montyand a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. The reason for this is that the anonymous-user account has a more specificHostcolumn value than the 'monty'@'%'account and thus comes earlier in the usertable sort order.

· One account has a username of admin and no password. This account can be used only by connecting from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges allow the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges later by issuing additional GRANT statements.

· One account has a username of dummy and no password. This account can be used only by connecting from the local host. No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later/.

Q. No. 6. Briefly describe the error log and general query log.

Answer:The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.

On some operating systems, the error log contains a stack trace if mysqld dies. The trace can be used to determine where mysqld died.

You can specify where mysqld writes the error log with the --log-error[=file_name] option. If no file_name value is given, mysqld uses the name host_name.err by default and writes the file in the data directory. If you execute FLUSH LOGS, the error log is renamed with the suffix -old and mysqld creates a new empty log file. (No renaming occurs if the --log-error option was not given to mysqld.)

In 5.1.21 and up, the default with no logging options is --skip-syslog, which is compatible with the default behavior of writing an error log file for releases prior to 5.1.20. To explicitly specify use of an error log file, specify --log-error=file_name to mysqld_safe, and mysqld_safe will arrange for mysqld to write messages to a log file. To use syslog instead, specify the --syslog option.

The General Query Log

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands: