MySQL Overview: MySQL is a relational database management system, the so-called Association database is to keep the data in different tables, rather than put all the data in a large warehouse. This increases speed and increases flexibility. and MySQL software is an open source software.
Note that MySQL supports the maximum range of timestamp problems on 32-bit machines, the supported range of values is preferably not more than 2030 years, and then if on a 64-bit machine, the year can reach 2106, and for date, and DateTime, the two types, is not related, can be expressed to 9999-12-31, so this point to note, and, in the installation of MySQL, we generally choose typical (typical installation) on it, of course, if there are other uses, it is best to choose complete (fully installed); During the installation process, the general will also allow you to choose the server type, there are three types of server choice, (Developer (development), Server machine (server), dedicated MYSQL server machine ( Dedicated MySQL server), choose which type of server, only to the Configuration Wizard on memory and so on, otherwise there is no impact; So, if we are developers, choose the development machine is OK, then there will be a Database Usage dialog box selection, We just have to follow the default.
To connect and disconnect the server:
Connection: At the Windows command prompt, enter a command set similar to the following: Mysql–h host–u user–p
For example, I entered when I was using: Mysql–h localhost–u root–p
Then you will be prompted to enter the user password, this time, if you have a password, enter the password to hit enter, if there is no password, directly hit enter, you can enter the database client; Connect to MySQL on the remote host, you can use the following command: Mysql–h 159.0.45.1–u root– P 123
Disconnect the server: After entering the client, you can enter the quit directly and then return to it;
The following is a description of the database related commands
You can enter the following command to the database table or database related operations, omitted here, and then directly to the text description;
Select version (), current
date;//from the server to get the current MySQL version number and the current date
Select user (); Get all users of the current database
Use DatabaseName; into the specified database, and then you can manipulate the tables in the database.
Show databases; Querying all the databases in the current database and displaying them;
Create batabase DatabaseName; creating databases, for example: Create Database manager;
Show tables; View all tables in the current database;
CREATE TABLE TableName (colums); Creates tables and assigns related columns to the table, for example: Create TABLE PET (name varchar), owner varchar, species varchar ( ), sex char (1), birth date,death date);
Describe tablename; Show all the information in the table in detail, for example: Describe pet;
You can insert multiple records at once with a command, for example:
Insert into pet values (' Puffball ', ' Diane ', ' hamster ', ' f ', ' 1993-12-3 ', null), (' Puffball ', ' Diane ', ' hamster ', ' f ', ' 1993-12-3 ', now ());
Select * from Pet; Check out all the records from the pet table and show them;
Delete from pet where id=1; Remove the record with ID 1;
Update pet Set birth= ' 2001-1-3 ' where name= ' bowser '; updates the value of the Bowser field in the record named birth;
Select distinct owner from pet; Choose a row from the pet table that has the value unique to the Owner field, and if there are multiple rows of records, the value of this field is the same, only one row of the last occurrence of this value is displayed;
For date Calculation:
Select name,birth,curdate (), (Year (Curdate ())-year (birth)) as the age from pet;
Here, the year () function is used to extract the years of the corresponding field, and, of course, the same month (), Day (), and so on;
In MySQL, SQL statements can use the like query, which can be used ""With any single character, with"% "with any number of characters, and SQL mode by default is case-insensitive, for example: SELECT
From the pet where name like '%fy ';
Of course, you can also use regular expression patterns to match.
At the same time in SQL, you should also pay attention to the grouping functions, sorting functions, statistical functions and other related uses, here only list one or two;
Select Species,count () from the pet group by Speceis;
Select
From pet order by birth desc;
Related operations for querying the maximum value:
Select Max (age) from pet;
Number of records before fetching, this is mainly used for paging query operation,
SelectFrom pet order BY birth DESC limit 3; Take the first three records,
Select * from Pet ORDER BY birth desc limit 0, 3; This can be used for paging query, the first parameter after the limit, is the starting position, the second parameter is the number of record bars;
About creating a table self-growing field:
Create table person (id int (4) is not null Auto_increment,name char (a) Not null,primary key (ID));
To modify a table operation:
Add fields to the table: note that in this place, if you are adding more than one field, you have to enclose it in parentheses, otherwise there will be a problem, if it is a single field, no parentheses are OK;
Alter table Test Add (address varchar () NOT NULL default ' XM ', email varchar (+) not NULL);
When the name of a field in a table is modified or its corresponding related property is modified, the change is used to manipulate it.
Alter table Test Change email email varchar (a) NOT null default ' ZZ ';//Do not modify field names
ALTER TABLE test Change email varchar (+) Not null;//modify field name
To delete a field from a table:
Alter table Test Drop email;//Delete a single field
Alter table Test Drop address,drop email;//Delete multiple columns
You can use drop to cancel the primary key and foreign key, for example:
Alter table test drop foreign key fk_symbol;
To delete an index:
Drop index index_name on table_name;
Example: Drop index t on test;
Insert a record into a table: note that when the table is inserted in the record is not all fields, you should list the field name before the line, or will be error;
Insert into test (name) VALUES (' Ltx ');
Insert into test values (1, ' ltx ');
You can also insert multiple columns of values into a table, such as:
Insert into Test (' Ltx '), (' Hhy '), (' XF ');
To delete a record in a table:
Delete from test;//deletes all records in the table;
Delete from test where id=1;//deletes records under specific conditions in the table;
When you want to query some fields from a table or multiple tables and then insert them into another table, you can use the insert .... select syntax;
Insert into TESTT (name) (select name from test where id=4);
Reading rows from a file into a data table, you can use the Load data infile statement;
Load data infile ' test.txt ' into table test;
You can use the describe syntax to get information about the column;
Describe test;//can view all the information of the test table, including the data type of the corresponding column field;
MySQL transaction-related syntax;
Start a new transaction: Start transaction or BEGIN TRANSACTION
Commit TRANSACTION: Commit
Transaction rollback: Rollback
The set Autocommit true|false statement can disable or enable the default autocommit mode, which can only be used for the current connection;
Example:
Start transaction;
Update person set name= ' LJB ' where id=1;
Commit | Rollback
Database management Statements
Modify user password: As an example of root user, you can write the following; Mysql–u root–p old password –password new password
Mysql–u Root–password 123;//The root user's password is changed to 123, because the root user started, there is no password, so-p old password is omitted;
For example, modify a user password with password: mysql–u ltx–p 123–password 456;
Add a user test1, password for ABC, so that he can at any time on the host login, and all databases have query, insert, modify, delete permissions.
Grant Select,insert,update,delete on
.to [e-mail protected] "%" identified by ' ABC ';
Add a test2 user, the password is ABC, so that he can only login on the localhost, and can query, insert, modify, delete operation of the database;
Grant Select,insert,update,delete on MyDB.
To [e- Mail protected] identified by ' ABC ';
If you do not want the user to test2 a password, you can then enter the following command to erase the password:
Grant select,insert,update,delete on MyDB.to [e-mail protected] identified by "";
Backup DATABASE Common commands: mysqldump–h host–u username–p dbname> save path and file name
Then enter, will let you input user password, enter the password, then enter the OK;
Mysqldump–hlocalhost–uroot–p Test >e:\db\test.sql
This command specifically explains:
The command is to back up the test database and store the contents of the backup as a Test.sql file, and save it under E:\db;
The command in the front of the test is the database name, and then after the database name to follow a ">", and then the next, is to write the location to save and save the file name;
Import the backed-up database into the database: that is, run the. sql file to import the database into the database.
First you have to create a database, and then run the following command: Mysql–hlocalhost–uroot–p Linux<e:\db\test.sql then enter, and then input the password can be;
Explain the above command: Linux is the name of the database to be imported, followed by the "<" symbol, followed by the database file to import;
To save a database export to an XML file, import data from an XML file to a database:
Export data in table: Mysql–x–h hostname–u username–p pwd–e "use Databasename;sql;" >xml file name
Or in another way: Mysqldump–xml–h hostname–u username–p pwd dbName tableName//This one is only for display in the current MySQL client, not saved to the file;
Related instructions:-X for the format of the file is XML, and then-E can not be written off, there is a double quotation marks will be manipulated in the statement, the single quotation mark does not work;
Example: mysql–x–hlocalhost–uroot–p–e "Use Test;select
from pet; ">e:\db\out.xml
To import data from an XML file to a database:
Insert into tableName values (1,load_file (' filepath '));
Example: INSERT into pet values (1,load_file ("E:\db\out.xml"));
To view the database status and query process:
Show status;//View status
Show processlist;//View Process
Change the user name to the following command:
Update set user= "new name" where user= "old user name";
To set administrator permissions for a database user:
Mysqladmin–h host–u username–p pwd;
Take the root user as an example;
Mysqladmin–h Localhost–u root–p 123;
Stored Procedures and functions
The stored programs and functions are using CREATE PROCEDURE and CREATE FUNCTION statements, a program is either a program or a function, using the call statement to invoke the program, and the program can only use the output variable return value;
To create a subroutine in MySql5.1, you must have create routine permission, and alter routine and execute permissions are automatically granted to its creator;
To create a stored procedure:
First declare the delimiter, the so-called delimiter refers to you notify the MySQL client you have entered a SQL statement character or string symbol, here we use "//" as the delimiter;
Delimiter delimiter \
such as: delimiter//
Then create the stored procedure:
Create Procedure Stored Procedure name ()
Declare the stored procedure to begin:
Begin
Then start writing the stored procedure body:
Selectfrom Pet;
To end a stored procedure:
end;//
Just the example is all written out, the complete code is:
Delimiter//
Create procedure SPT ()//Note that this place has a space between the stored procedure name and the parentheses
Begin
Select * from Pet;
end;//here, the entire stored procedure is finished.
To execute a stored procedure:
Call stored procedure name ();//
For example, we execute the stored procedure that we just created, which is:
Call SPT ();//
It is necessary to note that the stored procedure name must be preceded by a space, and the latter parenthesis, is used to transfer parameters of the parameter list, in addition, we create a stored procedure is completed, but also only created, but only call stored procedure name (),//After the execution is completed, to see the results of the stored procedure;
In addition, the group IDC online has a lot of products group, cheap and reputation
Jinwan login What does the values in the database mean? Buckle _892118
