Introduction to Mysql Basics _mysql

First, start and exit
1, into the MySQL:
Start the MySQL Command line Client (MySQL's DOS interface) and enter the installation password directly. The prompt at this point is:mysql>
or open the terminal and enter the SQL statement:
Mysql–uroot–p123

2, exit MySQL:
Quit or exit

Second, the library operation
1. Create a database
Command: CREATE DATABASE < database name >
For example: Create a database named XHKDB
mysql> CREATE DATABASE xhkdb;

2, display all the database
Command: Show databases (note: Last has an S)
mysql> show databases;

3, delete the database
Command: Drop Database < database name >
For example, to delete a database named Xhkdb
mysql> drop Database xhkdb;

4, connect the database
Command: Use < database name >
For example: If the XHKDB database exists, try to access it:
mysql> use XHKDB;
ScreenTip: Database changed

5, the currently selected (connected) database
Mysql> Select Database ();

6, the current database contains table information:
Mysql> Show tables; (Note: Last has an S)

7, create the user and give access to the right:
Grant all privileges on dbname.* to username@localhost identified by ' pwd123′;
For localhost Domain users username manage all rights to the dbname database, the password is pwd123.

Third, table operation, the operation should be connected to a database
1, build the table
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 (NOT NULL),
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

2. Get the table structure
command: desc table name, or Show columns from table name
mysql> desc MyClass;
Mysql> show columns from MyClass;

3, delete the table
command:drop table < table name >
For example, to delete a table named MyClass
mysql> drop table MyClass;

4. Inserting data
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 that say: Tom with a number 1 is 96.45, and the number 2 for Joan is 82.99, and the number 3 for Wang is 96.5.
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

5, the data in the query table
1), Query all lines
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 lines of data
For example: View the first 2 rows of data in table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;

6, delete the data in the table
command:delete from table name where expression
For example: Delete a record in table MyClass that is numbered 1
Mysql> Delete from MyClass where id=1;

7, modify the data in the table:
Update table name Set field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;

8. Add fields to the table:
command:ALTER TABLE name add field type other;
For example, a field passtest is added to the table MyClass, the type is int (4), and the default value is 0
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '

9, change the table name:
command:rename table original table name to new name;
For example: In the table MyClass the name is changed to Youclass
Mysql> Rename table MyClass to Youclass;
Update Field Contents
Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');
Add 4 spaces before the article
Update article Set Content=concat (", content);

Iv. Introduction to field types
1. int[(M)] Type: normal size integer type
2. double[(m,d)] [Zerofill] Type: normal size (double precision) floating-point number type
3. Date Date Type: The scope of support is from 1000-01-01 to 9999-12-31. MySQL displays date values in YYYY-MM-DD format, but allows you to assign values to date columns using strings or numbers
4. CHAR (M) Type: fixed-length string type, when stored, always fill the right to the specified length with a space
5. BLOB text type with a maximum length of 65535 (2^16-1) characters.
6. VARCHAR Type: variable-length string type

V. Database backup
1. Export the entire database
Mysqldump-u user name-p--default-character-set=latin1 Database name > exported file name (database default encoding is latin1)
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql

3. Export a database structure
Mysqldump-u wcnc-p-d–add-drop-table SMGP_APPS_WCNC >d:wcnc_db.sql
-D No data –add-drop-table add a drop table before each CREATE statement

4. Import Database
Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
Mysql>source D:wcnc_db.sql