Mysql programming skills

Mysql programming skills programming and database management (Mechanical Industry Press)
Start: net start mysql
Stop: net stop mysql
Change password: $ MySQL_HOME> bin> mysqladmin-u root-p password new_password (set the new password to new_password, press enter and enter the old password)
Yes, but the error mysql> set password for root = password ("why810707 ");
ERROR 1133 (42000): Can't find any matching row in the user table
Create database mydata;
Delete database: drop database mydata;
Display Database: show databases;
Open a database: use mydata;
Display all tables in a database: show tables;
Create table data (id int (6), name varchar (20 ));
Insert an insert into table_name values (,'',,,);
Import data from text files mysqlimport-u root-p mydata data.txt
Delete A data table: drop table data;
Query a data table: select * from table_name;
Export data: mysqladmin creat target_db_name
Mysql target_db_name <backup-file. SQL
Extended-status: displays the name and value of the server status variable.
Flush-hosts refresh host Cache
Flush-logs refresh the log file
Flush-privileges: Reload the authorization table
Flush-status clear status variable
Flush-tables
Version: Search and Display Server Information
Status: displays brief server status information.
Shutdown: indicates that the server is shut down.
Reload: reload the authorization table
Refresh: refresh the table notification cache and close and re-open the log file.
Kill id ......
Password new_password set new password
Ping to check whether the mysql server is running
\ C cancel current query

-------------------------------------------------------------------------------

(1) Data Record Filtering:
SQL = "select * from data table where field name = field value orderby field name [desc]"
SQL = "select * from data table where field name like '% Field Value %' orderby field name [desc]"
SQL = "select top10 * from data table where field name orderby field name [desc]"
SQL = "select * from data table where field name in ('value 1', 'value 2', 'value 3 ')"
SQL = "select * from data table where field name between value 1 and value 2"
SQL = "Select Distinct field name From data table"
The Distinct function is used to query records that are not repeated in the data inventory table.
SQL = "Select Count (*) From data table where field name 1 >#0:0 # and field name 1 <#19:00 #"
The count function is used to query the number of records in a database table. Field name 1 indicates the same field.
SQL = "select * from data table where field name = Field Value order by field name [desc]"

SQL = "select * from data table where field name like '% Field Value %' order by field name [desc]"
Fuzzy query %: represents any length of characters _: represents a character

SQL = "select top 10 * from data table where field name order by field name [desc]"
Search for the first 10 records in the database

SQL = "select top n * form data table order by newid ()"
How to randomly retrieve several records from a database
Top n, n is the number of records to be retrieved

SQL = "select * from data table where field name in ('value 1', 'value 2', 'value 3 ')"
(2) update data records:
SQL = "update data table set field name = field value where condition expression"
SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"
SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n"
If no conditions exist, update the specified field value in the entire data table.

(3) Delete data records:
SQL = "delete from data table where condition expression"
SQL = "delete from data table"
(Delete all records in the data table)
(4) add data records:
SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...) "
SQL = "insert into data table valuess (value 1, value 2, value 3 ...) "
If the field name is not specified, it indicates that the field in the data table is added in sequence.

SQL = "insert into target data table select * from source data table" (add records of source data table to target data table)
(5) statistical functions of data records:
AVG (field name) returns the average value of a table column
COUNT (* | field name) statistics on the number of data rows or the number of data rows with values in a column
MAX (field name) obtains the maximum value of a table column.
MIN (field name) obtains the minimum value of a table column.
SUM (field name) adds the values in the data column
The method for referencing the above functions:
SQL = "selectsum (field name) as Alias from data table where condition expression"
Setrs = conn. excute (SQL)
Use rs ("alias") to obtain the calculation value. Use the same method for other functions.
(5) Create and delete data tables:
CREATETABLE data table name (Field 1 type 1 (length), Field 2 type 2 (length )......)
Example: CREATETABLEtab01 (namevarchar (50), datetimedefaultnow ())
DROPTABLE data table name (permanently delete a data table)
4. Method of record set object:
Rs. movenext moves the record pointer down a row from the current position
Rs. moveprevious transfers the record pointer from the current position to a row up
Rs. movefirst move the record pointer to the first row of the data table
Rs. movelast moves the record pointer to the last row of the data table
Rs. absoluteposition = N move the record pointer to the nth row of the data table
Rs. absolutepage = N move the record pointer to the first row of page N
Rs. pagesize = N set N records per page
Rs. pagecount the total number of pages returned Based on pagesize settings
Rs. recordcount total number of returned records
Rs. bof indicates whether the record pointer exceeds the first end of the data table. true indicates yes, and false indicates no.
Rs. eof indicates whether the returned record pointer exceeds the end Of the data table. true indicates yes, and false indicates no.
Rs. delete deletes the current record, but the record pointer does not move down
Rs. addnew add record to end of data table
Rs. update data table records

----------------------------------------------------------------------

Connecting to mysql in jsp
(1) load the driver
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
Class. forName ("jdbc. DriverXYZ ");
(2) establish a connection
String Url = "jdbc: odbc: Fred ";
Connection con = DriverManager. getConnection (Url, "myLogin", "myPassword ");
(3) Execute SQL statements
ExecuteQuery () is used to execute a single result set statement.
ExecuteUpdate () is used to execute non-select statements such as insert, update, and delete.