mysql 常用操作

记录一些mysql 基本操作的命令，这是少接触，怕用到的那一部分。有需要了，还得回这里看看，以备万一，哪天有时间了，也可以过来学学。这就样。

mysql基本常用命令汇总

常用总结

creat database name; 创建数据库
use database_name; 选择数据库
drop database db_name; 直接删除数据库，不提醒
show tables; 显示表
describe table_name; 表的详细描述
select 中加上distinct去除重复字段

显示当前mysql版本和当前日期

select version(), current_date;

修改用户密码，如root

mysql> update user set authentication_string=password('new_password') WHERE user='root';
Query OK, ### row affected, ### warning (0.08 sec)
Rows matched: ###  Changed: ###  Warnings: 1

创建用户


CREATE USER 'your_name'@'localhost' IDENTIFIED BY 'password';

用户授权

GRANT ALL PRIVILEGES ON *.* TO 'your_name'@'localhost' WITH GRANT OPTION

取消授权

mysql> select user, host from mysql.user;  查看已经存在的用户
mysql> revoke all privileges on *.* from test@localhost; 取消授权
mysql> delete from user where user="test" and host="localhost"; 删除用户
mysql> delete from user where user="test" and host="localhost";

创建和操作表

创建表语法

create table <表名>(
    <列名###  <数据类型> <约束条件>
    <列名###  <数据类型> <约束条件>
    <列名###  <数据类型> <约束条件>
    .....
);

NULL值

在定义表时，可以在约束条件中指定该列允许或不允许NULL值。如果指定不允许NULL值，则在插入数据时如果为NULL就会报错。

默认值

可以在定义表时使用默认值，使用关键词default <值>进行设定，如果在插入数据时，没有值插入，则该列会插入默认值。

更新表

更新表使用alter table语句，我们可以进行如下操作：

alter table <表名> add column <列名> <数据类型>;   //增加新列
alter table <表名> drop column <列名>;  //删除列

重命名表

rename table <旧表名> to <新表名>;

删除表

drop table <表名>;

检索数据

检索单列

select <列名> from <表名>;

检索多列

select <列名1>, <列名2>, ... from <表名>;

检索不同值

select distinct(<列名>) from <表名>;

限制结果

在mysql中，如果我们需要限制结果输出的条数，可以使用limit <数字>来表示限制的行数。

注释

# 注释     //单行注释
/* 多行注释
   多行注释
*/

排序检索数据

有时我们需要对检索出来的数据进行排序，这时我们使用如下语句进行:

order by <列名> asc/desc;

asc为升序排序，也是mysql中默认的排序方式；desc表示按照降序排序。此外根据需要我们也可以对多列进行排序，如下代码所示：

order by <列名1> <排序方法>, <列名2> <排序方法>....;

值得注意的是，order by 语句通常位于查询语句的最后面。后面会具体介绍查询语句的完整构成。

过滤数据

使用where子句

在sql中，我们使用where子名来对查询出的结果进行过滤，得到我们所需要的数据。它的格式如下：

select <列名> from <表名>
where <过滤条件>;

过滤条件有多个时，我们可以使用and或or来进行多个条件的罗列。值得注意的是，当有多个过滤条件时，and的优先级会高于or，这也许会导致一些逻辑错误，需要注意。

where子句操作符

运算符	描述
=	等于
<>	不等于。注释：在SQL的一些版本中，该操作符可以被写成!=

|大于
<|小于
=|大于等于
<=|小于等于
BETWEEN|在某个范围内
LINK|搜索某种模式
IN|指定针对某个列的多个可能值

值得注意的是，在判断某个值是否为空值时，应该使用is null或is not null来判断，而不是使用”= null”或”!= null”。

使用通配符进行过滤

在使用通配符进行过滤进，我们使用like操作符进行。

通配符主要包括两个，它们分别为百分号’%’和下划线’‘，其中’%’表示使用长度的字符串，而下划线’‘只表示一个字符；需要注意的是，’%’不仅可以表示一个或多个字符，还可以表示0个字符。

创建计算字段

计算字段

存储在数据库中的数据一般不是应用程序所需要的格式。

我们需要直接从数据库中检索出转化、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用改程序或报告程序中重新格式化。

只有数据库知道select语句中哪些列是实际的表列，哪些列是计算字段。从客户机（应用程序）的角度来看，计算字段的数据是以与其它列的数据相同的方式返回的。

拼接字段

在mysql中，我们可以使用concat函数对字段进行拼接，格式如下：

concat(str1, str2, str3.....);

对于字符串中存在的空格，我们可以用字符串处理函数RTRIM()、LTRIM()、TRIM()来进行处理。

使用别名

我们可以用关键字as来给我们的列起一个别名。格式如下：

select <列名> as <别名> from <表名>;

使用函数处理数据

函数的可移植性是很可的，而且使用函数可以很方便的对数据进行处理。

mysql函数主要有甲种

• 文本函数
• 数值函数
• 时间与日期函数
• 系统函数

下面介绍常用的一些函数

文本函数

left(s, n)      //返回字符串左边的n个字符
right(s, n)     //返回字符串右边的n个字符
trim(s)         //去掉字符串s的空格
ltrim(s)        //去掉字符中左边的空格
rtrim(s)        //去掉字符串右边的空格
length(s)       //计算字符串的长度
lower(s)        //将字符串的所有字符变成小写
upper(s)        //将字符串的所有字符变成大写

数值函数

abs(x)      //绝对值
cos(x)
sin(x)
tan(x)
acos(x) asin(x) atan(x)     //三角函数
ceil(x)        //返回大于或等于X的最小整数
floor(x)       //舍去x的小数部分，只保留整数部分，并且不进行四舍五入
pi()           //返回圆周率
sqrt(x)        //平方根
pow(x,y)       //x的y次方
exp(x)         //返回e的x次方
rand()         //返回0－1的随机数
round(x,y)     //返回x的值小数点后面的第y位，进行四舍五入
truncate(x,y)  //返回x的值小数点后面的第y位，不进行四舍五入

日期与时间函数

current_date()      //返回当前日期
current_time()      //返回当前时间
current_timestap()/now()        //返回当前日期和时间
date()              //从日期或日期时间中撮日期值
year() month() day() hour() minute()
date_format(d, f)   //以指定格式显示日期
datediff()
timediff()
timestapdiff()

汇总数据

聚合函数：聚合函数就是用来数据汇总的一类函数。以下五个险函数务必牢记。它们通常和分级数据结合使用：

• avg()
• min()
• max()
• count()
• sum()

需要注意的是，聚合函数均不会处理NULL值

分级数据

创建分组

创建分组使用group by子句，格式如下：

select <列名> from <表名>
where <过滤条件>
group by <列名>
having <过滤条件>
order by <列名> <排序方式>

其实上面的格式也是select语句完整格式。

过滤分组

过滤分组倩having，牢记where是行级过滤，having是组级过滤

使用子查询

子查询也就是select语句的嵌套，有了子查询我们就可以进行复杂的查询。

子查询返回的一张表，所以基位置也比较灵活，可以位于rom后面，也可以位于where后面。

子查询也可以说是一个一次性的视图。

在执行顺序上，先执行子查询，再执行外层的查询。

牢记：where子句中不能使用聚合函数

连接表

表的连接主要分为以下几种：

• 内连接
• 外连接（左连接、右连接）
• 自连接

内连接

语法格式如下：

SELECT 查询字段 FROM 表1 [as] 别名 [INNER] JOIN 表2 [as] 别名 ON 表1.关系字段 ＝ 表2.关系字段

内连接就是两个表的次部分。

左连接

语法格式如下：

SELECT 查询字段 FROM 表1 [as] 别名 LEFT JOIN 表2 [as] 别名 ON 表1.关系字段 ＝ 表2.关系字段

左连接就是左边表的记录要全部显示出来，右右只有和械表匹配到的记录才进行显示。

右连接

和左连接相反。

自连接

就是表一张表看成两张表，进行自连接往往是一张表的某些列间存在逻辑关系。

组合查询

创建组合查询

创建组合查询时我们使用UNION将两个或多个select语句查询的结果组合在一起形成一个表。

需要不同的select语句返回相同数量的列且对应列的数据类型相同，事实上应该是意义都一样才合理。

union与union all

对于组合查询结果的排序只需要使用一个order by子名句，放在最后一个select语句的后面即可。

插入、更新删除数据

插入数据

insert into <表名>(列名1，列名2，列名3...) values (数据...);

虽然表名后面的列名表不是严格要求的，但是这样会更安全。如果不写出列名，则需要严格按照表的定义插入数据。

如果我们只需要插入一行中的部分列，那么列出列名就变得必须了。

插入检索出的数据

使用insert …select 语句进行。以下是格式：

insert into <表名>(列名表)
select <列名表> from <表名>;

复制表

create table copytable as 
select * from <表名>;

更新数据

update <表名>
set <列名> = ...
where <过滤条件>;

一定切记不要忘了过滤条件，否则将会对所有的行的数据进行更新。

删除数据

delect from <表名> where <过滤条件>;

如果要删除某行中某列的元素，可以将其值更新为NULL。

使用视图

视图是一张虚表，它并不在座实际的数据，存储的只是select语句。

使用视力有如下的优点

• 提高sql语句的重用性
• 保证了数据的安全性
• 大大简化了sql语句

创建视图

create view <视图名> as <select 语句>;

删除视图

drop view <视图名>;

事务

什么是事务

需要在同一个处理单元中执行的一系列更新处理的集合。一个事务中包含了多少个更新处理或者包含了哪些处理，在DBMS中没有固定的标准，而是根据用户的要求决定的。

语法

start tyransaction;
DML 语句1；
DML 语句2；
DML 语句3；
...
ROLLBACK／COMMIT；

理解事务

事务其实很好理解，它将一系列操作放在一个块中形成一个代码块，这个代码块要执行就全部执行，要么就不执行。

拿银行转账为例，转账过程为，我的账户金额减少－－》对方账户金额增加－－》交易完毕。

如果我的账户金额减少后出现了故障导致对方账户金额没有增加，那么这肯定是不被允许的。所以这个转账过程中的每一步都必须完整的执行。

ROLLBACK就是在DML语句出现故障时，来便利数据库回滚到执行操作前的状态。而COMMIT则是所有DML正确执行后才执行，告诉系统这个事务正常完成了，系统可以进入下一个新的状态了。

补充知识点

case when 语句

case when <表达式1> then <表达式>
     when <表达式2> them <表达式>
     ...
     else <表达式>
end

sql语句及种类

这是sql的基础知识：

－ DDL：数据定义语言，用来创建、删除存储数据的数据训以及数据库中的表的对象，包含的指令有：CREATE、DROOP、ALTER
－ DML：数据操纵语言，用来查询或者变更表中的记录。包含的指令有：SELECT、INSERT、UPDATE、DELETE
－ DCL：数据控制语言，用来确认或者取消对数据库中的数据进行的变量。包含的指令有：COMMIT、ROLLBACK、GRANT、REVOKE

exists谓词

在sql语言中，exists(存在)谓词是一个非常重要的查询关键词。

exists代表存在量词。带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“flase”。