SQL_study_notes2：the_management_of_tables

1 select statement
select * from * where * and * / * or */ is null/ not * like ‘%aa’ or ‘aa %’/ not * like ‘_im’ or ‘_aa%’/
(not) * between * and */ in (‘aa’, ‘bb’)/ not in (‘aa’, ‘bb’);
notes: =: equal , <>: not equal
remark:
A aa >= ‘L’ means that the query return aa whose first letter is L or later.
B %: the percent is a stand-in for any number of unknown character, e.g. %im Ephraim tim
C _: the underscore is a stand-in for just one unknown character, e.g. _im similar to “Kim” “Tim”
D not: it could be put in front of the original sentence, which could express the reverse meaning
e.g. where not aa = 0 means where aa > 0 or aa < 0 / where aa <> 0

2 delete statement

delete from aa where a='dd';
### which means delete the rows whose the value of variable a is 'dd'

3 update statement

update aa set a='cc' where a='dd';
### which means the variable a in the rows whose the value of variable a is 'dd' would be updated to 'cc'.
### or
update aa set 
a = (select b from dd where age between 9 and 99); 

4 show statement

### show create table aa;
### show columns from aa;
### ??show rows from aa;
### show create database aaa;
### show index from aa;

5 the alter&add statement
this statement is used for add totally new columns

alter table aa
add column id int not null auto_increment first,
add primary key (id);
### Explanation: FIRST tells us the SQL to make the new column the first one in the list. This is optional but it is good form to put your primary key first.
### Summary: so the DELETE and UPDATE statement are used to modify the rows, meanwhile, the ALTER statement is used to modify the columns.
### And the ID column whose other rows' value records have already exist in the table would be added to values automatically because of the AUTO_INCREMENT.

### add one more column which is not belong to primary key
alter table aa
add column phone varchar(20) not null
after first_name;
## AFTER is optional. If you do not use it, the column is added to the end of the table.
### In addition, LAST, the_concrete_position-th(forth), FIRST, BEFORE can all be used with the similar function to the AFTER.

6 the alter&rename statement

alter table b.aa
rename to b.cc
### this statement can help to change the table's name from aa to cc

7 the alter&change statement
this statement is used for change the type and name for existing columns.

alter table aa
change column number id int not null auto_increment,
add primary key (id);
### this statement can help to change the column's name of table from "number" to "id" and it also make "id" the primary key
alter table aa
change column number id int not null auto_increment,
change column aaaaa a varchar(20) default null,
change column ccccc c int default 1,
add primary key(id);

8 the alter&modify statement
this statement is used for change the type for the existing columns without changing their names.

alter table aa
modify column a varchar(120) not null;
### whose meaning expressed is the same as the following statement but more concise
alter table aa
change column a a varchar(120) not null;

What is need to be attention, “Alter table modify column aa int after bb” is false expression,because you cannot actually change the column order once the table already has been created.The best you can do is to add a new column into the position you want and delete the old existing column, but you will lose all the data in the old column.

9 the alter&drop statement

alter table aa
drop column a,
drop column b;

Watch out! Once you have dropped a column, everything that was stored in it is also removed.

10 more comprehensive statement which combine the above statements

alter table hooptie
rename to car_table;
alter table car_table
add column car_id int not null auto_increment first,
add primary key(car_id);
alter table car_table
add column vin varchar(16) second,
change column mo model varchar(20) default null,
modify column color after model, ###??? not sure, which is contracdictory to the above sentence
modify column year sixth, ###??? not sure 
change column howmuch price decimal(7,2);

If I want to drop the existing primary key and create new prmary key, I can use the following
statement.

alter table aa drop primary key;
alter table aa add column id int not null auto_increment first,
add primary key(id);

### the method to delete auto_increment or add auto_increment.
alter table aa change column a a int not null auto_increment;
### or
alter table aa modify column a int not null auto_increment;
alter table aa change column a a int not null;

IN SUMMARY
ADD: when I want to add totally new coulmns into the table.
CHANGE: when I want to change both the name and the data type of a column.
MODIFY: when I wish to change only the data type of a column.
DROP: it drops the named columns from the table.
RENAME: change the name of your table.
We can change the order of your columns with using FIRST, LAST, BEFORE column_name, AFTER column_name, SECOND, etc.
WITH some RDBMSs, we can only change the order of columns in a table when you add them to a table.