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.
mysql -uroot -p /*如果刚安装好MySQL，root是没有密码的*/
mysql> mysql -h192.168.206.100 -uroot -p12345678; /*u与root可以不加空格*/
mysql> exit/quit;
mysql> mysqladmin -uroot newpassword; -- 因为开始时root没有密码，所以-p旧密码 可以省略2. Use the sqladmin command to change the password
mysql> mysqladmin -uroot -p123456 password 12345678;
mysql> set password for 用户名@localhost = password('新密码');
4. If you forget the root password:
mysqld --skip-grant-tables mysql> use mysql; --连接权限数据库 mysql> update user set password=password("新密码") where user="root"; -- 改密码 mysql> flush privileges; -- 刷新权限 mysql> quit； -- 退出mysql1、创建数据库（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）
/*第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：*/ 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)
##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
Format:select [all|distinct] [top n[percent]]
##
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*/
are implemented through the where clause, which must follow the From clause.
The format is:select [all|distinct] [top n[percent]]
：: 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
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
#
The format of the like clause:select * from tb_name where
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)
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
(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
##(4) Sort the query results
功能: Find the specified numerical expression formula The sum or average of.
Template:select avg(
Function: Find the maximum or minimum value of the specified expression.Template:
select max(
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(
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:
Format:update tb_name set column name = new value where column name = a certain value;
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 '学号';
mysql> alter table employee add index emp_name (name);
mysql> alter table employee add primary key(id);
mysql> alter table employee add unique emp_name2(cardnumber);
mysql>alter table employee drop index emp_name;
mysql> ALTER TABLE table_name ADD field_name field_type;
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
MySQL ALTER TABLE table_name DROP field_name;
学号 Sno |
姓名 Sname |
性别 Ssex |
年龄 Sage |
所在系 Sdept |
|---|---|---|---|---|
| 20180001 | 张三 | 男 | 20 | CS |
| 20180002 | 李四 | 男 | 19 | CS |
| 20180003 | 王五 | 男 | 18 | MA |
| 20180004 | 赵六 | 男 | 20 | IS |
课程号 Cno |
课程名 Cname |
先修课 Cpno |
学分 Ccredit |
|---|---|---|---|
| 1 | 数据库 | 5 | 4 |
| 2 | 数学 | 2 | |
| 3 | 信息系统 | 1 | 4 |
| 4 | 操作系统 | 6 | 3 |
| 5 | 数据结构 | 7 | 4 |
| 6 | 数据处理 | 2 | |
| 7 | PASCAL语言 | 6 | 4 |
学号 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) );
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*/ );
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!