Start and stop services: net start mysql
net stop mysql
Enter the interactive command line: mysql-u root-p
mysql-u root-h db.imainary.com-p
Database operations: show databases;
create database andy;
drop database andy;
Table Management: use andy;
create table user (id int not null primary key, name varchar (22) not null);
Index: create index index_name on tablename (colum1, colum2 ,...);
Insert: insert into table_name (column1) values (value1);
insert into table_name set name = 'andy';
Sequence generation: definition of the table: auto_increment
update table_name set
Update: update table_name set column1 = value1 [where clause];
Delete: delete author from Author where Author.id = 1;
Query: select column1 from table1 [where clause];
select databse (); # returns the current name of the database
In connection: select book.title, author.name from author, book where book.asuthor = author.id;
select book.title, author.name from author left join on book book.asuthor = author.id;
Left join the table on the left includes all the rows, right join empathy.
Natural outer join:
select my_prod.name from my_prod natural left join their_prod
This natural connection will be listed in my_prod and their_prod table entry so the product has the same name.
Stock: select fname, lname from author
union select name, nname from editor;
Basic sort: select column1 from table order by column1;
desc reverse order.
Group: select rank from people group by rank;
Each type of grouping can be obtained using fg: the average wage
select rand, avg (salary) from people group by rank;
Limited results: where clause will scan the database table; having taken out the line to view!
select rank, avg (salary) from people where rank <> 'Private' group by rank having avg (salary)> 10000;
select * from people order by name limit 19,30;
# See line 20 to 49 rows
Logical operators: and, or, not
Members of the test: in, between .. and ..
Pattern matching: select name from people where name like 'andy%'
% Matches any number of characters; _ matches a single character.
Regular Expression Match: like an exact match
select name from people where name regexp 'andy' # anywhere andy
Full-text search: Full-text search key lies FULLTEST index.
create table document (
url varchar (25) not null primary key,
title varchar (100) not null,
page_text text not null,
fulltext (title, page_text)
Based on this table structure, you can search in the page title or page anywhere in the body contain the word "Mysql" document. Must ensure that the index to construct the query, rather than out. In other words, can match the table title and page_text, but can not find the title only in words, unless under the title only create a separate fulltext index.
select url from document where match (title, page_text) against ('java');
If the phrase in more than half of the line of java are there, so no search is considered to be a sense of the word (stopword) were ignored. For text matching, a common stopword is "the", "but".
Perform more complex full-text search, which uses the syntax and internet search engines used the same syntax.
select url, title from document where match (title, page_text) against ('+ mysql' - 'java' in boolean mode);
Adding a large amount of data, you should remove the fulltext indexes, insert data, then re-create the index. Table with a fulltext index to insert the data will be costly, and time index is able to work better.
Construction of the table or specify the type = InnoDB BDB
set autocommit = 0;
Commit can be issued before the rollback;
Table locking is a low level of transaction processing. Mysql allows you to lock a table so that only one client can be used.
Supports three kinds of locks: read, local read and write;
Read lock will lock the table for the client and all other customers to read. As long as the lock on the table can not be locked for writing. Read locks and read locks difference is local, local read lock allows a client to execute the insert statement of non-conflict, as long as no lock mysql from outside, you can change the mysql file. Otherwise, you need read locks.
Write lock will lock the specified table, it does not allow any customer to any other access, including read and write.
lock table account write;
Loading command line: mysql-h somehost-u uid-p <filename;
Load command: load data local infile 'books.dat' into table book;
load data local infile 'books.dat' into table book fields terminated by ',';
Remove the data from mysql: select * into outfile 'books.dat' fields enclosed by '"' terminated by ',' from book;