MySQL常见命令

Bin目录下：

Netstat –cno ：查看端口号
Mysql –u root –p 回车：直接登录到本机
Mysql –h 192.168.12.7 –u root –p root：连接远程服务器
Mysql –-h ：帮助
Mysql>select current_date ; :查看日期
Mysql>Select now（） ； 时间
Mysql>Create database name;
Mysql>Show databases ;
Mysql>Drop database name;
Mysql>Use name;
Mysql>Show tables
Mysql>Create table test(id int ,name varchar(20),age int，AUTO_INCREMENT);
Mysql>primary key (id)
Mysql>Describe test; 进入表

插入

Mysql>insert into test(id,name,age) values(1,’cm’,23);
Mysql>insert into test(id,name) values(1,’cm’);
Mysql>insert into test values(1,’cm’,23);

更新

Mysql>update test set name = ‘xxx’,age = 33 where id = 112;
Mysql>update test set age = age+1 where id >0; 全局一起变

删除记录

Mysql>delete from test where id = 112;

修改表：

改名
Alter table tset3 rename person;
添加列
Alter table test3 add password varchar(20);
添加唯一索引
Alter table tb_wechat_auth add unique index(open_id);
修改表字段默认值
alter table users_info alter column role_id set default 1;

删除表字段：

alter table 表名 drop column 字段名；

查询：

Mysql>select * from test
原sql语句查询（执行外部脚本）
Source sql语句路径

分页查询：

Mysql>select id ,name,age from test limit 1,3;

模糊查询：

Mysql>select id ,name,age from test where name like ‘t%’

Mysql>select id ,name,age from test where name (not)like ‘t%’
Mysql>select id ,name,age from test where name like ‘t_%’ 转义符
Mysql>select id ,name,age from test where Name is null;
Mysql>select id ,name,age from test Order by desc :降序
Mysql>select id ,name,age from test Order by Asc ：升序

聚集函数查询

Select count(*) from test ;
Select max(age) from test ;
Select min(age) from test ;
Select avg(age) from test ;
Select sun(age) feom test;

分组查询（统计报表）通常和聚集函数组合使用

1.创建表
2.group by
Mysql->select * from customs group by age;
3.对分组后的数据再次过滤
Mysql->select max(salary),min(salary),age fro customs group by age having age >10;

连接查询：

1.创建customs表，order表
2.添加外加约束(外键约束，保证数据一致性)
Mysql->alter table order add constraint foregin key(cid) reference customer(id);
3.笛卡尔值查询(没有连接条件)
Mysql->select a., b. from customer a,order b ;
4.等值连接查询
Mysql->select a., b. from customer a,order b where a.id = b.cid;
5.使用join连接查询(内连接)
Mysql->select a.*id, a.name,b.id,b.orderno from customer a (inner join)join order b on a.id = b.cid;
6.外连接（自动补全数据）（不支持全外连接）
有些客户没订单，有些订单没客户，查询的时候也要显示出来。
左外连接：左边看起，右边数据没有就补空值
Mysql->select a.id,a.name,b.id,b.order,b.price from customer a left outer join order b on a.id = b.cid;

右外连接：右边看起，左边数据没有就补空值
Mysql->select a.id,a.name,b.id,b.order,b.price from customer a right outer join order b on a.id = b.cid;

集合查询（两张表首尾连接）

1. union 并集操作（要求两表的列数相同）
   mysql->select id,name from customer union select id,orderno from order;
2. union all ,包含重复数据
   mysql->select id,name from customer union all select id,orderno from order;
3. minus (减法) intersect(交集)不支持

范围运算

1. and or
2. between and
   mysql->select id,name from customerwhere id between 4 and 6;
3. in 等价于 or or or
   mysql->select * from customer where id in (1,2,3,4);
4. any 集合任何一个
   mysql->select * from order where cid > any(select id from customer like ‘t%’);
5. all 集合全部元素
6. mysql->select * from order where cid > all(select id from customer like ‘t%’);

去除重复元素

1. mysql-> select distinct age from customer;
2. mysql-> select count(distinct age ) as ages from customer;

事务支持：

只有当表的类型是INNODB的时候，才支持事务

修改表的类型为INNODB的SQL：
alter table product_ ENGINE = innodb;

查看表的类型
show table status from how2java;