mysql基本使用

登录

mysql -h 192.168.1.100 -P3306 -u root -p    [登录数据库]

信息查看

show databases; [查看数据库]
create database boye; [创建数据库]
drop database boye; [删除数据库]
drop table if exists tb1;
use boye [使用数据库]
show columns from tb1 [查看表结构]
SHOW FULL COLUMNS FROM tb1 [查看表信息]
show index from tb1 [列出表的索引信息]
describe tb1; [查看表结构]
show tables; [查看表]

创建表、修改表

create table tb1 like tb01; [克隆tb01的表结构]
create table tb1 select * from test1 limit 1;
create table tb11 as select * from test1 limit 2;
create table tb1 (id int not null primary key auto_increment,name char(64)); [id主键自加1]
create table tb2 (id int(4) not null,name char(32)); [not null:必填项]
create table tb3 (id int(4) not null primary key,name char(32)) [创建表并指定主键]
create table tb4 (sex char(10) default 'woman' ) [创建表并给出默认值]

alter table tb1 add column sex char(1); [增加字段]
alter table tb1 drop column sex; [删除字段]
alter table tb1 add column (math float,english float); [增加多个字段]
alter table tb1 modify name char(20); [改变字段属性]
alter table tb1 change id id int PRIMARY KEY;[设置id为主键]
alter table tb1 add primary key(id,name); [添加主键]
alter table tb drop primary key;[删除主建]
alter table tb1 change sex sex char(1) default 'F';[设置sex字段默认值为‘F’]

增删改查操作

drop table tb1; [删除表]
delete from tb1 where name="Tom"; [删除记录]
delete from tb02; [删除表全部内容]
delete from tb1 where name="Tom";[删除]
truncate table tb1; [TRUNCATE在功能上与不带WHERE子句的DELETE语句相同(TRUNCATE速度快)]

insert into tt select * from test1 limit 1;
insert into tb1 values(1,'tom'); [插入数据]
insert into tb03(sex,name) values('m','boye'); [指定字段插入]
insert into chinese select * form math [注：两个表中字段一样]

update tb01 set id=5 where name="Jeap";[将名字为Jeap的id设置为5]
update tb01 set name='quanzhan.org',sex='M' where id=5;

SELECT count(1) NUM1 FROM information_schema.TABLES where table_schema='boye'; [查看表的数量]
SELECT TABLE_NAME T_NAME FROM information_schema.TABLES where table_schema='boye' order by T_NAME asc ; [查询数据库中所有表]
select column_name from information_schema.columns where table_schema='boye' and table_name='user' order by ordinal_position asc; [查询表中的所有列]

SELECT CASE WHEN 1> 0 THEN "yes " ELSE "no " END FROM book;
SELECT if(name=NULL,'',name) FROM book; [如果name=NULL输出'',否则输出name]
select concat('11','22','33'); [连接字符串，输出为112233]
select * from tb02 where id>3 and id<6; [条件查询]
select * from tb01 order by name desc; [显示所有记录并以name字段降序排序]
select * from math where name regexp '^a'; [匹配name字段以a开头的所有记录]
select id from tb01 where name like '%a%'; [查询名字中包含a的记录]_:匹配任意单个字符；
select id from tb01 where name like 'a_'; [查询名字中以a开头且只有两个字母的记录]%:匹配任意数目字符
select * from math order by name asc limit 0,3; [列出从第0条开始，显示3条记录以name字段升序排序]
select * from tb01 where id not in(1,55,5);
select * from user where username in ('admin','tom');
select * from A left join B on A.aID = B.bID [返回A表中所有记录及B表中bID等于aID的记录]
select * from mcate where not exists(select * from minfo where id <20);
select * from math where id in(select id from missions where name="tom");

select * from mcate where id in
(select id from minfo where name like 'a_');[select语句嵌套]

select * from minfo where 6 >any(select id from mcate);
[6大于mcate表中的任意一个id则为真，即输出minfo表全部记录]

select * from minfo where 6 >all(select id from mcate);
[6是否大于mcate表中的全部id]

select * from mcate where exists(select * from minfo where id <20);
[exists(select * from minfo where id <20):若 select 语句有输出结果，exists语句为真]

select sys_innt,tab_eng_nm,count(*) from ods_dic_dat_dict
group by sys_innt,tab_eng_nm having count(*) >1;

select * from student where name in
(select name from student group by name,sex having count(*) >1);

select id,year from minfo union select name,sex from tb3;
[将两个表中的相等字段的记录纵向合并在一起输出]

select tes2.name from test2 left join test on
test.name = test2.name where isnull(test.name);
[返回两个表中name不相同的数据]

select count(*) from math group by name,love;
[以name和love字段一致的为一组，分别计算每组有多少记录]

select math.id,math.u_id,math.name from math,missions
where math.id=missions.id and missions.name="tom";

select b1.*,b2.* from b1 left join b2 on b1.id = b2.id;
[输出b1中的全部记录及b2中id与b1相等的记录，并横向显示输出结果]

select b1.*,b2.* from b1 right join b2 on b1.id = b2.id;
[以右边b2为基准，右连接]

select math.id,class.name,math.math,chinese.chinese,politics.politics
from math left join
class on class.id = math.id
left join chinese on chinese.id = math.id
left join politics on politics.id = math.id;

[左右连接]
select t,t2
FROM(
select test.name t,test2.name t2
FROM test
left join test2
on test.name =test2.name
WHERE isnull(test2.NAME)
union
select test.name t,test2.NAME t2
from test
right join test2
on test.name = test2.name
where isnull(test.NAME)
) tb;

select * from (select t.name tname,t1.name t1name
from test t left join test1 t1 on t.name = t1.name
union select t.name tname,t1.name t1name from test1 t1
left join test t on t.name = t1.name) tt
where isNull(tname) OR isNull(t1name)[返回两个表中name不相同的数据]

select * from clrTheme as t1,
(
(select * from clrColor where 'sort' = 0) as c1,
(select * from clrColor where 'sort' = 1) as c2,
(select * from clrColor where 'sort' = 2) as c3,
(select * from clrColor where 'sort' = 3) as c4,
(select * from clrColor where 'sort' = 4) as c5
) where
t1.clrThemeId = c1.clrThemeId
and t1.clrThemeId = c2.clrThemeId
and t1.clrThemeId = c3.clrThemeId
and t1.clrThemeId = c4.clrThemeId
and t1.clrThemeId = c5.clrThemeId
order by t1.clrGroupId, t1.sort asc;