查看当前实例下有多少个数据库
show databases;
查看表
show tables；
创建数据库
create database jili;
使用数据库
use jili;
删除数据库
drop database jili;
建表
create table xxxx (字段 字段类型。。。。。) charset=utf8;
ex
create table jili (id int,name varchar(100),sex char(1),age int,address varchar(200)) charset=utf8;
create table jili2 (id int primary key,name varchar(100),sex char(1),age int,address varchar(200)) charset=utf8;
查询表结构
desc 表名;
删除表;
drop table 表名;
DML语句
insert 插入
insert into jili values (1,'王大为','m',73,'辽宁铁岭');
update
update 表名 set = 新值 where 修改字段 = 老值;
delete
delete from 表名 where 删除字段 = 值;
delete * from 表名
select * from q /*执行表格q*/
create table q (empno number(30),ename varchar2(30),job varchar2(30))/*创建表格q 加表结构*/
insert into q (empno,ename,job)values (1,'小明','manager') /*插入*/
insert into q (empno,ename,job)values (2,'小红','maishi') /*插入*/
update q set job='manager'where empno=1/*更改表内数据*/
delete from q where empno=2 /*删除一行*/
alter table q add(mgr number(30))/*增加一列*/
alter table q modify(mgr varchar2 (30))/*修改成字符串模式*/
update q set mgr='王' where empno=1 /*列表插值*/
alter table q drop column mgr/*删除一列*/
varchar2 /*字符串类型*/ number/*数字类型*/ date /*日期类型*/ int /*整数类型*/
insert into china values (1,'北京');
insert into china values (2,'上海');
insert into china values (3,'广州');
insert into china values (4,'深圳');
insert into china values (5,'哈尔滨');
insert into china values (6,'长春');
insert into china values (7,'沈阳');
insert into china values (8,'呼和浩特');
insert into china values (9,'天津');
insert into china values (10,'石家庄');
insert into china values (11,'郑州');
insert into china values (12,'合肥');
limit 语句 查询指定的行数
order by desc降序 asc升序 默认asc不用打出来
地区编号和尝试对应关系
东北 1 华北 2 华中 3 华南 4 西南 5 西北 6
create table china2 (id int , name varchar(50) , local_num int) charset=utf8;
insert into china2 values (1,'北京',2);
insert into china2 values (2,'上海',4);
insert into china2 values (3,'广州',4);
insert into china2 values (4,'深圳',4);
insert into china2 values (5,'哈尔滨',1);
insert into china2 values (6,'长春',1);
insert into china2 values (7,'沈阳',1);
insert into china2 values (8,'呼和浩特',6);
insert into china2 values (9,'天津',2);
insert into china2 values (10,'石家庄',2);
insert into china2 values (11,'郑州',3);
insert into china2 values (12,'合肥',3);
create table local_num (id int,name varchar(20)) charset=utf8;
insert into local_num values (1,'东北');
insert into local_num values (2,'华北');
insert into local_num values (3,'华中');
insert into local_num values (4,'华南');
insert into local_num values (5,'西南');
insert into local_num values (6,'西北');
insert into local_num values (7,'日本省');
select b.name, a.cout from (select count(1) cout ,local_num from china2 group by local_num) a ,local_num b where a.local_num = b.id;
local_num
select b.name , count(a.name) from local_num b left join china2 a on a.local_num=b.id group by a.local_num;
join (join 内连接 外链接 全连接(oracle)) ---- 空值
外连接----left join / right join ---驱动表 匹配表
如果你想展示一个表中的所有内容 那么你就讲他放到驱动表的位置上
select * from china2 a left join local_num b on a.local_num = b.id;
create table jili (empno int,name string,job string)
insert into jili (1,王坤华)