学习数据库MySQL

最新推荐文章于 2024-06-04 09:46:20 发布

一波万波

最新推荐文章于 2024-06-04 09:46:20 发布

阅读量436

点赞数

本文链接：https://blog.csdn.net/yibowanbo/article/details/80233098

版权

javascript 同时被 3 个专栏收录

15 篇文章 0 订阅

订阅专栏

nodejs

6 篇文章 0 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

查询本机数据库: show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

创建数据库: create database ‘数据库名字’;

create database test;
Query OK, 1 row affected (0.01 sec)

查询数据库下的表：show tables;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| article        |
| article2       |
| cagety         |
| user           |
| wechat         |
| wechat_list    |
+----------------+
6 rows in set (0.00 sec)

选择数据库: use test;

mysql> use test;
Database changed

向数据表插入数据: insert into article (‘键值’, ‘键值’, ‘键值’) values (‘内容’, ‘内容’, ‘内容’)

mysql> # 插入数据
mysql>
mysql> insert into article (title, author, date)
    -> values
    -> ('我想吃肉', 'bob', now());
Query OK, 1 row affected, 1 warning (0.01 sec)

查询数据表下的结构：show columns from ‘数据表名称’;

mysql> show columns from article;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| title  | varchar(50) | YES  |     | NULL    |                |
| author | varchar(50) | YES  |     | NULL    |                |
| date   | date        | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

查询数据表下的所有内容：select * from ‘数据库表’;

mysql> select * from article;
+----+-----------------------------------+---------------+------------+
| id | title                             | author        | date       |
+----+-----------------------------------+---------------+------------+
|  1 | 今晚聚餐哦!                       | bob           | 2018-05-01 |
|  2 | 我想吃肉                          | bob           | 2018-05-01 |
|  4 | 好呀，今晚买多点牛肉丸            | shaw          | 2018-05-01 |
|  6 | 一波万波                          | yibowanbo.com | 2018-04-30 |
|  8 | 一波                              | bo            | 2018-05-01 |
|  9 | 万波                              | wanbo         | 2018-05-01 |
+----+-----------------------------------+---------------+------------+
6 rows in set (0.00 sec)

mysql>

根据条件查询数据表下的内容：

select * from ‘数据库表’ where ‘键值’ = ‘条件’;

select ‘键值’ from ‘数据库表’;

mysql> select * from article where id = 2;
+----+--------------+--------+------------+
| id | title        | author | date       |
+----+--------------+--------+------------+
|  2 | 我想吃肉     | bob    | 2018-05-01 |
+----+--------------+--------+------------+
1 row in set (0.00 sec)

mysql>

# 也是可以使用like，like等于=号

mysql> select * from article where id like 2;
+----+--------------+--------+------------+
| id | title        | author | date       |
+----+--------------+--------+------------+
|  2 | 我想吃肉     | bob    | 2018-05-01 |
+----+--------------+--------+------------+
1 row in set (0.00 sec)

mysql>

mysql> select author from article;
+---------------+
| author        |
+---------------+
| bob           |
| bob           |
| shaw          |
| yibowanbo.com |
| bo            |
| wanbo         |
+---------------+
6 rows in set (0.00 sec)

mysql>

修改数据表数据：update ‘数据库表’ set ‘要修改的字段键值’ = ‘修改的内容’ where 条件

update article set title='今晚聚餐哦!' where id = 1;

mysql> update article set title='今晚聚餐哦!' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除数据表里面的数据：delete from ‘数据表’ where 条件;

delete from article where id = 3;

mysql> # 删除数据表里面的数据
mysql>
mysql> delete from article where id = 3;
Query OK, 1 row affected (0.00 sec)

union用法：select ‘键’ from ‘数据库表1’ union select ‘键’ from ‘数据库表2’;

链接查询多表之间相同的键值，返回不重复键值

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

mysql> select * from user;
+----+-----------+
| id | username  |
+----+-----------+
|  1 | 林大和    |
|  4 | 梁凤波    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select * from wechat_list;
+----+-----------+
| id | username  |
+----+-----------+
|  1 | 量风波    |
|  2 | 梁凤波    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select username from user union select username from wechat_list;
+-----------+
| username  |
+-----------+
| 林大和    |
| 梁凤波    |
| 量风波    |
+-----------+
3 rows in set (0.00 sec)

mysql>

降升排序：select * from ‘数据库表’ order by 键值 ASC;

select * from '数据库表' order by 键值 ASC;

ASC升序

DESC 降序

mysql>
mysql>
mysql> select * from article order by title ASC;
+----+-----------------------------------+---------------+------------+
| id | title                             | author        | date       |
+----+-----------------------------------+---------------+------------+
|  6 | 一波万波                          | yibowanbo.com | 2018-04-30 |
|  1 | 今晚聚餐哦!                       | bob           | 2018-05-01 |
|  4 | 好呀，今晚买多点牛肉丸            | shaw          | 2018-05-01 |
|  2 | 我想吃肉                          | bob           | 2018-05-01 |
+----+-----------------------------------+---------------+------------+
4 rows in set (0.00 sec)

mysql>
mysql> select * from article order by title DESC;
+----+-----------------------------------+---------------+------------+
| id | title                             | author        | date       |
+----+-----------------------------------+---------------+------------+
|  2 | 我想吃肉                          | bob           | 2018-05-01 |
|  4 | 好呀，今晚买多点牛肉丸            | shaw          | 2018-05-01 |
|  1 | 今晚聚餐哦!                       | bob           | 2018-05-01 |
|  6 | 一波万波                          | yibowanbo.com | 2018-04-30 |
+----+-----------------------------------+---------------+------------+
4 rows in set (0.00 sec)

统计分组：select date, count(*) from article group by date;

mysql> select * from article;
+----+-----------------------------------+---------------+------------+
| id | title                             | author        | date       |
+----+-----------------------------------+---------------+------------+
|  1 | 今晚聚餐哦!                       | bob           | 2018-05-01 |
|  2 | 我想吃肉                          | bob           | 2018-05-01 |
|  4 | 好呀，今晚买多点牛肉丸            | shaw          | 2018-05-01 |
|  6 | 一波万波                          | yibowanbo.com | 2018-04-30 |
+----+-----------------------------------+---------------+------------+
4 rows in set (0.00 sec)

mysql>
mysql> select date, count(*) from article group by date;
+------------+----------+
| date       | count(*) |
+------------+----------+
| 2018-04-30 |        1 |
| 2018-05-01 |        3 |
+------------+----------+
2 rows in set (0.00 sec)

NULL运用

# 直接等是查询不出来的，需要用is null或is not null

mysql> select * from cagety where count like NULL;
Empty set (0.00 sec)

mysql>
mysql> select * from cagety where count is NULL;
+--------+-------+
| author | count |
+--------+-------+
| go     |  NULL |
| css    |  NULL |
+--------+-------+
2 rows in set (0.00 sec)

mysql>
mysql> select * from cagety where count is not null;
+--------+-------+
| author | count |
+--------+-------+
| go     |    20 |
| html   |    20 |
| nodejs |    10 |
| php    |    30 |
+--------+-------+
4 rows in set (0.00 sec)

mysql>

mysql 正则使用

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

mysql> select * from article
    -> ;
+----+-----------------------------------+---------------+------------+
| id | title                             | author        | date       |
+----+-----------------------------------+---------------+------------+
|  1 | 今晚聚餐哦!                       | bob           | 2018-05-01 |
|  2 | 我想吃肉                          | bob           | 2018-05-01 |
|  4 | 好呀，今晚买多点牛肉丸            | shaw          | 2018-05-01 |
|  6 | 一波万波                          | yibowanbo.com | 2018-04-30 |
|  8 | 一波                              | bo            | 2018-05-01 |
|  9 | 万波                              | wanbo         | 2018-05-01 |
+----+-----------------------------------+---------------+------------+
6 rows in set (0.00 sec)

mysql>
mysql> select title from article where title  REGEXP '^波';
Empty set (0.00 sec)

mysql>
mysql>
mysql> select title from article where title  REGEXP '^一';
+--------------+
| title        |
+--------------+
| 一波万波     |
| 一波         |
+--------------+
2 rows in set (0.00 sec)

mysql>
mysql> select title from article where title  REGEXP '波$';
+--------------+
| title        |
+--------------+
| 一波万波     |
| 一波         |
| 万波         |
+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> select author from article where author regexp 'o';
+---------------+
| author        |
+---------------+
| bob           |
| bob           |
| yibowanbo.com |
| bo            |
| wanbo         |
+---------------+
5 rows in set (0.00 sec)

增加字段

mysql> # 增加字段
mysql>
mysql> alter table article add content varchar(200);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> show columns from article;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| title   | varchar(100) | NO   |     | NULL    |                |
| author  | varchar(40)  | NO   |     | NULL    |                |
| date    | date         | YES  |     | NULL    |                |
| content | varchar(200) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> # 删除字段

删除字段

mysql>
mysql> alter table article drop content;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> show columns from article;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| title  | varchar(100) | NO   |     | NULL    |                |
| author | varchar(40)  | NO   |     | NULL    |                |
| date   | date         | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from article;
+----+-----------------------------------+---------------+------------+
| id | names                             | author        | date       |
+----+-----------------------------------+---------------+------------+
|  1 | 今晚聚餐哦!                       | bob           | 2018-05-01 |
|  2 | 我想吃肉                          | bob           | 2018-05-01 |
|  4 | 好呀，今晚买多点牛肉丸            | shaw          | 2018-05-01 |
|  6 | 一波万波                          | yibowanbo.com | 2018-04-30 |
|  8 | 一波                              | bo            | 2018-05-01 |
|  9 | 万波                              | wanbo         | 2018-05-01 |
+----+-----------------------------------+---------------+------------+
6 rows in set (0.00 sec)

修改字段的类型和键值

mysql>
mysql> alter table article change names title varchar(50);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from article;
+----+-----------------------------------+---------------+------------+
| id | title                             | author        | date       |
+----+-----------------------------------+---------------+------------+
|  1 | 今晚聚餐哦!                       | bob           | 2018-05-01 |
|  2 | 我想吃肉                          | bob           | 2018-05-01 |
|  4 | 好呀，今晚买多点牛肉丸            | shaw          | 2018-05-01 |
|  6 | 一波万波                          | yibowanbo.com | 2018-04-30 |
|  8 | 一波                              | bo            | 2018-05-01 |
|  9 | 万波                              | wanbo         | 2018-05-01 |
+----+-----------------------------------+---------------+------------+
6 rows in set (0.00 sec)

修改字段的类型

mysql>
mysql> alter table article modify author varchar(50);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> show columns from article;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| title  | varchar(50) | YES  |     | NULL    |                |
| author | varchar(50) | YES  |     | NULL    |                |
| date   | date        | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

修改设置默认值

mysql>
mysql> alter table article alter date set default '2018-05-03';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from article;
+--------+-------------+------+-----+------------+----------------+
| Field  | Type        | Null | Key | Default    | Extra          |
+--------+-------------+------+-----+------------+----------------+
| id     | int(11)     | NO   | PRI | NULL       | auto_increment |
| title  | varchar(50) | YES  |     | NULL       |                |
| author | varchar(50) | YES  |     | NULL       |                |
| date   | date        | YES  |     | 2018-05-03 |                |
+--------+-------------+------+-----+------------+----------------+
4 rows in set (0.00 sec)

mysql>

删除默认值

mysql> alter table article alter date drop default;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from article;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| title  | varchar(50) | YES  |     | NULL    |                |
| author | varchar(50) | YES  |     | NULL    |                |
| date   | date        | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

一波万波

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
学习数据库MySQL

查询本机数据库: show databases;mysql&gt; show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys ...
复制链接

扫一扫

专栏目录