Some basic mysql operations in linux

Common MySQL database operations 1. display the database showdatabases; 2. Select the database use database name; 3. display the table showtables in the database; 4. display the table structure describe table name; 5. The SELECT * FROM table name is displayed. 6. The createdatabase database name is created. 7. The createtable table name (

Common MySQL database operations 1. Display database show databases; 2. Select database use database name; 3. display tables in the database show tables; 4. display the table structure describe table name; 5. display the SELECT * FROM table name in the table; 6. create database name in the database; 7. create table in the table (

Common MySQL database operations

1. display the database

 show databases;

2. Select a database

Use Database Name;

3. display tables in the database

show tables;

4. display the data table structure

Describe table name;

5. display table records

SELECT * FROM Table Name

6. Create a database

Create database name;

7. Create a table

Create table name (field setting list); mysql> create table name (-> id int auto_increment not null primary key,-> uname char (8 ), -> gender char (2),-> birthday date); Query OK, 0 rows affected (0.03 sec) mysql> show tables; + ------------------ + | Tables_in_userdb | + ---------------------- + | name | + ------------------ + 1 row in set (0.00 sec) mysql> describe name; + ---------- + --------- + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + ---------- + --------- + ------ + ----- + --------- + -------------- + | id | int (11) | NO | PRI | NULL | auto_increment | uname | char (8) | YES | NULL | gender | char (2) | YES | NULL | birthday | date | YES | NULL | + ---------- + --------- + ------ + ----- + --------- + ---------------- + 4 rows in set (0.00 sec) note: auto_increment auto-incrementing primary key

8. Add records

Insert into name (uname, gender, birthday) values ('zhang san', 'male', '2017-10-01 ');

9. Modify records

Update name set birthday = '2017-01-10 'where uname = 'zhang san ';

10. delete records

Delete from name where uname = 'zhangsan ';

11. delete a table

Drop table Name

12. delete a database

Drop database name;

13. Back up the database

Mysqldump-u root-p -- opt Database Name> Backup name; // enter the database directory

14. Recovery

Mysql-u root-p Database Name
 <备份名; 恢复时数据库必须存在，可以为空数据库< pre>
  

  
 
  
15. Database authorization
  
Format: grant select on database. * to username @ login host identified by "password"
  
Example 1: Add a user user001 with a password of 123456 so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:
  

  
 mysql> grant select,insert,update,delete on *.* to user001@"%" Identified by "123456";
  

  
 
  
Example 2: Add a user user002 with a password of 123456 so that the user can only log on to localhost or set the specified IP address, you can also query, insert, modify, and delete the database test (localhost refers to the local host, that is, the host where the MySQL database is located)
  
// In this way, the user knows the password user_2 and cannot directly access the database from the Internet. The user can only operate the test database on the MYSQL host.
// First use the root user to connect to MySQL, and then type the following command:
  

  
  mysql>grant select,insert,update,delete on test.* to user002@localhost identified by "123456";
  

  
 
  
Note: You can also use the table modification method to process user logon methods:
Database: Mysql
  
Table: User
  

  
Modify: set the value of the Host column in the User table to the actual logon entry.
  


  


  
16. mysql. h in linux: there is no such file or directory
  
Libmysqlclient-dev is missing. Install libmysqlclient-dev with OK. apt-get installlibmysqlclient-dev
  
 
  
17. How to uninstall mysql in linux
  
Run the command rpm-qa | grep-I mysql to obtain the mysql installation version.
  
If the version that appears is a MySQL-server-community-5.0.90-0.rhel4

  
Next use command rpm-e MySQL-server-community-5.0.90-0.rhel4

  
If nothing appears, it indicates that it has been uninstalled successfully.
  
After the execution, if the prompt is: error: Failed dependencies:
Libmysqlclient. so.15 is needed by (installed) amarok-1.4.8-1.fc7.i386
Libmysqlclient. so.15 (libmysqlclient_15) is needed by (installed) amarok-1.4.8-1.fc7.i386
  

  
Some of them are dependence relationships. Then, we can simply take them all at once.
  

  
Run the full uninstall command: rpm-e amarok-1.4.8-1.fc7.i386 mysql-libs-5.0.45-6.fc7
  

18. View mysql character sets in linux
  
Show variables like 'character _ set _ % ';
  
 
  
19. Access MYSQL Data Using C Language
  
Connection routine
Connecting to the MYSQL database using C language involves two steps:
(1) initialize a connection handle Structure
(2) actual connection
First, use mysql_init to initialize the connection handle.
# Include
   
    
MYSQL * mysql_init (MYSQL *);
    
Usually you give NULL to this routine and it will return a pointer pointing to the new connection handle structure. If you pass
    
The existing structure is reinitialized. This routine returns NULL when an error occurs.
    
So far, you have only allocated and initialized a structure. You still need to use mysql_real_connect
    
Connection parameters:
   
  
MYSQL * mysql_real_connect (MYSQL * connection,
  
Const char * server_host,
  
Const char * SQL _user_name,
  
Const char * sq_password,
  
Const char * db_name,
  
Unsigned int port_number,
  
Const char * unix_socket_name,
  
Unsigned int flags );
  
The connecton pointer must point to the structure initialized by mysql_init.
  
Server_host can be either a host name or an IP address.
  
SQL _username and SQL _password have the same meaning as their literal meanings. If the login name is NULL, assume that the login name is the login ID of the current linux User. If the password is NULL, you can only access data on the server without a password. The password is encrypted before transmission over the network.
  
Port_number and unix_socket_name should be 0 and NULL respectively, unless you change the default configuration for MYSQL installation.
  
If the connection fails, NULL is returned. The mysql_error function provides helpful information.
  
After the connection is used, mysql_close is usually called when the program exits:
  
Void mysql_close (MYSQL * connection );
  
This will close the connection. If the connection is established by mysql_init, the MYSQL structure will be released and the pointer will become invalid and cannot be used again.
  
 
  
Generally, the following four steps are required to extract data from a C application:
  
(1) execute the query
  
(2) extract data
  
(3) process data
  
(4) necessary cleanup work
  
You will use mysql_query to send SQL statements, use mysql_store_result or mysql_use_result to extract data, use mysql_fetch_row to process data, and use mysql_free_result to release the memory resources occupied by the query.
  
 
  
 
  
19. Create a user in mysql
  
We can create users for different machines and assign them different connection permissions. In particular, for security reasons, we only allow root users to connect through local machines.
  
We will create a new user with considerable permissions, rick, which can be connected in three different ways:
  
(1) connect from a local machine.
  
(2) From IP address 192.168.0.0 ~ Any machine in the range of 192.168.0.255
  
(3) connect to any machine in the wiley.com domain.
  
The safest and easiest way is to create three users who connect from three different locations. If you want, we can even set three different passwords for them based on their connection.
  
Connect to MYSQL as root and perform the following operations in sequence:
  
(1) create a local logon for rick:
  
Mysql> grant all on *. * to rick @ localhost identified by 'secretpassword ';
  
(2) create a logon from Class C subnet 192.168.0. Note that we must use single quotes to protect the IP range, and use the mask/mask 255.255.0 to determine the allowed IP Range:
  
Mysql> grant all on *. * to rick @ '192. 168.0.0/255.255.255.0 'identified by 'secretpassword ';
  
(3) create a logon so that rick can log on from any machine in the wiley.com domain.
  
Mysql> grant all on *. * to rick @ '% .wiley.com' identified by 'secretpassword ';
  
(4) Now let's check the user table again to check the entries:
  
Mysql> select user, host, password from mysql. user;