Mysql 使用

web数据库:
1)表格:
列 行 值 键(唯一的Id);
2)表关系:
外键表示俩个表的关系;（设计web数据库）;
3)数据库基础语句:
*sql语句不区分大小写
登录到Mysql:mysql -h locahost -u username -p password;
查看所有库:
show databases;
创建数据库和用户:
create database db_name;
GRANT、REVOKE 授权、取消用户权限;
grant:
grant all/usage
on item（指定数据库）
to username identified by 'password'
with grant option
创建表:
use db_name;
UNSIGNED 属性是将数字类型无符号化。用于主键是自增的类型。auto_increment 自增;
Databases(数据库)、table(表)、column(列)、index(索引)、Alias(别名)
primary key 主键;
float(6,2) 6位数,小数点位2位;
mysql> 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 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);
mysql> create table books
    -> (
    -> isbn char(13) not null primary key,
    -> author char(50),
    -> title char(100),
    -> price float(4,2))
    -> ;
mysql> create table order_items
    -> (
    -> orderid int unsigned not null,
    -> isbn char(13) not null,
    -> quantity tinyint unsigned,
    -> primary key (orderid,isbn)  
    -> );
mysql> create table book_revicews(
    -> isbn char(13) not null primary key,
    -> review text);
show tables;查看数据库
describe <>; 查看详细信息;
添加表: (在use情况下)
insert into <table> ([id],[name]) values ('','');&& insert into <table> set id = '',name = '';
mysql> insert into customers values (3,'Julie Smith','25 Oak Street','Airport West'),
    -> (4,'Alan Wong','1/47 Haines Avenue','Box Hill'),
    -> (5,'Michelle Arthur','357 North Road','Yarraville');
mysql> insert into orders values
    -> (NULL,3,69.98,'2007-04-02'),
    -> (NULL,1,49.99,'2007-04-15'),
    -> (NULL,2,74.98,'2007-04-19'),
    -> (NULL,3,24.99,'2007-05-01');
mysql> insert into books values
    -> ('0-672-31697-8','Michael Morgan','Java 2 for Professional Developers',34.99),
    -> ('0-672-31745-1','Thomas Down','Installing Debian GUN/LINUX',24.99),
    -> ('0-672-31509-2','Pruitt,et al.','Teach Yourself GIMP in 24 Hours',24.99),
    -> ('0-672-31769-9','Thomas Schenk','Caldera OpenLinux System Administration Unleashed',49.99);
mysql> insert into order_items values
    -> (1,'0-672-31697-8',2),
    -> (2,'0-672-31769-9',1),
    -> (3,'0-672-31769-9',1),
    -> (3,'0-672-31509-2',1),
    -> (4,'0-672-31745-1',3);
mysql> insert into book_revicews values
    -> ('0-672-31697-8','The Morgan book is clearly written and goes well beyond most of the basic Java books out there.');
查询表:
select [id]，[name],* from <> *代表全部;
select * from where [id]=1;  指定特殊条件查询; 可以加逻辑运算符;
特定的顺序查询:
select [id] from customers order by name; 升序查询;
select [id] from customers order by name desc; 降序查询;
更新表数据:
updata <table> set name='' where id = 1 ;
修改表数据:
alter table customers modify name char(70) not null;
alter table customers [add,drop] name char(70) not null;
删除表:
delete from customers where id = 1;
表的删除：
drop tablet customers;
库的删除:
drop databases db_name;