MySQL基础
MySQL的安装与配置
安装
//配置MySQL的yum 源
[root@zlb9 ~]# cd /usr/src/
[root@zlb9 src]# yum -y install wget
[root@zlb9 src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@zlb9 src]# ls
debug kernels mysql57-community-release-el7-10.noarch.rpm
[root@zlb9 src]# yum -y install mysql57-community-release-el7-10.noarch.rpm
[root@zlb9 ~]# ls /etc/yum.repos.d/
mysql-community.repo mysql-community-source.repo redhat.repo xx.repo
[root@zlb9 ~]#
[root@zlb9 ~]# yum -y install mysql-community-client mysql-community-server mysql-community-common mysql-community-devel
MySQL的配置
//首先要启动MySQL服务
[root@zlb9 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead)
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
[root@zlb9 ~]# systemctl start mysqld
[root@zlb9 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2019-02-16 22:38:32 CST; 13s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1500 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 1427 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 1504 (mysqld)
CGroup: /system.slice/mysqld.service
└─1504 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Feb 16 22:38:28 zlb9 systemd[1]: Starting MySQL Server...
Feb 16 22:38:32 zlb9 systemd[1]: Started MySQL Server.
[root@zlb9 ~]#
//检测3306端口是否起来了
[root@zlb9 ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 50 *:139 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 *:445 *:*
LISTEN 0 50 :::139 :::*
LISTEN 0 128 :::80 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 50 :::445 :::*
LISTEN 0 80 :::3306 :::*
[root@zlb9 ~]#
//在日志文件中找到临时密码
[root@zlb9 ~]#
[root@zlb9 ~]# grep "password" /var/log/mysqld.log
2019-02-16T14:38:28.905738Z 1 [Note] A temporary password is generated for root@localhost: ojYggY3cKt+! //此处的密码为ojYggY3cKt+!
[root@zlb9 ~]# 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; //这里的2条是为了设置规则
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 '123456'; //这里才是修改密码的命令
Query OK, 0 rows affected (0.00 sec)
mysql>
//防止MySQL自动升级，在这里要卸载最开始安装的yum源
[root@zlb9 ~]# rpm -qa | grep mysql
mysql-community-common-5.7.25-1.el7.x86_64
mysql-community-devel-5.7.25-1.el7.x86_64
mysql-community-libs-5.7.25-1.el7.x86_64
mysql-community-server-5.7.25-1.el7.x86_64
mysql-community-libs-compat-5.7.25-1.el7.x86_64
mysql57-community-release-el7-10.noarch
mysql-community-client-5.7.25-1.el7.x86_64
[root@zlb9 ~]# yum -y remove mysql57-community-release-el7-10.noarch
MySQL的程序组成
客户端：
- mysql
- mysql_secure_installation
- mysqldump
- mysqladmin
服务端：
- mysqld
mysql 的工具使用
//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
//具体使用情况
[root@zlb9 ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
[root@zlb9 ~]# mysql -uroot -p123456 -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@zlb9 ~]# mysql -uroot -p -hlocalhost
Enter password: //建议使用交互式的登录方式，在上面的-p后面不加密码就可以了
Welcome to the MySQL monitor. Commands end with ; or \g.
[root@zlb9 ~]# mysql -uroot -p -hlocalhost -e 'SHOW DATABASES;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@zlb9 ~]#
MySQL数据库操作
DDL操作
数据库操作
//创建数据库
//语法：CREATE DATABASE [IF NOT EXIST] 'DB_NAME';
//示例如下
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>
//删除数据库
//语法：DROP DATABASE [IF EXISTS ] 'DB_NAME' ;
//删除示例
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>
表操作
//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//创建示例
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>
//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';
//删除示例
mysql> DROP TABLE biao1;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql>
用户操作
mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
‘USERNAME’@'HOST的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
- IP地址：
- 通配符：
% ：匹配任意长度的任意字符，常用于设置允许从任何主机登录
_：匹配任意单个字符
//数据库用户创建
//语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//示例
mysql> CREATE USER 'test0'@'192.168.192.9' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@zlb9 ~]# mysql -utest0 -p123456 -h192.168.192.9
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>
//删除数据库用户
//语法：DROP USER 'username'@'host';
//示例
mysql> DROP USER 'test0'@'192.168.192.9';
Query OK, 0 rows affected (0.00 sec)
mysql>
查看命令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>
DML操作
DML操作就是我们常说的增删改查，这些都是针对于表的操作
insert语句
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
//示例
一次性添加一个
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>
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>
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>
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>
DCL操作
创建授权grant
权限类型(priv_type)
| 权限类型 | 代表 |
|---|---|
| ALL | 所有权限 |
| SELECT | 读取内容的权限 |
| INSERT | 插入内容的权限 |
| UPDATE | 更新内容的权限 |
| DELETE | 删除内容的权限 |
指定要操作的对象db_name.table_name
| 表示方式 | 意义 |
|---|---|
| . | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.table_name | 指定库的指定表 |
//语法：GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
//示例
一些实例
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>
mysql> create database zlb; //创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shujuku1 |
| sys |
| zlb |
+--------------------+
6 rows in set (0.05 sec)
mysql> use zlb;
Database changed
mysql> create table student(id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); //创建一张student表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_zlb |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from student; //查看表中是否存在记录
Empty set (0.00 sec)
mysql>
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,'wwangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20); //在表中插入数据
Query OK, 11 rows affected (0.03 sec)
Records: 11 Duplicates: 0 Warnings: 0
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)
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)
mysql>
mysql> update student set age = 50 where name = 'lisi'; //修改用户的年龄
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
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)
mysql>
mysql> select * from student order by age limit 3; //找出年纪最小的3个人
+----+----------+------+
| id | name | age |
+----+----------+------+
| 9 | wangwu | 3 |
| 8 | chenshuo | 10 |
| 10 | qiuyi | 15 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from student order by age desc limit 4; //找出年纪最大的4个人
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>
mysql> select * from student where name = 'zhangshan'; //查找名字叫zhangshan的记录
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.00 sec)
mysql> select * from student where name = 'zhangshan' and age > 20; //查找出年纪大于20岁的zhangshan的记录
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
mysql>
mysql> select * from student where age between 23 and 30; //找出年纪在23到30岁之间的记录
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>
mysql> update student set age = 100 where name = 'wangwu'; //把wangwu的年纪改成100岁
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)
mysql>
mysql> delete from student where name = 'zhangshan' and age <= 20; //把年纪小于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)
mysql>
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
