mysql基础语法

用户2824380043348
433 阅读9分钟

1:打开mysql

  • linux
    • mysql -r root -p然后输入密码
  • windos
    • 打开客户端输入密码

2：数据库和表

2.1：查看所有数据库

show databases

2.2:使用某一数据库

use [数据库名称]

2.3:查看某一数据库的所有表

show tables;

3:检索数据

在这里插入图片描述

3.1：查询单个列

mysql> select name from student;

+------+
| name |
+------+
| aaa  |
| bbb  |
| ccc  |
| bbb  |
+------+
4 rows in set (0.02 sec)

3.2：查询多个列

  • 列名逗号隔开
mysql> select name,age from student;

+------+-----+
| name | age |
+------+-----+
| aaa  |  18 |
| bbb  |  19 |
| ccc  |  17 |
| bbb  |  20 |
+------+-----+
4 rows in set (0.02 sec)

3.3：检索所有列

  • 使用*通配符查询所有列
mysql> select * from student;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  1 | aaa  |  18 |     1 |
|  2 | bbb  |  19 |     1 |
|  3 | ccc  |  17 |     2 |
|  4 | bbb  |  20 |     2 |
+----+------+-----+-------+
4 rows in set (0.03 sec)

3.4：查询不同的行

  • 使用distinct关键字
  • distinct关键字作用于所有列
  • 置于列名的最前面
mysql> select distinct name from student;
+------+
| name |
+------+
| aaa  |
| bbb  |
| ccc  |
+------+
3 rows in set (0.03 sec)

重复的名称bbb只出现了一次。

3.5：限制结果

  • limit [x] 查询前x行

查询前2行：

mysql> select * from student limit 2;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  1 | aaa  |  18 |     1 |
|  2 | bbb  |  19 |     1 |
+----+------+-----+-------+
2 rows in set (0.03 sec)
  • limit [x],[y] 查询x行开始的y行，注意从0行开始
mysql> select * from student limit 1,2;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  2 | bbb  |  19 |     1 |
|  3 | ccc  |  17 |     2 |
+----+------+-----+-------+
2 rows in set (0.02 sec)

4:排序检索数据

  • 使用order by子句
  • 默认是升序
  • 升序 asc
  • 降序 desc

4.1：排序数据

  • 按照年龄排序数据，==默认升序==
mysql> select * from student order by age;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  3 | ccc  |  17 |     2 |
|  1 | aaa  |  18 |     1 |
|  2 | bbb  |  19 |     1 |
|  4 | bbb  |  20 |     2 |
+----+------+-----+-------+
4 rows in set (0.03 sec)

4.2：排序多个列

  • 先排序年龄，再排序班级
  • 逗号隔开列名
mysql> select * from student order by age,class;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  3 | ccc  |  17 |     2 |
|  1 | aaa  |  18 |     1 |
|  2 | bbb  |  19 |     1 |
|  4 | bbb  |  20 |     2 |
+----+------+-----+-------+
4 rows in set (0.02 sec)

4.3：指定排序方向

  • desc或者asc放在列名的后面
  • 作用域只限于前面对应的那一个列
  • 按照年龄降序排列，使用desc
mysql> select * from student order by age desc;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  4 | bbb  |  20 |     2 |
|  2 | bbb  |  19 |     1 |
|  1 | aaa  |  18 |     1 |
|  3 | ccc  |  17 |     2 |
+----+------+-----+-------+
4 rows in set (0.03 sec)

4.4：order by与limit的组合使用

  • 可以找出某数据前几名或者后几名等等
  • 子句位置
    • order by要跟在from后面
    • limit只能出现在order by后面

5：过滤数据

5.1：使用where子句

  • where子句要在from子句之后给出
  • 过滤多个列用and表示取交集，or表示取并集
  • 过滤出年龄等于19的同学：
mysql> select * from student where age=19;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  2 | bbb  |  19 |     1 |
+----+------+-----+-------+
1 row in set (0.03 sec)

5.2：where子句操作符

操作符说明
=等于
<>不等于
!=不等于
<小于
<=小于等于
大于
>=大于等于
between [x] and [y]在x和y之间

5.3：空值检查

  • 使用is null
select * from student where name is null;

6:数据过滤

6.1：and操作符

  • 满足过滤条件的数据的交集，组要满足所有条件
mysql> select * from student where name='aaa' and age=18;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  1 | aaa  |  18 |     1 |
+----+------+-----+-------+
1 row in set (0.02 sec)

6.2：or操作符

  • 检索匹配任意过滤条件的行
  • 取并集

mysql> select * from student where name='aaa' or class=1;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  1 | aaa  |  18 |     1 |
|  2 | bbb  |  19 |     1 |
+----+------+-----+-------+
2 rows in set (0.03 sec)

6.3：混合or和and

  • 过滤条件or和and同时都有时，要加括号明确目的

6.4：in操作符

  • 要满足在括号内的数据之一才能被匹配（闭区间，包括两侧）
mysql> select * from student where age in (17,18);

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  1 | aaa  |  18 |     1 |
|  3 | ccc  |  17 |     2 |
+----+------+-----+-------+
2 rows in set (0.03 sec)

6.5：not操作符

  • 否定它之后跟的任何条件
  • 作用域只是一个，而不是后面的全部过滤条件
mysql> select * from student where not name='aaa' and class=1;

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  2 | bbb  |  19 |     1 |
+----+------+-----+-------+
1 row in set (0.02 sec)

7：用通配符进行过滤

7.1：like操作符

  • 模糊查找
  • 匹配一部分文本

百分号（%）通配符

  • % 表示任意字符出现任意次数
  • 可以是出现0次

查询名字是a开头的学生

mysql> select * from student where name like "a%";
+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  1 | aaa  |  18 |     1 |
+----+------+-----+-------+
1 row in set (0.02 sec)

7.2：下划线（_）通配符

  • 匹配任意单个字符
  • 只能是一个，不能是0个
mysql> select * from student where name like "aa_";

+----+------+-----+-------+
| id | name | age | class |
+----+------+-----+-------+
|  1 | aaa  |  18 |     1 |
+----+------+-----+-------+
1 row in set (0.02 sec)

8：创建计算字段

8.1：拼接字段

  • 使用concat()函数进行拼接。
mysql> select concat(name,"(",class,"班)") from student;

+------------------------------+
| concat(name,"(",class,"班)") |
+------------------------------+
| aaa(1班)                     |
| bbb(1班)                     |
| ccc(2班)                     |
| bbb(2班)                     |
+------------------------------+
4 rows in set (0.04 sec)

8.2：使用别名

  • 别名用as关键字赋予
mysql> select concat(name,"(",class,"班)") as '名字与班级' from student;

+------------+
| 名字与班级 |
+------------+
| aaa(1班)   |
| bbb(1班)   |
| ccc(2班)   |
| bbb(2班)   |
+------------+
4 rows in set (0.06 sec)

8.3:执行算数计算

操作符说明
+加
-减
*乘
%除

学生年龄与班级的和：

mysql> select name,age+class as '名字与班级' from student;
+------+------------+
| name | 名字与班级 |
+------+------------+
| aaa  |         19 |
| bbb  |         20 |
| ccc  |         19 |
| bbb  |         22 |
+------+------------+
4 rows in set (0.07 sec)

9:使用数据处理函数

9.1：文本处理函数

  • rtrim()：除去文本右边的空格
  • ltrim()：除去文本左边的空格
  • upper()将文本转换为全大写
  • lower()文本转换为全小写

9.2：日期和时间处理函数

参考

  • date():返回日期部分
  • time()：返回时间部分

9.3：数值处理函数

函数参考，所有

10：汇总数据

10.1：avg()

  • 计算某列的平均值

一班学生平均年龄

mysql> select avg(age) as ageavg from student where class=1;
+---------+
| ageavg  |
+---------+
| 18.5000 |
+---------+
1 row in set (0.04 sec)

10.2：count()

  • 计算符合条件的数据的总条数
  • count(*)：对表中行的数目进行计算，包括列含有null的
  • count(column):对特定列中具有值的行进行计数，不包括null。

查询一班的总人数

mysql> select count(*) as '一班人数'  from student where class=1;

+----------+
| 一班人数 |
+----------+
|        2 |
+----------+
1 row in set (0.03 sec)

10.3：max()

  • 返回列中最大值

查询一班年龄最大的同学：

mysql> select max(age) as '最大年龄'  from student where class=1;
+----------+
| 最大年龄 |
+----------+
|       19 |
+----------+
1 row in set (0.04 sec)

10.4：min()

  • 查询列的最小数据
  • 与max()使用方法类似

10.5：sum()

  • 对某列求和

一班年龄和：

mysql> select sum(age) as '年龄和'  from student where class=1;

+--------+
| 年龄和 |
+--------+
| 37     |
+--------+
1 row in set (0.04 sec)

10.5：聚集不同值

  • 使用distinct关键字
  • 默认是all也就是全部值

班级个数查询：

mysql> select count(distinct class) as “班级个数” from student;

+------------+
| “班级个数” |
+------------+
|          2 |
+------------+
1 row in set (0.06 sec)

==一个select查询语句中可以有多个聚集函数==

11：分组数据

11.1：创建分组

  • group by子句
mysql> select class,count(*) from student group by class;
+-------+----------+
| class | count(*) |
+-------+----------+
|     1 |        2 |
|     2 |        2 |
+-------+----------+
2 rows in set (0.03 sec)

11.2：过滤分组

  • having 子句
  • having与where的区别
    • where在数据分组前进行过滤
    • having在数据分组后进行过滤
mysql> select class,count(*) from student where age<=19 group by class;
+-------+----------+
| class | count(*) |
+-------+----------+
|     1 |        2 |
|     2 |        1 |
+-------+----------+
2 rows in set (0.03 sec)


mysql> select class,count(*) as sum from student where age<=19 group by class having sum>1 ;
+-------+-----+
| class | sum |
+-------+-----+
|     1 |   2 |
+-------+-----+
1 row in set (0.02 sec)

select子句顺序

  1. select
  2. from
  3. where
  4. group by
  5. having
  6. order by
  7. limit

12：子查询

  • 新表：
    • 订单表（order)
      • 订单号 (id)
      • 客户ID（customid)
      • 物品id (proid)
    • 客户表（custom)
      • 客户id(id)
      • 客户名字 （name)
    • 物品表（product)
      • 物品Id（id)
      • 物品名称（name)

12.1:利用子查询进行过滤

  • 能够将多条查询语句合为一条语句

查询包含订单的物品ID为1的用户信息

  • 首先要查询订单物品为ID=1的订单对应的客户I’d
  • 然后去客户表中查询客户信息
mysql> select customid from orders where proid=1;
+----------+
| customid |
+----------+
|        1 |
|        2 |
+----------+
2 rows in set (0.04 sec)

mysql> select * from custom where id in(1,2);
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
+----+------+
2 rows in set (0.05 sec)
  • 使用子查询
mysql> select * from custom where id in (select customid from orders where proid=1);
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
+----+------+
2 rows in set (0.05 sec)

12.2:作为计算字段使用子查询

查询每个客户的订单总数：

  • 在客户表中查询到所有客户
  • 对于检索出的每个客户，在订单表中查询其订单数目
mysql> select name, (select count(*) from orders where customid=custom.id)  from custom;
+------+--------------------------------------------------------+
| name | (select count(*) from orders where customid=custom.id) |
+------+--------------------------------------------------------+
| aaa  |                                                      1 |
| bbb  |                                                      2 |
+------+--------------------------------------------------------+
2 rows in set (0.06 sec)

13：联接表

13.1:创建联接

  • from后面可以接多个表
  • 使用where设置如何联接
  • 实际上就是前面的表每一行与后面的表每一行进行配对，where子句过滤掉不符合条件的行
mysql> select orders.id,custom.name,product.name from orders,custom,product  where orders.customid=custom.id and orders.proid=product.id;
+----+------+--------+
| id | name | name   |
+----+------+--------+
|  1 | aaa  | 可乐   |
|  2 | bbb  | 可乐   |
|  3 | bbb  | 西红柿 |
+----+------+--------+
3 rows in set (0.05 sec)

13.2：内部联接（等值联接）

  • 上面的联接也叫内部联接
  • 可以用inner join ........on.......语句实现
  • 这种语法就使用on而不是where了
 select orders.id,custom.name,product.name 
 from orders inner join custom inner join product 
 on orders.customid=custom.id and orders.proid=product.id;
+----+------+--------+
| id | name | name   |
+----+------+--------+
|  1 | aaa  | 可乐   |
|  2 | bbb  | 可乐   |
|  3 | bbb  | 西红柿 |
+----+------+--------+
3 rows in set (0.04 sec)
  • where子句可以用来作为联接后进行过滤
mysql> select orders.id,custom.name,product.name 
from orders inner join custom inner join product 
on orders.customid=custom.id and orders.proid=product.id 
where product.name='西红柿';
+----+------+--------+
| id | name | name   |
+----+------+--------+
|  3 | bbb  | 西红柿 |
+----+------+--------+
1 row in set (0.04 sec)

14：创建高级联接

14.1：表的别名

  • 表也可以取别名
mysql> select o.id,c.name,p.name 
from orders as o inner join custom as c inner join product as p 
on o.customid=c.id and o.proid=p.id;
+----+------+--------+
| id | name | name   |
+----+------+--------+
|  1 | aaa  | 可乐   |
|  2 | bbb  | 可乐   |
|  3 | bbb  | 西红柿 |
+----+------+--------+
3 rows in set (0.06 sec)

14.2：自联接

  • p108看书
  • 对于同一个表的查询使用自联接好一些，比起子查询

14.3：自然连接

  • 自然排除多次出现，使每个列只返回一次。
  • 自己实现，我们使用的所有联接一班都是自然连接，不会去把列多次重复出现

14.4：外部联接

  • 应该就是叫做联接或者右联接
  • left/right outer join
  • left左联接，在两个表做积后，通过过滤条件后，左边的表会被全部保留，即时没有对应的行，比起内部联接就是多了那一部分没有右边的与其对应的行
  • 右连接类似

这个就多了右边用户表的ccc行，即时没有订单与其对应。

mysql> select c.*,o.proid  
from orders as o right outer join custom as c 
on o.customid=c.id;
+----+------+-------+
| id | name | proid |
+----+------+-------+
|  1 | aaa  |     1 |
|  2 | bbb  |     1 |
|  2 | bbb  |     2 |
|  3 | ccc  | NULL  |
+----+------+-------+
4 rows in set (0.04 sec)

15：组合查询

  • 多个查询结果集取并，需要列相同

15.1：创建组合查询

  • 使用union关键字
  • 默认会去掉重复的行
  • union all不会去掉重复的行
mysql> select * from custom where id<=2 
    -> union
    -> select * from custom where id=3;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.04 sec)

16：插入数据

16.1：插入完整的行

mysql> insert into custom
    -> values(
    -> 4 ,'ddd');
Query OK, 1 row affected (0.01 sec)

16.2：插入多个行

mysql> insert into custom(name)
    -> values(
    -> "eee");
Query OK, 1 row affected (0.01 sec)

16.3：插入检索出的数据

mysql> insert into custom(name)
    -> select name from custom;
Query OK, 5 rows affected (0.02 sec)

+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
|  5 | eee  |
|  6 | aaa  |
|  7 | bbb  |
|  8 | ccc  |
|  9 | ddd  |
| 10 | eee  |
+----+------+

17：更新和删除数据

17.1：更新数据

mysql> update custom set name='abc' where name='eee';
Query OK, 2 rows affected (0.01 sec)

17.2：删除数据

mysql> delete from custom where id>5;
Query OK, 5 rows affected (0.02 sec)
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
|  5 | abc  |
+----+------+

18：表操作

18.1：添加列

mysql> alter table custom 
    -> add age int;
Query OK, 0 rows affected (0.14 sec)

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aaa  | NULL |
|  2 | bbb  | NULL |
|  3 | ccc  | NULL |
|  4 | ddd  | NULL |
|  5 | abc  | NULL |
+----+------+------+

18.2：删除表

drop tab表名]
avatar
文章
阅读
粉丝