mysql基础1.安装-权限-创建-查询.
时间：2006-02-03 来源：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 < c:\bookorama.sql
从光盘载入可能用-d
创建数据库表
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; # 必须为结果定义一个别名字。
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 < c:\bookorama.sql
从光盘载入可能用-d
创建数据库表
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; # 必须为结果定义一个别名字。
相关阅读 更多 +
