mysql学习笔记

mysql常用语句

创建用户
grant all provileges on database.table to 'user1'@'localhost' identified by "password";
grant select,insert,update,delete,index,alter,create,drop on databases.table to 'user2'@"%" identified by "password";
去掉权限
revoke drop,create,alter on databases.table from sally;
查看用户权限
show grants for 'user1'@'localhost';

创建数据库表
Customers(CustomerID,Name,Address,City)
Orders(OrderID,CustomerID,Amount,Date)

create table customers(customerid int(10) 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);

auto_increment 为自增键 指定这个属性的必然是索引列
primary key 主键 值必须唯一
unsigned 意思是它只能是0或者一个正数

查看数据库列表
show databases;
查看表详细信息
describe customers;
+------------+-----------+------+-----+---------+----------------+
| Field      | Type      | Null | Key | Default | Extra          |
+------------+-----------+------+-----+---------+----------------+
| customerid | int(10)   | NO   | PRI | NULL    | auto_increment |
| name       | char(50)  | NO   |     | NULL    |                |
| address    | char(100) | NO   |     | NULL    |                |
| city       | char(30)  | NO   |     | NULL    |                |
+------------+-----------+------+-----+---------+----------------+
describe orders;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| orderid    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| customerid | int(10) unsigned | NO   |     | NULL    |                |
| amount     | float(6,2)       | YES  |     | NULL    |                |
| date       | date             | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+


char(50) 为数据类型


插入数据
insert into customers values (NULL,'xiaozhang','street','guangzhou');
insert into order values (NULL,1,78.87,'2014-04-13');

查询数据
select * from customers;
+------------+-----------+---------+-----------+
| customerid | name      | address | city      |
+------------+-----------+---------+-----------+
|          1 | xiaozhang | street  | guangzhou |
+------------+-----------+---------+-----------+

mysql> select * from orders;
+---------+------------+--------+------------+
| orderid | customerid | amount | date       |
+---------+------------+--------+------------+
|       1 |          1 |  78.87 | 2014-04-13 |
+---------+------------+--------+------------+


select name,city from customers;
+-----------+-----------+
| name      | city      |
+-----------+-----------+
| xiaozhang | guangzhou |
+-----------+-----------+




where子句比较运算符
= > < <= >= !=或者<>  'IS NOT NULL'
select * from orders where customerid=1;
select * from orders where customerid>=1;
select * from orders where customerid!=1;

between     
in  
not in
like
not like
regexp
and
or

select * from orders where customerid=1 or customerid=2;
select * from orders where date like "2014%";

更新数据库记录
update books set price = price*10
update customers set address = 'dalian' where customers = 1;
update user set password=password("123123") where user="user1";


增加一个新列
alter table orders add tax float(6,2) after amount;


删除表数据
delete from table;
delete from table where customerid=1;

删除表
drop table table;
drop database database;

---------------------------

mysql权限:
数据:
select
insert
update
delete
file     允许从文件读入表

结构:
create
alter    允许修改表结构
index    允许用创建索引
drop  
create temporary tables 允许在create table语句中使用temporary关键字
show view
create routine
alter routine
execute
create view
event
trigger

管理:
grant
super
process
reload
shutdown
show databases
lock tables
refurences
replication client
replicaton slav
create user