MySQL基本使用

命令行使用mysql

C:\Windows\system32>mysql -uroot -p
Enter password: ****

查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.09 sec)

创建数据库

mysql> create database dbtest1;
Query OK, 1 row affected (0.18 sec)

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

使用创建的数据库并且创建一个表格

mysql> use dbtest1
Database changed
mysql> create table employee(id int,name varchar(15));
Query OK, 0 rows affected (0.28 sec)

mysql> show tables
    -> ;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employee          |
+-------------------+
1 row in set (0.02 sec)

向employee表格插入数据

mysql> insert into employee values(1001,'tom');
Query OK, 1 row affected (0.08 sec)

mysql> insert into employee values(1002,'kevin');
Query OK, 1 row affected (0.07 sec)

mysql> select * from employee;
+------+-------+
| id   | name  |
+------+-------+
| 1001 | tom   |
| 1002 | kevin |
+------+-------+
2 rows in set (0.01 sec)

向employee表格插入中文

mysql> insert into employee values(1003,'小黑子');
Query OK, 1 row affected (0.05 sec)

mysql> select * from employee;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | tom    |
| 1002 | kevin  |
| 1003 | 小黑子 |
+------+--------+
3 rows in set (0.01 sec)

上面的操作在mysql8下可以成功，但mysql5.7下会报错。

因为在mysql5.7下的字符集使用的latin1（拉丁字符），而中文应该使用utf-8字符集。

在mysql8.0下查看表的属性：

mysql> show create table employee;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                            |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `id` int DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

注意这里的CHARSET=utf8mb4。

查看字符集（建的表默认与character_set_database的字符集一致）：

mysql> show variables like 'character_%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

基本的SELECT语句的使用

SQL的分类

DDL：数据定义语言。CREATE创建 \ ALTER修改 \ DROP删除 \ RENAME重命名 \ TRUNCATE清空
DML：数据操作语言（增删改查）。INSERT插入 \ DELETE删除 \ UPDATE修改 \ SELECT查询（重中之重）
DCL：数据控制语言。COMMIT提交 \ ROLLBACK回滚、撤销 \ SAVEPOINT回滚到具体的保存点 \ GRANT赋予权限 \ REVOKE回收权限

其中，因为查询语句使用的非常频繁，SELECT也被称为DQL（数据查询语言）

还有单独将COMMIT和ROLLBACK取出来称为TCL（事务控制语言）

SQL的规则与规范

基本规则

SQL可以写在一行或者多行，为了提高可读性，各子句分行写，必要时进行缩进
每行命令以;或者\g或者\G结尾（Navicat等某些第三方工具不支持\g和\G结尾）
关键字不能被缩写也不能被分行
关于标点符号
- 必须保证所有的()、单双引号是成对结束的
- 必须使用英文状态下的半角输入方式
- 字符串型和时间类型的数据可以使用单引号''表示
- 列的别名，尽量使用双引号""，而不建议省略as

规范

Mysql在window下大小写不敏感
在Linux下大小写敏感
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名（或字段名）、列的别名（字段的别名）是忽略大小写的
推荐采用统一的书写规范：
- 数据库名、表名、表的别名、字段名等都小写
- SQL关键字、函数名、绑定变量等都大写

注释

单行使用#进行注释（Mysql特有），或者-- 注释（-- 后必须包含一个空格）
多行使用/**/，注意不能嵌套

命名规则

数据库、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使
用`（着重号）引起来
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据
类型在一个表里是整数，那在另一个表里可就别变成字符型了

数据库导入

使用source 路径;来导入数据库，例如：

mysql> source d:\mysqldb.sql

使用可视化界面进行导入

SELECT语句

SELECT ...

没有任何子语句，这时候会直接通过表格展示数据

SELECT 1 + 1,3 * 2;

SELECT 1 + 1,3 * 2
FROM DUAL; #dual：伪表
#结果
+------+-------+
| 1+1  |  3*2  |
+------+-------+
|  2   |   6   |
+------+-------+

SELECT ... FROM ...

SELECT department_id,department_name
FROM departments;

在生产环境下，不推荐你直接使用 SELECT * 进行查询。

列的别名

SELECT department_id dep_i,department_name AS "dep name"
FROM departments;

别名建议跟上AS关键字，如果别名有空格，需要使用双引号""

去除重复行

在我们使用SLECT ... FROM ...查询数据时，会有重复行，比如想查询某个公司有哪些部门。

SELECT department_id
FROM employees;

会有重复的数据，我们可以使用DISTINCT关键字去除重复行

SELECT DISTINCT department_id
FROM employees;

下面的去除重复行是department_id,manager_id两个数据都相同的情况下去除

SELECT DISTINCT department_id,manager_id
FROM employees;

下面的语句将会报错，因为两列数据的行数不一定一致。

SELECT department_id,DISTINCT manager_id
FROM employees;# 报错

空值与空值计算

所有运算符遇到空值（null）都为null

SELECT employee_id,salary*(1+commission_pct)*12 AS year_en
FROM employees;

着重号

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在
SQL语句中使用一对``（着重号）引起来。

错误的：

SELECT order_id FROM order;# 报错

正确的：

SELECT order_id FROM `order`;

常数

SELECT 查询还可以对常数进行查询，可以在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。

我们在查询id同时添加一个名叫school的列，并将其所有值都设置为移通：

SELECT '移通' as "school",employee_id
FROM employees;

显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees;
# 或者
DESC employees;

过滤器WHERE

SELECT employee_id,first_name,last_name
FROM employees
WHERE department_id = 90;

注意：Mysql在window下不严谨

SELECT employee_id,first_name,last_name
FROM employees
WHERE last_name = 'King';# 这里写小写king也能查找到

另外，WHERE子语句应该紧挨着FROM

运算符

算数运算符

在sql中，+没有连接字符串的用法。在sql中100+'1'值为101，‘1’会隐式转换为整型1。在java中100+'1'值为'1001'。如果sql中要使用到拼接，会使用CONCAT()方法。

100+'a'中的'a'按照0来处理，null参与运算都为null。

除法运算会保留到小数点后4位，所以都是浮点数。

比较运算符

等号运算符

sql中，比较运算符使用的是一个等号=，等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0

SELECT 1 = 2, 1 != 2, 1 = '1', 1 = 'a';# 0    1    1    0

SELECT 'a' = 'a', 'ab' = 'ab', 'a' = 'b';# 1    1    0

SELECT 1 = NULL,NULL = NULL;# NULL NULL

在使用等号运算符时，遵循如下规则：

如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

下面的语句不会查询到有任何数据

SELECT employee_id,salary,commission_pct
FROM employees
WHERE commission_pct = NULL;

安全等于`<=>`

为NULL而生，使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同。

下面返回的数据中的commission_pct都为NULL

SELECT employee_id,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

不等于运算符`!=`或`<>`

SELECT 1 != 2, 2 <> 2, 'a' <> 'b';# 1    0    1

非符号类型运算符

IS NULL和IS NOT NULL

之前的查询可以改为IS NULL或者ISNULL()

SELECT employee_id,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

SELECT employee_id,salary,commission_pct
FROM employees
WHERE  ISNULL(commission_pct);

改为查询不为空的可以这样写

SELECT employee_id,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

# 或者
SELECT employee_id,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

LEAST和GREATEST

求最小值和最大值

SELECT LEAST('a','b','c'),GREATEST('g','h','m');# a    m

SELECT LEAST('yes','kevin','ok'),GREATEST(LENGTH('nice'),LENGTH('tt'),LENGTH('qiankun'));# kevin    7

BETWEEN AND

求区间，使用BETWEEN 条件下界1 AND 条件上届2（上届不能小于下界）

SELECT employee_id,salary
FROM employees
WHERE salary BETWEEN 8000 AND 10000;
# 相当于
SELECT employee_id,salary
FROM employees
WHERE salary>=8000 && salary<=10000;

查询不在8000到10000的：

SELECT employee_id,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 10000;

IN和NOT IN

# 查询部门为10或20或30的员工
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id IN (10,20,30);
# 查询部门不是10或20或30的员工
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id NOT IN (10,20,30);

LIKE模糊查询

查询last_name中含a字符的数据。%表示不确定个数的字符。

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '%a%';

以a开头的：

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE 'a%';

同时包含a和b的

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%b%';

查询第二个字符是a的，_代表一个字符

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '_a%';

查询第二个字符是_，第三个字符是a的（使用转义字符\）：

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '_\_a%';# 106    P_ataballa

# 也可以使用ESCAPE来定义一个转义字符，后面跟的字符被看做转义字符
SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';# 106    P_ataballa

REGEXP、RLIKE正则表达式

^匹配以该字符后面的字符开头的字符串。
$匹配以该字符前面的字符结尾的字符串。
.匹配任何一个单字符。
[...]匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
*匹配零个或多个在它前面的字符。例如，x*匹配任何数量的x字符，[0-9]*匹配任何数量的数字，而*匹配任何数量的任何字符。

SELECT 'kevin' REGEXP '^k', 'kevin' REGEXP 'n$', 'kevin' REGEXP 'vi';# 1    1    1
SELECT 'kevin' RLIKE 'k.v', 'kevin' RLIKE '[ae]'# 1    1

逻辑运算符

XOR异或，查询只满足一种但是另一种不满足的情况下的数据：

SELECT employee_id,salary,department_id
FROM employees
WHERE salary>=8000 XOR department_id IN (80,90);

OR和AND可以同时使用，但是AND的优先级大于OR

位运算符

按位与`&`，按位或`|`，按位异或`^`

mysql> SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;
+--------+--------+--------+
| 12 & 5 | 12 | 5 | 12 ^ 5 |
+--------+--------+--------+
| 4 | 13 | 9 |
+--------+--------+--------+
1 row in set (0.00 sec)
mysql> SELECT 10 & ~1;
+---------+
| 10 & ~1 |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)

按位取反运算符

按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1。

按位左/右移运算符

将给定的值的二进制数的所有位左/右移指定的位数。左/右移指定的位数后，左/右边高位的数值被移出并丢弃，右/左边低位空出的位置用0补齐。

MySQL基本使用

MySQL基本使用

命令行使用mysql

基本的SELECT语句的使用

SQL的分类

SQL的规则与规范

基本规则

规范

注释

命名规则

数据库导入

SELECT语句

SELECT ...

SELECT ... FROM ...

列的别名

去除重复行

空值与空值计算

着重号

常数

显示表结构

过滤器WHERE

运算符

算数运算符

比较运算符

等号运算符

安全等于<=>

不等于运算符!=或<>

非符号类型运算符

IS NULL和IS NOT NULL

LEAST和GREATEST

BETWEEN AND

IN和NOT IN

LIKE模糊查询

REGEXP、RLIKE正则表达式

逻辑运算符

位运算符

按位与&，按位或|，按位异或^

按位取反运算符

按位左/右移运算符

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

安全等于`<=>`

不等于运算符`!=`或`<>`

按位与`&`，按位或`|`，按位异或`^`