Detailed introduction to MySQL basic common commands

This article brings you a summary of the basic commands of MySQL. In addition to how to connect to the database and change the password, there are also some commonly used commands. I hope it will be helpful to you.

1. Connect to MySQL

##Format: mysql -h host address -u username -p user password

1. Connect to MySQ on this machine:

First open the cmd window, enter mysql -uroot -p, and then enter the MySQL console with a space. The MySQL prompt is: mysql>.

mysql -uroot -p /*如果刚安装好MySQL，root是没有密码的*/

##2 , Connect to MySQL on the remote host:

## Assume that the IP of the remote host is: 192.168.206.100, and the user name is root, the password is 12345678.

mysql> mysql -h192.168.206.100 -uroot -p12345678; /*u与root可以不加空格*/

3. Exit the MySQL command: exit or quit (Enter):

mysql> exit/quit;

2. MySQL--Change Password

#Format:

mysqladmin -u Username -p old password password new password 1,

When the root password is empty, set the root password:

mysql> mysqladmin -uroot newpassword;   -- 因为开始时root没有密码，所以-p旧密码 可以省略

2. Use the sqladmin command to change the password

:

mysql> mysqladmin -uroot -p123456 password 12345678;

3. Use the set password command to change the password:

mysql> set password for 用户名@localhost = password('新密码');

4. If you forget the root password:

      ①关闭正在运行的MySQL服务。
      ②打开DOS窗口，转到mysql\bin目录。
      ③输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
      ④再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），输入mysql回车，如果成功，将出现MySQL提示符 >。
      ⑤连接权限数据库： use mysql; 。
      ⑥改密码：update user set password=password("新密码") where user="root";（别忘了最后加分号） 。
      ⑦刷新权限（必须步骤）：flush privileges;　。
      ⑧退出mysql  quit；。

mysqld --skip-grant-tables   
mysql> use mysql;   --连接权限数据库
mysql> update user set password=password("新密码") where user="root";   -- 改密码
mysql> flush privileges;    -- 刷新权限
mysql> quit；    -- 退出mysql

三、MySQL--常用命令

1、创建数据库（Create）

mysql> create database db_name;  -- 创建数据库
mysql> show databases;           -- 显示所有的数据库
mysql> drop database db_name;    -- 删除数据库
mysql> use db_name;              -- 选择数据库
mysql> create table tb_name (字段名 varchar(20), 字段名 char(1));   -- 创建数据表模板
mysql> show tables;              -- 显示数据表
mysql> desc tb_name；            -- 显示表结构
mysql> drop table tb_name；      -- 删除表

    例如：

/*创建学生表*/
create table Student(
     Sno char(10) primary key,
     Sname char(20) unique,
     Ssex char(2),
     Sage smallint,
     Sdept char(20)
)；

2、插入数据（Insert）

     insert into 语句用于向表格中插入新的行：

/*第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：*/
mysql> insert into tb_name values (value1,value2,value3,...);
/*第二种形式需要指定列名及被插入的值：*/
mysql> insert into tb_name (column1,column2,column3,...) values (value1,value2,value3,...);

     例如：

/*插入数据*/

mysql> insert into Student values ( 20180001,张三,男,20,CS）;

mysql> insert into Student values ( 20180002,李四,男,19,CS）;

mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180003,王五,男,18,MA）;

mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180004,赵六,男,20,IS）;

3、查询数据(Select)

In addition to viewing information about tables and views in the database, the select statement can also view SQL Server system information, copy, and create data tables. Its query function is powerful and it is the soul statement of the SQL language and the most frequently used statement in SQL.
Basic select statement:
## A basic select statement can be decomposed It is divided into three parts: what data to search for (select), where to search from (from), and what are the search conditions (where). The general format of the select statement is as follows:
#                                                                                                                                                                                                                                                                                                                                                                                            

From table name or view name

[where & lt; condition & gt;]
[Group by & lt; group expression & gt & gt by [ASC|DESC]]




(1) Query the specified column

##           

1. Query all columns in the table

## When the * sign is used in the position of the column specified in the select statement, it means all columns of the query table.

Template: select * from
tb_name；

##          2. Query the specified column in the table

When querying multiple columns, column names must be separated by commas.
Template: select tb_name., ... from tb_name;

#                                                            . Specifying column headers in query results## You can make outputting results easier by specifying column headers (also called column aliases) Be understood.

## When specifying a column title, you can use the AS clause after the column name; you can also use: column alias = The form specifies column headers.
The format of the AS clause is: column name or calculation expression [AS] column title
##                             Template : select as column header 1, as column header 2, as column header 3 from bt_name;

#4. Query the calculated column (that is, the value of the expression)

When you use select to query a column, you can not only use the original value of the column directly as the result, but also use the calculated value of the column value as the query result, that is, the select clause can query the value of the expression. ,Expressions can consist of column names, constants, and arithmetic operators.

##                   The calculated column of the query result shows "no column name", generally you need to add a column title to the calculated column.
# This: Among the expressions that can be used in the expression include: plus, reduction-, multiplied*, except/, and extra%
##

## Template：select , , column title = * n from tb_name;

(2) Select rows: Select some or all rows in the table as the result of the query

Format: select [all|distinct] [top n[percent]] from table name

#           1. Eliminate query results Duplicate rows in

## For relational databases, each row in the table must be different (that is, there are no duplicate rows) . However, if you select only certain columns when querying a table, duplicate rows may appear in the query results.
## Use the distinct keyword in the select statement to eliminate duplicate rows in the result set,
Template: ##select distinct [,,...] from tb_name ；

#                                                                Number of rows returned

## Use the top option to limit the number of rows returned in the query results, that is, return a specified number of records.

# This is: n is a positive integer, which means that the front n line of the query results set is returned; if there is a percent keyword, it means that the front n%line of the result set is returned.

##                                                                                            
Template：
celect top n from tb_name; /*Query Top n data*/          Template：celect top n percent from tb_name; /* Query the data of the first n% tb_name*/

(3) Query the rows that meet the conditions: Use the where clause to implement conditional query

                                                                                                                                                                                                                              are implemented through the where clause, which must follow the From clause.
The format is: select [all|distinct] [top n[percent]] from table name where & lt; Conditions & gt;;
：: In the query conditions, you can use the following transport or expression:
#                 Operator identification
                                                                        ‐ ‐ ‐ ‐ ‐ ‐‐ ‐‐‐‐‐‐‐‐‐‐‐ ,! & Lt;
... ... ... ... ... ... ... ... b ... ... ... #                                                                                                                           use use with               through out through out through out through out through out through out through ‐ through ‐ ‐ ‐ ‐ ,                                   to                                                                                   to  

# 1. Use comparison operators:

# Template：
select * from tb_name

where >= n ；

2. Specifying range:

## There are two keywords for specified range One: between...and and not between...and.
## The format is: select * from tb_name where [not] between and ;
                                                                                                                                                                                                                                                                to be to a higher value.
## Used to find rows whose field values ​​are within (or are not in) the specified range.

       

3. Use enumeration:

Use the in keyword to specify a set of values. All possible values ​​are listed in the set. When the value of the expression matches any element in the set, true is returned, otherwise false is returned. Template
：select * from tb_name where [not] in (value 1, value 2,..., Value n);
#              

4. Use wildcards for fuzzy query:

You can use the like clause to perform fuzzy matching queries on strings. The like clause will return a logical value (true or False).

              The format of the like clause: select * from tb_name where   [not] like ;
It means: Find records whose specified field value matches the matching string. The match string usually contains wildcard characters % and _ (underscore).
                                                                                                                                                                        ’ 　 　 　 　 　 　 　 　　　　　　　　　　　　　　　　　　　　　　　　    because %: represents a string of any length (including 0)

       

5. Query using null

                                                                                                                                                                    ## Use the is null keyword.

When not is not used, if the value of the expression is null, true is returned, otherwise false is returned; when not is used, the result is just the opposite.
Template：
select * from tb_name where is [not] null;

# 6. Multiple condition query: Use logic operator

(And: both conditions must be met) and or (or: one of the conditions is met) can be used to join multiple query conditions. and has a higher priority than or, but the priority can be changed if parentheses are used.
                 Template
：
select * from tb_name where = 'volues' and > n;

##(4) Sort the query results

######## ####### The order by clause can be used to arrange the query results in ascending order (ASC) or descending order (DESC) according to the value of one or more fields (or the value of an expression). The default is ascending order. ######### Format: ######order by {sort expression[ASC|DESC]}[,...n]; ######### Where: Sorting expression The formula can be a single field, an expression composed of fields, functions, constants, etc., or a positive integer. ######### Template: ######select * from tb_name ######order by###### ;# ####################(5) Use statistical functions: also known as set functions, aggregate functions################ ## When retrieving a table, it is often necessary to calculate or count the results. T-SQL provides some statistical functions (also called set functions or aggregate functions) to enhance the retrieval function. Statistical functions are used to calculate data in a table. These functions are used to calculate a set of data and return a single value. ######### ############### 函 ### ####### 功能 ####### ## AVG to find the average ########## counter, find the number of records, return to the INT type integer ######### Max Seek the maximum value ########## MIN Value######### sum sum##############

## 1. Sum and AVG

功能: Find the specified numerical expression formula The sum or average of.
Template: select avg() as average,sum() as total number from tb_name where ='string';

2. Max and Min

Function: Find the maximum or minimum value of the specified expression. Template:
select max() as maximum value, min() as minimum value from tb_name;

                     

3. count

This function has two formats: count(*) and count([all]|[distinct] field name). To avoid errors, count(*) is generally used to query the number of records. If a field has several values, use count(distinct field name).
(1) .count (*): ## Series: The total number of statistical records.
                                                                                                   ).count([all]|[distinct] field name)
Function: Count the number of records in which the specified field value is not empty. The data type of the field can be text, image, ntext, uniqueidentifier any type other than . Template: select count() as total number
from tb_name;

(6) Group the query results## The group by clause is used to group the query result table by a certain column or multiple Column values ​​are grouped, and those with equal column values ​​are grouped into one group, and one result is calculated for each group. This clause is often used together with statistical functions to perform group statistics.
The format is:

group by group field[,...n][having ];

# 1. After using Group By Sentence
## Select list can only include: Group The grouping fields and statistical functions specified in the by clause.

2. The usage of the use of a sentence of Having ## Haveing ​​Celestial Celestial Celestial Clauses are used together to filter grouped results (filtering conditions often contain statistical functions).
##                                                                                                                                                                                   not not not not not been the statistical function available? Clause; if the condition contains a statistical function, only the having clause can be used.
## Template: select ,count(*) as column header from tb_name where < Character field>='String' group by ;
##4. Modify data (Update) The Update statement is used to modify the data in the table.

Format: update tb_name set column name = new value where column name = a certain value;

5. Delete data(Delete)

##Delete a single line
## Format: delete from tb_name where column name = a certain value;
Delete all lines
## This can not delete the table without deleting Delete all lines below. This means that the structure, attributes and indexes of the table are complete:
## Format: delete * from tb_name or delete from tb_name;

##4. MySQL -- alter command

The alter add

command is used to add fields to the table. alter add
Command format: alter table Table name addField Type Other;

# For example, a field passtest
## is added to the table MyClass #, type is int(4), default value is 0:

mysql> alter table MyClass add passtest int(4) default '0';

 添加两个字段  

mysql> alter table Person add age int,add address varchar(11);

 删除两个字段

mysql> alter table Person drop column age,drop column address;

 修改字段的注释

mysql> alter table `student` modify column `id` comment '学号';

 1) 加索引
      mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

 mysql> alter table employee add index emp_name (name);

 2) 加主关键字的索引
      mysql> alter table 表名 add primary key (字段名);

mysql> alter table employee add primary key(id);

 3) 加唯一限制条件的索引
      mysql> alter table 表名 add unique 索引名 (字段名);

 mysql> alter table employee add unique emp_name2(cardnumber);

 4) 删除某个索引
      mysql> alter table 表名 drop index 索引名;

mysql>alter table employee drop index emp_name;

 5) 增加字段

mysql> ALTER TABLE table_name ADD field_name field_type;

 6) 修改原字段名称及类型

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

 7) 删除字段

MySQL ALTER TABLE table_name DROP field_name;

五、MySQL -- 应用

学生-课程数据库

 学生表：Student（Sno，Sname，Ssex，Sage，Sdept）
 课程表：Course（Cno，Cname，Cpno，Ccredit）
 学生选课表：SC（Sno，Cno，Grade）
 关系的主码加下划线表示。各个表中的数据示例如图所示：

Student表

学号 Sno	姓名 Sname	性别 Ssex	年龄 Sage	所在系 Sdept
20180001	张三	男	20	CS
20180002	李四	男	19	CS
20180003	王五	男	18	MA
20180004	赵六	男	20	IS

Course表

课程号 Cno	课程名 Cname	先修课 Cpno	学分 Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

SC表

学号 Sno	课程号 Cno	成绩 Grade
20180001	1	92
20180001	2	85
20180001	3	88
20180002	2	90
20180002	3	80

 建立一个“学生”表Student：

create table Student(
  Sno char(9) peimary key, /*列级完整性约束条件，Sno是主码*/
  Sname char(20) unique, /* Sname取唯一值*/
  Ssex char(2),
  Sage smallint,
  Sdept char(20)
);

 建立一个“课程”表Course：

create table Course(
  Sno char(4) primary key, /*列级完整性约束条件，Cname不能取空值*/
  Sname char(40) not null, /*Cpno的含义是先修课*/
  Cpno char(4)
  Ccredit smallint,
  foreign key (Cpnoo) references Course(Cno) /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);

 建立学生选课表SC：

create table SC(
  Sno char(9),
  Cno char(4),
  Grade smallint,
  frimary key (Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
  foreign key (Sno) references Student(Sno), /*表级完整性约束条件，Sno是外码，被参照表是Student*/
  foreign key (Cno) references Course(Cno)   /*表级完整性约束条件，Cno是外码，被参照表是Course */
);

推荐学习：mysql视频教程

The above is the detailed content of Detailed introduction to MySQL basic common commands. For more information, please follow other related articles on the PHP Chinese website!