mysql带c的命令_MySQL 常用命令大全

查看状态:

show database;

show tables;

show columns from table_1; (describe table_1)

show status

show create database/table database_1/table_1

show grants

show errors/warnings

关于show的终极命令：help show

检索相关：

select columns_1(,columns_2) from table_1

select * from table_1

select distinct columns_1 from table_1

select columns_1 from table_1 limit 5

select columns_1 from table_1 limit 5,5 (从第五行开始的5行，注意第一个索引是0)

select table_1.columns_1 from database_1.table_1

select column_1 from table_1 order by column_1(,column_2)

select * from table_1 order by column_1 desc (降序)

select * from table_1 order by column_1 desc, column_2 (asc)

过滤相关：

过滤：普通

select * from table_1 where column_1 =(,>=,<=,<>,!=) 10

select * from table_1 where column_1 between 5 and 10

select * from table_1 where column_1 IS NULL

select * from table_1 where (column_1 =10 or column_2 =200) and column_3>=10 (and 优先级更高)

select * from table_1 where column_1 (not) in (1002,1003) (in比or执行速度快些，多用，in可以包含select，not表否定)

过滤：通配

select * from table_1 where column_1 like 'jet%' (%:表示任意字符任意次数，需要注意NULL和尾部空格的问题)

select * from table_1 where column_1 like 'jet_abc' (_:表示任意字符一次)

过滤：正则

select * from table_1 where column_1 regexp '.000'

select * from table_1 where column_1 regexp binary 'abc .000' (binary区分大小写)

select * from table_1 where column_1 regexp 'abc | def | hij' (匹配OR的作用)

select * from table_1 where column_1 regexp '[123] abc' ([]在这里起到了OR的作用)

select * from table_1 where column_1 regexp '[1-5] abc'

select * from table_1 where column_1 regexp '//.' (匹配".")

select * from table_1 where column_1 regexp '\\( [0-9] sticks{0,1}\\)' ({0-1}表示匹配0次或1次)

select * from table_1 where column_1 regexp '^[0-9\\.]' (表示开头在0-9或者".")

字段格式化：

select Concat(column_1,'(',column_2,')') from table_1 order by column_1 (拼接)

select concat(trim(column_1),'(',rtrim(column_2),')') from table_2 order by column_2 (trim ,rtrim ,ltrim删除多余空格)

select Concat(column_1,'(',column_2,')') as column_concat from table_1 order by column_1 (别名和导出列)

select column_1*column_2 as multi from table_1 (算数计算)

数据处理：

select Upper(column_1) from table_1 (小写换大写)

select Lower(column_1) from table_1 (大写换小写)

select column_1 from table_1 where Soundex(column_1) = Soundex ('Y lie') (声音类似，我去！)

select Left(column_1),Length(column_2),Locate(column_3),Right(column_4),SubString(column_5) from table_1

日期和时间处理：

select * from table_1 where Date(column_1) = '2005-09-30'

select * from table_1 where Date(column_1) between '2005-09-01' and '2005-10-10'

select * from table_1 where Year(column_1) = 2005 and Month(column_1) = 10

数值处理:

select * from table_1 where Abs(column_1) < 5 and Sin(column_2) = 0 and Sqrt(column_3) >4

汇总数据：

select count(*) from table_1;

select count(column_1) from table_1

select max(column_1),min(column_2),sum(column_3),avg(column_4) from table_1

分组：

select count(*),column_1 from table_1 group by column_1 [with rollup] 分组显示[显示汇总级别]

select column_1,count(*) from table_1 group by column_1 having count(*)>=2 where是行过滤，having是组过滤

select column_1,count(*)from table_1 where column_3 >=10 group by column_1 having count(*) >=2 (group要使用选择列的表达式)

字句顺序：

select * from * where * group by * having * order by * limit *

子查询：

select column_1,column_2 from table_1 where column_3 in (select column_3 from table_2 where column_4 = 'value_1')

select column_1,(select count(*) from table_2 where table_2.column_2 = table_1.column_2) from table_1 where column_1 = 'value_1' (相关子查询要指定table)

联结表：

select table_1.column_1,table_2.column_2,table1.column_3 from table_1,table_2 where table_1.column_4 = table_2.column_4 order by column_1,column_2 (联结表会导致性能的极大下降)

select column_1,column_2,column_3 from table_1 inner join table_2 on table_1.column_4 = table_2.column_4 (inner join与上一条语句相同)

select column_1,column_2 from table_1 as t_1, table_2 as t_2, table_3 as t_3 where t_1.column_1 = t_2.column_2 and t_3.column_2 = t_2.column_1 (表别名，不返回客户机，只在查询时有效)

select id,name from products where vendor = (select vendor from products where id='item1')

select p1.id,p1.name from products as p1, products as p2 where p1.vendor = p2.vendor and p2.id="item1" (与上条语句相同，但自联结可能性能好些，测试下)

select c.*,o.num,oi.item from customers as c, orders as o, items as oi where c.id=o.id and oi.num=onum

select customers.id, orders.num from customers inner join orders on customers.cusid = orders.cusid (仅包含关联行)

select customers.id, orders.num from customers left outer join orders on customers.cusid = orders.cusid (包含cumstomers所有行)

select customers.id, orders.num from customers right outer join orders on customers.cusid = orders.cusid (包含orders所有行)

组合查询：

select id from products where price <=5 union [all] select id from products where id in(1001,1002) order by price (union可以和where互换，若使用all将返回所有行，不去重；不能使用多条order by，只能在最后有一个)

select id from products where price <=5 or id in(1001,1002)

全文本搜索：

MyISAM支持全文本搜索，而InnoDB不支持全文本搜索

select * from productnotes where match(column_1) against('rabbit')

select * from productnotes where column_1 like '%rabbit%' 与上条类似

插入：

insert into table_1 values(NULL,value_1,value_2)

insert into table_1(column_1, column_2) values(value_1,value_2)

insert low_primary into table_1 values(value_1,value_2) (低优先级，对update和delete同样适用)

insert into table_1 values(value_1,value_2),(value_3,value_4)

insert into table_1(column_1,column_2) select column_3,column_4 from table_2

更新和删除：

update table_1 set column_1 = 'value_1', column_2 = 'value_2' where column_3 = 'value_3'

delete from table_1 where column_1 = 'value_1'

truncate table table_1

创建、更改表：

create table table_1 (

id int not null auto_increment,

name char(50) not null,

city char(50) null default 1, (默认值：1)

primary key (id),

) engine=InnoDB

primary key (num,item) 指定组合主键

select last_insert_id()获得最新自增主键

alert table table_1 add column_x char(20)

alert table table_1 drop column_x

alter table table_1 add constraint column_1 foreign key (id) references vendors (id) (??外键?)

drop table table_1

rename table table_1 to table_r1

视图：

create view viewtable_1 as select column_1,column_3 from table_1,table_2 where table_1.column_1=table2.column_2 and table_2.column_3 = table_1.column_4

select * from viewtable_1 where column_1 = 'value_1'

存储过程：

call productpricing(@pricelow,@pricehigh,@priceaverage)

create procedure productpricing() begin select avg(price) as priceaverage from products; end;

drop procedure productpricing [if exists]

create procedure productpricing (out pl decimal(8,2),out ph decimal(8,2),out pa decimal(8,2)) begin select min(prod_price) into pl from products; select max(prod_price) into ph from products; select avg(prod_price) into pa from products; end;

触发器：

四个必要信息：唯一触发器名；关联的表；响应的活动(delete, insert, update)，每个表最多支持6(before和after乘以三个活动)个触发器

create trigger trig_1 after insert on table_1 for each row select 'added'; (added为显示文本)

drop trigger trig_1

create trigger new_trig after insert on table_1 for each row select NEW.column_1 (利用new来访问虚拟新表)

create trigger new_trig before delete on table_1 for each row select OLD.column_1 (利用OLD来访问虚拟旧表)

create trigger new_grig before update on table_1 for each row select OLD.column_1 (update可以用OLD和NEW)

事务:

InnoDB才支持事务，MyISAM并不支持。

开始：start transaction

回撤: rollback (对于create和drop无法回退，对于select也无意义，只用于管理insert，update和delete)

提交：commit

保留点：savepoint point_1

保留点回退：rollback to point_1

全球化相关：

字符集：字母和符号的集合

编码：为某个字符集成员的内部表示

校对：为规定字符如何比较的指令

show character set (可用字符集及每个字符集的描述和默认校对)

show collation (显示所有可用的校对)

show variables like 'character%' (所用的字符集)

show variables like 'collation%' (所用的校对)

create table table_1 (column_1 int, column_2, varchar(10)) default character set utf8 collate utf8_romanian_ci

select * from table_1 order by column_1 collate utf8_romanian_ci

安全：

use mysql; select user from user;

create user user_1 identified by 'p@$$sOrd'

rename user_1 to user_2

drop user user_1

show grants for user_1

grant select[,insert] on database_1.* to user_1

set password for user_1 = Password('abcdefg')

set password = password("abcd") (设置自己的口令)

维护和性能监控：

analyze table table_1

check table table_1

show variables

show status

show processlist

explain解释语句