mysql基础1.安装-权限-创建-查询.

dalarge

如果改变安装路径:
d:\programs\mysql\bin\mysqld --basedir d:\programs\mysql
要以windows服务来运行mysql，必须创建my.ini文件，保存在windows的主目录下，文件内容如下:
[mysqld]
basedir=d:\programs\mysql\bin\
datadir=d:\programs\mysql\data\
安装服务，启机后自动运行,在\bin\mysql-nt -install      net start mysql
创建数据库：create database DATABASENAME
删除数据库：drop database DATABASENAME
设置密码：mysqladmin -u root password YOUPASSWORD
如果mysql安装在其他主机上 -h HOSTNAME
登陆数据库：mysql -u root -p   
重新装载：mysqladmin -u root -p reload
权限：grant revoke
with限制每个用户每小时的操作数量
max_queries_per_hour n
max_updates_per_hour n
max_connections_per_hour n
gran all
on *
to FRED identified by 'PASSWORD'
with grant option;
以上命令授予FRED，密码为password的用语使用所有数据库的权限，并允许他向其他人授予这些权限。
撤消用户
revoke all privileges,grant
from FRED;
grant usage
on books.*
to sally identified by 'password';
创建一个没有任何权限的用户sally
grant select,insert,update,delete,index,alter,create,drop
on books.*
to sally;
revoke alter,create,drop
on books.*
from sally;
revoke all
on books.*
from sally;
创建一个web用户
grant select, insert, delete, update
on books.*
to bookorama identified by '123';
编辑和载入mysql脚本,在未登陆状态：
mysql -u bookorama -f books -p
创建数据库表
create table TABLENAME(COLUMNS)
create table customers
( customerid int unsigned not null auto_increment primary key,
  name char(50) not null,
  address char(100) not null,
  city char(30) not null
);
create table orders
( orderid int unsigned not null auto_increment primary key,
  customerid int unsigned not null,
  amount float(6,2),
  date date not null,
);
create table books
( isbn char(13) not null primary key,
  autor char(50),
  title char(100),
  price float(4,2),
);
create table order_items
( orderid int unsigned not null,
  isbn char(13) not null,
  quantity tinyint unsigned, #范围在0-255之间的一个整数
  primary key (orderid,isbn)
);
create table book_reviews
( isbn char(13) not null primary key,
  review text
);
创建索引
create index index_name
on table_name (index_column_name[(length)][asc|desc])
在数据库中插入数据
insert into table [(column1,column2,column3)] values (value1,value2,value3);
insert into customers values (null, 'julie smith', '25 oak street', 'airport west'x);
insert into customers(name, city) values ('melissa jones','nar nar goon north');
insert into customers
set name='michael archer',
    address='12 adderley avenue',
    city='leeton';
更新数据库记录
update table_name
set column =price*1.1 [where column=][limit number];
update books
set autor ='wang' where autor = 'thomas down';
删除数据库记录
delete from table_name #全部删除
[where column_name='';]  
创建后修改表
alter table table_name
add[colume]column_description[first|after column]# 默认在最后
alter table orders
add tax float(6,2) after amount;
alter table customers
modify name char(70) not null;
alter table orders
drop tax;
删除数据库中的记录
delete from table_name #删除所有记录
delete[quick][ignore][low_priority]
from table_name [where condition]
[limit number]
[order byu order_cols]
删除表
drop table table_name;
删除数据库
drop database database_name;
多表关联
select orders.orderid, orders.amount orders.date
from customers,orders   #,等价与inner join 或者 cross ioin 有时候也称为full join
where customers.name='julie smith'
and books.customers.customerid = books.orders.customerid;   # books.可以省略
select customers.name
from customers, orders, order_items, books
where customers.customerid = orders.customerid
and orders.orderid = order_items.orderid
and order_items.isbn = books.isbn
and books.title like '%java%';    #  关联条件的数量应该比关联表的数量少一个
查找不匹配行，左关联
从来没有定单的可户，或从来没被订购过的图书，左关联是在两个表之间指定关联条件下匹配的数据行，
如果右边的表中没有匹配行，结果中就会增加一行，该行右边的列为null
select customers.customerid,customers.name, orders.orderid
from customers left join orders
on customers.customerid = orders.customerid;
如果要查没有定单的客户
select customers.customerid, customers.name
from customers left join orders
using (customerid)
where orders.orderid is null;  #希望使用using子句，两个表中的列必须有同样的名称
表的别名aliases
select c.name
from customers as c , order as o ,order_items as oi, books as b
where c.customerid= o.customerid and o.orderid= oi.orderid and oi.isbn= b.isbn
and b.title like '%java%';
当要关联一个表到表本身的时候就必须使用别名，如果要查找住在同一个城市的顾客
select c1.name , c2.name, c1.city
from customers as c1, customers as c2
where c1.city = c2.city
and c1.name != c2.name;
以特定的顺序获取数据
select name,address
from customers
order by name[asc|desc];
分组与合计数据
select avg(amount)
from orders;
avg()指定列的平均值
count()列中非空值的列数，如在列前加distinct将得到不同值的列数，(*)所有列数。
min()
max()
std()指定列的标准背离值
sun()指定列的所有值的和
要获取更详细的信息，用grouo by 子句
select customerid, avg(amount)
from orders
group by customerid
order by amount; #看谁定单平均总量就高
可以用having子句测试合计的结果
having avg(amount) > 50;
选择要返回的行
select name from customers
limit 2, 3 ;  # 反回三行，从第2行开始 ，行号是从0开始的。
limit不是ansi sql的一部分，的能导致与其他数据库不兼容。
子查询
select customerid, amount
from orders
where amount = (select max(amount) from orders);
等价于
select customerid, amount
from orders
order by amount desc  #descent下降，遗传  ， ascent上升
limit 1;  # linit 0,1;
子查询操作符
any|some  select c1 from t1 where c1 > any (select c1 from t2);
all                                    all
in select c1 from t1 where c1 in (select c1 from t2);
关联子查询
select isbn,title
from books
where not exists
(select * from order_items where order_items.isbn=books.isbn);
# 检索还没有被订购的图书，内部查询中使用外部查询的结果。
使用子查询作为临时表
select * from
(select customerid, name from customers where city ='box hill')
as box_hill_customers;  # 必须为结果定义一个别名字。






本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/13535/showart_71532.html