1、选择查看一个Mysql数据库
USE database;
2、查看数据库
SHOW DATABASES;
3、查看数据库表
show tables;
4、查看数据库表的详细情况
describe table;
5、创建数据库
create database db_name;
6、创建表
create table table_name(field_name TYPE(SIZE),field2_name TYPE(SIZE));
eg:create table person(name VARCHAR(20),sex CHAR(1),birth DATE);
7、Load tab-delimited data into a table
load data local infile "infile.txt" into table table_name;
(use \n for NULL)
8、在一张表中插入一行
insert into table_name values('MyName','MyOwner','2014-07-22');
(use null for null)
9、检索信息
select from_columns from table where conditions;
ALL VALUES:select * from table;
Some values:select * from table where rec_name="value";
Multiple critera:select * from table where rec1="value1" and rec2="value2";
10、Reloading a new data set into existing table:
set AUTOCOMMIT=1;# used for quick recreation of table;
delete from pet;
load data local infile "infile.txt" into table table;
11、修改表中记录
update table set column_name = "new_value" where record_name="value";
12、查看表中unique记录
select distinct column_name from table;
13、排序输出
select col1,col2 from table order by col2;
(逆序)select col1,col2 from table order by col2 desc;
14、关于日期计算
select current_date,(year(current_date)-year(date_col)) as time_diff from table;MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.
15、Pattern Matching:
select * from table where rec like "blah%";
FIND 5-char values:select * from table where rec like "_____";(_is any single character)
16、Extended Regular Expression Matching正则表达式:
select * from table where rec rlike "^b$";
17、计算行数
select count(*) from table;
18、多表查询
select pet.name,comment from pet,event where pet.name=event.name;
19、查看当前选定数据库
select database();
20、自增行号
create table xxxx(number int not null auto_increment,name char(10) not null);
21、添加列到已创建的表
alter table tb1 add column[column_create syntax] after col_name;
22、移除一列
alter table tb1 drop column col;
Mysql cheat sheet——Mysql使用方便查
最新推荐文章于 2021-12-03 15:54:40 发布