MySQL数据库的基本操作
1、查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2、创建数据库
mysql> create database dunkai;
Query OK, 1 row affected (0.06 sec)
3、使用数据库
mysql> use dunkai;
Database changed
4、删除数据库
mysql> drop database dunkai;
Query OK, 0 rows affected (0.07 sec)
如果要删除的数据库已经被删除了,再次删除时,会报错:
mysql> drop database dunkai;
ERROR 1008 (HY000): Can't drop database 'dunkai'; database doesn't exist
为防止报错,可使用下面安全删除方式!!!
安全方式删除:
mysql> drop database if exists dunkai;
Query OK, 0 rows affected, 1 warning (0.01 sec)
5、数据库的存储引擎
MySQL的核心就是存储引擎
mysql> show engines \g
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
6、查看数据库中有哪些数据表
mysql> show tables;
Empty set (0.01 sec)
7、数据表的创建
mysql> create table student(
-> id int,
-> name varchar(10),
-> sex varchar(2),
-> age int,
-> score int
-> );
Query OK, 0 rows affected (0.02 sec)
create table student(
id int,
name varchar(10),
sex varchar(2),
age int,
score int
);
8、删除表
mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)
9、1064错误
表示SQL语句错误。
10、修改表
修改表名:
方法1:
mysql> alter table student rename to student1;
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+------------------+
| Tables_in_dunkai |
+------------------+
| student1 |
+------------------+
1 row in set (0.00 sec)
方法2:
mysql> alter table student1 rename as student;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_dunkai |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
11、desc查看表中字段类型
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| sex | varchar(2) | YES | | NULL | |
| age | int | YES | | NULL | |
| score | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
12、往表中插入数据
插入单条记录
mysql> insert into student
-> (id,name,sex,age,score)
-> value
-> (1,"张三","男",18,100);
Query OK, 1 row affected (0.01 sec)
查看:
mysql> select * from student;
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
+------+------+------+------+-------+
1 row in set (0.00 sec)
插入多条记录
mysql> insert into student
-> (id,name,sex,age,score)
-> values
-> (2,"李四","男",19,99),
-> (3,"王五","女",17,98);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
insert into student
(id,name,sex,age,score)
values
(2,"李四","男",19,99),
(3,"王五","女",17,98);
mysql> select * from student;
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
+------+------+------+------+-------+
3 rows in set (0.00 sec)
13、使用主键约束
主键:主码。
主键约束:设置为主键的字段,数据是唯一的,不能为空。
主键能够唯一的表示表中的一条记录。
单个字段主键
mysql> create table student(
-> id int primary key,
-> name varchar(10),
-> sex varchar(2),
-> age int,
-> score int
-> );
Query OK, 0 rows affected (0.02 sec)
表中已有了id为1的这条记录,再次插入:
mysql> insert into student
-> (id,name,sex,age,score)
-> value
-> (1,"张三","男",18,100);
ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'
在定义之后指定主键
mysql> create table student(
-> id int,
-> name varchar(10),
-> sex varchar(2),
-> age int,
-> score int,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.07 sec)
多个字段联合主键
mysql> create table student(
-> id int,
-> name varchar(10),
-> sex varchar(2),
-> age int,
-> score int,
-> primary key(id,name,age)
-> );
Query OK, 0 rows affected (0.02 sec)
自增
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(10),
-> sex varchar(2),
-> age int,
-> score int
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(10),
-> sex varchar(2),
-> age int,
-> score int
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into student
-> (id,name,sex,age,score)
-> value
-> (1,"张三","男",18,100);
Query OK, 1 row affected (0.06 sec)
mysql> insert into student
-> (name,sex,age,score)
-> value
-> ("赵六","女",22,99);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+------+------+-------+
| id | name | sex | age | score |
+----+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 赵六 | 女 | 22 | 99 |
+----+------+------+------+-------+
2 rows in set (0.00 sec)
14、数据类型
数值类型、日期/时间类型、字符串类型
数值类型:
整数类型:
tinyint, smallint, mediumint, int, bigint
类型 | 大小 | 所占字节 | 有符号 | 无符号 |
---|---|---|---|---|
tinyint | 很小的整形 | 1个字节 | -128~127 | 0~255 |
smallint | 小的整形 | 2个字节 | -32768~32767 | 0~65535 |
mediumint | 中等整形 | 3个字节 | -8388608~8388607 | 0~16777215 |
int | 普通整形 | 4个字节 | ||
bigint | 大的整形 | 8个字节 |
重新设计表
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(10),
-> sex varchar(2),
-> age tinyint unsigned,
-> score int
-> );
Query OK, 0 rows affected (0.01 sec)
age tinyint unsigned,
小数类型
浮点型:
单精度浮点型:
float:占4个字节
双精度浮点型:
double:8个字节
定点型:
decimal(M,N):M+2字节 以串的方式存储
注:浮点型和定点型都可以使用(M,N的方式来表示),M:精度,总位数;N:标度,小数位数。
重新设计表:
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(10),
-> sex varchar(2),
-> age tinyint unsigned,
-> score float,
-> salary decimal(9,2)
-> );
Query OK, 0 rows affected (0.02 sec)
日期时间类型:
DATETIME:
YYYY-MM-DD HH:MM:SS
1000-01-01 00:00:00->9999-12-31 23:59:59
8个字节
DATE:
YYYY-MM-DD
1000-01-01 ->9999-12-31
3个字节
TIMESTAMP:
YYYY-MM-DD HH:MM:SS
1970-01-01 00:00:00->2038-01-19 03:14:07
4个字节
TIME:
HH:MM:SS
-838:59:59->838:59:59
3个字节
YEAR:
YYYY
1901->2155
1个字节
重新设计表:
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(10),
-> sex varchar(2),
-> age tinyint unsigned,
-> score float,
-> salary decimal(9,2),
-> birth date
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student
-> (name,sex,age,score,salary,birth)
-> value
-> ("张三","男",18,100,5000,"2002-12-16");
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+------+-------+---------+------------+
| id | name | sex | age | score | salary | birth |
+----+------+------+------+-------+---------+------------+
| 1 | 张三 | 男 | 18 | 100 | 5000.00 | 2002-12-16 |
+----+------+------+------+-------+---------+------------+
1 row in set (0.00 sec)
添加默认值重新设计表:
mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(10) not null default"",
-> sex varchar(2) not null default"",
-> age tinyint unsigned not null default 0,
-> score float not null default 0,
-> salary decimal(9,2) not null default 0,
-> birth date not null default "2020-01-01"
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student
-> (name,sex,age,score)
-> value
-> ("张三","男",18,100);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+-----+-----+-------+--------+------------+
| id | name | sex | age | score | salary | birth |
+----+------+-----+-----+-------+--------+------------+
| 1 | 张三 | 男 | 18 | 100 | 0.00 | 2020-01-01 |
+----+------+-----+-----+-------+--------+------------+
1 row in set (0.00 sec)
文本字符串类型
char, varchar, text,
char(M)
固定长度字符串 M字节,1<=M<=255
varchar(M)
可变长字符串 L+1字节,L<=M, 1<=M<=255 (L:字符串实际长度)
**char和varchar区别:**char是固定长度,固定M个字节,而varchar是实际长度+1,实际长度不能超过M。
如果要求查询速度:可以使用char,尽量不要浪费空间。
varchar的查询速度低于char,如果要求存储空间,可以使用varchar。
text
tinytext
小的字符串 L+1字节,L<2^8
mediumtext
中等字符串 L+2字节,L<2^16
longtext
长的文本 L+3个字节,L<2^24
例:
mysql> create table news(
-> id int primary key auto_increment,
-> titel varchar(50) not null default"",
-> publictime datetime not null default "2020-12-16 00:00:00",
-> publier varchar(10) not null default "",
-> msg mediumtext
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into news
-> (titel,publictime,publier,msg)
-> value
-> ("吼吼吼吼吼"","2020-12-16 00:00:00","admin","好好学习,天天向上");
Query OK, 1 row affected (0.06 sec)
mysql> select * from news;
+----+-------------------------------------+---------------------+---------+--------------------+
| id | titel | publictime | publier | msg |
+----+-------------------------------------+---------------------+---------+--------------------+
| 1 | 吼吼吼吼吼 | 2020-12-16 00:00:00 | admin | 好好学习,天天向上 |
+----+-------------------------------------+---------------------+---------+--------------------+
1 row in set (0.00 sec)
15、查询数据库
查询语句
查询所有字段:
(*是一个通配符,匹配所有字段)
mysql> select * from student;
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
+------+------+------+------+-------+
3 rows in set (0.00 sec)
查询多个字段:
mysql> select id,name,score from student;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | 张三 | 100 |
| 2 | 李四 | 99 |
| 3 | 王五 | 98 |
+------+------+-------+
3 rows in set (0.00 sec)
指定条件查询(while)
= 相等
<>, != 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于
between…and 位于两者之间
插入数据
mysql> insert into student
-> (id,name,sex,age,score)
-> values
-> (4,"赵六","男",13,60),
-> (5,"王八","男",21,58),
-> (6,"徐象","男",21,97);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
| 4 | 赵六 | 男 | 13 | 60 |
| 5 | 王八 | 男 | 21 | 58 |
| 6 | 徐象 | 男 | 21 | 97 |
+------+------+------+------+-------+
6 rows in set (0.00 sec)
查询:
mysql> select * from student where name="徐象";
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 6 | 徐象 | 男 | 21 | 97 |
+------+------+------+------+-------+
1 row in set (0.01 sec)
mysql> insert into student
-> (id,name,sex,age,score)
-> values
-> (7,"八戒","男",13,80),
-> (8,"悟空","男",21,58),
-> (9,"沙僧","男",21,97);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student where score=97;
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 6 | 徐象 | 男 | 21 | 97 |
| 9 | 沙僧 | 男 | 21 | 97 |
+------+------+------+------+-------+
2 rows in set (0.00 sec)
mysql> select id,name,sex from student where sex="女";
+------+------+------+
| id | name | sex |
+------+------+------+
| 3 | 王五 | 女 |
+------+------+------+
1 row in set (0.00 sec)
查出所有男(女)生:
mysql> select id,name,sex from student where sex<>"女";
+------+------+------+
| id | name | sex |
+------+------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
| 4 | 赵六 | 男 |
| 5 | 王八 | 男 |
| 6 | 徐象 | 男 |
| 7 | 八戒 | 男 |
| 8 | 悟空 | 男 |
| 9 | 沙僧 | 男 |
+------+------+------+
8 rows in set (0.00 sec)
mysql> select id,name,sex from student where sex<>"男";
+------+------+------+
| id | name | sex |
+------+------+------+
| 3 | 王五 | 女 |
+------+------+------+
1 row in set (0.00 sec)
mysql> select id,name,sex from student where sex!="男";
+------+------+------+
| id | name | sex |
+------+------+------+
| 3 | 王五 | 女 |
+------+------+------+
1 row in set (0.00 sec)
查询成绩在60~80分范围:
mysql> select id,name,score from student where score between 60 and 70;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 4 | 赵六 | 60 |
+------+------+-------+
1 row in set (0.00 sec)
mysql> select id,name,score from student where score between 60 and 80;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 4 | 赵六 | 60 |
| 7 | 八戒 | 80 |
+------+------+-------+
2 rows in set (0.00 sec)
mysql> insert into student
-> (id,name,sex,age,score)
-> values
-> (10,"观音","女",100,70);
Query OK, 1 row affected (0.00 sec)
mysql> select id,name,score from student where score between 60 and 80;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 4 | 赵六 | 60 |
| 7 | 八戒 | 80 |
| 10 | 观音 | 70 |
+------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from student where score between 60 and 80;
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 4 | 赵六 | 男 | 13 | 60 |
| 7 | 八戒 | 男 | 13 | 80 |
| 10 | 观音 | 女 | 100 | 70 |
+------+------+------+------+-------+
3 rows in set (0.00 sec)
查询成绩在60~80分范围的女生:
mysql> select * from student where score between 60 and 80 and sex="女";
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 10 | 观音 | 女 | 100 | 70 |
+------+------+------+------+-------+
1 row in set (0.00 sec)
带in关键字查询
in把在指定包含数字查询出来
mysql> select * from student;
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
| 4 | 赵六 | 男 | 13 | 60 |
| 5 | 王八 | 男 | 21 | 58 |
| 6 | 徐象 | 男 | 21 | 97 |
| 7 | 八戒 | 男 | 13 | 80 |
| 8 | 悟空 | 男 | 21 | 58 |
| 9 | 沙僧 | 男 | 21 | 97 |
| 10 | 观音 | 女 | 100 | 70 |
+------+------+------+------+-------+
10 rows in set (0.00 sec)
mysql> select * from student where score in(60,100);
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 4 | 赵六 | 男 | 13 | 60 |
+------+------+------+------+-------+
2 rows in set (0.00 sec)
mysql> select * from student where score in(60,70,80,90,100);
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 4 | 赵六 | 男 | 13 | 60 |
| 7 | 八戒 | 男 | 13 | 80 |
| 10 | 观音 | 女 | 100 | 70 |
+------+------+------+------+-------+
4 rows in set (0.00 sec)
not in关键字查询
mysql> select * from student where score not in(60,70,80,90,100);
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
| 5 | 王八 | 男 | 21 | 58 |
| 6 | 徐象 | 男 | 21 | 97 |
| 8 | 悟空 | 男 | 21 | 58 |
| 9 | 沙僧 | 男 | 21 | 97 |
+------+------+------+------+-------+
6 rows in set (0.00 sec)
带like的关键字
一般和like联合使用的两个通配符:% , _
查询姓王的:
mysql> select * from student where name like "王%";
+------+------+------+------+-------+
| id | name | sex | age | score |
+------+------+------+------+-------+
| 3 | 王五 | 女 | 17 | 98 |
| 5 | 王八 | 男 | 21 | 58 |
+------+------+------+------+-------+
2 rows in set (0.01 sec)
使用%通配符,匹配任意长度的字符,甚至包括零字符
mysql> insert into student
-> (id,name,sex,age,score)
-> values
-> (11,"王隔壁","男",30,30),
-> (12,"王老五","男",32,98);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student where name like "王%";
+------+--------+------+------+-------+
| id | name | sex | age | score |
+------+--------+------+------+-------+
| 3 | 王五 | 女 | 17 | 98 |
| 5 | 王八 | 男 | 21 | 58 |
| 11 | 王隔壁 | 男 | 30 | 30 |
| 12 | 王老五 | 男 | 32 | 98 |
+------+--------+------+------+-------+
4 rows in set (0.00 sec)
mysql> select * from student where name like "%老%";
+------+--------+------+------+-------+
| id | name | sex | age | score |
+------+--------+------+------+-------+
| 12 | 王老五 | 男 | 32 | 98 |
+------+--------+------+------+-------+
1 row in set (0.00 sec)
mysql> select * from student where name like "王%五";
+------+--------+------+------+-------+
| id | name | sex | age | score |
+------+--------+------+------+-------+
| 3 | 王五 | 女 | 17 | 98 |
| 12 | 王老五 | 男 | 32 | 98 |
+------+--------+------+------+-------+
2 rows in set (0.00 sec)
下划线通配符 _ ,一次只能匹配任意一个字符
mysql> select * from student where name like "王_五";
+------+--------+------+------+-------+
| id | name | sex | age | score |
+------+--------+------+------+-------+
| 12 | 王老五 | 男 | 32 | 98 |
+------+--------+------+------+-------+
1 row in set (0.00 sec)
mysql> insert into student
-> (id,name,sex,age,score)
-> values
-> (13,"王老老五","男",32,98);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student where name like "王_五";
+------+--------+------+------+-------+
| id | name | sex | age | score |
+------+--------+------+------+-------+
| 12 | 王老五 | 男 | 32 | 98 |
+------+--------+------+------+-------+
1 row in set (0.00 sec)
mysql> select * from student where name like "王__五";
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 13 | 王老老五 | 男 | 32 | 98 |
+------+----------+------+------+-------+
1 row in set (0.00 sec)
% _结合使用:
mysql> select * from student where name like "_老%";
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 12 | 王老五 | 男 | 32 | 98 |
| 13 | 王老老五 | 男 | 32 | 98 |
+------+----------+------+------+-------+
2 rows in set (0.00 sec)
查询空值
NULL和0不一样,跟空字符串不一样,NULL表示数据是未知的
查询成绩为空的
mysql> insert into student
-> (id,name,sex,age)
-> values
-> (14,"蜘蛛精","女",32);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
| 4 | 赵六 | 男 | 13 | 60 |
| 5 | 王八 | 男 | 21 | 58 |
| 6 | 徐象 | 男 | 21 | 97 |
| 7 | 八戒 | 男 | 13 | 80 |
| 8 | 悟空 | 男 | 21 | 58 |
| 9 | 沙僧 | 男 | 21 | 97 |
| 10 | 观音 | 女 | 100 | 70 |
| 11 | 王隔壁 | 男 | 30 | 30 |
| 12 | 王老五 | 男 | 32 | 98 |
| 13 | 王老老五 | 男 | 32 | 98 |
| 14 | 蜘蛛精 | 女 | 32 | NULL |
+------+----------+------+------+-------+
14 rows in set (0.00 sec)
mysql>
mysql> select * from student where score is null;
+------+--------+------+------+-------+
| id | name | sex | age | score |
+------+--------+------+------+-------+
| 14 | 蜘蛛精 | 女 | 32 | NULL |
+------+--------+------+------+-------+
1 row in set (0.00 sec)
查询非空值
is not null
查询成绩不为空的:
mysql> select * from student where score is not null;
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
| 4 | 赵六 | 男 | 13 | 60 |
| 5 | 王八 | 男 | 21 | 58 |
| 6 | 徐象 | 男 | 21 | 97 |
| 7 | 八戒 | 男 | 13 | 80 |
| 8 | 悟空 | 男 | 21 | 58 |
| 9 | 沙僧 | 男 | 21 | 97 |
| 10 | 观音 | 女 | 100 | 70 |
| 11 | 王隔壁 | 男 | 30 | 30 |
| 12 | 王老五 | 男 | 32 | 98 |
| 13 | 王老老五 | 男 | 32 | 98 |
+------+----------+------+------+-------+
13 rows in set (0.00 sec)
带and多条件查询(两个条件同时满足)
查询男生中及格人
mysql> select * from student where sex="男"and score >=60;
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 4 | 赵六 | 男 | 13 | 60 |
| 6 | 徐象 | 男 | 21 | 97 |
| 7 | 八戒 | 男 | 13 | 80 |
| 9 | 沙僧 | 男 | 21 | 97 |
| 12 | 王老五 | 男 | 32 | 98 |
| 13 | 王老老五 | 男 | 32 | 98 |
+------+----------+------+------+-------+
8 rows in set (0.01 sec)
并且年龄大于20岁
mysql> select * from student where sex="男"and score >=60 and age>20;
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 6 | 徐象 | 男 | 21 | 97 |
| 9 | 沙僧 | 男 | 21 | 97 |
| 12 | 王老五 | 男 | 32 | 98 |
| 13 | 王老老五 | 男 | 32 | 98 |
+------+----------+------+------+-------+
4 rows in set (0.00 sec)
带or的多条件查询
查询是男生或者及格的人
mysql> select * from student where sex="男" or score >=60;
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 1 | 张三 | 男 | 18 | 100 |
| 2 | 李四 | 男 | 19 | 99 |
| 3 | 王五 | 女 | 17 | 98 |
| 4 | 赵六 | 男 | 13 | 60 |
| 5 | 王八 | 男 | 21 | 58 |
| 6 | 徐象 | 男 | 21 | 97 |
| 7 | 八戒 | 男 | 13 | 80 |
| 8 | 悟空 | 男 | 21 | 58 |
| 9 | 沙僧 | 男 | 21 | 97 |
| 10 | 观音 | 女 | 100 | 70 |
| 11 | 王隔壁 | 男 | 30 | 30 |
| 12 | 王老五 | 男 | 32 | 98 |
| 13 | 王老老五 | 男 | 32 | 98 |
+------+----------+------+------+-------+
13 rows in set (0.00 sec)
消除重复记录
mysql> select distinct name from student;
+----------+
| name |
+----------+
| 张三 |
| 李四 |
| 王五 |
| 赵六 |
| 王八 |
| 徐象 |
| 八戒 |
| 悟空 |
| 沙僧 |
| 观音 |
| 王隔壁 |
| 王老五 |
| 王老老五 |
| 蜘蛛精 |
+----------+
14 rows in set (0.06 sec)
对查询结果进行排序
order by排序:
-
默认(升序)
mysql> select * from student order by score; +------+----------+------+------+-------+ | id | name | sex | age | score | +------+----------+------+------+-------+ | 14 | 蜘蛛精 | 女 | 32 | NULL | | 11 | 王隔壁 | 男 | 30 | 30 | | 14 | 王八 | 男 | 30 | 30 | | 5 | 王八 | 男 | 21 | 58 | | 8 | 悟空 | 男 | 21 | 58 | | 4 | 赵六 | 男 | 13 | 60 | | 10 | 观音 | 女 | 100 | 70 | | 7 | 八戒 | 男 | 13 | 80 | | 6 | 徐象 | 男 | 21 | 97 | | 9 | 沙僧 | 男 | 21 | 97 | | 3 | 王五 | 女 | 17 | 98 | | 12 | 王老五 | 男 | 32 | 98 | | 13 | 王老老五 | 男 | 32 | 98 | | 15 | 王老五 | 男 | 31 | 98 | | 2 | 李四 | 男 | 19 | 99 | | 1 | 张三 | 男 | 18 | 100 | +------+----------+------+------+-------+ 16 rows in set (0.00 sec)
-
ASC关键字(升序)
mysql> select * from student order by score asc; +------+----------+------+------+-------+ | id | name | sex | age | score | +------+----------+------+------+-------+ | 14 | 蜘蛛精 | 女 | 32 | NULL | | 11 | 王隔壁 | 男 | 30 | 30 | | 14 | 王八 | 男 | 30 | 30 | | 5 | 王八 | 男 | 21 | 58 | | 8 | 悟空 | 男 | 21 | 58 | | 4 | 赵六 | 男 | 13 | 60 | | 10 | 观音 | 女 | 100 | 70 | | 7 | 八戒 | 男 | 13 | 80 | | 6 | 徐象 | 男 | 21 | 97 | | 9 | 沙僧 | 男 | 21 | 97 | | 3 | 王五 | 女 | 17 | 98 | | 12 | 王老五 | 男 | 32 | 98 | | 13 | 王老老五 | 男 | 32 | 98 | | 15 | 王老五 | 男 | 31 | 98 | | 2 | 李四 | 男 | 19 | 99 | | 1 | 张三 | 男 | 18 | 100 | +------+----------+------+------+-------+ 16 rows in set (0.00 sec)
-
DESC关键字(降序)
mysql> select * from student order by score desc; +------+----------+------+------+-------+ | id | name | sex | age | score | +------+----------+------+------+-------+ | 1 | 张三 | 男 | 18 | 100 | | 2 | 李四 | 男 | 19 | 99 | | 3 | 王五 | 女 | 17 | 98 | | 12 | 王老五 | 男 | 32 | 98 | | 13 | 王老老五 | 男 | 32 | 98 | | 15 | 王老五 | 男 | 31 | 98 | | 6 | 徐象 | 男 | 21 | 97 | | 9 | 沙僧 | 男 | 21 | 97 | | 7 | 八戒 | 男 | 13 | 80 | | 10 | 观音 | 女 | 100 | 70 | | 4 | 赵六 | 男 | 13 | 60 | | 5 | 王八 | 男 | 21 | 58 | | 8 | 悟空 | 男 | 21 | 58 | | 11 | 王隔壁 | 男 | 30 | 30 | | 14 | 王八 | 男 | 30 | 30 | | 14 | 蜘蛛精 | 女 | 32 | NULL | +------+----------+------+------+-------+ 16 rows in set (0.00 sec)
对查询结果进行数量限制