Linux学习-mysql基本操作_linux mysql database changed-优快云博客

本文详细介绍在CentOS 6.10环境下MySQL的安装、配置及基本操作，包括数据库的创建与删除、表的管理、数据操作语言(DML)、数据定义语言(DDL)、数据控制语言(DCL)的应用等，并提供了具体的命令实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文基于centos6.10完成
RDBMS(Relational Database Management System)关系型数据库管理系统

数据库创建，删除
表的创建，修改，删除
索引的创建、删除
用户和权限
数据的增删改
数据的查询
DML(Data Manapulate Language)数据操作语言
INSERT,UPDATE,REPLACE,DELETE
DDL(Data Definition Language)数据定义语言
CREATE,ALTER,DROP
DCL(Data Control Language)数据控制语言
GRANT,REVOKE
SELECT
MySQL服务器的安装

#安装完mysql-server，客户端mysql也会安装
[root@lotus ~]# yum install -y mysql-server
...

启动服务器，初使化信息

#启动mysql服务器时，会对一些信息进行初使化
[root@lotus ~]# service mysqld start
Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h lotus.tye password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
#查看一下mysqld服务是否正常启动，mysqld服务监听在服务器的3306端口上
[root@lotus ~]# netstat -tnlp | grep mysql
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1744/mysqld

mysql命令

-u USERNAME 默认为root
-p PASSWORD 默认为空
-h HOSTNAME 默认为localhost

本地主机的连接方式：

linux:socket

/var/lib/mysql/mysql.sock

windows:内存

远程主机的连接方式：TCP
mysql用户格式：USERNAME@HOSTNAME
mysql客户端：

交互式模式
批处理模式【执行mysql脚本】
交互式模式中的命令类别

客户端命令

\q,quit 退出
\h 帮助
？获得帮助

服务器端命令

必须使用命令结束符，默认为分号【;】
show databases;显示数据库列表

数据类型：

字符

char(n)：存储固定字符，n为多少，则分配多少空间，最多256个字符
varchar(n)：存储可度字符，字符占用多少，则分配多少，最多65536
binary(n):存储固定长度的数据
varbinary(n):可变长度的数据
text(n):超大文本
blob(b):二进制大对象

数值

精确数值

整型
- TINYINT
- SMALLINT
- MEDIUMINT
- INT
- BIGINT
- 修饰符unsigned，无符号

十进制
- DECIMAL

近似数值

浮点型
- FLOAT
- DOUBLE

日期时间型
+ DATE
+ TIME
+ DATETIME
+ STAMP
布尔
+ BOOL
+ ENUM
创建数据库
CREATE DATABASE TESTDB;

#创建testdb数据库
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
#如果数据库不存在则创建
mysql> create database if not exists testdb;
Query OK, 1 row affected, 1 warning (0.00 sec)
#删除数据库
mysql> drop database testdb;
Query OK, 0 rows affected (0.00 sec)
#查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

创建表
CREATE TABLE tb_name(col1,col2,…);

#切换至testdb
mysql> use testdb;
Database changed
#创建数据库表
mysql> create table students(Name CHAR(20) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL);
Query OK, 0 rows affected (0.02 sec)
#查看数据库中的表
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| students         |
+------------------+
1 row in set (0.00 sec)
#查看表结构
mysql> desc students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#删除表
mysql> drop table if exists students;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

修改表
ALTER TABLE tb_name
MODIFY
CHANGE
ADD
DROP

#向表最后添加字段
mysql> alter table students add course varchar(100);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
| course | varchar(100)        | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#修改course为Course并将字段的顺序调整至Name后面
mysql> alter table students change course Course varchar(100) after Name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Course | varchar(100)        | YES  |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

插入信息
INSERT INTO tb_name (col1,col2,…) values(‘string’,number,…);
INSERT INTO tb_name (col1,col2,…) values(‘string’,number,…),(‘string’,number,…)

#向students表中插入3条记录
mysql> insert into students(Name,Gender)values('Edison','M'),('Dizzy','M'),('cc','F');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from students;
+--------+--------+------+--------+
| Name   | Course | Age  | Gender |
+--------+--------+------+--------+
| Edison | NULL   | NULL | M      |
| Dizzy  | NULL   | NULL | M      |
| cc     | NULL   | NULL | F      |
+--------+--------+------+--------+
3 rows in set (0.00 sec)
#插入数据不使用表字段
mysql> insert into students values('water','bxj',36,'M');
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+--------+--------+------+--------+
| Name   | Course | Age  | Gender |
+--------+--------+------+--------+
| Edison | NULL   | NULL | M      |
| Dizzy  | NULL   | NULL | M      |
| cc     | NULL   | NULL | F      |
| water  | bxj    |   36 | M      |
+--------+--------+------+--------+
4 rows in set (0.00 sec)

更新表数据信息
UPDATE tb_name SET column=value WHERE field=value

mysql> update students set Course='txws' where Name='cc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+--------+--------+------+--------+
| Name   | Course | Age  | Gender |
+--------+--------+------+--------+
| Edison | NULL   | NULL | M      |
| Dizzy  | NULL   | NULL | M      |
| cc     | txws   | NULL | F      |
| water  | bxj    |   36 | M      |
+--------+--------+------+--------+
4 rows in set (0.00 sec)

删除表信息
DELETE FROM tb_name 【where field=value】如没有where条件则删除所有行
选择：
SELECT 字段 FROM tb_name WHERE CONDITION
*：表示所有字体
WHERE:没有条件显示所有行
创建、删除用户
create user ‘username’@‘hostname’ identified by ‘password’;
drop user ‘username’@‘hostname’;
在mysql中jerry@localhost,jerry@192.168.88.135不是同一用户
HOSTNAME:
IP
HOSTNAME
NETWORK
通配符
:匹配任意单个字符,192.168.88.
%:匹配任意字符
注：使用通配符必须使用引号
授权、解除授权
GRANT pri1,pri2,… ON DB_NAME.TB_NAME TO ‘username’@‘hostname’ [IDENTIFY BY ‘password’];
授权命令如果用户不存在，则会自动创建用户并授权
REVOKE pri1,pri2,… ON DB_NAME.TB_NAME TO ‘username’@‘hostname’;

#创建用户
mysql> create user 'dizzy'@'%' identified by 'dizzy';
Query OK, 0 rows affected (0.00 sec)
#查看用户情况
mysql> select Host,User,Password from mysql.user;
+-----------+-------+-------------------------------------------+
| Host      | User  | Password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| lotus.tye | root  |                                           |
| 127.0.0.1 | root  |                                           |
| localhost |       |                                           |
| lotus.tye |       |                                           |
| %         | dizzy | *9C1DFBDE443A0C7E96767F5A39CC60E429ADABC4 |
+-----------+-------+-------------------------------------------+
6 rows in set (0.00 sec)
#查看用户授权信息
mysql> show grants for dizzy;
+------------------------------------------------------------------------------------------------------+
| Grants for dizzy@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dizzy'@'%' IDENTIFIED BY PASSWORD '*9C1DFBDE443A0C7E96767F5A39CC60E429ADABC4' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#对dizzy@'%'授于mydb的所有权限
mysql> grant all privileges on mydb.* to 'dizzy'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'dizzy';
+------------------------------------------------------------------------------------------------------+
| Grants for dizzy@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dizzy'@'%' IDENTIFIED BY PASSWORD '*9C1DFBDE443A0C7E96767F5A39CC60E429ADABC4' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'dizzy'@'%'                                                      |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
#授权后，通过连接
[root@lotus ~]# mysql -udizzy -pdizzy -h192.168.88.135
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

为MySQL用户设定密码

mysql>SET PASSWORD FOR ‘USERNAME’@‘HOSTNAME’=password(‘password’)

mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected (0.00 sec)
#修改完成后，让mysql重读授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

在命令提示符下输入：mysqladmin -uroot -poldpassword-hHost password ‘password’

[root@lotus ~]# mysqladmin -uroot -p -h127.0.0.1 password '123456';
Enter password:

直接修改mysql.user表中内容
mysql> UPDATE user set Password=PASSWORD(‘password’) WHERE user=‘root’ and host=‘127.0.0.1’

mysql> update mysql.user set password=password('123456') where User='root' and Host='127.0.0.1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select User,Host,Password from mysql.user;
+----------+-----------+-------------------------------------------+
| User     | Host      | Password                                  |
+----------+-----------+-------------------------------------------+
| root     | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root     | lotus.tye |                                           |
| root     | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|          | localhost |                                           |
|          | lotus.tye |                                           |
| dizzy    | %         | *9C1DFBDE443A0C7E96767F5A39CC60E429ADABC4 |
| jerry    | %         |                                           |
| testuser | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------+-----------+-------------------------------------------+
8 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)