MySQL Common Operations Command

Installation: RPM Package

wget http://cdn.mysql.com/Downloads/MySQL-5.5/MySQL-server-5.5.27-1.linux2.6.i386.rpm

wget http://cdn.mysql.com/Downloads/MySQL-5.5/MySQL-client-5.5.27-1.linux2.6.i386.rpm

RPM–IVH mysql-server-5.5.27-1.linux2.6.i386.rpm

RPM–IVH mysql-client-5.5.27-1.linux2.6.i386.rpm

To view the running status:

Service MySQL Status

A To connect to MySQL:

Format: MySQL -H host address -u user name -p user password

Two Change Password:

Format: Mysqladmin-u username-P Old password password new password

Iii. Common commands:

1. Display database: Usedatabases;

2. Display the specific table: show tables;

3, display the structure of the data table:
describe table name;
4, build the library:
CREATE database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
select * from table name;

9. Other commonly used statements:

SELECT USER ();

SELECT now ();

Select version (), current_date ();

Select (20+5*10)/4;

10. Data Update

Insert into tablename ( column name ...) VALUES ( column value);

Insert into tables values(', ', ', ');

Update Tables setvalues= "where ...

11. Query result Limit bar number "limit", similar to the top in SQL Server

Select *from Info limit10

Data type:

Integral type: int

Character (string) type: char (10), variable character: varchar (50);

Time type: date,time,datetime,year;

12.mysql database script export and restore:

Mysql>mysqldumpbaibu–u root–r>baibu.sql---script export

Mysql>use Baibu; ---script restore

mysql> source /root/baibu.sql---database Script storage directory

SQL statement:

1. Determine how many records are in the data table

Select count (PubID) from publishers;

2. removal of excess Distinct

Select count (distinct PubID) from publishers;

SelectCount (*)/count (distinct titleid) from info;

3. Sort the results of the query

select* from authors Orderby AuthName (DESC)

4. Filter the data record: "Where,having"

Select* from info where sbbid= ' yu2000 '

Select* from info where snbid like (%yu);

Select* from info where snbid in (yu2000,yu2020);

5. Multi-table query:

You need to use the join syntax to construct:

Inner Join Inner Coupling

Outer Join outer coupling

INNER JOIN (INNER join): The most common use of a comparison operator (=,<>), will be based on a common value in two tables to match rows in two tables

SELECT Publname,title

Frompublishers Innerjoin titles

on Publishers.publid=titles.publid

ORDER by Publname

1. Merge Query Result: Union

Results of merging two select commands from the same data table:

SELECT * from authors WHERE authname like ' b% '

UNION

SELECT * fromauthors WHERE authname like ' g%

2. Modify the data

Insert

Update

Delete

Example: INSERT INTO

1) List the names of the data columns (with default values, allow null, do not list data columns with the Auto_increment attribute), and then write out the inserted data:

use mylibrary;

INSERT into titles (title,year)

VALUES (' MySQL ', 2005);

2) The name of the data column, you must provide the data values for all data columns in their order

INSERT into titles

VALUES (NULL, ' deleteme ', ' ', 1,null,null,null,2005,null,null,null,null)

Example: Update...set...where

UPDATE titles SET title= ' linux,6th ed. ' Wheretitleid=1

Example: Delete

Deletefrom titles where titleid=8;

Droptable

MySQL Service:

1. Start the service automatically:

Mysqlsystemtraymonitor

MySQL system administrator

Windows System Control

2. Command loading and unloading:

Mysqld-install mysql-defaults-file= "C:\mysql\my.ini"

Mysqld-remove MySQL

3. Manually start or stop:

Net start MySQL

Net stop MySQL

MySQL Common Operations Command