Sql common usage
1.create table table_name(var1 type1,var2 type2)
CREATE TABLE Persons
( Id_P int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )
create table if not exists table_name(same ....);
2.desc table_name //to see the structure of table
3.delete from table_name where id>1 and id <10;
4.drop table table_name;
5.drop index index_name on table_name;
6.utf-8 3byte gbk 2byte so->varchar(12) have 6 chinese words and utf-8->varchar(12) have 3 chinese words
7.create database database_name; use database_name;
8.update table_name set field='value" where condition(id>=5 && id <=19);
update table_name set field=field+1 where id='value';
9.delete from table_name where
10.select table_name.field,table_name.field from table_name
select field as alias from table_name
select field1,field2,field2*0.8 as discount from table_name
select * from table_name where id between 10 and 20;
select * from table_name where id not between 10 and 20;
select * from table_name where id in(10,29,32);
11. _ present a char and % present any chars;
select * from table_name where name like "%ly";//选出已ly结尾的
select * from table_name where name not like ""//
12.select * from table_name where id=(select max(id)from table_anem);
//It's good solution
13.show databases; //show databases
14.use databases;//use database;
15.select distinct name from table_name //display only once and remove the dumplicate
16.select age from table_name where age>12;//数值不使用单引号，字符使用单引号
17.select * from table_name order by age;//根据年龄默认升序排列
select * from table_name order by age DESC//降序排列
18.select top 12 * from table_name //选出前12条的记录的内容
19.select top 12 percent * from table_name //选出12%的记录的内容
20.select * from table_name where city like "%lon%";//这里的%就是*的意思，是一种通配符查找 表示一个或多个字符，_这个表示一个字符
21.sql的类正则表达：
select * from table_name where city like '[ABC]%'//以任意三个字母开头的字符串，放过来就是[!ABC]%
22.select * from table_name where column_name in ('value1','value2');//从字段column_name中找出数值为value1跟value2
23.select t1.id t2.name from table1_name as t1 ,table2_name as t2 where t1.id=1;//将两个表重新命名，使用别名
seleect id as MyId name as MyName from table_name //别名显示
24.select * from b inner join a on a.id=b.id where b.id=2;//用于多表的查询，特别有意思
