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;