MySql
Query Syntax Of SQL
CREATE
CREATE TABLE employee (id_employee INT(10) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT(2),gender VARCHAR(2),address TEXT,id_dept INT(10));
CREATE TABLE IF NOT EXISTS table_name AS SELECT * FROM another_table_name;
SELECT
SELECT * FROM employee WHERE id_dept=10 ORDER BY id_employee DESC ;
SELECT * FROM employee_group WHERE id_dept=10 GROUP BY emp_group HAVING salary<10000 ORDER BY id_emp DESC LIMIT 0,5;
SELECT * FROM clients WHERE clno = (SELECT clno FROM firms WHERE city = 'bbsr');
INSERT
INSERT INTO employee (id_employee,name,age,gender,address,id_dept) VALUES ('','Rahul','25','M','Cuttack','20') ;
OR
INSERT INTO employee VALUES ('','Rahul','25','M','Cuttack','20') ;
We can also used insert statement with select statement
INSERT INTO employee SELECT * FROM employee_details;
REPLACE
REPLACE INTO test VALUES(2,'x');
Difference Between INSERT and REPLACE :
* REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts.
* Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
UPDATE
UPDATE employee set id_dept='30' WHERE name='Rahul' ;
DESCRIBE
DESCRIBE employee;
DELETE
DELETE FROM employee WHERE id_dept='20';
DELETE FROM employee;
TRUNCATE
TRUNCATE TABLE employee;
"TRUNCATE" deletes only the data, and resets the auto increment column to 0. "DELETE" deletes the selected/all rows from a table, it doest not reset auto increment, here we can delete set of records by specifying the condition in where clause.When "TRUNCATE" command is issued, all the rows from the table are deleted and the memory space occupied by the table in the tablespace is also released whereas when "DELETE" command is issued, only all the rows are deleted from the table not from the tablespace,resulting ROLLBACK of the records back where as in case of "TRUNCATE" it is not possible.
ALTER
ALTER TABLE test ADD COLUMN lname VARCHAR(30) AFTER first_name;
ALTER TABLE test ADD COLUMN(dob DATE,salary FLOAT(7,2));
ALTER TABLE test CHANGE address per_address VARCHAR(50);
ALTER TABLE test MODIFY COLUMN per_address VARCHAR(50) AFTER salary;
ALTER TABLE test DROP COLUMN lname;
DROP
DROP TABLE IF EXISTS test;
RENAME
RENAME TABLE emplyee TO emp;
SHOW
SHOW TABLES ;
SHOW DATABASES ;
SHOW ENGINES ;
SHOW VARIABLES LIKE '%query_cache%';