MySQL语句

最新推荐文章于 2022-10-19 04:26:53 发布

原创最新推荐文章于 2022-10-19 04:26:53 发布 · 157 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

13 篇文章

订阅专栏

DML 数据操纵语言(Data Manipulation Language)

DDL 数据定义语言(Data Definition Language)

DQL 数据查询语言

DCL 数据控制语言

增加数据

mysql> create database db1;  //创建数据库
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
mysql> use db1;  //使用数据库
mysql> create table t1(id int , name varchar(255));   //创建一个表。并指定字段
mysql> insert into t1 values (11,"alice");   //向表中添加一条数据

mysql> show tables;  //查看表
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;   //查询表中所有记录
+------+-------+
| id   | name  |
+------+-------+
|   11 | alice |
+------+-------+
1 row in set (0.00 sec)

mysql>

另一种添加：

mysql> insert into t1 (id) value (12); //这时只指定了一个字段，所以另一个name为空。

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|   12 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql>

修改数据

mysql> update t1 set name="kate" where id=11;  //修改ID是11 的记录，neme改为kate
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|   11 | kate |
+------+------+
1 row in set (0.00 sec)

mysql> update t10 set name=“niuc”;
不指定修改条件
mysql> select * from t10;
如果不指定修改条件那么是很危险的，会修改所有的记录。

删除数据：

mysql> delete from t1 where id=11; //删除id位11 的记录。
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
Empty set (0.00 sec

mysql> delete from t10;
mysql> select * from t10;
Empty set (0.00 sec)
这个也是不指定删除，删除所有

数据导入数据库：

原始数据
[root@localhost datafile]$ cat shop.txt 
id name price city street date
1001 .1niu1 5.99 shenyang wuaijie 2013/11/12
1002 .2niu2 6.99 shenyang wuaijie 2013/11/13
1003 .3niu3 9.99 shenyang wuaijie 2013/11/15
1004 4niu4 5.39 beijing zhongguancun 2015/1/5
1005 5niu5 10.9 beijing zhongguancun 2015/1/10
1006 6niu6 20 beijing zhongguancun 2015/1/25
1007 wangqi 10.5 shanghai pudong 2017/10/11
1008 maba 100 shanghai pudong 2017/10/16
1009 zhaojiu 130 shanghai pudong 2018/3/12
1010 zhoushi 200 shenyang sanhaojie 2018/3/15

建一个表，表的字段和数据格式要和这个原始数据相匹配。

mysql> create table shop (id int, name varchar(255),proce float(5,2),city varchar(255),street varchar(255),date date );

mysql> load data local infile "/tmp/gsc-test/datafile/shop.txt" into table shop fields terminated by ' ';  

//fields terminated by " "指定分隔符为空格，如果不写这些的话可能会有数据导入失败。
//整段可以翻译一下，导入数据从文件“XXXXXXX”，到表 “XXX”  指定分隔符为“X”

mysql> select * from shop
    -> ;
+------+---------+--------+----------+--------------+------------+
| id   | name    | proce  | city     | street       | date       |
+------+---------+--------+----------+--------------+------------+
|    0 | name    |   0.00 | city     | street       | 0000-00-00 |
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie      | 2013-11-12 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie      | 2013-11-13 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie      | 2013-11-15 |
| 1004 | 4niu4   |   5.39 | beijing  | zhongguancun | 2015-01-05 |
| 1005 | 5niu5   |  10.90 | beijing  | zhongguancun | 2015-01-10 |
| 1006 | 6niu6   |  20.00 | beijing  | zhongguancun | 2015-01-25 |
| 1007 | wangqi  |  10.50 | shanghai | pudong       | 2017-10-11 |
| 1008 | maba    | 100.00 | shanghai | pudong       | 2017-10-16 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong       | 2018-03-12 |
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie    | 2018-03-15 |
+------+---------+--------+----------+--------------+------------+
11 rows in set (0.00 sec)

DQL数据查询语言：
1. 全表查询

mysql> select * from shop;

尽量少用

2. 单列查询

mysql> select id,name from shop ;
+------+---------+
| id   | name    |
+------+---------+
|    0 | name    |
| 1001 | .1niu1  |
| 1002 | .2niu2  |
| 1003 | .3niu3  |
| 1004 | 4niu4   |
| 1005 | 5niu5   |
| 1006 | 6niu6   |
| 1007 | wangqi  |
| 1008 | maba    |
| 1009 | zhaojiu |
| 1010 | zhoushi |
+------+---------+
11 rows in set (0.00 sec)

3. 指定条件查询 where

mysql> select * from shop where name="6niu6";
+------+-------+-------+---------+--------------+------------+
| id   | name  | proce | city    | street       | date       |
+------+-------+-------+---------+--------------+------------+
| 1006 | 6niu6 | 20.00 | beijing | zhongguancun | 2015-01-25 |
+------+-------+-------+---------+--------------+------------+
1 row in set (0.00 sec)

(1)使用比较运算符
> < >= <= != <> between and
很好理解，大于小于，大于等于。

那个between and是在什么和什么之间。等价于 <= and >=
(2)使用逻辑运算符
and or !
这个也很好理解，and与，or或！非。

mysql> select * from shop where id<=1003 or id >= 1009;
+------+---------+--------+----------+-----------+------------+
| id   | name    | proce  | city     | street    | date       |
+------+---------+--------+----------+-----------+------------+
|    0 | name    |   0.00 | city     | street    | 0000-00-00 |
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie   | 2013-11-12 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie   | 2013-11-13 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie   | 2013-11-15 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong    | 2018-03-12 |
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie | 2018-03-15 |
+------+---------+--------+----------+-----------+------------+
6 rows in set (0.00 sec)

(3)使用算数运算符
+ - * / %

mysql> select name,proce+100 as new_proce from shop ;
+---------+-----------+
| name    | new_proce |
+---------+-----------+
| name    |    100.00 |
| .1niu1  |    105.99 |
| .2niu2  |    106.99 |
| .3niu3  |    109.99 |
| 4niu4   |    105.39 |
| 5niu5   |    110.90 |
| 6niu6   |    120.00 |
| wangqi  |    110.50 |
| maba    |    200.00 |
| zhaojiu |    230.00 |
| zhoushi |    300.00 |
+---------+-----------+
11 rows in set (0.00 sec)
as后边接别名，另外也可以用""双引号来表示别名。

(4)多值匹配
in not in 他们后边接（值1，值2…）符合的就被搜索到。

mysql> select * from shop where id in (1001,1003,1004);
+------+--------+-------+----------+--------------+------------+
| id   | name   | proce | city     | street       | date       |
+------+--------+-------+----------+--------------+------------+
| 1001 | .1niu1 |  5.99 | shenyang | wuaijie      | 2013-11-12 |
| 1003 | .3niu3 |  9.99 | shenyang | wuaijie      | 2013-11-15 |
| 1004 | 4niu4  |  5.39 | beijing  | zhongguancun | 2015-01-05 |
+------+--------+-------+----------+--------------+------------+
3 rows in set (0.00 sec)

(5)空值匹配
NULL not NULL
注意，没有内容的不代表为空，只有些NULL的才是空值。

(6) 模糊查询
like
通配符：
_ ：表示任意的单个字符
% ：表示任意的字符

mysql> select * from shop where city like "sh%";
+------+---------+--------+----------+-----------+------------+
| id   | name    | proce  | city     | street    | date       |
+------+---------+--------+----------+-----------+------------+
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie   | 2013-11-12 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie   | 2013-11-13 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie   | 2013-11-15 |
| 1007 | wangqi  |  10.50 | shanghai | pudong    | 2017-10-11 |
| 1008 | maba    | 100.00 | shanghai | pudong    | 2017-10-16 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong    | 2018-03-12 |
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie | 2018-03-15 |
+------+---------+--------+----------+-----------+------------+

_下划线表示1个字符，也就是sh后有且只有1个字符的。并没有这个项。

mysql> select * from shop where city like "sh_";
Empty set (0.00 sec)

说明：
like比较消耗时间。
不要过度使用；
不要把通配符%放在开始的位置，%i

(7) 正则表达式
regexp

. * ^ $ + ? { }
\ < \ > 就等于 [ [ : < : ] ] [ [ : > : ] ]
在这里不支持 \ < \ >
转义符号 \\ 2个斜杠才是转义。

mysql> select * from shop where name regexp "^\\."; //\\双反斜杠才是转义，表示以.开头的。
+------+--------+-------+----------+---------+------------+
| id   | name   | proce | city     | street  | date       |
+------+--------+-------+----------+---------+------------+
| 1001 | .1niu1 |  5.99 | shenyang | wuaijie | 2013-11-12 |
| 1002 | .2niu2 |  6.99 | shenyang | wuaijie | 2013-11-13 |
| 1003 | .3niu3 |  9.99 | shenyang | wuaijie | 2013-11-15 |
+------+--------+-------+----------+---------+------------+
3 rows in set (0.00 sec)

mysql> select * from shop where name regexp "^\.";
+------+---------+--------+----------+--------------+------------+
| id   | name    | proce  | city     | street       | date       |
+------+---------+--------+----------+--------------+------------+
|    0 | name    |   0.00 | city     | street       | 0000-00-00 |
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie      | 2013-11-12 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie      | 2013-11-13 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie      | 2013-11-15 |
| 1004 | 4niu4   |   5.39 | beijing  | zhongguancun | 2015-01-05 |
| 1005 | 5niu5   |  10.90 | beijing  | zhongguancun | 2015-01-10 |
| 1006 | 6niu6   |  20.00 | beijing  | zhongguancun | 2015-01-25 |
| 1007 | wangqi  |  10.50 | shanghai | pudong       | 2017-10-11 |
| 1008 | maba    | 100.00 | shanghai | pudong       | 2017-10-16 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong       | 2018-03-12 |
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie    | 2018-03-15 |
+------+---------+--------+----------+--------------+------------+
11 rows in set (0.00 sec)
//如果只写一个的话并不会转义。

4. 对输出结果进行排序
order by

mysql> select * from shop order by proce;
+------+---------+--------+----------+--------------+------------+
| id   | name    | proce  | city     | street       | date       |
+------+---------+--------+----------+--------------+------------+
|    0 | name    |   0.00 | city     | street       | 0000-00-00 |
| 1004 | 4niu4   |   5.39 | beijing  | zhongguancun | 2015-01-05 |
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie      | 2013-11-12 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie      | 2013-11-13 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie      | 2013-11-15 |
| 1007 | wangqi  |  10.50 | shanghai | pudong       | 2017-10-11 |
| 1005 | 5niu5   |  10.90 | beijing  | zhongguancun | 2015-01-10 |
| 1006 | 6niu6   |  20.00 | beijing  | zhongguancun | 2015-01-25 |
| 1008 | maba    | 100.00 | shanghai | pudong       | 2017-10-16 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong       | 2018-03-12 |
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie    | 2018-03-15 |
+------+---------+--------+----------+--------------+------------+
11 rows in set (0.00 sec)

倒是也能对非数值的排序但是没啥意义。

mysql> select * from shop order by name;
+------+---------+--------+----------+--------------+------------+
| id   | name    | proce  | city     | street       | date       |
+------+---------+--------+----------+--------------+------------+
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie      | 2013-11-12 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie      | 2013-11-13 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie      | 2013-11-15 |
| 1004 | 4niu4   |   5.39 | beijing  | zhongguancun | 2015-01-05 |
| 1005 | 5niu5   |  10.90 | beijing  | zhongguancun | 2015-01-10 |
| 1006 | 6niu6   |  20.00 | beijing  | zhongguancun | 2015-01-25 |
| 1008 | maba    | 100.00 | shanghai | pudong       | 2017-10-16 |
|    0 | name    |   0.00 | city     | street       | 0000-00-00 |
| 1007 | wangqi  |  10.50 | shanghai | pudong       | 2017-10-11 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong       | 2018-03-12 |
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie    | 2018-03-15 |
+------+---------+--------+----------+--------------+------------+
11 rows in set (0.00 sec)

在后边加 desc 表示倒叙、

mysql> select * from shop order by id desc;
+------+---------+--------+----------+--------------+------------+
| id   | name    | proce  | city     | street       | date       |
+------+---------+--------+----------+--------------+------------+
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie    | 2018-03-15 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong       | 2018-03-12 |
| 1008 | maba    | 100.00 | shanghai | pudong       | 2017-10-16 |
| 1007 | wangqi  |  10.50 | shanghai | pudong       | 2017-10-11 |
| 1006 | 6niu6   |  20.00 | beijing  | zhongguancun | 2015-01-25 |
| 1005 | 5niu5   |  10.90 | beijing  | zhongguancun | 2015-01-10 |
| 1004 | 4niu4   |   5.39 | beijing  | zhongguancun | 2015-01-05 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie      | 2013-11-15 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie      | 2013-11-13 |
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie      | 2013-11-12 |
|    0 | name    |   0.00 | city     | street       | 0000-00-00 |
+------+---------+--------+----------+--------------+------------+
11 rows in set (0.01 sec)

5. 限制输出结果
limit

mysql> select * from shop limit 0,3;
+------+--------+-------+----------+---------+------------+
| id   | name   | proce | city     | street  | date       |
+------+--------+-------+----------+---------+------------+
|    0 | name   |  0.00 | city     | street  | 0000-00-00 |
| 1001 | .1niu1 |  5.99 | shenyang | wuaijie | 2013-11-12 |
| 1002 | .2niu2 |  6.99 | shenyang | wuaijie | 2013-11-13 |
+------+--------+-------+----------+---------+------------+
3 rows in set (0.00 sec)

显示前3行，我们常见的分页，就是用这个来做的。

6. 去掉重复输出
distinct

mysql> select distinct city from shop ;
+----------+
| city     |
+----------+
| city     |
| shenyang |
| beijing  |
| shanghai |
+----------+
4 rows in set (0.00 sec)

7. 分组查询
group by
group有一个大牛说的非常清晰。我就不细说了。
大佬的解释
贴上链接。

总结起来就是group时候要用聚合函数。
group_concat就是一个聚合函数，讲内容拼接成字符串。

mysql> select city,group_concat(id),group_concat(name) from shop group by city;
+----------+---------------------+------------------------------+
| city     | group_concat(id)    | group_concat(name)           |
+----------+---------------------+------------------------------+
| beijing  | 1004,1005,1006      | 4niu4,5niu5,6niu6            |
| city     | 0                   | name                         |
| shanghai | 1007,1008,1009      | wangqi,maba,zhaojiu          |
| shenyang | 1001,1002,1003,1010 | .1niu1,.2niu2,.3niu3,zhoushi |
+----------+---------------------+------------------------------+
4 rows in set (0.00 sec)

8. mysql的函数
(1)字符串函数

concat() 字符串连接

mysql> select concat(city,"市",street) from shop;
+---------------------------+
| concat(city,"市",street)  |
+---------------------------+
| city市street              |
| shenyang市wuaijie         |
| shenyang市wuaijie         |
| shenyang市wuaijie         |
| beijing市zhongguancun     |
| beijing市zhongguancun     |
| beijing市zhongguancun     |
| shanghai市pudong          |
| shanghai市pudong          |
| shanghai市pudong          |
| shenyang市sanhaojie       |
+---------------------------+
11 rows in set (0.00 sec)

mysql>

这个能识别中文是因为字符集编码为utf8

lower() 大写转小写
upper() 小写转大写

length() 字符串的长度

(2)数据汇总函数
count() 统计

mysql> select * from shop;
+------+---------+--------+----------+--------------+------------+
| id   | name    | proce  | city     | street       | date       |
+------+---------+--------+----------+--------------+------------+
|    0 | name    |   0.00 | city     | street       | 0000-00-00 |
| 1001 | .1niu1  |   5.99 | shenyang | wuaijie      | 2013-11-12 |
| 1002 | .2niu2  |   6.99 | shenyang | wuaijie      | 2013-11-13 |
| 1003 | .3niu3  |   9.99 | shenyang | wuaijie      | 2013-11-15 |
| 1004 | 4niu4   |   5.39 | beijing  | zhongguancun | 2015-01-05 |
| 1005 | 5niu5   |  10.90 | beijing  | zhongguancun | 2015-01-10 |
| 1006 | 6niu6   |  20.00 | beijing  | zhongguancun | 2015-01-25 |
| 1007 | wangqi  |  10.50 | shanghai | pudong       | 2017-10-11 |
| 1008 | maba    | 100.00 | shanghai | pudong       | 2017-10-16 |
| 1009 | zhaojiu | 130.00 | shanghai | pudong       | 2018-03-12 |
| 1010 | zhoushi | 200.00 | shenyang | sanhaojie    | 2018-03-15 |
| 1111 | NULL    |   NULL | NULL     | NULL         | NULL       |
+------+---------+--------+----------+--------------+------------+
12 rows in set (0.00 sec)

mysql> select count(*)from shop ;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

mysql> select count(name) from shop ;
+-------------+
| count(name) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

count不支持空值，有空的话就少了。

sum() 求和，要求是数值型数据

mysql> select sum(id) from shop;
+---------+
| sum(id) |
+---------+
|   11166 |
+---------+
1 row in set (0.00 sec)
//正常来说id是不能做sum的，虽然技术上可行，不过为了解释sum嘛，不严谨就不严谨了吧。

avg() 求平均数，要求是数值型数据

mysql> select avg(id) from shop;
+----------+
| avg(id)  |
+----------+
| 930.5000 |
+----------+
1 row in set (0.01 sec)

max() 求最大值
min() 求最小值

mysql> select max(id) from shop;
+---------+
| max(id) |
+---------+
|    1111 |
+---------+
1 row in set (0.00 sec)

mysql> select min(id) from shop;
+---------+
| min(id) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

忽略空值

(3)时间和日期函数
date() 返回日期
time() 返回时间
year() 返回年
month() 返回月
day() 返回日

mysql> select year(date) from shop;;
+------------+
| year(date) |
+------------+
|          0 |
|       2013 |
|       2013 |
|       2013 |
|       2015 |
|       2015 |
|       2015 |
|       2017 |
|       2017 |
|       2018 |
|       2018 |
|       NULL |
+------------+
12 rows in set (0.00 sec)
mysql> select month(date) from shop;
+-------------+
| month(date) |
+-------------+
|           0 |
|          11 |
|          11 |
|          11 |
|           1 |
|           1 |
|           1 |
|          10 |
|          10 |
|           3 |
|           3 |
|        NULL |
+-------------+
12 rows in set (0.00 sec)

这个用的不多。

(4)其他函数

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-11-13 19:40:35 |
+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.00 sec)

这个就看着玩吧。