mysql clr_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;