MySql basic operation summary

2011-03-17  来源:本站原创  分类:Database  人气:78 

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;

use andy;

drop database andy;

Table Management: use andy;

show tables;

describe tablename;

create table user (id int not null primary key, name varchar (22) not null);

drop tablename;

Index: create index index_name on tablename (colum1, colum2 ,...);

Management Data

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;

External connections:

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;

Alias: as

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

Advanced Features:

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".

Boolean model:

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.

Transaction processing;

Construction of the table or specify the type = InnoDB BDB

set autocommit = 0;

begin;

...

commit;

Commit can be issued before the rollback;

Table locking:

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;

...

unlock tables;

Batch:

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;

相关文章
  • MySql basic operation summary 2011-03-17

    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; use andy; drop database andy; Table Management: use

  • mysql basic operation 2010-04-07

    1, mysql service start and stop net stop mysql net start mysql 2, landing mysql Syntax is as follows: mysql-u username-p password type the command mysql-uroot-p, Enter your password after the prompt, enter 12345, then press Enter in the access to mys

  • mysql basic grammar summary (reproduced) 2010-03-29

    SQL Category: DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE) DML-Data Manipulation Language (SELECT, DELETE, UPDATE, INSERT) DCL-DCL (GRANT, REVOKE, COMMIT, ROLLBACK) First, a brief presentation of basic statement: 1, Description: Create

  • MySql basic operation [change] 2010-09-06

    - Create a database (test) drop database if exists test; create database test; - Delete database drop database test; - Create a table (userInfo) - Note: When the id is added when the word when inserting data corresponding to the value of the id shoul

  • MySql basic operation [turn] 2010-09-06

    - Create a database (test) drop database if exists test; create database test; - Delete database drop database test; - Create a table (userInfo) - Note: When the id is added when the word when inserting the data correspond to the id value should be n

  • MySQL frequently used commands (landing) and basic operations summary (reproduced) 2010-07-09

    Keywords: mysql MySQL basic operation of common operations, the following are MySQL5.0 tested by first description, remember the end of each command with; (semicolon) 1. Export the entire database mysqldump-u username-p - default-character-set = lati

  • MySql common command summary 2010-06-18

    MySQL basic operation of common operations, the following are MySQL5.0 tested by first description, remember the end of each command with; (semicolon) 1. Export the entire database mysqldump-u username-p - default-character-set = latin1 database name

  • Learning MySQL entry (1) installation and basic operation 2010-02-09

    From: http://www.bccn.net/Article/sjk/mysql/jc/200505/437.html Install Posts PHP + MySQL + Linux has become a classic small web server combination. In the Windows environment, building and debugging MySQL database is a lot of web developers choice. I

  • Learning MySQL entry (b) Basic operation 2010-02-09

    From: http://www.bccn.net/Article/sjk/mysql/jc/200505/443.html Learning articles Learn some basic operation command, let us learn how to create a database and database table. 1, using the SHOW statement to find out what currently exists in the server

  • mysql configure error solutions and basic operation 2011-09-22

    Suddenly two days to use the mysql database, rarely used, it is mounted a bar. Results in the last step when configuring the instance of the emergence of a common 2003 error: Can't connect to MySQL server on 'localhost' (10061) In a lot of online sea

  • MySQL5.0 the basic operation of common operations 2010-03-18

    MySQL5.0 the basic operation of common operations (2009-01-15 14:53:43) <script> </ script> Tag: it Category: Database Basic operation of common operations, the following are MySQL5.0 tested by first description, remember the end of each comma

  • Some transfer mysql insert operation (DELAYED. IGNORE.ON DUPLICATE KEY UPDATE) 2010-05-12

    Some mysql insert operation (DELAYED, IGNORE, ON DUPLICATE KEY UPDATE) INSERT syntax INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [ IGNORE ] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),.. . [ ON DUPLICATE KEY UPDATE col_na

  • Some mysql insert operation (DELAYED. IGNORE.ON DUPLICATE KEY UPDATE) 2010-06-29

    Some mysql insert operation (DELAYED, IGNORE, ON DUPLICATE KEY UPDATE) INSERT syntax INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name ,...)] VALUES ((expr | DEFAULT), ...), (...), ... [ON DUPLICATE KEY UPDATEcol_nam

  • The basic operation of XML documents 2010-08-17

    XML file is the most common operation in the development of the paper. Whether or WEB client application development, there is something extraordinary operation XML file. I am a beginner, now own XML file to add, delete, change the basic operation of

  • Ubuntu install and configure Mysql and operation commands 2011-01-22

    Ubuntu install and configure Mysql Three methods of installation: 1 online installation sudo apt-get install mysql-server. Zhuangwan has automatically configured the environment variables, you can directly use the mysql command. Note: recommended / e

  • Jboss Seam, mysql Chinese garbled summary of 4 steps 2009-08-17

    Jboss Seam, mysql Chinese garbled summary of 4 steps Keywords: seam hbm2ddl Chinese garbled mysql In fact, the following summary is suitable for any situation hibernate + mysql; Step 1: In the Create Database Schema Please specify code when, for exam

  • perl-list and array. basic operation. qw. scalar 2010-03-07

    1, List is included in brackets the value of a sequence, can be any value can be empty, such as: (1, 5.3, "hello", 2), an empty list: (). Note: The list contains only a numeric value (such as: (43.2)) and the value itself (ie: 43.2) is different

  • mysql common operation 2010-03-09

    First, connect MYSQL. Format: mysql-h host address-u username-p password 1, Example 1: Connect to the machine on MYSQL. First, open the DOS window, then enter the directory mysqlbin, then type the command mysql-uroot-p, a carriage return after the pr

  • Reprinted: Android Simulator Basic Operation 2010-06-02

    The basic operation of Android emulator 1, install JDK 2, install Android SDK Recommendation: Add the JDK, SDK environment variable, add the path to the path environment variable. Configuration steps: 1, enter the cmd command line mode, determine whe

  • PHP implementation of the text database data. Join. Modification. Delete. Check the basic operation of the five methods 2010-06-17

    http://www.php100.com/html/webkaifa/PHP/PHPyingyong/2009/0418/2866.html PHP implementation of the text data from the database to add, modify, delete, check the basic operation of the five methods I use this program as an example of a message to expla