Mysql常用的SQL语句

前言

一、Linux如何安装Mysql？

二、如何启用Mysql？

三、数据库操作

四、表结构操作

五、表数据操作

六、Mysql用户权限操作

七、存储过程

八、索引

九、视图

十、外键

---

前言

Mysql是数据库管理系统（DBMS）的一种，使用的是标准的结构化查询语言（Structured Query Language，SQL），DBMS分为关系型数据库和非关系型数据库，关系型数据库包括Mysql、Oracle、SQL server等等，非关系型数据库又包含hbase，redis等等；这篇文章仅介绍一些在Mysql中常用的SQL语句。

---

一、Linux如何安装Mysql？

在linux终端运行命令：yum install mysql 和 yum install mysql-server

二、如何启用Mysql？

启动Mysql命令：service mysqld start

登录数据库：mysql -uroot -p（新安装的数据库没有密码，可以直接进入）

设置密码：mysql -uroot password "xxxxxx"

密码登录：mysql -uroot -p 回车后输入密码

关闭数据库命令：service mysqld stop

重启：service mysqld restart

查看状态：service mysqld status

三、数据库操作

登录数据库账号后，可以看到命令行提示变为mysql>,表示之后输入的都是SQL命令了，SQL语句都是用；结尾的，而linux命令不用；结束

常用的数据库操作如下：

创建一个新的数据库：create database 数据库名；

删除某个数据库：drop database 数据库名；

查看所有的数据库：show databases; 

使用某个数据库：use 数据库名；

查看数据库中的所有表：show tables；

查看当前在哪个数据库中：select database()；

数据库备份：mysqldump -uroot -pxxxxx 数据库名>备份数据库名.sql

数据库还原：mysql -uroot -pxxxxx 新数据库名 < 备份数据库名.sql 

四、表结构操作

表结构定义了表的字段名，表数据的类型以及对数据的约束，因而在说明表结构的操作命令之前，有必要先解释以下定义表结构需要用到的概念。

1.表字段：可以理解为每张表的表头，它表明了每一列数据的含义。

2.数据类型：定义了字段名，还要定义每个字段的数据类型，常用的数据类型如下

tinyint(M)：可存储1字节大小的整数，范围0-255，M指定数据在显示时显示的最小长度，如M=2，当数据长度大于2时则正常显示，当数据长度小于2时，则需指定填充数，如zerofill，显示为03

int(M)：4字节，类似于tinyint

bigint(M)：8字节

float：4字节，默认保留4位小数

double：8字节,默认保留13位小数

decimal(M,D)：M是最大位数（精度），范围是1到65。可不指定，默认值是10。D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0;一般用于存储精确的数值，如银行金额；

char(M)：定长字符串,M可取0-255之间，指定M值后如果长度不够M，会在右边补充空格直到长度为M，超过长度M的部分则不显示

varchar(M)：可变长字符串,可指定最大字符长度M，取值范围0-65532，当长度不足M时不会补空格，超过长度M的部分则不显示

date：日期型数据

3.数据约束

主键约束 primary key

自增长约束 auto_increment

非空约束 not null

默认值约束 default ，default  默认值

注释约束 comment ，comment 注释值

4.表结构操作

创建新的表结构 create table 表名（字段名1 数据类型 [数据约束]，字段名2 数据类型 [数据约束]，......，字段名N 数据类型 [数据约束])；                [  ]表示可选项

删除表结构： drop table 表名；

查询表结构： desc 表名；

修改表结构：

alter table 旧表名 rename 新表名；   更改表名

alter table 表名 change 旧字段名 新字段名 数据类型 [数据约束]；  更改字段

alter table 表名 modify 字段名 数据类型 [数据约束] first/after 字段名； 调整字段

alter table 表名 add 新字段名 数据类型 [数据约束]；  添加新字段

alter table 表名 drop 字段名；  删除某字段

复制一张表：

create table 新表名 like 旧表名；   复制表结构

insert into 新表名 select * from 旧表名； 插入所有的表数据

查看建表语句：show create table 表名；

五、表数据操作

新增表数据：

只新增一行数据：insert into 表名（指定字段名） values（对应字段的值）；

新增多行数据：insert into 表名（指定字段名） values（对应字段的值1）,(对应字段值2）,......,(对应字段值N）；

如果不指定字段名，则字段值长度要与表字段长度对应。

删除表数据：

delete from 表名 where 条件表达式；  删除满足特定条件的数据

delete from 表名； 删除所有数据，但不释放空间，新增的数据会从上一条数据之后开始添加

truncate 表名； 删除所有数据并释放空间

drop table 表名；删除表结构和表数据

修改表数据：

update 表名 set 字段名 = 数据 where 条件表达式；  修改特定的字段值

查询表数据：

select * from 表名；  查看表中的所有数据

select * from 表名 where id>10;   

select * from 表名 where id=10；

select * from 表名 where id > 5 and id < 10;

select * from 表名 where id < 5 or id >10;

select * from 表名 where id limit 2,10；  查看第三行开始之后的十行数据，即3-12行

select * from 表名 where id between 3 and 10；

select * from 表名 where id in （3,8,10）；

select * from 表名 where id not in（3,8,10）；

select * from 表名 where id is null；

select * from 表名 where id is not null；

select * from 表名 where name like “%x%”;  查看name字段包含x的数据

select * from 表名 order by 字段； 默认按字段名升序排列，降序末尾指定desc

select * from 表名 group by 字段 having 条件表达式；  聚合函数常常与group by搭配使用,常见的聚合函数有count(),sum(),max(),min(),avg(),distinct()

多表查询：

基本连接：select * from 表1，表2 where 表1.字段名=表2.字段名；  只显示两表共有的部分

内连接：select * from 表1 inner join 表2 on  表1.字段名=表2.字段名； 只显示两表共有的部分

左连接：select * from 表1 left join 表2 on 表1.字段名=表2.字段名；以左边为主表显示，右表不足的数据显示null

右连接：select * from 表1 right join  表2 on 表1.字段名=表2.字段名；以右表为主表显示，左表不足的数据显示null

硬连接：select * from 表1 union select * from 表2； 两表的长度必须一致，结果会去重并排序

              select * from 表1 union all select * from 表2；两表的长度必须一致，结果不会去重和排序

子查询：所有的嵌套语句都可以看做是子查询语句

临时表查询：嵌套的sql查询出来的结果都能当成一张临时表来使用

行子查询：嵌套的sql查询出来的结果只有一行数据，这个结果只能当成临时表使用

列子查询：嵌套的sql查询出来的结果只有一列值，既可以当成临时表使用，也可以当成条件使用

标量子查询：嵌套的sql查询出来的结果只有一个字段一个值，既可以当成临时表使用，也可以当成条件使用

六、Mysql用户权限操作

use mysql;   //进入mysql数据库

select user,host,password from user;  //查看user表中的用户，主机和密码信息

//向user表中添加新的用户，密码用password()函数加密

insert into user(user,host,password) values(username,hostname,password(xxx));

//赋予新用户所有库的所有表 所有操作权限

grant all on *.* to username@hostname identified by password(root账户密码）;

flush privileges； //刷新权限

show grants for username@hostname； //显示新用户拥有的权限

revoke all on *.* from username@hostname； //撤销新用户所有的权限

七、存储过程

mysql的存储过程类似于高级编程语言的函数，常用于数据库批量造数据，语法结构如下

drop procedure if exists 函数名； //新建某个函数前清空已有的同名函数

create procedure 函数名()   //创建一个存储过程

begin                                        //表示存储过程的开始

函数体                                        //中间写入存储过程的功能代码

end                                             //表示存储过程的结束  

call 函数名()                                 //调用存储过程

八、索引

索引用于提高某些表数据的查询效率，当索引过多时反而会降低整体的查询效率，索引分为三种：普通索引、唯一索引和主键索引

普通索引：可以重复，可以为空值

增 create index 索引名 on 表名（字段名）；

删 alter table 表名 drop index 索引名；

查 show index from 表名；

唯一索引：不能重复，但可以为空值

增 create unique index 索引名 on 表名（字段名）；

删 alter table 表名 drop index 索引名；

查 show index from 表名；

主键索引：不能重复也不能为空值（可以认为设置了主键约束就已经添加了主键索引）

增 create table 表名（字段名 数据类型 primary key）;   建表时添加

     alter table 表名 change 旧字段名 新字段名 数据类型 primary key； 建表后添加

     alter table 表名 add primary key（字段名）； 建表后添加

删  删除主键前得先取消自增长约束，否则无法删除 alter table 表名 drop primary key；

查 show index from 表名；

九、视图

视图源于数据库中实际的表，相当于将数据库中的表复制了部分出来生成的一张虚拟表，我们可以生成视图来隐藏数据库中重要的数据，只提供可以给人看到的部分数据。

新建视图：create view 视图名 as（select 部分字段名 from 数据库中的原表名）；

删除视图：drop view 视图名；

查询视图数据：select * from 视图名；

查询建视图语句：show create view 视图名；

视图中的数据可表数据的修改一样，修改了视图中的数据，原表中对应的数据也会改变，所有视图我们一般只提供允许操作的数据。

十、外键

当一张表过于冗长，我们可以拆分成几张表，为了维持这几张表的关联性，我们需要用到外键。

外键的作用就是为了避免表过于冗长，建立表与表之间的关系，保证数据的完整性和一致性。

外键必须在innodb存储引擎下才能生效，mysql默认的存储引擎是MyISAM。

创建外键有两种方法：

1.建表时添加外键（主表已建好且有字段id）

create table 子表名（sid int(4) primary key,constraint 外键名 foreign key（sid） references 主表名（id）） engin=innodb；

2.表建好后添加外键（主表子表已建好，有id和sid字段需要关联）

alter table 子表名 add constraint 外键名 foreign key（sid） references 主表名（id）；

删除外键：alter table 子表名 drop foreign key 外键名；