MySQL database simple to use

　　

A Getting Started statement:

1. Connect to the server:

Command: CD installation directory \ Bin

Mysql-uroot-p

Then enter the password

(specifically: mysql-u username-p password)

Remote connection to MySQL database: Mysql-ip address or URL-u user name-p password;

Quit MySQL database: quit;

Change Password: The first time with MySQL if not set the password, you can write: Mysqladmin-u user name password new password;

Add new user (before MySQL is available): Grant Select,insert,delete,update [All privileges permissions] on database. * To User name @ login host identified by "password";

Example: Grant Select,insert,delete,update on * * to [e-mail protected]% identified by "ABC";

(Add a user test1 password to ABC, can be logged on any host, and all databases have the right to add and revise the check)

Grant Select,insert,delete,update on mydb.* to [e-mail protected] identified by "ABC";

(Add a user test2 password to ABC, and let him log in only on the local machine.) MyDB database can be added to the search operation, if you do not want to test2 have a password, you can tap the command again, and set the password as by "";)

MySQL an official Visual Database manipulation tool: MySQL Administrator tools;

Modify the password of the existing user can be executed again to create a new user's instructions, the password can be replaced.

2. Database:

Select Library: Use library name;

List database lists: show databases;

Creating library: Create database name [charset character set (gbk,utf8,gb2312 ...)];

Delete Library: drop database name; (MySQL cannot change the database name)

3. Data sheet:

View a list of tables under the library: show tables;

Change table name: Rename table Oldname to newname;

Build tables: Create TABLE table name (ID int, name varchar,.....) engine MyISAM charset character set;

Delete tables: drop table name;

Clear table data: TRUNCATE TABLE name;

Insert data: Inser into table name (List of column names) values

(value 1, value 2, value 3,。。。。 ) ,

(value 1, value 2, value 3,。。。。 ) ,

(value 1, value 2, value 3,。。。。 ) ;

< insert multiple data, separated by commas ",", "Separating >

View GBK characters: set names GBK;

Jump out of execution: \c;

Delete a field: ALTER Table ' drop ' field ';

Insert a field: ALTER TABLE ' Add ' new Field 1 ', ' new Field 2 ' ... [After ' Existing Fields '] (after the addition of after, the newly added field is inserted after the field.) If you do not add after, the new field is added to the end by default. )

Display database table structure: describe table_name;

Load data into a data table in text mode: Load data local infile "D:\mysql.sql" into table table_name;

In the. text file: The field data is separated by the TAB key, and the null value is replaced by [Null=file://\\n]\\n[/url].

Import. sql file command: use database_name; SOURCE D:\mysql.sql;

JDBC Connection MySQL Database

Driver:com.mysql.jdbc.Driver

Url:jdbc:mysql://localhost:3306/dbname

Conn=drivermanager.getconnection (URL, "User name", "password");

Common DML statements in MySQL

--Add a String column for table Tbl_user emailalter table tbl_user Add email varchar (50);--delete table tbl_user email column ALTER TABLE tbl_user drop email ;--Renaming the Age column of table Tbl_user to User_age, change is the key in MySQL ALTER TABLE tbl_user changes age user_age int;--changing tables Tbl_user user_ Age definition, data type tinyint, length 1, non-null ALTER TABLE Tbl_user change user_age user_age tinyint (1) Not null;--rename table Tbl_user to User_ Tblalter table Tbl_user Rename user_tbl;

Inserting data samples

CREATE TABLE tbl_user (    ID INT (one) UNSIGNED NOT NULL auto_increment,/* Self-growing column *    /name VARCHAR (not null DEFAULT ') ,    password varchar (+) not NULL default ' ',    email VARCHAR (+) Default ',    PRIMARY KEY (ID))  engine= INNODB DEFAULT charset=utf8;create table tbl_address (    ID int (one) unsigned not NULL auto_increment, city    varchar ( Default NULL,    country varchar (default) NULL,    user_id int (one) unsigned not NULL,    primary key (ID)) Engi NE = InnoDB  default charset = Utf8;insert into Tbl_user (id,name,password,email)/    * Here is a one-time insertion of two data, separated by commas */ VALUES (1, ' xiaoming ', ' 123456 ', ' [email protected] '), (2, ' Xiaozhang ', ' 123456 ', ' [email protected] '); INSERT INTO Tbl_ Address (city,country,user_id)/    * Here only one data */values (' Beijin ', ' China ', 1) is inserted at a time, insert into tbl_address (city, country,user_id) VALUES (' Tianjin ', ' China ', 2);

MySQL database simple to use