mysql命令常用参数

-u   用户名
-p   用户密码
-h   服务器IP地址
-D   链接的数据库
-N   不输出列信息
-B   使用tab键代替默认交互分隔符
-e   执行SQL语句

其他参数

-E 垂直输出
-H html的格式输出
-X 以XML的格式输出

例子

mysql -u "root" -p"myPassword123#" -h "192.168.100.12" -D "dongli" -e "select id from k_base"

数据库链接

指定数据库字符集和排序规则

mysql创建中文数据库并指定编码utf8

CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;

用户和授权

创建用户

命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明:

username – 你将创建的用户名,

host – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%.

password – 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

CREATE USER 'lin'@'localhost' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY ''; 
CREATE USER 'pig'@'%';

授权

命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

GRANT SELECT, INSERT ON school.* TO 'lin' @'%';
GRANT ALL ON *.* TO 'pig'@'%';

授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：

grant all privileges on testDB.* to test@localhost identified by '1234';

flush privileges;//刷新系统权限表

设置与更改用户密码

命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");

5.7也正常。

撤销用户权限

命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';

说明: privilege, databasename, tablename – 同授权部分.

例子: REVOKE SELECT ON *.* FROM 'pig'@'%';

查询数据

语句：select * from xxx;

例子：

• select id,username,password from t_user;
• select id,username,password,gender from t_user where gender = '男';
• select id,username,password,gender from t_user where gender is null;

添加数据：insert xxx(id, username) values(xx, "xxx");

例子：

• insert into t_user(id, username) values(10, "hehehe");
• insert into t_user(id, gender, username, age, password) values(15, '男', 'shihu', 18, '123456');
• insert into t_user values(16, 'benladeng', '123456', '拉登', 'nan', 18);

修改数据：update tablename set xx=xx,xxx=xx where xxx=xxx and xxx=xxx;

删除数据：delete from tablename where xx=xxx and xxx = xxx or xxx = xxx;

Mysql导入txt

show databases;
use search;

source /User/qidong/Downloads/phone.sql

LOAD DATA LOCAL INFILE '/Users/qidong/Downloads/201801.txt'
INTO TABLE phone
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(pref, phone, province,city,isp,post_code,city_code,area_code,types);

Query OK, 380158 rows affected (3.64 sec)
Records: 380158 Deleted: 0 Skipped: 0 Warnings: 0

如果数据表的字段对应。没有prefix phone等，上面字段的那一行也可以简写SQL

LOAD DATA  INFILE '/Users/qidong/Downloads/201801.txt'
INTO TABLE phone；

删除重复数据

实战中查询重复当天评论重复课程的语句

SELECT  * from wk_course_score as a
join (
select user_id,course_id,id,FROM_UNIXTIME(create_time,'%Y%m%d') as Ymd from  wk_course_score GROUP BY Ymd,user_id,course_id having count(user_id)>1 ORDER BY course_id ASC
) 
b on b.user_id=a.user_id and a.id>b.id and a.course_id=b.course_id

SQL链接: https://pan.baidu.com/s/19QHmfNy8Pr4HRLNzVCjsUw

查询语句

select order_id,product_id,count(*) from product
 group by order_id,product_id having count(*) >1

删除重复数据语句

delete a from product_comment  a 
join(
 select order_id,product_id,min(comment_id) as comment_id
 from product_comment
 group by order_id,product_id
 having count(*)>2
) b on a.order_id=b.order_id and a.product_id=b.product_id
and a.comment_id>b.comment_id

备份表数据

数据少的情况执行sql语句对数据进行备份

create table bak_xxxxxx_180203  like product; //创建一个表结构和product一样

insert into bak_xxxxxx_180101 select * from product;
//备份数据

进行分区间统计

统计消费总金额大于1000元的,800到1000元，500到800元的,以及500元以下的人数

select 
count(case when ifnull(total_money,0) >=1000 then a.customer_id end ) as '大于1000',
count(case when ifnull(total_money,0) >=800 AND ifnull(total_money,0)<1000
 then a.customer_id end ) as '800~100',
count(case when ifnull(total_money,0) >=500 and ifnull(total_money,0)<800
  then a.customer_id end ) as '500~800',
count(case when ifnull(total_money,0)<500 then a.customer_id end ) '小于500'
from  `customer_login` a left join 
(
   select customer_id,sum(order_money) as total_money 
    from `order_master` group by customer_id
) b on a.`customer_id`=b.`customer_id`

查看表的字段信息

语法：select column_name, column_comment from information_schema.columns where table_schema ='db' and table_name = 'tablename';

select column_name, column_comment from information_schema.columns where  table_name = 'hk_user' 
 and table_schema ='hk_finance'

show columns from hk_user

Insert Into

复制本表数据

INSERT into wk_wenda_post(uid,title,course_id,video_id,content) SELECT uid,title,course_id,video_id,content from wk_wenda_post

Select update

在遇到需要update设置的参数来自从其他表select出的结果时,需要把update和select结合使用,不同数据库支持的形式不一样,在mysql中如下:

update A inner join(select id,name from B) c on A.id = c.id set A.name = c.name;

导出表

mysql> select * from k_admin into outfile "/Users/dong/Downloads/2.txt";
Query OK, 11 rows affected (0.00 sec)