常見(jiàn)的數(shù)據(jù)庫(kù)

當(dāng)前主流的數(shù)據(jù)庫(kù)系統(tǒng)包括關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS）和一些主要的NoSQL數(shù)據(jù)庫(kù)。以下是幾個(gè)主流的數(shù)據(jù)庫(kù)系統(tǒng)：

關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng) (RDBMS)

MySQL / MariaDB:（本文詳細(xì)介紹該數(shù)據(jù)庫(kù)的命令使用）
MySQL是一個(gè)流行的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，被廣泛應(yīng)用于Web應(yīng)用開(kāi)發(fā)中。
MariaDB是MySQL的一個(gè)分支，保持與MySQL高度兼容，并添加了一些新的特性。

PostgreSQL:
PostgreSQL是一個(gè)強(qiáng)大的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，以其功能豐富和可擴(kuò)展性而聞名。
它支持復(fù)雜的查詢、事務(wù)、觸發(fā)器等高級(jí)數(shù)據(jù)庫(kù)特性。

Oracle Database:
Oracle Database是一種商業(yè)的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，廣泛用于企業(yè)級(jí)應(yīng)用。
它以其高可用性、強(qiáng)大的管理功能和豐富的特性集合而著稱。

NoSQL非關(guān)系型數(shù)據(jù)庫(kù)

MongoDB:
MongoDB是一個(gè)流行的開(kāi)源NoSQL數(shù)據(jù)庫(kù)，采用文檔存儲(chǔ)模型，適合處理大量的非結(jié)構(gòu)化數(shù)據(jù)。
它支持高度靈活的數(shù)據(jù)模型和分布式部署。

Redis:
Redis是一個(gè)開(kāi)源的內(nèi)存數(shù)據(jù)結(jié)構(gòu)存儲(chǔ)系統(tǒng)，可以用作數(shù)據(jù)庫(kù)、緩存和消息代理。
它支持多種數(shù)據(jù)結(jié)構(gòu)（如字符串、哈希表、列表等），并提供高性能的讀寫(xiě)操作。

ubuntu2404安裝mariadb數(shù)據(jù)庫(kù)

apt install -y mariadb-server

systemctl enable --now mariadb

查看當(dāng)前用戶及其權(quán)限

查看用戶語(yǔ)法

SELECT user, host FROM mysql.user;

查看用戶權(quán)限語(yǔ)法

SHOW GRANTS FOR 'username'@'host';

MariaDB [(none)]> SELECT user, host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)MariaDB [(none)]> SHOW GRANTS FOR 'mysql'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mysql@localhost|
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'mysql'@'localhost' WITH GRANT OPTION|
+------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

創(chuàng)建用戶

語(yǔ)法格式

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username: 要?jiǎng)?chuàng)建的用戶名。
host: 允許訪問(wèn)數(shù)據(jù)庫(kù)的主機(jī)名或IP地址?？梢允褂猛ㄅ浞?% 表示任意主機(jī)，或者具體的IP地址或主機(jī)名。
password: 用戶的密碼。

MariaDB [(none)]> CREATE USER 'huhy'@'localhost' IDENTIFIED BY '000000';
Query OK, 0 rows affected (0.001 sec)MariaDB [(none)]> SELECT user, host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| huhy        | localhost |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)

授權(quán)用戶訪問(wèn)數(shù)據(jù)庫(kù)

語(yǔ)法格式

GRANT privileges ON database_name.table_name TO 'username'@'host';

privileges: 用戶需要的權(quán)限，例如 SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 等。
database_name.table_name: 數(shù)據(jù)庫(kù)和表名，可以使用通配符 * 表示所有數(shù)據(jù)庫(kù)或表。
username 和 host: 已創(chuàng)建用戶的用戶名和主機(jī)。

例：

GRANT SELECT, INSERT ON database1.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON database2.* TO 'user1'@'%';
GRANT DELETE ON database1.table1 TO 'user1'@'192.168.1.100';

賦予huhy用戶所有權(quán)限

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'huhy'@'localhost';
Query OK, 0 rows affected (0.001 sec)MariaDB [(none)]> SHOW GRANTS FOR 'huhy'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for huhy@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `huhy`@`localhost` IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

撤銷用戶權(quán)限

語(yǔ)法格式

REVOKE privileges ON database_name.table_name FROM 'username'@'host';

撤銷huhy所有權(quán)限

MariaDB [(none)]> REVOKE ALL PRIVILEGES ON *.* FROM 'huhy'@'localhost';
Query OK, 0 rows affected (0.001 sec)MariaDB [(none)]> SHOW GRANTS FOR 'huhy'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for huhy@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `huhy`@`localhost` IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)MariaDB [(none)]>

如果只需要撤銷單個(gè)權(quán)限；可按照如下

REVOKE SELECT, INSERT ON *.* FROM 'huhy'@'localhost';

修改用戶密碼

語(yǔ)法格式

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

修改用戶密碼為111111

MariaDB [(none)]> ALTER USER 'huhy'@'localhost' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.001 sec)

刪除用戶

語(yǔ)法格式

DROP USER 'username'@'host';

刪除huhy

MariaDB [(none)]> drop user 'huhy'@'localhost';
Query OK, 0 rows affected (0.001 sec)MariaDB [(none)]> SELECT user, host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
3 rows in set (0.001 sec)MariaDB [(none)]>

以下操作使用mysql數(shù)據(jù)庫(kù)

增

創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)

語(yǔ)法格式
create database 數(shù)據(jù)庫(kù)名;

mysql> create database test;
Query OK, 1 row affected (0.00 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studentdb          |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)mysql>

創(chuàng)建完后可以用show databases;查看所有數(shù)據(jù)庫(kù)，注意databases是復(fù)數(shù)形式的

創(chuàng)建表

創(chuàng)建表時(shí)注意要指定在那個(gè)數(shù)據(jù)庫(kù)下創(chuàng)建表，用use來(lái)選擇

創(chuàng)建表語(yǔ)法格式
create table 表名(字段名 數(shù)據(jù)類型 primary key,字段名 數(shù)據(jù)類型,字段名 數(shù)據(jù)類型);

primary key表示該字段不為空且值不能重復(fù)

mysql> use test;
Database changed
mysql> create table info(id int primary key,name varchar(255),age varchar(100));
Query OK, 0 rows affected (0.00 sec)mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql>

表中插入數(shù)據(jù)

語(yǔ)法格式
insert into 表名 (字段1,字段2,字段3) values (值1,值2,值3);

mysql> insert into info(id,name,age) values ("1","張三","19");
Query OK, 1 row affected (0.00 sec)mysql> select * from info;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 張三   | 19   |
+----+--------+------+
1 row in set (0.00 sec)mysql>

這里先提前學(xué)習(xí)一個(gè)查詢所有信息命令select * from info

表中添加字段（三種方式）

第一種方式，末尾添加字段
alter table 表名 add 新字段名 數(shù)據(jù)類型 約束條件(可選);

mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> alter table info add sex varchar(50);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
| sex   | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql>

第二種方式，開(kāi)頭創(chuàng)建字段
alter table 表名 add 新字段名 數(shù)據(jù)類型 約束條件(可選) first;

mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
| sex   | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> alter table info add num int(10) first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | int(10)      | YES  |     | NULL    |       |
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
| sex   | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)mysql>

第三種方式，中間添加字段
alter table 表名 add 新字段名 數(shù)據(jù)類型 約束條件(可選) after 已經(jīng)存在的字段名;

MySQL 除了允許在表的開(kāi)頭位置和末尾位置添加字段外，還允許在中間位置（指定的字段之后）添加字段，此時(shí)需要使用 after 關(guān)鍵字

mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | int(10)      | YES  |     | NULL    |       |
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
| sex   | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)mysql> alter table info add sno varchar(50) after name;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | int(10)      | YES  |     | NULL    |       |
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| sno   | varchar(50)  | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
| sex   | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql>

刪

刪除表記錄

語(yǔ)法格式如下
delete from 表名 where 字段 = 值;

注；如果不加上where條件語(yǔ)句的話，就會(huì)把整張表給刪除了

mysql> delete from info where id = 1;
Query OK, 1 row affected (0.00 sec)mysql> select * from info;
Empty set (0.00 sec)mysql>

刪除表字段

命令格式
alter table 表名 drop column 字段名;

mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> alter table info drop column age;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql>

刪除表（三種方式）

第一種方式；
drop table 表名;

刪除內(nèi)容和定義，刪除的是整個(gè)表（結(jié)構(gòu)和數(shù)據(jù)），將表所占用的空間全釋放掉。無(wú)法回滾，所以刪除是不能恢復(fù)的，如果再次使用的話需要新建表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)mysql> select * from info;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 張三   | 19   |
+----+--------+------+
1 row in set (0.00 sec)mysql> drop table info;
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
Empty set (0.00 sec)mysql>

第二種刪除方式
truncate table 表名;

只是清空表，刪除內(nèi)容，釋放空間，但不刪除定義（保留表的數(shù)據(jù)結(jié)構(gòu)）。且不會(huì)把刪除操作記錄記入日志保存，無(wú)法回滾，所以刪除是不能恢復(fù)的。并且在刪除的過(guò)程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)mysql> select * from info;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 張三   | 19   |
+----+--------+------+
1 row in set (0.00 sec)mysql> truncate table info;
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)mysql> select * from info;
Empty set (0.00 sec)mysql>

第三中刪除方式
delete from 表名;

刪除表中的行，不刪除表的結(jié)構(gòu)。執(zhí)行刪除的過(guò)程是每次從表中刪除一行，并且將該行的刪除操作作為事務(wù)在日志中保存，以便進(jìn)行進(jìn)行回滾操作。delete會(huì)根據(jù)指定的條件刪除表中滿足條件的數(shù)據(jù)，where就是條件判斷。如果不指定where子句，那么刪除表中所有記錄。delete操作不會(huì)減少表或索引所占用的空間,不推薦此方法刪除表

刪除數(shù)據(jù)庫(kù)

語(yǔ)法格式
drop database 數(shù)據(jù)庫(kù)名;

數(shù)據(jù)庫(kù)刪除之后，原來(lái)分配的空間將被收回。需要注意的是，數(shù)據(jù)庫(kù)刪除之后該數(shù)據(jù)庫(kù)中所有的表和數(shù)據(jù)都將被刪除。因此刪除數(shù)據(jù)庫(kù)要特別小心

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studentdb          |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)mysql> drop database test;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studentdb          |
| sys                |
+--------------------+
5 rows in set (0.00 sec)mysql>

改

修改表名

語(yǔ)法格式
alter table 舊表名 rename 新表名;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)mysql> alter table info rename new_info;
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| new_info       |
+----------------+
1 row in set (0.00 sec)mysql>

修改表數(shù)據(jù)

語(yǔ)法格式
update 表名 set 字段名 = ”新的值“ where 條件;

注意判斷的條件是否鎖定為修改修改的字段

mysql> select * from info;
+------+----+--------+------+------+------+
| num  | id | name   | sno  | age  | sex  |
+------+----+--------+------+------+------+
| NULL |  1 | 張三   | NULL | 19   | NULL |
+------+----+--------+------+------+------+
1 row in set (0.00 sec)mysql> update info set sex = "男" where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from info;
+------+----+--------+------+------+------+
| num  | id | name   | sno  | age  | sex  |
+------+----+--------+------+------+------+
| NULL |  1 | 張三   | NULL | 19   | 男   |
+------+----+--------+------+------+------+
1 row in set (0.00 sec)mysql>

修改表字段名

語(yǔ)法格式
alter table 表名 change 字段名 舊字段 新字段 新字段數(shù)據(jù)類型;

注；此方式可以修改字段名字的同時(shí)也可以修改字段數(shù)據(jù)類型，也可以指定為原來(lái)的字段類型

mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | int(10)      | YES  |     | NULL    |       |
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| sno   | varchar(50)  | YES  |     | NULL    |       |
| age   | varchar(100) | YES  |     | NULL    |       |
| sex   | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> alter table info change name new_name varchar(100);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| num      | int(10)      | YES  |     | NULL    |       |
| id       | int(11)      | NO   | PRI | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(100) | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql>

修改表字段數(shù)據(jù)類型

語(yǔ)法格式
alter table 表名 modify 字段名 數(shù)據(jù)類型(長(zhǎng)度);

mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| num      | int(10)      | YES  |     | NULL    |       |
| id       | int(11)      | NO   | PRI | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(100) | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> alter table info modify age varchar(10);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| num      | int(10)      | YES  |     | NULL    |       |
| id       | int(11)      | NO   | PRI | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(10)  | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql>

修改字段排列段順序

方式一，將指定字段放在開(kāi)頭
alter table 表名 modify 字段名 字段類型 first;

mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| num      | int(10)      | YES  |     | NULL    |       |
| id       | int(11)      | NO   | PRI | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(10)  | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> alter table info modify id int(11) first;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| num      | int(10)      | YES  |     | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(10)  | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql>

第二種方式，指定為某字段的后面
alter table 表名 modify 字段名 字段類型 after 字段名;

mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| num      | int(10)      | YES  |     | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(10)  | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> alter table info modify num int(10) after sex;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(10)  | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
| num      | int(10)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql>

拓展；如果字段的數(shù)據(jù)類型寫(xiě)錯(cuò)了也是會(huì)被修改的，并且還是會(huì)排序在指定字段的后面

mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(10)  | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
| num      | int(10)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> alter table info modify num int(100) after sex;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc info;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| new_name | varchar(100) | YES  |     | NULL    |       |
| sno      | varchar(50)  | YES  |     | NULL    |       |
| age      | varchar(10)  | YES  |     | NULL    |       |
| sex      | varchar(50)  | YES  |     | NULL    |       |
| num      | int(100)     | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

查

通配符和條件表達(dá)式

可參考菜鳥(niǎo)教程

order by排序

升序
select 字段1,字段2 from 表名 order by 排序字段 desc;

mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno       | sname     | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇      | 男   |   22 | cs    | NULL |
| 201215122 | 劉晨      | 女   |   20 | cs    | NULL |
| 201215123 | 王敏      | 女   |   18 | ma    | NULL |
| 201215124 | 張?jiān)铝?   | 女   |   20 | cs    | NULL |
| 201215125 | 張立      | 男   |   19 | is    | NULL |
| 201215126 | 李晚      | 男   |   21 | is    | NULL |
| 201215127 | 林方成    | 男   |   19 | cs    | NULL |
| 201215128 | 趙立何    | 男   |   21 | ma    | NULL |
| 201215129 | 趙城      | 男   |   20 | ma    | NULL |
| 201215130 | 張浩      | 男   |   20 | is    | NULL |
| 201215131 | 王信韻    | 女   |   19 | cs    | NULL |
| 201215132 | 孫思      | 女   |   21 | ma    | NULL |
| 201215133 | 陳信      | 女   |   22 | cs    | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)mysql> select sno,sname from student order by sno desc;
+-----------+-----------+
| sno       | sname     |
+-----------+-----------+
| 201215133 | 陳信      |
| 201215132 | 孫思      |
| 201215131 | 王信韻    |
| 201215130 | 張浩      |
| 201215129 | 趙城      |
| 201215128 | 趙立何    |
| 201215127 | 林方成    |
| 201215126 | 李晚      |
| 201215125 | 張立      |
| 201215124 | 張?jiān)铝?   |
| 201215123 | 王敏      |
| 201215122 | 劉晨      |
| 201215121 | 李勇      |
+-----------+-----------+
13 rows in set (0.00 sec)mysql>

降序
select 字段1,字段2 from 表名 order by 排序字段 asc;

mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno       | sname     | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇      | 男   |   22 | cs    | NULL |
| 201215122 | 劉晨      | 女   |   20 | cs    | NULL |
| 201215123 | 王敏      | 女   |   18 | ma    | NULL |
| 201215124 | 張?jiān)铝?   | 女   |   20 | cs    | NULL |
| 201215125 | 張立      | 男   |   19 | is    | NULL |
| 201215126 | 李晚      | 男   |   21 | is    | NULL |
| 201215127 | 林方成    | 男   |   19 | cs    | NULL |
| 201215128 | 趙立何    | 男   |   21 | ma    | NULL |
| 201215129 | 趙城      | 男   |   20 | ma    | NULL |
| 201215130 | 張浩      | 男   |   20 | is    | NULL |
| 201215131 | 王信韻    | 女   |   19 | cs    | NULL |
| 201215132 | 孫思      | 女   |   21 | ma    | NULL |
| 201215133 | 陳信      | 女   |   22 | cs    | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)mysql> select sno,sname from student order by sno asc;
+-----------+-----------+
| sno       | sname     |
+-----------+-----------+
| 201215121 | 李勇      |
| 201215122 | 劉晨      |
| 201215123 | 王敏      |
| 201215124 | 張?jiān)铝?   |
| 201215125 | 張立      |
| 201215126 | 李晚      |
| 201215127 | 林方成    |
| 201215128 | 趙立何    |
| 201215129 | 趙城      |
| 201215130 | 張浩      |
| 201215131 | 王信韻    |
| 201215132 | 孫思      |
| 201215133 | 陳信      |
+-----------+-----------+
13 rows in set (0.00 sec)mysql>