sql查询语句

0x00 基本语法

建立数据库

mysql> create database security;
Query OK, 1 row affected (0.00 sec)

查询所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| security           |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

使用数据库 新建一个表

mysql> use security;
Database changed
mysql> create table users (id int,username varchar(255),password varchar(255));
Query OK, 0 rows affected (0.09 sec)

查看数据库，插入，与查询

mysql> show tables;
+---------------------+
| Tables_in_security  |
+---------------------+
| users               |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into users (id,username,password) values (1,"admin","admin");
Query OK, 1 row affected (0.00 sec)

mysql> select * from admin;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | admin    |
+------+----------+----------+
1 row in set (0.00 sec)

查询 where 语句

mysql> select * from admin where id =1;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | admin    |
+------+----------+----------+
1 row in set (0.00 sec)

当where后面跟的条件变为逻辑运算式时

mysql> select * from users where id=1 or 1=1;//逻辑式的值为1，返回整表内容

mysql> select * from users where id=1 or 1=0;//条件值正确，返回对应条件值的行
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | 1234     |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> select * from users where id=1 and 1=0;//逻辑式的值为0，返回空表
Empty set (0.01 sec)

where后面可以跟模糊查询like

mysql> select * from users where password like 'adm%';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  8 | admin    | admin    |
|  9 | admin1   | admin1   |
+----+----------+----------+
5 rows in set (0.00 sec)

其中'%'代表匹配多个字符，'_'下划线代表匹配单个字符

范围查询

select * from students where id in(1,3,8);//查询id是1，3，8的
select * from students where (id between 3 and 8) and gender=1;//查询id在3到8的

order by 排序

select * from students order by 1;//按照第一个字段值进行排序