Quick Start for MySQL

Recently wanted to use C language to write a chat system, so looked at MySQL. See the process of continuous summary, wrote a few things.

To illustrate, the system I'm using is ubuntu18.04,

Installation and use of MySQL under Linux

1. Installation
sudo apt-get install Mysql-server
sudo apt install mysql-client
sudo apt install Libmysqlclient-dev
What does it mean to install with these three commands?
The first one is the MySQL server
The second one is the MySQL client
The third one is the C language library of MySQL. After installation, the location under/usr/include/mysql/


2. Connect to the database
Mysql-h host Address-u user name-P user Password
Mysql-h Localhost-u Root-p


3. Simple operation of the database

# whereis MySQL View file installation path
# which MySQL query run file path (folder address)
Usr/bin/mysql refers to: The running path of MySQL
Var/lib/mysql refers to: The storage path of MySQL database files
Usr/lib/mysql refers to: MySQL installation path

4. Database-related Operations

(1). operation of the database
View Database
show databases; (MySQL must end with a semicolon);
Create a database
Create Database MyData;
Deleting a database
Drop Database MyData;
Use a database
Use MyData;
(2). Create, add, delete, modify tables in the database
Create a table
CREATE TABLE MyTable
(//using parentheses
ID int auto_increment NOT NULL primary key,//integer, self-increment, not NULL, primary key
Name varchar (20),//String 20.
);
Inserting data
Insert MyTable value (123, "Qiny"); Sets the value of the first ID, which is the self-increment
Insert MyTable (name) value ("Hello"); Add data with Selection
Insert MyTable (name) VALUES ("Dxx"), ("Xiin"); Bulk Add data
Delete a table
drop table mytable; Delete a table
Delete a row of data from a table
Delete from mytable where id = 123;
Modify the property values of a table//modify the properties of a table including adding properties, deleting properties, modifying properties
ALTER TABLE MyTable
The Add age int is not NULL,
Drop name,
Modify ID int not NULL PRIMARY key auto_increment,
Modify the table's data
Update mytable Set name = ' Nxnn ' where id = 123;

(3) database query (Common)
SELECT * from student;
Select Name,id from student;
SELECT * FROM student where id = 123;
Select COUNT (*), AVG (score), SUM (score) from student group by age;
SELECT * FROM student ORDER BY id DESC | Asc

(4) Other
View the construction of a table
DEAC mytable;

5. Database Permissions management (I don't know why it's not successful)
(1) Create a user
CREATE USER ' qiny ' @ ' localhost ' identified by ' 123456 ';
Create user ' root ' @ ' localhost ' identified by ' password ';
(2) Assigning permissions
GRANT all privileges on * * to ' qiny ' @ ' localhost ' identified by ' 123456 ';
GRANT all privileges on * * to ' root ' @ ' localhost ' identified by ' password ';
(3) Remove permissions
Evoke all privileges on * * from ' username ' @ ' localhost ';

6.c language Connection MySQL
(1) Installing the MSYQL library file
Compilation: GCC Xxx.c-o target-lmysqlclient
The meaning of-lmysqlclient is to use Mysqlclient's dynamic library file.
(2) using MySQL library function (related function reference mysql.h or online search)
MYSQL * CONN; Create a MySQL database connection pointer
conn = Msyql_init (NULL); Initialize this database connection pointer
conn = Mysql_real_connect (conn, "localhost", "qiny", "Password", "Test", 0,null,0);//Connect to the database, where the first parameter is a database connection pointer, the second parameter is the address, The third parameter is the user name, the fourth parameter is the password, and the fifth parameter is the name of the database.
mysql_query (conn,sql);//Issue a query request to the database.
Mysql_res * res_ptr = Mysql_store_result (conn); Create a data storage area to accept the results of the query.
int column = Mysql_num_fields (res_ptr);//Gets the number of columns for the database table.
int row = mysql_num_rows (res_ptr);//Gets the number of rows for the database table.
Mysql_field * FIELD = Mysql_fetch_field (res_ptr);//Gets the column name of the database return value table.
Mysql_row * Mysql_row = mysql_fetch_row (res_ptr);//Gets a row of data in the database.

Quick Start for MySQL