Embedded Files
Comments:
# For single line comment
/*For Multi line comment*/
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)
Create Database.
CREATE DATABASE school;
USE school;
Create table.
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
contact LONG,
address TEXT,
active BINARY,
lu DATETIME);
Alter table.
# To Add Column
ALTER TABLE student ADD COLUMN new_Col int AFTER name;
# To Change data type, name, sequence etc
ALTER TABLE student CHANGE COLUMN new_col new_col VARCHAR(100) AFTER address;
# To Drop Column
ALTER TABLE student DROP COLUMN new_col;
View table details.
SHOW CREATE TABLE student; #Shows create statement for table.
SHOW TABLE STATUS LIKE 'student'; #Shows complete table details like- data volume, engine version, row length etc.
Drop table.
DROP TABLE student;
Drop database.
DROP DATABASE school; #Use other before dropping the target database.
Insert records.
INSERT INTO student VALUES(1, 'Rohit', '9967425667', 'Kharghar', 1, '2016-10-23 15:22'); #all column values
INSERT INTO student(name, contact, address, active, lu) VALUES('Rahul', '123444322', 'Kharghar', 1, '2016-10-23 15:22'); #specific column values
Simple selects from table
SELECT * FROM student; #fetch all rows
SELECT * FROM student LIMIT 10; #fetch top 10 rows.
SELECT * FROM student LIMIT 2, 5; #fetch top 5 rows starting from 3rd row
Find size of lengthiest data in a column
Compare password
select * from users where `password` = '' + password('rohit');
Row number assignment...
Useful for sorting, finding de-duping etc..
SET @ronum=0; SELECT *, @ronum:=@ronum+1 as row_number FROM student;
Case when then
if else
Google Sites
Report abuse