mysql nio 语句 windows命令 信息 存储方式 sta into
数据库的基本操作:-- 增、删、改、查-- 数据库的存储方式:表格-- 数据库的基本操作语句:-- 启动数据库服务 net start mysql-- 关闭数据库服务 net stop mysql-- 通过windows命令窗口连接数据库 mysql -uroot -p123456-- 查看数据库 show databases-- 创建数据库 create database value-- 删除数据库 drop database value-- 查看创建数据库 show create database value-- 进入数据库 use value-- 使用资源文件 source c:/mysql.sql-- 查看数据库已有表 show tables-- 指定数据库查看表 show tables from databaseVaule-- 查看表结构 desc tablesValue-- 查看建表语句 show create table tablesValue-- 创建表 create table tablesValue( value int(numb) primary key auto_increment, value double not null, value varchar(numb), value date, value char unique )-- 添加数据 insert into tablesValue (value,value,value) values(XXX,XXX,XXX); insert into tablesValue values(XXX,XXX,XXX,XXX,XXX); /* insert into students (id,name,sno,birthday) values(1,"hnn","144215",0820) insert into students values(2,"zhangsan",144216,0624); */-- 查看表信息 select * from tablesValue select value,value from tablesValue-- 有条件查询 select * from tablesValue where value=values /* select id,name,sno from students where sno = 144215 */-- 局限性查询 select value,value from tablesValue-- 局限性有条件查询 select value,value from tablesValue where value=values-- 多条件使用字符 and or xor not /* select id,name,sno from students where sno = 144215 select id,name,sno from students where sno = 144215 and id = 1 select id,name,sno from students where sno = 144215 xor id = 1 select id,name,sno from students where sno = 144215 or sno = 144216 select id,name,sno from students where not sno = 144215 and not id = 2 */-- 清除重复记录 select distinct value,value from tablesValue /* select distinct sno from students select sno from students */-- 按照规定排序 select * from tablesValue order by value asc/desc /* select * from students order by id desc */-- 按照指定值查询 select * from tablesValue where value in(XXXX,XXXX,XXXX) /* select * from students where id in(1,2) */-- 指定包含第几条开始查询多少条信息 select * from tablesValue limit x,y /* select * from students limit 0,3 */-- 聚合函数 min() max() sum() avg() count() 对数据库中数字类型的字段取最大值可以直接用: SELECT MAX(field-name) FROM table-name WHERE conditions 而对于其它类型的字段要使用以下语句: SELECT MAX(CAST(field-name AS UNSIGNED)) FROM table-name WHERE conditions /* select count(*) from students select count(id) from students select max(sno) from students select sum(id) from students select avg(sno) from students */-- 过滤聚合值的记录行* select * from tablesValue order by value asc/desc having sum(value)>100/* select * from students group by id having sum(id)>1select *,avg(sno),avg(id) from students group by sno having avg(sno)>2http://www.cnblogs.com/yank/p/3672478.html */-- 连接多个查询结果 select.... union select ... /* select sum(id) as sumset from students union select avg(sno) from students */-- 对当前表添加一列 alter table tablesValue add column value varchar(12) not null /* alter table students add column sname varchar(12) not null */-- 删除当前表中的某列 alter table tablesValue drop column value /* alter table students drop column sname */-- 修改当前列的定义 alter table tablesValue modify value varchar(11) not null /* alter table students modify sname int(11) not null 列表不能为空 */-- 修改列的定义和名字 alter table tablesValue change oldvalue newvalue char(10) not null /* alter table students change id idd char(10) not null */-- 添加主键 alter table tablesValue add primary key(id)-- 删除主键 alter table tablesValue drop primary key-- 改变表名 alter table tablesValue rename tablesValue rename table tablesValue to tablesValue /* alter table students rename studentarename table studenta to students */-- 删除表 drop table tablesValue -- 更新表数据 update tablesValue set value=XXXX update tablesValue set value=XXXX,value=XXXX where value=XXXX -- 删除表数据 delete from tablesValue delete from tablesValue where value=XXXX -- 两个表之间的关联 select * from tablesValue,tablesValue select * from tablesValue t1,tablesValue t2 select t1.value,t2.value from tablesValue t1,tablesValue t2 select t1.value,t2.value from tablesValue as t1,tablesValue as t2 -- 内连接 select * from tablesValue t1 join tablesValue t2 on t2.value = t1.value where t1.value = 10000 select * from tablesValue t1 inner join tablesValue t2 on t2.value = t1.value where t1.value = 10000 -- 外连接 左右无区别 select * from tablesValue t1 left outer join tablesValue t2 on t2.value = t1.value where t1.value = 10000 select * from tablesValue t1 right outer join tablesValue t2 on t2.value = t1.value where t1.value = 10000-- 创建表外键 create table tablesValue( value int(numb) primary key auto_increment, value double not null, value varchar(numb), value date, value char unique, foreign key (value) references tablesValue(value) ) alter table tablesValue add foreign key (value) references tablesValue(value) alter table student add foreign key (Tno) references teacher(Tno) //后面的表的属性是主键,前面的表的属性不是主键。属性的类型的长度必须一样-- 事务控制语句 start transaction select * from tablesValue where value=1 delete from tablesValue where value=1 select * from tablesValue where value=1 rollback //否认上面四句对数据库的更改,数据库回到它未执行这四条语句前 select * from tablesValue where value=1 start transaction select * from tablesValue where value=1 delete from tablesValue where value=1 select * from tablesValue where value=1 commit //同意上面四句对数据库的更改 select * from tablesValue where value=1 -- 控制自动提交 set autocommit = off set autocommit = on set session autocommit = off set session autocommit = on -- 创建视图 create view tablesValue as select value,value from tablesValue select * from tablesValue create view tablesValue (value,value) as select value,value from tablesValue-- 修改视图 alter view tablesValue (value,value) as select value,value from tablesValue -- 删除视图 drop view tablesValue-- 展现创建视图 show create view tablesValue
数据库 MySQL语句
来源: http://www.bubuko.com/infodetail-2105100.html