Common commands for MySQL database operations and Common commands for mysql

Common commands for MySQL database operations and Common commands for mysql

Common commands for MySQL database operations
DOS Connection database
1. Install MySQL to configure the environment
2. Run the cmd command. net start mysql
3. Find the root directory of the mysql file and enter the mysql-h localhost-u root-p. Press enter and enter the password to connect to the database.
MySQL Common commands
Show databases: displays all databases
Create database name; create a database?
Use databasename; select database?
Drop database name directly deletes the database. Don't remind me?
Show tables; displays tables?
Describe tablename; what is the detailed description of the table?
Select * from tablename ;?
Mysql command to query the total number of records in a table (three methods)
Select count (*) from tablename;
Or
Select count (*) as num from tablename;
Or
Select count (*) as total from tablename;
In select, how does one add distinct to remove duplicate fields?
Before mysqladmin drop databasename deletes a database, a message is displayed .?
Display current mysql version and current date?
Select version (), current_date ;?
Modify the root password in mysql
Shell> mysql-u root-p?
Mysql> update user set password = password ("xueok654123") where user = 'root ';?
MySQL> flush privileges // refresh the database?
Mysql> use dbname; open the database :?
Mysql> show databases; show all databases?
Mysql> show tables; display all tables in mysql: use mysql first; then?
Mysql> describe user; displays the column information of the user table in the mysql database );?
Grant?

Create a full super user who can connect to the server from anywhere, but must use a password something to do this?

Mysql> grant all privileges on *. * to user @ localhost identified by 'something'?

Add new users?

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

Grant all privileges on *. * TO monty @ localhost identified by 'something' with grant option ;?

Grant all privileges on *. * TO monty @ "%" identified by 'something' with grant option ;?

Delete authorization :?

Mysql> revoke all privileges on *. * from root @ "% ";?

Mysql> delete from user where user = "root" and host = "% ";?

Mysql> flush privileges ;?

Create a User custom to log on to the specific client it363.com. can I access the specific database fangchandb?

Mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom @ it363.com identified by 'passwd '?
Rename a table :?

Mysql> alter table t1 rename t2 ;?

4. mysqldump?

Backup database?

Shell> mysqldump-h host-u root-p dbname> dbname_backup. SQL?

Restore database?

Shell> mysqladmin-h myhost-u root-p create dbname?

Shell> mysqldump-h host-u root-p dbname <dbname_backup. SQL?

If you only want to unload the table creation command, the command is as follows :?

Shell> mysqladmin-u root-p-d databasename> a. SQL?

If you only want to unload the SQL command for inserting data without the table creation command, the command is as follows :?

Shell> mysqladmin-u root-p-t databasename> a. SQL?

What should I do if I only want data and do not want any SQL commands ??

Mysqldump-T./phptest driver?

Only when the-T parameter is specified can the plain text file be detached, indicating the directory where the data is detached and./indicates the current directory, that is, the same directory as mysqldump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands .?

5. You can store the query in a file and tell mysql to read the query from the file instead of waiting for keyboard input. You can use the shell to type the redirection utility to do this. For example, if the file my_file. SQL contains a query?

Query :?
For example, if you want to prefix the table syntax in SQL .txt :?

Mysql> mysql-h myhost-u root-p database <SQL .txt?

 

1. install and configure MYSQL?
2. Commonly Used mysql command line commands?
1. Start and Stop mysql?
Start MYSQL service. net start mysql?
Stop MYSQL service. net stop mysql?
2. netstat-na | findstr 3306: Check the listening port. Is findstr used to find whether the following port exists?
3. log on to the MYSQL console in the command LINE, that is, using mysql commend line tool?
Syntax: mysql-user = root-password = 123456 db_name?
Or mysql-u root-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. What are the 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 a value is inserted into each field in the table, can the field names in the [] brackets be writable or not?
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 ;?
For example: select id, username from mytable where id = 1 order by desc; multi-Table query statement ---------- refer to the 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?
?
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?
Multiple database users can be created at a time, such :?
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. How to 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) can all data in the table be cleared 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 usage, see instance 17 )?
① Constraint (Primary key, Unique, non-Null Not Null )?
② Automatically add auto_increment?
③ Use the Foreign key ----- in combination with reference table_name (col_name column name) and separately used for table creation?
④ Delete data associated with multiple tables ---- set foreign key to set null --- for specific 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. Do I not set a foreign key when creating the table?
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 into the users table 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. For multi-table queries, select all messages and all 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 the database engine type?
Show create table users ;?
?
-- 7. Modify the database engine type?
Alter table users engine = MyISAM; --- because the IDs in the users table are set as foreign keys, an error occurs when executing this statement?
?
-- 8. If the same table query has a condition 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 ;?
------ Can also be written?
Select id, nikename, address from users where id> (select id from users where nikename = 'lyh1 ');?
?
9. 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 number, and then query the username obtained by users according to the number .?
Then query by association .?
Select * from articles, users get the flute 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 the person older than Tom: If Tom is 28 years old, first want to find the person older than 28?
Select * from users where age> (select age from users where name = 'xiaowang ');?
* *** For the record to be queried, 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 .?
4. How can I solve a character set error?
Question :?
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 |? Í? Bytes |?
+ -------- + ---------- +?
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 characters are garbled .?
?
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 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 then there is an error .?
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 ');?
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 the table already has data, the operation for 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 )?
?
Changing 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 the 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>?