java小白零基础（十三）通过命令操作数据库，修改创建好的表字段，事务操作，查询，开发中使用的多表联查

4.通过命令操作数据库

wind + r
#1.连接数据库服务器的命令
<>mysql -hlocalhost -uroot -pAa112233
<>mysql -uroot -p  //进入命令
#2.mysql>  show databases; //服务器下面有多少数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| java2304           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
<>mysql exit; //退出命令
#3.>mysql create  database 库名;
//mysql> create database java2304;  举例
#4.mysql> show databases;   //查看建好库没
+--------------------+
| Database           |
+--------------------+
| information_schema |
| java2304           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
#5.删除库  drop database 库名;  慎用
mysql> drop database java2304;  列
#6.选中库， 服务器下面有跟多数据库，选中哪个库然后再建表  use 库名;
mysql> use java2304;
Database changed
#7.查看当前数据库下面有多少张表
mysql> show tables;
Empty set (0.00 sec)#空的
#8.创建表 #语法格式:  create table 表名字 (字段1 数据类型,字段2 数据类型,...);
mysql> create table person (id int(11), name varchar(32), age int);
Query OK, 0 rows affected (0.02 sec)
#9.查看表是否创建成功
mysql> show tables;
+--------------------+
| Tables_in_java2304 |
+--------------------+
| person             |
+--------------------+
#10.查看表的结构
mysql> desc person;
#11.换一种方式创建表
mysql> create table person1 (
 -> id int,
 -> name varchar(32),
 -> age int
 -> );
 #12.删除表  慎用！！！ drop table 表名;
 mysql> drop table person1;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------------+
| Tables_in_java2304 |
+--------------------+
| person             |
+--------------------+
#13.额外讲两个命令  查看创建库和创建表时候的信息
mysql> show create database java2304;
mysql> show create table person;

5.修改创建好的表字段

alter  关键字
#1.删除age字段   语法格式:  alter table 表名 drop  字段;
mysql> alter table person drop age;
#2.添加age字段语法格式:  alter table 表名  add 字段 数据类型;
mysql> alter table person add age int;
#3.在指定的字段的后面添加一个字段  name的字段的后面添加一个字段 
#语法格式:  alter table 表名 add 字段 数据类型 after 字段;
mysql> alter table person add sex boolean after name;
#4.修改一个字段的数据类型   name数据类型 varchar 改为char类型
#varchar 和char的区别：
	#varchar 和char 都是用来字符串类型的数据的
	#char(32)  定长的    存入数据的时候，不管你的数据多长，都是开辟32长度的
	#varchar(32) 变长的  存入数据的额时候，随着你的数据长短，而开辟长度
#语法格式:   alter table 表名 modify 字段 修改后的数据类型;
mysql> alter table person modify name char(20);
#5字段和数据类型一起修改
mysql> alter table person change sex gender int;
#6.添加一个字段
#数据类型 text  文本 字符串 不限制大小    
mysql> alter table person add info text;
 表字段的数据类型：   
 int    tinyint(0)  boolean    varchar    char    text    double    float    decimal

6.添加数据到表中

1.#插入一条数据到表中
insert into person values (1, '苏桐', 0, 99, "可爱得很", 8.99);
Query OK, 1 row affected (0.01 sec)
mysql> select * from person;
+------+--------+--------+------+--------------+--------+
| id   | name   | gender | age  | info         | salary |
+------+--------+--------+------+--------------+--------+
|    1 | 苏桐   |      0 |   99 | 可爱得很     |   8.99 |
+------+--------+--------+------+--------------+--------+
2.#通过指定的列 插入指定的数据
mysql> insert into person(id, name) values(2, "还在睡觉");
3.#一次性插入多条数据
mysql> insert into person(id, name, age, gender , info, salary)
 -> values(3, "狗蛋", 12, 1, "是真的狗", 98.98),(4, "二贝", 78, 0,"神经吧", 23.4);

7.删除数据【慎用】

DELETE FROM 表名称 WHERE 列名称 = 值

#删除狗蛋这个人
#注意删除的时候一定要加条件  where  条件
mysql> delete from person where name = "狗蛋";
#删除多条数据
mysql> delete from person where id in (1,3,4);

8.修改数据【慎用】

UPDATE 表名称 SET 列名称 = 新值，列名称=新值,... WHERE 列名称 = 某值
#修改单个
mysql> update person set name = "heidan" where name = "狗蛋";
#修改多个
mysql> update person set name="小苏苏", age=29, gender=1 where id = 1;

9.事务操作

#如何保证多个sql语句同时执行或者同时不执行呢？
#学习事务：select * from person;
#1.开启事务，默认是关闭的 换句话说其实就是将sql语句自动提交关闭掉！！！  
#set autocommit = 0;
#要么回滚  rollback;
#要么都执行  commit;
mysql> set autocommit = 0;
mysql> update person set salary= salary-100 where id = 5;
mysql> update person set salary= salary+100 where id = 6;
mysql> rollback;
#要么回滚  不变
mysql> update person set salary= salary-100 where id = 5;
mysql> update person set salary= salary+100 where id = 6;
mysql> commit;
#要么都执行  commit;

10.查询【重点】

#查询指定的字段的数据
1.#select 字段1,字段2,字段3,... from 表名;
mysql> select id, name, salary  from person;
#按照字段进行查询的时候，可以对字段进行起别名
2.#select 字段1 as 别名1, 字段2 as 别名2 , ... from 表名
mysql> select id as "编号", name as "名字", salary as "薪资" from person;

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

下面的运算符可在 WHERE 子句中使用：

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

1.# selecty *   查什么  查所有字段
#from person   从哪查   从person表中
#where id = 6  条件是啥  id为6 
mysql> select * from person where id=6;
mysql> select * from person where id != 6;
mysql> select * from person where age >= 30;
#  where 字段 between  值1 and 值2;    在哪个之间  查年龄在10~30岁之间的额数据
mysql> select * from person where age between 10 and 30;
#like关键  模糊匹配
#where 字段  like  "%值%";
mysql> select * from person where name like "%苏%";
mysql> select * from person where name like "%苏";
mysql> select * from person where name like "苏%";
mysql> select * from person where name like "%苏%";

逻辑运算符：

&& （and）

|| （or）

#找出来性别为1并且年龄为29的数据
mysql> select * from person where gender = 1 and age = 29;
#年龄小于30岁的 或者性别为0De数据
mysql> select * from person where age < 30 or gender = 0;

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

语法格式

select 字段 from 表 order by 字段 asc;默认是升序  asc可以省略
select 字段 from 表 order by 字段 desc;
mysql> select * from person order by salary;
mysql> select * from person order by salary desc;
#扩展：
	#年龄大于20岁的  然后薪资倒序排
	mysql> select * from person where age > 20 order by salary desc;
	#注意事项:  当where和order by 一起使用的时候 where 必须在order by的前面

#先按照薪资倒序排，如果薪资一样的话，再按照年龄进行升序排
mysql> select * from person order by salary desc, age asc;

limit :限制输出

语法格式

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

详情见day28；

1.查询

方法：

​ max();

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

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

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

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

select max(字段名字) FROM 表名字;
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 |
+------+-----------+--------+------+--------------+--------+
#求一个字段的最大值的
mysql> select max(salary) from person;
#求一个字段所有的数据平均值 null  是不计入的
mysql> select avg(age) from person;
#求一个字段的所有的数据的总和
mysql> select sum(salary) from person;
#统计一个表中有多少条数据
mysql> select count(*) from person;

2.1默认值

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

2.2非空

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

mysql> create table person2 (
    -> id int not null,
    -> name varchar(32)
    -> );

2.3唯一

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

mysql> create table person3 (
    -> id int not null,
    -> name varchar(32) unique not null
    -> );

2.4主键

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

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

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

mysql> create table person4(
    -> id int primary key ,
    -> name varchar(32)
    -> );

2.5自增长

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

auto_increment

mysql> create table person5 (
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );

所以创建表的基础：

mysql> create table person6(
    -> id int primary key auto_increment,
    -> name varchar(32) not null,
    -> age int not null,
    -> info text
    -> );

扩展知识点：

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

类型	格式	用途	默认值
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 person7 (
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> createTime timestamp default current_timestamp
    -> );

1.外键的约束

真实开发的时候，特别是外键约束的表的时候，要加上级联删除和级联修改

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> 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-10 10:46:04 |
|      4 | erbei    |       2 | 2023-04-10 10:46:08 |
|      5 | erjia    |       2 | 2023-04-10 10:46:11 |
+--------+----------+---------+---------------------+
#修改：
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-10 10:46:04 |
|      4 | erbei    |       4 | 2023-04-10 10:46:08 |
|      5 | erjia    |       4 | 2023-04-10 10:46:11 |
+--------+----------+---------+---------------------+

2.常规的联表查询

select emp.emp_name, dept.dept_name  # 查什么
from emp, dept  #从哪查
where emp.dept_id = dept.dept_id and emp.emp_name = "erbei" #查询的条件
#查所有的id
mysql> select *
    -> from emp, dept
    -> where emp.dept_id = dept.dept_id;
#查需要的内容    
    mysql> select *
    -> from emp, dept
    -> where emp.dept_id = dept.dept_id and emp.emp_name = "erbei"; #对表起表名  
select e.emp_name, d.dept_name
from emp e, dept d
where e.dept_id = d.dept_id and e.emp_name = "erbei";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erbei    | 品保部    |
+----------+-----------+
#找出教学部有有哪些人
select d.dept_name, e.emp_name
from dept d, emp e
where d.dept_id = e.dept_id  and d.dept_name= "教学部";

2.2内连接和外连接

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

**注释：**INNER JOIN 与 JOIN 是相同的。

内连接

select e.emp_name, d.dept_name 
from emp  e
inner join dept d
on  e.dept_id = d.dept_id
where e.emp_name = "erjia";

mysql> select e.emp_name, d.dept_name  #查什么
    -> from emp  e  #从第一个表
    -> inner join dept d #关联第二表
    -> on  e.dept_id = d.dept_id  #表之间的连接的字段
    -> where e.emp_name = "erjia"; #条件

外连接【开发中用的很少】

#左外连接 和右外连接
#LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
mysql> select *
    -> from dept d
    -> left outer join emp e
    -> on d.dept_id = e.dept_id;
    #RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

mysql> select *
    -> from emp e
    -> right outer join dept d
    -> on e.dept_id = d.dept_id;

3.开发中如何使用多表联查

3.1一对多或者多对一 的查询

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 = "老邢";
mysql> select teacher.t_name, student.s_name
    -> from teacher, student
    -> where teacher.t_id = student.teacher_id and teacher.t_name = "老邢";
    //内连接方式
    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 = "老邢";

3.2多对多的场景

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 = "挖掘机";