mysql命令

mysql

登录到本机上的mysql:

在配置好环境变量的前提下，打开命令行窗口：
mysql -h localhost -u root -p
随后输入mysql密码。

创建数据库：

create database <数据库名>

显示数据库：

show databases;

删除数据库：

drop database <数据库名>

删除一个不确定是否存在的数据库名：

drop database if exists tmp

如果不存在此数据库的话，会返回一个警告显示此数据库不存在。

连接数据库：

use <数据库名>

显示当前正在使用的数据库:

select database();

创建数据表：

create table<表名>）(<字段名1> <类型1> [,..<字段名n> <类型n>])

向数据表中添加数据：

insert into 表名 values(…..);//insert一次只能插入一条记录。

查看表中所有数据：

select *from 表名

按顺序查找（可按多个列查找）：

select 列名 from 表名 order by 列名1，列名2

按降序查找：

select 列名 from 表名 order by 列名1，列名2 desc;
想要按照某一列进行降序则在该列后面加上desc的关键字，desc只对直接位于其之前的列名有作用，如果想在多个列上进行降序排序则需要为每个列执行desc关键词。

查询固定某项的数据：

select 类型 （as 别名） from 表名

如果想要使得到的数据去重：

select distinct 类型 from 表名

删除表中数据:

delete from 表名 where 类型名 =…

想要将某些类型进行拼接输出有如下函数：

select concat(…,…,…) as …(别名) from emp;

修改表中数据:

update 表名 set 字段=新值 where 条件
update myclass set name=’we’ where id=1;

过滤数据

where关键字：

where子句操作符：
除了常见的=，！=，<=,>=,!>,!<还有between(在指定两值之间)，is null(为null值),<>(不等于)

between:select price form products where price between 2 and 12;//查找出来的数据的价格处于2~12之间。

is null: select name from products where name is null;//查找名称列为空的数据。

高级数据过滤

组合where子句

AND操作符：
为了通过不止一个列进行过滤，可使用AND操作符给where子句附加条件。

select prod_id,prod_price,prod_name from products where vend_id=’DLL01’ AND prod_price<=4;

OR操作符：
类似于and表示几个条件满足其一。
当or与and合用时要注意and的优先级大于or，因而妥善的利用（）至关重要。

in操作符：
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全部括在括号内。

select prod_name, prod_price from products where vend_id IN (‘DLL01’,’BRS01’) ;

not操作符：
not操作符等同于<>，它的作用只有一个那就事否定其后的语句。

select prod_name from products where not vend_id =’DLL01’ order by prod_name;

通配符过滤

之前介绍的过滤方法都是已知取值范围或具体数值的，为了适用更普遍的情况，因而引入了通配符。通配符：用来匹配值的一部分的特殊字符。此外通配符只能用于文本字段，非文本数据类型字段则不能使用通配符搜索。

LIKE操作符

%通配符

搜索串中，%表示任何字符出现任意次数。为了寻找以Fish起头的产品可以使用如下语句：

select prod_id,prod_name from products where prod_name like ‘Fish%’;

通配符还可以放在搜索模式的中间，可以用来寻找以一个字母为开头以另外一个字母为结尾的结果。

select prod_name From products where prod_name like ‘F%y’;//搜寻以F开头以y结尾的结果。

下划线（——）通配符

下划线与%用途相同，但下划线只匹配单个字符而不是多个字符。

select prodid,prod_name,from products where prod_name like ‘ inch teddy bear’;

方括号（[]）通配符

[]用来指定一个字符集，它必须匹配指定位置的一个字符。
例如，找出所有名字以J或M开头的联系人，可如下查询：

select cust_contact from customer Like ‘[JM]%’ order by cust_contact;

但并非所有的DBMS都支持这一语句，mysql就不支持，但在access之类的Microsoft系支持。

计算字段

拼接字段

concatenate将值连接到一起构成单个值。
一般使用+以及||进行拼接。但在mysql使用函数concat（）；

select concat(vend_name,’(‘,vend_country,vend_country,’)’);

执行算术运算

计算字段的另一个常见用途是对检索出来的数据进行计算。如下：
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num=20008;

使用函数

大多数sql实现并支持以下类型的函数：

1. 处理文本字符串的函数。
2. 用于数值操作的数值函数。
3. 用于处理日期以及时间的函数。
4. 返回DBMS正在使用的特殊信息的函数（如返回用户登录信息）的系统函数。

函数带来的问题

不同于select操作，不同的数据库管理系统有着不同的函数，因而各个函数的实现各不相同，因而在使用时需要多加注意。在本篇博客中主要使用mysql函数。

常用的文本处理函数：

left() 返回串左边的字符；
length() 返回字符串的长度。
lower() 将串转化为小写。
ltrim() 去掉串左边的空格。
right() 返回串右边的字符。
rtrim() 去掉右边的空格。
soundex() 返回串的soundex值。
upper() 将串转化为大写。
其中soundex是一个将任何文本串转换为描述其语言表示的字母数字模式的算法。其一般应用于在对于某项数据出现拼写错误，在获悉其正确名称后用soundex来限定读音相近的结果进行搜索。

select cust_name,cust_contact from customer where soundex(cust_contact)==soundex(‘Michelle Green’));//搜寻读音同MIchelle Green相近的结果。

日期处理函数

mysql用户使用year()的函数从日期中提取年份。
select order_num from orders where year(order_date)=2004;

数值处理函数

数值处理函数仅处理数值数据。

常用的数值处理函数：
ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦函数
SIN() 返回一个角度的正弦函数
EXP() 返回一个数的指数值
PI() 返回圆周率
SQRT() 返回平方根
TAN() 返回一个角度的正切值

汇总数据

聚集函数

我们经常需要汇总数据却不一定要将他们检索出来，因而sql提供了专门的函数。例如：

• 确定表中的行数。
• 获得表中行组的和。
• 找出表列的最大、最小、平均值。

聚集函数：
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列最大值
MIN() 返回某列最小值
SUM() 返回某列之和

例子：
AVG()

select AVG(prod_price) AS avg_price From products;

COUNT()

select count(*) AS num_cust from Customers;//返回行数。

聚集不同值

以上的5个聚集函数如果想要将他们全部进行运算，指定ALL参数，或者不指定参数（ALL默认）。如果想对无重复值进行运算需要distinct参数。

select AVG(DISTINCT prod_price) As avg_price FROM products where vend_id=’DLL01’;//只计算不同价格的平均值。

注意点

在将聚合函数用作条件语句时，语法有所变化：

sql

SELECT 专业名, COUNT(*) 人数 FROM Student WHERE 学号 IN (SELECT 学号 FROM StuCourse GROUP BY 学号 HAVING AVG(成绩)>80)GROUP BY 专业名;

group by要提前使用；

创建分组

分组是在select语句的group by子句中建立的。

select vend_id ,count(*) as num_prods from products group by vend_id;

by后面的所有列的组合为唯一的。

过滤分组

除了使用group by 进行分组以外，sql还允许过滤分组，规定包括哪些分组，排除哪些分组。在此引用入了HAVING语句，之前所有的where语句均可以使用HAVING语句进行替换。

select cust_id,count() AS orders from orders group by cust_id Having
Count() >=2;

select vend_id,count() as num_prods from products where prod_price>=4 group by vend_id having count(\) >=2;

Having和where的区别：where在数据分组之前进行过滤，Having在数据进行分组之后进行过滤。Where排除的行不包括在分组之中。

select子句的顺序

• select
• from
• where
• group by
• having
• order by

使用子查询

所谓的子查询即为嵌套在其他查询之中的查询。

利用子查询进行过滤

子查询的实质即为将多个查询合并为一个查询。子查询总是由内向外进行处理的。

select cust_id from orders where order_num in(select order_num from orderItems where prod_id=’RGAN01’);

三个查询嵌套：

select cust_name,cust_contact from customers where cust_id in(select cust_id from orders where order_num in(select order_num from orderItems where prod_id=’RGAN01’));

值得注意的是作为子查询的select语句只能查询单个列，企图检索多个列将返回错误。

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。

select cust_name,cust_state,(select count(*) from orders where orders.cust_id==customer.cust_id) as orders from customers order by cust_name;

联结表

创建联结

联结的创建非常简单，规定要联结的所有表以及他们如何关联即可。

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id==products.vend_id;

此处代码所检索的列与之前的略有不同，vend_name来自于表vendors,而其他的都来自与products表，引入头from语句指向了两个不同的表，并使用where进行了正确的联结。

内部联结

目前所涉及到的联结都称为等值联结，它基于两个表之间的相等测试。这种联结也称为内部联结，对于这种联结可以使用稍微不同的语法来指明指定联结的类型。

select vend_name,prod_name,prod_price from vendors INNER JOIN products on vendors.vend_id=products.vend_id;

此条代码与上面的代码运行结果相同，仅仅是语法不同，此处两表之间的关系是from子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结的条件用特定的语句on子句而不是WHERE子句。

联结多个表

SQL对一条select语句中可以联结的表的数目没有限制。创建联结的规则也基本相同。首先列出所有的表，然后定义表之间的关系。

select prod_name,vend_name,prod_price,quantity from orderItems,products,vendors where products.vend_id=vendors.vend_id and orderitems.prods_id=products.prod_ids and order_num=2007;

创建高级联结

使用表别名

之前介绍过使用列别名，别名除了用于列名以及计算字段之外，sql还允许给表名起别名。理由主要有两个：

• 缩短sql语句
• 允许在单条select语句中多次使用相同的表。

select cust_name ,cust_concat from customers AS c,orders As o, orderitems as oi where c.cust_id=o.cust_id and oi.order_nam=O.order_num and pord_id=’RGAN01’;

需要注意的是表别名只在查询执行中使用。与列别名不同，表别名不返回到客户机之中。

组合查询

多数sql查询都只包含从一个或多个表中返回数据的单条select语句。但是，sql也允许执行多查询，并将结果作为单个查询结果集返回。这些组合查询通常称为并或复合查询。
有两种基本情况，其中需要使用组合查询：

• 在单个查询中从不同的表类返回结构数据。
• 对单个表执行多个查询，按单个查询返回数据。

创建组合查询

用UNION操作符来组合数条SQL查询。

下面两个例子的结果相同：

select cust_name,cust_contact from customers where cust_state in(‘IL’,’IN’,’MI’);

select cust_name,cust_contact from customers where cust_name=’Fun4All’;

select cust_name,cust_contact from customers where cust_state in(‘IL’,’IN’,’MI’) UNION
select cust_name,cust_contact from customers where cust_name=’Fun4All’;

当我们使用union后得到的结果会自动去除重复的元组，如果想输出重复的元组只需将union换成union all 即可。
另外需要注意的是组合查询中的select语句必须包含相同的列、表达式或聚集函数。

对于组合查询的排序（order by）只能出现在最后一句的末尾。

数据插入

INSERT可以插入：

• 插入完整的行
• 插入行的一部分
• 插入某些查询的结果

insert插入的值不能和已经存在的值的主键的值重复。

插入完整的行：

insert into customers
values(‘1000006’,’’Toy Land’,’123 any street’,’new york’,’NY’,’11111’,’USA’,NULL,NULL);

上述语句存在安全性问题：语句高度依赖插入的顺序，更为安全的方法是：

insert into customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
values(‘….’,’..’,….);

插入部分行：

insert into customers(cust_id,
cust_name)
values(‘’,’’);

需要注意的是没有插入的行必须支持null或存在默认值。

插入检索出的数据：
insert select 插入搜索出来的值：

INSERT into customers(cust_id,cust_contact……)
select cust_id,cust_contact,cust_email…from custnew;

此处select出的列不一定需要与插入的列一一对应。
一般的INSERT语句只插入一行数据，但insert select则是搜寻出来多少项就插入多少。

从一个表复制到另一个表：
有一种不使用insert语句的数据插入。select into。
与insert select增补数据到一个已经存在的表不同，select into将复制数据到一个新表。

select *into custCopy from customers;

此处复制的是所有的内容，如果只是想复制单独的几列可以一一列举。
mysql语句：

create Table Custcopy as select *from customers;

更新和删除数据

更新

为了更新（删除）表中的数据，可使用update语句。可采用两种方式使用update：

• 更新表中特定行；
• 更新表中所有行；

客户10005现在有了电子邮件地址，因此数据需要更新：

update Customers set cust_email=’Kim@qq.com’ where cust_id=’10005’;

也可以更新多个列：

update Customers set cust_contact=’sam roberts’ ,cust_email=’sam@gmail.com’ where cust_id=’10006’l;

还可以通过设置null删除某列的值：

update customer set cust_email=NULL where cust_id=’10005’;

如果想要将删去的某列的某空值加回来

update customer set cust_email=’Sam@gmail.com’ where cust_email is null;

删除

从一个表中删除（去除）数据，使用delete语句：

delete from customers where cust_id=’10005’;

如果想要删除表中的所有数据则需要使用truncate tables;

当删除有外链关联的数据或表示，会出现报错：Cannot delete or update a parent row: a foreign key constraint fails

解决方法：

set foreign_key-checks=0;//先设置外链约束检查关闭

drop table mytable;//删除数据、表或者视图；

set foreign_key_checks=1;//开启外检约束检查，以保持表结构的完整性；

创建和操纵表

创建表

例如下面对一个products表的实例：

create table products(

prod_id char(10) not null,

vend_id char(10) not null,

prod_name char(254) not null,

prod_price decimal(8,2) not null,

prod_desc varchar(1000) not null

);

混合null和not null:

create table products(prod_id char(10) ,

vend_id char(10) ,

prod_name char(254) not null,

prod_price decimal(8,2) not null,

prod_desc varchar(1000)

);

不指定null即为not null;

指定默认值：

create table orderitems(

order_num integer not null,

order_item integer not null,

prod_id char(10) not null,

quantity integer not null default 1,

item_price decimal(8,2) not null

);

对于日期或时间戳列。可以通过指定引用系统日期的函数或者变量，将系统日期用作默认日期。例如mysql用户指定default current_date();

更新表

为了对表进行更新定义，可以使用alter table语句。

给已有的表增加列：

alter table vendors add vend_phone char(20);

其他操作，例如更改或删除列，增加约束或增加键，也可以使用类似的语法

alter table vendors drop column vend_phone;

注意：删除操作并非所有的DBMS都支持。

更新表的列名：

alter table emp change column mgr grm int;

删除表

drop table custcopy

重命名表

每个DBMS所支持的表的重命名有所不同。对于这个操作，不存在严格的标准。DB2、MYSQL、Oracle用户支持rename语句，SQL_SERVER支持sp_rename语句，所有重命名操作的基本语法都要求指定旧表名以及新表名。

mysql中的rename方法：

rename table customers to customer;

使用视图

视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

使用视图的优点:

• 重用sql语句
• 简化复杂的sql操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
• 使用表的组成部分而不是整个表。
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图

视图用create view 语句来进行创建，create view只能用于创建不存在的视图。使用drop view viewname来删除视图。若要更新或覆盖视图：必须先drop它，然后再重建它。

利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂的sql，这通常都涉及联结；

create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders ,orderitems where customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num;

掌握了创建视图的方法以后视图的使用，搜索就和正常的表一样了，可以进行条件查找，运算，需要注意的是视图的使用在有的时候会造成效率的低下。

删除视图

drop view productcustomers;

创建存储过程

create procedure mailinglistcount(listcount out number)

is

begin

​ select * from customers

​ where not cust_email is null;

​ Listcount:=SQL%ROWCOUNT;

END;

此处是oracle版本的代码，此存储过程中有一个名为ListCount的参数。此参数从存储过程返回一个值而不是传递一个值给存储过程。

所谓的存储过程与普通查询的区别就类似于python在pycharm编程和在命令行编程的区别，使用存储过程就是将一组查询或修改语句封装成一个函数，可以选择输入输出，方便对大量，大批数据的操作。

下面通过mysql的可视化工具Navicat进行举例，在Navicat新建函数，选择过程，选择输入选项，设置变量名称，在函数体中实现简单的查询操作：

CREATE DEFINER=root@localhost PROCEDURE NewProc(IN numofor int)
BEGIN

Routine body goes here…

select order_num,cust_id,order_date from orders where order_num=numofor;
select cust_id,count(cust_id) from orders group
by cust_id;
END

管理事务处理

事务处理

事务处理可以用来维护数据库的完整性，它保证成批的sql操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的sql操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，他们或者作为整体执行，或者完全不执行。如果没有错误发生，整组语句提交给数据库表。如果发生错误，则进行回退以恢复数据库到某个已知的安全状态。

事务处理有几大术语：

• 事物：指一组sql语句；
• 回退：指撤销指定sql语句的过程；
• 提交：指将为存储的sql语句结果写入数据库表；
• 保留点：值事务处理中设置的临时站位符，你可以对它发布回退。

控制事务处理

管理事务处理的关键在于将sql语句组分解为逻辑块，并明确规定数据应当何时回退，何时不应回退。

mysql：

START TRANSACTION

…………

COMMIT TRANSACTION

使用ROLLBACK

sql的ROLLBACK命令用来回退（撤销）sql语句：

delete erom orders;

rollback;

需要注意的是事务处理用来管理insert、update、delete语句。不能回退selete（没有意义），也不能回退create和drop操作（已经成为最终结果）。

使用commit

一般的mysql语句都是直接针对数据库表执行编写的。这就是所谓的隐含提交，及提交操作是自动进行的。但在事务处理块中，提交不会隐含德进行。为进行明确的提交，使用commit语句，

当commit或rollback语句执行后，事物会自动关闭（将来的更改会隐含提交）；

Code

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

因为涉及了两个数据库表，为了保证订单不被部分删除。最后的commit语句仅在不出错是作出更改，即第一条delete成功，第二条出错，delete也不会被提交（被自动撤销）；

使用保留点

简单的rollback和commit语句就可以写入或撤销整个事务处理。但是，只是对简单的事物处理才能这么做，更为复杂的事务可能需要部分提交和回退。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

创建占位符：

Code

savepoint delete1;

回退到保留点：

Code

rollback to delete;

tips:

保留点越多越好，因为可以进行灵活的回退。

例子：

Code

delete from student where id=3;
savepoint delete1;
delete from student where id=4;
savepoint delete2;
insert into student (id,name,address,phone) values(77,'hf','vc',null);
savepoint delete3;
rollback to delete2;
select *from student;