UwLh6HET

1mySQL
2creating tables
3simple example
4
5CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), 
6    species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
7if not exists
8
9CREATE TABLE IF NOT EXISTS pet (name VARCHAR(20), owner VARCHAR(20), 
10    species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
11copy table schema
12
13CREATE TABLE new_tbl LIKE orig_tbl;
14with primary key auto increment and not null
15
16CREATE TABLE t1 (
17    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
18    c2 VARCHAR(100),
19    c3 VARCHAR(100) )
20with table engine
21
22CREATE TABLE t1 (
23    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
24    c2 VARCHAR(100),
25    c3 VARCHAR(100) )
26ENGINE=InnoDB
27FOREIGN KEY
28CREATE TABLE ORDERS (
29   ID INT NOT NULL,
30   DATE DATETIME, 
31   CUSTOMER_ID INT references CUSTOMERS(ID),
32   AMOUNT double,
33   PRIMARY KEY (ID)
34);
35altering table
36adding foreign key
37
38ALTER TABLE ORDERS 
39   ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
40changing column name
41
42ALTER TABLE t1 CHANGE col1 newname BIGINT NOT NULL;
43remove column
44
45ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
46create new column
47
48ALTER TABLE mytable ADD COLUMN dummy1 VARCHAR(40) AFTER id;
49insert rows
50adding row
51
52INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
53adding multi rows
54
55INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
56drop table
57droping table
58
59DROP TABLE IF EXISTS table_name;
60remove row
61delete row example
62
63DELETE FROM somelog WHERE user = 'dan'
64  ORDER BY timestamp_column LIMIT 1;
65select
66simplest select
67
68SELECT * FROM tutorials_tbl
69specific columns
70
71SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
72  FROM tutorials_tbl
73where is
74
75SELECT * FROM tutorials_tbl WHERE tutorial_author = 'amit';
76order by and limit
77
78SELECT * FROM somelog WHERE user = 'dan'
79  ORDER BY timestamp_column LIMIT 1;
80inner join
81SELECT employees.id, employees.department_id, employees.name, departments.department
82FROM employees
83INNER JOIN departments ON employees.department_id = departments.id