目录
通过命令操作数据库
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 |
+-----------+-----------+
| 挖掘机 | 孙悟空 |
| 挖掘机 | 林黛玉 |
+-----------+-----------+