MYSQL 备忘

having multiple occurrences of the same data is never a good thing ,and that princple is the basis for relational database design.Relational tables are designed so information is split into multiple tables,the tables are related to each other though

common values.The foreign key is a column which is the primary key of other table

1  %

1)case senstivity:Depending on how mysql is configured,searches might be case-senstive,in which case 'incomplete%' would not match 'Incompletely'.

2) like '%' DOES NOT MATCH  'NULL'

3) % match everything more than 1 or less than 1,zero is ok.however, _ means 1 character no more no more

4)wildcard searches typically take far longer to process than others.so do not use it if there is a replacement.

5)NULL don't confuse null value with the empty strings.A null value is the lack of a value,it is not empty string

 

 

 

2  calculated field

 

1)diff from oracle using || for concatnate,Concat() is using for mysql.

   // Concat(a,b,'(',c,')') concat the strings seperated by commas

  // RTrim() trims all the spaces right of a value

 // LTrim() trims all the spaces left of a value

 // Trim() trims all the spaces left and right

 and ofen alias is to name the one ,i.e. Concat(name,addr,'(',city,')') as ContactAddr

2)

 Left() // returns characters from left of string.   SELECT Left(name,3) FROM users;

 Length() // returns the length of string

 

length--------Length(str)

trim  --------LTrim(str) / RTrim(str)/Trim(str)

substr -------SubString(str,pos,len)/SubString(str,pos)

insert ------Insert(str,pos,len,newstr)

uppercase ---- UPPER(str)

lower -----LOWER(str)

soundex ----Soundex(str)

.........too many funcs here.

 

3)DATE

MYSQL:now() curdate() curtime() date() extract() date_add() date_sub(),datediff() date_format()

SQLSERVER:GETDATE() DATEPART() DATEADD() DATEDIFF(),CONVERT()

// if you want to simpility your sql,do not use time part ofen.

 

a) day of week    DAYOFWEEK(date)   SELECT DAY0FWEEK(order_date) from orders;

b) day of month  DAYOFMONTH(date)   from 1 to 31

c) day of year     DAYOFYEAR(date) from 1 to 366

d)date add      DATE_ADD(date,INTERVAL expr type)   ADDDATE(date,INTERVAL exp type)

                        DTE_SUB(date,INTERVAL expr type)     SUBDATE(date,INTERVAL expr type)

 

INTERVAL 1 MONTH NOT MEANS 31 DAYS OR 30 DAYS, BASE ON  THE DATE YOU ADD BY.

 

e) format         DATE_FORMAT(DATE,'%Y%m%d')      

f)diff of two date   DATEDIFF(date1,date2)

G)Date          Date(20101010)           // geneal the date TYPE 2010-10-10

                    DATE('2005-09-01 11:11:11')  // GENERAL THE DATE TYPE 2005-09-01 00:00:00

 so,if you want to match the date only ,use

SELECT ORDER_DATE FROM ORDER WHERE  DATE(ORDER_DATE)='20100101' FOR SAFE

 

4）avg() sum() count() max() min()

count(line) // return count ,ignore the line null

count(*) // return count,no regard of null of which columns

 

 

3 working with subqueries.

subqueries can be  IN = <>  and so on. use subqueries is not always the most efficient way to perform this type of data retrieval,although it might be.

 

4 join:one of the most important features.

first,you must understand relational tables and the basics of relational database design

 

 

a) inner join

select count(*) from a;//result:10

    select count(*) from b;// result:20

    select count(*) from a,b; // result:200 CartesianProduct   INNER JOIN ,the same a SELECT count(*) from a inner join b

join two tables means pairing every row in the first table with every row in the second table on-the-fly,so where filter needed

b)outer join

 

 

 

5.update

Don't omit the where Clause on UPDATE/DELETE before using this statement

To delete a columns's value,you can set it to NULL

 

DELETE takes no column names or wildcard characters

there is no undo button for delete,so make sure.

 

6. InnoDB / MyISAM

InnoDB:transational-safe engine,not support full-text searching

MyISAM:high-performance engine ,support full-text searching,not transational-safe

MEMORY:equivalent to MYISAM,stored in memory,extremely fast

// full text searching

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit')

 

7.updating tables:ALTER TABLE statement is used,but tables should never be altered after they contain data

 

8:create table: CREATE TABLE IF NOT EXIST test(name char(20) not null ,PRIMARY KEY(name));