JAVA第七周小结（上）

最新推荐文章于 2024-06-16 09:46:44 发布

wei_5guang_

最新推荐文章于 2024-06-16 09:46:44 发布

阅读量96

点赞数

文章标签： java mysql 数据库

本文链接：https://blog.csdn.net/wei_5guang_/article/details/130174636

版权

通过命令操作数据库

wind+ r
#1.连接数据库服务器的命令
C:\Users\user>mysql -uroot -p
Enter password:123456
#mysql:   告知计算机启动哪个软件 
#-h localhost：  -h  host 主机 地址是localhost（127.0.0.1）
#-u root   :  -u  user 用户  默认安装mysql用户叫root
#-p 123456:   -p password  密码  123456
mysql> exit;#退出命令
C:\Users\user>mysql -uroot -p
Enter password:******
#2.展示当前服务器下面有多少个数据库
mysql> show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yhs                |
| yhs123             |
+--------------------+
6 rows in set (0.03 sec)

3.新建一个数据库   create  database 库名;
mysql> create database yhs111;
Query OK, 1 row affected (0.00 sec)
#4.看一眼这个库创建好了没
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yhs                |
| yhs111             |
| yhs123             |
+--------------------+
#5.删除库  drop database 库名;  慎用
mysql> drop database yhs111;
Query OK, 0 rows affected (0.05 sec)
#删除数据库已经成功了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yhs                |
| yhs123             |
+--------------------+
6 rows in set (0.00 sec)
#7.选中库， 服务器下面有跟多数据库，选中哪个库然后再建表  use 库名;
mysql> use yhs123;
Database changed
#8.查看当前数据库下面有多少张表
mysql> show tables;
+------------------+
| Tables_in_yhs123 |
+------------------+
| person           |
| son              |
| work             |
+------------------+
3 rows in set (0.00 sec)
#9.创建表  
#语法格式:  create table 表名字 (字段1 数据类型,字段2 数据类型,...);
mysql> create table dog (id int, name varchar(32),age int);
Query OK, 0 rows affected (0.05 sec)
#10.查看表是否创建成功
mysql> show tables;
+------------------+
| Tables_in_yhs123 |
+------------------+
| dog              |
| person           |
| son              |
| work             |
+------------------+
#11.查看表的结构
mysql> desc dog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
|name  | varchar(32) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
#12.换一种方式创建表

mysql> create table dog1 (
    -> id int,
    -> name varchar(32),
    -> age int
    -> );
Query OK, 0 rows affected (0.05 sec)
#13.删除表  慎用！！！ drop table 表名;
mysql> drop table dog1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_yhs123 |
+------------------+
| dog              |
| person           |
| son              |
| work             |
+------------------+
4 rows in set (0.00 sec)
#14查看创建库和创建表时候的信息
mysql> show create database yhs123;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| yhs123   | CREATE DATABASE `yhs123` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table dog;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| dog   | CREATE TABLE `dog` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改创建好的表字段

alter 关键字


mysql> desc dog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
|name  | varchar(32) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#1.删除age字段   语法格式:  alter table 表名 drop  字段;
mysql> alter table dog drop age;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
|name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>
#2.添加age字段语法格式:  alter table 表名  add 字段 数据类型;
mysql> alter table dog add age int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
#3.在指定的字段的后面添加一个字段  name的字段的后面添加一个字段 
#语法格式:  alter table 表名 add 字段 数据类型 after 字段;
mysql>  alter table dog add sex boolean after name;

mysql> desc dog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#4.修改一个字段的数据类型   name数据类型 varchar 改为char类型
#语法格式:   alter table 表名 modify 字段 修改后的数据类型;
mysql> alter table dog modify name char(20);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
#varchar 和char的区别：
	#varchar 和char 都是用来字符串类型的数据的
	#char(32)  定长的    存入数据的时候，不管你的数据多长，都是开辟32长度的
	#varchar(32) 变长的  存入数据的额时候，随着你的数据长短，而开辟长度
mysql> desc dog;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| name  | char(20)   | YES  |     | NULL    |       |
| sex   | tinyint(1) | YES  |     | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#5字段和数据类型一起修改
mysql> alter table dog change sex gender int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dog;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | YES  |     | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| gender | int(11)  | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
#6.添加一个字段
#数据类型 text  文本 字符串 不限制大小
mysql> alter table dog add info text;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dog;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | YES  |     | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| gender | int(11)  | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
| info   | text     | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#加薪资字段  小数
#  float(8,2);
#  double(8,2);
#  decimal(8,2);
#最大长度是8位 其中小数占2位     999999.99
mysql> alter table dog add salary decimal(8,2);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dog;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | YES  |     | NULL    |       |
| name   | char(20)     | YES  |     | NULL    |       |
| gender | int(11)      | YES  |     | NULL    |       |
| age    | int(11)      | YES  |     | NULL    |       |
| info   | text         | YES  |     | NULL    |       |
| salary | decimal(8,2) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

添加数据到表中

insert into 语句用于向表格中插入新的行数据。 sql语句不区分大小写的

语法格式：

insert into 表名称 VALUES (值1, 值2,....)

或者

insert into table_name (列1, 列2,...) values (值1, 值2,....)（开发中用的）

#插入一条数据到表中
mysql> insert into dog values(1,'嘟嘟',0,2,'看门',999999.99);
Query OK, 1 row affected (0.00 sec)
#发现在用这种方式插入数据的时候  数据一定和表的字段进行匹配！！！
# select * from person;
#select 查询
#*  所有字段
# from  从哪查
#person 从这个表中查所有的数据

mysql> select * from dog;
+------+--------+--------+------+--------+-----------+
| id   | name   | gender | age  | info   | salary    |
+------+--------+--------+------+--------+-----------+
|    1 | 嘟嘟   |      0 |    2 | 看门   | 999999.99 |
+------+--------+--------+------+--------+-----------+
1 row in set (0.00 sec)
#通过指定的列 插入指定的数据
mysql> insert into dog(id,name) values (2,'二哈');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dog;
+------+--------+--------+------+--------+-----------+
| id   | name   | gender | age  | info   | salary    |
+------+--------+--------+------+--------+-----------+
|    1 | 嘟嘟   |      0 |    2 | 看门   | 999999.99 |
|    2 | 二哈   |   NULL | NULL | NULL   |      NULL |
+------+--------+--------+------+--------+-----------+
2 rows in set (0.00 sec)
#一次性插入多条数据
mysql> insert into dog(id, name, age, gender , info, salary)
    -> values(3,'牛彩云',1,2,'嘎嘎黑',777777.77),(4,'毛球',0,4,'憨得很',666666.66);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dog;
+------+-----------+--------+------+-----------+-----------+
| id   | name      | gender | age  | info      | salary    |
+------+-----------+--------+------+-----------+-----------+
|    1 | 嘟嘟      |      0 |    2 | 看门      | 999999.99 |
|    2 | 二哈      |   NULL | NULL | NULL      |      NULL |
|    3 | 牛彩云    |      2 |    1 | 嘎嘎黑    | 777777.77 |
|    4 | 毛球      |      4 |    0 | 憨得很    | 666666.66 |
+------+-----------+--------+------+-----------+-----------+
4 rows in set (0.00 sec)

删除数据【慎用】

delete语句用于删除表中的行。delete from where

delete from 表名称 where 列名称 = 值

mysql> select * from dog;
+------+-----------+--------+------+-----------+-----------+
| id   | name      | gender | age  | info      | salary    |
+------+-----------+--------+------+-----------+-----------+
|    1 | 嘟嘟      |      0 |    2 | 看门      | 999999.99 |
|    2 | 二哈      |   NULL | NULL | NULL      |      NULL |
|    3 | 牛彩云    |      2 |    1 | 嘎嘎黑    | 777777.77 |
|    4 | 毛球      |      4 |    0 | 憨得很    | 666666.66 |
+------+-----------+--------+------+-----------+-----------+
#注意删除的时候一定要加条件  where  条件

mysql> delete from dog where name = "二哈";
Query OK, 1 row affected (0.00 sec)

mysql> select * from dog;
+------+-----------+--------+------+-----------+-----------+
| id   | name      | gender | age  | info      | salary    |
+------+-----------+--------+------+-----------+-----------+
|    1 | 嘟嘟      |      0 |    2 | 看门      | 999999.99 |
|    3 | 牛彩云    |      2 |    1 | 嘎嘎黑    | 777777.77 |
|    4 | 毛球      |      4 |    0 | 憨得很    | 666666.66 |
+------+-----------+--------+------+-----------+-----------+
3 rows in set (0.00 sec)
#删除多条数据

mysql> delete from dog where id in (3,4);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from dog;
+------+--------+--------+------+--------+-----------+
| id   | name   | gender | age  | info   | salary    |
+------+--------+--------+------+--------+-----------+
|    1 | 嘟嘟   |      0 |    2 | 看门   | 999999.99 |
+------+--------+--------+------+--------+-----------+
1 row in set (0.00 sec)

修改数据【慎用】

Update 语句用于修改表中的数据。

语法格式:Update 表名称 SET 列名称 = 新值，列名称=新值,... where 列名称 = 某值


mysql> update dog set name = "来福" where name = "牛彩云";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update dog set name = "毛球",age=8,gender=1 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dog;
+------+--------+--------+------+-----------+-----------+
| id   | name   | gender | age  | info      | salary    |
+------+--------+--------+------+-----------+-----------+
|    1 | 嘟嘟   |      0 |    2 | 看门      | 999999.99 |
|    3 | 来福   |      2 |    1 | 嘎嘎黑    | 777777.77 |
|    4 | 毛球   |      1 |    8 | 憨得很    | 666666.66 |
+------+--------+--------+------+-----------+-----------+
3 rows in set (0.00 sec)

事务操作

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

#如何保证多个sql语句同时执行或者同时不执行呢？
#学习事务：
#1.开启事务，默认是关闭的 换句话说其实就是将sql语句自动提交关闭掉！！！  
#set autocommit = 0;
#要么回滚  rollback;
#要么都执行  commit;
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update person set salary= salary-100 where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 200.00 |
+------+-----------+--------+------+--------------+--------+
5 rows in set (0.00 sec)

mysql> update person set salary= salary+100 where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
+------+-----------+--------+------+--------------+--------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 800.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 200.00 |
+------+-----------+--------+------+--------------+--------+
5 rows in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update person set salary= salary-100 where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update person set salary= salary+100 where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
+------+-----------+--------+------+--------------+--------+
5 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
+------+-----------+--------+------+--------------+--------+
5 rows in set (0.00 sec)


#总结：
	set autocommit= 0;  自动提交关闭
	sql1
	sql2
	等着你提交(commit;)或者回滚（rollback;）

查询【重点】

开发中查询占得比重是比较大的

#查询表中所有数据  查询的关键字 select 
#select * from 表名;  *  代表所有 是通配符
#select *   查询所有的字段  
#from  从哪
#表名 
mysql> select * from dog;
+------+--------+--------+------+-----------+-----------+
| id   | name   | gender | age  | info      | salary    |
+------+--------+--------+------+-----------+-----------+
|    1 | 嘟嘟   |      0 |    2 | 看门      | 999999.99 |
|    3 | 来福   |      2 |    1 | 嘎嘎黑    | 777777.77 |
|    4 | 毛球   |      1 |    8 | 憨得很    | 666666.66 |
+------+--------+--------+------+-----------+-----------+
3 rows in set (0.00 sec)
#查询指定的字段的数据
#select 字段1,字段2,字段3,... from 表名;
mysql>  select id, name, salary  from dog;
+------+--------+-----------+
| id   | name   | salary    |
+------+--------+-----------+
|    1 | 嘟嘟   | 999999.99 |
|    3 | 来福   | 777777.77 |
|    4 | 毛球   | 666666.66 |
+------+--------+-----------+
3 rows in set (0.00 sec)
#按照字段进行查询的时候，可以对字段进行起别名
#select 字段1 as 别名1, 字段2 as 别名2 , ... from 表名

mysql>  select id as "编号", name as "名字", salary as "薪资" from dog;
+--------+--------+-----------+
| 编号   | 名字   | 薪资      |
+--------+--------+-----------+
|      1 | 嘟嘟   | 999999.99 |
|      3 | 来福   | 777777.77 |
|      4 | 毛球   | 666666.66 |
+--------+--------+-----------+
3 rows in set (0.00 sec)

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

语法格式:

select 列名称 from 表名称 where 列运算符值

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

注释：在某些版本的 SQL 中，操作符 <> 可以写为 !=。

# selecty *   查什么  查所有字段
#from person   从哪查   从person表中
#where id = 6  条件是啥  id为6 
mysql> select * from person where id=6;
+------+----------+--------+------+------+--------+
| id   | name     | gender | age  | info | salary |
+------+----------+--------+------+------+--------+
|    6 | ruiqiang |   NULL | NULL | NULL | 300.00 |
+------+----------+--------+------+------+--------+
1 row in set (0.00 sec)
mysql> select * from person where id != 6;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
+------+-----------+--------+------+--------------+--------+
4 rows in set (0.00 sec)
mysql> select * from person where age >= 30;
+------+--------+--------+------+-----------+--------+
| id   | name   | gender | age  | info      | salary |
+------+--------+--------+------+-----------+--------+
|    4 | 二贝   |      0 |   78 | 神经吧    |  23.40 |
+------+--------+--------+------+-----------+--------+
#  where 字段 between  值1 and 值2;    在哪个之间  查年龄在10~30岁之间的额数据
mysql> select * from person where age between 10 and 30;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
+------+-----------+--------+------+--------------+--------+
2 rows in set (0.01 sec)
#like关键  模糊匹配
#where 字段  like  "%值%";
mysql> select * from person where name like "%苏%";
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
+------+-----------+--------+------+--------------+--------+
1 row in set (0.00 sec)

mysql> insert into person (id, name, salary) values(1, "苏大", 965);
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (id, name, salary) values(1, "ladi苏", 965);
Query OK, 1 row affected (0.00 sec)

mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
|    1 | 苏大      |   NULL | NULL | NULL         | 965.00 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
+------+-----------+--------+------+--------------+--------+
7 rows in set (0.00 sec)

mysql> select * from person where name like "%苏";
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
+------+-----------+--------+------+--------------+--------+
2 rows in set (0.00 sec)

mysql> select * from person where name like "苏%";
+------+--------+--------+------+------+--------+
| id   | name   | gender | age  | info | salary |
+------+--------+--------+------+------+--------+
|    1 | 苏大   |   NULL | NULL | NULL | 965.00 |
+------+--------+--------+------+------+--------+
1 row in set (0.00 sec)

mysql> select * from person where name like "%苏%";
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    1 | 苏大      |   NULL | NULL | NULL         | 965.00 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
+------+-----------+--------+------+--------------+--------+
3 rows in set (0.00 sec)

mysql>

逻辑运算符：

&& （and）

|| （or）

#找出来性别为1并且年龄为29的数据
mysql> select * from person where gender = 1 and age = 29;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
+------+-----------+--------+------+--------------+--------+
1 row in set (0.00 sec)

mysql> select * from person where gender = 1 && age = 29;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
+------+-----------+--------+------+--------------+--------+

#年龄小于30岁的 或者性别为0De数据
mysql> select * from person where age < 30 or gender = 0;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
+------+-----------+--------+------+--------------+--------+
3 rows in set (0.00 sec)

ORDER BY 语句用于对结果集进行排序。

语法格式:

select 字段 from 表 order by 字段 asc;默认是升序 asc可以省略
select 字段 from 表 order by 字段 desc;


mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
|    1 | 苏大      |   NULL | NULL | NULL         | 965.00 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
+------+-----------+--------+------+--------------+--------+
7 rows in set (0.00 sec)

mysql> select * from person order by salary;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    1 | 苏大      |   NULL | NULL | NULL         | 965.00 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
+------+-----------+--------+------+--------------+--------+
7 rows in set (0.01 sec)

mysql> select * from person order by salary desc;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 苏大      |   NULL | NULL | NULL         | 965.00 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
+------+-----------+--------+------+--------------+--------+
7 rows in set (0.00 sec)

#扩展：
	#年龄大于20岁的  然后薪资倒序排
	mysql> select * from person where age > 20 order by salary desc;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 苏大      |      1 |   45 | hnan         | 965.00 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
+------+-----------+--------+------+--------------+--------+
3 rows in set (0.00 sec)
#注意事项:  当where和order by 一起使用的时候 where 必须在order by的前面

#先按照薪资倒序排，如果薪资一样的话，再按照年龄进行升序排
mysql> select * from person order by salary desc, age asc;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
|    1 | 苏大      |      1 |   45 | hnan         | 965.00 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
+------+-----------+--------+------+--------------+--------+
7 rows in set (0.01 sec)

limit :限制输出

语法格式:

limit 数字;
limit 数字1, 数字2;

mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
|    1 | 苏大      |      1 |   45 | hnan         | 965.00 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
+------+-----------+--------+------+--------------+--------+
7 rows in set (0.00 sec)

#前三个数据
mysql> select * from person limit 3;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
+------+-----------+--------+------+--------------+--------+

#limit  数字1,数字2;
#数字1    偏移量
#数字2    数据的个数
mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id   | name      | gender | age  | info         | salary |
+------+-----------+--------+------+--------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很     |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗     |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧       |  23.40 |
|    5 | zihao     |   NULL | NULL | NULL         | 700.00 |
|    6 | ruiqiang  |   NULL | NULL | NULL         | 300.00 |
|    1 | 苏大      |      1 |   45 | hnan         | 965.00 |
|    1 | ladi苏    |   NULL | NULL | NULL         | 965.00 |
+------+-----------+--------+------+--------------+--------+
7 rows in set (0.00 sec)

mysql> select * from person limit 2,3;
+------+----------+--------+------+-----------+--------+
| id   | name     | gender | age  | info      | salary |
+------+----------+--------+------+-----------+--------+
|    4 | 二贝     |      0 |   78 | 神经吧    |  23.40 |
|    5 | zihao    |   NULL | NULL | NULL      | 700.00 |
|    6 | ruiqiang |   NULL | NULL | NULL      | 300.00 |
+------+----------+--------+------+-----------+--------+


#注意事项:    limit 必须在where  和   order by的后面
#性别为1的  按照 薪资进行排序 升序  然后限制输出  3个数据
select * from person where gender=1 order by salary limit 0, 3;


limit 分页以后是要做分页的
1  2  3 4  5  6  7 8  9 10 11
每页显示3条数据  一共有4页
前端会提供一个值 第几页
第一页：  1  2 3     limit 0, 3;
第二页:   4 5  6     limit 3, 3;
第三页:   7 8  9     limit 6, 3;
第四页:   10  11     limit 9, 3;

Java中有一个变量 叫 int  pageNo = 1
每页显示的数据  int  pageCount = 3;
			limit (pageNo - 1) * pageCount, pageCount;

查询

和查询相关的sql内置的函数

方法：

max();

min(); 求一个字段中数据的最小值

avg(); 求一个字段的数据的平均值

sum(); 求一个字段所有数据的和

count(*); 统计当前表中数据有多少个

select max(字段名字) FROM 表名字;


mysql> select * from dog;
+------+--------+--------+------+-----------+-----------+
| id   | name   | gender | age  | info      | salary    |
+------+--------+--------+------+-----------+-----------+
|    1 | 嘟嘟   |      0 |    2 | 看门      | 999999.99 |
|    3 | 来福   |      2 |    1 | 嘎嘎黑    | 777777.77 |
|    4 | 毛球   |      1 |    8 | 憨得很    | 666666.66 |
+------+--------+--------+------+-----------+-----------+
3 rows in set (0.00 sec)
#求一个字段的最大值的
mysql> select max(salary) from dog;
+-------------+
| max(salary) |
+-------------+
|   999999.99 |
+-------------+
1 row in set (0.00 sec)

mysql> select max(salary) as "最大值" from dog;
+-----------+
| 最大值    |
+-----------+
| 999999.99 |
+-----------+
1 row in set (0.00 sec)
#求一个字段所有的数据平均值 null  是不计入的

mysql>  select avg(age) from dog;
+----------+
| avg(age) |
+----------+
|   3.6667 |
+----------+
1 row in set (0.00 sec)
#求一个字段的所有的数据的总和
mysql> select sum(salary) from dog;
+-------------+
| sum(salary) |
+-------------+
|  2444444.42 |
+-------------+
#统计一个表中有多少条数据

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

分组统计：

group by

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

select * from person group by gender; 错误的写法，以谁分组就展示谁还可以加上合函数

mysql> select * from person;
+------+-----------+--------+------+-------------------------------------------------+--------+
| id   | name      | gender | age  | info                                            | salary |
+------+-----------+--------+------+-------------------------------------------------+--------+
|    1 | 小苏苏    |      1 |   29 | 可爱得很                                        |   8.99 |
|    3 | heidan    |      1 |   12 | 是真的狗                                        |  98.98 |
|    4 | 二贝      |      0 |   78 | 神经吧                                          |  23.40 |
|    5 | zihao     |      1 |   78 | dasjhjksj nn sahjnjdsnj计算机视觉你就能         | 700.00 |
|    6 | ruiqiang  |      0 |   34 | qweq                                            | 300.00 |
+------+-----------+--------+------+-------------------------------------------------+--------+
#分组
mysql> select gender from person group by gender;
+--------+
| gender |
+--------+
|      0 |
|      1 |
+--------+
#分组以后的统计问题
mysql> select gender,count(*)  from person group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
|      0 |        2 |
|      1 |        3 |
+--------+----------+
#按照性别分组，然后统计，然后要性别类人数 大于2的
#你们想法：是使用where
select gender,count(*) from person where count(*) > 2 group by gender;
#上面sql是错的，  因为此时  count(*)  呢
#一般使用group by的时候 需要对分组之后的数据进行筛选  使用having 不能where
#正确的写法
mysql> select gender, count(*) from person group by gender having count(*) > 2;
+--------+----------+
| gender | count(*) |
+--------+----------+
|      1 |        3 |
+--------+----------+
#找出年龄大于20岁的，然后分组统计 ，统计之后大于2 的性别类别
mysql> select gender,count(*) from  person where age > 20 group by gender having count(*) >2;
+--------+----------+
| gender | count(*) |
+--------+----------+
|      1 |        3 |
+--------+----------+

.数据约束

在创建表的时候，一定要对字段进行约束，这样插入数据的时候，才更加合理，约束性。

数据约束在创建字段的时候就开始添加了

默认值

#default 
#在插入数据的时候，如果没有给当前字段设置一个值的时候，会当前字段赋值一个默认值。
mysql> create table person1 (
    -> id int,
    -> name varchar(32),
    -> country varchar(32) default "PRC" #在一个数据类型的后面 跟一个默认值
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc person1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(32) | YES  |     | NULL    |       |
| country | varchar(32) | YES  |     | PRC     |       |
+---------+-------------+------+-----+---------+-------+

非空

not null 如果给一个字段加上了非空，如果添加数据的时候，必须给当前字段赋值。如果不赋值就会报错

mysql> create table person2 (
    -> id int not null,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into person2 (name) values("狗蛋");
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into person2 (id, name) values(1, "狗蛋");
Query OK, 1 row affected (0.00 sec)

唯一

unique 设置字段的唯一性，添加数据的时候，如果添加数据重复的值话会报错。

mysql> create table person3 (
    -> id int not null,
    -> name varchar(32) unique not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into person3 (id, name) values(1, "毛球");
Query OK, 1 row affected (0.00 sec)

mysql> insert into person3 (id, name) values(2, "毛球");
ERROR 1062 (23000): Duplicate entry '毛球' for key 'name'
mysql>

主键

主键是唯一和非空的组合。 primary key

主键是唯一的并且是非空的，以后开发中必须每张表都得有且只有一个主键字段

而且主键字段必须和业务逻辑无关

mysql> create table person4(
    -> id int primary key ,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into person4 (id,name) values(1, "嘻嘻");
Query OK, 1 row affected (0.00 sec)

mysql> insert into person4 (id,name) values(1, "哈哈");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into person4(name) values("xixi");
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql>

自增长

我们通常希望在每次插入新记录时，自动地创建主键字段的值。

auto_increment

mysql> create table person5 (
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into person5 (name) values("panglong");
Query OK, 1 row affected (0.01 sec)

mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
+----+----------+
1 row in set (0.00 sec)

mysql> insert into person5 (name) values("xiaoxiao");
Query OK, 1 row affected (0.00 sec)

mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
|  2 | xiaoxiao |
+----+----------+
2 rows in set (0.00 sec)

mysql> insert into person5 (name) values("xixi");
Query OK, 1 row affected (0.00 sec)

mysql> insert into person5 (name) values("hehe");
Query OK, 1 row affected (0.01 sec)

mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
|  2 | xiaoxiao |
|  3 | xixi     |
|  4 | hehe     |
+----+----------+
4 rows in set (0.00 sec)java 
 
 #当删除最后一条数据的时候，再次插入数据的时候什么效果！！！
  mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
|  2 | xiaoxiao |
|  3 | xixi     |
|  4 | hehe     |
+----+----------+
4 rows in set (0.00 sec)

mysql> delete from person5 where id = 4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
|  2 | xiaoxiao |
|  3 | xixi     |
+----+----------+
3 rows in set (0.00 sec)

mysql> insert into person5(name) values("haha");
Query OK, 1 row affected (0.00 sec)

mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
|  2 | xiaoxiao |
|  3 | xixi     |
|  5 | haha     |
+----+----------+
4 rows in set (0.00 sec)

mysql> update person5 set id = 4 where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
|  2 | xiaoxiao |
|  3 | xixi     |
|  4 | haha     |
+----+----------+
4 rows in set (0.00 sec)

mysql> insert into person5 (name) values ("jiejie");
Query OK, 1 row affected (0.00 sec)

mysql> select * from person5;
+----+----------+
| id | name     |
+----+----------+
|  1 | panglong |
|  2 | xiaoxiao |
|  3 | xixi     |
|  4 | haha     |
|  6 | jiejie   |
+----+----------+
5 rows in set (0.00 sec)

以后创建表：

mysql> create table person6(
    -> id int primary key auto_increment,
    -> name varchar(32) not null,
    -> age int not null,
    -> info text
    -> );
Query OK, 0 rows affected (0.02 sec)

扩展知识点：

当表中的数据有时间的话表字段如何设置

类型	格式	用途	默认值
date（出现频率偏高）	YYYY-MM-DD	2023-04-08
time	HH:MM:SS	16:45:03
year	YYYY	2023
datetime	YYYY-MM-DD hh:mm:ss
timestamp（出现频率偏高）	YYYY-MM-DD hh:mm:ss	2023-04-08 16:47:08	current_timestamp


mysql> create table cat (
    ->  id int primary key auto_increment,
    -> name varchar(32),
    -> createTime timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql>  insert into cat (name) values("加菲猫");
Query OK, 1 row affected (0.01 sec)

mysql> insert into cat (name) values("来财");
Query OK, 1 row affected (0.00 sec)

mysql>  insert into cat (name) values("毛毛");
Query OK, 1 row affected (0.00 sec)

mysql> select * from cat;
+----+-----------+---------------------+
| id | name      | createTime          |
+----+-----------+---------------------+
|  1 | 加菲猫    | 2023-04-15 17:16:06 |
|  2 | 来财      | 2023-04-15 17:16:59 |
|  3 | 毛毛      | 2023-04-15 17:17:29 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)

外键的约束

mysql> create table dept (
    -> dept_id int primary key auto_increment,
    -> dept_name varchar(32)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql>  create table emp (
    ->  emp_id int primary key auto_increment,
    ->  emp_name varchar(32) ,
    -> dept_id int,
    -> reg_time timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into  dept(dept_name) values("教学部");
Query OK, 1 row affected (0.01 sec)

mysql> insert into  dept(dept_name) values("品保部");
Query OK, 1 row affected (0.00 sec)

mysql> insert into  dept(dept_name) values（"技术部");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values（"技术部")' at line 1
mysql> insert into  dept(dept_name) values("技术部");
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp (emp_name, dept_id) values ("哈哈", 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values ("嘻嘻", 1);3
Query OK, 1 row affected (0.00 sec)

    -> insert into emp (emp_name, dept_id) values ("嘻嘻", 1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3
insert into emp (emp_name, dept_id) values ("嘻嘻", 1)' at line 1
mysql> insert into emp (emp_name, dept_id) values ("呵呵", 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp (emp_name, dept_id) values ("sb", 2);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into emp (emp_name, dept_id) values ("ll", 3);
Query OK, 1 row affected (0.00 sec)

mysql>  select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 教学部    |
|       2 | 品保部    |
|       3 | 技术部    |
+---------+-----------+
3 rows in set (0.00 sec)

mysql>  select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 哈哈     |       1 | 2023-04-15 19:04:01 |
|      2 | 嘻嘻     |       1 | 2023-04-15 19:04:12 |
|      3 | 呵呵     |       2 | 2023-04-15 19:05:52 |
|      4 | sb       |       2 | 2023-04-15 19:06:20 |
|      5 | ll       |       3 | 2023-04-15 19:06:59 |
+--------+----------+---------+---------------------+
5 rows in set (0.00 sec)

mysql> insert into emp (emp_name, dept_id) values("gd", 8);
Query OK, 1 row affected (0.00 sec)

mysql> delete from dept where dept_id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 哈哈     |       1 | 2023-04-15 19:04:01 |
|      2 | 嘻嘻     |       1 | 2023-04-15 19:04:12 |
|      3 | 呵呵     |       2 | 2023-04-15 19:05:52 |
|      4 | sb       |       2 | 2023-04-15 19:06:20 |
|      5 | ll       |       3 | 2023-04-15 19:06:59 |
|      6 | gd       |       8 | 2023-04-15 19:07:46 |
+--------+----------+---------+---------------------+
6 rows in set (0.00 sec)

mysql> create table emp (
    -> emp_id int primary key auto_increment,
    -> emp_name varchar(32) ,
    -> dept_id int,
    -> reg_time timestamp default current_timestamp,
    #constraint  约束的意思
    #fk_emp_dept  外键的名字 随意起
    #foreign key(dept_id)  外键 使用本表中的一个字段作为外键去和别的表产生关系
    #references 关联 引用的意思，去关联主表里面的某一个字段（外键所在的表叫副表）
    
    -> constraint fk_emp_dept foreign key(dept_id)  references dept(dept_id)
    -> );
Query OK, 0 rows affected (0.09 sec)
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 教学部    |
|       2 | 品保部    |
+---------+-----------+
#插入数据  
mysql> insert into emp (emp_name, dept_id) values("lx", 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values("sl", 1);
Query OK, 1 row affected (0.00 sec)
#插入了一个不存在的部门  的时候结果报错课
mysql> insert into emp (emp_name, dept_id) values("gd", 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`java2304`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))

#删除数据  删除教学部
mysql> delete from dept where dept_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java2304`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))

#修改数据
mysql> update dept set dept_id  = 5 where dept_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java2304`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))
mysql>

#加上外键约束以后，增删改  有的时候会报错
#主表（部门表）   副表（员工表）
#增加的时候： 先看主表  然后增加副表
#删除的时候： 先删除副表 再删除主表
#修改的时候:  先修改副表 再修改主表
#这样才能不报错
#太麻烦了，还得记这些
#级联操作！！！  级联修改 和级联删除
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)

mysql> create table emp (
    -> emp_id int primary key auto_increment,
    -> emp_name varchar(32) ,
    -> dept_id int,
    -> reg_time timestamp default current_timestamp,
    -> constraint fk_emp_dept foreign key(dept_id)  references dept(dept_id)
    #加的两个状态
    -> on delete cascade
    -> on update cascade
    -> );
Query OK, 0 rows affected (0.01 sec)
	mysql> delete from dept where dept_id  =1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      3 | xueke    |       2 | 2023-04-15 19:08:01|
|      4 | erbei    |       2 | 2023-04-15 19:08:06 |
|      5 | erjia    |       2 | 2023-04-15 19:08:09 |
+--------+----------+---------+---------------------+
#修改：
mysql> update dept set dept_id = 4 where dept_id  =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      3 | xueke    |       4 | 2023-04-15 19:09:46 |
|      4 | erbei    |       4 | 2023-04-15 19:09:49 |
|      5 | erjia    |       4 | 2023-04-15 19:09:52 |
+--------+----------+---------+---------------------+

真实开发的时候，特别是外键约束的表的时候，要加上级联删除和级联修改
mysql> create table emp (
    -> emp_id int primary key auto_increment,
    -> emp_name varchar(32) ,
    -> dept_id int,
    -> reg_time timestamp default current_timestamp,
    -> constraint fk_emp_dept foreign key(dept_id)  references dept(dept_id)
    #加的两个状态
    -> on delete cascade
    -> on update cascade
    -> );

联表查询

常规的联表查询

#增删改和外键约束有关     查询没有关系的
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       4 | 品保部    |
|       5 | 教学部    |
|       6 | 技术部    |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      3 | xueke    |       4 | 2023-04-10 10:46:04 |
|      4 | erbei    |       4 | 2023-04-10 10:46:08 |
|      5 | erjia    |       4 | 2023-04-10 10:46:11 |
|      6 | 东哥     |       5 | 2023-04-10 11:00:59 |
|      7 | 飞哥     |       5 | 2023-04-10 11:01:11 |
+--------+----------+---------+---------------------+
#联表查询  查看 erbei 属于哪个部门


select emp.emp_name, dept.dept_name  # 查什么
from emp, dept  #从哪查
where emp.dept_id = dept.dept_id and emp.emp_name = "erbei" #查询的条件
mysql> select *
    -> from emp, dept
    -> where emp.dept_id = dept.dept_id;
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time            | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
|      3 | xueke    |       4 | 2023-04-10 10:46:04 |       4 | 品保部    |
|      4 | erbei    |       4 | 2023-04-10 10:46:08 |       4 | 品保部    |
|      5 | erjia    |       4 | 2023-04-10 10:46:11 |       4 | 品保部    |
|      6 | 东哥     |       5 | 2023-04-10 11:00:59 |       5 | 教学部    |
|      7 | 飞哥     |       5 | 2023-04-10 11:01:11 |       5 | 教学部    |
+--------+----------+---------+---------------------+---------+-----------+
5 rows in set (0.01 sec)

mysql> select *
    -> from emp, dept
    -> where emp.dept_id = dept.dept_id and emp.emp_name = "erbei";
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time            | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
|      4 | erbei    |       4 | 2023-04-10 10:46:08 |       4 | 品保部    |
+--------+----------+---------+---------------------+---------+-----------+
1 row in set (0.00 sec)

mysql> select emp.emp_name, dept.dept_name
    -> from emp, dept
    -> where emp.dept_id = dept.dept_id and emp.emp_name = "erbei";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erbei    | 品保部    |
+----------+-----------+
1 row in set (0.00 sec)


#对表起表名
select e.emp_name, d.dept_name
from emp e, dept d
where e.dept_id = d.dept_id and e.emp_name = "erbei";


mysql> select e.emp_name as "员工名字", d.dept_name  as "部门名字"
    -> from emp e, dept d
    -> where e.dept_id = d.dept_id and e.emp_name = "erbei";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erbei    | 品保部    |
+----------+-----------+
1 row in set (0.00 sec)

#找出教学部有有哪些人
select d.dept_name, e.emp_name
from dept d, emp e
where d.dept_id = e.dept_id  and d.dept_name= "教学部";
mysql> select d.dept_name, e.emp_name
    -> from dept d, emp e
    -> where d.dept_id = e.dept_id  and d.dept_name= "教学部";
+-----------+----------+
| dept_name | emp_name |
+-----------+----------+
| 教学部    | 东哥     |
| 教学部    | 飞哥     |
+-----------+----------+
2 rows in set (0.00 sec)

内连接

SELECT column_name(s)  查什么
FROM table_name1    从哪查  表1
INNER JOIN table_name2   关联 表2
ON table_name1.column_name=table_name2.column_name

1.外键约束
	在创建表的时候，本表中的一个字段h会作为另外一个表的的外键 让两个表有关系
	constraint  外键名字(自定义的)  foreign key(本表字段) references 另外一张表(字段)
	on delete cascade  on update cascade;
	
2.多表联查
	常规写法：   select * from emp, dept where emp.dept_id = dept.dept_id 
    内连接：   select * from emp inner join dept on emp.dept_id = dept.dept_id
    左外连接： select * from emp left outer join dept on emp.dept_id = dept.dept_id
    右外连接

一对多或者多对一的查询

mysql> create table teacher (
    -> t_id int primary key auto_increment,
    -> t_name varchar(32)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table student(
    -> s_id int primary key auto_increment,
    -> s_name varchar(32),
    -> teacher_id int
    -> );
Query OK, 0 rows affected (0.01 sec)


#老邢带学生有哪些？
select teacher.t_name, student.s_name
from teacher, student 
where teacher.t_id = student.teacher_id and teacher.t_name = "heh";
mysql> select teacher.t_name, student.s_name
    -> from teacher, student
    -> where teacher.t_id = student.teacher_id and teacher.t_name = "heh";
+--------+--------+
| t_name | s_name |
+--------+--------+
|heh  | 张三   |
| heh   | 李四   |
|heh  | 王五   |
| heh   | 老八   |
+--------+--------+
4 rows in set (0.00 sec)

mysql> select t.t_name,s.s_name
    -> from teacher t
    -> inner join student s
    -> on t.t_id = s.teacher_id
    -> where t.t_name = "老邢";
+--------+--------+
| t_name | s_name |
+--------+--------+
|heh  | 张三   |
| heh   | 李四   |
| heh   | 王五   |
| heh   | 老八   |
+--------+--------+
4 rows in set (0.00 sec)

多对多的场景

mysql> create table stu (
    -> s_id int primary key auto_increment,
    -> s_name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table course (
    -> c_id int primary key auto_increment,
    -> c_name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table stu_course (
    -> id int primary key auto_increment,
    -> s_id int,
    -> c_id int
    -> );
Query OK, 0 rows affected (0.08 sec)


#有三张表就意味着着必须使用三表联查
#需求：  司马懿选了哪些课程
select stu.s_name, course.c_name
from stu, stu_course, course
where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "司马懿";
mysql> select *
    -> from stu, stu_course, course
    -> where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "司马懿";
+------+-----------+----+------+------+------+--------+
| s_id | s_name    | id | s_id | c_id | c_id | c_name |
+------+-----------+----+------+------+------+--------+
|    1 | 司马懿    |  1 |    1 |    1 |    1 | 物理   |
|    1 | 司马懿    |  2 |    1 |    3 |    3 | Java   |
|    1 | 司马懿    |  3 |    1 |    4 |    4 | python |
+------+-----------+----+------+------+------+--------+
3 rows in set (0.01 sec)

mysql> select stu.s_name, course.c_name
    -> from stu, stu_course, course
    -> where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "司马懿";
+-----------+--------+
| s_name    | c_name |
+-----------+--------+
| 司马懿    | 物理   |
| 司马懿    | Java   |
| 司马懿    | python |
+-----------+--------+
3 rows in set (0.01 sec)

#内连接的写法
select s.s_name, c.c_name
from stu s
inner join stu_course sc
on s.s_id  = sc.s_id
inner join course c
on sc.c_id = c.c_id
where s.s_name = "司马懿";
mysql> select s.s_name, c.c_name
    -> from stu s
    -> inner join stu_course sc
    -> on s.s_id  = sc.s_id
    -> inner join course c
    -> on sc.c_id = c.c_id
    -> where s.s_name = "司马懿";
+-----------+--------+
| s_name    | c_name |
+-----------+--------+
| 司马懿    | 物理   |
| 司马懿    | Java   |
| 司马懿    | python |
+-----------+--------+
#挖掘机被哪些人选了

select c.c_name, s.s_name
from course c
inner join stu_course sc
on c.c_id = sc.c_id
inner join stu s
on sc.s_id = s.s_id
where c.c_name = "挖掘机";
mysql> select c.c_name, s.s_name
    -> from course c
    -> inner join stu_course sc
    -> on c.c_id = sc.c_id
    -> inner join stu s
    -> on sc.s_id = s.s_id
    -> where c.c_name = "挖掘机";
+-----------+-----------+
| c_name    | s_name    |
+-----------+-----------+
| 挖掘机    | 孙悟空    |
| 挖掘机    | 林黛玉    |
+-----------+-----------+

wei_5guang_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JAVA第七周小结（上）

insert into table_name (列1, 列2,...) values (值1, 值2,....)（开发中用的）语法格式:Update 表名称 SET 列名称 = 新值，列名称=新值,... where 列名称 = 某值。not null 如果给一个字段加上了非空，如果添加数据的时候，必须给当前字段赋值。在创建表的时候，一定要对字段进行约束，这样插入数据的时候，才更加合理，约束性。unique 设置字段的唯一性，添加数据的时候，如果添加数据重复的值话会报错。delete语句用于删除表中的行。
复制链接

扫一扫