Tag: mysql command

Uncategorized

SELECT SQL Query

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

SELECT query is used to retrieve data from a table. It is the most used SQL query. We can retrieve complete table data, or partial by specifying conditions using the WHERE clause.

Syntax of SELECT query

SELECT query is used to retieve records from a table. We can specify the names of the columns which we want in the resultset.

SELECT 
    column_name1, 
    column_name2, 
    column_name3, 
    ...
    column_nameN 
    FROM table_name;

Time for an Example

Consider the following student table,

s_idnameageaddress
101Adam15Chennai
102Alex18Delhi
103Abhi17Banglore
104Ankit22Mumbai
SELECT s_id, name, age FROM student;

The above query will fetch information of s_id, name and age columns of the student table and display them,

s_idnameage
101Adam15
102Alex18
103Abhi17
104Ankit22

As you can see the data from address column is absent, because we did not specif it in our SELECT query.

Select all records from a table

A special character asterisk * is used to address all the data(belonging to all columns) in a query. SELECT statement uses * character to retrieve all records from a table, for all the columns.

SELECT * FROM student;

The above query will show all the records of student table, that means it will show complete dataset of the table.

s_idnameageaddress
101Adam15Chennai
102Alex18Delhi
103Abhi17Banglore
104Ankit22Mumbai

Select a particular record based on a condition

We can use the WHERE clause to set a condition,

SELECT * FROM student WHERE name = 'Abhi';

The above query will return the following result,

103Abhi17Rohtak

Performing Simple Calculations using SELECT Query

Consider the following employee table.

eidnameagesalary
101Adam265000
102Ricky428000
103Abhi2510000
104Rohan225000

Here is our SELECT query,

SELECT eid, name, salary+3000  FROM employee;

The above command will display a new column in the result, with 3000 added into existing salaries of the employees.

eidnamesalary+3000
101Adam8000
102Ricky11000
103Abhi13000
104Rohan8000

So you can also perform simple mathematical operations on the data too using the SELECT query to fetch data from table.

< prev
Next >
Uncategorized

Using GRANT and REVOKE

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Data Control Language(DCL) is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges. Privileges are of two types,

  • System: This includes permissions for creating session, table, etc and all types of other system privileges.
  • Object: This includes permissions for any command or query to perform any operation on the database tables.

In DCL we have two commands,

  • GRANT: Used to provide any user access privileges or other priviliges for the database.
  • REVOKE: Used to take back permissions from any user.

Allow a User to create session

When we create a user in SQL, it is not even allowed to login and create a session until and unless proper permissions/priviliges are granted to the user.

Following command can be used to grant the session creating priviliges.

GRANT CREATE SESSION TO username;

Allow a User to create table

To allow a user to create tables in the database, we can use the below command,

GRANT CREATE TABLE TO username;

Provide user with space on tablespace to store table

Allowing a user to create table is not enough to start storing data in that table. We also must provide the user with priviliges to use the available tablespace for their table and data.

ALTER USER username QUOTA UNLIMITED ON SYSTEM;

The above command will alter the user details and will provide it access to unlimited tablespace on system.

Grant all privilege to a User

sysdba is a set of priviliges which has all the permissions in it. So if we want to provide all the privileges to any user, we can simply grant them the sysdba permission.

GRANT sysdba TO username

Grant permission to create any table

Sometimes user is restricted from creating come tables with names which are reserved for system tables. But we can grant privileges to a user to create any table using the below command,

GRANT CREATE ANY TABLE TO username

Grant permission to drop any table

As the title suggests, if you want to allow user to drop any table from the database, then grant this privilege to the user,

GRANT DROP ANY TABLE TO username

To take back Permissions

And, if you want to take back the privileges from any user, use the REVOKE command.

REVOKE CREATE TABLE FROM username
< prev
Next >

Uncategorized

Using DELETE SQL command

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

When you ask any question in Studytonight’s Forum it gets saved into a table. And using the Delete option, you can even delete a question asked by you. How do you think that works? Yes, using the Delete DML command.

Let’s study about the syntax and the usage of the Delete command.

DELETE command

DELETE command is used to delete data from a table.

Following is its general syntax,

DELETE FROM table_name;

Let’s take a sample table student:

s_idnameage
101Adam15
102Alex18
103Abhi17

Delete all Records from a Table

DELETE FROM student;

The above command will delete all the records from the table student.

Delete a particular Record from a Table

In our student table if we want to delete a single record, we can use the WHERE clause to provide a condition in our DELETE statement.

DELETE FROM student WHERE s_id=103;

The above command will delete the record where s_id is 103 from the table student.

S_idS_Nameage
101Adam15
102Alex18

Isn’t DELETE same as TRUNCATE

TRUNCATE command is different from DELETE command. The delete command will delete all the rows from a table whereas truncate command not only deletes all the records stored in the table, but it also re-initializes the table(like a newly created table).

For eg: If you have a table with 10 rows and an auto_increment primary key, and if you use DELETE command to delete all the rows, it will delete all the rows, but will not re-initialize the primary key, hence if you will insert any row after using the DELETE command, the auto_increment primary key will start from 11. But in case of TRUNCATE command, primary key is re-initialized, and it will again start from 1.

< prev
Next >
Uncategorized

SQL: ALTER command

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

alter command is used for altering the table structure, such as,

  • to add a column to existing table
  • to rename any existing column
  • to change datatype of any column or to modify its size.
  • to drop a column from the table.

ALTER Command: Add a new Column

Using ALTER command we can add a column to any existing table. Following is the syntax,

ALTER TABLE table_name ADD(
    column_name datatype);

Here is an Example for this,

ALTER TABLE student ADD(
    address VARCHAR(200)
);

The above command will add a new column address to the table student, which will hold data of type varchar which is nothing but string, of length 200.

ALTER Command: Add multiple new Columns

Using ALTER command we can even add multiple new columns to any existing table. Following is the syntax,

ALTER TABLE table_name ADD(
    column_name1 datatype1, 
    column-name2 datatype2, 
    column-name3 datatype3);

Here is an Example for this,

ALTER TABLE student ADD(
    father_name VARCHAR(60), 
    mother_name VARCHAR(60), 
    dob DATE);

The above command will add three new columns to the student table

ALTER Command: Add Column with default value

ALTER command can add a new column to an existing table with a default value too. The default value is used when no value is inserted in the column. Following is the syntax,

ALTER TABLE table_name ADD(
    column-name1 datatype1 DEFAULT some_value
);

Here is an Example for this,

ALTER TABLE student ADD(
    dob DATE DEFAULT '01-Jan-99'
);

The above command will add a new column with a preset default value to the table student.

ALTER Command: Modify an existing Column

ALTER command can also be used to modify data type of any existing column. Following is the syntax,

ALTER TABLE table_name modify(
    column_name datatype
);

Here is an Example for this,

ALTER TABLE student MODIFY(
    address varchar(300));

Remember we added a new column address in the beginning? The above command will modify the address column of the student table, to now hold upto 300 characters.

ALTER Command: Rename a Column

Using ALTER command you can rename an existing column. Following is the syntax,

ALTER TABLE table_name RENAME 
    old_column_name TO new_column_name;

Here is an example for this,

ALTER TABLE student RENAME 
    address TO location;

The above command will rename address column to location.

ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns. Following is the syntax,

ALTER TABLE table_name DROP(
    column_name);

Here is an example for this,

ALTER TABLE student DROP(
    address);

The above command will drop the address column from the table student.

< prev
Next >