mysql基础二

已于 2022-08-10 11:05:44 修改
阅读量245 收藏
点赞数
文章标签： 数据库 mysql
于 2022-07-26 21:27:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Eternity_zzh/article/details/126003683
版权

目录

mysql基础二

DML操作

INSERT语句

SELECT语句

update语句

DELETE语句

truncate语句

DCL操作

创建授权GRANT

查看授权

取消授权REVOKE

实战案例

搭建mysql服务

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

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

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

修改lisi的年龄为50

以age字段降序排序

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

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

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

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

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

修改wangwu的年龄为100

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

DML操作

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

INSERT语句

 

mysql> use zhaozihao 
 

Database changed 
 

mysql> insert into student (id,name,age) value (1,'wrq',18); //插入一条数据 
 

Query OK, 1 row affected (0.03 sec) 
 

 

mysql> select * from student; //查看插入的数据 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 1 | wrq | 18 | 
 

+----+------+------+ 
 

1 row in set (0.00 sec) 
 

 

mysql> insert into student (id,name,age) values (2,'zzh',20),(3,'www',25),(4,'rrr',30),(5,'qqq',35),(6,'zzz',40),(7,'hhh',null); //插入多条数据 
 

Query OK, 6 rows affected (0.00 sec) 
 

Records: 6 Duplicates: 0 Warnings: 0 
 

 

mysql> select * from student; 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

| 7 | hhh | NULL | 
 

+----+------+------+ 
 

7 rows in set (0.00 sec) 
 

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 student; //查看表中所有内容 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

| 7 | hhh | NULL | 
 

+----+------+------+ 
 

7 rows in set (0.00 sec) 
 

 

mysql> select name from student; //查看表中name列内容 
 

+------+ 
 

| name | 
 

+------+ 
 

| wrq | 
 

| zzh | 
 

| www | 
 

| rrr | 
 

| qqq | 
 

| zzz | 
 

| hhh | 
 

+------+ 
 

7 rows in set (0.00 sec) 
 

 

mysql> select * from student order by age; //将表根据年龄进行升序排序查看 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 7 | hhh | NULL | 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

+----+------+------+ 
 

7 rows in set (0.00 sec) 
 

 

mysql> select * from student order by age desc; //根据年龄进行降序排列 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 6 | zzz | 40 | 
 

| 5 | qqq | 35 | 
 

| 4 | rrr | 30 | 
 

| 3 | www | 25 | 
 

| 2 | zzh | 20 | 
 

| 1 | wrq | 18 | 
 

| 7 | hhh | NULL | 
 

+----+------+------+ 
 

7 rows in set (0.00 sec) 
 

 

mysql> select * from student order by age limit 3; //根据年龄进行升序排序并只取前3个结果 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 7 | hhh | NULL | 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

+----+------+------+ 
 

3 rows in set (0.00 sec) 
 

mysql> select * from student order by age limit 1,2; //根据年龄进行升序排序并且略过第1个结果取后面的2个结果 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

+----+------+------+ 
 

2 rows in set (0.00 sec) 
 

 

mysql> select * from student where age >= 35; //查看student表中年龄大于等于35 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

+----+------+------+ 
 

2 rows in set (0.00 sec) 
 

 

mysql> select * from student where age >= 40 and name= 'zzz'; //查看表中年龄大于等于35并且名字为zzz 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 6 | zzz | 40 | 
 

+----+------+------+ 
 

1 row in set (0.00 sec) 
 

 

mysql> select * from student where age between 30 and 45; //查看表中年龄在30至45中间的 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

+----+------+------+ 
 

3 rows in set (0.00 sec) 
 

 

mysql> select * from student where age is not null; //查看表中年龄不为空值的 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

+----+------+------+ 
 

6 rows in set (0.00 sec) 
 

 

mysql> select * from student where age is null; //查看表中年龄为空值的 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 7 | hhh | NULL | 
 

+----+------+------+ 
 

1 row in set (0.00 sec) 
 

 

update语句
 

 

mysql> select * from student; //查看所有内容 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

| 7 | hhh | NULL | 
 

+----+------+------+ 
 

7 rows in set (0.00 sec) 
 

 

mysql> update student set age = 45 where name = 'hhh'; //用update语句将hhh年龄从null改为45 
 

Query OK, 1 row affected (0.01 sec) 
 

Rows matched: 1 Changed: 1 Warnings: 0 
 

 

mysql> select * from student where name = 'hhh'; //查看hhh 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 7 | hhh | 45 | 
 

+----+------+------+ 
 

1 row in set (0.00 sec) 
 

 

DELETE语句
 

DML操作之删操作delete
 

 

mysql> select * from student; //查看所有内容 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 1 | wrq | 18 | 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

| 7 | hhh | 45 | 
 

+----+------+------+ 
 

7 rows in set (0.00 sec) 
 

 

mysql> delete from student where id = 1; //删除id为1表记录 
 

Query OK, 1 row affected (0.00 sec) 
 

 

mysql> select * from student; //查看已被删除 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

| 7 | hhh | 45 | 
 

+----+------+------+ 
 

6 rows in set (0.00 sec) 
 

 

mysql> select * from student; //删除整张表的内容 
 

Empty set (0.00 sec) 
 

 

mysql> desc student; 
 

+-------+-------------+------+-----+---------+-------+ 
 

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

 

truncate语句
 

truncate与delete的区别：
 

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

 

mysql> select * from student; 
 

+----+------+------+ 
 

| id | name | age | 
 

+----+------+------+ 
 

| 2 | zzh | 20 | 
 

| 3 | www | 25 | 
 

| 4 | rrr | 30 | 
 

| 5 | qqq | 35 | 
 

| 6 | zzz | 40 | 
 

| 7 | hhh | NULL | 
 

| 1 | wrq | 18 | 
 

+----+------+------+ 
 

7 rows in set (0.00 sec) 
 

 

mysql> truncate student; //删除student表 
 

Query OK, 0 rows affected (0.01 sec) 
 

 

mysql> select * from student; 
 

Empty set (0.00 sec) 
 

 

mysql> desc student; 
 

+-------+-------------+------+-----+---------+-------+ 
 

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

 

DCL操作
 

创建授权GRANT
 

权限类型(priv_type)
 

权限类型代表什么？
ALL所有权限
SELECT读取内容的权限
INSERT插入内容的权限
UPDATE更新内容的权限
DELETE删除内容的权限
 

指定要操作的对象db_name.table_name
 

表示方式意义
.所有库的所有表
db_name指定库的所有表
 

vdb_name.table_name |指定库的指定表| 
 WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。
 

 

mysql> show databases; 
 

+--------------------+ 
 

| Database | 
 

+--------------------+ 
 

| information_schema | 
 

| mysql | 
 

| performance_schema | 
 

| zhaozihao | 
 

+--------------------+ 
 

5 rows in set (0.00 sec) 
 

 

mysql> grant all on *.* to 'zhaozihao'@'localhost' identified by 'zhaozihao'; //授权zhaozihao用户在数据库本机上登录访问所有数据库 
 

Query OK, 0 rows affected, 1 warning (0.00 sec) 
 

 

mysql> grant all on *.* to 'zhaozihao'@'127.0.0.1' identified by 'zhaozihao'; 
 

Query OK, 0 rows affected, 1 warning (0.00 sec) 
 

 

mysql> grant all on shuai.* to 'zhaozihao'@'192.168.183.133' identified by 'zhaozihao'; //授权zhaozihao用户在192.168.183.133上远程登录访问shuai数据库 
 

Query OK, 0 rows affected, 1 warning (0.00 sec) 
 

 

mysql> grant all on *.* to 'zhaozihao'@'%' identified by 'zhaozihao'; //授权zhaozihao用户在所有位置上远程登录访问zhaozihao数据库 
 

Query OK, 0 rows affected, 1 warning (0.00 sec) 
 

查看授权
 

 

//查看当前登录用户的授权信息 
 

mysql> show grants; 
 

+---------------------------------------------------------------------+ 
 

| Grants for root@localhost | 
 

+---------------------------------------------------------------------+ 
 

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | 
 

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | 
 

+---------------------------------------------------------------------+ 
 

2 rows in set (0.00 sec) 
 

 

 

//查看指定用户shuai的授权信息 
 

mysql> show grants for zhaozihao; 
 

+------------------------------------------------+ 
 

| Grants for shuai@% | 
 

+------------------------------------------------+ 
 

| GRANT ALL PRIVILEGES ON *.* TO 'zhaozihao'@'%' | 
 

+------------------------------------------------+ 
 

1 row in set (0.01 sec) 
 

 

mysql> show grants for 'zhaozihao'@'localhost'; 
 

+--------------------------------------------------------+ 
 

| Grants for shuai@localhost | 
 

+--------------------------------------------------------+ 
 

| GRANT ALL PRIVILEGES ON *.* TO 'zhaozihao'@'localhost' | 
 

+--------------------------------------------------------+ 
 

1 row in set (0.00 sec) 
 

 

mysql> show grants for 'zhaozihao'@'127.0.0.1'; 
 

+--------------------------------------------------------+ 
 

| Grants for shuai@127.0.0.1 | 
 

+--------------------------------------------------------+ 
 

| GRANT ALL PRIVILEGES ON *.* TO 'zhaozihao'@'127.0.0.1' | 
 

+--------------------------------------------------------+ 
 

1 row in set (0.00 sec) 
 

取消授权REVOKE
 

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
 

GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
 

 

mysql> revoke all on *.* from 'zhaozihao'@'192.168.183.133'; 
 

Query OK, 0 rows affected (0.00 sec) 
 

 

mysql> flush privileges; 
 

Query OK, 0 rows affected (0.00 sec) 
 

实战案例
 

搭建mysql服务
 

 

[root@zzh ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm //配置mysql的yum源 
 

--2022-07-25 08:35:42-- http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm 
 

Resolving dev.mysql.com (dev.mysql.com)... 23.64.178.143, 2600:1406:cc00:289::2e31, 2600:1406:cc00:287::2e31 
 

Connecting to dev.mysql.com (dev.mysql.com)|23.64.178.143|:80... connected. 
 

HTTP request sent, awaiting response... 301 Moved Permanently 
 

Location: https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm [following] 
 

--2022-07-25 08:35:42-- https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm 
 

Connecting to dev.mysql.com (dev.mysql.com)|23.64.178.143|:443... connected. 
 

HTTP request sent, awaiting response... 302 Moved Temporarily 
 

Location: https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm [following] 
 

--2022-07-25 08:35:43-- https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm 
 

Resolving repo.mysql.com (repo.mysql.com)... 104.85.245.54 
 

Connecting to repo.mysql.com (repo.mysql.com)|104.85.245.54|:443... connected. 
 

HTTP request sent, awaiting response... 200 OK 
 

Length: 25548 (25K) [application/x-redhat-package-manager] 
 

Saving to: 'mysql57-community-release-el7-10.noarch.rpm' 
 

 

mysql57-community-release-el7-10.noarch.rpm 100%[=======================================================================================================>] 24.95K 142KB/s in 0.2s 
 

 

2022-07-25 08:35:50 (142 KB/s) - 'mysql57-community-release-el7-10.noarch.rpm' saved [25548/25548] 
 

[root@zzh ~]# yum module disable mysql //禁用mysql 
 

Failed to set locale, defaulting to C.UTF-8 
 

MySQL Connectors Community 16 kB/s | 49 kB 00:03 
 

MySQL Tools Community 105 kB/s | 651 kB 00:06 
 

MySQL 5.7 Community Server 265 kB/s | 2.6 MB 00:10 
 

Last metadata expiration check: 0:00:01 ago on Mon Jul 25 08:38:44 2022. 
 

Dependencies resolved. 
 

================================================================================================================================================================================================= 
 

Package Architecture Version Repository Size 
 

================================================================================================================================================================================================= 
 

Disabling modules: 
 

mysql 
 

 

Transaction Summary 
 

================================================================================================================================================================================================= 
 

 

Is this ok [y/N]: y 
 

Complete! 
 

 

[root@zzh ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck //使用yum安装 
 

Failed to set locale, defaulting to C.UTF-8 
 

Last metadata expiration check: 0:22:50 ago on Mon Jul 25 08:38:44 2022. 
 

Dependencies resolved. 
 

================================================================================================================================================================================================= 
 

Package Architecture Version Repository Size 
 

================================================================================================================================================================================================= 
 

Installing: 
 

mysql-community-client x86_64 5.7.38-1.el7 mysql57-community 28 M 
 

mysql-community-common x86_64 5.7.38-1.el7 mysql57-community 311 k 
 

mysql-community-devel x86_64 5.7.38-1.el7 mysql57-community 4.2 M 
 

[root@zzh ~]# systemctl enable --now mysqld //启动mysql 
 

[root@zzh ~]# systemctl status mysqld 
 

● mysqld.service - MySQL Server 
 

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) 
 

Active: active (running) since Mon 2022-07-25 09:05:23 EDT; 5s ago 
 

Docs: man:mysqld(8) 
 

http://dev.mysql.com/doc/refman/en/using-systemd.html 
 

Process: 299040 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) 
 

Process: 298915 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) 
 

Main PID: 299042 (mysqld) 
 

Tasks: 27 (limit: 54222) 
 

Memory: 302.1M 
 

CGroup: /system.slice/mysqld.service 
 

└─299042 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid 
 

 

Jul 25 09:05:21 zzh systemd[1]: Starting MySQL Server... 
 

Jul 25 09:05:23 zzh systemd[1]: Started MySQL Server. 
 

[root@zzh ~]# ss -anltup | grep 3306 //查看端口是否监听 
 

tcp LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=299042,fd=21)) 
 

[root@zzh ~]# rm -rf /etc/yum.repos.d/mysql-community.repo mysql57-community-release-el7-10.noarch.rpm //删除源 
 

[root@zzh ~]# grep "password" /var/log/mysqld.log 
 

2022-07-25T13:05:22.199998Z 1 [Note] A temporary password is generated for root@localhost: REqIh6fe(XpR //过滤日志文件找到临时密码 
 

[root@zzh ~]# mysql -uroot -p //登录mysql 
 

Enter password: 
 

Welcome to the MySQL monitor. Commands end with ; or \g. 
 

Your MySQL connection id is 2 
 

Server version: 5.7.38 
 

 

Copyright (c) 2000, 2022, Oracle and/or its affiliates. 
 

 

Oracle is a registered trademark of Oracle Corporation and/or its 
 

affiliates. Other names may be trademarks of their respective 
 

owners. 
 

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 

mysql> set global validate_password_policy=0; 
 

Query OK, 0 rows affected (0.00 sec) 
 

 

mysql> set global validate_password_length=1; 
 

Query OK, 0 rows affected (0.00 sec) 
 

 

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'zhaozihao'; //修改密码 
 

Query OK, 0 rows affected (0.00 sec) 
 

 

mysql> 
 

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

 

mysql> desc student; 
 

+-------+--------------+------+-----+---------+----------------+ 
 

| Field | Type | Null | Key | Default | Extra | 
 

+-------+--------------+------+-----+---------+----------------+ 
 

| id | int(11) | NO | PRI | NULL | auto_increment | 
 

| name | varchar(100) | NO | | NULL | | 
 

| age | tinyint(4) | YES | | NULL | | 
 

+-------+--------------+------+-----+---------+----------------+ 
 

3 rows in set (0.01 sec) 
 

 

mysql> create database zhaozihao; 
 

Query OK, 1 row affected (0.01 sec) 
 

 

mysql> use zhaozihao; 
 

Database changed 
 

mysql> create table student (id int not null,name varchar(100) not null,age tinyint,primary key (id)); 
 

Query OK, 0 rows affected (0.01 sec) 
 

 

mysql> desc student; 
 

+-------+--------------+------+-----+---------+-------+ 
 

| Field | Type | Null | Key | Default | Extra | 
 

+-------+--------------+------+-----+---------+-------+ 
 

| id | int(11) | NO | PRI | NULL | | 
 

| name | varchar(100) | NO | | NULL | | 
 

| age | tinyint(4) | YES | | NULL | | 
 

+-------+--------------+------+-----+---------+-------+ 
 

3 rows in set (0.02 sec) 
 

 

mysql> 
 

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

 

mysql> select * from student; 
 

Empty set (0.00 sec) 
 

 

mysql> 
 

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

 

+----+-------------+------+ 
 

| id | name | age | 
 

+----+-------------+------+ 
 

| 1 | tom | 20 | 
 

| 2 | jerry | 23 | 
 

| 3 | wangqing | 25 | 
 

| 4 | sean | 28 | 
 

| 5 | zhangshan | 26 | 
 

| 6 | zhangshan | 20 | 
 

| 7 | lisi | NULL | 
 

| 8 | chenshuo | 10 | 
 

| 9 | wangwu | 3 | 
 

| 10 | qiuyi | 15 | 
 

| 11 | qiuxiaotian | 20 | 
 

+----+-------------+------+ 
 

 

mysql> insert into student (id,name,age) VALUES (1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20); 
 

Query OK, 11 rows affected (0.00 sec) 
 

Records: 11 Duplicates: 0 Warnings: 0 
 

 

mysql> select * from student; 
 

+----+-------------+------+ 
 

| id | name | age | 
 

+----+-------------+------+ 
 

| 1 | tom | 20 | 
 

| 2 | jerry | 23 | 
 

| 3 | wangqing | 25 | 
 

| 4 | sean | 28 | 
 

| 5 | zhangshan | 26 | 
 

| 6 | zhangshan | 20 | 
 

| 7 | lisi | NULL | 
 

| 8 | chenshuo | 10 | 
 

| 9 | wangwu | 3 | 
 

| 10 | qiuyi | 15 | 
 

| 11 | qiuxiaotian | 20 | 
 

+----+-------------+------+ 
 

11 rows in set (0.00 sec) 
 

修改lisi的年龄为50
 

 

mysql> update student set age = 50 where name = 'lisi'; 
 

Query OK, 1 row affected (0.00 sec) 
 

Rows matched: 1 Changed: 1 Warnings: 0 
 

 

mysql> select * from student; 
 

+----+-------------+------+ 
 

| id | name | age | 
 

+----+-------------+------+ 
 

| 1 | tom | 20 | 
 

| 2 | jerry | 23 | 
 

| 3 | wangqing | 25 | 
 

| 4 | sean | 28 | 
 

| 5 | zhangshan | 26 | 
 

| 6 | zhangshan | 20 | 
 

| 7 | lisi | 50 | 
 

| 8 | chenshuo | 10 | 
 

| 9 | wangwu | 3 | 
 

| 10 | qiuyi | 15 | 
 

| 11 | qiuxiaotian | 20 | 
 

+----+-------------+------+ 
 

11 rows in set (0.00 sec) 
 

以age字段降序排序
 

 

mysql> select * from student order by age desc; 
 

+----+-------------+------+ 
 

| id | name | age | 
 

+----+-------------+------+ 
 

| 7 | lisi | 50 | 
 

| 4 | sean | 28 | 
 

| 5 | zhangshan | 26 | 
 

| 3 | wangqing | 25 | 
 

| 2 | jerry | 23 | 
 

| 1 | tom | 20 | 
 

| 6 | zhangshan | 20 | 
 

| 11 | qiuxiaotian | 20 | 
 

| 10 | qiuyi | 15 | 
 

| 8 | chenshuo | 10 | 
 

| 9 | wangwu | 3 | 
 

+----+-------------+------+ 
 

11 rows in set (0.00 sec) 
 

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

 

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

 

mysql> select * from student order by age limit 2,3; 
 

+----+-------------+------+ 
 

| id | name | age | 
 

+----+-------------+------+ 
 

| 10 | qiuyi | 15 | 
 

| 1 | tom | 20 | 
 

| 11 | qiuxiaotian | 20 | 
 

+----+-------------+------+ 
 

3 rows in set (0.00 sec) 
 

 

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

 

mysql> select * from student order by age desc limit 4; 
 

+----+-----------+------+ 
 

| id | name | age | 
 

+----+-----------+------+ 
 

| 7 | lisi | 50 | 
 

| 4 | sean | 28 | 
 

| 5 | zhangshan | 26 | 
 

| 3 | wangqing | 25 | 
 

+----+-----------+------+ 
 

4 rows in set (0.00 sec) 
 

 

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

 

mysql> select * from student where name = 'zhangshan'; 
 

+----+-----------+------+ 
 

| id | name | age | 
 

+----+-----------+------+ 
 

| 5 | zhangshan | 26 | 
 

| 6 | zhangshan | 20 | 
 

+----+-----------+------+ 
 

2 rows in set (0.00 sec) 
 

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

 

mysql> select * from student where name = 'zhangshan' and age > 20; 
 

+----+-----------+------+ 
 

| id | name | age | 
 

+----+-----------+------+ 
 

| 5 | zhangshan | 26 | 
 

+----+-----------+------+ 
 

1 row in set (0.00 sec) 
 

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

 

mysql> select * from student where age between 23 and 30; 
 

+----+-----------+------+ 
 

| id | name | age | 
 

+----+-----------+------+ 
 

| 2 | jerry | 23 | 
 

| 3 | wangqing | 25 | 
 

| 4 | sean | 28 | 
 

| 5 | zhangshan | 26 | 
 

+----+-----------+------+ 
 

4 rows in set (0.00 sec) 
 

修改wangwu的年龄为100
 

 

mysql> update student set age = 100 where name = 'wangwu'; 
 

Query OK, 1 row affected (0.00 sec) 
 

Rows matched: 1 Changed: 1 Warnings: 0 
 

 

mysql> select * from student where name = 'wangwu'; 
 

+----+--------+------+ 
 

| id | name | age | 
 

+----+--------+------+ 
 

| 9 | wangwu | 100 | 
 

+----+--------+------+ 
 

1 row in set (0.00 sec) 
 

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

 

mysql> delete from student where name = 'zhangshan' and age <= 20; 
 

Query OK, 1 row affected (0.00 sec) 
 

 

mysql> select * from student; 
 

+----+-------------+------+ 
 

| id | name | age | 
 

+----+-------------+------+ 
 

| 1 | tom | 20 | 
 

| 2 | jerry | 23 | 
 

| 3 | wangqing | 25 | 
 

| 4 | sean | 28 | 
 

| 5 | zhangshan | 26 | 
 

| 7 | lisi | 50 | 
 

| 8 | chenshuo | 10 | 
 

| 9 | wangwu | 100 | 
 

| 10 | qiuyi | 15 | 
 

| 11 | qiuxiaotian | 20 | 
 

+----+-------------+------+ 
 

10 rows in set (0.00 sec)

                

        

        

    




    

      

        

            

              
                
                  Eternity_zzh
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      










                



	

		

			

				
					
MySQL增删改查语句

				
			

			

				

					qq_58480972的博客
				

				

					06-25
					
					1925
					
				

			

		

		

			
				
count()记录查询列有多少行 SUM():求数值序列的和 AVG():求平均数 MAX():求最大值 MIN():求最小值。基本语法 insert into 表名(列1，列2，列3.列4，…*代表代表所有列，要查询哪一列就把*改成哪一列，可以查询一列，也可以查询多列，多列用逗号隔开。基本语法update表名set列=值，列=值，....where..hereid=1(表示查询student表中id=1的学生的所有列)insertinto表名set列=值，列=值，列=值，....

			
		

	



                

              
                



	

		

			

				
					
Mysql基础

				
			

			

				

					博客
				

				

					07-25
					
					404
					
				

			

		

		

			
				
数据结构模型主要有层次模型网状结构关系模型关系模型二维关系row，column数据库管理系统DBMS关系。

			
		

	



                


  
              

                


	

		

			

				
					
jsp新闻发布系统大作业，mysql，有增删改查

				
			

			

				

					06-13
				

			

		

		

			
				
jsp新闻发布系统大作业，可导入myeclipse运行，使用mysql数据库，有增删改查，模糊查询，数据分页等基本功能，导入即可用

			
		

	





	

		

			

				
					
Mysql数据库

				
			

			

				

					Learning_xzj的博客
				

				

					03-21
					
					669
					
				

			

		

		

			
				
一、操作数据库

1. 创建数据库mydb

  create database mydb ;

2. 显示数据库

  show databases ; — 查看当前数据库服务器中所有的数据库

  show create database mydb ; — 查看前面创建的mydb数据库的定义信息

3. 修改数据库

  alter database [if not exists] mydb [alter_specification [ , alter_spection] ... ]...

			
		

	



		

			
		



	

		

			

				
					
创建数据库和数据表

				
			

			

				

					xkkkkkkke的博客
				

				

					03-18
					
					3313
					
				

			

		

		

			
				
1．掌握创建、修改及删除数据库、数据表的方法；
2．掌握SQL语言增、删、改操作。

			
		

	





	

		

			

				
					
MySQL基础PDF版

				
			

			

				

					05-16
				

			

		

		

			
				
包含： 1.数据库概述 2.MySQL数据操作 3.数据库三大约束 4. 常用函数 5.事务 6.视图 7.索引 8.权限管理 9.规范化数据库设计

			
		

	





	

		

			

				
					
mysql数据库基础.pdf

				
			

			

				

					01-02
				

			

		

		

			
				
数据库基础 题⽬ 01- ⼀条 SQL 语句在 MySQL 中是如何执⾏的？ 以如下SQL查询语句为例： 1 select c_id, first_name, last_name from customer where c_id=14; 第⼀步：连接器 通过MYSQL Server层中内置的连接器，...

			
		

	





	

		

			

				
					
mysql mysql基础介绍

				
			

			

				

					09-24
				

			

		

		

			
				
MySQL 基础介绍  MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），它使用 SQL（Structured Query Language）作为其操作语言。MySQL 具有高性能、可靠性和易于使用的特点，支持大量的并发用户连接，可以处理...

			
		

	





	

		

			

				
					
数据库MySQL-基础知识

				
			

			

				

					01-16
				

			

		

		

			
				
课程资源为...在基础知识中，主要讲解MySQL的基础知识，包含MySQL安装、SQL、约束、函数、多表查询、事务等。

			
		

	





	

		

			

				
					
MySql数据库基础知识点总结

				
			

			

				

					09-08
				

			

		

		

			
				
MySQL数据库是世界上最流行的开源关系型数据库管理系统之一，其基础知识点对于任何想要从事数据库管理或开发工作的人来说至关重要。本文将深入探讨MySQL的一些核心概念，包括数据库的创建、查看、选择、删除，以及...

			
		

	





	

		

			

				
					
数据库实验-创建创建学生表

				
			

			

				

					qq_45648856的博客
				

				

					04-29
					
					2万+
					
				

			

		

		

			
				
（1）在表空间TableSpace_XXX下创建学生表（student）、学生选课表（SC）、课程表（course）

学生表：Student_XXX，属性为：(Sno 学号, Sname 姓名, Ssex 性别, Sdept 所在院系），其中学号Sno主码，其中sno为number，sname为varchar2（10），ssex为char（2），sdept为varchar2（10）。

课程表：...

			
		

	





	

		

			

				
					
数据库入门

				
			

			

				

					BBG_Clown的博客
				

				

					12-01
					
					2616
					
				

			

		

		

			
				
数据库学习笔记

			
		

	





	

		

			

				
					
MySQL实操练习

				
			

			

				

					Karl_plb的博客
				

				

					09-17
					
					3532
					
				

			

		

		

			
				
1.以自己的姓名创建一个数据库


CREATE DATABASE plb

2.在此数据库下创建如下3表，数据类型，宽度，是否为空等，根据实际情况自己定义。

A．雇员表（employee）：

雇员编号（empid），

姓名（name），

性别（gender），

职称（title），

出生日期（birthday），

所在部门编号（depid）；

其中雇员编号为主键；

B．部门表（department）：

部门编号（depid），

部门名称（depname）；

其中部门编号为...

			
		

	





	

		

			

				
					
解决ajax请求出现'302 moved temporarily'问题

				
			

			

				

					Heier的专栏
				

				

					12-29
					
					1万+
					
				

			

		

		

			
				
ajax请求出现’302 moved temporarily’问题在使用PHP作为后台语言时，前台通过ajax请求带验证的后台PHP文件问，总会在首次加载页面时用F12开发工具看到302 moved temporarily提示，并且无法请求到后台的数据。 
在网上查阅相关资料后，借助其中一篇的解决思想ajax请求302 Moved Temporarily，在去掉了后台PHP文件的验证后，问题解决！

			
		

	





	

		

			

				
					
MySQL的一些基本增删改查操作

				
			

			

				

					a790398207的博客
				

				

					09-16
					
					678
					
				

			

		

		

			
				
MySQL基本指令
show databases ---------------------------------------->查看当前实例包含多少个数据库
create database if not exits [数据库名] ------------>创建数据库
use 数据库名 --------------------------------------------->进入指定数据

			
		

	





	

		

			

				
					
sql50题带建表语句带详细答案（sql高阶函数详解）

				
			

			

				

					啊策策大数据社区
				

				

					03-23
					
					4312
					
				

			

		

		

			
				
1. sql基础
)
建表语句
--建表
--学生表
CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL...

			
		

	





	

		

			

				
					
Hbase 入门笔记II ：基本shell操作

				
			

			

				

					weixin_41684423的博客
				

				

					04-16
					
					688
					
				

			

		

		

			
				
Hbase的表可以看作是一种服务，需要客户端和他连接，所以有disable，enable 不可用和可用状态。

致谢：http://c.biancheng.net/view/3587.html

基础操作

1. 查询服务器状态

status2. 查询版本号

version


DDL 操作：数据定义语言（Data Defination Language，DDL）操作主要用来定义、修改和查询表的数据库模式。

1. 创建一个表

create 'studen','info1' 最少有一个列族 例如i..

			
		

	





	

		

			

				
					
Centos yum命令提示failed to set locale, defaulting to C

					
热门推荐

				
			

			

				

					ice_actor的博客
				

				

					12-20
					
					4万+
					
				

			

		

		

			
				
目录问题locale提示解决方案
问题
在输入yum命令是出现
Failed to set locale, defaulting to C

出现这个问题是由于系统没有正确设置locale环境, 而locale是用于设置本地环境的比如：语言、时区、数字等
locale提示
运行locale命令会出现如下提示
locale: Cannot set LC_CTYPE to default locale...

			
		

	





	

		

			

				
					
mysql基础（二）

				
			

			

				

					m0_56681539的博客
				

				

					07-26
					
					302
					
				

			

		

		

			
				
它们区别在于可容纳存储范围不同。对应的这4种BLOB类型，可存储的最大长度不同，可根据实际情况选择。//注意char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，比如CHAR(30)就可以存储30个字符。WITHGRANTOPTION被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。...

			
		

	





	

		

			

				
					
mysql 基础操作

					
最新发布

				
			

			

				

					10-12
				

			

		

		

			
				
好的，关于 MySQL 基础操作，以下是一些常用的命令：  1. 连接 MySQL 数据库：`mysql -u 用户名 -p 密码 -h 主机名/IP地址`  2. 显示所有数据库：`show databases;`  3. 创建数据库：`create database 数据库名;`  4...

			
		

	



              



  
“相关推荐”对你有帮助么？

  

      
    
          
  • 
              
    
                  
                  
              
    
              
    非常没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    一般
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    有帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    非常有帮助
    
          
    • 
      

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值