mongodb常用指令

\c：結束當前mysql命令
\g：== go 執行 ==
cls：清空界面
cd：進去目錄
dir：查看檔案

匯出庫

C:\Users\fwhf-pc>mysqldump -uroot -p test > e:\a.sql

匯入庫

C:\Users\fwhf-pc>mysql -h localhost -uroot -p test < e:\a.sql

匯入資料

source e:\a.sql

匯出單張表

C:\Users\fwhf-pc>mysqldump -uroot -p test user > e:\d.sql

匯出所有表結構

C:\Users\fwhf-pc>mysqldump -uroot -p -d --add-drop-table test > e:e.sql

匯出單張表結構

C:\Users\fwhf-pc>mysqldump -uroot -p -d test user > e:\f.sql

查看所有庫

show databases

創建庫

create database 庫名

洗掉指定庫

drop database 庫名

使用指定庫

use 庫名

查看當前使用的庫

select database()

查看當前庫下所有表

show tables

創建表

create table 表名(id int(8) , username varchar(45) , password char(32)); 

洗掉表

drop table 表名

查看表結構

desc 表名

查看建表陳述句

show create table 表名

查看引擎

show engines

修改表名字

alter table 原來的表名字 rename 新的表名字

修改表欄位

alter table 表名 change 原來的欄位 新的欄位(資料型別)

修改表欄位資料型別

alter table 表名 modify 原來的欄位(新的資料型別)

插入新的欄位

alter table 表名 add 新的欄位(資料型別)

洗掉欄位

alter table 表名 drop 你要洗掉的欄位

在指定欄位后面插入新的欄位

alter table 表名 add 新欄位(資料型別) after 欄位名

在開頭查如新的欄位

alter table 表名 add 新欄位(資料型別) first

資料庫索引

alter table user add index(money)

普通索引

alter table user add unique(province)

唯一索引

alter table user add primary key(id)

主鍵索引

alter table user add fulltext(username)

全文索引

查看索引

show index from user \G

復制表

create table newuser select * from user

不僅僅復制表結構還有資料

create table newuser like user

復制表結構

修改自增的初始值

alter table user auto_increment=起始值

增

insert into user(id,name,money,province,age,sex)values(1,'名字',20,'中國',12,1);
insert into user values(2,'名字',12,'山東',56,0);
insert into user(name,money,province,age,sex)values('名字',23,'山西',45,1),('名字2',34,'河南',32,0),('名字3',23,'河北',12,0);

刪

delete from user where id = 7;
delete from user where money=34 or age=12;

改

update user set name='new名字' where id = 2;
update user set name='new名字',money=99,province='哪里' where id = 2;
update user set money=money-9 where name = 'new名字';

查

select * from user;
select name , age from user;
select distinct name from user;
select * from user where age = 32;
select * from user where age between 20 and 40;
select * from user where age > 32 and age < 50;
select * from user where age=32 or age=12;
select * from user where age != 40;
select * from user where age <> 40;
select * from user where age in(12,32);
select * from user where province like '河%';
select * from user where province like '%河%';
select * from user where province like '河_';
select * from user where province like '_河_';
select * from user order by id asc;(默認的排序，升序)
select * from user order by age desc;(降序)
select * from user limit 0 , 2;
select * from user order by age asc limit 0 , 2;
select * from user where > 20 order by age asc limit 0 , 2;

select count(*) , name from user group by name;

分組

select count(*) as total , name from user group by name;

起別名

select count(*) as total , name from user group by name having total > 2;

對結果進行再次過濾 having

多表查詢

mysql> select * from shop_user join shop_goods;

隱式內連接

mysql> select shop_user.username,shop_goods.gname from shop_user,shop_goods where shop_user.gid = shop_goods.gid;

顯示內連接

mysql> select username as uname , gname as name from shop_user inner join shop_goods on shop_user.gid = shop_goods.gid;

左連接查詢

mysql> select username , gname from shop_user left join shop_goods on shop_user.gid = shop_goods.gid;

右連接查詢

mysql> select username , gname from shop_user right join shop_goods on shop_user

嵌套查詢

mysql> select username from shop_user where gid in(select gid from shop_goods);

union 聯合查詢

mysql> select * from shop_user left join shop_goods on shop_goods.gid = shop_user.gid union select * from shop_user right join shop_goods on shop_user.gid = shop_goods.gid;

同時修改兩張表

mysql> update shop_user as u , shop_goods as g set u.username='qwq' , g.gname='rtr' where u.gid = g.gid and u.gid=1;

多欄位查詢

mysql> select * from shop_goods order by price , gid desc;

sum、count、max、min、avg

sum 求和
count 求總條數
max 求最大值
min 求最小值
avg 求平均值

select count(shop_user.gid) as count , shop_goods.gname ,avg(shop_goods.price) from shop_user inner join shop_goods on shop_user.gid = shop_goods.gid group by shop_goods.gname order by count desc limit 0 , 1;
select count(shop_user.gid) as ugid , shop_goods.gname as name from shop_user right join shop_goods on shop_user.gid = shop_goods.gid group by shop_goods.gname order by ugid desc limit 0 , 1;
select count(shop_user.gid) as ugid , shop_goods.gname from shop_user left join shop_goods on shop_user.gid = shop_goods.gid group by shop_goods.gname order by ugid desc limit 0 , 1 ;

關于用戶

revoke grant

mysql> create user 'whj'@'localhost' identified by 'love';

創建用戶

mysql> drop user 'whj'@'localhost';

洗掉用戶

mysql> grant select on *.* to 'whj'@'localhost';

賦予權限

mysql> revoke select on *.* from 'whj'@'localhost';

剝奪權限

查詢管理員

mysql> select user from mysql.user;