MySQL数据库1

最新推荐文章于 2023-07-08 17:34:41 发布

LiZongShao

最新推荐文章于 2023-07-08 17:34:41 发布

阅读量160

点赞数

文章标签：数据库 mysql 服务器

本文链接：https://blog.csdn.net/LiZongShao/article/details/130161004

版权

1.什么是数据库

顾名思义，数据库就是用来存储数据的，各个网站、app都需要存储数据，而这些数据就存在数据库中。

数据库的分类：①关系型数据库：mysql，Oracle，SqlServer，DB2，SQLite等；

②非关系型数据库：Redis，MongoDB，memoryche等；

其中，关系型数据库的存储形式是表格形式，非关系型数据库的存储形式是键值对形式。

2.数据库的相关概念

①数据库服务器：安装好mysql数据库软件之后，这个服务器会支撑数据的存储。

②数据库：一个项目对应一个数据库，数据库服务器下面可以有很多数据库。

③数据表：一个数据库中可以有多个表。

④字段和数据：一个表中可以有很多字段和数据。

总之，数据库服务器下可以有很多数据库，一个数据库可以有很多表，一个表可以有多个字段和数据。

3.通过命令操作数据库

在用命令操作数据库之前先要安装数据库的服务器，并且配置好全局的环境变量。

wind+ r
#1.连接数据库服务器的命令
C:\Users\bowang>mysql -hlocalhost -uroot -p123456
#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\bowang>mysql -uroot -p
Enter password: ******
#现在已经连接上了数据库服务器了，接下来咋办？查看服务器下面有多少个数据库
#2.展示当前服务器下面有多少个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
#以上的数据库是内置库不能动，你自己可以新建库
#3.新建一个数据库 create database 库名;
mysql> create database java2304;
Query OK, 1 row affected (0.00 sec)
#4.看一眼这个库创建好了没
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| java2304 |
| mysql |
| performance_schema |
| sys |
+--------------------+
#5.删除库 drop database 库名; 慎用
mysql> drop database java2304;
Query OK, 0 rows affected (0.01 sec)

#删除数据库已经成功了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
#6.删除以后再次创建
mysql> create database java2304;
Query OK, 1 row affected (0.00 sec)

#7.选中库，服务器下面有跟多数据库，选中哪个库然后再建表 use 库名;
mysql> use java2304;
Database changed
#8.查看当前数据库下面有多少张表
mysql> show tables;
Empty set (0.00 sec)#空的

#9.创建表
#语法格式: create table 表名字 (字段1 数据类型,字段2 数据类型,...);
mysql> create table person (id int(11), name varchar(32), age int);
Query OK, 0 rows affected (0.02 sec)
#10.查看表是否创建成功
mysql> show tables;
+--------------------+
| Tables_in_java2304 |
+--------------------+
| person |
+--------------------+
#11.查看表的结构
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| 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 person1 (
-> id int,
-> name varchar(32),
-> age int
-> );
Query OK, 0 rows affected (0.01 sec)
#13.删除表慎用！！！ drop table 表名;
mysql> drop table person1;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------------+
| Tables_in_java2304 |
+--------------------+
| person |
+--------------------+

#14额外讲两个命令查看创建库和创建表时候的信息
mysql> show create database java2304;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| java2304 | CREATE DATABASE `java2304` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table person;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`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)

步骤总结：连接数据库—查看库—新建库—选中库—查看库—新建表—删除表—删除库

4.修改表的字段

通过alter关键字修改表字段

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

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#2.添加age字段语法格式: alter table 表名 add 字段数据类型;
mysql> alter table person add age int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| 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 person add sex boolean after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| 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.修改一个字段的数据类型 name数据类型 varchar 改为char类型
#语法格式: alter table 表名 modify 字段修改后的数据类型;
mysql> alter table person modify name char(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

#varchar 和char的区别：
   #varchar 和char 都是用来字符串类型的数据的
   #char(32) 定长的存入数据的时候，不管你的数据多长，都是开辟32长度的
   #varchar(32) 变长的存入数据的额时候，随着你的数据长短，而开辟长度
mysql> desc person;
+-------+------------+------+-----+---------+-------+
| 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 person change sex gender int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc person;
+--------+----------+------+-----+---------+-------+
| 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 | |
+--------+----------+------+-----+---------+-------+
#6.添加一个字段
#数据类型 text 文本字符串不限制大小
mysql> alter table person add info text;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc person;
+--------+----------+------+-----+---------+-------+
| 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 | |
+--------+----------+------+-----+---------+-------+
#加薪资字段小数
# float(8,2);
# double(8,2);
# decimal(8,2);
#最大长度是8位其中小数占2位 999999.99
mysql> alter table person add salary decimal(8, 2);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc person;
+--------+--------------+------+-----+---------+-------+
| 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 | |
+--------+--------------+------+-----+---------+-------+

表字段数据类型总结：int，tinyint(boolean)，varchar，char，text，double，float，decimal

5.添加数据到表中

添加的语法格式：

insert into 表名 values(值1，值2，......)

或

insert into 表名(列1，列2，......) values (值1，值2，......)开发中常用

#插入一条数据到表中
mysql> insert into person values (1, '苏桐', 0, 99, "可爱得很", 8.99);
Query OK, 1 row affected (0.01 sec)
#发现在用这种方式插入数据的时候数据一定和表的字段进行匹配！！！
# select * from person;
#select 查询
#* 所有字段
# from 从哪查
#person 从这个表中查所有的数据

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

6.删除数据（慎用）

删除数据的语法格式：

delete from 表名 where 列名=值

mysql> select * from person;
+------+--------------+--------+------+--------------+--------+
| id | name | gender | age | info | salary |
+------+--------------+--------+------+--------------+--------+
| 1 | 苏桐 | 0 | 99 | 可爱得很 | 8.99 |
| 2 | 还在睡觉 | NULL | NULL | NULL | NULL |
| 3 | 狗蛋 | 1 | 12 | 是真的狗 | 98.98 |
| 3 | 狗蛋 | 1 | 12 | 是真的狗 | 98.98 |
| 4 | 二贝 | 0 | 78 | 神经吧 | 23.40 |
+------+--------------+--------+------+--------------+--------+
5 rows in set (0.00 sec)
#删除狗蛋这个人
#注意删除的时候一定要加条件 where 条件
mysql> delete from person where name = "狗蛋";
Query OK, 2 rows affected (0.01 sec)

mysql> select * from person;
+------+--------------+--------+------+--------------+--------+
| id | name | gender | age | info | salary |
+------+--------------+--------+------+--------------+--------+
| 1 | 苏桐 | 0 | 99 | 可爱得很 | 8.99 |
| 2 | 还在睡觉 | NULL | NULL | NULL | NULL |
| 4 | 二贝 | 0 | 78 | 神经吧 | 23.40 |
+------+--------------+--------+------+--------------+--------+
#删除多条数据
mysql> select * from person;
+------+--------+--------+------+--------------+--------+
| id | name | gender | age | info | salary |
+------+--------+--------+------+--------------+--------+
| 1 | 西西 | 0 | 99 | 可爱得很 | 8.99 |
| 3 | 狗蛋 | 1 | 12 | 是真的狗 | 98.98 |
| 4 | 二贝 | 0 | 78 | 神经吧 | 23.40 |
+------+--------+--------+------+--------------+--------+
3 rows in set (0.00 sec)

mysql> delete from person where id in (1,3,4);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from person;
Empty set (0.00 sec)

7.修改数据（慎用）

修改的语法格式：

update 表名 set 列名=新值1，列名=新值2，...... where 列名=值

mysql> update person set name = "heidan" where name = "狗蛋";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update person set name="小苏苏", age=29, gender=1 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

8.事务操作

事务处理可以用来维护数据库的完整性，保证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;）

9.查询（重点）

查询在开发中占的比重比较大

#查询表中所有数据查询的关键字 select
#select * from 表名; * 代表所有是通配符
#select * 查询所有的字段
#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 |
+------+-----------+--------+------+--------------+--------+
5 rows in set (0.00 sec)
#查询指定的字段的数据
#select 字段1,字段2,字段3,... from 表名;
mysql> select id, name, salary from person;
+------+-----------+--------+
| id | name | salary |
+------+-----------+--------+
| 1 | 小苏苏 | 8.99 |
| 3 | heidan | 98.98 |
| 4 | 二贝 | 23.40 |
| 5 | zihao | 700.00 |
| 6 | ruiqiang | 300.00 |
+------+-----------+--------+
5 rows in set (0.00 sec)
#按照字段进行查询的时候，可以对字段进行起别名
#select 字段1 as 别名1, 字段2 as 别名2 , ... from 表名
mysql> select id as "编号", name as "名字", salary as "薪资" from person;
+--------+-----------+--------+
| 编号 | 名字 | 薪资 |
+--------+-----------+--------+
| 1 | 小苏苏 | 8.99 |
| 3 | heidan | 98.98 |
| 4 | 二贝 | 23.40 |
| 5 | zihao | 700.00 |
| 6 | ruiqiang | 300.00 |
+--------+-----------+--------+

以上的都是查询所有的数据

以下是加条件的查询语句

语法格式：

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

下面的运算符可以在where子句中使用

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

# 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)

逻辑运算符：&&（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;

LiZongShao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库1

顾名思义，数据库就是用来存储数据的，各个网站、app都需要存储数据，而这些数据就存在数据库中。数据库的分类：①关系型数据库：mysql，Oracle，SqlServer，DB2，SQLite等；②非关系型数据库：Redis，MongoDB，memoryche等；其中，关系型数据库的存储形式是表格形式，非关系型数据库的存储形式是键值对形式。
复制链接

扫一扫