Mysql常用命令(2)

--------数据库
-- 连接数据库
-- help or h 查看帮助信息
-- c 清除当前输入的语句
mysql -hhostname -uroot -p
******

-- 修改root密码
set password = password('123456');

-- 查看一下数据库服务器上有哪些数据库
SHOW DATABASES;

-- 删除数据库 必须确定再确定此库已经没有任何需要了。
DROP DATABASE IF EXISTS `s32`;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `s32`;

-- 选择数据库
USE `s32`;

--------------------- CREATE建表语句------------------------------
 --CREATE建表语句
CREATE TABLE IF NOT EXISTS `user`(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    password CHAR(32) NOT NULL,
    sex TINYINT NOT NULL DEFAULT 1,
    age TINYINT NOT NULL DEFAULT 0,
    province VARCHAR(255) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

-- 表引擎
MyISAM  存取速度快      不支持事务   表锁机制   不支持外键
InnoDB  存取速度稍慢    支持事务     行锁机制   支持外键

-- unsigned 无符号
-- zerofill 前导零 设置了此属性之后，会自动加上unsigned
-- auto_increment 自增
-- not null 不能用null来作为值
-- default  设置默认值
-- 查询数据

-- 增insert into 删delete 改update 查select

insert into tabname (字段列表) values(值列表);

delete from tabname where 表达式;

update tabname set 字段=值,字段=值,字段=值 where 表达式;

select 字段列表 from tabname [where 表达式]; 



---------------------ALTER TABLE修改表----------------------------------------
-- 修改表名
ALTER TABLE `旧表名` RENAME [AS] `新表名`;
alter table `user` rename `s32_user`;

-- 修改字段数据类型或字段属性
ALTER TABLE `表名` MODIFY `字段名` 字段属性...
alter table `user` modify `sex` int not null default 1;

-- 修改字段数据类型或字段属性及修改字段名 change
-- 改名
alter table `user` change `sex` `xingbie`  TINYINT NOT NULL DEFAULT 1;
alter table `user` change `xingbie` `sex`  TINYINT NOT NULL DEFAULT 1;
-- 修改字段属性
alter table `user` change `sex` `sex` INT NOT NULL DEFAULT 1;
-- 增加字段 add

-- 添加一个密码字段
alter table `user` add `password` char(32) not null after `name`;
添加订单状态字段
alter table `dds_order_goods` add `state` varchar(255) default '未付款';
-- 添加一个test字段
alter table `user` add `test` text;
-- 删除test字段
alter table `user` drop `test`;
-- 改变字段位置 FIRST 第一 AFTER 在......后面
alter table `user` modify `password ` char(32) not null first;
alter table `user` modify `password` char(32) not null after `name`;

--------------------------UPDATE修改数据--------------------------------------------
-- 修改和删除是危险动作，一定要加上删除哪些记录：一定要加上where条件
-- 修改数据

update tabname set 字段=值,字段=值,字段=值 where 表达式;
UPDATE `user` SET `name`='桐桐',`sex`='1' where `id`='6'; 

---------------------------------select查询表----------------------------------------

-- 查询表中所有字段
mysql> select * from user;

-- 查询指定字段
select name from user;

-- 查询指定记录
select id,name,password,sex,age,province from user where id='1';

-- 表达式中的where条件子句的方式有：
-- > < >= != 
-- 查询id大于7的记录

select id,name,password,sex,age,province from user where id>'7';

-- 查询id小于7的记录
select id,name,password,sex,age,province from user where id<'7';

select id,name,password,sex,age,province from user where id>='7';
select id,name,password,sex,age,province from user where id<='7';

-- BETWEEN AND 进行范围比较查询
-- NOT BETWEEN AND
select id,name,password,sex,age,province from user where `id` between '2' and '10';
select id,name,password,sex,age,province from user where `id` not between '2' and '10';

-- IN
-- NOT IN进行范围比对查询
select id,name,password,sex,age,province from user where `id` IN ('2','5','8','100','22','7','10');

-- 使用LIKE进行模糊查询
-- _ 代表一个字符
-- % 代表任意多个字符
select id,name,password,sex,age,province from user where `name` like '莫_';
select id,name,password,sex,age,province from user where `name` like '莫__';
select id,name,password,sex,age,province from user where `name` like '莫%';

-- 查询出所有王姓朋友
select id,name,password,sex,age,province from user where `name` like '王%';

-- 查询名字中还王的朋友
select id,name,password,sex,age,province from user where `name` like '%王%';
select id,name,password,sex,age,province from user where `name` like '%王';

-- 使用ORDER BY 对查询结果排序
-- order by `字段名`
-- desc 降序
-- asc  升序 默认
select id,name,password,sex,age,province from user order by `id` desc;

-- 先按年龄大小排序 降序，再按id排序 升序
select id,name,password,sex,age,province from user order by `age` desc, `id` asc;

-- 限制查询结果的数量 (行数)
-- limit $offset,$num
select * from user limit 0,5;
select * from user limit 5,5;
select * from user limit 10,5;
select * from user limit 15,5;

-- 将所有的年龄全部查询出来
select age from user;
-- 查询结果不重复 distinct
-- 注意：所有字段的内容必须全部一致,才会被去掉
select distinct age from user order by age;

-- group by 分组查询
-- 单独使用group by 没有什么用, 一定要配合下面的集合函数一起使用才有意义

-- 集合函数 count()记录数  avg()平均数  max()大 min()小 sum()和
    select count(*) from user;
    select avg(age) from user;
    select max(age) from user;
    select min(age) from user;
    select sum(age) from user;


    select * from user group by province;

-- 计算一下各个省份分别有多少人
    select province,count(*) from user group by province order by count(*) desc;
-- 计算一下各省分的用户的平均年龄
    select province,avg(age) from user group by province;

-- having 是在group by 的基础上再进行过滤
-- 把人数大于3个的省份给我查询出来
    select province,count(*) from user group by province having count(*)>3;
    

-- distinct-- 查询结果不重复 distinct
-- 注意：所有字段的内容必须全部一致,才会被去掉
    select distinct sex from user;
-- concat() 拼接
select concat(`id`,',',`name`,',',`sex`) from user;

-- 给字段或表名取别名 as
select user.id,user.name,user.sex,user.age,province from user; 

select u.id,u.name,u.sex,u.age,u.province from user as u; 

-- 给字段取别名 
select u.id i,u.name n,u.sex s,u.age a,u.province p from user as u; 


-- 两个表关联查询一下
-- 查询用户表的的用户分别有哪些情人
select u.id, u.name, m.name
from user u, mistress m
where u.id=m.user_id;

select u.id, u.name, m.name
from user u, mistress m
where u.id=m.user_id and u.id=3;