Mysql SQL语句

0.分类

（1）DDL( data defination language)

数据定义语言，作用：创建、删除、修改库表结构(重点)

（2）DML (data manipulation language)

数据操作语言，作用：增、删、改表的记录(重点)

（3）DCL( data controll anguage)

数据控制语言，作用：用户的创建以及授权(懂)

（4）DQL( data query language)

数据查询语言，作用：查询数据(重点)

1.DDL

（1）show databases

创建数据库

（2）use databases

切换数据库

（3）create database xxx

创建数据库

（4）drop database xxx

删除数据库

（5）create table xxx(列名1 类型 关键字,… )

创建数据表，举例：

create table people(
id int unsigned auto_increment not null, 
name varchar(45) not null comment '用户名' , 
password char(32) not null comment '密码', 
time datetime, primary key(id)
);
#创建people表：
#id为整数，自增且非空；
#name为varchar，并注释说明为用户名；
#password为32位定长char且非空，并注释说明为密码
#time为时间
#主键为id

（6）desc xxx

查看表格式，比如上面创建的people表：

mysql> desc people;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(45)      | NO   |     | NULL    |          |
| password | char(32)         | NO   |     | NULL    |           |
| time     | datetime         | YES  |     | NULL    |           |
+----------+------------------+------+-----+---------+----------------+

（7）show create table xxx

查看创建表的SQL语句，比如还是对上面的people表：

mysql> show create table people;
| Table  | Create Table  | people | CREATE TABLE `people` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL COMMENT '用户名',
  `password` char(32) NOT NULL COMMENT '密码',
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

（8）drop table xxx

删除表

（9）alter table xxx modify/change/add xxx

修改表结构，举例：

mysql> alter table people modeify name varchar(50) not null;
#将name这列类型改成varchar(50)且非空
mysql> alter table people change name username varchar(45) not null;
#把name这列改成username，类型改成varchar(45)且非空
mysql> alter table people add age int not null after username;
#创建age列，且位置在username的后一列
mysql> alter table people drop age;
#删除age列
mysql> alter table people rename peoples;
#将people表名改成peoples

（10）truncate xxx

清空表，举例：

truncate people;

该方法清空数据后，整个表相当于被初始化，比如id自增到15，用drop删除数据后，再新增数据，id会从16开始；而用truncate清空的话，新增数据，id会从1开始

2.DML

（1）insert into 表名 (列1,列2,…) values(列值1,列值2,…)

插入数据，举例：

mysql> insert into peoples(username, password, time) values('aaa', '111', '2017-
5-1 12:2:5');
mysql> insert into peoples values(3, 'das', 'aaabbbccc', '2017-05-01 02:32');
#如果列名一一对应插入，可以不用输入每个列名

（2）update 表名 set 列1=值1, 列2=值2,… where 条件

修改数据，举例：

mysql> update peoples set username='ccc' where id=5;
mysql> update peoples set username='aaa' where id in (1,2,3);
#当id=1/2/3的修改
mysql> update peoples set username='abc';
#不加条件就全改

（3）delete from 表名 where 条件

删除数据，举例：

mysql> delete from peoples where id=7;
mysql> delete from peoples;
#没条件就全删了

3.DCL

（1）权限设置

进入数据库mysql（自带），输入：

select host, user from user;

可以看到所有的用户，比如结果如下：

+-----------+-----------+
| host      | user      |
+-----------+-----------+
| localhost | mysql.sys |
| localhost | root      |
+-----------+-----------+

host和user代表能登录的ip和对应用户名，这里说明只有本地的能够登录该数据库，假如表里有别的ip和用户名，那么可以删除那条数据；如果要指定用户和ip，设置下ip和对应用户名，然后输入：

flush privileges;

此时就会刷新用户权限

（2）密码设置

有以下方式修改密码：
① 方法一：
当忘记密码时，可以先关闭数据库，然后输入命令：

mysqld --skip-grant-tables;

此时直接输入命令：mysql，会发现无需密码直接登录进数据库，然后在进入数据库mysql，输入：

select host, user, authentication_string from user;

可以看到所有的用户、对应IP和加密密码，此时更新用户的密码即可（注意更新时密码要用password函数，即authentication_string=PASSWORD('新密码')）。
② 方法二：
当记得密码时，可以直接进mysql数据库的user表修改密码，或者直接输入命令：

mysqladmin -u用户 -p旧密码 -hIP地址 password 新密码;

举例：

mysqladmin -uroot -p123456 -h127.0.0.1 password 233333;

（3）用户设置

①创建用户：
输入命令：

create user '用户名'@'IP' identified by '密码';
flush privileges;

举例：

mysql> create user 'dawson'@'192.168.0.1' identified by 'abc123';

注：如果要所有ip地址都能登录，那么ip地方就设置为：%
② 用户授权
输入命令：

grant 权限1, 权限2, ... on 数据库.* to '用户名'@'IP地址'

举例：

grant update, insert, delete on shop.* to 'dawson'@'127.0.0.1';
#shop数据库增加修改、插入、删除权限给用户dawson

这个授权语句不仅可以给已存在的用户授权，也可以同时创建一个新用户并授权，举例：

grant update, insert, delete on shop.* to 'aaa'@'127.0.0.1' identified by '123456';

可以看出其可以和创建用户语句结合使用
注：如果要给全部数据库授权就用：*.*；全部IP就用：%；全部权限就用：all或all privileges
③ 撤销权限：
输入命令：

revoke权限1, 权限2, ... on 数据库.* from '用户名'@'IP地址'

④ 查看权限：

show grants for '用户名'@'IP地址'

⑤ 删除用户：

drop user '用户名'@'IP地址'

4.DQL

（1）基本查询

基本格式：

select 列名1, 列名2,... from 表名 where 条件

as关键字

可以用as设置别名，比如修改展示的列名，但并不会真正的把表里的列名修改，举例：

mysql> select username as name from peoples;

like关键字

其相当于在=的基础上加上模糊查询的功能，举例：

select * from peoples where username='aaa';
select * from peoples where username like 'aaa';
#上面两个是等效的
select * from peoples where username like '%a';
#查询username以a结尾的
select * from peoples where username like '%abc%';
#查询username中间有abc的

当然如果数据量很大的话不建议用like来模糊查询，可以使用sphinx引擎

order by关键字

以某一列为基准排序，可以设置asc升序（默认）或者desc降序，举例：

mysql> select * from peoples order by id desc;

count()关键字

计算数据条数，举例：

select count(*) from peoples

sum()关键字

计算某列值总和，举例：

mysql> select sum(id) from peoples;

avg()关键字

计算某列平均值

max()关键字

计算某列最大值

min()关键字

计算某列最小值

group by关键字

以某列分组，比如有性别列，只有0和1两个值，假如现在有3个0和2个1，举例：

mysql> select sex, count(*) as total from peoples group by sex;
+------+-------+
| sex  | total |
+------+-------+
|    0 |     3 |
|    1 |     2 |
+------+-------+

可以看出其分别计算了两种值的数量

distinct关键字

把该列下重复的值过滤后列出来，举例：

mysql> select distinct password from peoples;

concat()关键字

把数据拼起来展示，可以自己加格式，比如用:把用户名密码隔开：

mysql> select concat(username, ':', password) from peoples;

展示时还可以用as来修改列名展示，举例：

mysql> select concat(username, ':', password) as 'information' from peoples;

limit关键字

基本格式1：

select ... from表名where 条件 limit 数字

基本格式2：

select ... from表名where 条件 limit 数字1, 数字2

第一个意思就是展示从所有数据里展示前几条数据，第二个意思是从第几条的后一条开始，展示几条数据，举例：

mysql> select * from peoples limit 2;
#展示前两条数据
mysql> select * from peoples limit 2,1;
#从第三条开始展示一条数据（即展示第三行的数据）
mysql> select * from peoples limit 0,3;
#从第一条开始展示3条数据（即展示第一、二、三行的数据）

这个关键字比较适合分页展示数据，比如1到10条、11到20条这样显示

（2）内连接查询

基本格式1：

select ... from 表名1, 表名2 where 条件

基本格式2：

select ... from 表名1 ... inner join表名2 on 条件

意思是只有当左右两个表都符合条件的数据才会显示，举例：

mysql> select * from peoples, food where peoples.id=food.id;
mysql> select peoples.id, food.name, food.number, peoples.sex from food inner join peoples on food.id=peoples.id;

（3）左连接查询

基本格式：

select ... from 表名1 ... left join 表名2 on 条件

意思是左边的表数据全部显示，右边的表只有满足条件才会显示，举例：

mysql> select food.name, food.number, peoples.sex from food left join peoples on food.id=peoples.id;

（4）右连接查询

基本格式：

select ... from 表名1 ... right join 表名2 on 条件

意思是右边的表数据全部显示，左边的表只有满足条件才会显示，举例：

mysql> select food.name, food.number, peoples.sex from food right join peoples on food.id=peoples.id;

（5）联合查询

基本格式：

select 列名1, 列名2,... from 表名 where 条件 union all select 列名1, 列名2,... from 表名 where 条件

可以看出是由两个select查询语句通过union all拼在一起的，要注意的是两个查询语句查询的列数要相等，举例：

mysql> select username,password from peoples union all select name, number from food;

（6）子查询

基本格式：

select列名1, 列名2,... from 表名 where 条件 (select ...)

子查询即在查询里再进行查询，用括号包起来，相当于嵌套查询，举例：

mysql> select * from peoples where id in(select id from food);
#从peoples表里获取food里存在的id的所有信息