MySQL 命令总结

mysql -uroot -p

Windows系统下    winpty mysql -uroot -p    

数据库是按照 数据结构 来 组织,存储 和 管理数据 的仓库
每个数据库都有一个或多个不同的API 用于创建,访问,管理,
搜索和复制所保存的数据,所谓的关系型数据库,是建立在关系模型基础上的数据库.
特点:
数据以表格的形式出现
每行 为各种记录的名称
每列 为记录名称所对应的数据域
许多的行和列做成一张表单
若干的表单组成database

----数据库-------------------------------------------------------------------------
------查看数据库------
show databases
show create database 库名

------创建数据库------
create database 库名 (character set utf8);

------修改数据库------
alter database 库名 (character set 编码方式)

------删除数据库------
drop database 库名

------修改编码方式-----
alter database 库名 default character set utf8;
alter table 表名 character set utf8;

------查看编码方式-----
show create database 库名;
show create table 表名

-----选中并使用要用的数据库------
use 库名

-----------------------------------------------------------------------------------

----数据表-------------------------------------------------------------------------

------查看当前数据下有哪些数据表------
show tables；

-------创建数据表-----
create table if not exists 表名（
字段名 数据类型 【约束】，(unsigned key auto_increment)    每一个字段代表一个类型的数据
......
PRIMARY KEY (字段名) default'',        默认值 default
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

PRIMARY KEY
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

unique()
UNIQUE 约束唯一标识数据库表中的每条记录
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证
PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

注意:设置主键的字段 必须制定 auto_increment 自增属性
不允许多个PRIMARY主键,但可以有多个UNIQUE约束

------查看建表信息------
show create table 表名

------查看表结构--------
    desc 表名

------插入信息----------
方法一:
insert into 表名[（字段名1...）]values('值'),('值'),('值');
方法二:
insert into 表名 select 字段 from 表名

----------------------------------------------------------------------------------

----SELECT-------------------------------------------------------------------------
-------查看指定表中的所有数据------
select * from 表名;

--------条件查询---------
select 字段名称 from 表名 where 条件;
select u_name from users where u_id=2;    (查询 u_id=2 的 字段 u_name 的值)

mysql> select * from users where u_age<40;
mysql> select * from users where u_age<40&&u_age>20;

--------like查询---------
mysql> select * from users where u_name like 'IG.w%';

-----------------------------------------------------------------------------------
----记录操作-----------------------------------------------------------------------

--------更新记录-------------
update 表名 set u_age=20,u_sex="f" where u_id=2;

---------删除记录------------
mysql> delete from users where u_id=3;
mysql> delete from users where u_age<20;
mysql> delete from ebuser where u_id in (1,2,3);

-------清空表数据------------
truncate 表名;
delete from 表名;
注意:只会清空表数据 不会把表删除

--------删除指定表-----------
drop table 表名;

--------删除指定的数据库------
drop database 库名;

--------增加一列(table)------
alter table 表名 add 字段名 数据类型 (约束);

--------删除一列(table)-------
alter table 表名 drop column 字段名;

--------设置主键--------------
alter table 表名 add primary key(字段名);

--------删除主键--------------
alter table 表名 drop primary key;

-----------------------------------------------------------------------------------

--------避免重复(去重复)--------------
select distinct 字段名 from 表名; (最好找一个字段名)

--------取别名----------------
select p_name,p_price*p_number as(可省略) total_money from ebproduct;

--------格式化显示(连接字符串)(concat)---------
select concat_ws(":",p_name,p_price*p_number) as total_info from ebproduct;

--------between--------------------
select p_name from ebproduct where p_price  between 1000 and 3000;

----------is (not)-----------------
select p_name from ebproduct where p_name is not Null;(判断Null 必须用is)

-----------------------------------------------------------------------------------

---------------函数---------------
max()
select max(p_price) as max from ebproduct;

min()
select min(p_price) as min from ebproduct;

sum()
select sum(p_price) from ebproduct;

count()
select count(*) from ebproduct; (表中有多少条记录)

avg()平均值
select p_price,avg(p_price) from ebproduct group by p_price;

--------排序 order by--------asc 从小到大   desc 从大到下
select * from ebproduct order by p_number asc,p_price desc;

----------limit------------------
mysql> select * from ebproduct limit 5,3;  (5表示起始位置,3表示条数)

-----------正则-------------------
mysql> select * from ebproduct where p_name regexp "02";
mysql> select * from ebproduct where p_name regexp "美的";
mysql> select * from ebproduct where p_name regexp "i*one";

--------------having  过滤------------
select p_number,avg(p_number) from ebproduct group by p_number having avg(p_number)>=1000;

select p_number,avg(p_number) from ebproduct group by p_number having avg(p_number) between 1000 and 10000;
-------------------------------------------------------------------------------------

-------------多表插入--------------
insert 表名（字段） select 字段 from 另一个表名 group by 字段；

--------------多表更新--------------
update goods( as g )inner join brand (as b) 
on goods.brandname=brand.brandname
set goods.brandname=brang.bid;

update goods as g inner join category as c 
on g.catename= c.catename
set g.catename=c.cid

---------------更改字段--------------
alter table goods change catename（原字段）  cid（新字段） smallint unsigned;

-------------------------------------------------------------------------------------

------/*内连接查询*/---------
select id,goodsname,brandname,catename,price
from goods as g inner join brand as b
on g.bid=b.bid
inner join category as c
on g.cid=c.cid;

-------/*左连接*/----------
select id,goodsname,brandname,catename,price
from goods as g left join brand as b 
on g.bid=b.bid
left join category as c
on g.cid=c.cid;

-------/*右连接*/--------
select id,goodsname,brandname,price
from goods as g right join brand as b
on g.bid=b.bid;

/*类似于内连接的链接查询*/
select id,goodsname,brandname,catename,price
from goods,brand,category
where goods.bid=brand.bid 
and goods.cid=category.cid
and price>5000 limit 5;

-------/*多表删除*/---------
delete g1 from goods as g1 inner join (select min(id) as mid,goodsname,count(goodsname)from goods
group by goodsname having count(goodsname)>1) as g2
on g1.goodsname=g2.goodsname
where g1.id>g2.mid;

----------------------------------------------------------------------------
查询优化  explain
函数
连接字符串； concat(S1,S2,sn)
转换小写；   lower（）
转换大写； upper（）
从左数；left；（str，n）
从右数；right；（str，n）
返回空格；space（n）
去除两边空格；trim（）
长度；length（）
重复；repeat（）
替换；replace（s,s1,s2）
从s中n的位置获取长度为len的字符串；substring(s,n,length)
从字符串s中获取s1的开始位置；locate（s1，s）
四舍五入；round（x）
保留小数点Y位四舍五入；round（x，y）
日期；curdate（）当前日期
         curtime（）当前时间
         now（） 年月日时分秒
当前时间戳；unix_timestamp()      秒
           from_unixtime()    年月日-时分秒

------------------------------------------------------------------------------
'''
数据操作
插入数据(记录): insert
insert into 表名(key1,key2...,keyN) select(key1,key2...keyN) where ...;
数据更新:update 表名 set key1=value1,key2=value2,...,keyN=valueN where ...;
删除数据:delete from 表名 where ...;
'''
'''
select distinct key1,key2,...,keyN from 表名 
where           条件 
group by field  分组
having          筛选
order by field  排序
limit 限制条数
;

关键字的优先级:
from        #1.找到表
where       #2.通过where 指定的约束条件,去 文件/表 中取出一条记录
group by    #3.将取出的一条条记录进行分组 group by, 如果没有则为一组
having      #4.将分组之后的结果进行having 过滤
select      #5.执行select
distinct    #6.去重复
order by    #7.将结果按顺序排列
limit       #8.限制结果的显示条数
'''
'''
where 约束 条件约束
1.比较运算符 >  <  >=  <=  !=
2.between    1 and 2
3.in(v1,v2,v3,...)  数值为 v1,v2,v3....
4.like "pattern" 可以是 % 或者 _  (通配符 %表示任意个任意字符  _ 表示一个任意字符 )
5.逻辑运算符   and  or  not 
注意: null 是单独的数据类型 判断NULL 类型 必须使用  is null 来判断
    空字符串 不是 null 类型
'''
'''
聚合函数
注意: 如果是按照某一个字段分组,那么select 查询的字段也必须是 该字段 (MySQL 5.7+ )
如果想拿到其他字段的信息, 必须使用函数 group_concat(其他字段)
mysql> select p_number,group_concat(p_name) from ebproduct group by p_number;
group by 和 group_concat() 一起使用才能获取结果
注意: where 中不能有聚合函数
'''
'''
having  过滤
select p_number,avg(p_number) from ebproduct group by p_number having avg(p_number)>=1000;
'''
--------------------------------------------------------------------------------
mysql> select tname,group_concat(p_name) from ebproduct inner join ebtype on ebproduct.type_id=ebtype.tid group by tname;
+--------+---------------------------------+
| tname  | group_concat(p_name)            |
+--------+---------------------------------+
| 冰箱   | 海尔冰柜01,海尔冰箱01           |
| 手机   | iphone7,诺基亚N71,iphone6,小米8 |
| 汽车   | Jeep牧马人01,路虎揽胜           |
| 洗衣机 | 美的洗衣机02                    |
| 空调   | 格力空调01,美的空调02           |
+--------+---------------------------------+
5 rows in set (0.00 sec)
--------------------------------------------------------------------------------

select [distinct|distinctrow|all] 字段名
[ from 表名
    [where 条件]
    [group by 条件]
    [having 条件]
    [order by 条件]
    [limit [开始位置][查询条数]]
    [procedure name]     查询存储过程返回的结果集数据
---------------------------------------------------------------------------------

创建用户(root下才能执行)
create user 'Tony'@'localhost' identified by '123456';

修改权限               库名.表名
 grant all privileges on py03db.* to 'Tony'@'localhost';
 flush privileges;  (提交修改) 

创建 用户 并 赋予权限(root下执行)
 grant all on py03db.* to 'Tony2'@'localhost' identified by '123456';

grant select on testdb.* to common_user@’%’;    
grant insert on testdb.* to common_user@’%’;     
grant update on testdb.* to common_user@’%’;     
grant delete on testdb.* to common_user@’%’;     
grant select,update,delete,insert on testdb.* to common_user@’%’;

撤销用户 的权限
 revoke all on py03db.* from 'Tony2'@'localhost';

删除用户
 drop user 'Tony2'@'localhost';

查看权限 　　show grants;         show grants for dba@localhost;

----------------------------------------------------------------------------------
----------更改字段为unique----------
alter table 表名 add unique key(字段名);

-----为已经添加好的数据表添加外键--------foreign
语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名)；
alter table 表名 add index(字段名);
alter table 子表的数据表名 add foreign key(子表的外键名称) references 父表的数据表名称（父表的主键名称)