数据库 MySQL语句
数据库的基本操作：
-- 增、删、改、查
-- 数据库的存储方式：表格
-- 数据库的基本操作语句：
-- 启动数据库服务
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)>1
select *,avg(sno),avg(id) from students group by sno having avg(sno)>2
http://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 studenta
rename 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
