首頁 > 資料庫 > mysql教程 > MySQL常用操作及基礎知識

MySQL常用操作及基礎知識

爱喝马黛茶的安东尼
發布: 2019-08-31 17:36:51
轉載
3583 人瀏覽過

MySQL常用操作及基礎知識

1、啟動和關閉mysql伺服器:

service mysql start
service mysql stop
登入後複製

2、重新啟動MySQL服務:##

service mysql restart
登入後複製

3、確認是否啟動成功,mysql節點處於LISTEN狀態表示啟動成功:

sudo netstat -tap | grep mysql
登入後複製

MySQL常用操作及基礎知識

4、進入mysql shell介面:

mysql -u root -p
登入後複製

5、連接MYSQL:

格式: mysql -h主機位址-u使用者名稱-p使用者密碼(註:u與root可以不用加空格,其它也一樣)

1、連接到本機上的MYSQL

找到mysql的安裝目錄,一般可以直接鍵入指令mysql -uroot -p,回車後提示你輸密碼,如果剛安裝好MYSQL,超級使用者root是沒有密碼的,故直接回車即可進入到MYSQL中了.

2、連接到遠端主機上的MYSQL

假設遠端主機的IP為:10.0.0.1,使用者名稱為root,密碼為123。則鍵入以下指令:

mysql -h10.0.0.1 -uroot -p123
登入後複製

6、退出MYSQL指令:

exit (回车)
登入後複製

7、查詢mysql正在執行的程序:

show processlist;
登入後複製

8、檢視使用者:

use mysql;
select * from user;
登入後複製

9、新使用者:

CREATE USER  'user_name'@'host'  IDENTIFIED BY  'password';
登入後複製

user_name:要建立使用者的名字。

host:表示要這個新建立的使用者允許從哪台機登陸,如果只允許從本機登陸,則填'localhost' ,如果允許從遠端登陸,則填'%'

password:新建立使用者的登陸資料庫密碼,如果沒密碼可以不寫。

範例:

CREATE USER  ‘aaa’@‘localhost’ IDENTIFED BY ‘123456’; //表示创建的新用户,名为aaa,这个新用户密码为123456,
只允许本机登陆
CREATE USER  'bbb'@'%' IDENTIFED BY '123456';//表示新创建的用户,名为bbb,这个用户密码为123456,
可以从其他电脑远程登陆mysql所在服务器
CREATE USER  ‘ccc’@‘%’ ;//表示新创建的用户ccc,没有密码,可以从其他电脑远程登陆mysql服务器
登入後複製

10、授權使用者:

GRANT privileges ON  databasename.tablename  TO  ‘username’@‘host’
登入後複製

privileges:表示要授予什麼權力,例如可以有select , insert ,delete, update等,如果要授予全部權力,則填ALL

databasename.tablename:表示使用者的權限能用在哪個庫的哪個表中,如果想要使用者的權限很作用於所有的資料庫所有的表,則填* . *,*是一個通配符,表示全部。

’username‘@‘host’:表示授權給哪個使用者。

例如:

grant all on *.* to 'test'@'localhost'; #给test用户授权,让test用户能给所有库所有表实行所有的权力
GRANT  select,insert  ON  zje.zje  TO ‘aaa’@‘%’;//表示给用户aaa授权,让aaa能给zje库中的zje表 实行 insert 
和 select。
登入後複製

注意:

用以上指令授權的使用者不能給其他使用者授權,如果想這個使用者能夠給其他使用者授權,就要在後面加上WITH GRANT OPTION

如: GRANT ALL ON *.* TO 'aaa'@'%' WITH GRANT OPTION;

相關推薦:《

mysql教學

11、限制ip登入:

例如,設定mysql只有172.29.8.72和192.168.3.39可以連接上

GRANT ALL ON *.* TO 'username'@'172.29.8.72' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL  ON *.* TO 'username'@'192.168.3.39' IDENTIFIED BY 'password' WITH GRANT OPTION;
flush privileges;
登入後複製

12、刪除使用者:

指令:DROP USER 'user_name'@'host'

#範例:

drop user 'test'@'%';  #删除用户test
登入後複製

#13、顯示資料表模式:

use 資料庫名稱;

show columns from 表名;

14、mysql資料類型:

MySQL支援多種類型,大致可以分為三類:數值、日期/時間、字串(字元)類型。

MySQL支援所有標準SQL數值資料型別。作為SQL標準的擴展,MySQL也支援整數型別TINYINT、MEDIUMINT和BIGINT

#15、插入資料:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
登入後複製

如果資料是字元型,必須使用單引號或雙引號,如:「value」。

16、查詢資料:

   SELECT column_name,column_name
    FROM table_name
    [WHERE Clause]
    [LIMIT N][ OFFSET M]
登入後複製

查詢語句中你可以使用一個或多個表,表之間使用逗號(,)分割,並使用WHERE語句來設定查詢條件。

SELECT 指令可以讀取一筆或多筆記錄。

你可以使用星號(*)來取代其他字段,SELECT語句會返回表格的所有字段資料

你可以使用 WHERE 語句來包含任何條件。

你可以使用 LIMIT 屬性來設定傳回的記錄數。

你可以透過OFFSET指定SELECT語句開始查詢的資料偏移量。預設偏移量為0。

17、limit和offset用法

mysql里分頁一般用limit來實作

1、select* from article LIMIT 1,3

2、select * from article LIMIT 3 OFFSET 1

上面兩種寫法都表示取2,3,4三條條資料

當limit後面跟兩個參數的時候,第一個數字表示要跳過的數量,後一位表示要取的數量,例如

select* from article LIMIT 1,3 就是跳過1條資料,從第2條資料開始取,取3條數據,也就是取2,3,4三條數據

當limit後面跟一個參數的時候,該參數表示要取的數據的數量

例如select* from article LIMIT 3 表示直接取前三條數據,類似sqlserver裡的top語法。

當 limit和offset組合使用的時候,limit後面只能有一個參數,表示要取的的數量,offset表示要跳過的數量 。

例如select * from article LIMIT 3 OFFSET 1 表示跳過1條數據,從第2條數據開始取,取3條數據,也就是取2,3,4三條數據

#18、更新資料:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
登入後複製

你可以同時更新一個或多個欄位。

你可以在 WHERE 子句中指定任何条件。

你可以在一个单独表中同时更新数据。

19、模糊查询:

select c field1,field2,…fieldN
from table_name
where field1 like condition1 [and[or]] field2=‘somevalue’;
登入後複製

通配符的分类:

%百分号通配符: 表示任何字符出现任意次数 (可以是0次).

_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符。

20、排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
登入後複製

可以添加 WHERE…LIKE 子句来设置条件

21、分组

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
登入後複製

例子:

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
登入後複製

22、null值处理

MySQL 中处理 NULL 使用 IS NULL 、 IS NOT NULL 、<=>运算符。

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符(不同于=运算符),当比较的的两个值为 NULL 时返回 true。
登入後複製

23、in

in常用于where表达式中,其作用是查询某个范围内的数据。

用法:select * from table where field in (value1,value2,value3,…);

例子:

查询book表中id为2和4的所有数据:

select * from book where id in(2,4)
登入後複製

24、not in

not in与in作用相反,用法和示例如下:

用法:select * from where field not in (value1,value2,value3,…);

25、exists

本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:

SELECT title
FROM titles
WHERE EXISTS
    (SELECT *
    FROM publishers
    WHERE pub_id = titles.pub_id
    AND city LIKE &#39;B%&#39;)
登入後複製

用IN:

SELECT title
FROM titles
WHERE pub_id IN
    (SELECT pub_id
    FROM publishers
    WHERE city LIKE &#39;B%&#39;)
登入後複製

26、导入数据:

mysql 命令导入

使用 mysql 命令导入语法格式为:

mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)
登入後複製

实例:

# mysql -uroot -p123456 < runoob.sql
登入後複製

以上命令将将备份的整个数据库 runoob.sql 导入。

source 命令导入

source 命令导入数据库需要先登录到数库终端:

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库
登入後複製

使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ,将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE &#39;dump.txt&#39; INTO TABLE mytbl;
登入後複製

如果指定LOCAL关键词,则表明从客户主机上按路径读取文件。如果没有指定,则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记,但是默认标记是定位符和换行符。

使用 mysqlimport 导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令:

$ mysqlimport -u root -p --local mytbl dump.txt
password *****
登入後複製

27、导出数据:

使用SELECT…INTO OUTFILE语句来简单的导出数据到文本文件上。

#将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中: 
mysql> SELECT * FROM runoob_tbl    
    -> INTO OUTFILE &#39;/tmp/runoob.txt&#39;;
    
#通过命令选项来设置数据输出的指定格式,以下实例为导出 CSV 格式:
    mysql> SELECT * FROM passwd INTO OUTFILE &#39;/tmp/runoob.txt&#39;
    -> FIELDS TERMINATED BY &#39;,&#39; ENCLOSED BY &#39;"&#39;
    -> LINES TERMINATED BY &#39;\r\n&#39;;
#生成一个文件,各值用逗号隔开。这种格式可以被许多程序使用。
    SELECT a,b,a+b INTO OUTFILE &#39;/tmp/result.text&#39;
    FIELDS TERMINATED BY &#39;,&#39; OPTIONALLY ENCLOSED BY &#39;"&#39;
    LINES TERMINATED BY &#39;\n&#39;
    FROM test_table;
登入後複製

SELECT … INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE是SELECT … INTO

OUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT … INTO

OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。

SELECT…INTO OUTFILE

'file_name’形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。

输出不能是一个已存在的文件。防止文件数据被篡改。

你需要有一个登陆服务器的账号来检索文件。否则 SELECT … INTO OUTFILE 不会起任何作用。

导出表作为原始数据

mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本,其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录,该目标必须是可写的。

以下实例将数据表 runoob_tbl 导出到 /tmp 目录中:

$ mysqldump -u root -p --no-create-info 
–tab=/tmp RUNOOB runoob_tbl
password ******
登入後複製

导出 SQL 格式的数据

导出 SQL 格式的数据到指定文件,如下所示:

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******
登入後複製

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令,将数据备份至 dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****
登入後複製

如果完整备份数据库,则无需使用特定的表名称。

如果你需要将备份的数据库导入到MySQL服务器中,可以使用以下命令,使用以下命令你需要确认数据库已经创建:

$ mysql -u root -p database_name < dump.txt
password *****
登入後複製

你也可以使用以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的:

$ mysqldump -u root -p database_name 
| mysql -h other-host.com database_name
登入後複製

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

28、 MySQL 事务

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
事务用来管理 insert,update,delete 语句
登入後複製

一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。

MYSQL 事务处理主要有两种方法:

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
登入後複製

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
登入後複製

29、字符集设置:

Ubuntu下设置MySQL字符集为utf8

1.mysql配置文件地址

/etc/mysql/my.cnf
登入後複製

2.在[mysqld]在下方添加以下代码

[mysqld]
init_connect=&#39;SET collation_connection = utf8_unicode_ci&#39;
init_connect=&#39;SET NAMES utf8&#39;
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
登入後複製

3.重启mysql服务

sudo service mysql restart
登入後複製

4.检测字符集是否更新成utf8.

进入mysql,mysql -u root -p,输入show variables like '%character%' 查看字符集

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
登入後複製

注意事项:在修改字符集之前已经建立的数据库,character_set_database值不会发生改变,往数据库中插入中文数据仍然会显示乱码,所以最好在安装完MySQL后就将字符集改成utf8,否则后续修改会较麻烦。

字符集修改:

show character set; #查看当前MySQL服务实例支持的字符集、字符序以及字符集占用的最大字节长度等信息
登入後複製

如下图:

MySQL常用操作及基礎知識

show variables like 'character%';#查看当前MySQL会话使用的字符集

结果如图:

MySQL常用操作及基礎知識

character_set_client: 客户端来源数据使用的字符集

character_set_connection: 数据通信链路的字符集,当MySQL客户机向服务器发送请求时,数据以该字符集进行编码

character_set_database: 数据库字符集

character_set_filesystem: MySQL服务器文件系统的字符集,该值是固定的binary。

character_set_results: 结果集的字符集,MySQL服务器向MySQL客户机返回执行结果时,执行结果以该字符集进行编码

character_set_server: 内部操作字符集(MySQL服务实例字符集) character_set_system: 元数据(字段名、表名、数据库名等)的字符集默认为utf8

修改字符集:

set character_set_results=gbk;
登入後複製

如图:

MySQL常用操作及基礎知識

30、时间同步:

tzselect设置时区命令,根据选项选到中国的北京时间即可。

执行如下命令:

vi .bash_profile
登入後複製

在末尾加入这两行:

TZ=&#39;Asia/Shanghai&#39;
export TZ
登入後複製

然后运行命令刷新配置:

source .bash_profile
登入後複製

再次运行date命令发现时区已经成功修改:

hadoop@Master:~$ date -R
Tue, 30 Jul 2019 19:42:41 +0800
登入後複製

linux时区重新设置后,发现mysql插入的数据还是原来时区的时间,下面是重新设置mysql时区的方法:

进入mysql控制台运行如下指令查看mysql时间。

select CURTIME();
登入後複製

查询后发现和date命令查询出来的时间不一样,下面开始修改:

运行临时解决命令:

SET time_zone = &#39;+8:00&#39;;
登入後複製

刷新配置:

flush privileges;
登入後複製

修改配置文件使得下次重启mysql服务之后永久生效

vi /etc/my.cnf
登入後複製

添加如下配置:

[mysqld]
default_time_zone = &#39;+8:00&#39;
登入後複製

31、grep命令:

1.作用

Linux系统中grep命令是一种强大的文本搜索工具,它能使用正则表达式搜索文本,并把匹 配的行打印出来。grep全称是Global Regular Expression Print,表示全局正则表达式版本,它的使用权限是所有用户。

2.格式

grep [options]
登入後複製

简单实例:

$ grep ‘test’ d* #显示所有以d开头的文件中包含 test的行。
$ grep ‘test’ aa bb cc  #显示在aa,bb,cc文件中匹配test的行。
登入後複製

32、awk命令:

awk是一个强大的文本分析工具,相对于grep的查找,sed的编辑,awk在其对数据分析并生成报告时,显得尤为强大。简单来说awk就是把文件逐行的读入,以空格为默认分隔符将每行切片,切开的部分再进行各种分析处理。

使用方法 : awk '{pattern + action}' {filenames}

以上是MySQL常用操作及基礎知識的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:csdn.net
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板