mysql 命令

一、连接

-- -u 用户名
-- -h host
-- -P port
-- -p password
mysql -u root -h 127.0.0.1 -P 3306 -p

二、数据库相关

-- 创建数据库
create database 数据库名 default charset=utf8;

-- 显示所有数据库
show databases;

-- 进入某数据库环境
use 数据库名;

-- 显示创建数据库的语句
show create database 数据库名;

-- 删除数据库，多个的话用逗号隔开
drop database 数据库名;

三、表相关

-- 显示当前数据库的表
show tables;
show tables like "db_name%";

-- 显示创建表的语句
show create table table_name;

-- 显示表的结构
desc table_name;

-- 删除表
drop table [if exists] table_name;

-- 修改表名
alter table 旧表名 rename to 新表名;

-- 为表添加主键 id
alter table 表名 add primary key(id);

-- 为表 tmp 的 country 字段添加默认设置'CN'
alter table tmp alter country set default 'CN';

-- 在表中添加一个新字段，默认添加在最后面
alter table 表名 add 新字段 字段类型 [字段选项] [first | after 字段名];

-- 修改表中的字段类型 modify
alter table 表名 modify 字段 字段类型 [字段选项] [first | after 字段名];

-- 修改表中的字段名 change
alter table 表名 change 老字段 新字段 字段类型 [字段选项] [first | after 字段名];

-- 删除表中的字段
alter table 表名 drop 字段;

-- 清空表数据
truncate table 表名;

四、插入

-- insert into table_name select ... 
-- 要求表 socrebak 必须存在
insert into socrebak select * from socre where neza='neza';    

-- create table table_name select ...
-- 要求表 socrebak 不存在
create table socrebak select * from socre where neza='neza';
-- select * into socrebak from socre where neza='neza';    # mysql 不支持

五、通配符

通配符	描述	说明
%	任意个字符	配合 like
_	一个字符	配合 like
[]	字符集中任何一个单一字符	配合 regexp 或者 rlike，正则表达式

select * from mm where name like 'abc%';
select * from mm where name like 'abc_';
select * from mm where name regexp '^[^abcde]';
select * from mm where name rlike '^[^a-e]';

六、别名

-- as 可以省略
-- 把多个字段放在一起的时候用 concat()
select name 网站名, concat(url, ', ', alexa, ', ', country) 网站信息 from websites;
select date 日期 from access_log;
-- 表的别名
select w.name, w.url, a.count, a.date from websites w, access_log a where a.site_id=w.id and w.name='123';

七、join

select w.id 网站id, w.name 网站名, a.count 网站用户, a.date 日期 from websites w join access_log a on w.id=a.site_id;

-- 在使用 join 时，on 和 where 条件的区别：
-- 1、on 条件是在生成临时表时使用的添加，它不管on中的添加是否为真，都会返回左表中的记录；
-- 2、where 条件是在临时表生成好之后，再对临时表进行过滤的条件，这时已经没了 left jion 的含义（必须返回左表的记录）了，条件不为真的就全部过滤掉；

八、外键

-- 创建表时添加外键
create table 表名 (
    column1 datatype null/not null, 
    column2 datatype null/not null, 
    ...
    constraint 外键约束名 foreign key (column1, column2 ...),
    references 外键依赖的表 (column1, column2 ...),
    on delete cascade   # 级联删除
    )

-- 修改表时添加外键
alter table 表名
    add  constraint 外键约束名 foreign key (column1, column2 ...),
    references 外键依赖的表 (column1, column2 ...),
    on delete cascade   # 级联删除

八、查询语句

-- select .....  from ....  where ....  group by .... having ...  order by ....  limit ....;

select 
	w.name, sum(a.count) 数量 
from 
	websites w join access_log a on w.id=a.site_id 
where 
	w.alexa<200 
group by 
	w.name 
having 
	sum(a.count) > 200 
order by 
	name desc 
limit 1,1;

九、常用内置函数

平均值：avg(字段名)
总记录数：count(字段名)
最大值：max(字段名)
最小值：min(字段名)
总数字相加：sum(字段名)
字符串截取：mid(字段名, 起始位置[，截取长度])   # 起始位置从1开始
长度：length(字段名)
转大写：ucase(字段名)
转小写：lcase(字段名)
四舍五入：round(字段名[, int])  # 保留多少位小数，没有的话就是不要小数位
当前日期时间：now()
日期格式化：date_format(字段名, 格式)    

select name, url, date_format(now(), '%Y-%m-%d') from websites;
select lcase(country) from websites group by country having count(country)>1;

十、自定义函数

drop function if exists get_alexa;
delimiter $$   -- 定义结束符，函数内部回用到;以防冲突
create function get_alexa(wid int) returns int 
begin 
    declare result int; 
    select alexa into result from websites where id=wid; 
    return result; 
end$$
delimiter ;

-- 调用
select get_alexa(2);

十一、case … when … then … else … end

-- case [字段名]
--     when 条件1 then 选项1
--     when 条件2 then 选项2
--     else 默认值
-- end
-- end 后面最好加上一个别名
select 
	name, url, case 
				when alexa between 10 and 30 then 'alexa在10到30之间' 
				when alexa < 10 then 'alexa小于10' 
				when alexa >30 then 'alexa大于30' 
				else '其他' 
			end 判断alexa的范围 
from 
	websites;


select 
	name, url, case 
				alexa when 20 then 'alexa在10到30之间' 
				when 3 then 'alexa小于10' 
				when 0 then '0' 
				else '其他' 
			end 
from 
	websites;