MySQL基础

最新推荐文章于 2022-10-29 20:00:21 发布

`廿十一

最新推荐文章于 2022-10-29 20:00:21 发布

阅读量122

点赞数 2

本文链接：https://blog.csdn.net/weixin_44436998/article/details/87714160

版权

1.Mysql安装

mysql安装方式有三种：

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
- rpm：有两种
  - OS Vendor：操作系统发行商提供的
  - 项目官方提供的
- deb

配置yum源

[root@fupian ~]# wget wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@fupian ~]# ls
anaconda-ks.cfg  mysql57-community-release-el7-10.noarch.rpm
[root@fupian ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm 
warning: mysql57-community-release-el7-10.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-10 ################################# [100%]
[root@fupian ~]# cd /etc/yum.repos.d/
[root@fupian yum.repos.d]# ls
mysql-community.repo  mysql-community-source.repo  redhat.repo  fp.repo

安装mysql5.7

[root@fupian yum.repos.d]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel
[root@fupian ~]# systemctl start mysqld
[root@fupian ~]# systemctl enable mysqld

2. mysql配置

启动mysql

[root@fupian ~]# systemctl start mysqld
[root@fupian ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-02-15 15:22:13 CST; 13s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1325 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1249 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1327 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1327 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/my...

Feb 15 15:22:13fupian systemd[1]: Starting MySQL Server...
Feb 15 15:22:13fupian systemd[1]: Started MySQL Server.

确保3306端口已经监听起来

[root@fupian ~]# ss -antl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN      0      128         *:22                      *:*
LISTEN      0      100    127.0.0.1:25                      *:*
LISTEN      0      128        :::22                     :::*
LISTEN      0      100       ::1:25                     :::*
LISTEN      0      80         :::3306                   :::*

在日志文件中找出临时密码

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

使用获取到的临时密码登录mysql

[root@fupian ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

修改mysql登录密码

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.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

为避免mysql自动升级，这里需要卸载最开始安装的yum源

[root@fupian ~]# rpm -qa|grep mysql
mysql-community-server-5.7.23-1.el7.x86_64
mysql57-community-release-el7-10.noarch
mysql-community-common-5.7.23-1.el7.x86_64
mysql-community-client-5.7.23-1.el7.x86_64
mysql-community-libs-compat-5.7.23-1.el7.x86_64
mysql-community-libs-5.7.23-1.el7.x86_64
mysql-community-devel-5.7.23-1.el7.x86_64
[root@fupian ~]# yum -y remove mysql57-community-release-el7-10.noarch
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-
              : manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check 
....
Removed:
  mysql57-community-release.noarch 0:el7-10

Complete!

3.mysql的程序组成

客户端
- mysql：CLI交互式客户端程序
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- mysqldump：mysql备份工具
- mysqladmin
服务器端
- mysqld

mysql工具使用

语法：

mysql [OPTIONS] [database]

常用的OPTIONS：

    -uUSERNAME      //指定用户名，默认为root
    -hHOST          //指定服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD      //指定用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              //查看当前使用的mysql版本
    -e          //不登录mysql执行sql语句后退出，常用于脚本

[root@fupian ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

[root@fupian ~]# mysql -uroot -pfupian-hlocalhost
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> 

[root@fupian ~]# mysql -uroot -p -hlocalhost
Enter password:    //建议使用交互式的登录方式，在上面的-p后面不加密码就可以了
Welcome to the MySQL monitor.  Commands end with ; or \g.

[root@fupian ~]# mysql -uroot -p -hlocalhost -e 'SHOW DATABASES;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@fupian ~]#

4.mysql数据库操作

1. DDL操作
数据库操作

1.创建数据库

语法：CREATE DATABASE [IF NOT EXIST] 'DB_NAME';

2.示例如下

mysql> CREATE DATABASE IF NOT EXISTS shujuku1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shujuku1           |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

3.删除数据库

语法：DROP DATABASE [IF EXISTS ] 'DB_NAME' ;

4.删除示例

mysql> DROP DATABASE IF EXISTS shujuku1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

2. 表操作

1.创建表

语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';

2.创建示例

mysql> CREATE DATABASE shujuku1;
Query OK, 1 row affected (0.00 sec)

mysql> use shujuku1;
Database changed
mysql> CREATE TABLE biao1 (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;
+--------------------+
| Tables_in_shujuku1 |
+--------------------+
| biao1              |
+--------------------+
1 row in set (0.00 sec)

mysql>

3.删除表

语法：DROP TABLE [ IF EXISTS ] 'table_name';

4.删除示例

mysql> DROP TABLE biao1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql>

3. 用户操作

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录

这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

IP地址，如：192.168.123.10
通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符

1.数据库用户创建

语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

2.示例

mysql> CREATE USER 'test0'@'192.168.123.10' IDENTIFIED BY 'fupian';
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
[root@fupian ~]# mysql -utest0 -pfupian -h192.168.123.10
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

3.删除数据库用户

//语法：DROP USER 'username'@'host';

4.示例

mysql> DROP USER 'test0'@'192.168.123.10';
Query OK, 0 rows affected (0.00 sec)

mysql>

4. 查看命令SHOW

mysql> SHOW CHARACTER SET;   //查看支持的所有字符集
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |

mysql> SHOW ENGINES;    //查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables 

mysql> SHOW DATABASES;   //查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |

mysql> SHOW TABLES FROM shujuku1;   //不进入某数据库而列出其包含的所有表
Empty set (0.00 sec)

mysql>

查看表结构

语法：

DESC [db_name.]table_name;

mysql> DESC shujuku1.biao1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

查看某表的创建命令

语法：

SHOW CREATE TABLE table_name;

mysql> SHOW CREATE TABLE shujuku1.biao1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| biao1 | CREATE TABLE `biao1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

查看某表的状态

语法：

SHOW TABLE STATUS LIKE 'table_name'\G

示例

mysql> use shujuku1;   //首先要进入到数据库中
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLE STATUS LIKE 'biao1'\G    //查看表的状态
*************************** 1. row ***************************
           Name: biao1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-02-17 00:29:31
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql>

5. 获取帮助

获取命令使用帮助

语法：

HELP keyword;

mysql> HELP CREATE TABLE;       //获取创建表的帮助
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
 ......
 ......

5.DML操作

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

1. INSERT语句

语法：

示例
一次性添加一个

mysql> INSERT INTO biao1 (id,name,age) VALUE (1,'user1',1);   
Query OK, 1 row affected (0.00 sec)

一次性添加多个

mysql> INSERT INTO biao1 (id,name,age) VALUES (2,'user2',2),(3,'user3',3),(4,'user4',4);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

2. SELECT语句
语法：

SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

示例

mysql> select * from biao1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |    1 |
|  2 | user2 |    2 |
|  3 | user3 |    3 |
|  4 | user4 |    4 |
+----+-------+------+
4 rows in set (0.00 sec)

mysql>
mysql> select name from biao1;
+-------+
| name  |
+-------+
| user1 |
| user2 |
| user3 |
| user4 |
+-------+
4 rows in set (0.00 sec)


mysql> 

mysql> select * from biao1 ORDER BY age;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |    1 |
|  2 | user2 |    2 |
|  3 | user3 |    3 |
|  4 | user4 |    4 |
+----+-------+------+
4 rows in set (0.00 sec)

mysql>
mysql> 
mysql> select * from biao1 ORDER BY age DESC;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  4 | user4 |    4 |
|  3 | user3 |    3 |
|  2 | user2 |    2 |
|  1 | user1 |    1 |
+----+-------+------+
4 rows in set (0.00 sec)

mysql>

mysql> select * from biao1 ORDER BY age limit 2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |    1 |
|  2 | user2 |    2 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> select * from biao1 ORDER BY age limit 1,2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | user2 |    2 |
|  3 | user3 |    3 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from biao1 WHERE age >= 4;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  4 | user4 |    4 |
+----+-------+------+
1 row in set (0.00 sec)

mysql> 
mysql> select * from biao1 WHERE age >= 3 AND name = 'user3';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  3 | user3 |    3 |
+----+-------+------+
1 row in set (0.00 sec)

mysql>
mysql> select * from biao1 WHERE age BETWEEN 2 and 3;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | user2 |    2 |
|  3 | user3 |    3 |
+----+-------+------+
2 rows in set (0.00 sec)

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

mysql> select * from biao1 where age is not null;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |    1 |
|  2 | user2 |    2 |
|  3 | user3 |    3 |
|  4 | user4 |    4 |
+----+-------+------+
4 rows in set (0.00 sec)

mysql>

3. update语句

语法：
```UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

示例

mysql> select * from biao1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |    1 |
|  2 | user2 |    2 |
|  3 | user3 |    3 |
|  4 | user4 |    4 |
+----+-------+------+
4 rows in set (0.00 sec)

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

mysql> select * from biao1 where name = 'user1';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |    5 |
+----+-------+------+
1 row in set (0.00 sec)

mysql>

4. delete语句

语法：

DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

示例

mysql> delete from biao1 where id = 3;   //删除某一行的内容
Query OK, 1 row affected (0.00 sec)

mysql> select * from biao1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |    5 |
|  2 | user2 |    2 |
|  4 | user4 |    4 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> delete from biao1;   //删除整张表的内容
Query OK, 3 rows affected (0.00 sec)

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

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

mysql>

案例：

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

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

3.查看下该新建的表有无内容（用select语句）
4.往新建的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 |
+----+-------------+------+

5.修改lisi的年龄为50
6.以age字段降序排序
7.查询student表中年龄最小的3位同学
8.查询student表中年龄最大的4位同学
9.查询student表中名字叫zhangshan的记录
10.查询student表中名字叫zhangshan且年龄大于20岁的记录
11.查询student表中年龄在23到30之间的记录
12.修改wangwu的年龄为100
13.删除student中名字叫zhangshan且年龄小于等于20的记录

案例解析：

搭建mysql服务
略
创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age）

[root@fupian ~]# grep "password" /var/log/mysqld.log 
2019-01-17T11:02:44.387565Z 1 [Note] A temporary password is generated for root@localhost: wwqS;jonS09f
[root@fupian ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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 'wangqing123!';
Query OK, 0 rows affected (0.00 sec)
mysql> create database fp;
Query OK, 1 row affected (0.00 sec)

mysql> use fp;
Database changed
mysql> CREATE TABLE student (id int(11),name varchar(100),age tinyint(4) NULL);
Query OK, 0 rows affected (0.22 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.16 sec)

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

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

往新建的student表中插入数据（用insert语句）

mysql> insert student 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.04 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.80 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.03 sec)

查询student表中年龄最小的3位同学

mysql> select * from student order by age limit 3;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    9 | wangwu   |    3 |
|    8 | chenshuo |   10 |
|   10 | qiuyi    |   15 |
+------+----------+------+
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.01 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.02 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 (1.34 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      |  100 |
|   10 | qiuyi       |   15 |
|   11 | qiuxiaotian |   20 |
+------+-------------+------+
11 rows in set (0.00 sec)

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

mysql> delete from student where name = 'zhangshan' and age <= 20;
Query OK, 1 row affected (0.06 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)

`廿十一

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础

1.Mysql安装mysql安装方式有三种：源代码：编译安装二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用程序包管理器管理的程序包：rpm：有两种OS Vendor：操作系统发行商提供的项目官方提供的deb配置yum源[root@fupian ~]# wget wget http://dev.mysql.com/get/mysql57-com...
复制链接

扫一扫