MySQL基础二

最新推荐文章于 2023-12-14 21:00:00 发布

Cinnamorollwq

最新推荐文章于 2023-12-14 21:00:00 发布

阅读量464

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/cinnamorollwq/article/details/126002503

版权

mysql数据库操作

2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

3.查看下该新建的表有无内容（用select语句）

4.往新建的student表中插入数据（用insert语句），结果应如下所示：

5.修改lisi的年龄为50

6.以age字段降序排序

7.查询student表中年龄最小的3位同学跳过前2位

8.查询student表中年龄最大的4位同学

9.查询student表中名字叫zhangshan的记录

10.查询student表中名字叫zhangshan且年龄大于20岁的记录

11.查询student表中年龄在23到30之间的记录

12.修改wangwu的年龄为100

13.删除student中名字叫zhangshan且年龄小于等于20的记录

DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

INSERT语句

mysql> use wq;
Database changed

mysql> create table wangquan(id int not null,name varchar(10) not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_wq |
+--------------+
| wangquan     |
+--------------+
1 row in set (0.00 sec)

mysql> desc wangquan;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into wangquan(id,name,age) values(1,'w',20),(2,'q',21),(3,'wang',22),(4,'quan',23);   一次插入多条记录 
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from wangquan;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   23 |
+----+------+------+
4 rows in set (0.00 sec)

mysql>

SELECT语句

字段column表示法

表示符	代表什么？
*	所有字段
as	字段别名，如col1 AS alias1 当表名很长时用别名代替

条件判断语句WHERE

操作类型	常用操作符
操作符	>，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空
条件逻辑操作	AND OR NOT

ORDER BY：排序，默认为升序（ASC）

ORDER BY语句	意义
ORDER BY ‘column_name'	根据column_name进行升序排序
ORDER BY 'column_name' DESC	根据column_name进行降序排序
ORDER BY ’column_name' LIMIT 2	根据column_name进行升序排序并只取前2个结果
ORDER BY ‘column_name' LIMIT 1,2	根据column_name进行升序排序并且略过第1个结果取后面的2个结果

mysql> select * from wangquan;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   23 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> select name from wangquan;
+------+
| name |
+------+
| w    |
| q    |
| wang |
| quan |
+------+
4 rows in set (0.00 sec)

mysql> select * from wangquan order by age;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   23 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> select * from wangquan order by age desc;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | quan |   23 |
|  3 | wang |   22 |
|  2 | q    |   21 |
|  1 | w    |   20 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> select * from wangquan order by age limit 2;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from wangquan order by age limit 1,2;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | q    |   21 |
|  3 | wang |   22 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from wangquan where age >= 21;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   23 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from wangquan where age >=22 and name = 'quan';
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | quan |   23 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from wangquan where age between 21 and 22;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | q    |   21 |
|  3 | wang |   22 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from wangquan where age is not null;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   23 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> select * from wangquan where age is null;
Empty set (0.00 sec)

mysql>

update语句

mysql> select * from wangquan;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   23 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> update wangquan set age = 25 where name = 'quan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wangquan where name = 'quan';
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | quan |   25 |
+----+------+------+
1 row in set (0.00 sec)

mysql>

delete语句

mysql> select * from wangquan;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   25 |
+----+------+------+
4 rows in set (0.00 sec)

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

mysql> select * from wangquan;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> delete from wangquan;
Query OK, 4 rows affected (0.00 sec)

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

truncate语句

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间
truncate	删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

语句类型

特点

delete

DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间

truncate

删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

mysql> select * from wangquan;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | w    |   20 |
|  2 | q    |   21 |
|  3 | wang |   22 |
|  4 | quan |   25 |
+----+------+------+
4 rows in set (0.00 sec)

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

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

mysql> desc wangquan;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)