mysql数据库入门手册

常见的数据库

当前主流的数据库系统包括关系型数据库管理系统（RDBMS）和一些主要的NoSQL数据库。以下是几个主流的数据库系统：

关系型数据库管理系统 (RDBMS)

MySQL / MariaDB:（本文详细介绍该数据库的命令使用）
MySQL是一个流行的开源关系型数据库管理系统，被广泛应用于Web应用开发中。
MariaDB是MySQL的一个分支，保持与MySQL高度兼容，并添加了一些新的特性。

PostgreSQL:
PostgreSQL是一个强大的开源关系型数据库管理系统，以其功能丰富和可扩展性而闻名。
它支持复杂的查询、事务、触发器等高级数据库特性。

Oracle Database:
Oracle Database是一种商业的关系型数据库管理系统，广泛用于企业级应用。
它以其高可用性、强大的管理功能和丰富的特性集合而著称。

NoSQL非关系型数据库

MongoDB:
MongoDB是一个流行的开源NoSQL数据库，采用文档存储模型，适合处理大量的非结构化数据。
它支持高度灵活的数据模型和分布式部署。

Redis:
Redis是一个开源的内存数据结构存储系统，可以用作数据库、缓存和消息代理。
它支持多种数据结构（如字符串、哈希表、列表等），并提供高性能的读写操作。

ubuntu2404安装mariadb数据库

apt install -y mariadb-server

systemctl enable --now mariadb

查看当前用户及其权限

查看用户语法

SELECT user, host FROM mysql.user;

查看用户权限语法

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)

创建用户

语法格式

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

username: 要创建的用户名。
host: 允许访问数据库的主机名或IP地址。可以使用通配符 % 表示任意主机，或者具体的IP地址或主机名。
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)

授权用户访问数据库

语法格式

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

privileges: 用户需要的权限，例如 SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 等。
database_name.table_name: 数据库和表名，可以使用通配符 * 表示所有数据库或表。
username 和 host: 已创建用户的用户名和主机。

例：

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用户所有权限

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)

撤销用户权限

语法格式

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

撤销huhy所有权限

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)]>

如果只需要撤销单个权限；可按照如下

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

修改用户密码

语法格式

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)

删除用户

语法格式

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数据库

增

创建一个数据库

语法格式
create database 数据库名;

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>

创建完后可以用show databases;查看所有数据库，注意databases是复数形式的

创建表

创建表时注意要指定在那个数据库下创建表，用use来选择

创建表语法格式
create table 表名(字段名 数据类型 primary key,字段名 数据类型,字段名 数据类型);

primary key表示该字段不为空且值不能重复

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>

表中插入数据

语法格式
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>

这里先提前学习一个查询所有信息命令select * from info

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

第一种方式，末尾添加字段
alter table 表名 add 新字段名 数据类型 约束条件(可选);

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: 0

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 表名 add 新字段名 数据类型 约束条件(可选) 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: 0

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 表名 add 新字段名 数据类型 约束条件(可选) after 已经存在的字段名;

MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 after 关键字

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: 0

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>

删

删除表记录

语法格式如下
delete from 表名 where 字段 = 值;

注；如果不加上where条件语句的话，就会把整张表给删除了

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: 0

mysql> 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 表名;

删除内容和定义，删除的是整个表（结构和数据），将表所占用的空间全释放掉。无法回滚，所以删除是不能恢复的，如果再次使用的话需要新建表

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 表名;

只是清空表，删除内容，释放空间，但不删除定义（保留表的数据结构）。且不会把删除操作记录记入日志保存，无法回滚，所以删除是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

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 表名;

删除表中的行，不删除表的结构。执行删除的过程是每次从表中删除一行，并且将该行的删除操作作为事务在日志中保存，以便进行进行回滚操作。delete会根据指定的条件删除表中满足条件的数据，where就是条件判断。如果不指定where子句，那么删除表中所有记录。delete操作不会减少表或索引所占用的空间,不推荐此方法删除表

删除数据库

语法格式
drop database 数据库名;

数据库删除之后，原来分配的空间将被收回。需要注意的是，数据库删除之后该数据库中所有的表和数据都将被删除。因此删除数据库要特别小心

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>

改

修改表名

语法格式
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>

修改表数据

语法格式
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: 0

mysql> select * from info;
+------+----+--------+------+------+------+
| num  | id | name   | sno  | age  | sex  |
+------+----+--------+------+------+------+
| NULL |  1 | 张三   | NULL | 19   | 男   |
+------+----+--------+------+------+------+
1 row in set (0.00 sec)

mysql>

修改表字段名

语法格式
alter table 表名 change 字段名 旧字段 新字段 新字段数据类型;

注；此方式可以修改字段名字的同时也可以修改字段数据类型，也可以指定为原来的字段类型

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: 0

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 表名 modify 字段名 数据类型(长度);

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: 0

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 表名 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: 0

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 表名 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: 0

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>

拓展；如果字段的数据类型写错了也是会被修改的，并且还是会排序在指定字段的后面

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: 0

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(100)     | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

查

通配符和条件表达式

可参考菜鸟教程

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 | 张月琳    | 女   |   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 | 张月琳    |
| 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 | 张月琳    | 女   |   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 | 张月琳    |
| 201215125 | 张立      |
| 201215126 | 李晚      |
| 201215127 | 林方成    |
| 201215128 | 赵立何    |
| 201215129 | 赵城      |
| 201215130 | 张浩      |
| 201215131 | 王信韵    |
| 201215132 | 孙思      |
| 201215133 | 陈信      |
+-----------+-----------+
13 rows in set (0.00 sec)

mysql>