I am going to share some most common mysql queries that you always play with. This article includes the top 10 queries you use most often in MySQL….
1. Query for Creating New database Table :
If you want to create table in any existing database than you need to execute following query…
CREATE TABLE ''students'' ( ''id'' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, ''name'' VARCHAR(45) NOT NULL, ''lastname'' VARCHAR(45), ''school'' VARCHAR(45), PRIMARY KEY (''id'') ) ENGINE = InnoDB; In above query I am trying to create a table ''students'' and field will be id, name, lastname, school and we made primary kay ''id''..
2. Query for Inserting data :
Now let”s insert data in above table as we created in above query.. For inserting data we use following query…
insert into students(id,name,lastname,school) values(''key that already exists'', ?''monu'',''jadaun'',''DPS Delhi'');
3. Query for Updating data :
Now if we want to update some data in the table than we need to execute following query….
UPDATE students SET name=''mahendra'', lastname = ''singh'', school = ''ALS Delhi'';
3. Add a primary key:
ALTER TABLE ''TABLE_NAME'' DROP PRIMARY KEY, ADD PRIMARY KEY (''COLUMN_NAME'');
4.Drop a Primary key:
ALTER TABLE ''TABLE_NAME'' DROP PRIMARY KEY;
While adding a primary key, we first drop the already added keys and then add the key to a new column. Otherwise it won”t allow you to add the key.
5. Query for Fetching data :
Now lets fetch some records from database table using mysql query…
SELECT * FROM students;
Above query will select all the records from students table.. Now we will use ”WHERE” clause to fetch certain records from database table..
SELECT * FROM students WHERE id = ''1'';
above query will fetch only one record from table students where id is 1… means only single record will be fetched by this query.\r\n\r\n
6. Order By, Having Clauses
Why use ? Order by is used to arrange data by a specific column and then
having is used to filter that result set. These are really useful when you need some specific information from a large database. below is the query…
Select * from students group_by name having fees > 10000;
In the above query the result set would contain data organized by name and those who have fees greater than 10,000. It”s worth noting that when we use the “having” clause without “order by” it works more like the “where” clause.
7. Query for Alter and Update Columns of a Table
As we know Tables are created and then edited quite often. In the following few lines we will see how to add/change columns of an existing table.
Add a column
Alter table ''emp'' add column salary varchar(45);
Edit a column
Salary should be in integer instead of having varchar, so let”s change its datatype to int. Note that there may be loss of data if there is a compatibility issue.
Alter table ''emp'' modify column salary int(10);
Rename a column
Alter table ''emp'' change salary salary2 varchar(45);
Drop a column
Alter table ''emp'' drop column salary;