mysql入门

一、mac安装MySQL用法总结

1、官网安装MySQL后，在系统服务MySQL点击Start MySQL Server启动服务，用完记得关闭
2、登录及退出
PATH="$PATH":/usr/local/mysql/bin

mysql -u root -p
exit

3、切换root用户
sudo -i(--login)

/var/root

man sudoers
visudo -f /private/etc/sudoers

exit(logout)

二、基本用法

1、创建数据库demo及表user

drop database if exists demo;
create database demo;
use demo;
drop table if exists user;
create table user(
	id int primary key auto_increment,
	name varchar(20) not null,
	sex enum('boy','girl'),
	age int unsigned,
	major varchar(30) default 'computer',
	insertDate timestamp default now()
);
insert into user(name,sex,age) values('talent','boy',25);
insert into user(name,sex,age) values('beauty','girl',22);

2、一些实用操作

show databases;查看数据库列表

show tables;查看数据库表列表

select *from user;查看表数据

describe user;查看表结构

select now();

select current_date();

select current_time();

3、操作sqlite数据库

ls -l /data/data/com.roy.devil

chmod 777 /data/data/com.roy.devil/databases 设置权限

ls -l /data/data/com.roy.devil/databases

sqlite3 /data/data/com.roy.devil/databases/devil 打开数据库

.database

.table 查看所有表

.schema Template

select *from Template limit 0,20;

select count(*) from Template;

.quit

4、限制数据库表行数

/**
 * 删除超过maxCount条数的最旧的历史记录
 * @param maxCount 历史最大条数
 */
private void deleteRedundantHistory(int maxCount) {
    try {
        String tableName = HeadNewsHistory.TABLE_NAME;
        String sql =
                "delete from " + tableName + " where (select count(_id) from " + tableName + ") > "
                        + maxCount + " and _id in (select _id from " + tableName
                        + " order by _id desc limit (select count(_id) from " + tableName + ") offset "
                        + maxCount + ")";
        init();
        dbHandler.execute(sql);
    } catch (Throwable e) {
        e.printStackTrace();
    }
}
5、分页查询数据
/**分页查询 default size=20
 * @param desc true倒序(desc)false正序(asc默认)
 * @param pageIndex 从1开始
 */
public List<UsedCarFav> queryAll(boolean desc, int pageIndex) {
    init();
    Cursor cursor = dbHandler.rawQuery("select * from "+UsedCarFav.TABLE_NAME
            +" where "+UsedCarFav.DELETED+" = "+UsedCarFav.DELETED_NO
            +(desc?(" order by "+UsedCarFav.KEEP_TIME+" desc"):"")
            +" limit "+ Finals.TWENTY+" offset "+Finals.TWENTY*(pageIndex-1)
            +";");
    return readCursorToList(cursor, UsedCarFav.class);
}
6、按范围查询多行
select name from user where age=28 and name='Victor';

select name from user where age=28 or name='line';

select id,name,age from user where age in(5,26);