Common MySQL database commands

Source: Click to open the link

Error executing insert: incorrect string value: '\ xe8 \ XAE \ xA1 \ xe7 \ XAE \ x97...' for column 'dep' at Row 1

The solution to this problem is as follows: (read carefully)

1. install and configure MySQL
Ii. Commonly Used MySQL command line commands
1. Start and Stop MySQL
Start the MySQL service. Net start MySQL
Stop MySQL service. net stop MySQL
2. netstat-Na | findstr 3306: Check the listening port. findstr is used to check whether the following port exists.
3. log on to the MySQL console using the 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 running environment information
5. Switch the database connection Syntax: Use new_dbname;

6. display all databases: Show databases;

7. display all tables in the database: show tables;

8. Show all information about 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; Abbreviation: DESC table_name;
3. SQL statements in MySQL
1. Create a database: Create Database db_name;
Database deletion: drop database db_name; When deleting a database, you can first determine whether it exists and write it as: drop database if exits db_name

2. Create a 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 to each field in the table, the field names in the front [] brackets can be written or not written.
Example: insert into mytable (ID, username) values (1, 'hangsan ');

9. Query: Query all data: Select * From table_name;
Query the data of a 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 17th instances

10. Update the specified data and update the data of a field (Note: it is not the name of the updated field)
Update table_name set field name = 'new value' [, Field 2 = 'new value',...] [Where id = id_num] [order by field order]
For example, update mytable set username = 'lisi' where id = 1;
The Order statement is the query order, for example, order by id desc (or ASC). There are two types of order: DESC Reverse Order (100-1, that is, querying 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 the information in the entire table: delete from table_name;
Statement for deleting the specified condition in the Table: delete from table_name where Condition Statement; Condition Statement: Id = 3;

12. Create a database user
You can create multiple database users at a time, for example:
Create user username1 identified by 'Password', username2 identified by 'Password '....

13. user permission control: Grant
Database and table-level permission control: grant the control of a table in a database to a user
Grant all on db_name.table_name to user_name [indentified by 'Password'];

14. Modify the table structure
(1) Add a Field Format:
Alter table table_name add column (field Name field type); ---- This method contains parentheses
(2) Specify the field Insertion Location:
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 the field name/Type
Alter table table_name change the type of the new field in the old field name;
(4) change the table name
Alter table table_name Rename to new_table_name;
(5) Clear all data in the table at one time
Truncate table table_name; this method also enables the number generator (ID) in the table to start from 1.

15. Add the primary key, foreign key, constraint, index .... (For how to use this function, see instance 17)
① Constraint (primary key, unique, non-null not null)
② Automatically add auto_increment
③ Use the foreign key ----- with reference table_name (col_name column name) separately during table Creation
④ Delete data associated with multiple tables ---- set foreign key to set null --- for detailed settings, see the help documentation.

16. view the current database engine
Show create table table_name;
Modify Database Engine
Alter table table_name engine = MyISAM | InnoDB;

17. SQL statement application example:
-- 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 (200 ), reg_date timestamp not null default current_timestamp );

-- 2 create the articles table and set the 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.1 create an articles table. No foreign keys are set during table creation.
Create Table articles (ID int primary key auto_increment, content longtext not null, userid INT );
-- 2.2 set a foreign key for the articles table
Alter table articles add constraint foreign key (userid) References users (ID) on Delete set NULL;
------------------------------------------------------------------------

-- 3. insert data to the users table and insert multiple data entries at the same time
Insert into users (ID, nikename, password, address) values (1, 'lyh1 ', '20170101', null), (10, 'ly292', '123 ', 'hubei Wuhan '), (null, 'lyh333', '123', 'Beijing Haidian ');

-- 4. insert three pieces of data into article
Insert into articles (ID, content, userid) values (2, 'hahahahahahahaha', 11), (null, 'xixixixixix ', 10), (13, 'aiaiaiaiaiaiaiaiaiaa ', 1), (14, 'hoahaoaoooooooooooo ', 10 );

-- 5. Perform a multi-Table query and select all messages and information of the user whose ID is 10 in the users table.
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 database engine types
Show create table users;

-- 7. Modify the database engine type
Alter table users engine = MyISAM; --- an error occurs when this statement is executed because the IDs in the users table are set as foreign keys.

-- 8. query in the same table. If a condition is known, query all users whose ID number is greater than the user's ID number lyh1.
Select a. ID, A. nikename, A. Address from users a, users B where B. nikename = 'lyh1 'and A. ID> B. ID;
------ It can also be written
Select ID, nikename, address from users where ID> (select ID from users where nikename = 'lyh1 ');

9. Display employees older than leaders:
Select a. name from users a, users B where a. managerid = B. ID and A. age> B. Age;

Query the sender of 2: first query the articles table to obtain the sender's ID, and then query the username obtained by users.
Then query by association.
Select * from articles, users get the Cartesian Product, and add order by articles. ID for observation.

Use select * from articles, users where articles. ID = 2 to filter the combination records of post 2 and each user.

Then use select * from articles, users where articles. ID = 2 and articles. userid = users. ID to select the record of the sender ID of the users. ID = 2 Post.

Username only: Select User where user. ID = (select userid from articles where article. ID = 2)

Find someone older than Tom: If Tom is 28 years old, first find someone older than 28.
Select * from users where age> (select age from users where name = 'xiaowang ');
* ***** The record to be queried must refer to other records in the table:
Select a. name from users a, users B where B. Name = 'xiaowang 'and A. age> B. Age

Each user in the table wants To pk. Select a. Nickname, B. nickname from users a, users B where a. ID> B. ID;

More insurance statement: select a. Nickname, B. nickname from (select * from users order by ID) A, (SE
Lect * from users order by ID) B where a. ID> B. ID;

Query all posts posted by a user.
Select B. * from articles A, Articles B where a. ID = 2 and A. userid = B. userid

Note: There is a relationship between tables. The ER concept is used to explain the relationship between tables in the example database in access. only the InnoDB Engine supports foreign keys. Currently, any MySQL engine does not support check constraints.
Iv. Solutions to Character Set errors
Problems:
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 |? Í? Optional |
+ -------- + ---------- +
3 rows in SET (0.00 Sec)
Chinese characters in the table are garbled.
Solution:
Run the following 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
--------------
Check MySQL and find that the character set of server characterset and DB characterset is set to Latin1, so Chinese garbled characters are displayed.

Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Users |
+ ---------------- +
1 row in SET (0.00 Sec)

Modify the character set of a table.
Mysql> alter table users Character Set GBK;
Query OK, 3 rows affected (0.08 Sec)
Records: 3 duplicates: 0 Warnings: 0

View the table structure:
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)

Insert Chinese characters into the table and an error occurs.
Mysql> insert into users values (88, 'Chinese ');
Error 1366 (hy000): incorrect string value: '\ xd6 \ xd0 \ xce \ xc4' for column 'usern
Ame' at Row 1
Mysql> insert into users values (88, 'Chinese ');
Error 1366 (hy000): incorrect string value: '\ xd6 \ xd0 \ xce \ xc4' for column 'usern
Ame' at Row 1

You also need to change the username Character Set 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 data already exists in the table, changing the username character set is not ***
Clear Data in the users table
Mysql> truncate table users;
Query OK, 3 rows affected (0.01 Sec)

Change the username character set 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

Then, insert a Chinese character ***.
Mysql> insert into users values (88, 'Chinese ');
Query OK, 1 row affected (0.01 Sec)

Mysql> select * from users;
+ -------- + ---------- +
| Userid | username |
+ -------- + ---------- +
| 88 | Chinese |
+ -------- + ---------- +
1 row in SET (0.00 Sec)
Mysql>