一、数据库的字符集编码设置
utf-8 utf8mb4
1.查看数据库默认的字符集
MariaDB [(none)]> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
用户提交数据时的字符集
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
2.永久设置字符集
服务端配置文件配置字符集
[root@c7-100 ~]# vim /etc/my.cnf
...
[mysqld]
character-set-server=utf8mb4
...
客户端配置文件配置字符集
[root@c7-100 ~]# vim /etc/my.cnf.d/client.cnf
...
[client]
default-character-set=utf8mb4
...
[client-mariadb]
default-character-set=utf8mb4
重启数据库服务
[root@c7-100 ~]# systemctl restart mariadb.service
验证是否设置成功
MariaDB [(none)]> create database a2;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database a2;
+----------+----------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------+
| a2 | CREATE DATABASE `a2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
查看系统中有哪些字符集
MariaDB [(none)]> show charset;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
二、数据库的基础操作
1.操作数据库
查看当前数据库列表
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | //默认的数据库：系统运行的状态信息，性能信息的存储库
| mysql | //默认的数据库：授权权限，用户管理的数据库
| a1 |
| a2 |
| performance_schema | //默认的数据库：系统运行的状态信息，性能信息的存储库
| test | //测试库，让用户先测试使用的；
+--------------------+
6 rows in set (0.00 sec)
查看指定的库
MariaDB [(none)]> show databases like "%a%";
+---------------------+
| Database (%a%) |
+---------------------+
| a1 |
| a2 |
+---------------------+
2 rows in set (0.00 sec)
查看创建数据库时的语句信息
MariaDB [(none)]> show create database a2;
+----------+----------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------+
| a2 | CREATE DATABASE `a2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
2.创建数据库
1.创建数据库使用默认的字符集
MariaDB [(none)]> create database a3;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| a1 |
| a2 |
| a3 |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
2.创建数据库指定字符集
MariaDB [(none)]> create database a4 character set utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database a4;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| a4 | CREATE DATABASE `a4` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
3.切换数据库
切换数据库
MariaDB [(none)]> use a1;
Database changed
MariaDB [a1]>
查看当前所在的数据库
MariaDB [a1]> select database();
+------------+
| database() |
+------------+
| a1 |
+------------+
1 row in set (0.00 sec)
4.删除数据库
MariaDB [a1]> drop database a1;
Query OK, 0 rows affected (0.05 sec)
MariaDB [(none)]>
三、数据库语句分类
1.DDL 数据定义语句
定义：管理数据库中的基础数据语句：增删表、增删库、增删索引、增删用户；
MariaDB [(none)]> ? data definition;
You asked for help about help category: "Data Definition"
For more information, type 'help <item>', where <item> is one of the following
topics:
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
CONSTRAINT
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP SERVER
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP VIEW
MERGE
RENAME TABLE
TRUNCATE TABLE2.DCL数据控制语句
定义：定义数据的访问权限，和安全级别的语句
MariaDB [(none)]> ? account management;
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
3.DML数据操作语句
定义：主要针对数据库当中的数据表进行操作，用来操作数据的语句；(增删改查数据)
-insert //表中插入数据
-delete //删除表中的数据
-update //修改表中的数据
-select //查询数据表中的数据
MariaDB [(none)]> ? data manipulation;
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
CALL
DELETE
DO
DUAL
HANDLER
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
REPLACE
SELECT
UNION
UPDATE
4.DQL数据查询语句
select #数据库最难的学习点，就是查询数据信息；
四、数据表的操作
1.创建数据表
语法: create table 自定义表名(
字段 数据类型，
字段 数据类型，
. . .
字段 数据类型
)；
创建数据表
MariaDB [(none)]> use a1;
Database changed
MariaDB [a1]> create table user(
-> id int(10),
-> name varchar(20),
-> age tinyint(3),
-> sex varchar(3)
-> );
Query OK, 0 rows affected (0.01 sec)
查看表结构
MariaDB [a1]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
数据类型介绍
整型
tinyint //整形（小） 1byte=8bit==256 【-128~127】
int //整型（大） 4byte=32bit==（40亿+）
big int //整型（超大）8byte=64bit==（老大了）
字符类型
varchar //变长 varchar（3）
= abc存储abc
=a 存储 a
=abcd 存储 abc （把最后一个去掉）
char //定长 char（3）
= abc 存储 abc
= a 存储 a + 空格 +空格
= abcd 存储 abc （）MariaDB [a1]> create table a1( id int(10) not null auto_increment,
-> name varchar(20) not null,
-> age tinyint(3) not null default '0',
-> sex varchar(3) default null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [a1]> desc a1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) | NO | | 0 | |
| sex | varchar(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
not null //不能为空
default '值' //设置默认值
auto_increment //自增
primary key(字段) //设置主键（唯一值）2.修改表名
MariaDB [a1]> show tables;
+--------------+
| Tables_in_a1 |
+--------------+
| a1 |
| user |
+--------------+
2 rows in set (0.00 sec)
MariaDB [a1]> rename table a1 to qwq;
Query OK, 0 rows affected (0.01 sec)
MariaDB [a1]> show tables;
+--------------+
| Tables_in_a1 |
+--------------+
| qwq |
| user |
+--------------+
2 rows in set (0.00 sec)
3.删表
MariaDB [a1]> show tables;
+--------------+
| Tables_in_a1 |
+--------------+
| qwq |
| user |
+--------------+
2 rows in set (0.00 sec)
MariaDB [a1]> drop table qwq;
Query OK, 0 rows affected (0.00 sec)
MariaDB [a1]> show tables;
+--------------+
| Tables_in_a1 |
+--------------+
| user |
+--------------+
1 row in set (0.00 sec)4.查看表
查看表的字段结构
MariaDB [a1]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
查看数据库下的所有数据表
MariaDB [a1]> show tables;
+--------------+
| Tables_in_a1 |
+--------------+
| user |
+--------------+
1 row in set (0.00 sec)
筛选数据库下的表
MariaDB [a1]> show tables like "%s%";
+--------------------+
| Tables_in_a1 (%s%) |
+--------------------+
| user |
+--------------------+
1 row in set (0.00 sec)
五、数据的操作
1.表中新增字段
语法 【alter table 表名 add 新增字段名 新增数据类型 [not null] [comment '注释'] [first/after + 列]】
1.插入字段最后一列
MariaDB [a1]> alter table user add hight int(3) not null comment '注释';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [a1]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| hight | int(3) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2.插入字段到第一列
MariaDB [a1]> alter table user add weight int(3) not null first;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [a1]> desc user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| weight | int(3) | NO | | NULL | |
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| hight | int(3) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
3.指定列后面插入字段
MariaDB [a1]> alter table user add marry varchar(3) not null default 'no' after sex;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [a1]> desc user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| weight | int(3) | NO | | NULL | |
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| marry | varchar(3) | NO | | no | |
| hight | int(3) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
2.表中插入数据insert
语法1【insert into 表名(字段1，字段2，字段3...) value(字段1插入的值，字段2插入的值，字段3插入的值...);】
语法2【insert into 表名 value(字段1插入的值，字段2插入的值，字段3插入的值...);】
1.语法1插入方式
MariaDB [a1]> insert into user(id,name,age,sex,marry) value(1,'awa',22,'男','yes');
Query OK, 1 row affected, 2 warnings (0.00 sec)
MariaDB [a1]> select * from user;
+--------+------+------+------+------+-------+-------+
| weight | id | name | age | sex | marry | hight |
+--------+------+------+------+------+-------+-------+
| 0 | 1 | awa | 22 | 男 | yes | 0 |
+--------+------+------+------+------+-------+-------+
1 row in set (0.00 sec)
MariaDB [a1]> insert into user(name,age,sex,marry) value('awa2',33,'男','yes'),('awa3',44,'女','no');
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2
MariaDB [a1]> select * from user;
+--------+------+------+------+------+-------+-------+
| weight | id | name | age | sex | marry | hight |
+--------+------+------+------+------+-------+-------+
| 0 | 1 | awa | 22 | 男 | yes | 0 |
| 0 | 2 | awa2 | 33 | 男 | yes | 0 |
| 0 | 3 | awa3 | 44 | 女 | no | 0 |
+--------+------+------+------+------+-------+-------+
3 rows in set (0.00 sec)
2.语法2插入方式
MariaDB [a1]> insert into user value(123,4,'awa4',45,'男','yes',123),(111,5,'awa5',14,'女','no',110);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [a1]> select * from user;
+--------+------+------+------+------+-------+-------+
| weight | id | name | age | sex | marry | hight |
+--------+------+------+------+------+-------+-------+
| 0 | 1 | awa | 22 | 男 | yes | 0 |
| 0 | 2 | awa2 | 33 | 男 | yes | 0 |
| 0 | 3 | awa3 | 44 | 女 | no | 0 |
| 123 | 4 | awa4 | 45 | 男 | yes | 123 |
| 111 | 5 | awa5 | 14 | 女 | no | 110 |
+--------+------+------+------+------+-------+-------+
5 rows in set (0.00 sec)
3.删除数据
语法：【delete from 表名 where 字段名=数据】
MariaDB [a1]> select * from user;
+--------+------+------+------+------+-------+-------+
| weight | id | name | age | sex | marry | hight |
+--------+------+------+------+------+-------+-------+
| 0 | 1 | awa | 22 | 男 | yes | 0 |
| 0 | 2 | awa2 | 33 | 男 | yes | 0 |
| 0 | 3 | awa3 | 44 | 女 | no | 0 |
| 123 | 4 | awa4 | 45 | 男 | yes | 123 |
| 111 | 5 | awa5 | 14 | 女 | no | 110 |
+--------+------+------+------+------+-------+-------+
5 rows in set (0.00 sec)
MariaDB [a1]> delete from user where id=5;
Query OK, 1 row affected (0.00 sec)
MariaDB [a1]> select * from user;
+--------+------+------+------+------+-------+-------+
| weight | id | name | age | sex | marry | hight |
+--------+------+------+------+------+-------+-------+
| 0 | 1 | awa | 22 | 男 | yes | 0 |
| 0 | 2 | awa2 | 33 | 男 | yes | 0 |
| 0 | 3 | awa3 | 44 | 女 | no | 0 |
| 123 | 4 | awa4 | 45 | 男 | yes | 123 |
+--------+------+------+------+------+-------+-------+
4 rows in set (0.00 sec)
注意：删除数据之前，一定要按照删除条件，查一遍，二次确认
4.修改数据内容
语法【update 表名 set 字段='新值'，... where 条件】
MariaDB [a1]> select * from user where marry='no';
+--------+------+------+------+------+-------+-------+
| weight | id | name | age | sex | marry | hight |
+--------+------+------+------+------+-------+-------+
| 0 | 3 | awa3 | 44 | 女 | no | 0 |
+--------+------+------+------+------+-------+-------+
1 row in set (0.00 sec)
MariaDB [a1]> update user set marry='no' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [a1]> select * from user where marry='no';
+--------+------+------+------+------+-------+-------+
| weight | id | name | age | sex | marry | hight |
+--------+------+------+------+------+-------+-------+
| 0 | 3 | awa3 | 44 | 女 | no | 0 |
| 123 | 4 | awa4 | 45 | 男 | no | 123 |
+--------+------+------+------+------+-------+-------+
2 rows in set (0.00 sec)
5.查询数据内容
准备环境
导入“世界”数据库
1.上传sql文件到本地
[root@c7-100 test]# rz -E
rz waiting to receive.
[root@c7-100 test]# ll
总用量 392
-rw-r--r-- 1 root root 397334 8月 12 15:45 world.sql
2.将文件导入数据库中
第一种方式
[root@c7-100 test]# mysql -uroot -p1 < ./world.sql
[root@c7-100 test]# mysql -uroot -p1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| a1 |
| performance_schema |
| world |
+--------------------+
第二种方式
MariaDB [(none)]> source ./world.sql;
3.切换到world数据库下
use world
MariaDB [world]> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
查看城市表中多少条数据
count() 统计、计数
MariaDB [world]> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
查看city表字段
MariaDB [world]> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
查看城市表的前十条数据
limit n //前n行
limit 从第几行显示（从0行开始），显示几行
MariaDB [world]> select * from city limit 10;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 10 | Tilburg | NLD | Noord-Brabant | 193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.00 sec)
--------------------------------------------------------------------------
MariaDB [world]> select * from city limit 0,1;
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
MariaDB [world]> select * from city limit 0,3;
+----+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
+----+----------+-------------+----------+------------+
3 rows in set (0.00 sec)
MariaDB [world]> select * from city limit 10,3;
+----+-----------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+---------------+------------+
| 11 | Groningen | NLD | Groningen | 172701 |
| 12 | Breda | NLD | Noord-Brabant | 160398 |
| 13 | Apeldoorn | NLD | Gelderland | 153491 |
+----+-----------+-------------+---------------+------------+
3 rows in set (0.00 sec)等值查询
MariaDB [world]> select * from city where CountryCode='CHN';
+------+---------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+----------------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 1894 | Wuhan | CHN | Hubei | 4344600 |
| 1895 | Harbin | CHN | Heilongjiang | 4289800 |
| 1896 | Shenyang | CHN | Liaoning | 4265200 |
| 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 |
| 1898 | Chengdu | CHN | Sichuan | 3361500 |
| 1899 | Nanking [Nanjing] | CHN | Jiangsu | 2870300 |
| 1900 | Changchun | CHN | Jilin | 2812000 |
....
MariaDB [world]> select * from city where CountryCode='CHN' limit 10;
+------+--------------------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------------+-------------+--------------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 1894 | Wuhan | CHN | Hubei | 4344600 |
| 1895 | Harbin | CHN | Heilongjiang | 4289800 |
| 1896 | Shenyang | CHN | Liaoning | 4265200 |
| 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 |
| 1898 | Chengdu | CHN | Sichuan | 3361500 |
| 1899 | Nanking [Nanjing] | CHN | Jiangsu | 2870300 |
+------+--------------------+-------------+--------------+------------+
10 rows in set (0.00 sec)
MariaDB [world]> select name from city where CountryCode='CHN' limit 10;
+--------------------+
| name |
+--------------------+
| Shanghai |
| Peking |
| Chongqing |
| Tianjin |
| Wuhan |
| Harbin |
| Shenyang |
| Kanton [Guangzhou] |
| Chengdu |
| Nanking [Nanjing] |
+--------------------+
10 rows in set (0.00 sec)
MariaDB [world]> select name,countrycode from city where CountryCode='CHN' limit 10;
+--------------------+-------------+
| name | countrycode |
+--------------------+-------------+
| Shanghai | CHN |
| Peking | CHN |
| Chongqing | CHN |
| Tianjin | CHN |
| Wuhan | CHN |
| Harbin | CHN |
| Shenyang | CHN |
| Kanton [Guangzhou] | CHN |
| Chengdu | CHN |
| Nanking [Nanjing] | CHN |
+--------------------+-------------+
10 rows in set (0.00 sec)
区间查询
区间符号：
> //大于号
>= //大于等于
< //小于
<= //小于等于
!= //不等于查询人口数小于等于700的城市
MariaDB [world]> select * from city where population <=700;
+------+---------------------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+-------------+------------+
| 62 | The Valley | AIA | – | 595 |
| 1791 | Flying Fish Cove | CXR | – | 700 |
| 2316 | Bantam | CCK | Home Island | 503 |
| 2317 | West Island | CCK | West Island | 167 |
| 2728 | Yaren | NRU | – | 559 |
| 2805 | Alofi | NIU | – | 682 |
| 2912 | Adamstown | PCN | – | 42 |
| 3333 | Fakaofo | TKL | Fakaofo | 300 |
| 3538 | Città del Vaticano | VAT | – | 455 |
+------+---------------------+-------------+-------------+------------+
9 rows in set (0.00 sec)
查询人口数大于7000000的城市
MariaDB [world]> select * from city where population >7000000;
+------+-------------------+-------------+------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------------+-------------+------------------+------------+
| 206 | São Paulo | BRA | São Paulo | 9968485 |
| 456 | London | GBR | England | 7285000 |
| 939 | Jakarta | IDN | Jakarta Raya | 9604900 |
| 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 |
| 1025 | Delhi | IND | Delhi | 7206704 |
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 2331 | Seoul | KOR | Seoul | 9981619 |
| 2515 | Ciudad de México | MEX | Distrito Federal | 8591309 |
| 2822 | Karachi | PAK | Sindh | 9269265 |
| 3357 | Istanbul | TUR | Istanbul | 8787958 |
| 3580 | Moscow | RUS | Moscow (City) | 8389200 |
| 3793 | New York | USA | New York | 8008278 |
+------+-------------------+-------------+------------------+------------+
14 rows in set (0.00 sec)
逻辑查询
逻辑符号：
and //和，并且
or //或者查询，中国大于500w人口的城市
MariaDB [world]> select * from city where population >5000000 and countrycode='CHN';
+------+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
+------+-----------+-------------+-----------+------------+
4 rows in set (0.00 sec)
查询 美国大于100w人口的城市
MariaDB [world]> select * from city where population >1000000 and countrycode='USA';
+------+--------------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------+-------------+--------------+------------+
| 3793 | New York | USA | New York | 8008278 |
| 3794 | Los Angeles | USA | California | 3694820 |
| 3795 | Chicago | USA | Illinois | 2896016 |
| 3796 | Houston | USA | Texas | 1953631 |
| 3797 | Philadelphia | USA | Pennsylvania | 1517550 |
| 3798 | Phoenix | USA | Arizona | 1321045 |
| 3799 | San Diego | USA | California | 1223400 |
| 3800 | Dallas | USA | Texas | 1188580 |
| 3801 | San Antonio | USA | Texas | 1144646 |
+------+--------------+-------------+--------------+------------+查询 大于700w人口 或者 日本的城市
MariaDB [world]> select * from city where population >7000000 or countrycode='JPN' limit 10;
+------+---------------------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+--------------+------------+
| 206 | São Paulo | BRA | São Paulo | 9968485 |
| 456 | London | GBR | England | 7285000 |
| 939 | Jakarta | IDN | Jakarta Raya | 9604900 |
| 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 |
| 1025 | Delhi | IND | Delhi | 7206704 |
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 1533 | Jokohama [Yokohama] | JPN | Kanagawa | 3339594 |
| 1534 | Osaka | JPN | Osaka | 2595674 |
| 1535 | Nagoya | JPN | Aichi | 2154376 |
| 1536 | Sapporo | JPN | Hokkaido | 1790886 |
+------+---------------------+-------------+--------------+------------+
10 rows in set (0.00 sec)
模糊查询
查询以【J】开头的国家
MariaDB [world]> select * from city where countrycode like 'J%';
+------+---------------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+---------------+------------+
| 1529 | Spanish Town | JAM | St. Catherine | 110379 |
| 1530 | Kingston | JAM | St. Andrew | 103962 |
| 1531 | Portmore | JAM | St. Andrew | 99799 |
| 1786 | Amman | JOR | Amman | 1000000 |
| 1787 | al-Zarqa | JOR | al-Zarqa | 389815 |
| 1788 | Irbid | JOR | Irbid | 231511 |
| 1789 | al-Rusayfa | JOR | al-Zarqa | 137247 |
| 1790 | Wadi al-Sir | JOR | Amman | 89104 |
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 1533 | Jokohama [Yokohama] | JPN | Kanagawa | 3339594 |
| 1534 | Osaka | JPN | Osaka | 2595674 |
| 1535 | Nagoya | JPN | Aichi | 2154376 |
| 1536 | Sapporo | JPN | Hokkaido | 1790886 |
| 1537 | Kioto | JPN | Kyoto | 1461974 |
...查询 以【N】结尾的国家
MariaDB [world]> select * from city where countrycode like '%N';
+------+------------------------+-------------+------------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------------+-------------+------------------------+------------+
| 186 | Cotonou | BEN | Atlantique | 536827 |
| 187 | Porto-Novo | BEN | Ouémé | 194000 |
| 188 | Djougou | BEN | Atacora | 134099 |
| 189 | Parakou | BEN | Borgou | 103577 |
| 192 | Thimphu | BTN | Thimphu | 22000 |
| 538 | Bandar Seri Begawan | BRN | Brunei and Muara | 21484 |
| 926 | Conakry | GIN | Conakry | 1090610 |
| 939 | Jakarta | IDN | Jakarta Raya | 9604900 |
| 940 | Surabaya | IDN | East Java | 2663820 |
| 941 | Bandung | IDN | West Java | 2429000 |
| 942 | Medan | IDN | Sumatera Utara | 1843919 |
| 943 | Palembang | IDN | Sumatera Selatan | 1222764 |
| 944 | Tangerang | IDN | West Java | 1198300 |
...查询以【J】开头以【N】结尾的国家
MariaDB [world]> select * from city where countrycode like 'J%N';
+------+---------------------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+-----------+------------+
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 1533 | Jokohama [Yokohama] | JPN | Kanagawa | 3339594 |
| 1534 | Osaka | JPN | Osaka | 2595674 |
| 1535 | Nagoya | JPN | Aichi | 2154376 |
| 1536 | Sapporo | JPN | Hokkaido | 1790886 |
| 1537 | Kioto | JPN | Kyoto | 1461974 |
...查询 国家含有【P】的国家城市
MariaDB [world]> select * from city where countrycode like '%P%';
+------+---------------------------------+-------------+----------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------------------+-------------+----------------------+------------+
| 653 | Madrid | ESP | Madrid | 2879052 |
| 654 | Barcelona | ESP | Katalonia | 1503451 |
| 655 | Valencia | ESP | Valencia | 739412 |
| 656 | Sevilla | ESP | Andalusia | 701927 |
| 657 | Zaragoza | ESP | Aragonia | 603367 |
| 658 | Málaga | ESP | Andalusia | 530553 |
| 659 | Bilbao | ESP | Baskimaa | 357589 |
| 660 | Las Palmas de Gran Canaria | ESP | Canary Islands | 354757 |
....多条件查询
查询 人口在100w和200w之间的城市
MariaDB [world]> select * from city where population > 1000000 and population < 2000000;查询进阶
in #在...里
not in #不在...里
between and #在...之间（数值区间）
MariaDB [world]> select * from city where countrycode in ('CHN','USA');
MariaDB [world]> select * from city where countrycode not in ('CHN','USA');
MariaDB [world]> select * from city where population between 1000000 and 2000000;
592
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
