Basic operation statements for MySql applications

1. export mysqldump-u username-p -- default-character-set = latin1 Database Name> exported file name (the default database encoding is latin1) mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. sql2. export a table mysqldump-u user name-p database name Table Name> export file name mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users.sql3. export a database structure mysqldump-u wcnc-p-d-add-drop-table smgp_1__wcnc> d: the wcnc_db.sql-d does not have data-add-drop-table add A drop table4. import database A before each create statement: common source commands go to the mysql Database Console, such Mysql-u root-pmysql> use Database and then use the source command. The following parameter is a script file (as used here. SQL) mysql> source wcnc_db. SQL www.2cto.com B: Use mysqldump command mysqldump-u username-p dbname <filename. sqlC: use mysql Command mysql-u username-p-D dbname <filename. SQL 1. Start and Exit 1. Enter MySQL: Start MySQL Command Line Client (DOS interface of MySQL) and enter the password for installation. The prompt is: mysql> 2. exit MySQL: quit or exit.
Ii. database operation 1. create database Command: create database <database Name> For example: create a database named xhkdb mysql> create database xhkdb; 2. Display All database commands: show databases (Note: There is a last s) mysql> show databases; 3. delete a database command: drop database <database Name> example: delete a database named xhkdb mysql> drop database xhkdb; 4. Database Connection command: use <Database Name> for example, if the xhkdb database exists, try to access it: mysql> use xhkdb; on-screen prompt: database changed5. view the currently used database mysql> select Database (); 6. The table information contained in the current database: mysql> show tables; (Note: There is finally s) 3. Table operations. before the operation, you must connect to a database., Create a table www.2cto.com command: create table <table Name> (<field name 1> <type 1> [,.. <field name n> <type n>]); mysql> create table MyClass (> id int (4) not null primary key auto_increment,> name char (20) not null,> sex int (4) not null default '0',> degree double (); 2. Table Structure command: desc table name, or show columns from table name mysql> DESCRIBE MyClassmysql> desc MyClass; mysql> show columns from MyClass; 3. drop table command <table Name> example: delete A Table named MyClass mysql> drop Table MyClass; 4. insert Data command: insert into <table Name> [(<field name 1> [,.. <field name n>])] values (value 1) [, (value n)] For example, insert two records into the MyClass table. The two records indicate: the result of "Tom" in "1" is 96.45, the result of "Joan" in "2" is 82.99, and the result of "Wang" in "3" is 96.5.mysql> insert into MyClass values (1, 'Tom ', 96.45), (2, 'job', 82.99), (2, 'wang', 96.59); 5. query the data in the table 1), query all the line commands: select <Field 1, Field 2,...> from <Table Name> where <expression> For example: view all data in the MyClass table mysql> select * from MyClass; 2) query the data of the first few rows, for example: Query See mysql> select * from emp order by empno limit; or: mysql> select * from emp limit; 6. Delete table data command: delete from table name where expression for example: delete the record mysql> delete from MyClass where id = 1 in the table MyClass; 7. Modify the data update table name set field = new value ,... Where condition mysql> update MyClass set name = 'Mary 'where id = 1; 7. add a field to the table: Command: alter table name add field type other; for example: A passtest field is added to the MyClass table and the type is int (4). The default value is 0 mysql> alter table MyClass add passtest int (4) default '0' 8. Change table name: Command: rename table original table name to new table name; for example, change MyClass name to YouClass mysql> rename table MyClass to YouClass; 9. update field content update table name set field name = new content update table name set field name = replace (field name, 'old content', 'new content '); add four spaces to update artic before the article Le set content = concat ('', content); field type 1.INT[ (M)] type: normal Integer type 2. DOUBLE [(M, D)] [ZEROFILL] type: normal size (DOUBLE Precision) floating point number type 3. DATE type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays the DATE value in YYYY-MM-DD format, but allows you to assign the value to the DATE column with a string or number 4. CHAR (M) type: fixed-length string type. When stored, it is always filled with spaces to the right to the specified length of 5. blob text type. The maximum length is 65535 (2 ^ 16-1) characters. 6. VARCHAR: variable-length string type www.2cto.com 5. import database table (1) create. SQL file (2) First generates a library such as auction. c: mysqlbin> mysqladmin-u root-p creat auction. A message is displayed, indicating that the password is entered and created successfully. (2) import the auction. SQL file c: mysqlbin> mysql-u root-p auction <auction. SQL. With the preceding operations, you can create a database auction and a table auction. 6. modify database (1) add a field to the mysql table: alter table dbname add column userid int (11) not null primary key auto_increment; in this way, in the dbname table, a field userid is added, whose type is int (11 ). 7. mysql database authorization mysql> grant select, insert, delete, create, drop on *. * (or test. */user. */..) to username @ localhost identified by 'Password'; for example, to create a user account to access the database, you need to perform the following operations: mysql> grant usage-> ON test. *-> TO testuser @ localhost; Query OK, 0 rows affected (0.15 sec) then a new user named testuser has been created, this user can only connect to the database from localhost and connect to the test database. Next, we must specify the operations that testuser can perform: mysql> GRANT select, insert, delete, update-> ON test. *-> TO testuser @ localhost; Query OK, 0 rows affected (0.00 sec) This operation enables testuser TO perform SELECT, INSERT, DELETE, and UPDATE Query operations on tables in each test database. Now we end the operation and exit the MySQL client program: mysql> exit Bye9! 8: display table structure: mysql> describe mytable; 9: Add a record to the table mysql> insert into MYTABLE values ("hyq", "M"); 10: load data into a database table (for example, D:/mysql.txt) using text: 3 rose Shenzhen No. 2, 1976-10-10 4 mike Shenzhen No. 1, 1975-12-23mysql> load data local infile "D: /mysql.txt "into table mytable; 11: import. SQL file commands (such as D:/mysql. SQL) mysql> use database; mysql> source d:/mysql. SQL; in windows, MySql exists as a Service. Before use, make sure that the service has been started, and the net start mysql command is not available to start. In Linux, the "/etc/rc. d/init. d/mysqld start" command is available. Note that the initiator must have administrator privileges. The newly installed MySql contains a root account with a blank password and an anonymous account, which poses a major security risk. For some important applications, we should improve the security as much as possible, here, you should delete anonymous accounts and Set passwords for root accounts. You can run the following command: use mysql; delete from User where User = ""; modify the password: update User set Password = PASSWORD ('newpassword') where User = 'root'; to restrict the logon terminal used by the User, you can update the Host field of the corresponding User in the User table, after the above changes, you should restart the database service. At this time, the following commands can be used for Logon: mysql-uroot-p; www.2cto.com mysql-uroot-pnewpassword; mysql mydb-uroot-p; mysql mydb-uroot-pnewpassword; command parameters above Is a part of common parameters. For details, refer to the documentation. Here, mydb is the name of the database you want to log on.
In development and practical applications, users should not only use root users to connect to the database. Although it is convenient to use root users for testing, it will bring significant security risks to the system, it is not conducive to the improvement of management technology. We grant the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be granted the permission to delete data. MySql User management is implemented through the User table. There are two common methods to add new users. One is to insert the corresponding data rows in the User table and set the corresponding permissions; the second is to use the GRANT command to create a user with certain permissions. The common usage of GRANT is as follows: grant all on mydb. * to NewUserName @ HostName identified by "password"; grant usage on *. * to NewUserName @ HostName identified by "password"; grant select, insert, update on mydb. * to NewUserName @ HostName identified by "password"; grant update, delete on mydb. testTable to NewUserName @ HostName identified by "password"; to GRANT this user the ability to manage permissions on the corresponding object, you can add the with grant option after GRANT. For users inserted into the User table, use the Password function to update and encrypt the PASSWORD field to prevent unauthorized users from stealing the Password. Users who do not need permissions should be cleared, and those who pass the permissions should be revoked in a timely manner. To REVOKE permissions, you can update the corresponding fields in the User table or use the REVOKE operation. Global Management permission: FILE: read and write files on the MySQL server. PROCESS: displays or kills service threads of other users. RELOAD: RELOAD Access Control tables and refresh logs. SHUTDOWN: Shut down the MySQL service. Database/data table/data column permissions: Alter: Modify existing data tables (such as adding/deleting columns) and indexes. Create: Create a new database or data table. Delete: Delete table records. Drop: delete a data table or database. INDEX: Create or delete an INDEX. Insert: Add Table records. Select: displays/searches for table records. Update: Modify existing records in the table. Special permissions: ALL: allow to do anything (same as root ). USAGE: Only logon is allowed. Other operations are not allowed. MYSQL Common commands: 1. MYSQL connection format: mysql-h host address-u username-p User Password 1. Example 1: MYSQL connected to the local machine opens the DOS window first, enter the mysqlbin directory, type the mysql-uroot-p command, and press enter to prompt you to enter the password. If you have just installed MYSQL, the Super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql> 2. Example 2: connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the username is root and the password is abcd123. Run the following command: mysql-h110.110.110.110-uroot-pabcd123 (Note: you do not need to add spaces to the u and root nodes.) 3. exit MYSQL: exit (Press ENTER) 2. modify the format of the password www.2cto.com: mysqladmin-u username-p old password new password 1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command mysqladmin-uroot-password ab12. Note: because the root has no password at the beginning, the old-p password can be omitted. 2. Example 2: Change the root password to djg345 mysqladmin-uroot-pab12 password djg345. 3. Transfer the text data to the database. 1. The text data should conform to the following format: field data is separated by the tab key, and the null value is replaced by \ n. example: 3 rose Shenzhen No. 2 Middle School 1976-10-10 4 mike Shenzhen No. 1 1975-12-23 2. data Import command load data local infile "file name" into table Name; note: you 'd better copy the file to the \ mysql \ bin directory and use the use command to create the database where the table is located. 4. Back up the database: (execute the command in the \ mysql \ bin directory of DOS) mysqldump -- opt school> school. bbb Note: Back up the database school to school. bbb file, school. bbb is a text file with any file name. Open it and you will find a new one. 1. ALL, DISTINCT, DISTINCTROW, and TOP predicates (1) ALL return ALL records meeting the SQL statement conditions. If this predicate is not specified, the default value is ALL. For example, select all FirstName, LastName FROM Employees (2) DISTINCT. If the selected fields of multiple records have the same data, only one is returned. (3) DISTINCTROW if there are duplicate records, only one (4) TOP is returned to display several records at the beginning and end of the query. You can also return the percentage of records. This is an example of using the top n percent clause (where N represents the percentage: returns the select top 5 PERCENT * FROM [Order Details] order by UnitPrice * Quantity * (1-Discount) DESC2. Use the AS clause to get the alias of a field. If you want to get a new title for the returned column, or after calculation or summarization of the field, a new value is generated, if you want to display it in a new column, use AS to keep it. Example: return the FirstName field with the alias NickName SELECT FirstName AS NickName, LastName, City FROM Employees. For example, return a new column showing the inventory value: SELECT ProductName, UnitPrice, UnitsInStock, unitPrice * UnitsInStock AS valueInStock FROM Products II. the WHERE clause specifies the query condition 1. comparison operator meaning = equal to> greater than <less than> = greater than or equal to <= less than or equal to <> not equal to!> Not greater! <Not less than example: return the SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate> #1/1/96 # AND OrderDate Orders FROM January. <#1/30/96 # Note: in Mcirosoft jet SQL, dates are bounded. The date can also be replaced by the Datevalue () function. When comparing character data, you must add single quotation marks ''. Trailing spaces are ignored during comparison. For example, WHERE OrderDate> #96-1-1 # can also be expressed as: WHERE OrderDate> Datevalue ('192/96 ') www.2cto.com 2, range (BETWEEN and not between)... AND... The operator specifies a closed interval to be searched. For example, an order from January 1, January to January 31, February is returned. WHERE OrderDate Between #1/1/96 # And #2/1/96 #3. The list (IN, not in) IN operator is used to match any value IN the list. An IN clause can replace a series of conditions connected by an OR clause. 4. The LIKE operator checks whether a field value containing string data matches a specified pattern. Wildcard characters :? Any single character * character of any length #0 ~ A single number between 9 [Character List] any value in the character list [! Character List] any value not in the character list-specifies the character range. The values on both sides are the upper and lower limits. sort results by order by clause the order clause sorts the query results BY one or more (up to 16) fields, either ascending or descending ), the default value is ascending. The ORDER clause is usually placed at the end of an SQL statement. Multiple fields are defined in the ORDER clause. For example, SELECT ProductName, UnitPrice, UnitInStock FROM Products order by UnitInStock DESC, UnitPrice DESC, and ProductName order by clauses can replace the field name with the position number in the selection list, you can mix the field names and positions. For example, the following statement produces the same effect as the preceding one. SELECT ProductName, UnitPrice, UnitInStock FROM Products order by 1 DESC, 2 DESC, 3 4. multi-Table query example using the connection relationship: find the name of the supplier and customer in the same city SELECT MERs. companyName, Suppliers. comPany. name FROM MERs, Suppliers WHERE Customers. city = Suppliers. city: SELECT ProductName, OrderID, UnitInStock, Quantity FROM Products, [Order Deails] WHERE Product. productID = [Order Details]. productID AND Uni TsInStock> Quantity another method is to use the jnner join syntax exclusive to Microsof jet SQL: FROM table1 INNER JOIN table2 ON table1.field1 comparision table2.field2 WHERE comparision is the comparison operator used in the preceding WHERE clause. SELECT FirstName, lastName, OrderID, CustomerID, OrderDate FROM Employees inner join Orders ON Employees. EmployeeID = Orders. EmployeeID Note: inner join cannot be connected to Memo OLE Object Single Double data type fields. JOIN multiple ON Clause syntax in a JOIN statement: SELECT fields FROM table1 inner join table2 ON table1.field1 compopr table2.field1 and on table1.field2 compopr table2.field2 or on outer compopr tables can also explain SELECT fields FROM table1 inner join (table2 inner join [(] table3 [INNER JOER] [(] tablex [inner join] ON table1.field1 compopr table2.field1 ON table1.field2 compopr table2.field2 ON table1.f Ield3 compopr table2.field3 external connection returns more records, keep the records that do not match in the results, no matter there are records that do not meet the conditions, all records on the other side are returned. FROM table [LEFT | RIGHT] JOIN table2 ON table1.field1comparision table. field2 uses a left join to establish an external join. All data examples are displayed in the table on the left of the expression: SELECT ProductName, orderID FROM Products left join Orders ON Products. prductsID = Orders. the difference between the right join and the left join of ProductID is that it returns all records from the left table regardless of the matching records in the left table. For example, if you want to know the customer information and calculate the customer distribution in each region, you can use a right connection to return the customer information even if there are no customers in a region. NULL values do not match each other. You can use an external connection to test whether the fields in a joined table have null values. SELECT * FROM talbe1 left join table2 ON table1.a = table2.c 1 use the Iif function in the JOIN query to display null Iif expressions with 0 values: Iif (IsNull (Amount, 0, Amout) Example: whether the order is greater than or less than ￥50, a flag is returned. Iif ([Amount]> 50 ,? Big order ?,? Small order ?) Www.2cto.com v. Grouping and summarizing query results in SQL syntax, the GROUP BY and HAVING clauses are used to summarize data. The group by clause specifies which fields are grouped. After grouping records, the HAVING clause is used to filter these records. SELECT fidldlist FROM table WHERE criteria [group by groupfieldlist [HAVING groupcriteria] Note: Microsoft Jet Database cannot GROUP remarks or OLE object fields. The Null Value in the group by field is used for grouping, but cannot be ignored. No Null value is calculated in any SQL aggregate function. The group by clause can contain up to ten fields, and the sorting priority is arranged from left to right. For example, After grouping by title in the employee table of the 'wa 'region, find all titles with the same title with more than 1 employee. SELECT Title, Count (Title) as Total FROM Employees WHERE Region = 'wa 'group BY Title HAVING Count (Title)> 1 accumulation function in jet SQL aggregate function meaning SUM () sum AVG () Average Value COUNT () the number of records in the expression COUNT (*) calculate the number of records MAX maximum www.2cto.com min var variance STDEV standard error FIRST value LAST value 6. use Parameters Declaration to create a parameter to query the syntax declared by Parameters: PARAMETERS name datatype [, name datatype [,…] Here, name is the identifier of the parameter. You can use the identifier to reference the parameter. datatype indicates the Data Type of the parameter. you must place the PARAMETERS declaration before any other statements. example: PARAMETERS [Low price] Currency, [Beginning date] datatime SELECT OrderID, OrderAmount FROM Orders WHERE OrderAMount> [low price] AND OrderDate> = [Beginning date] VII. function query is actually an operation query that can perform quick and efficient operations on the database. it selects matching data for the purpose of selecting a query and then processes the data in batches. function query includes update query, delete query, add query, and generate Table query. 1. UPDATE query the UPDATE clause can change the data in one or more tables at the same time. it can also change the values of multiple fields at the same time. update query syntax: UPD New WHERE criterion for ATE table name SET: the order volume of UK customers increases by 5%, the Freight volume increases by 3% update oeders set OrderAmount = OrderAmount * 1.1 Freight = Freight * 1.03 WHERE ShipCountry = 'uk '2. Deleting the query DELETE clause allows users to DELETE a large number of obsolete or redundant data. note: The query object is deleted as a whole record. syntax of the DELETE clause: www.2cto.com DELETE [Table name. *] FROM source table WHERE criterion example: to DELETE all Orders deleted 94 years ago * FROM Orders WHERE OrderData <#94-1-1 #3. The INSERT clause of the append query can append one or more records to one or more the end of the table. the INTO clause specifies the table valueS keyword that accepts the new record to specify the data value contained in the new record. INS Syntax of the ERT clause: insetr into target table or query (Field 1, Field 2 ,...) ValueS (value 1, value 2 ,...) Example: Add a customer insert into Employees (FirstName, LastName, title) valueS ('Harry ', 'Washington', 'trainee ') 4. Generate a Table query to copy all records meeting the conditions to a new table at a time. A backup or copy of a record is usually created or serves as the basis for a report. the select into clause is used to create the generated Table query syntax: SELECT Field 1, Field 2 ,... INTO new table [IN external database] FROM source database WHERE criterion example: Create an archive backup for the Order SELECT * INTO OrdersArchive FROM Orders 8. the UNION operation of the joint query can merge the results of multiple queries into a single result set for display. general syntax for UNION operations: [Table] query 1 UNION [ALL] query 2 UNION... Example: return the names of all Suppliers and Customers in Brazil and the cities SELECT CompanyName, City FROM Suppliers WHERE Country = 'Brazil 'union select CompanyName, City FROM Customers WHERE Country = 'Brazil'. Note: by default, the UNION clause does not return repeated records. if you want to display ALL records, you can add the ALL option UNION operation to query fields with the same number. however, the field data types do not have to be the same. you can use the group by clause or HAVING clause to GROUP each query parameter. to display the returned data in the specified order, you can use the oreer by clause at the end of the last query. 9. cross-query: calculates the sum, average, Count, or other sum calculation methods of data. The data is grouped by two types of information: one is displayed on the left of the table, the other is displayed on the top of the table. micro Soft Jet SQL uses the TRANSFROM statement to create a cross tabulation query syntax: TRANSFORM aggfunction www.2cto.com SELECT statement GROUP BY clause using struct tfield [IN (value1 [, value2 [,…])] Aggfounction refers to the SQL accumulation function. The SELECT statement selects the field as the title. GROUP BY GROUP Description: The field or expression used BY the Pivotfield to create the column title in the query result set, use an optional IN clause to limit its values. value indicates the fixed value of the created column title. for example, the number of Orders received by each employee in each quarter of 1996 is displayed: TRANSFORM Count (OrderID) SELECT FirstName & ''& LastName AS FullName FROM Employees inner join Orders ON Employees. employeeID = Orders. employeeID WHERE DatePart ("yyyy", OrderDate) = '000000' group by FirstName & ''& LastName order by FirstName &'' & LastName POVOT DatePart ("q", OrderDate) & 'quarter 10. A subquery can be considered as a set query. A subquery is a SELECT statement. 1. syntax for comparing the expression value with the single value returned by the subquery: expression comparision [ANY | ALL | SOME] (subquery) Description: ANY and SOME predicates are synonyms, used with comparison operators (=, <,>, <>, <=,> =. returns a Boolean value of True or False. ANY means that the expression is compared with a series of values returned by the subquery one by one. If a comparison produces a True result, ANY tests return a True value (both the result of the WHERE clause ), the current record corresponding to this expression will be included in the results of the primary query. the ALL test requires that the expression and a series of values returned by the subquery produce the True result before returning the True value. example: the unit price returned by the primary query is higher than the unit price of ANY product whose Discount is greater than or equal to 25%. SELECT * FROM Products WHERE UnitPrice> ANY (SELECT UnitPrice FROM [Order Details] WHERE Discount> 0.25) 2. Check whether the expression value matches a Value Syntax of a group of values returned by the subquery: www.2cto.com [NOT] IN (subquery). For example, products whose inventory value is greater than or equal to 1000 are returned. SELECT ProductName FROM Products WHERE ProductID IN (SELECT PrdoctID FROM [Order DEtails] WHERE UnitPrice * Quantity> = 1000) 3. Check whether the subquery returns any record Syntax: [NOT] EXISTS (subquery) Example: Use EXISTS to retrieve UK customers' SELECT ComPanyName, ContactName FROM Orders where exists (SELECT * FROM MERs WHERE Country = 'uk' AND MERs. customerID = Orders. merid) by hanzhou4519