Mysql database commands

If you are used to using windows databases, that is, operating databases on the graphic interface, you will feel unfamiliar when you operate mysql on commands. In fact, as long as you have mastered some basic commands, there are also frequent use, skilled, it will be quite convenient, and very fast.

Now, let's bring some new mysql friends into the mysql world. Haha.

Check the current database version, because some commands may be used differently in different versions,
Note that-V is an uppercase letter.
[Root @ localhost ~] # Mysqladmin-V
Mysqladmin Ver 8.42 Distrib 6.0.4-alpha, for redhat-linux-gnu on i686

In this way, the mysql version I am using is 6.0.4-alpha.

----------------------
After you install the database, you must enter mysql for testing.

First, the login and exit command should be the most basic.

Mysql-uroot-p123

This indicates that the user name is root and the password is 123.

Mysql> indicates that you have successfully logged in.

Then you can enter some commands for mysql to display relevant information, for example, view the following operations:

---------------------------------

View database list

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| 17 rumen |
| Mysql |
| Test |
+ -------------------- +
4 rows in set (0.01 sec)

In this way, four databases are displayed.

I forgot to mention that when you enter the mysql command, you need to add a; semicolon, which indicates that this command has been used

Then press Enter.

------------------------------

To access a database, run the following command:

Use Database Name;

For example, use mysql; indicates entering the mysql database

After entering the database, you can view the data table of the current database.

Show tables;

All columns of the current database are displayed;

---------------------------------

Then you can view the table content, that is, the field content.

For example:

Select User, Password from user;

This is the username and password used to view the mysql database. Of course, this password is encrypted.

==================================
Although these steps are complicated, they are easy to understand and can only be viewed layer by layer.

To view the data table, you must enter a database,

When you want to view the table data, you need to enter a table.

Do I have to exit step by step after entering?

This is not required. For example, you can access other databases at any time.

For example, if I am currently on a table in the mysql database,

I can directly use 17 rumen; directly go to the database 17 rumen.

Yes, it's very convenient.

==========================================================

Then let's talk about cross-database queries, that is, I am currently viewing

Information.

I am currently in the mysql database. I want to check the news table in the database 17 rumen.

How?

Select * from 17rumen. news;

In this way, you do not need to operate on the 17 rumen database.

As you can see, this makes it easy to perform operations, leaving a lot of time.

======================================

You can also view the structure of a table, including the field name, whether to allow Null, primary key, foreign key, default value, and so on.

Desc user;

View the structure of the user table. Field indicates the Field name and Type indicates the Data Type of the Field.

---------------

If you want to view the details of a field

For example, desc user; is used to view the table structure.

Next, view the Host field of the user table.

Desc user Host;

The Host field is displayed.

Desc user Host;
+ ------- + ---------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
| Host | char (60) | NO | PRI |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
1 row in set (0.00 sec)

It cannot be blank. If the Key is PRI, it indicates that this is the primary Key.

======================

Some basic common commands are here. Of course, the exit command is exit.

In the future, we will introduce you to creating database tables, querying, deleting, and adding tables. Thank you for watching

After installing the mysql database, you will have a default local user root. The password will prompt you to enter a password during database installation, that is, your root password. Although other users can log on to the database, we can comment out the configuration file/etc/mysql/my after installing the database with less permissions. bind-address = 127.0.0.1 in cnf to enable mysql to log on remotely (and then restart)

How to change the user password in command line mode:

Mysqladmin-u root-p password "new password"; (however, when you change the password, you will be asked to enter your old password first)

Log on to the local mysql Database "mysql-u root-p" and press Enter. This will allow you to enter the password and enter the correct password to log on.

Connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

The command used after logging on to the mysql database: (Note that you must add a semicolon after writing the command) The command letters can be case-insensitive.

Display the name of the current user: select user ();
View All users: select user, host, password from mysql. user;
Show permissions of the current user: show grants for username @ localhost; (of course, the user must be a user already created in the database. If not, an error is displayed)
Create a new user: create user username identified by 'Password' (this user does not have any permissions or identity); you can also create a new user: grant select, insert, update, delete on *. * to test2 @ localhost identified by 'abc ';
Delete a new user: Delete FROM mysql. User Where user = 'test' and Host = 'localhost ';
Refresh the system permission table: flush privileges;
Select Database: use databasename; then you can operate the database;
Create database databasename;
Delete database: drop database databasename;
View table: show tables;
Alter table name: alter table tablename rename newtablename;
View the table structure: desc tablename; or show columns from tablename;
Delete A table: delete from tablename; or truncate tablename; delete deletes one of all records in the mysql table, while truncate retains the structure of the mysql table, after this table is re-created, all States are equivalent to new tables. Therefore, truncate is faster than delete and data cannot be recovered.

1) create a student table that contains the following information: Student ID, name, age, gender, home address, and contact number.

Create table student
(
Student ID int,
Name varchar (10 ),
Age int,
Gender varchar (4 ),
Home address varchar (50 ),
Contact number varchar (11)
); (Note that the English letters, numbers, and parentheses are written in English format)
2) modify the structure of the student table, add a column of Information, education level
Alter table student add column education level varchar (6 );
Alter table test modify gender char (10) -- modify the table column type
3) modify the structure of the student table, delete a column of information, home address
Alter table student drop column home address; // note that drop instead of delete is used here.
4) Add the following information to the student table:
Student ID name age gender contact phone number Education

1A22 male 123456 Primary School

2B21 male 119 Middle School

3C23 male 110 High School

4D18 female 114 University
Insert into student (student ID, name, age, gender, contact number, education) values (1, "A", 22, "male", "123456", "primary "); (When the inserted Chinese characters cannot be recognized, nchar () or nvarchar () can be used instead of unicode encoding)
Insert into student (student ID, name, age, gender, contact number, education) values (1, "B", 21, "male", "119", "Middle School ");
Insert into student (student ID, name, age, gender, contact number, education) values (1, "C", 23, "male", "123456", "High School ");
Insert into student (student ID, name, age, gender, contact number, education) values (1, "D", 23, "female", "114", "");
5) modify the data in the student table and change the educational background of the student whose phone number starts with 11 to "Junior College"
Update student set education level = "" where contact number like "11% ";
6) Delete the data in the student table, whose name starts with "C" and whose gender is "male ".
Delete from student where name like "C" and Gender = "male ";
7) query the data in the student table and display the names and student IDs of all students younger than 22 years old with a "junior college degree ".
Select name, student ID from student where age <22 and education level = "Junior College ";
8) query the data in the student table, query all information, and list the first 25% records.
Select top 25 percent * from student ;????
Select * from student limit 25% ;???? This is a problem. In SQL 2000, it should be select top 25 percent * from student;
9) query the names, gender, and age of all students in descending order.
Select name, gender, age from student order by age desc;
10) query all average ages by Gender Group
Select avg (AGE) as average age from student group by gender;
Select avg (AGE) from student group by gender;
Select avg (AGE) average age from student group by gender;
3) indicate the meanings of the following Aggregation numbers: avg, sum, max, min, count, count (*)

AVG: Average Value
SUM: SUM
MAX: calculates the maximum value.
MIN: Minimum value
COUNT (*): returns all rows.
COUNT returns the record value that meets the specified condition.