mysql

1、mysql 命令行常用命令

>mysql -h 192.168.1.102 -u root -p
>mysql -u root -p
#mysql连接
>mysql> show databases;
#查询数据库
>mysql> create DATABASE rookie;
#创建数据库
>mysql> drop database rookie;
#删除数据库
>mysql> use rookie;
#选择数据库

>CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
#建表
>mysql> show tables;
#查询表

2、osx mysql 安装及服务启动

>brew install mysql #安装
>brew services start mysql #(启动)
>brew services stop mysql  #(停止)

3、路径及常用命令配置

>vim ~/.bash_profile
# 添加如下mysql相关别名
export PATH=$PATH:/usr/local/mysql/bin
alias mysqlstart='/usr/local/mysql/support-files/mysql.server start'
alias mysqlstop='/usr/local/mysql/support-files/mysql.server stop'
alias mysqlrestart='/usr/local/mysql/support-files/mysql.server restart’
#source生效
>source ~/.bash_profile

4、常用查询语句

>select a,b from table where a>'某个时间' order by a desc limit 1
>select oid,status,max(time) time from 表名 group by oid,max(time)；
>SELECT * from tb where id = (SELECT max(id) FROM tb);
>select * from t_assistant_article as a, (select max(base_id) as base_id, max(create_time) as create_time from t_assistant_article as b group by base_id ) as b where a.base_id=b.base_id and a.create_time = b.create_time  

>select base_id,max(create_time), max(article_id) as article_id from t_assistant_article as b group by base_id   

>select * from (select * from t_assistant_article order by create_time desc) as a group by base_id  

>select   *   from   table  limit   1

>select * from table1  limit n-1,m-n;
>SELECT * FROM table LIMIT 5,10；返回第6行到第15行的记录
>select * from employee limit 3,1; // 返回第4行

>select * from table1 limit 0,n;
或
>select * from table1 limit n;

>select * from table1 order by id desc dlimit n；//倒序排序，取前n行 id为自增形式
>select * from table1 where id>$id  order by id asc dlimit 1

>select * from table1 where id<$id  order by id desc dlimit 1