rxz4wnv9

13. Creating database table :
2
3///primary key by deafult not null and unique
4
5CREATE TABLE [IF NOT EXISTS]  tablename(
6
7	col1 datatype [NOT NULL] [DEFAULT def_val] [UNIQUE] [AUTO_INCREMENT] [PRIMARY KEY],
8	col2 datatype .....................................,
9	col3 datatype .....................................,
10	.
11	.
12	.
13
14	CONSTRAINT const_name PRIMARY KEY (col1, col2, .....),
15	CONSTRAINT const_name1 UNIQUE (col4, col5, .....),
16	CONSTRAINT const_name2 FOREIGN KEY(col5, vol6,....) REFERENCES ref_table_name (ref_col1, ref_col2, ....)
17)
18
19
204. Deleting database table
21
22DROP TABLE [IF EXISTS] tablename1, tablename2,....;
23
24
255. Updating database table
26
275.1. Adding new column : 
28
29ALTER TABLE tabelname
30ADD COLUMN col_name DATATYPE [NOT NULL] [DEFAULT def_VAL] [AUTO INREMENT] [PRIMARY KEY] [UNIQUE];
31
325.2 deleting tbale column
33
34ALTER TABLE tablename
35DROP COLUMN column_name
36
375.3 Adding default constant
38
39ALTER TABLE tablename
40ALTER COLUMN column_name SET DEFAULT = def_val;
41
425.4 Deleting default constant
43
44ALTER TABLE tablename
45ALTER COLUMN column_name DROP DEFAULT;
46
47
485.5 Adding new CONSTRAINT ///(UNIQUE, PRIMARY, FOREIGN)
49
50ALTER TABLE tablename ///ONLY FIRST BRACKET IS PART OF CODE AND THE REST IS FOR OUR UNDERSTANDING
51ADD CONSTRAINT constraint_name {UNIQUE / PRIMARY KEY / FOREIGN KEY} (column1, column2, .......)  [REFERENCES ref_table (ref_col1, ref_col2,....)];
52
53
545.6 Deleting primary key
55ALTER TABLE TABLENAME
56DROP PRIMARY KEY;
57
585.7 Deleting foreign key
59ALTER TABLE TABLENAME
60DROP FOREIGN KEY foreign_key_constant_name;
61
625.8 Deleting nuique key
63ALTER TABLE TABLENAME
64DROP INDEX unique_key_constant_name;
65
66
67
68DML
691.Inserting data into table :
70
71SYTAX :
72INSERT INTO tablename[(column1, column2,......)] VALUES(val1, val2, val3,......);
73
74CODE :
75INSERT INTO tablename VALUES(a,b,c) ///since columns not specified, it will update all column sequentially
76INSERT INTO tablename(column1, column3,...) VALUES(val1, val2,....) ///ONLY UPDATE THOSE COLUMNS THAT ARE SPECIFIED
77
78
792. Deleting data from table
80
81syntax :
82DELETE FROM tablename
83WHERE condition;
84
85code :
86DELETE FROM tablename
87WHERE column1 < 3  AND column3 > 100
88
89
903. Updating data
91
92syntax :
93UPDATE tablename
94SET column1 = val1, column2 = val2
95WHERE condition
96
97
98code :
99UPDATE tablename
100SET column3 = 500, column2 = 'd'
101WHERE column1 >= 2;