本文详细介绍了MySQL数据库的登录、创建与切换数据库、用户管理、数据操作等基本操作。包括如何创建和删除用户，使用数据库进行数据的增删查改，以及如何进行数据的排序、计数等。此外，还涉及到了正则表达式在查询中的应用和数据库重启的步骤。
目录
1. 登录Mysql
$mysql -u userName -p
Enter password: *******2.创建和切换数据库
2.1 创建数据库
mysql> CREATE DATABASE menagerie;2.2 切换数据库
2.2.1 直接切换
mysql> USE menagerie;
Database changed2.2.2 登录直接切换
shell> mysql -h host -u user -p menagerie
Enter password: ********3. 创建用户
3.1 创建
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';3.2 授权（所有权限）
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY '123456';3.3 撤销授权
EVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';3.4 指定授权
GRANT SELECT, UPDATE ON wordpress.* TO 'user'@'localhost' IDENTIFIED BY '123456';4.删除用户
mysql>DROP USER user@localhost;5. 使用数据库
5.1 创建表格
创建表，使用CREATE TABLE语句指定表的布局
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE) ;创建表后，SHOW TABLES应该产生一些输出
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet |
+---------------------+要验证您的表是否按预期方式创建，请使用以下DESCRIBE语句：
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+5.2 选择数据
5.2.1 选择所有数据
mysql> SELECT * FROM pet;5.2.2 选择特定行
mysql> SELECT * FROM pet WHERE name = 'Bowser';MySQL提供标准的SQL模式匹配以及基于类似于Unix实用程序（如vi，grep和 sed）使用的扩展正则表达式的模式匹配形式 。
SQL模式匹配使您可以使用_ 匹配任何单个字符并%匹配任意数量的字符（包括零个字符）。在MySQL中，SQL模式默认情况下不区分大小写。这里显示了一些例子。不要使用 =或<>使用SQL模式时。请改用LIKE或 NOT LIKE比较运算符。
5.2.2.1 要查找以下开头的名称b：
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+5.2.2.2 要查找以fy结尾的名称：
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+5.2.2.3 要查找包含名称w：
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+5.2.2.4 要查找包含五个字符的名称，请使用_模式字符的五个实例：
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+MySQL提供的另一种模式匹配使用扩展的正则表达式。当您测试此类模式的匹配项时，请使用 REGEXP_LIKE()函数（ REGEXP或RLIKE 运算符，它们是同义词 REGEXP_LIKE()）。
以下列表描述了扩展正则表达式的一些特征：
1、匹配任何单个字符。
字符类[...]匹配括号内的任何字符。例如， [abc]匹配a， b或c。要命名一系列字符，请使用短划线。[a-z] 匹配任何字母，而[0-9] 匹配任何数字。
2、*匹配前面的事物的零个或多个实例。例如，x* 匹配任意数量的x字符， [0-9]*匹配任意数量的数字，并.*匹配任意数量的任何数字。
3、如果模式匹配正在测试的值中的任何位置，则正则表达式模式匹配成功。（这与LIKE模式匹配不同，模式匹配仅在模式匹配整个值时才会成功。）
4、锚定的图案，使得它必须在值的开头或结尾匹配正在测试中，使用^在一开始或$在图案的端部。
5.2.2.5 要查找以...开头的名称b，请使用 ^以匹配名称的开头：
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+5.2.2.6 查找以fy结尾的名称，请使用$：
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+5.2.2.7 要查找包含a的名称w，请使用以下查询：
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+5.2.2.8 要查找包含五个字符的名称，请使用 ^和$匹配名称的开头和结尾，以及.中间的五个实例：
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
#您还可以使用 （“ repeat- -times ”）运算符编写上一个查询 ： {n}n
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');5.2.3 判断
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';5.2.4 结合条件来定位
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';AND可以和OR混合，但 AND优先级高于 OR。如果您同时使用这两个运算符，最好使用括号明确指出条件应如何分组：
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');5.2.5 选择特定列
mysql> SELECT name, birth FROM pet;5.2.6 对输出的列进行去重，添加DISTINCT
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+5.2.7 行列混合筛选
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+5.2.8 排序
mysql> SELECT name, birth FROM pet ORDER BY birth;
mysql> SELECT name, birth FROM pet ORDER BY birth DESC; #反向排序
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; #按优先顺序对多列进行排序5.2.9 跳过NULL
mysql> SELECT name, birth, death,
-> TIMESTAMPDIFF(YEAR,birth,death) AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+6. 查看所有的用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;7. 计数行
7.1 计数总行
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+7.2 根据分组计行
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+7.3 混合计行
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+8.设置ID
8.1 重置
truncate table test; #(这里假定你的表名test)这种方法好处是运行速度超快
8.2 从指定数字开始
alter table `test` auto_increment=1;这种方法好处是可以从任何值开始，缺点是如果数据量大的话delete from test;非常耗时
9.数据库重启
sudo systemctl stop mysqld.service
sudo systemctl start mysqld.service
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
