Cheatsheet-SQLite3和MySQL常用命令
SQL(Structure Query Language)数据库作为一种关系型数据库,包括SQLite3和MySQL等,两者在 Android应用开发中会经常用到,这里摘录了常用的操作命令。
SQLite3常用命令(在SQLite网站下载源码后,用Cygwin编译即可)
打开/创建 数据库(如果存在则打开,不存在则新建):.open my_database.db
显示数据库结构 :.schema 或 .dump
显示/隐藏 查询结果的列名: .explain on/off
建表:CREATE TABLE cities_table ('id' int primary key, 'name' varchar(20));
删除表:drop table my_things;
添加列:alter table cities_table ADD 'description' varchar(50);
插入数据:insert into cities_table(id,name) values (1, 'Beijing');
选择数据:select * from cities_table where name = 'Beijing' limit 10;
Transactions:begin transaction; commit;
输出到文件:.output 'cities.txt';
MySQL常用命令(如没安装,请看这里):
连接MYSQL:mysql -h主机地址 -u用户名 -p用户密码
例子:mysql –h 121.199.14.231 –u root –p 123abc
退出MYSQL:exit (回车)
修改密码:mysqladmin -u用户名 -p旧密码 password 新密码
例子:mysqladmin –u root –p 123abc password cba321
增加新用户:grant select on 数据库.* to 用户名@登录主机 identified by \"密码\"
例子:
任何主机上登录
grant select,insert,update,delete on *.* to test1@\"%\" Identified by \"abc\";
本地主机登录
grant select,insert,update,delete on mydb.* to test2@localhost identified by \"abc\";
无密码登陆
grant select,insert,update,delete on mydb.* to test2@localhost identified by \"\";
出现ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
UPDATE mysql.user SET Password=PASSWORD('newpassword') where USER='root';
建库: create database my_database;
删除数据库:drop database my_database;
显示数据库:show databases;
使用数据库:use my_database;
建表:create table my_table;
建表:create table my_table (ID INT NOT NULL AUTO_INCREMENT, TITLE CHAR(50) NOT NULL,CONTENT TEXT NOT NULL,PRIMARY KEY(ID));
删表: drop table my_table;
表重命名:rename table `oldtabel` to `newtable`; (back tick, not single quote)
列重命名:alter table my_table change old_row new_row type;
列改变类型:alter table my_table modify my_row varchar(128);
更新列:UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1
[, col_name2=expr2 ...][WHERE where_definition][ORDER BY ...][LIMIT row_count];
表合并:insert into table_b select * from table_a where not exits(select * from table_b where table_b.key=table_a.key);
显示所有表 show tables;
显示表结构: describe my_table;
清空表:delete from my_table;
显示表中记录:select * from my_table;
显示表中记录总数:select count(1) from my_table;
显示10条记录:select * from my_table limit 10;
显示随机10条记录:select * from my_table order by rand() limit 10;
导入数据库:source database_name.sql;
备份数据库:以管理员身份进入bin目录,mysqldump –h host –u root –p dbname > dbname_backup.sql;
备份数据表:以管理员身份进入bin目录,mysqldump –h host –u root –p dbname dbtable> dbname_backup.sql;
恢复数据库:以管理员身份进入bin目录,mysqldump –h host –u root –p dbname < dbname_backup.sql;