mysql常用语句梳理

重新复习mysql，记录了一些语句,整理如下。

---

mysql版本为5.7.23

---

安装

CentOS系统
yum install mysql mysql-server mysql-devel

Ubuntu系统
apt install mysql-server mysql-client

sql语句

注：下面举例的被标黑的字段是参数字段。

1>显示所有数据库
show databases;

2>应用数据库
use [database_name];

3> 显示所有数据表
show tables;

4>显示数据表的简略信息
desc [table_name];
显示数据表中的结构，在使用起来貌似与下面这个语句相同，没看出来什么区别。。
show columns from [table_name];

5> show index from [table_name];
显示数据表的索引

6>查找数据表中column字段的值
select [column1,column2] from [table_name];

7>创建用户，并设置密码
create user ‘[user_name]’@’[user_local]’ identified by ‘[password]’;
比如，我想为本地主机（localhost）创建一个test用户，且该用户的密码是12345。

create user ‘test’@‘localhost’ identified by ‘12345’;

你也可以将localhost改为特定的ip地址

8>为用户授权
grant [privileges] on [database.table] to ‘[username]’@’[host]’;

其中privileges包括，select，delect，update等。

每次修改过授权之后，需要刷新权限使用如下命令

flush privileges;

9>将所有数据库的所有表的所有权限都赋予给用户
grant all on * . * to ‘[username]’@’[host]’;

10>撤销某用户的某权限
revoke [previleges] on [database.table] from ‘[username]’@’[host]’;

11>show grants for ‘[username]’@’[host]’;
显示对用户做过的grants的操作

12>drop user ‘[username]’@’[host]’;
删除某用户

13>更改用户密码
set password for ‘[username]’@’[host]’=password(’[passowrd]’);
如果是更改当前用户密码则为
set password =password(’[passowrd]’);

14>设置mysql的字符集的编码格式
set names [char_type];
以utf8为例
set names utf8;

15>一些重要的sql命令
select -从数据库中提取数据
update -更新数据库中数据
delete -从数据库中删除数据
insert into -向数据库中插入新数据
create database -创建新数据库
alter database -修改数据库
create table -创建新表
alter table -变更数据库表
drop table -删除表
create index -创建索引
drop index -删除索引

16>创建数据库
create database [database_name];

建议在创建数据库的时候，直接设置数据库使用的默认编码为utf-8
create database [database_name] character set utf8;

17>创建数据表
create table [table_name] (id int,name varchar(255),sex varchar(16),age int,tel int,address varchar(255));

18>向表中插入数据

insert into [table_name] values (5,‘tom’,‘男’,15,110,‘washington’);

values后的小括号中的值的顺序与表结构中每个字段的顺序是一致的，每个字段之间使用逗号分隔。

另外，也只可以不按照数据表中的默认顺序去插入数值，也可以自己设置顺序，如下：
insert into [table_name] (name,sex,tel,address,id,age) values (‘mary’,‘女’,120,‘shanghai’,‘2’,13);

19>查询数据字段
select [column1],[column2] from [table_name];
前面说过，这是从某表中查询某两个字段的值。

如果想要查询特定值的某些字段，比如想要查询性别为男的人的name和id值，可用如下方式：
select name,id from student where sex=‘男’；

其中 where的条件匹配还可以使用如下的方式：
=　　　　　　　　　　　　　　　　 -等于
>　　　　　　　　 　　　　　　　　 -大于
< 　　　　　　　　　　　　　　　　 -小于
<>　　　　　　　　　　　　　　　　 -不等于
>=　　　　　　　　　　　　　　　　 -大于等于
<=　　　　　　　　　　　　　　　　 -小于等于
between and　　　　　　　　　　　　-限定值在某两个值之间

用法
select * from [table_name] where [column_name] between 13 and 14;
还可以是not between，意思和使用方法不言而喻

like -查询与查询语句类似的值
用法
select * from [table_name] where [column_name] like ‘%ang%’;
这里使用%为通配符，而不是常用的*.

in -查询在某个范围之间的值
用法
select * from [table_name] where [column_name] in (13,14);

and/or -可以把多个限定条件一起使用

20>对数据进行排序，可以多个字段一起排序
select * from [table_name] order by [column_name];
这种排序方式默认是按照正序去排（asc），如果是逆序，可以加入desc,使用如下方式
select * from [table_name] order by [column_name] desc;

21>对表中数据进行更新
update student set age=22,tel=333 where name=‘jerry’;
以上的意思是将student表中名字叫做Jerry的年龄和电话分别做修改。

22>删除表中某一行数据
delete from [table_name] where [column_name]=1;

如果直接想清空表数据

23>查询时，对查询结果的数量进行限制

select * from [table_name] where [column_name]=‘男’ limit 1;
这条语句前面的限定条件不论匹配了多少条，最终只会显示第一条。

24>正则表达式
下面语句的意思是查询以s或者c开头的字段
select * from [table_name] where [column_name] regexp ‘[s,c].’;
再对括号内的字段进行取反的意思就是开头不包含s或者c的字段
select * from [table_name] where [column_name] regexp '[s,c].’;

下面语句的意思是查询字段中只要包含s或者c就可以
select * from [table_name] where [column_name] regexp ‘[s,c].*’;

25>别名（alias）
select [column_name] as ‘性别’ from [table_name] ;
将sex这列的名称改成‘性别’并显示，但是需要注意的是，这只是在本次显示和筛选有效，实际并没有修改column的名字

select * from student as 学生;
这句话的意思是将表的名字设置一个别名，并显示，同样，并没有实际修改表的名字。

26>表关联，
分为内联，全联，左联，右联。

将student表的id和score表的sid，内联起来一一对应显示。
select * from student inner join score on id=sid;
mysql> select * from student inner join score on id=sid;
±-----±-----±-----±-----±-----±-----------±-----±--------±-----±--------+
| id | name | sex | age | tel | address | sid | chinese | math | english |
±-----±-----±-----±-----±-----±-----------±-----±--------±-----±--------+
| 2 | mary | 女 | 13 | 120 | shanghai | 2 | 88 | 78 | 99 |
| 3 | lili | 女 | 18 | 199 | beijing | 3 | 78 | 88 | 79 |
| 4 | Lucy | 女 | 28 | 199 | beijing | 4 | 79 | 98 | 99 |
| 5 | tom | 男 | 15 | 110 | washington | 5 | 74 | 95 | 49 |
| 6 | jam | 男 | 38 | 999 | suzhou | 6 | 75 | 96 | 89 |
±-----±-----±-----±-----±-----±-----------±-----±--------±-----±--------+
5 rows in set (0.01 sec)

左联表又称为左外联表，以左表为基础，即使右表没有查询到任何数据，也会显示，如下示例。
我们发现对于score表的sid值而言，并没有8，所以查询的结果都是null，但是依然显示。

mysql> select * from student left join score on sid=8;
±-----±-----±-----±-----±-----±-----------±-----±--------±-----±--------+
| id | name | sex | age | tel | address | sid | chinese | math | english |
±-----±-----±-----±-----±-----±-----------±-----±--------±-----±--------+
| 5 | tom | 男 | 15 | 110 | washington | NULL | NULL | NULL | NULL |
| 2 | mary | 女 | 13 | 120 | shanghai | NULL | NULL | NULL | NULL |
| 3 | lili | 女 | 18 | 199 | beijing | NULL | NULL | NULL | NULL |
| 4 | Lucy | 女 | 28 | 199 | beijing | NULL | NULL | NULL | NULL |
| 6 | jam | 男 | 38 | 999 | suzhou | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----±-----------±-----±--------±-----±--------+

右联，与左联概念一致，优先保证右表显示。使用方法如下。
select * from student right join score on id=sid;

全联，即把左联结果表 + 右联结果表组合在一起，然后过滤掉重复的，这个感觉不怎么用的到。
select * from student join score on id=sid;

27>创建表时候，同时加入约束
约束如下：
NOT NULL　　　　　　　　　　　　　　　　　-非空
UNIQUE　　　　　　　　　　　　　　　　　　-唯一
PRIMARY KEY　　　　　　　　　　　　　　　-主键
FOREIGN KEY　　　　　　　　　　　　　　　-外键
CHECK　　　　　　　　　　　　　　　　　　-检查
DEFAULT　　　　　　　　　　　　　　　　　-默认

每个表可以有多个UNIQUE但是只能有一个PRIMARY KEY，且主键不能为空

示例如下，除了not null可以直接写在字段类型之后，其他的都是在创建表的最后特殊表明，如下示例中用到了not null和unique。

create table [table_name] (P_id int not null,Last_name varchar(255) not null,Firstname varchar(255),Adddress varchar(255),City varchar(255),unique(P_id));

我们关系型数据库中如果想要将两个表之间建立关系，那么需要在创立的时候制定本表的FOREIGN KEY，并制定到外表的PRIMARY KEY主键，也就是说被指向的表必须得有主键。不过新表的FOREIGN KEY与原表的PRIMARY KEY之间制定的字段不一定要是一样，示例如下：
create table orders (o_id int not null,order_num int,P_id int,PRIMARY KEY(o_id),FOREIGN KEY(order_num) references persons(P_id));

default用法，当创建一个表的某个字段，如果在插入数据时，不给该字段赋值，则该字段值为默认值，示例如下，当不给city赋值时，city的值为‘beijing’。

create table orders (o_id int,order_num int not null,P_id varchar(255),city varchar(255) default ‘beijing’);

28>创建和删除索引
create index [index_name] ON [table_name] ([column])；
drop index [index_name] ON [table_name]；

29>sql中的删除操作

drop table [table_name] -删除表
drop database [database_name] -删除数据库
truncate table [table_name] -清空表中数据，但不删除表

30>alter 操作

向数据表中增加一列
alter table [table_name] add [column_name] int;

删除数据表中的某列
alter table [table_name] drop column [column_name];

修改数据表中column的数据类型
alter table [table_name] modify [column_name] [date_type];

31>创建自增字段
create table [table_name] (id int auto_increment not null,sex varchar(333),name varchar(200) not null,PRIMARY KEY (id));