MySQL basic knowledge-database creation, viewing and other common operations

Command Creation MySQL Database :

start the MySQL database and connect to the database first:

mysql-uroot-p123456 ( syntax:mysql-u logon name - p password )

To create a table:

Create Database Spring_data; ( syntax:create database name )

Use the database you created:

Use Spring_data;

To see if a table exists:

Show tables;

# view columns in a table

SHOW COLUMNS from Auth_User;

To create a table:

Create Table Student (    intnotnull  auto_increment,    varchar(  notnull,    intis notnull,     primarykey(ID));

To view the table structure:

DESCRIBE Auth_User;

Syntax: The describe table name is A quick way to show columns from a table name.

Second, modify the MySQL database password

Method One:

using phpmyadmin, modify the User Table of the Mysql library directly . or use Navicat for Mysql to directly modify the connection properties.

method Two: Use mysqladmin

#cmd, run DOS,cd to the bin folder of MySQL, Then do the following:

D:\mysql\bin>mysqladmin-u root-p Password Newpwd

#Enter Password: ( Enter the original password here )

#newPwd means a new password.
then open MySQL and enter the new password directly

format:mysqladmin-u username - p old password password new password.

accessing the database, using the use statement

Create a database

mysql> CREATE database name ;

mysql> use library name ;

mysql> CREATE Table name ( field name VARCHAR (), field name CHAR (1));

To delete a database:

mysql> DROP database name ;

To delete a data table:

mysql> DROP table name;

Empty the records in the table:

Mysql> DELETE from table name ;

Create a table ( complex form ) :

# Create a customer table:

Create TableCustomers (IDint  not NULLauto_increment, nameChar( -) not NULL, AddressChar( -)NULL, CityChar( -)NULL, Ageint  not NULL, LoveChar( -) not NULL default 'No habbit',    Primary Key(ID)) engine=InnoDB;

　　SELECT last_insert_id (); This function can get the last auto_increment value returned .

#　　 defaults :default ' No habbit ',

　　# Engine Type , many for engine = InnoDB, if the engine= statement is omitted, the default engine (MyISAM) is used

To change the table structure:

# Add a column: syntax: ALTER TABLE tablename add COLUMMNAME type [null];

ALTER TABLE pet add des char (+) null;

# Delete: syntax: ALTER TABLE tablename drop column colummname;

ALTER TABLE pet drop column des;

To rename a table:

Syntax: Rename table tablename1 to tablename2;

Rename table pet to animals;

Add ID Field

You can do the following:

# add ID field, including primary key

ALTER TABLE pet Add ID int not NULL primary key auto_increment first;

To set the index:

to set a foreign key, in the reference table (referencing table, i.e. Pc tables ) and referenced tables (referencedtable, i.e. parts tables ) , the corresponding two fields must all be set to an index (Index) .

the Parts Table:

ALTER TABLE Parts ADD INDEX Idx_model (model);

To add an index to the Parts table, the index is built on the model field, and the call is named Idx_model.

MyBatis SQL statement symbol is incompatible with greater than sign less than sign (XML escape character)

when we need to process SQL statements in XML format, we often use symbols such as < ,<=,>,>=, etc. However, it is easy to cause errors in the XML format, which causes the background to convert the XML string to an XML document times error, which causes the program to be wrong. Such problems often need us to deal with in MyBatis or in a custom XML- processing SQL program. In fact, it is very simple, we just need to replace the following to avoid the above error:

Original symbol	<	<=	>	>=	&	‘	"
Replace symbol	<	<=	>	>=	&	‘	"

MySQL basic knowledge-database creation, viewing and other common operations