1. Connection database format: mysql-h host address-u user name-P user password 1.1. Connect to MySQL on this computer. First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after you are prompted to lose the password.NoteYou can have a space before the user name, but there must be no space before the password, or let you re-enter the password. If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>
1.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-u root-p 123; (Note: You can not add a space between the root and the other) 1.3 exit MySQL command: Exit (enter)
2. Add User format: Grant select on database. * To username @ Login host identified by "password" 2.1 Add a user test1 password for ABC, so that he can log on any host and have access to query, insert, modify, delete for all databases. First connect to MySQL with the root user,
Then type the following command: Grant Select,insert,update,delete on * * to [[email protected] "%][email protected]"%[/email] "identified by" ABC ";
But the increase of the 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 into your MySQL database and your data can be
Whatever you do, see 2.2 for the solution. 2.2 Add a user test2 password to ABC, so that he can only login on localhost, and can query, insert, modify, delete the database mydb operation (localhost refers to the local host
, which is the host on which the MySQL database resides), so that the user knows Test2 's password, 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 [[E-mail Protected]][email protected][/email] identified by "ABC"; If you do not want to test2 have a password, you can call another command to erase the password. Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by ""
3. Operational database
3.1 Creating a DatabaseNote: Connect the MySQL server command before you create the database: CREATE DATABASE < database name > Example 1: Establish a mysql> named Xhkdb. xhkdb;
Example 2: Create a database and assign the user ①create database name; ②grant select,insert,update,delete,create,drop,alter on database name. * To User name @localhost Identified by ' password '; ③set PASSWORD for ' database name ' @ ' localhost ' = old_password (' password '); Execute 3 commands to complete the creation of the database.Note: The Chinese "password" and "database" are the users need to set.
3.2 Display Database command: show databases (Note: Finally there's a s) mysql> show databases3.3 Delete database command: Drop databases < database name > For example: Delete the database named Xhkdb mysql> drop Databases xhkdb;
Example 1: Delete a database that has been determined to exist mysql> drop databases drop_database; Query OK, 0 rows Affected (0.00 sec)
Example 2: Delete a database that is not deterministic mysql> drop DB drop_database; ERROR 1008 (HY000): Can ' t drop database ' drop_database '; Database doesn ' t exist//error occurred, cannot delete ' drop_database ' databases, the database does not exist. Mysql> drop database if exists drop_database; Query OK, 0 rows affected, 1 Warning (0.00 sec)//generates a warning stating that this database does not exist mysql> create database drop_database; Query OK, 1 row affected (0.00 sec) mysql> drop database if exists drop_database;//if exists determine if the databases exist, do not exist, and do not produce errors query OK , 0 rows Affected (0.00 sec)
3.4 Connection Database command: Use < database name > For example: If the XHKDB database exists, try to access it:mysql> use XHKDB; ScreenTip: Database changeduse statement can advertise MySQL Db_ The name database is used as the default (current) database for subsequent statements. The database remains the default database until the end of the segment, or until the
Cloth a different Use statement:mysql> use db1;mysql> SELECT COUNT (*) from mytable; # selects from db1.mytablemysql> use db2;mysql> SELECT COUNT (*) from mytable; # selects from db2.mytable
Using the USE statement to mark a specific current database does not prevent you from accessing tables in other databases. The following example accesses the author table from the DB1 database and accesses it from the DB2 database
Edit Table:mysql> use db1;mysql> SELECT author_name,editor_name from Author,db2.editor WHERE author.editor_ id = db2.editor.editor_id; to exit the database or connect to another database direct user ' other database name '.
3.5 The current Selection database command:mysql> select databases (); The select command in MySQL is similar to print or write in other programming languages, and you can use it to display the results of a string, a number, a mathematical expression, and so on. How do I use the special features of the Select command in MySQL?
(1). Shows the version of MySQL mysql> select version (); +-----------------------+ | Version () | +-----------------------+ | 6.0.4-alpha-community | +-----------------------+ 1 row in Set (0.02 sec)
(2). Show Current Time mysql> select Now (); +---------------------+ | Now () | +---------------------+ | 2009-09-15 22:35:32 | +---------------------+ 1 row in Set (0.04 sec)
(3). Display Month Day Select DayOfMonth (current_date); +--------------------------+ | DayOfMonth (current_date) | +--------------------------+ | 15 | +--------------------------+ 1 row in Set (0.01 sec) SELECT MONTH (current_date); +---------------------+ | MONTH (current_date) | +---------------------+ | 9 | +---------------------+ 1 row in Set (0.00 sec) SELECT Year (current_date); +--------------------+ | Year (current_date) | +--------------------+ | 2009 | +--------------------+ 1 row in Set (0.00 sec)
(4). Display string mysql> Select "Welecome to my blog!"; +----------------------+ | Welecome to my blog! | +----------------------+ | Welecome to my blog! | +----------------------+ 1 row in Set (0.00 sec)
(5). When calculator with select ((4 * 4)/10) + 25; +----------------------+ | ((4 * 4)/10) + 25 | +----------------------+ | 26.60 | +----------------------+ 1 row in Set (0.00 sec)
(6). Threaded string Select CONCAT (F_name, "", L_name) as name from Employee_data where title = ' Marketing Executive '; +---------------+ | Name | +---------------+ | Monica Sehgal | | Hal Simlai | | Joseph Irvine | +---------------+ 3 rows in Set (0.00 sec)Note:The concat () function is used here to string strings together. In addition, we used the previously learned as to give the result column ' CONCAT (f_name, "", L_name) ' a pseudonym
4 Operations on a Table 4.1 create TABLE command: "Create tables < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]); For example, create a table named MyClass mysql> CREATE TABLE MyClass (> ID int (4) NOT null primary key AUTO_INCR ement,> name char () not null,> sex int (4) is not null default ' 0 ',> degree double (16,2));
4.2 Get Table Structure command: DESC table name, or Show columns from table name mysql> desc myclass;mysql> show columns from MyClass; When using the MySQL database desc table name, we See key that column, there may be 4 kinds of values, namely ', ' PRI ', ' UNI ', ' MUL '. (1). If key is empty, the column value can be duplicated, indicating that the column has no index, or a non-leading column of a non-unique composite index, (2). If key is a PRI, the column is part of the primary key, and (3). If key is uni, the column is the first column of a unique value index ( (null), and (4). If key is Mul, then the value of the column can be repeated, which is a leading column of a non-unique index (the first column) or a component of a unique index but can contain null values. If the definition of a column to meet the above 4 cases, such as a column is both a PRI and uni, then the "DESC table name", the display of the key value according to the priority to display
Pri->uni->mul. So at this point, the PRI is displayed. A unique index column can be displayed as a PRI, and the column cannot contain null values, and the table does not have a primary key. A unique index column can be displayed as Mul if multiple columns constitute a unique composite index, because although the multiple-column combination of indexes is unique, such as Id+name is unique, there is no single
A single column can still have duplicate values, as long as the id+name is unique.
4.3 Delete table command: Drop table < table name > For example: Delete table named MyClass mysql> drop table MyClass;
DROP table is used to cancel one or more tables. You must have drop permissions for each table. All table data and table definitions are canceled, so use this statement with caution! Note: For a partitioned table, the DROP table permanently cancels the tables definition, cancels the partitions, and cancels all the data stored in those partitions. DROP table will also be canceled and canceled
The table has an associated partition definition (. par) file. For tables that do not exist, use the if exists to prevent errors from occurring. When using the if exists, a note is generated for each table that does not exist. Restrict and cascade can make partitioning easier. Currently, restrict and cascade do not work.
4.4 Inserting data into a table command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)] For example: Insert two records into table MyClass, two records indicate: 1 is named Tom's score is 96.45, 2 is named Joan, the score is 82.99, the number is 3 Name is
Wang's score was 96.5. mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);Note: INSERT into only one record can be inserted into the table at a time.
4.5 query Table (1), Query All Rows command: Select < Field 1, field 2,...> from < table name > where < expression > For example: View all data in table MyClass mysql> SELECT * from MyClass; (2), query the first few rows of data for example: View the first 2 rows of data in table MyClass mysql> select * FROM MyClass ORDER by ID limit 0,2;select general mates where to use to query more refined more complex data.
4.6 Delete Table command: Delete from table name where expression For example: Delete the record in table MyClass 1 mysql> Delete from MyClass where id=1;
4.7 Modify data syntax in table: Update table name SET field = new value,... where Condition mysql> update MyClass set name= ' Mary ' where id=1;
Example 1: Single table MySQL UPDATE statement: Update [low_priority] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition] [ORDER by ...]
[LIMIT Row_count] Example 2: Multiple table UPDATE statement: Update [low_priority] [IGNORE] table_references SET col_name1=expr1 [, col_name2= Expr2 ...] [WHERE Where_definition] The update syntax can update the columns in the original table row with the new values. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If an ORDER BY clause is specified, the row is updated in the order specified. The limit clause is used to limit the number of rows that can be updated, given a limit value.
4.8 Add table field command: ALTER TABLE name add field type other; For example, add a field passtest in table MyClass, type int (4), and the default value is 0mysql> ALTER TABLE MyClass add Passtest Int (4) default ' 0 ' plus index mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]); Example: mysql> ALTER TABLE employee ADD index Emp_name (name), index of the primary keyword mysql> ALTER TABLE name add primary key (field name); Example: my Sql> ALTER TABLE employee ADD primary key (ID); add UNIQUE constraint index mysql> ALTER TABLE name add unique index name (field name); Example: Mysql> alt ER table employee Add unique emp_name2 (cardnumber); Delete an index mysql> ALTER TABLE name DROP index name; example: Mysql>alter table Employee DROP index emp_name; add field:mysql> ALTER TABLE table_name add field_name field_type; Modify the original field name and type:mysql> alter T ABLE table_name Change old_field_name new_field_name field_type; modify field type; Mysql>alter Table table_name MODIFY colum_ Name Field_type new_type Delete field: MySQL ALTER TABLE table_name drop field_name;
4.9 Modify Table name command: Rename table name to the new list; For example, change the table MyClass name to youclassmysql> rename table MyClass to Youclass; When you execute rename, you cannot have any A locked table or an active transaction. You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table. If MySQL encounters any errors in the multi-table renaming, it reverses all renamed tables, returning everything to its original state. RENAME TABLE was added to MySQL 3.23.23.
5. Backing up data
The command executes under the DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory (1). Export the entire database export file by default exists in the Mysql\bin directory mysqldump-u user name-p database name > Exported filename mysqldump-u user_name-p123456 database_name > Outfile_name.sql (2). Export a table mysqldump-u user name-P database name Table name > Exported FileName MYSQLDUMP-U user_name-p database_name table_name > Outfile_name.sql (3). Export a database structure mysqldump-u user_name-p-d–add- drop-table database_name > OUTFILE_NAME.SQL-D No data –add-drop-table add a drop table (4) before each CREATE statement. Export with language parameters mysqldump -uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_ Name.sql For example, back up the AAA library to file back_aaa: [[email protected] root]# cd/home/data/mysql[[email protected] mysql]# mysqldump-u Root-p--opt AAA > BACK_AAA
6. A complete instance of database creation drop exists school; Delete Create Database school if school is present; Establish library Schooluse School; Open the Library schoolcreate table teacher//Set up tables teacher (ID int (3) auto_increment NOT null primary key, name Char (TEN) NOT NULL, Address varchar (+) Default "Shenzhen", year date); End of Build table
The Insert field below inserts into teacher values (' ' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 '); insert into teacher values ("', '" Jack ', ' Shenzhen One in ', ' 1975-12-23 ');
Note: In the Build 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; and cannot be empty: not null; and let him become the main field primary key. (2), set name to a character field of length 10 (3), a character field with address set to length 50, and a default value of Shenzhen.
It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. (1), you can write the above command as is written in a text file, assuming that school.sql, and then copied to c:\\, and in the DOS state into the directory [url=file://\\mysql\\bin]\
\mysql\\bin[/url], 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, you will be prompted. (The above command has been debugged, you can use it only if you remove//comment). (2), or enter the command line after using mysql> source C:\\school.sql; You can also import the School.sql file into the database.
MYSQL from beginner to proficient
