Shulou(Shulou.com)06/01 Report--

1 Connect Mysql

Format: mysql-h host address-u user name-p user password

1. Connect to the MYSQL on this machine.

First open the DOS window, then enter the directory mysql\ bin, type the command mysql-u root-p, and press enter to prompt you for your password. Note that there can be spaces or no spaces before the user name, but there must be no spaces before the password, otherwise you will be asked to re-enter the password.

If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by pressing enter. The prompt for MYSQL is: mysql >

2. Connect to the MYSQL on the remote host. Suppose the IP of the remote host is 192.168.1.5, the user name is root, and the password is abcd123. Type the following command:

Mysql-h292.168.1.5-u root-p 123; (note: there is no space between u and root, and so do others)

3. Exit MYSQL command: exit (enter)

2 change the password

Format: mysqladmin-u username-p old password password new password

1. Add a password ab12 to root.

First enter the directory mysql\ bin under DOS, and then type the following command

Mysqladmin-u root-password ab12

Note: since root does not have a password at the beginning, the-p old password can be omitted.

2. Change the password of root to djg345.

Mysqladmin-u root-p ab12 password djg345

3 add new users

Format: grant select on database. * to user name @ login host identified by "password"

1. Add a user's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First connect to MYSQL with the root user, and then type the following command:

Grant select,insert,update,delete on *. * to [email=test1@ "%] test1@"% [/ email] "Identified by" abc "

2, add a user's test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that even if the user knows the test2 password, he cannot access the database directly from the internet, only through the web page on the MYSQL host.

Grant select,insert,update,delete on mydb.* to [email=test2@localhost] test2@ localhost [/ email] identified by "abc"

3. If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grant select,insert,update,delete on mydb.* to [email=test2@localhost] test2@ localhost [/ email] identified by ""

4 database operations (operations entering the database)

4.1 create a database

Note: connect to the Mysql server before creating the database

Command: create database

Example 1: set up a database called xhkdb

Mysql > create database xhkdb

Example 2: create a database and assign users

① CREATE DATABASE database name

② GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON database name. * TO database name @ localhost IDENTIFIED BY 'password'

③ SET PASSWORD FOR 'database name' @ 'localhost' = PASSWORD (' password')

Execute three commands in turn to complete the database creation. Note: Chinese "password" and "database" are set by users themselves.

4.2 display database

Command: show databases (Note: there is an s at the end)

Mysql > show databases

Note: in order to no longer display the garbled code, change the default code of the database. Take the GBK coding page as an example to illustrate:

1. Modify the configuration file of MYSQL: modify default-character-set=gbk in my.ini

2. Modify the code at run time:

① Java Code: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk

② PHP Code: header ("Content-Type:text/html;charset=gb2312")

③ C code: int mysql_set_character_set (MYSQL * mysql, char * csname)

This function sets the default character set for the current connection. The string csname specifies a valid character set name. Connection proofreading becomes the default proofreading of the character set. This function works similar to the SET NAMES statement, but it can also set the value of mysql- > charset, thus affecting the character set set by mysql_real_escape_string ().

4.3 Delete the database

Command: drop database

For example: delete the database named xhkdb

Mysql > drop database xhkdb

Example 1: delete a database that has been determined to exist

Mysql > drop database drop_database

Query OK, 0 rows affected (0.00 sec)

Example 2: delete an uncertain database

Mysql > drop database drop_database

ERROR 1008 (HY000): Can't drop database' drop_database'; database doesn't exist

/ / an error occurred and the 'drop_database' database cannot be deleted. It does not exist.

Mysql > drop database if exists drop_database

Query OK, 0 rows affected, 1 warning (0.00 sec) / / generates a warning indicating 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 determines whether the database exists, does not exist and does not generate an error

Query OK, 0 rows affected (0.00 sec)

4.4 connect to the database

Command: use

For example, if the xhkdb database exists, try to access it:

Mysql > use xhkdb

Screen Tip: Database changed

The use statement can tell MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default database until the end of the paragraph or until a different USE statement is issued:

Mysql > USE db1

Mysql > SELECT COUNT (*) FROM mytable; # selects from db1.mytable

Mysql > USE db2

Mysql > SELECT COUNT (*) FROM mytable; # selects from db2.mytable

Using the user 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 the edit table from the db2 database:

Mysql > USE db1

Mysql > SELECT author_name,editor_name FROM author,db2.editor

-> WHERE author.editor_id = db2.editor.editor_id

USE statements are set up to be compatible with Sybase.

After the use database, you can use show databases to query all databases, and if you want to jump to other databases, you can use use other database names.

4.5 currently selected database

Command: mysql > select database ()

The SELECT command in MySQL is similar to print or write in other programming languages. You can use it to display the results of a string, number, mathematical expression, and so on. How do I use the special features of the SELECT command in MySQL?

1. Display the version of MYSQL

Mysql > select version ()

+-- +

| | version () |

+-- +

| | 6.0.4-alpha-community |

+-- +

1 row in set (0.02 sec)

two。 Show current time

Mysql > select now ()

+-+

| | now () |

+-+

| | 2009-09-15 22:35:32 |

+-+

1 row in set (0.04 sec)

3. Show year, month and 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. Used as a calculator

Select ((4 * 4) / 10) + 25

+-+

| | (4 * 4) / 10) + 25 |

+-+

| | 26.60 |

+-+

1 row in set (0.00 sec)

6. Concatenated 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 concatenate strings. In addition, we used the AS we learned before to give the result column 'CONCAT (f_name, ", l_name) a pseudonym.

5 create a datasheet

Command: create table ([,.. ])

For example, create a table called MyClass

Whether the field name numeric type data width is empty or whether the primary key automatically increases the default value

Id int 4 No primary key auto_increment

Name char 20 No

Sex int 4 No 0

Degree double 16 is

Mysql > create table MyClass (

> id int (4) not null primary key auto_increment

> name char (20) not null

> sex int (4) not null default'0'

> degree double (16Pol 2))

5.1 Delete the data table

Command: drop table

For example: delete a table named MyClass

Mysql > drop table MyClass

DROP TABLE is used to cancel one or more tables. You must have DROP permission for each table. All table data and table definitions will be canceled, so be careful when using this statement!

Note: for a partitioned table, DROP TABLE permanently undefines the table, cancels the partitions, and cancels all data stored in those partitions. DROP TABLE also cancels the partition definition (.par) file associated with the canceled table.

Use IF EXISTS to prevent errors for tables that do not exist. When using IF EXISTS, a NOTE is generated for each table that does not exist.

RESTRICT and CASCADE can make partitioning easier. Currently, RESTRICT and CASCADE are not working.

5.2 Table insert data

Command: insert into [[,.. ])] Values (value 1) [, (value n)]

For example, insert two records into the table MyClass, which indicate that the score of Tom with number 1 is 96.45, that of Joan with number 2 is 82.99, and that of Wang with number 3 is 96.5.

Mysql > insert into MyClass values (1), (2), (2)

Note: insert into can only insert one record into the table at a time.

5.3 query the data in the table

1), query all rows

Command: select from

Where

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 two rows of data in table MyClass

Mysql > select * from MyClass order by id limit 0Pol 2

Select is generally used with where to query more accurate and complex data.

5.4 Delete data from the table

Command: delete from table name where expression

For example: delete the record numbered 1 in table MyClass

Mysql > delete from MyClass where id=1

The following is a comparison of tables before and after deleting data.

FirstName

LastName

Age

Peter Griffin 35

Glenn Quagmire 33

Take the PHP code as an example to delete all LastName='Griffin' records in the "Persons" table:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.