MySql

  Alter Table  It is used to change existing table structure such as adding or removing columns attributes etc. Syntax- alter table table_name action, [action2…..] Adding new column Drop a column Rename table describe emp2; We want to add a new column as dept. alter table emp2; → add column dept int NOT NULL → after empid; By default it will add at the end. Drop field Alter table table_name drop column_name alter emp2 → drop column dept; Rename Table Rename old table to new name like rename emp2 to emp6; Drop Table It will remove table drop table table_name; Truncate Table It will clear the table data truncate table emp6; drop table emp6 Index in Mysql An index helps in fast retrieval of data from tables. It is just like a index of book where you first search index to look for contents. All primary key column are in the primary index of the table automatically Syntax- Create index index_name on table_name column_name Deleting Index Create table emp_new3(eid, ename varchar(10))...

  Primary Key/Foreign Key Primary Key Combinagtion of column that uniquely identifies a row in table. Rules for Primary Key Primary key must contain unique value. Primary key can’t be null. There is only one primary key in table. Syntax- Col_name data_type PRIMARY KEY. like roll no in every class. We can define more than one column for primary key. PRIMARY KEY(COL1, COL2,......) PRIMARY KEY in existing table ALTER table(table_name) add PRIMARY KEY(primary key column) use college; create table emp11(empid int PRIMARY KEY, ename varchar(10)); describe emp11; create table emp12 (empid int, ename varchar(10),  → PRIMARY KEY(empid, ename); now you can see by describe command create table emp13 (empid int, ename varchar(10)); alter table emp13 → add PRIMARY KEY(empid); FOREIGN KEY It is a field in a table that matches another field of another table. It is used to maintain referential integrity. A table may have more than one foreign key and each fireign key in the child table may re...

  Managing Database Create database - To create a database  create database database_name create database EEC Display database - To display database  show databases   Select database - To select a database   select database database_name select database EEC Removing database - To delete a database  drop database database_name drop database EEC Creating Table in Sql with Constraints (बिशेष शर्त के अनुसार ) Create table emp(empid int, ename varchar(40), salary int); insert into emp values(1, ‘a’, 2000); select * from emp; Various Constraints Not null or null This constraints forces the values of a column to non- null values only (column में value भरना अनिवार्य है ) Syntax- Col_name data_type not null; create table emp1(empid int, ename varchar(40), salary int not null); insert into (empid, ename) values(1, ‘a’); क्योंकि salary के कॉलम को खाली नहीं छोड़ सकते इसलिए error  प्रदर्शित होगी | describe emp; table का structure प्रदर्शित होगा | इसमें आप ...

  Modifying Data  (Insert, Update, Delete) Insert command का प्रयोग table में डाटा भरने के लिए किया जाता है | Syntax- insert into table(column1, column2,........) value(value1, value2….) select * from department; insert into department values (8, ‘ICE’); insert into department values (‘temp’, 6); ----error आ जाएगी | insert into department (dname, deptid) values (‘Temp’, 6) हो जायेगा | you can insert multiple rows insert into department values<enter> → (7, ‘A’), → (8, ‘B’);<enter> Insert with SELECT statement  It is used to insert data into table from another table. insert into table_name select col1, col2, …. from table_name; Update Statement Update statement is used to update existing data in a table . Syntax- update table_name set col1=exp1 col2=exp2 where condition We want to change deptname where deptid=7 in dept_temp. update department_temp → set deptname=’Applide Sc.’, → where deptid=7; select * from dept_temp; update dept_temp → set deptname=’test’; sel...

  ORDER BY CLAUSE (Sort Command) Order  by clause is used to sort the result by the select statement. ये कमांड query के परिणाम को sort करके प्रदर्शित करती है | Syntax- order by column1[ASC|DEC], Column2[ASC|DEC],...... By default ASC (Ascending Order) होता है | student table में से सभी छात्रों की जानकारी नाम के  ascending order में प्रदर्शित करो | select * from student order by sname asc; asc दें या नहीं asc ही होगा लेकिन descending में देने के लिए dec दें | student table में से marks के घटते क्रम में छात्रों की जानकारी प्रदर्शित करो | select * from student order by marks dec; student table में से छात्रों के नाम ascending और उनके marks descending order में प्रदर्शित करो | select * from student order by sname asc, marks dec; faculty table में से qualification के निम्न क्रम से प्रदर्शित करो - M.Tech, Ph.D, B.Tech. select * from faculty order by field(qualification, ‘M.Tech’, ‘Ph.D’, ‘B.Tech’); Group by & Having Clause (Grouping Data) select sum(marks) from student; |---...