SQL学习一

落日映苍穹つ 2022-08-13 10:29 98阅读 0赞

sql大小写不敏感

select：

select  \* from 表名

select 列 from 表名

distinct ：

select distinct 列 from 表名

where:

select 列 from 表名 where 列 操作符 值

where子句中使用

<table style="margin:10px 0px 0px; padding:0px; border:1px solid rgb(170,170,170); border-collapse:collapse; width:709px; color:rgb(0,0,0); font-family:Verdana,Arial,宋体; font-size:12px; background-color:rgb(249,249,249)"> 
 <tbody style="margin:0px; padding:0px; border:0px"> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> 操作符</th> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> 描述</th> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> =</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 等于</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> &lt;&gt;</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 不等于</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> &gt;</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 大于</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> &lt;</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 小于</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> &gt;=</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 大于等于</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> &lt;=</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 小于等于</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> BETWEEN</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 在某个范围内</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> LIKE</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 搜索某种模式</td> 
  </tr> 
 </tbody> 
</table>

select \* from persons where city  = 'bj‘

SQL 使用单引号来环绕**文本值**（大部分数据库系统也接受[双引号][Link 1]）。如果是**数值**，不要使用引号。

between and:

select distinct need from type where id between 10 and 15（包含情况视数据库而定）

select distinct need from type where id not between 10 and 15

select distinct need from type where name between 'abd' and 'c'

like:

select distinct need from type where name like '%d'(d结尾）

select distinct need from type where name like 'd%'(d开头）

select distinct need from type where name like '%d%'(包含d）

select distinct need from type where name not like '%d%'(包含d）

order by:

select distinct need from type order by id desc

select distinct need from type order by id asc,name desc(在第一列中有相同的值时，第二列是以升序排列的。如果第一列中有些值为 nulls 时，情况也是这样的。)

insert into:

insert into 表名 values(值1，值2，...)

insert into 表名 (列1，列2，...）values(值1，值2，...)

update：  
语句用于修改表中的数据。

update 表名 set 列名 = 新值，列名 = 新值 where 列名 = 某值

delete:

语句用于删除表中的行。

delete from 表名 where 列名 = 值

delete from 表名 （删除所有行）

Top:

用于规定要返回的记录的数目。

select 列名 from 表名 limit 数目

通配符：

SQL 通配符必须与 LIKE 运算符一起使用。

<table style="margin:10px 0px 0px; padding:0px; border:1px solid rgb(170,170,170); border-collapse:collapse; width:709px; color:rgb(0,0,0); font-family:Verdana,Arial,宋体; font-size:12px; background-color:rgb(249,249,249)"> 
 <tbody style="margin:0px; padding:0px; border:0px"> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> 通配符</th> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> 描述</th> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> %</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 替代一个或多个字符</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> _</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 仅替代一个字符</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> [charlist]</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 字符列中的任何单一字符</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; border:0px; line-height:18px"> [^charlist]</p> <p style="margin-top:12px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; border:0px; line-height:18px"> 或者</p> <p style="margin-top:12px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; border:0px; line-height:18px"> [!charlist]</p> </td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> 不在字符列中的任何单一字符</td> 
  </tr> 
 </tbody> 
</table>

select \* from persons where city like 'n%'

select \* from persons where city like '%n%'

select \* from persons where city like '\_nv'

select \* from persons where city like '\_nv\_n\_d'

in:

操作符允许我们在 WHERE 子句中规定多个值。

select \* from persons where city in ('a','b')

alter table:

语句用于在已有的表中添加、修改或删除列。

alter table 表名 add 列名 数据类型

alter table 表名 drop column 列名

function: