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

The following content mainly brings you common methods of operating MySQL additions, deletions, changes and queries. The knowledge mentioned, different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Add record

INSERT INTO table name (field name) VALUES (value, value)

Delete record

DELETE FROM table name WHERE condition (from can be avoided in oracal)

Modify record

UPDATE table name SET field = value, field = value WHERE condition

Query record

SELECT field, field FROM table name WHERE condition

After understanding some of the most basic operation commands, let's learn how to create a database and database tables.

1. Use the show statement to find out what databases currently exist on the CVM:

Mysql > SHOW DATABASES

+-+

| | Database |

+-+

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

2. Create a database abccs

Mysql > CREATE DATABASE abccs

Note the case sensitivity of different operating systems.

3. Select the database you created

Mysql > USE abccs

Database changed

At this point, you have entered the database abccs that you just created.

4. Create a database table

First of all, let's see what tables exist in your database:

Mysql > SHOW TABLES

Empty set (0.00 sec)

Indicates that there are no database tables in the database you just created. Let's create a database table mytable:

We want to set up a birthday table for your employees, which contains the employee's name, gender, date of birth and city of birth.

Mysql > CREATE TABLE mytable (name VARCHAR (20), sex CHAR (1)

Birth DATE, birthaddr VARCHAR (20)) ENGINE=InnoDB DEFAULT CHARSET=gb2312

Query OK, 0 rows affected (0.00 sec)

Because the column values of name and birthadd vary, the length of VARCHAR is not necessarily 20. You can choose any length from 1 to 255, and if you need to change its word length later, you can use the ALTER TABLE statement. Gender only needs one character to indicate "m" or "f", so choose CHAR (1); the birth column uses the DATE data type.

After creating a table, we can look at the results we just did and use SHOW TABLES to show which tables are in the database:

Mysql > SHOW TABLES

+-+

| | Tables in menagerie |

+-+

| | mytables |

+-+

5. Display the structure of the table:

Mysql > DESCRIBE mytable

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name | varchar (20) | YES | | NULL |

| | sex | char (1) | YES | | NULL |

| | birth | date | YES | | NULL |

| | deathaddr | varchar (20) | YES | | NULL |

+-+ +

4 rows in set (0.00 sec)

6. Add a record to the table

Let's first use the SELECT command to view the data in the table: when a field is queried with an alias, the Chinese alias should be enclosed in single quotation marks

Mysql > select * from mytable

Empty set (0.00 sec)

This means that the table you just created is not yet recorded.

Add a new record:

Mysql > insert into mytable

-> value ('abccs','f','1977-07-07)

Query OK, 1 row affected (0.05sec)

Then use the SELECT command above to see what has changed. We can add the records of all employees to the table one by one in this way.

7. Load the data into a database table in text

If you enter it one by one, it will be troublesome. We can add all records to your database table as a text file. Create a text file "mysql.txt" with each line containing a record, separated by a tab, and given in the column order listed in the CREATE TABLE statement, for example:

Abccs f 1977-07-07 china

Mary f 1978-12-12 usa

Tom m 1970-09-02 usa

Load the text file "mytable.txt" into the mytable table using the following command: mysql > LOAD DATA LOCAL INFILE "mytable.txt" INTO TABLE pet (table name)

Then use the following command to see if the data has been entered into the database table: mysql > select * from mytable

MySQL utility command

Wikipedia, Encyclopedia of Freedom

1) Connect MYSQL:

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

1. Example 1: connect to the MYSQL on this computer

First, open the DOS window, and then enter the bin directory under the mysql installation directory, for example: d:\ mysql\ bin, and then type the command mysql-uroot-p. Enter prompts you to enter the password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by entering the MYSQL. The prompt for MYSQL is: enter >

Example 2: connect to the MYSQL on the remote host

Suppose the IP of the remote host is 10.0.0.1, the user name is root, and the password is 123. Type the following command:

Mysql-h30.0.0.1-uroot-p123

(note: U and root do not have to add spaces, and so do others)

3. Exit the MYSQL command

Exit (enter)

(2) change the password:

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

1. Example 1: add a password to root. First enter the directory C:\ mysql\ bin under DOS, and then type the following command:

Mysqladmin-uroot-password 123

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

2. Example 2: change the password of root to 456

Mysqladmin-uroot-pab12 password 456

(3) add new users: (note: unlike the above, the following is a command in the MYSQL environment, so it is followed by a semicolon as the command Terminator)

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

Example 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 use the root user to connect to MYSQL, and then type the following command:

Grant select,insert,update,delete on *. * to test1@ "%" Identified by "abc"

But the increase in the number of users in example 1 is very dangerous, if you want someone who knows the password of test1, then he can log in to your mysql database on any computer on internet and can do whatever he or she wants with your data.

Example 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 can not access the database directly from the internet, only through the web page on the MYSQL host.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"

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 test2@localhost identified by ""

(4) display command

1. Display a list of databases:

Show databases

At the beginning, there were only two databases: mysql and test. The mysql library is very important. it contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data table in the library:

Use mysql; / / Open the library

Show tables

3. Display the structure of the data table:

Describe table name

4. Build the database:

Create database library name

5. Create a table:

Use library name

Create table table name (list of field settings)

6. Delete the library and the table:

Drop database library name

Drop table table name

7. Clear the records in the table:

Delete from table name

8. Display the records in the table:

Select * from table name

# #

View the character set of mysql:

Mysql > show variables like "character\ _ set\ _%"

+-+ +

| | Variable_name | Value |

+-+ +

| | character_set_client | gbk |

| | character_set_connection | gbk |

| | character_set_database | gbk |

| | character_set_filesystem | binary |

| | character_set_results | gbk |

| | character_set_server | gbk |

| | character_set_system | utf8 |

+-+ +

Character_set_system is always utf-8.

These five had better always be consistent, or have fun with the garbled code:

Character_set_client

Character_set_connection

Character_set_database

Character_set_results

Character_set_server

%%

2008-09-12

How to start / stop / restart MySQL

I. start-up mode

D:\ MySQL\ MySQL Server 5.0\ bin > net stop mysql

The MySQL service is stopping.

The MySQL service stopped successfully.

1. Start with service: service mysqld start

2. Start with mysqld script: / etc/inint.d/mysqld start

3. Start with safe_mysqld: safe_mysqld&

2. Stop

Stop:

D:\ MySQL\ MySQL Server 5.0\ bin > net stop mysql

The MySQL service is stopping.

The MySQL service stopped successfully.

1. Start with service: service mysqld stop

2. Start with mysqld script: / etc/inint.d/mysqld stop

3 、 mysqladmin shutdown

3. Restart

1. Start with service: service mysqld restart

2. Start with mysqld script: / etc/inint.d/mysqld restart

One. Install and configure MYSQL

Two. Common mysql command line commands

1. Start and stop MySQL

Start the MYSQL service net start mysql

Stop the MYSQL service net stop mysql

2. Netstat-na | findstr 3306 to view the listening port. Findstr is used to find whether the following port exists.

3. Log in to the MYSQL console from the command line and use MYSQL COMMEND LINE TOOL

Syntax format mysql-user=root-password=123456 db_name

Or mysql-uroot-p123456 db_name

4. After entering the MYSQL command line tool, use status; or\ s to view the runtime information

5. Toggle the syntax for connecting to the database: use new_dbname

6. Show all databases: show databases

7. Show all tables in the database: show tables

8. Displays all the information about the creation of a table: show create table table_name

9. View the specific attribute information of the table and the description of each field in the table

Describe table_name; abbreviated form: desc table_name

Three. SQL statement in MySql

1. Database creation: Create database db_name

Database deletion: when deleting Drop database db_name;, you can first determine whether it exists. Write as follows: drop database if exits db_name

2. Table creation: syntax for creating a data table: create table table_name (field 1 data type, field 2 data type)

Example: create table mytable (id int, username char (20))

Delete table: drop table table_name; example: drop table mytable

8. Add data: Insert into table name [(field 1, field 2, … .)] Values (value 1, value 2, … .)

If you insert a value into each field in the table, the field name in the preceding [] parentheses can be written or unwritten

Example: insert into mytable (id,username) values

9. Query: query all data: select * from table_name

Query the data of the specified field: select field 1, field 2 from table_name

Example: select id,username from mytable where id=1 order by desc; multi-table query statement-refer to Article 17 as an example

10. Update the specified data, update the data of a field (note, not the name of the update field)

Update table_name set field name = 'new value' [, field 2 = 'new value', … ..] [where id=id_num] [order by field order]

Example: update mytable set username='lisi' where id=1

Order statements are the order of queries, such as order by id desc (or asc). There are two kinds of order: desc reverse (100-1, that is, query from the latest data), asc (from 1-100), Where and order statements can also be used to query select and delete delete

11. Delete the information in the table:

Delete information from the entire table: delete from table_name

Delete statements that specify conditions in a table: delete from table_name where conditional statements; conditional statements such as: id=3

12. Create a database user

You can create multiple database users at a time, such as:

CREATE USER username1 identified BY 'password', username2 IDENTIFIED BY' password'... .

13. User access control: grant

Library, table-level permission control: give control of a table in a library to a user

Grant all ON db_name.table_name TO user_name [indentified by 'password']

14. Modification of table structure

(1) add a field format:

Alter table table_name add column (field name field type);-this method is bracketed

(2) specify the location where the field is inserted:

Alter table table_name add column field name field type after a field

Delete a field:

Alter table table_name drop field name

(3) modify field name / type

Type of alter table table_name change old field name new field name new field

(4) change the name of the table

Alter table table_name rename to new_table_name

(5) clear all the data in the table at once

Truncate table table_name; this method also causes the number taker (ID) in the table to start at 1

15. Add primary key, foreign key, constraint, index. (for usage, see 17 examples)

① constraints (primary key Primary key, unique Unique, non-empty Not Null)

② auto-add auto_increment

③ foreign key Foreign key- is used in conjunction with reference table_name (col_name column name) and used separately when creating a table

④ deletes data associated with multiple tables-sets foreign key to set null-specific settings refer to help documentation

16. View the current database engine

SHOW CREATE TABLE table_name

Modify the database engine

ALTER TABLE table_name ENGINE=MyISAM | InnoDB

17. An example of the application of SQL statement:

-- 1 create users table

Create table users (id int primary key auto_increment,nikename varchar (20) not null unique,password varchar (100) not null,address varchar, reg_date timestamp not null default CURRENT_TIMESTAMP)

-- 2 create an articles table and set a foreign key when creating the table

Create table articles (id int primary key auto_increment,content longtext not null,userid int,constraint foreign key (userid) references users (id) on delete set null)

2.1Building articles tables without setting foreign keys

Create table articles (id int primary key auto_increment,content longtext not null,userid int)

-- 2.2 set foreign keys to the articles table

Alter table articles add constraint foreign key (userid) references users (id) on delete set null

-3. Insert data into the users table and insert multiple bars at the same time

Insert into users (id,nikename,password,address) values (1) (null,'lyh533','5678',' Beijing Haidian)

-- 4. Insert three pieces of data into article

Insert into articles (id,content,userid) values (2 recorder ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo

-5. Perform a multi-table query and select all messages posted by the user of ID=10 in the users table and all the information of that user

Select articles.id,articles.content,users.* from users,articles where users.id=10 and articles.userid=users.id order by articles.id desc

-6. View the database engine type

Show create table users

-7. Modify the type of database engine

Alter table users engine=MyISAM;-because ID in the users table is set to a foreign key, executing this sentence will cause an error

-8. Query with the table, in the case of a known condition. Query all users whose ID number is greater than the ID number of user lyh3

Select a.idmema. Nikenamejie a.address from users a users b where b.nikenamee girls lyh3 'and a.id > b.id

-can also be written as

Select id,nikename,address from users where id > (select id from users where nikename='lyh3')

9. Show employees who are older than their leaders:

Select a.name from users a Journal users b where a.managerid=b.id and a.age > b.age

Query the sender with the number 2: first check the articles table to get the sender's number, and then check the user name obtained by users according to the number.

Then use the association query.

Select * from articles,users gets the Descartes product and adds order by articles.id for observation.

Use select * from articles,users where articles.id=2 to filter out the combined records of No.2 post and each user

Then use select * from articles,users where articles.id=2 and articles.userid=users.id to select the record of the poster whose users.id is equal to No.2 post id.

Take only the user name: select user where user.id= (select userid from articles where article.id = 2)

Find people who are older than Xiao Wang: suppose Xiao Wang is 28 years old, first want to find people who are older than 28.

Select * from users where age > (select age from users where name='xiaowang')

* the records to be queried need to refer to other records in the table:

Select a.name from users a journal users b where b. Nameplates Xia owang 'and a.age > b.age

Every user in the table wants to compete. Select a. Nickname from users b. Nickname and a journal users b where a.id > b.id

A safer sentence: select a.nicknamejie b. Nickname from (select * from users order by id) a, (se

Lect * from users order by id) b where a.id > b.id

Then check all the posts posted by someone.

Select b.* from articles a, articles b where a.id=2 and a.userid=b.userid

Description: there is a relationship between tables, the interpretation of the concept of ER, using the sample database in access to demonstrate the relationship between tables. Only the innodb engine supports foreign key,mysql. Any engine that supports check currently does not support check constraints.

IV. Solutions to errors in the character set

Problems that have occurred:

Mysql > update users

-> set username=' Guan Yu'

-> where userid=2

ERROR 1366 (HY000): Incorrect string value:'\ xB9\ xD8\ xD3\ xF0' for column 'usern

Ame' at row 1

An error occurred while inserting Chinese characters into the table.

Mysql > select * from users

+-+ +

| | userid | username |

+-+ +

| | 2 |? |

| | 3 |? |

| | 4 |? í? | |

+-+ +

3 rows in set (0.00 sec)

The Chinese characters in the table are garbled.

Solution:

Use the command:

Mysql > status

-

Mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)

Connection id: 8

Current database: test

Current user: root@localhost

SSL: Not in use

Using delimiter:

Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Protocol version: 10

Connection: localhost via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: gbk

Conn. Characterset: gbk

TCP port: 3306

Uptime: 7 hours 39 min 19 sec

Threads: 2 Questions: 174 Slow queries: 0 Opens: 57 Flush tables: 1 Open ta

Bles: 1 Queries per second avg: 0.006

-

Look at mysql and find that the character set of Server characterset,Db characterset is set to latin1, so Chinese garbled appears.

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | users |

+-+

1 row in set (0.00 sec)

Change the character set of the table.

Mysql > alter table users character set GBK

Query OK, 3 rows affected (0.08 sec)

Records: 3 Duplicates: 0 Warnings: 0

View the structure of the table:

Mysql > show create users

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

Corresponds to your MySQL server version for the right syntax to use near 'users

'at line 1

Mysql > show create table users

+-

-+

| | Table | Create Table |

| |

+-

-+

| | users | CREATE TABLE `users` (

`userid` int (11) default NULL

`username` char (20) character set latin1 default NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

+-

-+

1 row in set (0.00 sec)

Mysql > desc users

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | userid | int (11) | YES | | NULL |

| | username | char (20) | YES | | NULL |

+-+ +

2 rows in set (0.02 sec)

At this point, insert Chinese into the table and then there is an error.

Mysql > insert into users values (88Jing 'Chinese')

ERROR 1366 (HY000): Incorrect string value:'\ xD6\ xD0\ xCE\ xC4' for column 'usern

Ame' at row 1

Mysql > insert into users values (88Jing 'Chinese')

ERROR 1366 (HY000): Incorrect string value:'\ xD6\ xD0\ xCE\ xC4' for column 'usern

Ame' at row 1

Also change the character set of the username of the users table.

Mysql > alter table users modify username char (20) character set gbk

ERROR 1366 (HY000): Incorrect string value:'\ xC0\ xEE\ xCB\ xC4' for column 'usern

Ame' at row 1

Mysql > alter table users modify username char (20) character set gbk

ERROR 1366 (HY000): Incorrect string value:'\ xC0\ xEE\ xCB\ xC4' for column 'usern

Ame' at row 1

Because there is already data in the table, the operation to change the username character set did not succeed.

Clear the data in the users table

Mysql > truncate table users

Query OK, 3 rows affected (0.01sec)

Newly change the character set of username in the user table

Mysql > alter table users modify username char (20) character set gbk

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

At this time, Chinese characters are inserted and inserted into *.

Mysql > insert into users values (88Jing 'Chinese')

Query OK, 1 row affected (0.01sec)

Mysql > select * from users

+-+ +

| | userid | username |

+-+ +

| | 88 | Chinese |

+-+ +

1 row in set (0.00 sec)

Mysql >

For the above common methods of operating MySQL additions, deletions, changes and searches, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.