MYSQL日常操作

1、MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM

2、InnoDB用于事务处理(默认)，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

 

安装完mysql第一次登陆账号为root,密码为空

mysql –u root

[root@c1 ~]# mysqladmin -u root password  qwer1234  #设置密码

[root@c1 ~]# mysqladmin -uroot -pqwer1234 password qwert6789     #修改密码
[root@c1 ~]# mysql -uroot –pqwert12345                  #密码登陆

MariaDB [mysql]> create database auth;                #创建库
MariaDB [(none)]> show databases;                        #查看有哪些库

MariaDB [(none)]> use mysql;                                  #进入库

MariaDB [mysql]> show tables;                               #查看有哪些表

MariaDB [mysql]> describe user;                            #查看表结构

创建表

MariaDB [mysql]> use auth;

create  tables  表名 （字段1名称 类型，字段2名称 类型，…..,primary key (主键名)）                  #创建表

如

MariaDB [auth]> create  table users (user_name char(16) not null, user_passwd char(48) default '', primary key (user_name));

mysql> create table user(
    -> id int unsigned not null auto_increment primary key,
    -> user_name varchar(20) not null,
    -> password char(32) not null,
    -> email varchar(50) not null,
    -> mobile char(11) not null,
    -> fee decimal(10,2) default 0.00 not null,
    -> age tinyint unsigned not null
    -> );

插入数据记录

insert into  表名 （字段1，字段2，……） values (字段1的值，字段2的值，…….)    #插入数据记录

MariaDB [auth]> insert into users(user_name,user_passwd) values('zhangsan', password ('123456'));

如果记录完整包括表中所有字段的值，则插入语句中指定字段的部分可以省略

MariaDB [auth]> insert into users  values('lisi',password('654321'));

查询数据记录

select 列名1，列名2，…… from 表名  where  条件表达式

MariaDB [auth]> select * from auth.users;

MariaDB [auth]> select user_name,user_passwd from auth.users where user_name='zhangsan';

mysql> truncate user;                      #清空表

修改数据记录

update 表名 set  字段1=字段值1[，字段2=字段值2]  where 条件表达式

如

MariaDB [auth]> update auth.users set user_passwd=password('') where user_name='li';

在如，修改连接mysql root的连接密码

MariaDB [auth]> update  mysql.user set password=password('123456') where user='root';

MariaDB [auth]> flush privileges;                            #刷新权限

因为这个改密码和上面的定义结构不一样，所以是password不是user_passwd
image

删除数据记录

delete from 表名 where  条件表达式

如

MariaDB [(none)]> delete from auth.users where user_name='li';         #删除用户

在如，删除默认用户表user里的用户名密码都为空的记录

MariaDB [(none)]> select user,host,password from mysql.user where user='';

MariaDB [(none)]> delete from auth.users where user='';        

授予权限

gant  权限列表 on 库名.表名  to 用户名@来源地址 [  identified by  ‘密码’]

权限列表 : select insert uptate   使用all表示所有权

库名.表名：指定授权的库和表的名称，使用通配符’*‘

用户名@来源地址：指定谁能连接，能从哪里连接

identified by: 用于设置用户连接数据库时所使用的密码字符串，在新建用户时，若省略’identified by‘部分，则用户的密码将为空

授权给root所有权限,包括远程权限

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'qwer1234' WITH GRANT OPTION;;

mysql> FLUSH PRIVILEGES;

创建用户并授权，这个不用刷新

MariaDB [(none)]> grant select on auth.* to 'xiaoqi'@'localhost' identified  by '123456';


创建数据库，创建连接用户，授权

MariaDB [(none)]> create database bdqn;

MariaDB [(none)]> grant all on bdqn.* to 'dbuser'@'192.168.4.19' identified by 'qwe123';


查看权限

show grants for 用户名@来源地址

MariaDB [(none)]> show grants for 'dbuser'@'192.168.4.19';      #查看dbuser用户从地址访问数据库时的授权信息


撤销权限

revoke  权限列表 on 数据库名.表名  from 用户名@来源地址

MariaDB [(none)]> revoke  all on auth.* from 'xiaoqi'@'localhost';

MariaDB [(none)]> show grants for 'xiaoqi'@'localhost'；


 

备份

[root@c1 ~]# mysqldump -u root -p auth  users > auth-users.sql       #备份auth库的users 表
[root@c1 ~]# mysql -uroot -p123456  auth <auth-users.sql               #还原表到auth库中

[root@c1 ~]# mysqldump -uroot -p123456 --database auth >auth.sql   #备份auth库

[root@c1 ~]# mysql -uroot -p123456  < auth.sql                             #还原单个库

[root@c1 ~]# mysqldump -uroot -p123456 --opt --all-databases >all-data.sql         #备份所有库

[root@c1 ~]# mysql -uroot -p123456 < ~/all-data.sql                     #还原全部库
注：若备份文件中已经包括完整的库信息，则执行导入操作时无需指定库名

MariaDB [test]> source /root/all-data.sql;                                   #也可以用source还原数据

drop  table auth.user;                                              #删除数据表

drop  database  auth;                                               #删除数据库

MariaDB [(none)]> show master logs;                      #查看二进制日志文件

管理表中的数据记录

insert into 表名                                                          #插入数据记录

select  * from auth.users                                         #查询users表中的所有数据记录

update  auth.users set…………                                  #修改数据记录

delete from 表名  条件表达式                                       #删除数据记录

grant                                                                         #授权

show  grant for 用户名@来源地址                                #查看授权

revoke                                                                       #撤销权限

mysqldump  -u root –p mysql user  > mysql-user.sql   #备份库中的表

mysql –u root –p  --databasa  auth   >auth.sql            #备份库

mysql –u root –p –-opt –all-databases > all-data.sql    #备份所有的库

mysql –u root –p test < mysql-user.sql                         #恢复表到库中

mysql –u root –p < ~/all-data.sql                                  #导入备份的所有库

若备份文件中已经包含完整的库信息，则执行导入操作时无需指定库名

mysql> update user set user_name='lili'  where id in(5,6,7);    #修改567列的用户名为lili

mysql> update user set password='123456' where  id in(2,5,6,7);

mysql> update user set email='lili@163.com'  where id  in(3,5,6,7);

mysql> update user set  mobile='15800000000' where id between 2 and 7;   #修改类型mobile2-7列值为15800000000

mysql> update user set password='*6BB4837EB74329105EE4568DDA7DC67',email='peng@163.com' where id=4;          #更改ID4的密码和email

mysql> update user set fee='11' where fee=0.00;           #把fee为0.00都改改成11

 

mysql> select user,host  from user;

mysql> delete from user where host='::1'

mysql> flush privileges;

mysql> update user set host='10.0.0.123' where user='root';

mysql> flush privileges;

一, 创建用户:
命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';

二,授权:
命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

三.设置与更改用户密码

命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");
例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

五.删除用户

命令: DROP USER 'username'@'host';

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DQL查询

以下练习操作在centos6.8：

show grants for 'root'@'192.168.4.19'G;

过滤重复的值

mysql> select distinct password from user;

image

把字段连接起来

mysql> select concat(user_name,email) from user;

image

把字段连接起并重命名

mysql> select concat(user_name,email) as user_name_email from user;

image

分隔符

mysql> select concat(‘==’user_name,email) as user_name_email from user;

查询字段并重命名查看

mysql> select user_name as name,email as em from user;

image

模糊查询数据流大不用)

select user_name from user where user_name like '%n%';  

image

排序

image

mysql> select count(*) from user;                  #共多少条记录  数据大不用

image

mysql> select sum(age) as sum_age from user;    # 字段总和     #应用场景销量

mysql> select avg(fee) as sum_age from user;    #字段平均数，字段就是列

mysql> select  max(age) from user;                       #列的最大值

mysql> select  min(age) from user;                  #列的最小值

image

mysql> alter table user add sex tinyint unsigned not null default 0 comment'0男1女' after email;#添加列

image

mysql> update user set sex=1 where id in (2,3);

分组

按性别分组

mysql> select count(*) from user group by sex;

image

命名总人数

image

mysql> select sex from user group by sex having count(*)>2;   #把总人数大于2的列出来 0是男 1是女

也可以这样查

mysql> select count(*) from user where sex=0;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SELECT u.`user_id`,u.`user_name`, u.`user_rank`, ap.`id`,ap.`authority_zone`, ap.`apply_class`, ap.`apply_type`, ap.`apply_time` FROM dsc_users u LEFT JOIN dsc_user_apply ap ON ap.`user_id` = u.`user_id` WHERE u.`user_name`IN ( '彭忠涛');

 image

SELECT * FROM dsc_region_grant r WHERE r.`user_id` IN( 13364 );

image

SELECT * FROM dsc_region r WHERE r.`parent_id` = 38854;

image

image

image

image

pid  cid rid tid

image

生产练习-查询数据记录

SHOW TABLE STATUS                                                    #查看表注释

SHOW FULL COLUMNS FROM dsc_suning_address      #查看字段注释

SHOW FULL FIELDS FROM  dsc_suning_address         #查看字段注释

SHOW FULL FIELDS FROM  dsc_order_info

image

image

image

order         #订单

SHOW FULL COLUMNS FROM dsc_user_commision

image

SHOW FULL COLUMNS FROM dsc_user_profits

image

 

profilts                   #收益

DESCRIBE dsc_users                                       #用户表   （所有字段）

SHOW FULL FIELDS FROM  dsc_users        

image

image

user_rank                #用户等级

SHOW FULL COLUMNS FROM dsc_user_apply

 image

apply_type        #申请类型 1大客户 2服务商 3运营商

apply_type         #类别  0未区分 1A类服务商 2B类服务商   9大客户

apply_time          #申请时间

DESCRIBE dsc_region                              #地区表

SHOW FULL FIELDS FROM dsc_region

image

表内容

image

DESCRIBE dsc_region_grant        #地区授权表

SHOW FULL FIELDS FROM  dsc_region_grant

image

表内容

image

dsc_suning_address                                       #苏宁地址表

DESCRIBE dsc_suning_address

SHOW FULL FIELDS FROM  dsc_suning_address

image

SELECT * FROM  dsc_suning_address  WHERE   pid=190

pid  # 父编码

second_pid   #二级父编码                 比如说我要找番禺区下的所有的镇：

1.找出二级父编码，对镇来说区是一级父编码，市就是二级父编码，先找出市的编码，找市的编码先找省的

SELECT * FROM dsc_suning_address WHERE pid=0                    #找出广东省编码是190

SELECT * FROM dsc_suning_address WHERE pid=“190”                #找出市是020

SELECT * FROM dsc_suning_address WHERE pid="020"           #找出区是07，因为区在全国地址库中有可能会重复，所以不能像上面那样直接使用07找出区下面的所有镇，要结合上面两个条件

2.SELECT * FROM dsc_suning_address WHERE  second_pid=020  AND pid=07          #番禺区下面所有的镇

image

select 列名1，列名2，…… from 表名  where  条件表达式

SELECT * FROM  dsc_suning_address  WHERE   pid=190          #广东所有城市

PID字段      #父

OR(或) 和 AND(且) 连接语句

SELECT * FROM  dsc_suning_address  WHERE   pid=08 AND NAME="申港镇"

image

-- 1 查询当前授权区域

SELECT u.`user_id`,u.`user_name`, u.`user_rank`, ap.`id`,ap.`authority_zone`, ap.`apply_class`, ap.`apply_type`, ap.`apply_time` FROM dsc_users u LEFT JOIN dsc_user_apply ap ON ap.`user_id` = u.`user_id`
WHERE u.`user_name`IN ( '18992619159');

image

 

-- 2 修改授权区域
SELECT * FROM dsc_region_grant r WHERE r.`user_id` IN( 3803 );  -- 用户ID

 image

3.--  查询地区及ID

SELECT * FROM dsc_region r WHERE r.`parent_id` = 16570;  -- 区域id

image

-- 3 处理界外订单（佣金）（ 变量：user_id, street）， 修改 grant_status 为 1
SELECT o.order_id, o.`order_sn` ,uc.`id`,uc.`check_status`, uc.`grant_status` FROM dsc_order_info o
LEFT JOIN dsc_user_commision uc ON o.order_id = uc.`order_id`
WHERE o.`user_id` = 3803 AND o.`street` = 16575 AND o.`pay_status` = 2;

image

order_sn                #苏宁订单

grant_status        #授权区域结算状态

LEFT JOIN          #左外联接，结果中的记录在A表中存在，B表中不一定有。相当于a表为主体表，b为辅助表

order_id              #订单编号

street                 #街道

pay_status        #支付状态

-- 3 处理界外订单（收益）  变量：订单号， 修改 grant_status 为 1

SELECT up.`id`, up.`order_sn`, up.`grant_status` FROM dsc_user_profits up WHERE up.`order_sn` IN (
'2018042108432996911');

 

 

SHOW TABLE STATUS                                                    #查看表注释

SHOW FULL FIELDS FROM  dsc_suning_address         #查看字段注释

数学符号条件--WHERE限制条件可以有数学符号 (=,<,>,>=,<=)

例如

SELECT user_id,user_rank FROM  dsc_users WHERE  user_rank>0

模糊查询数据流大不用)

SELECT * FROM  dsc_suning_address  WHERE  NAME LIKE '%沙头%'

SELECT COUNT(*) FROM  dsc_suning_address;                 #表共多少条记录

 

SELECT DISTINCT  user_name,user_rank FROM dsc_users    #完全重复的记录只一次

select user_name,id from user order by  字段;   #升序

select user_name,id from user order by  字段 desc ;  #降序 （场景发布文章）

 

 

表关联语法

select * from a left join b on a.id=b.id ;   #a表关联

SELECT COUNT(*) FROM dsc_users LEFT JOIN dsc_user_apply   ON dsc_users.`user_id` = dsc_user_apply.`user_id`                    #统计表中所有用户

SELECT COUNT(*) FROM dsc_user_apply  WHERE apply_time BETWEEN '2017-04-01 00:00:00' AND '2017-05-01 00:00:00' 

BETWEEN           #在….之间

 

SELECT COUNT(*) FROM dsc_users LEFT JOIN dsc_user_apply   ON  dsc_users.`user_id` = dsc_user_apply.`user_id` WHERE  apply_time BETWEEN '2017-04-01 00:00:00' AND '2017-05-01 00:00:00'

 

 

 

 

其他>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysql>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysqladmin -u root password 'qwer1234'              #为root用户设置密码，不然无法登录msyql

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。

什么是元数据呢？元数据是关于数据的数据,属性等信息，如表名等

mysqldump -uroot -p --default-character-set=utf8  db >1.sql  备份数据库

show tables ; 查看当前库下有哪些表

status 查看mysql数据库的运行状态

desc  table_name; 查看表结构

show full  processlist;  #查询数据库连接

show status like '%Threads_connected%';#当前连接数

show status like '%qcache%'; #查询缓存情况

show status like 'Aborted_clients';  #由于客户没有正确关闭连接已经死掉，已经放弃的连接数量

show variables like '%max_connections%';//查看最大连接数量

show variables like '%timeout%';#查看超时时间

show variables like 'log_%'; #查看日志是否启动

show variables like '%quer%'; log_slow_queries状态为OFF, 说明没有开启慢查询

开启慢查询修改mysqld服务配置文件/etc/my.cnf

log-slow-queries = /var/lib/mysql/mysql-slow.log        #慢查询日志放置目录，必须有写入权限，此项配置放放置[mysqld]下

long_query_time = 2                                     #最长执行时间，此项配置放放置[mysqld]下

重启mysqld服务

select * from 表名 查表所有数据

show variables like 'storage_engine';          查看mysql支持的存储引擎

mysql忘记密码修改mysqld服务配置文件/etc/my.cnf的[mysqld]下添加skip-grant

mysql                 #不用输密码直接登录

use mysql;

update user set password=password('qwer1234') where  user='root';

flush privileges; 

最后修改/etc/my.cnf 去掉 skip-grant , 重启mysql服务

当我们第一次使用mysql命令的时候我们提示命令不存在的时候，我们加入path就行了

在配置文件中 /etc/profile 中

PATH=$PATH:/usr/local/mysql/bin

然后别忘记

source /etc/profile

或者写全路径   /usr/local/mysql/bin/mysql -uroot

查看表的字段 desc tb;

当前是哪个用户  select user();

当前库 select database();

查看数据库版本 select version();

mysql操作记录>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

#查看表数据

SELECT  * FROM   sys_module_version;

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/pengrj/p/8777228.html