MySQL学习

发表于 2017-11-16 更新于 2020-04-16 分类于数据库阅读次数：本文字数： 4.5k 阅读时长 ≈ 4 分钟

记录常用的功能备查

mac 使用

安装完毕后再系统设置里面能看到 mysql 入口

新建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

CREATE USER 'demo'@'%' IDENTIFIED BY '1qaz';

username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

授权

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*

GRANT privileges ON databasename.tablename TO 'username'@'host'
GRANT ALL ON *.* TO 'demo'@'%';

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

删除用户

DROP USER 'username'@'host';

命令行登陆退出

quit
mysql -u 用户名 -p
mysql -h 127.0.0.1 -u root -p

登录错误ERROR 1045 (28000)

第一次安装时root用户没有密码，会出现这个问题。使用mysqladmin -u root password设置一下就可以了。

SQL执行顺序

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

查询去重

利用 group by

获取信息

获取版本

select version();

查看所有用户信息

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

查看某个具体用户的权限
show grants for 'root'@'localhost';

查看数据库相关信息

选择数据库
use xx;

查看显示所有数据库
show databases;

查看当前使用的数据库
select database();

查看数据库使用端口
show variables  like 'port';

查看当前数据库大小
use  information_schema
select concat(round(sum(data_length)/(1024*1024),2) + round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' from tables where table_schema='gisDb';

查看数据所占的空间大小
use information_schema;
select concat(round(sum(data_length)/(1024*1024),2),'MB') as 'DB Size' from tables where table_schema='gisDb';

查看索引所占的空间大小
select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' from tables where table_schema='gisDb';

查看数据库编码
show variables like 'character%';

character_set_client      为客户端编码方式；
character_set_connection  为建立连接使用的编码；
character_set_database    为数据库的编码；
character_set_results     为结果集的编码；
character_set_server      为数据库服务器的编码；
只要保证以上采用的编码方式一样，就不会出现乱码问题。

查看数据库的表信息
show tables;

查看数据库的最大连接数
show variables like '%max_connections%';

查看数据库当前连接数，并发数。
show status like 'Threads%';

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

查看数据文件存放路径
show variables like '%datadir%';

设置信息

修改数据库编码
SET character_set_client = utf8;  
SET character_set_connection = utf8;  
SET character_set_database = utf8;  
SET character_set_results = utf8;  
SET character_set_server = utf8;

修改数据库密码

/etc/init.d/mysql stop   (service mysqld stop )
/usr/bin/mysqld_safe --skip-grant-tables
另外开个SSH连接
[root@localhost ~]# mysql
mysql>use mysql
mysql>update user set password=password("123456") where user="root";
mysql>flush privileges;
mysql>exit

最后一条记录

select * from table order by id DESC limit 1

删除数据库

mysqladmin -u root -p drop gisDb

导入、导出数据

导出数据和表结构：
导出数据，回车后会提示输入密码，命令行中不要带密码
mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

mysqldump -uroot -p abc > abc.sql

只导出表结构
mysqldump -uroot -p -d abc > abc.sql

导入数据库：

首先建空数据库
create database abc;
退出

mysql -u用户名 -p密码 数据库名 < 数据库名.sql
#mysql -u abc_f -p abc < abc.sql

建议使用第二种方法导入。
注意：有命令行模式，有sql命令

查询数据

不重复查询

select distinct 字段 from 表;

查询后n行记录
select * from table order by id desc limit n；//倒序排序（desc）

查询一条记录($id)的下一条记录
select * from table where id>$id order by id asc limit 1

查询一条记录($id)的上一条记录
select * from table where id<$id order by id desc limit 1

结果集排序

asc 升序排列，从小到大（默认）
desc 降序排列，从大到小

多字段排序
select 字段 from 表 order by 字段1 排序关键词,… …字段n desc|asc;

多个表查询
select d.f_id,d.f_sn,d.f_nickName,d.f_registerDate,d.f_lastActiveDate,d.f_comment, g.f_label
from t_devices as d, t_devGroups as dg,t_groups as g where dg.f_deviceId = d.f_id and g.f_id=dg.f_groupId

触发器

NEW 和 OLD

在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

NEW.columnName （columnName为相应数据表某一列名）
OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

执行顺序

如果 BEFORE 触发器执行失败，SQL 无法正确执行。
SQL 执行失败时，AFTER 型触发器不会触发。
AFTER 类型的触发器执行失败，SQL 会回滚。

procedure vs function

初步看是function可以在sql语句中调用，但是有一些使用上的限制。
procedure没有使用限制，但是不能再sql语句中调用

SELECT get_foo(myColumn) FROM  mytable

如果get_foo是procedure那么是非法的。如果是function那就是合法的。

MySql中delimiter的作用是什么?

用来防止输入多条语句时带有;导致语句在客户端执行的问题。