mysql命令

进入mysql:mysql -h localhost -u root -p 前提是已经将mysql命令路径加入到环境变量中。
密码修改：
create database db;
show databases;
drop database db;
use db;
select database();---------查看目前正在使用的数据库。
select now();--------------得知当前的日期和时间
select user();-------------得知当前登建网的用户


表格：
create table abc (number int,name char(10));
字段名称 数据类型与大小 是否可以为空    是否为主键              默认值                 备注
         int ,char(10)   not null      primary key            default 'abc'          auto_increment自动增长
show tables;---------------显示数据库中的所有表
describe abc;
desc abc;
drop table abc;
create table user(serial int unsigned auto_increment primary key,name varchar(10) not null,gender enum('F','M') not null,birthday date,phone char(20));
desc user;
insert into user values(NULL,'黄蒙','F','1975-10-13','0922555655');
select * from user;
select name from user;
select name,phone from user;
delete from user;------------便可删除表内的所有数据
drop table user;-------------删除表
delete from user where name='方正';
deleete from user where phone='05556542555';
update user set phone='093856545' where name='令人公';--------修改数据
alter table user add zipcode int unsigned;---------新添加一列
desc user;
alter table user rename usr;-----修改user表名称为usr;
alter table user modify phone varchar(30);
alter table user change phone mobile varchar(20);
alter table user drop name;-----删除name字段
alter table user add address char(100);
脚本程序---create_zip.sql
drop table if exists zip;
create table zip{
id smallint unsigned not null auto_increment primary key,
zip int unsigned;
zone char[10];
area char[10];
};
mysql db -h localhost -u root -p < c:\create_zip.sql---执行脚本程序，批量生成
********************************
从命令行导入到mysql数据库：：
mysql -u root -p youju
use youju
source h:\youju.sql
进入mysql安装所在的bin目录下。
mysql -u root -p youju > youju.sql;
*********************************
mysql db -h localhost -u root -p <c:\insert_zip.sql
导出数据---
mysqldump db -h localhost -u root -p > c:\db_bak.sql
select * from zip where zone='桃园';
select zip,area from zip where zone='桃园';
select * from zip where zone like '台%';一个%表示零个，一个或任意多个任意字符。
select * from zip where zone like '%和';
select * from zipo where area not like '台北%';
select zip,zone from zip where zone like '台%' and area='东势';
select zip,zone,area from zip where zip >=350 and zip <=359;
select * from zip where zone like '%海%' or area like '%海%';
--连接
select * from user join zip where user.zipcode=zip.zip;
select user.name,zip.zone,zip.area from user join zip where user.zipcode=zip.zip;
单向连接---A left join B on,则意思是指用A与B进交集的操作，得出的结果一定包含所有A的数据。
select customers.id,customers.name,orders.id from customers left join orders on customers.id =oredrs.customerid;
select customers.id,customers.name,orders.id from customers left join orders on customers.id =oredrs.customerid where isnull(orders.id);
万用字符:_代表一个英文字符或者半个中文字符，要通知mysql使用万用字符，必须使用操作符like 或 not like;
统计函数
MAX(column),MIN(column),SUM(column),AVG(column),STD(column)标准差。
select AVG(zip) from zip;
select AVG(zip) from zip where zip>=100 and zip <=300;
select zone,MAX(zip) from zip group by zone;------分组
select zone,count(zip) from zip group by zone;
having子句用来限制分组，而where子句是用来过滤掉最后的输出数据。
select zone,count(zip) from zip where zone like '台北%' group by zone having MAX(zip) <300;
select zone,count(zip) as cnt_zip from zip group by zone order by cnt_zip desc;