Operations on MySQL Databases in Linux

First install the following software package on Ubuntu/Debian: mysql-client-
5.0 mysql-server-5.0

Install the following software packages on Redhat: mysql-3.23.54a-11.i386.rpm
Mysql-server-3.23.54a-11.i386.rpm
And mysql-devel-3.23.54a-11.i386.rmp (can be installed with a higher version, this article is tested under Ubuntu)

1. Run
Row: mysql_install_db
(This step does not seem to work)

/* Startmysqld

Server and establish the initialMySQL
Authorization Table, which contains the permissions that determine how a user is allowed to connect to the server */
2. Start the database


Method 1: Run sudo mymysqld_safe on the terminal.
&
// Start the MySQL database
Method 2: Run sudo/etc/init. d/MySQL on the terminal.
Start

3. Check whether MySQL is started.


Method 1: terminal run: pstree | grep mysqld

The following result indicates that the instance has been started:
|-Mysqld_safe-+-logger
| '-Mysqld --- 9 * [{mysqld}]
Method 2: Run sudo/etc/init. d/MySQL on the terminal.
Status

The following result indicates that the instance has been started: (different system versions are slightly different)
*/Usr/bin/mysqladmin ver 8.41 distrib 5.0.24a,
PC-Linux-GNU on ibench
Copyright (c) 2000 MySQL AB & MySQL Finland AB & tcx
Datakonsult AB
This software comes with absolutely no warranty. This is free
Software,
And you are welcome to modify and redistribute it under the GPL
License
Server version 5.0.24a-Debian_9-log
Protocol version 10
Connection localhost via UNIX socket
UNIX socket/var/run/mysqld. Sock
Uptime: 1 min 23 sec
Threads: 1 questions: 147 slow queries: 0 opens: 98 flush
Tables: 1 open tables: 14 queries per second AVG: 1.771
In addition, if you want to automatically start the system after it starts up, you can download a sysvconfig command: sudo
Apt-Get install sysvconfig
(This command is available in RedHat: ntsysv)
Run sudo sysvconfig on the terminal and enter enable/disable in the menu. Select and save MySQL.
4. Close the MySQL database

Method 1: sudo/etc/init. d/MySQL stop

Method 2: sudo mysqladmin Shutdown

5. Enter the database

Terminal run: mysql-u root-P

// Enter a blank password. MySQL defaults to a blank password for the first time. Press enter.
6. Create a database

Mysql> Create Database test;

// Create a Test Database
Output results: Query OK, 1 row affected (0.00 Sec)
Mysql> show databases;

// Display all databases
Output result:
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Bear |
| MySQL |
| Test |
+ -------------------- +
4 rows in SET (0.00 Sec)
7. delete a database


Mysql> database test;

// Delete the Test Database
Output result:
Query OK, 0 rows affected (0.00 Sec)
Mysql> show databases;

// Display all databases
Output result:
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Bear |
| MySQL |
+ -------------------- +
3 rows in SET (0.01 Sec)
8. Create a table


Mysql> Create Database test;

// Create a Test Database
Mysql> use test;

// Enter the Test Database
Mysql> show tables;

// View all tables
Output result: empty set (0.00 Sec)
Mysql> Create Table people (
//
Create a table named "people" with the following four columns:
-> Lele_id char (6) primary
Key,
// Lele_id is the primary key
-> Name
Char (10 ),
// Name
-> Sex
Char (2 ),
// Sex
-> Birthday
Date );
// Birthday
Output result:
Query OK, 0 rows affected (0.02 Sec)
Mysql> show tables;
// Display
Show all tables
Output result:
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| People |
+ ---------------- +
1 row in SET (0.00 Sec)
9. delete a table

/* This step can be done at the end, because the table people */is also used below */
Mysql> table people;

// Delete a table
Mysql> show tables;
//
Show all tables
10. view the table structure


Mysql> use test;

// Enter the database for viewing the table structure
Mysql> describe people;

// View the table structure
Display result:
+ -------- + ------- + ---- + ------ + ----- +
| FIELD | type | null | key |
Default | extra |
+ -------- + ------- + ---- + --- + ------ +
| People_id | char (6) | no | PRI
|
| Name | char (10) | Yes |
Null |
| Sex | char (2) | Yes |
Null |
| Birthday | date | Yes |
Null |
+ -------- + ------- + --- + ----- + ------ +
4 rows in SET (0.00 Sec)
11. Add and view data to the table

Mysql> use test;

// Enter the Test Database
Mysql> into people
Values
// Add two groups of data to the people table
->
('123456', 'xiong feng', 'M', '2014/1/23 ');

Mysql> into people values


-> ('20140901', 'zheng ling', 'w', '2017-08-18 ');

Mysql> select * people;

// View the data in the people table
Display result:
+ ----------- + -------- + ------ + ------------ +
| People_id | Name | sex
| Birthday |
+ ----------- + -------- + ------ + ------------ +
| 200701 | Xiong Feng | M
| 1983-04-23 |
| 200702 | Zheng Ling | w |
1986-08-18 |
+ ----------- + -------- + ------ + ------------ +
2 rows in SET (0.00 Sec)
Mysql> select * People
Lele_id = '20140901 ';

// View the data whose primary key value is 200701 in The People table
Display result:
+ ----------- + -------- + ------ + --------- +
| People_id | Name | sex | birthday |
+ ----------- + -------- + ------ + --------- +
| 200701 | Xiong Feng | M | 1983-04-23 |
+ ----------- + -------- + ------ + --------- +
1 row in SET (0.00 Sec)
12. Modify the table structure


Mysql> alter table people add score
INT (3) not null;

Display result:
Query OK, 2 rows affected (0.01 Sec)
Records: 2 duplicates: 0 Warnings: 0
Mysql> describe people;
// Display
Table Structure
Display result:
+ -------- + ------- + ---- + ------ + ---- +
| FIELD | type | null | key | default |
Extra |
+ -------- + ------- + ---- + --- + ------- + ---- +
| People_id | char (6) | no | pri |
|
| Name | char (10) | Yes | null
|
| Sex | char (2) | Yes | null
|
| Birthday | date | Yes | null
|
| Core | int (11) | Yes | null
|
| Score | int (3) | no |
|
+ -------- + ------- + ---- + -- + -------- + ---- +
6 rows in SET (0.00 Sec)

Note: Data Types in MySQL

Data Type
Default Value

Char/varchar/binary ""

Date
0000-00-00

Time
00:00:00

Datetime
0000-00-00 00:00:00

Timestamp
12:01:00 (now ()!)

Year
0000

Enum ('A', 'B', 'C ')
'A'

Set ('A', 'B', 'C ')
() (Empty set)