MySQL Alter Table Add Column Command

Alter Table command is used for modifying the structure of the table. Means to add the new column, drop the column etc.

ADD COLUMN:
Syntax:
ALTER TABLE table_name ADD COLUMN column_name column-definition [ FIRST | AFTER col_name ]
Note: We can use both ADD COLUMN or ADD for adding the column.

Example 1:
ALTER TABLE student ADD COLUMN st_phone varchar(12) NOT NULL, st_gender varchar(5) NOT NULL;

Example 3:
ALTER TABLE student ADD COLUMN st_phone varchar(12) NOT NULL AFTER st_mobile,ADD COLUMN st_gender varchar(5) NOT NULL AFTER st_name,ADD COLUMN st_id int FIRST;

Example 4:
ALTER TABLE student ADD (st_phone varchar(100), st_email varchar(200),st_address varchar(200));

Example 5:
ALTER TABLE student ADD COLUMN st_phone varchar(12) DEFAULT NULL;

Example 6:
ALTER TABLE student ADD COLUMN st_id int NOT NULL AUTO_INCREMENT PRIMARY KEY;

»»  read more

MySQL Create Table Commands

Create Table command is used for creating tables in mysql.

Syntax 1:
CREATE TABLE [IF NOT EXISTS] table_name(
column_name1 type(size) constraints,
column_name2 type(size) constraints,
------------------------------------
------------------------------------
);

Constraints of tables: NOT NULL, AUTO_INCREMENT, PRIMARY KEY,UNIQUE
Note: The sequence of Constraints will be : NOT NULL, AUTO_INCREMENT, PRIMARY KEY,UNIQUE

Example:
CREATE TABLE student(
st_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
st_name varchar(200),
st_father varchar(200) NOT NULL
);

Syntax 2:
CREATE TABLE table_name(
column_name1 type(size) constraints,
column_name2 type(size) constraints,
------------------------------------
------------------------------------
column_name2 type(size) constraints,
PRIMARY KEY(column_name)
);

Example:
CREATE TABLE student(
st_id int(11) NOT NULL AUTO_INCREMENT,
st_name varchar(200),
st_father varchar(200) NOT NULL, PRIMARY KEY(st_id)
);

Syntax 3:
CREATE TABLE table_name SELECT column1,column2,column3 FROM table_name WHERE condition;

Note 1: The above syntax will create the backup of existing table, with the data structure and contents.

Note 2: The above command will not copy auto incremented fields and the primary key constraints.

Example:
CREATE TABLE student_backup SELECT * FROM student;

Show the structure of the table:

Syntax 1: DESC table_name;
Example: DESC student;

Syntax 2: EXPLAIN table_name;
Example: EXPLAIN student;

Syntax 3: EXPLAIN table_name;
Example: EXPLAIN student;

Note: EXPLAIN, DESC, DESCRIBE works as same way.

Syntax 4: SHOW CREATE TABLE table_name;
Example: SHOW CREATE TABLE student;

»»  read more

How to connect MySQL with Java?

Steps to Connnect the MySQL with JDBC:
------------------------------------------
1). Download MySQL database.
2). Install it.
3). Download the mysql-connector-java-5.1.10, it depends on your JDBC version
4). It is a type 4 driver.
5). Extract the mysql-connector-java-5.1.10 and copy the "mysql-connector-java-[version]-bin-g.jar" into such location where you can find it easily.
6). Now set the "CLASSPATH" for that jar file.
7). Copy the code and paste it to your file:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySQLCon {

  public static void main(String args[]) {
    Connection con = null;

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql:///DATABASE NAME","root", "");

      if(!con.isClosed())
        System.out.println("Successfully connected to MySQL server using TCP/IP...");

    } catch(Exception e) {
      System.err.println("Exception: " + e.getMessage());
    } finally {
      try {
        if(con != null)
          con.close();
      } catch(SQLException e) {}
    }
  }
}

8). Provide the Database Name and the credentials of the MySQL.
9). Compile it and run it.

»»  read more

MySQL User Management Commands

To create a user in MySQL:
CREATE USER 'user_name'@'hostname' IDENTIFIED BY 'password';

Note:All the users of MySQL are stored in the MySQL database inside the user table.

Example 1:
Create a user kaushal on localhost with password test:
CREATE USER 'kaushal'@'localhost' IDENTIFIED BY 'test'

Create a user kishore without password:
CREATE USER 'kishore'@'localhost';

To delete the user:
DROP USER 'username'@'localhost';

Example: DROP USER 'kishore'@'localhost';

To Rename the User:
RENAME USER 'username'@'hostname' TO 'username'@'hostname';

Example: RENAME USER 'kaushal'@'localhost' TO 'iics'@'localhost';

To show all the users of MySQL:
SELECT user FROM mysql.user;

To show User,Password,host of MySQL:
SELECT user,host,password FROM mysql.user;

Note:All the user information such as Hostname, username, password and its privileges for all databases are stored in “mysql” database inside the “user” table.

To reset the password for specific user:
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');

Example: SET PASSWORD FOR 'iics'@'localhost' = PASSWORD('hello');

Syntax 2:  UPDATE mysql.user SET password = PASSWORD('test') WHERE user='iics' AND host='localhost';

»»  read more

MySQL Basic Command List

Command for Create the database:
CREATE DATABASE database_name;
CREATE SCHEMA database_name;

Command For Creating The Database IF DATABASE NOT EXIST:

CREATE DATABASE IF NOT EXISTS database_name;
CREATE SCHEMA IF NOT EXISTS database_name;

To show the database:
SHOW DATABASES;

To show the particular database:
SHOW DATABASES LIKE “%search_text%”

To Delete the Database:
DROP DATABASE database_name;

To Delete the DATABASE if database available:
DROP DATABASE IF EXISTS database_name;

To select the database or change the database:
USE database_name;

To show all the tables of database:
SHOW TABLES;

To show the specific tables from database:
SHOW TABLES LIKE '%search_text%'

To show tables from another database:
SHOW TABLES FROM database_name;

For searching:
SHOW TABLES FROM database_name LIKE “%search_text%”;

To describe the table:
DESCRIBE table_name;

To show the create syntax of the Database:
SHOW CREATE DATABASE database_name;

To List all the Character Set of the MySQL:
SHOW CHARACTER SET;

Note: Character set shows the type of the character, which are storing in the database. Such as chinees, european, swidish, latin, arabic etc.

To show the sytax of create table:
SHOW CREATE TABLE table_name;

Show all the columns and the type of the table:
SHOW COLUMNS FROM table_name;

Note: It works just like a DESCRIBE table_name;

Show all the Open Tables and its status:
SHOW OPEN TABLES;

Show the error generated by Query and other operations:
SHOW ERRORS;

Note : It show the Error number and description also.

Count the number of Errors:
SHOW COUNT(*) ERRORS;
SELECT COUNT(*) ERRORS;
SELECT @@error_count;

Show all the warnings:
SHOW WARNINGS;

To Count the warnings:
SELECT COUNT(*) WARNINGS;
SHOW COUNT(*) WARNINGS;
SELECT @@warning_count;

Note: warning_count is the variable of MySQL and we can use the variable of MySQL using the “@@” symbol. Show the syntax is:

Syntax:
@@variable_name;

To show the Privileges of the current login user:
SHOW PRIVILEGES;

Note: It will display all the privileges of the current login user means that what type of operation he can be done. Some examples of privileges are ALTER, UPDATE, DROP, CREATE, DELETE etc.

To show all the Processlist:
SHOW PROCESSLIST;

Note: It will display all the process, users, states and the database which are currently running.

To show all the indexes of the tables:
1).
SHOW INDEX FROM table_name;
SHOW INDEX FROM student;

2).
SHOW INDEX FROM table_name FROM database_name;
SHOW INDEX FROM student FROM school;

3).
SHOW INDEX FROM database_name.table_name;
SHOW INDEX FROM school.student;

SHOW all the columns or fields name of the table:
1).
SHOW COLUMNS FROM table_name
SHOW COLUMNS FROM table_name FROM database_name;
SHOW COLUMNS FROM database_name.tablename;

2).
SHOW FIELDS FROM table_name;
SHOW FIELDS FROM table_name FROM database_name;
SHOW FIELDS FROM database_name.tablename;

3). DESC table_name;
4). DESCRIBE table_name;

To show all the database engines of MySQL:
1). SHOW ENGINES;
2). SHOW STORAGE ENGINES;
3). SHOW TABLE TYPES;

To show the status of any Engine:
SHOW ENGINE engine_name STATUS;
Example:
SHOW ENGINE INNODB STATUS;

To show the Logs for any Engine:
SHOW ENGINE engine_name LOGS;
Example:
SHOW ENGINE INNODB LOGS;

To Status of the tables of databse:
SHOW TABLE STATUS;

Note: It displays all the data of the table structure with advance options.

»»  read more

MySQL Revoke Command Syntax And Example

The opposite of GRANT is REVOKE. It is used to take privileges away from a user. It is very similar to GRANT in syntax. The REVOKE command is used to rescind privileges previously granted to a user.
Syntax:
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name …]

To revoke the SELECT persmission from the “payroll” database:
REVOKE SELECT ON payroll.* FROM 'iics'@'localhost';

To revoke the DELETE persmission from the “student” table from “school” database:
REVOKE DELETE ON school.student FROM 'iics'@'localhost';

To revoke the ALL privileges from the “school” database:
REVOKE ALL PRIVILEGES ON school.* FROM 'kaushal'@'localhost';

To revoke the all privileges from the “hospital” database:
REVOKE ALL ON hospital.* FROM 'iics'@'localhost';

To revoke MULTIPLE Privileges to all tables of customer database:
REVOKE SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* FROM 'iics'@'localhost';

To revoke the all privileges from the all databases and tables:
REVOKE ALL ON *.* FROM 'iics'@'localhost';

To Revoke INSERT,SELECT Privileges to specific columns of the tables:
REVOKE SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'iics'@'localhost';

»»  read more

MySQL Grant Command Syntax and Examples

To Give all the permission to iics user to school database:
GRANT ALL ON school.* TO 'iics'@'localhost';

Give the SELECT Privileges to “invoice” table of “db2” database:
GRANT SELECT ON db2.invoice TO 'iics'@'localhost';

Give the USAGE Privileges to all database and tables:
GRANT USAGE ON *.* TO 'iics'@'localhost';

Give All the Privileges to all database and tables:
GRANT ALL ON *.* TO 'iics'@'localhost';

Give INSERT,SELECT Privileges to all database and tables:
GRANT SELECT, INSERT ON *.* TO 'iics'@'localhost';

Give INSERT,SELECT Privileges to specific database and tables:
GRANT SELECT, INSERT ON mydb.* TO 'iics'@'localhost';

Give INSERT,SELECT Privileges to specific columns of the tables:
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'iics'@'localhost';

Give ALL Privileges to all database and tables with new password:
GRANT ALL PRIVILEGES ON *.* TO db_user @'%' IDENTIFIED BY 'db_passwd';

Give MULTIPLE Privileges to all tables of customer database:
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO 'custom'@'localhost';

To Show all the GRANT Permissions to Current Login User:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();


To Show all the GRANT Permissions for specific user:
SHOW GRANTS FOR 'iics'@'localhost';

To list all the privileges supported by MySQL:
SHOW PRIVILEGES;

To flush all the privileges:
FLUSH PRIVILEGES;

»»  read more

Difference Between mysql_connect() and mysql_pconnect()

mysql_connect() and mysql_pconnect() both are working for database connection but with little difference. In mysql_pconnect(), ‘p’ stands for persistance connection.

When we are using mysql_connect() function, every time it is opening and closing the database connection, depending on the request .

But in case of mysql_pconnect() function,
First, when connecting, the function would try to find a (persistent) connection that’s already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.
Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the connection will remain open for future use (mysql_close() will not close connection established by mysql_pconnect()).

mysql_pconncet() is useful when you have a lot of traffice on your site. At that time for every request it will not open a connection but will take it from the pool. This will increase the efficiency of your site. But for general use mysql_connect() is best.

I think this is a very imp concept in case of Database Connectivity.

»»  read more

MySQLAdmin Command

mysqladmin is a client for performing administrative operations.
You can use it to check the server’s configuration and current status,
create and drop databases, and more.

Check whether MySQL Server is up and running?
	mysqladmin -u root -p ping
Find out what version of MySQL I am running
	mysqladmin -u root -proot version
Current status of MySQL server
	mysqladmin -u root -proot status
Variable Names and Description:
	Uptime: 	   Uptime of the mysql server in seconds
	Threads: 	   Total number of clients connected to the server.
	Questions:         Total number of queries the server has executed since the startup.
	Slow queries:      Total number of queries whose execution time was more than long_query_time variable’s value.
	Opens: 	           Total number of tables opened by the server.
	Flush tables:      How many times the tables were flushed.
	Open tables:       Total number of open tables in the database.

View all the MySQL Server status variable and it's current value
	mysqladmin -u root -proot extended-status
To display all MySQL server system variables and the values
	mysqladmin  -u root -proot variables
To display all the running process/queries in the mysql database
	mysqladmin -u root -proot processlist
Create a MySQL Database
	mysqladmin -u root -proot create testdb
Delete/Drop an existing MySQL database
	mysqladmin -u root -proot DROP testdb
Reload/refresh the privilege or the grants tables
	mysqladmin -u root -proot reload
	mysqladmin -u root -proot refresh
Shutdown the MySQL Server:
	mysqladmin -u root -proot shutdown
Kill a hanging MySQL Client Process
	mysqladmin -u root -proot kill processID

Note: In above examples, -proot,-p represents the password of the mysqladmin server and there should not be space between -p and password.

»»  read more