Look at their online, write is the same, and, at the same time, a lot of writing is not very good, the following is my own summary of the use of MySQL details, is also my study in the process of some records it, I hope that you have a little help, the following about the stored procedures and other related operations have not been summed up good, the next time ~~~~~ mysql Study Notes mysql Overview: MySQL is a relational database management system, 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, With datetime These two types, then no relationship, 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 (full installation), during the installation process, the general will let you choose the server type, there are three types of server choice, (Developer (dev), Server machine (server), dedicated MYSQL Server machine (dedicated MySQL server), the choice of which type of server, only the configuration wizard on memory, such as the impact, otherwise there is no impact on the other side, so if we are developers, choose the development machine can be, and then next, There will also be a choice of database Usage dialog box, we can just follow the default; connect and Disconnect server: Connect: Enter a command set similar to the following at the Windows command prompt: Mysql–h host–u user–p For example, I entered when I was using: MySQL –h Localhost–u Root–p then will prompt you 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 the; The following is a description of the database-related commands you can enter the following command to the database table or database related operations, here is omitted, 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 user use databasename of current database; Enter into the specified database, then you can manipulate the table in this database show databases; //queries all the databases in the current database and displays them; create batabase databasename; Creates a database, for example: Create DB Manager; Show tables; //View all tables in the current database, CREATE TABLE tablename (colums), make tables, and specify related columns for the table, for example: Create table PET (name varchar), owner varchar (species), sex char (1), birth date,death date), describe tablename, all the information in the table is displayed in detail, For example: describe pet; you can insert multiple records with a command at once, 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, display them, delete from pet where id=1; Remove the record with ID 1; Update pet set birth= ' 2001-1-3 ' where name= ' Bowser '; Update the value of the birth field in the record named Bowser, select distinct owner from pet, choose the row with the value of the Owner field from the Pet table, and if there is more than one row, the value of this field is the same. Displays only one row of the last occurrence of this value; about date calculation: select name,birth,curdate (), (Year (Curdate ())-year (birth)) as aGE from Pet; here, the year () function is used to extract the years of the corresponding field, and, of course, month (), day () and so on; in MySQL, SQL statements can use the like query, you can use "_" with any single character, with "%" With any number of characters, and SQL mode defaults to ignoring the case, for example: SELECT * from pet where name like '%fy '; Of course, you can also use regular expression pattern to match. At the same time in SQL, also pay attention to grouping functions, sorting functions, statistical functions and other related usages, here only one or two; Select species,count (*) from the pet group by Speceis; SELECT * from the pet order by birth desc; queries the maximum value of the operation: select Max (age) from pet, the number of records before fetching, this is mainly used for paging query operation, Select * from Pet o Rder 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 queries, the first parameter after the limit, is the starting position, the second parameter is fetch record Number of; about creating a table self-growing field: CREATE TABLE person (ID int (4) NOT null Auto_increment,name char (a) Not null,primary key (ID)); &n Modify Table Actions: Add a field to a table: note that in this place, if you add 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 (50) Not null default ' XM ', e-mail varchar (+) NOT null); the name of a field in the table to modify or modify its corresponding related properties, you need to use the change to manipulate it; Alter table test Change email varchar (a) NOT null default ' ZZ ';//Do not modify field name alter table test Changes email varchar (+) nOT null;//Modify field names Delete fields in table: ALTER TABLE test drop email;//Delete 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; DROP index index_name on table_name; For example: Drop INDEX t o n test; Inserts a record into a table: note that the field names should be listed before they are not all fields when inserting records in the table, otherwise they will be error; INSERT into Test (' 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 '), delete table records: Delete from test;// Delete all records in the table; Delete from Test where id=1;//deletes records under specific conditions in the table; when you want to query out some fields from a table or multiple tables and then insert them into another table, you can use Insert .... Select syntax, insert into TESTT (name) (select name from Test where id=4); reads rows from a file into the data table, can be used with the load data infile statement; load data in The file ' test.txt ' into table test; can get information about the column in Describe syntax; Describe test;//can view all the information in 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: Rollbackset autocommit true|false Statement can disable or enable the default autocommit mode, only available for the current connection; example: start transaction; updatePerson set name= ' LJB ' where Id=1; commit | rollback; database Management Statements to modify the user password: As an example, the root user can be written as follows; Mysql–u root–p old password –password new password mysql–u root–password 123;// The root user's password is modified to 123, because the root user started, there is no password, so-p old password is omitted; For example, modify a password user password: mysql–u ltx–p 123–password 456; Add a user test1 , the password is ABC, so that he can log on at any time on the host, and all databases have query, insert, modify, delete permissions. Grant select,insert,update,delete On * * to [email protected] "%" identified by ' ABC '; add a test2 user with a password of 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 [email protected] Identified by ' ABC '; If you do not want the user to test2 a password, you can enter the following command to erase the password: Grant select,insert,update,delete on mydb.* to [email Protected] identified by ""; Backup Database Common command: mysqldump–h host–u username–p dbname> save path and file name and then enter, will let you input user password, enter the password, then return to 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; in the command, the test in front of-p is the database name, followed by a ">" after the database name, and then the next, is to write the location to save and save the file name; Import the backed-up database into the database:The. sql file imports the database into the database. First you have to create the database, and then run the following command: Mysql–hlocalhost–uroot–p linux<e:\db\ Test.sql and then enter the password, you can 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 the database exported to an XML file, Import data from XML file to database: export table data: mysql–x–h hostname–u username–p pwd–e "use Databasename;sql;" >xml file name or another way: mysq Ldump–xml–h hostname–u username–p pwd dbName tableName //This type is only used for display in the current MySQL client, not saved to the file; Description:-x represents the file format is XML, and then -E can not be written off, there is to use double quotation marks to operate the statement enclosed, single quotation marks do not, for example: mysql–x–hlocalhost–uroot–p–e "Use test;select * from pet;" >e:\db\ Out.xml importing data from an XML file into a database: INSERT into tableName values (1,load_file (' filepath ')); For example: INSERT INTO pet values (1,load_file ("E : \db\out.xml ")); View database status and query process: show status;//View status show processlist;//view process change username for command: Update set user= "new name" where user= "old username"; set administrator rights to the database user: Mysqladmin–h host–u username–p pwd; take root as an example; mysqladmin–h localhost –u root–p 123; stored procedures and functions stored procedures 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 processThe program can only return values with output variables; 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; create stored procedure: first declare the delimiter , the so-called delimiter is that you notify the MySQL client that you have entered a SQL statement character or string symbol, where we use "//" as the delimiter; delimiter delimiter \ Delimiter//Create stored procedure: Procedure stored procedure name () declares that the stored procedure begins: Begin and then start writing the stored procedure body: Select * from pet; end 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 Beginselect * from pet; end;//here, the entire stored procedure is finished. Execute stored procedure: Call stored procedure name ();//For example, we execute the stored procedure just created, that is: Call SPT ();// need to note that the stored procedure name must be preceded by a space, The latter bracket is the parameter list for the parameter, and we create the stored procedure, but only the call stored procedure name () is called, and then the execution is completed before we can see the result of the stored procedure; "Connect MySQL, change password, Add users " has many friends who have installed MySQL but don't know how to use it. In this article, we will learn some common MySQL commands from connecting MySQL, changing passwords, and adding users. , connect MySQL. Format: mysql-h host address-u user name-P user Password 1, example 1: Connect to MySQL on this computer. First open the DOS window, then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is:mysql> 2, example 2: Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command: mysql-h110.110.110.110-uroot-pabcd123 (Note: You and root can not add space, the other same) 3, quit MySQL command: Exit (enter) Two, change the password. Format: Mysqladmin-u user name---old password password new password 1, example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command mysqladmin-uroot-password AB12 NOTE: Because Root does not have a password at the beginning, the-p old password can be omitted. 2, Example 2: Then change the root password to djg345. &NBSP;MYSQLADMIN-UROOT-PAB12 Password djg345 Third, add new users. (Note: Unlike above, the following is because it is a command in the MySQL environment, so a semicolon is followed as a command terminator) format: Grant select on database. * To username @ Login host identified by \ "Password \" Example 1, Add a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First use the root user to connect to MySQL, and then type the following command: grant select,insert,update,delete on * * to [email protected]\ "%\" identified by \ "Abc\"; But Example 1 added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on to your MySQL database and your data can do whatever you like, the workaround is shown in Example 2. Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can be queried, inserted, modified, deleted operations (localhost refers to the local host, that is, the MySQL database is located on the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host. grant Select,insert,update,delete on mydb.*To [email protected] identified by \ "Abc\"; If you do not want to test2 have a password, you can use another command to erase the password. grant Select,insert,update,delete on mydb.* to [email protected] identified by \ "\"; (next) in the previous article we talked about login, add user, password change and other issues. In the next section, we'll look at MySQL's operations on the database. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon. operation Skills 1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it. In other words, you can break a complete command into a few lines, and then use a semicolon to make the end sign OK. &NBSP;2, you can use the cursor up and down keys to bring up previous commands. But previously I used an old version of MySQL that was not supported. I'm using Mysql-3.23.27-beta-win now. Display the command 1, display the database list. show databases; just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate. &NBSP;2, display the data table in the library: use MySQL;//Open the library, learn foxbase must not be strange show tables; 3, display the structure of the data table: describe table name; 4, build Library: create database name; 5, Build table: use library name, create table name (field setting list), 6, delete library and delete table: drop database name; drop table table name; 7, clears records from tables: delete from table name; 8, display the records in the table: select * from table name; a city build and build tables and insert dataExample drop database if exists school; Delete create Database School if school is present; Establish library school use School; Open Library School create table teacher//Build Tables teacher ( id int (3) auto_increment NOT NULL primary key, nam e Char (TEN) NOT NULL, address varchar (() default \ ' Shenzhen \ ', year date ); Build table End //The following is the Insert field insert into teacher values (\ ' \ ', \ ' glchengang\ ', \ ' Shenzhen one in \ ', \ ' 1976-10-10\ '); insert into teacher values (\ ' \ ', \ ' jack\ ', \ ' Shenzhen one in \ ', \ ' 1975-12-23\ '); Note: In the table (1), set the ID to a number field of length 3: Int (3) and let it automatically add one to each record: Auto_increment cannot be empty: NOT null and let him become the main field primary Key (2) sets the name to a character field of length 10 (3) To set address to a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char, only to wait for a later article to say. (4) Set year as the Date field. If you type the above command at the MySQL prompt, it's not easy to debug. You can write the above command as-is to a text file, assume School.sql, then copy it to c:\\, and enter the directory \\mysql\\bin in DOS, and then type the following command: mysql-uroot-p password < c:\\ School.sql If successful, empty a row without any display, if there is an error, there will be a hint. (The above command has been debugged, you can use it only if you remove//comment). Four, the text data to the database 1, text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by the \\n. Example: 3 rose Shenzhen II 1976-10-10 4 Mike Shenzhen A 1975-12-23 2, data incoming command the load information local infile \ "file name \" into table name; NOTE: You'd better copy the files to the \\mysql\\bin directory, and first use the using command to hit the library where the table is located. Backup Database: (command executes in DOS \\mysql\\bin directory) mysqldump--opt school>school.bbb Comments: Back up the database school to the school.bbb file, school.bbb is a text file, the filename is taken, open to see what you will find. PostScript: In fact, MySQL database operation and other SQL class database is much the same, you'd better find a book to read SQL. I am here only to introduce some basic, in fact, I only understand these, hehe. The best MySQL tutorial or "Yanzi" translated "MySQL Chinese reference manual" not only free every relevant website has downloaded, and it is the most authoritative. Unfortunately not like the "PHP4 Chinese manual \" that is the format of the CHM, in the search function command is not very convenient.
SQL Authorization Statement (MYSQL basic statement)
