本文主要给大家介绍MySQL的安装方式及基本命令,文章内容都是笔者用心摘选和编辑的,具有一定的针对性,对大家的参考意义还是比较大的,下面跟笔者一起了解下MySQL的安装方式及基本命令吧。
Mysql安装方式
MySQL安装方式有三种,一种是RPM方式安装,一种是源码编译安装,一种是通用二进制格式包安装。
RPM方式安装,可以直接使用yum命令安装或者下载RPM包后再安装
yum安装命令:
yum -y install mysql-server
系统会自动解决依赖关系,并将mysql客户端也安装上。
mysql交互模式中的命令类别:
客户端命令:在客户端执行的命令
云服务器命令:在云服务器上执行,并将结果返回给客户端。必须使用语句结束符,默认为封号“;”
MySQL数据库:
mysql安装完成后,默认有3个数据库
information_schema:是mysql运行过程中位于内存中的信息,保存mysql运行时数据,只有mysql启动时才有数据,平时是空的。
test:测试时才用到的数据库
mysql:mysql 的数据库
MySQL数据库目录:
[root@host2 ~]# ls /var/lib/mysql/
ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test
可以看到没有information_schema数据库,因为它是存在内存中的。
MySQL常用命令:
DDL:定义数据库对象:
CREATE:
ALTER:
DROP:
DML:数据操纵语言
INSERT
UPDATE
DELETE
DCL数据控制语言
GRANT:
REVOKE:
创建数据库:
CREATE DATABASE db_name;
CREATE DATABASES IF NOT EXISTS testdb;
mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| test_db |
+--------------------+
4 rows in set (0.01 sec)
删除数据库:
DROP DATABASE db_name;
mysql> DROP DATABASE test_db;
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,...) col1,col2为字段名
注意:表是依赖于数据库的,所以在创建表之前,一定要指定默认数据库,使用命令
USER DATABASE; 来指定数据库
创建一个表,名为student,包含三个字段,Name、Age、Gender
mysql> CREATE TABLE students(Name CHAR(20) NOT NULL, Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| students |
+-------------------+
1 row in set (0.00 sec)
查看某个数据库中的表:
SHOW TABLES FROM db_name;
查看表结构:
DESC table_name;
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)
删除表:
DROP TABLE tb_name;
修改表:
ALTER TABLE tb_name;
MODIFY:修改某个字段,修改字段属性,字段名不改
CHANGE:改变某个字段,改变字段名称
ADD:添加字段
DROP:删除字段
可以通过help命令查询帮助信息:
help CREATE TABLE;
增加一个字段
例如:修改前面创建的student表,增加一个字段课程course,
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
mysql> ALTER TABLE students CHANGE course 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)
删除一个字段:
DROP col_name;
插入数据:
INSERT INTO tb_name (col1,col2,...) VALUES|VALUE ('STRING',NUM...);
上面命令意思是:往某个表中插入数据,字段名为col1,col2,... 插入的值为字符串则,用引号引起来,如果为数值,则直接用数字。如果每一个字段都给值,则不用写字段名称
例如:在students表中插入两条数据,张三和李四
mysql> INSERT INTO students (Name,Gender) VALUE ('ZhangSan','M'),('LiSi','F');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
查看数据
mysql> SELECT * FROM students;
+----------+------+--------+--------+
| Name | Age | Gender | Course |
+----------+------+--------+--------+
| ZhangSan | NULL | M | NULL |
| LiSi | NULL | F | NULL |
+----------+------+--------+--------+
2 rows in set (0.01 sec)
插入一个用户,所有字段都有值:
注意,所有字段都有值,就不需要指定字段名
mysql> INSERT INTO students VALUES ('ZengChengpeng',28,'M','IT');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students WHERE Name='ZengChengpeng';
+---------------+------+--------+--------+
| Name | Age | Gender | Course |
+---------------+------+--------+--------+
| ZengChengpeng | 28 | M | IT |
+---------------+------+--------+--------+
1 row in set (0.00 sec)
修改数据:
UPDATE tb_name SET column=value WHERE 条件
例如:将ZengChengpeng的Course课程名称改为Develop
mysql> UPDATE students SET Course='Develop' WHERE Name='ZengChengpeng';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM students WHERE Name='ZengChengpeng';
+---------------+------+--------+---------+
| Name | Age | Gender | Course |
+---------------+------+--------+---------+
| ZengChengpeng | 28 | M | Develop |
+---------------+------+--------+---------+
1 row in set (0.00 sec)
SELETE语句:
selete语句分为两种情况,
选择:指定以某字段作为搜索码,做逻辑比较,筛选符合条件的行。WHERE指定选择条件
投影:指定以某字段作为搜索码,做逻辑比较,筛选符合条件的字段。
mysql> SELECT Name,Course FROM students WHERE Gender='M';
+---------------+---------+
| Name | Course |
+---------------+---------+
| ZhangSan | NULL |
| ZengChengpeng | Develop |
+---------------+---------+
删除数据:
DELECT FROM tb_name WHERE 条件;
创建用户:
CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';
删除用户:
DROP USER 'USERNAME'@'HOST';
HOST:
IP:
HOSTNAME:
NETWORK:
通配符: 通配符用引号引起来
_:下划线匹配任意单个字符:例如172.16.0._
%:匹配任意字符:
jerry@'%'
用户权限:
添加权限:
GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD'];
pri1 pri2表示权限名称,所有权限用ALL PRIVILEGES表示
取消权限:
REVOKE pri1,pri2,... ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST';
创建用户示例:
mysql> CREATE USER 'jerry'@'%' IDENTIFIED BY 'jerry';
查看用户授权:
SHOW GRANTS FOR 'USERNAME'@'HOST';
mysql> SHOW GRANTS FOR jerry@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
+------------------------------------------------------------------------------------------------------+
示例:给jerry用户test_db这个数据库所有表的所有权限
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'jerry'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'jerry'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'jerry'@'%' |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
取消所有权限:
mysql> REVOKE ALL PRIVILEGES ON test_db.* FROM jerry@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'jerry'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
看完以上关于MySQL的安装方式及基本命令,很多读者朋友肯定多少有一定的了解,如需获取更多的行业知识信息 ,可以持续关注我们的数据库栏目的。