MySQL常用功能及指令_show table person-CSDN博客

本文链接：https://blog.csdn.net/sixsix668/article/details/123429360

本文详细介绍了数据库的基础知识，包括常用的数据库管理系统如MySQL、Oracle和SQL Server，以及SQL语言的基本语法，如增删改查操作。通过实例展示了如何使用SQL进行数据库连接、数据操作以及表和数据库的管理。还探讨了数据类型、主键约束、数据查询与排序、聚合函数以及数据库之间的关系。此外，提到了JDBC在Java中与数据库交互的作用。

摘要由CSDN通过智能技术生成

数据库

对数据进行增删改查操作

DBMS(数据库管理系统)

DataBaseManagementSystem,数据库管理系统(数据库软件)
常见的DBMS
- MySQL: Oracle公司产品 08年被Sun收购 09年Sun被Oracle , 原MySQL团队从Oracle离职又创建了MariaDB. 市占率第一
- Oracle: Oracle公司当家产品市占率第二闭源产品
- SqlServer: 微软公司产品 , 市占率第三闭源产品 .net 编程语言+web服务软件+操作系统+数据库软件
- DB2: IBM公司产品闭源产品
- SQLite: 轻量级数据库, 安装包只有几十k , 只有最基础的增删改查功能

SQL：结构化查询语言(Structured Query Language)

Structured Query Language: 结构化查询语言.
通过这门语言让程序员和数据库软件进行交流
举例: insert into person values(“刘德华”,18);

和数据库软件建立连接

通过命令行/终端和数据库建立连接
- 首先检查MySQL/MariaDB服务是否开启: 在我的电脑/此电脑上右键->管理->服务和应用程序->服务->MySQL/MariaDB
- 从开始菜单中找到MySQL/MariaDB->MySQL Client 打开客户端 , 输入自己的密码，显示如下信息说明建立好了链接。

Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.5.15-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

退出指令: exit

MariaDB [(none)]> exit;
Bye

建立连接指令: mysql -uroot -p 回车后输入密码再回车

C:\Program Files\MariaDB 10.5\bin>mysql -uroot -p//此处没有分号！！！
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.15-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

通过三方的可视化工具和数据库软件建立连接

数据库相关

在MySQL数据库软件中保存数据需要先建库再建表,然后再往表里面添加数据

查询所有数据库

格式：show databases;

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.002 sec)

创建数据库

默认字符集格式: create database 数据库名;

MariaDB [(none)]> create database mydb1;
Query OK, 1 row affected (0.001 sec)

指定字符集格式: create database 数据库名 charset=utf8/gbk;

MariaDB [(none)]> create database mydb2 charset=utf8;
Query OK, 1 row affected (0.001 sec)

再次查询数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mydb2              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.001 sec)

查询数据库信息

格式: show create database 数据库名;

MariaDB [(none)]> show create database mydb1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mydb1    | CREATE DATABASE `mydb1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.000 sec)

删除数据库

格式: drop database 数据库名;

MariaDB [(none)]> drop database mydb1;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.001 sec)

使用数据库

在执行表相关和数据相关的SQL语句之前必须使用了某个数据库,
格式: use 数据库名;

MariaDB [(none)]> use mydb2;
Database changed
MariaDB [mydb2]>

表相关

执行表相关的SQL之前必须使用了某个数据库.

MariaDB [mydb2]> create database newdb1 charset=utf8;
Query OK, 1 row affected (0.002 sec)
MariaDB [mydb2]> use newdb1;
Database changed
MariaDB [newdb1]>

创建表

格式: create table 名字(字段1名类型,字段2名类型,…)charset=utf8/gbk;

MariaDB [newdb1]>
   create table student(name varchar(50),chinese int,math int,english int);
Query OK, 0 rows affected (0.006 sec)

查询所有表

格式: show tables;

MariaDB [newdb1]> show tables;
+------------------+
| Tables_in_newdb1 |
+------------------+
| person           |
| student          |
+------------------+
2 rows in set (0.000 sec)

查询表信息

格式: show create table 表名；

MariaDB [newdb1]> show create table person;
+--------+------------------------------------------------------------------+
| Table  | Create Table     
                                                                                       
+--------+------------------------------------------------------------------+
| person | CREATE TABLE `person` (
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------+

查询表字段

格式: desc 表名;

MariaDB [newdb1]> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(50) | YES  |     | NULL    |       |
| chinese | int(11)     | YES  |     | NULL    |       |
| math    | int(11)     | YES  |     | NULL    |       |
| english | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.007 sec)

删除表

格式: drop table 表名;

MariaDB [newdb1]> drop table student;
Query OK, 0 rows affected (0.004 sec)

修改表名

格式: rename table 原名 to 新名;

MariaDB [newdb1]> drop table student;
Query OK, 0 rows affected (0.004 sec)

MariaDB [newdb1]> rename table person to per;
Query OK, 0 rows affected (0.006 sec)

MariaDB [newdb1]> show tables;
+------------------+
| Tables_in_newdb1 |
+------------------+
| per              |
+------------------+
1 row in set (0.000 sec)

添加表字段

MariaDB [newdb1]> create database newdb2;
Query OK, 1 row affected (0.001 sec)

MariaDB [newdb1]> use newdb2;
Database changed

MariaDB [newdb2]> create table emp(name varchar(30));
Query OK, 0 rows affected (0.006 sec)

最后面添加格式: alter table 表名 add 字段名类型;

MariaDB [newdb2]> alter table emp add age int;
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

最前面添加格式: alter table 表名 add 字段名类型 first;

MariaDB [newdb2]> alter table emp add id int first;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

某字段后面添加格式: alter table 表名 add 字段名类型 after 字段名;

MariaDB [newdb2]> alter table emp add gender varchar(10) after name;
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表字段

格式: alter table 表名 drop 字段名;

MariaDB [newdb2]> alter table emp drop gender;
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表字段

格式:alter table 表名 change 原名新名新类型;

MariaDB [newdb2]> alter table emp change age gender varchar(10);
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

数据相关

插入数据

全表插入格式: insert into 表名 values(值1,值2);

MariaDB [mydb5]> insert into person values("tom",18);
Query OK, 1 row affected (0.002 sec)

指定字段插入格式: insert into 表名(字段1名,字段2名) values(值1,值2);

MariaDB [mydb5]> insert into person(name) values("jerry");
Query OK, 1 row affected (0.001 sec)

批量插入格式

全表插入格式: insert into 表名 values(值1,值2),(值1,值2),(值1,值2);

MariaDB [mydb5]> insert into person values("lucy",20),("lili",23);
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

指定字段插入格式: insert into 表名(字段1名,字段2名) values(值1,值2),(值1,值2);

MariaDB [mydb5]> insert into person(name) values("lilei"),("hanmeimei");
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

中文插入

MariaDB [mydb5]> insert into person values("周星驰",50);
Query OK, 1 row affected (0.001 sec)

执行上述程序报错的话,执行以下程序

MariaDB [mydb5]> set names gbk;

查询数据

格式: select 字段信息 from 表名 where 条件;

MariaDB [mydb5]> select name from person;
+-----------+
| name      |
+-----------+
| tom       |
| jerry     |
| lucy      |
| lili      |
| lilei     |
| hanmeimei |
| 刘德华     |
| 周星驰     |
+-----------+
8 rows in set (0.000 sec)

MariaDB [mydb5]> select name,age from person;
+-----------+------+
| name      | age  |
+-----------+------+
| tom       |   18 |
| jerry     | NULL |
| lucy      |   20 |
| lili      |   23 |
| lilei     | NULL |
| hanmeimei | NULL |
| 刘德华    |   50  |
| 周星驰    |   50  |
+-----------+------+
8 rows in set (0.000 sec)

MariaDB [mydb5]> select * from person;
+-----------+------+
| name      | age  |
+-----------+------+
| tom       |   18 |
| jerry     | NULL |
| lucy      |   20 |
| lili      |   23 |
| lilei     | NULL |
| hanmeimei | NULL |
| 刘德华    |   50  |
| 周星驰    |   50  |
+-----------+------+
8 rows in set (0.000 sec)

MariaDB [mydb5]> select * from person where age>20;
+--------+------+
| name   | age  |
+--------+------+
| lili   |   23 |
| 刘德华 |   50  |
| 周星驰 |   50  |
+--------+------+
3 rows in set (0.000 sec)

修改数据

格式: update 表名 set 字段名=值,字段名=值 where 条件;

MariaDB [mydb5]>  update person set name="汤姆" where name="tom";
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mydb5]> update person set name="杰瑞",age=15 where name="jerry";
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mydb5]> update person set age=10 where age is null;
Query OK, 2 rows affected (0.001 sec)
Rows matched: 2  Changed: 2  Warnings: 0

删除数据

格式: delete from 表名 where 条件;

MariaDB [mydb5]> delete from person where name="汤姆";
Query OK, 1 row affected (0.001 sec)

MariaDB [mydb5]> delete from person;
Query OK, 7 rows affected (0.001 sec)

表相关SQL语句总结:

创建 create table 表名(name varchar(20),age int)charset=utf8/gbk;
查询所有表 show tables;
查询表信息 show create table 表名;
表字段: desc 表名;
删除表: drop table 表名;
修改表名: rename table 原名 to 新名;
添加表字段; alter table 表名 add 字段名类型 first/after xxx;
删除表字段: alter table 表名 drop 字段名;
修改表字段; alter table 表名 change 原名新名新类型;

数据相关总结:

插入数据: insert into 表名(字段1名,字段2名) values(值1,值2),(值1,值2);
查询数据: select 字段信息 from 表名 where 条件;
修改数据: update 表名 set 字段名=值,字段名=值 where 条件;
删除数据; delete from 表名 where 条件;

常见错误提示:

You have an error in your SQL; 你的SQL语句中有个错 (检查SQL拼写)
database exists; 数据库已存在
Column count doesn’t match value count 插入的值的数量和列(表字段)的数量不一致

SQL分类

DDL: 数据定义语言, 包括: 数据库向和表相关的SQL
DML:数据操作语言, 包括: 增删改查
DQL: 数据查询语言, 只包括查询 select
TCL: 事务控制语言, 包含和事务相关的内容
DCL: 数据控制语言, 包含用户管理,权限分配相关SQL

.sql 批处理文件导入

把需要用到的后缀名为.sql的文件,放到某个容易找到的位置,例如根目录下,然后再命令行执行以下指令
格式:source 路径,例如:source d/emp.sql;
导入完成后,用show databases检查是否出现刚刚导入的数据库
如果出现乱码执行 set names utf8;

数据类型

整数：int(m)和bigint(m) , bigint相当于Java中的long, m代表显示长度,m=5 存18 得到00018, 需要结合zerofill关键字使用

MariaDB [empdb]> create table t1(age int(5) zerofill);
Query OK, 0 rows affected (0.005 sec)

MariaDB [empdb]> insert into t1 values(18);
Query OK, 1 row affected (0.002 sec)

MariaDB [empdb]> select * from t1;
+-------+
| age   |
+-------+
| 00018 |
+-------+
1 row in set (0.001 sec)

浮点数：double(m,d) m代表总长度, d代表小数长度 m=5,d=3 23.123

MariaDB [empdb]> create table t2(price double(5,3));
Query OK, 0 rows affected (0.006 sec)

MariaDB [empdb]> insert into t2 values(54.321);
Query OK, 1 row affected (0.003 sec)

MariaDB [empdb]> insert into t2 values(54.332211);
Query OK, 1 row affected (0.001 sec)

//超出范围！会报错或出现99.999
MariaDB [empdb]> insert into t2 values(5433.2211);
ERROR 1264 (22003): Out of range value for column 'price' at row 1

字符串

char(m): 固定长度, m=5 存"abc" 占5个字符长度, 执行效率略高,当存储内容长度固定时使用,比如:性别 m最大值255
varchar(m):可变长度, m=5 存"abc" 占3个字符长度,更节省存储空间,m最大值65535, 长度在255以内的建议使用
text(m):可变长度, m最大值65535 , 建议长度大于255时使用

日期

date:保存年月日
time:保存时分秒
datetime:保存年月日时分秒,默认值null
timestamp(时间戳举例1970年1月1日的毫秒数):保存年月日时分秒,默认值为当前系统时间

MariaDB [empdb]> insert into t3 values("2021-11-22","10:38:20",null,null);
Query OK, 1 row affected (0.003 sec)

MariaDB [empdb]> insert into t3 values(null,null,"2021-11-22 10:38:20",null);
Query OK, 1 row affected (0.001 sec)

MariaDB [empdb]> select * from t3;
+------------+----------+---------------------+---------------------+
| t1         | t2       | t3                  | t4                  |
+------------+----------+---------------------+---------------------+
| 2021-11-22 | 10:38:20 | NULL                | 2022-03-14 10:41:46 |
| NULL       | NULL     | 2021-11-22 10:38:20 | 2022-03-14 10:42:50 |
| 2021-11-22 | 10:38:20 | NULL                | 2022-03-15 16:18:06 |
| NULL       | NULL     | 2021-11-22 10:38:20 | 2022-03-15 16:18:15 |
+------------+----------+---------------------+---------------------+
4 rows in set (0.000 sec)

主键约束

主键: 表示数据唯一性的字段称为主键
约束: 创建表时,给表字段添加的限制条件
主键约束:限制主键的值唯一且非空

举例

create table t4(id int primary key,name varchar(20));
insert into t4 values(1,‘aaa’);
insert into t4 values(1,‘bbb’); //报错重复数据
insert into t4 values(null,‘ccc’); //报错不能为null

主键约束+自增

自增规则:从历史最大值+1
create table t5(id int primary key auto_increment,name varchar(20));
insert into t5 values(null,‘aaa’);
insert into t5 values(null,‘bbb’);
insert into t5 values(10,‘ccc’);
insert into t5 values(null,‘ddd’);

MariaDB [empdb]> insert into t5 values(null,'aaa');
Query OK, 1 row affected (0.001 sec)

MariaDB [empdb]>  insert into t5 values(null,'bbb');
Query OK, 1 row affected (0.001 sec)

MariaDB [empdb]>  insert into t5 values(10,'ccc');
Query OK, 1 row affected (0.001 sec)

MariaDB [empdb]>  insert into t5 values(null,'eee');
Query OK, 1 row affected (0.001 sec)

MariaDB [empdb]> select * from t5;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
| 10 | ccc  |
| 11 | eee  |
+----+------+
4 rows in set (0.000 sec)

去重distinct

查询员工表中有哪几种不同的工作?

MariaDB [empdb]> select distinct job from emp;
+----------+
| job      |
+----------+
| 销售     |
| 销售经理  |
| 项目经理  |
| 程序员    |
| CEO      |
| 人事      |
| 市场      |
| NULL     |
+----------+
8 rows in set (0.001 sec)

查询员工表中有哪几个不同的部门id?

MariaDB [empdb]> select distinct dept_id from emp;
+---------+
| dept_id |
+---------+
|       1 |
|       3 |
|       2 |
|    NULL |
+---------+
4 rows in set (0.000 sec)

is null 和 is not null

查询没有上级领导的员工姓名

MariaDB [empdb]> select name from emp where manager is null;
+------+
| name |
+------+
| 刘备 |
| 观音 |
| 灭霸 |
+------+
3 rows in set (0.000 sec)

查询有上级领导的员工姓名

MariaDB [empdb]> select name from emp where manager is not null;
+--------+
| name   |
+--------+
| 孙悟空 |
| 猪八戒 |
| 沙僧   |
| 唐僧   |
| 关羽   |
| 张飞   |
| 白骨精 |
| 蜘蛛精 |
| 黑熊怪 |
+--------+
9 rows in set (0.000 sec)

and和or

多个条件同时满足时使用and
多个条件满足一个就可以时使用or
查询1号部门工资高于2000的员工信息

MariaDB [empdb]> select * from emp where dept_id=1 and sal>2000;
+----+------+----------+---------+------------+---------+------+---------+
| id | name | job      | manager | hiredate   | sal     | comm | dept_id |
+----+------+----------+---------+------------+---------+------+---------+
|  4 | 唐僧 | 销售经理  |       8 | 1981-04-02 | 2975.00 | NULL |       1 |
|  8 | 观音 | CEO      |    NULL | 1981-11-17 | 5000.00 | NULL |       1 |
+----+------+----------+---------+------------+---------+------+---------+
2 rows in set (0.001 sec)

查询3号部门的员工或者工资等于5000的员工信息

MariaDB [empdb]> select * from emp where dept_id=3 or sal=5000;
+----+------+----------+---------+------------+---------+---------+---------+
| id | name | job      | manager | hiredate   | sal     | comm    | dept_id |
+----+------+----------+---------+------------+---------+---------+---------+
|  5 | 刘备 | 项目经理 |     NULL | 1981-09-28 | 1255.00 | 1400.00 |       3 |
|  6 | 关羽 | 程序员   |        5 | 1981-05-01 | 2855.00 |    NULL |       3 |
|  7 | 张飞 | 程序员   |        5 | 1981-06-09 | 2455.00 |    NULL |       3 |
|  8 | 观音 | CEO     |     NULL | 1981-11-17 | 5000.00 |    NULL |       1 |
+----+------+----------+---------+------------+---------+---------+---------+
4 rows in set (0.000 sec)

查询孙悟空和猪八戒的员工信息

MariaDB [empdb]> select * from emp where name="孙悟空" or name="猪八戒";
+----+--------+------+---------+------------+---------+--------+---------+
| id | name   | job  | manager | hiredate   | sal     | comm   | dept_id |
+----+--------+------+---------+------------+---------+--------+---------+
|  1 | 孙悟空  | 销售 |       4 | 1980-12-17 |  800.00 |   NULL |       1 |
|  2 | 猪八戒  | 销售 |       4 | 1981-02-20 | 1600.00 | 300.00 |       1 |
+----+--------+------+---------+------------+---------+--------+---------+
2 rows in set (0.001 sec)

比较运算符 > < >= <= = !=和<>

查询不是程序员的姓名和工资

两种写法都可以
MariaDB [empdb]> select name,sal from emp where job<>“程序员”;
MariaDB [empdb]> select name,sal from emp where job!="程序员";
+--------+---------+
| name   | sal     |
+--------+---------+
| 孙悟空 |  800.00 |
| 猪八戒 | 1600.00 |
| 沙僧   | 1250.00 |
| 唐僧   | 2975.00 |
| 刘备   | 1255.00 |
| 观音   | 5000.00 |
| 白骨精 | 1500.00 |
| 蜘蛛精 |  950.00 |
| 黑熊怪 | 3000.00 |
+--------+---------+
9 rows in set (0.000 sec)

两者之间between x and y 包含x和y

查询工资在2000到3000之间的员工信息

MariaDB [empdb]> select * from emp where sal between 2000 and 3000;
+----+--------+----------+---------+------------+---------+------+---------+
| id | name   | job      | manager | hiredate   | sal     | comm | dept_id |
+----+--------+----------+---------+------------+---------+------+---------+
|  4 | 唐僧   | 销售经理  |       8 | 1981-04-02 | 2975.00 | NULL |       1 |
|  6 | 关羽   | 程序员    |       5 | 1981-05-01 | 2855.00 | NULL |       3 |
|  7 | 张飞   | 程序员    |       5 | 1981-06-09 | 2455.00 | NULL |       3 |
| 11 | 黑熊怪 | 市场      |       8 | 1981-12-03 | 3000.00 | NULL |       2 |
+----+--------+----------+---------+------------+---------+------+---------+
4 rows in set (0.001 sec)

范围取反

MariaDB [empdb]> select * from emp where sal not between 2000 and 3000;
+----+--------+----------+---------+------------+---------+---------+---------+
| id | name   | job      | manager | hiredate   | sal     | comm    | dept_id |
+----+--------+----------+---------+------------+---------+---------+---------+
|  1 | 孙悟空 | 销售      |       4 | 1980-12-17 |  800.00 |    NULL |       1 |
|  2 | 猪八戒 | 销售      |       4 | 1981-02-20 | 1600.00 |  300.00 |       1 |
|  3 | 沙僧   | 销售      |       4 | 1981-02-22 | 1250.00 |  500.00 |       1 |
|  5 | 刘备   | 项目经理  |    NULL | 1981-09-28 | 1255.00 | 1400.00 |       3 |
|  8 | 观音   | CEO      |    NULL | 1981-11-17 | 5000.00 |    NULL |       1 |
|  9 | 白骨精 | 人事      |       8 | 1981-09-08 | 1500.00 |    0.00 |       2 |
| 10 | 蜘蛛精 | 人事      |       8 | 1981-12-03 |  950.00 |    NULL |       2 |
+----+--------+----------+---------+------------+---------+---------+---------+
7 rows in set (0.000 sec)

in关键字

当查询某个字段的值为多个值的时候使用in
查询工资为3000,1500,5000的员工信息

MariaDB [empdb]> select * from emp where sal=3000 or sal=1500 or sal=5000;
+----+--------+------+---------+------------+---------+------+---------+
| id | name   | job  | manager | hiredate   | sal     | comm | dept_id |
+----+--------+------+---------+------------+---------+------+---------+
|  8 | 观音   | CEO  |    NULL | 1981-11-17 | 5000.00 | NULL |       1 |
|  9 | 白骨精 | 人事  |       8 | 1981-09-08 | 1500.00 | 0.00 |       2 |
| 11 | 黑熊怪 | 市场  |       8 | 1981-12-03 | 3000.00 | NULL |       2 |
+----+--------+------+---------+------------+---------+------+---------+
3 rows in set (0.000 sec)

可以改写为：

MariaDB [empdb]> select * from emp where sal in(3000,1500,5000);
+----+--------+------+---------+------------+---------+------+---------+
| id | name   | job  | manager | hiredate   | sal     | comm | dept_id |
+----+--------+------+---------+------------+---------+------+---------+
|  8 | 观音   | CEO  |    NULL | 1981-11-17 | 5000.00 | NULL |       1 |
|  9 | 白骨精 | 人事  |       8 | 1981-09-08 | 1500.00 | 0.00 |       2 |
| 11 | 黑熊怪 | 市场  |       8 | 1981-12-03 | 3000.00 | NULL |       2 |
+----+--------+------+---------+------------+---------+------+---------+
3 rows in set (0.001 sec)

查询孙悟空,猪八戒和白骨精的信息

MariaDB [empdb]> select * from emp where name in('孙悟空','猪八戒','白骨精');
+----+--------+------+---------+------------+---------+--------+---------+
| id | name   | job  | manager | hiredate   | sal     | comm   | dept_id |
+----+--------+------+---------+------------+---------+--------+---------+
|  1 | 孙悟空 | 销售  |       4 | 1980-12-17 |  800.00 |   NULL |       1 |
|  2 | 猪八戒 | 销售  |       4 | 1981-02-20 | 1600.00 | 300.00 |       1 |
|  9 | 白骨精 | 人事  |       8 | 1981-09-08 | 1500.00 |   0.00 |       2 |
+----+--------+------+---------+------------+---------+--------+---------+
3 rows in set (0.000 sec)

模糊查询like

%：代表0或多个未知字符
_：代表1个未知字符
举例：
以x开头：x%
以x结尾：%x
包含x：%x%
第二个字符是x： x%
以x开头以y结尾：x%y
第二个是x倒数第三个是y：x%y

1、查询姓孙员工信息

MariaDB [empdb]> select * from emp where name like "孙%";
+----+--------+------+---------+------------+--------+------+---------+
| id | name   | job  | manager | hiredate   | sal    | comm | dept_id |
+----+--------+------+---------+------------+--------+------+---------+
|  1 | 孙悟空  | 销售 |       4 | 1980-12-17 | 800.00 | NULL |       1 |
+----+--------+------+---------+------------+--------+------+---------+
1 row in set (0.001 sec)

2、查询名字以精结尾的员工姓名

MariaDB [empdb]> select name from emp where name like "%精";
+--------+
| name   |
+--------+
| 白骨精  |
| 蜘蛛精  |
+--------+
2 rows in set (0.000 sec)

3、查询工作第二个字是售的员工姓名和工作

MariaDB [empdb]> select name,job from emp where job like "_售%";
+--------+----------+
| name   | job      |
+--------+----------+
| 孙悟空 | 销售      |
| 猪八戒 | 销售      |
| 沙僧   | 销售      |
| 唐僧   | 销售经理  |
+--------+----------+
4 rows in set (0.000 sec)

4、查询名字中包含僧并且工资大于2000的员工姓名和工资

MariaDB [empdb]> select name,sal from emp where name like "%僧%" and sal>2000;
+------+---------+
| name | sal     |
+------+---------+
| 唐僧 | 2975.00 |
+------+---------+
1 row in set (0.000 sec)

排序 order by

格式: order by 字段名 asc(默认升序)/desc降序;
1、查询员工姓名和工资,按照工资升序

MariaDB [empdb]> select name,sal from emp order by sal;
+--------+---------+
| name   | sal     |
+--------+---------+
| 灭霸   |    NULL |
| 孙悟空 |  800.00 |
| 蜘蛛精 |  950.00 |
| 沙僧   | 1250.00 |
| 刘备   | 1255.00 |
| 白骨精 | 1500.00 |
| 猪八戒 | 1600.00 |
| 张飞   | 2455.00 |
| 关羽   | 2855.00 |
| 唐僧   | 2975.00 |
| 黑熊怪 | 3000.00 |
| 观音   | 5000.00 |
+--------+---------+
12 rows in set (0.001 sec)

2、查询员工姓名和工资,按照工资降序

MariaDB [empdb]> select name,sal from emp order by sal desc;
+--------+---------+
| name   | sal     |
+--------+---------+
| 观音   | 5000.00 |
| 黑熊怪 | 3000.00 |
| 唐僧   | 2975.00 |
| 关羽   | 2855.00 |
| 张飞   | 2455.00 |
| 猪八戒 | 1600.00 |
| 白骨精 | 1500.00 |
| 刘备   | 1255.00 |
| 沙僧   | 1250.00 |
| 蜘蛛精 |  950.00 |
| 孙悟空 |  800.00 |
| 灭霸   |    NULL |
+--------+---------+
12 rows in set (0.000 sec)

3、查询1号部门的姓名和工资并且按照工资降序排序

MariaDB [empdb]> select name,sal from emp where dept_id=1 order by sal desc;
+--------+---------+
| name   | sal     |
+--------+---------+
| 观音   | 5000.00 |
| 唐僧   | 2975.00 |
| 猪八戒 | 1600.00 |
| 沙僧   | 1250.00 |
| 孙悟空 |  800.00 |
+--------+---------+
5 rows in set (0.000 sec)

4、查询每个员工的姓名,工资和部门id按照部门id升序排序,如果部门id相同则按照工资降序排序

MariaDB [empdb]> select name,sal,dept_id from emp order by dept_id,sal desc;
+--------+---------+---------+
| name   | sal     | dept_id |
+--------+---------+---------+
| 灭霸   |    NULL |    NULL |
| 观音   | 5000.00 |       1 |
| 唐僧   | 2975.00 |       1 |
| 猪八戒 | 1600.00 |       1 |
| 沙僧   | 1250.00 |       1 |
| 孙悟空 |  800.00 |       1 |
| 黑熊怪 | 3000.00 |       2 |
| 白骨精 | 1500.00 |       2 |
| 蜘蛛精 |  950.00 |       2 |
| 关羽   | 2855.00 |       3 |
| 张飞   | 2455.00 |       3 |
| 刘备   | 1255.00 |       3 |
+--------+---------+---------+
12 rows in set (0.000 sec)

分页查询 limit

格式: limit 跳过的条数,请求的条数(每页的条数)
举例: 跳过的条数=(请求页数-1)*每页条数
第一页的5条数据 limit 0,5
第一页的10条 limit 0,10
第2页的10条 limit 10,10
第5页的10条 limit 40,10
第8页的9条 limit 63,9 ------ ((8-1)*9=63，9)
第15页的3条 limit 42,3

1、查询工资升序第4页的2条数据

MariaDB [empdb]> select * from emp order by sal limit 6,2;
+----+--------+--------+---------+------------+---------+--------+---------+
| id | name   | job    | manager | hiredate   | sal     | comm   | dept_id |
+----+--------+--------+---------+------------+---------+--------+---------+
|  2 | 猪八戒 | 销售    |       4 | 1981-02-20 | 1600.00 | 300.00 |       1 |
|  7 | 张飞   | 程序员  |       5 | 1981-06-09 | 2455.00 |   NULL |       3 |
+----+--------+--------+---------+------------+---------+--------+---------+
2 rows in set (0.001 sec)

别名

select name from emp;
select name as “名字” from emp;
select name “名字” from emp;
select name 名字 from emp;

数值计算

1、查询每个员工的姓名,工资和年终奖(3个月的工资)

MariaDB [empdb]> select name,sal,3*sal 年终奖 from emp;
+--------+---------+----------+
| name   | sal     | 年终奖   |
+--------+---------+----------+
| 孙悟空 |  800.00 |  2400.00 |
| 猪八戒 | 1600.00 |  4800.00 |
| 沙僧   | 1250.00 |  3750.00 |
| 唐僧   | 2975.00 |  8925.00 |
| 刘备   | 1255.00 |  3765.00 |
| 关羽   | 2855.00 |  8565.00 |
| 张飞   | 2455.00 |  7365.00 |
| 观音   | 5000.00 | 15000.00 |
| 白骨精 | 1500.00 |  4500.00 |
| 蜘蛛精 |  950.00 |  2850.00 |
| 黑熊怪 | 3000.00 |  9000.00 |
| 灭霸   |    NULL |     NULL |
+--------+---------+----------+
12 rows in set (0.001 sec)

2、给3号部门的员工每人涨薪5块钱

MariaDB [empdb]> update emp set sal=sal+5 where dept_id=3;
Query OK, 3 rows affected (0.001 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [empdb]> select sal from emp;
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1260.00 |
| 2860.00 |
| 2460.00 |
| 5000.00 |
| 1500.00 |
|  950.00 |
| 3000.00 |
|    NULL |
+---------+
12 rows in set (0.000 sec)

聚合函数

可以对查询的多条数据进行统计查询, 统计方式包括: 求平均值,最大值,最小值,求和,计数

1、平均值avg(字段名)

查询2号部门的平均工资

MariaDB [empdb]> select avg(sal) from emp where dept_id=2;
+-------------+
| avg(sal)    |
+-------------+
| 1816.666667 |
+-------------+
1 row in set (0.000 sec)

2、最大值max(字段名)

查询1号部门的最高工资

MariaDB [empdb]> select max(sal) from emp where dept_id=1;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.000 sec)

3、最小值min(字段名)

查询1号部门的最低工资

MariaDB [empdb]> select min(sal) from emp where dept_id=1;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.000 sec)

4、求和sum(字段名)

查询1号部门的工资总和

MariaDB [empdb]> select sum(sal) from emp where dept_id=1;
+----------+
| sum(sal) |
+----------+
| 11625.00 |
+----------+
1 row in set (0.001 sec)

5、计数count(*)

查询工资高于2000的员工人数

MariaDB [empdb]> select count(*) from emp where sal>2000;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.000 sec)

分组查询group by

将某个字段相同值的数据划分为一组, 然后以组为单位进行统计查询

1、查询每个部门的平均工资

MariaDB [empdb]> select dept_id,avg(sal) from emp group by dept_id;
+---------+-------------+
| dept_id | avg(sal)    |
+---------+-------------+
|    NULL |        NULL |
|       1 | 2325.000000 |
|       2 | 1816.666667 |
|       3 | 2193.333333 |
+---------+-------------+
4 rows in set (0.001 sec)

2、查询每种工作的平均工资

MariaDB [empdb]> select job,avg(sal) from emp group by job;
+----------+-------------+
| job      | avg(sal)    |
+----------+-------------+
| NULL     |        NULL |
| CEO      | 5000.000000 |
| 人事      | 1225.000000 |
| 市场      | 3000.000000 |
| 程序员    | 2660.000000 |
| 销售      | 1216.666667 |
| 销售经理  | 2975.000000 |
| 项目经理  | 1260.000000 |
+----------+-------------+
8 rows in set (0.001 sec)

3、查询每个部门的最高工资

MariaDB [empdb]> select dept_id,max(sal) from emp group by dept_id;
+---------+----------+
| dept_id | max(sal) |
+---------+----------+
|    NULL |     NULL |
|       1 |  5000.00 |
|       2 |  3000.00 |
|       3 |  2860.00 |
+---------+----------+

4、查询每种工作的人数

MariaDB [empdb]> select job,count(*) from emp group by job;
+----------+----------+
| job      | count(*) |
+----------+----------+
| NULL     |        1 |
| CEO      |        1 |
| 人事     |        2 |
| 市场     |        1 |
| 程序员   |        2 |
| 销售     |        3 |
| 销售经理 |        1 |
| 项目经理 |        1 |
+----------+----------+
8 rows in set (0.000 sec)

having关键字

where后面只能写普通字段的条件,不能写聚合函数函数
having关键字作用和where类似都是用来添加条件的, 但是having后面专门写聚合函数条件,而且having要和group by分组查询结合使用, 写在分组关键字的后面

1、查询每个部门的平均工资要求平均工资大于2000

MariaDB [empdb]> select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000 ;
+---------+-------------+
| dept_id | avg(sal)    |
+---------+-------------+
|       1 | 2325.000000 |
|       3 | 2193.333333 |
+---------+-------------+
2 rows in set (0.001 sec)

别名用法

MariaDB [empdb]> select dept_id,avg(sal) a from emp group by dept_id having a>2000 ;
+---------+-------------+
| dept_id | a           |
+---------+-------------+
|       1 | 2325.000000 |
|       3 | 2193.333333 |
+---------+-------------+
2 rows in set (0.000 sec)

2、查询每种工作的人数,只查询人数大于1 的

MariaDB [empdb]> select job,count(*) c from emp group by job having c>1;
+--------+---+
| job    | c |
+--------+---+
| 人事   | 2 |
| 程序员 | 2 |
| 销售   | 3 |
+--------+---+
3 rows in set (0.000 sec)

3、查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400

MariaDB [empdb]> select dept_id,sum(sal) s from 
emp where manager is not null group by dept_id having s>5400;
+---------+---------+
| dept_id | s       |
+---------+---------+
|       1 | 6625.00 |
|       2 | 5450.00 |
+---------+---------+
2 rows in set (0.000 sec)

4、查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的

MariaDB [empdb]> select dept_id,avg(sal) a from 
emp where sal between 1000 and 3000 group by dept_id having a>=2000;
+---------+-------------+
| dept_id | a           |
+---------+-------------+
|       2 | 2250.000000 |
|       3 | 2193.333333 |
+---------+-------------+
2 rows in set (0.000 sec)

各个关键字的顺序

select * from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 desc limit 跳过条数,请求条数;

表与表直接如何建立关系

一对一：在AB任意一张表里面添加一个建立关系的字段指向另外一张表的主键
一对多：在一对多的两张表中,在"多"的表里面添加建立关系的字段,指向另一张表的主键
多对多：创建一个单独的关系表，表里面有两个字段指向另外两个表的主键

多对多举例：苍老师：小红，小花，小绿 -------------- 传奇哥：小花，小绿
创建以下数据库

create table t(id int primary key auto_increment,name varchar(20));
create table s(id int primary key auto_increment,name varchar(20));
create table t_s(tid int,sid int);
insert into t values(null,‘苍老师’),(null,‘传奇哥’);
insert into s values(null,‘小红’),(null,‘小花’),(null,‘小绿’);
insert into t_s values(1,1),(1,2),(1,3),(2,2),(2,3);

MariaDB [empdb]>  create table t(id int primary key auto_increment,name varchar(20));
create table s(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected (0.005 sec)

MariaDB [empdb]> create table t_s(tid int,sid int);
Query OK, 0 rows affected (0.005 sec)

MariaDB [empdb]> insert into t values(null,'苍老师'),(null,'传奇哥');
Query OK, 2 rows affected (0.003 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [empdb]> insert into s values(null,'小红'),(null,'小花'),(null,'小绿');
Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [empdb]> insert into t_s values(1,1),(1,2),(1,3),(2,2),(2,3);
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0

等值连接

格式：select * from A,B where 关联关系 and 其他条件；
例如：查询工资高于2000的员工的姓名

MariaDB [empdb]> select e.name,d.name
-> from emp e,dept d where e.dept_id=d.id and sal>2000;
+--------+--------+
| name   | name   |
+--------+--------+
| 唐僧   | 神仙    |
| 关羽   | 普通人  |
| 张飞   | 普通人  |
| 观音   | 神仙    |
| 黑熊怪 | 妖怪    |
+--------+--------+
5 rows in set (0.004 sec)

内连接

等值连接和内连接查询到的是一样的数据，推荐使用内连接
格式：select * from A join B on 关联关系 where 其他条件；
上面的例子用内连接查询

MariaDB [empdb]> select e.name,d.name 
-> from emp e join dept d on e.dept_id=d.id where sal>2000;
+--------+--------+
| name   | name   |
+--------+--------+
| 唐僧   | 神仙    |
| 关羽   | 普通人  |
| 张飞   | 普通人  |
| 观音   | 神仙    |
| 黑熊怪 | 妖怪    |
+--------+--------+
5 rows in set (0.001 sec)

外连接

等值连接与内连接查询到的是两个表的交集数据
外连接查询到的是一张表的全部和另外一张表的交集
格式：select * from A left/right join B on 关联关系 where 其他条件；

MariaDB [empdb]> insert into emp(name) values("灭霸");
Query OK, 1 row affected (0.001 sec)

例如：查询所有员工姓名和对应的部门名

使用内连接查询无法查到"灭霸"

MariaDB [empdb]> select e.name,d.name,loc,sal from
    -> emp e join dept d on dept_id=d.id;
+--------+--------+--------+---------+
| name   | name   | loc    | sal     |
+--------+--------+--------+---------+
| 孙悟空 | 神仙   | 天庭   |  800.00 |
| 猪八戒 | 神仙   | 天庭   | 1600.00 |
| 沙僧   | 神仙   | 天庭   | 1250.00 |
| 唐僧   | 神仙   | 天庭   | 2975.00 |
| 刘备   | 普通人 | 北京   | 1255.00 |
| 关羽   | 普通人 | 北京   | 2855.00 |
| 张飞   | 普通人 | 北京   | 2455.00 |
| 观音   | 神仙   | 天庭   | 5000.00 |
| 白骨精 | 妖怪   | 盘丝洞 | 1500.00 |
| 蜘蛛精 | 妖怪   | 盘丝洞 |  950.00 |
| 黑熊怪 | 妖怪   | 盘丝洞 | 3000.00 |
+--------+--------+--------+---------+
11 rows in set (0.001 sec)

使用外连接查询可以查到"灭霸"

MariaDB [empdb]> select e.name,d.name,loc,sal from
    -> emp e left join dept d on dept_id=d.id;
+--------+--------+--------+---------+
| name   | name   | loc    | sal     |
+--------+--------+--------+---------+
| 孙悟空 | 神仙   | 天庭   |  800.00 |
| 猪八戒 | 神仙   | 天庭   | 1600.00 |
| 沙僧   | 神仙   | 天庭   | 1250.00 |
| 唐僧   | 神仙   | 天庭   | 2975.00 |
| 刘备   | 普通人 | 北京   | 1255.00 |
| 关羽   | 普通人 | 北京   | 2855.00 |
| 张飞   | 普通人 | 北京   | 2455.00 |
| 观音   | 神仙   | 天庭   | 5000.00 |
| 白骨精 | 妖怪   | 盘丝洞 | 1500.00 |
| 蜘蛛精 | 妖怪   | 盘丝洞 |  950.00 |
| 黑熊怪 | 妖怪   | 盘丝洞 | 3000.00 |
| 灭霸   | NULL  |  NULL  |    NULL |
+--------+--------+--------+---------+
12 rows in set (0.001 sec)

JDBC(Java DataBase Connectivity)

Java DataBase Connectivity：Java数据库连接
学习JDBC主要学习的就是如何通过Java语言和数据库软件进行连接并执行SQL语句。
JDBC是Sun公司提供的一套用于Java语言和数据库软件进行连接的API (Application Programming Interface)
为什么Sun公司定义JDBC系列接口？
- Sun公司为了避免Java程序员每一种数据库软件都学习一套全新的方法，通过JDBC接口将方法名定义好，让各个数据库厂商根据此接口中方法名写各自的实现类（驱动），这样Java程序员只需要掌握JDBC接口中的方法的调用即可访问任何数据库软件。