SQL配置及基本操作

打开服务:service sql start

进入数据库:mysql -u root -p

查看数据库:show databases;

进入数据库:use 库名

查看当前数据库中所有表：show tables;

查看字段名：desc 表名；

查询：select 字段名(uid,username,password) from 数据库名.数据表名;

查询指定字段的值：select * from 数据库名.数据表名 where 字段=><值(字符型的要用‘’)

select SCHEMA_NAME from schemata;

schemata表中SCHEMA_NAME字段存放的是数据库名称

查询语句

1.检索数据

从a表中检索b,c,d的列：

select b,c,d（所有列*） from a;

从1表中检索2的列的不同值：

select distinct 2（所有列*） from 1;

（使用distinct关键字必须放在列前面）

从1表中检索m列的前n行的值 (不同的DBMS有不同的语法) ：

Mysql,MariaDB,PostgreSQL:select m（所有列*） from 1 limit n;

DB2:select m from 1 fetch first n rows only;

Oracle:select m from 1 where rownum <=n;

从1表中检索m的列从a行开始的b行的值：

select m from 1 limit b offset a;

注释：--注释内容；#注释内容；/*内容*/(多行)

2.排序检索数据

升序排列某一列数据：

select 列 from 表 order by 列；（order by为最后的字句）

升序排列多列(a表中的吗m,n列先按m排序后按n排序)：

select m,n from a order by m,n（1,2）;

降序排列多列(a表中的吗m,n列先按m排序后按n排序)：

select m,n from a order by m desc,n desc;

3.过滤数据

Where子句操作符：

=等于；<>,!=不等于；<小于；>大于; >=大于等于; !>不大于; !<不小于; between 。。。。and在指定的两个值之间; is null为null值;

条件限定查询：

select m,n from a where 限定条件 order by 排序规则；

例：select name,id from cityone where id>=5 order by id desc,name;

4.高级数据过滤

And过滤(1,2条件都得满足)：优先级高于or

select 列 from 表 where 条件1 and 条件2；

例：检索id大于5且name值不为aa的数据

select m,n from a where id>a and name!=‘aa’;

or 过滤（1,2条件满足其一即可）：

select 列 from 表 where 条件1 or 条件2；

优先级易错点：

Select m,n from a where name=1 or name=2 and age>10;

由于and的优先级大于or,所以此语句的意思是检索出name值为2并且age大于10或者name为1 的数据；

若想检索name为1,2且age大于10的sql语句为：

Select m,n from a where (name=1 or name=2)and age>10;(（）优先级最高)

In过滤：

Select m,n from a where m in (a,b) order by n;

检索a表中m列中名为a,b的值输出对应的n列的值，相当于or语句：

Select m,n from a where m=a or m=b order by n;

Not过滤：

Select m,n from a where not m=’aa’order by n,m;

检索出除m=aa以外的值

5.用通配符进行过滤

%通配符（%表示任意字符出现任意次数）：

Select m,n from a where n like ‘a%’;

检索出n列中以a开头的所有值；

_通配符（一个_只匹配一个字符）：

Select m,n from a where n like ‘_a’;

[]通配符:

Select m,n from a where n like ‘[ab]%’;

检索以a或b开头的所有数据；

Select m,n from a where n like ‘[^ab]%’;

检索除了以a或b开头的所有数据；

6.创建计算字段

拼接字段：

Select m + ‘(’+ n + ‘)’ from a ;

或Select m || ‘(’n || ‘)’ from a;

消除空格：select rtrim(m) + ‘(’+ n + ‘)’ from a ;

Select m + ‘(’+ n + ‘)’as aa from;(设置别名为aa,||同上)

Select concat(m,‘(’n,‘)’) from a;(mysql mariadb)

Select concat(m,‘(’n,‘)’) as aa from a;(mysql mariadb别名设置)

列与列之间的运算(运算符*-+/)：

Select m,n,m*n as sum from a;

7.使用函数处理数据

大(小)写转换函数：upper（），lower()

Select m,upper(m) as n from a;

检索字符串左（右）边的n个字符：left（列，n），right（列，n）

Select left(列,n) from a;

返回字符串长度：length()

Select m,length(m) from a;

去掉字符串左（右）边的空格：ltrim(),rtrim()

Select m,ltrim(m) from a;

检索发音相似：soundex()

Select m,n from a where soundex(m)=soundex(‘字符串’)；

数值处理函数：

绝对值Abs(); 余弦cos(); 正弦sin(); 指数值exp();

圆周率pi(); 平方根sqrt(); 正切tan()

8.汇总数据

求列平均值：avg()

Select avg(m) from a where name=‘n’；

求m列name的值为n的平均值

计算列的行数：count()

Select count(*) as b from a;

返回列中最大（小）值：max(),min()

Select max(m) from a;

求指定列的和：sum()

Select sum(m) as sum from a;

只包含不同的值，指定distinct:(上面四种函数都适用)

Select sum(distinct m) as n from a;

9.分组数据

创建分组：group by

Selct m,count(*) from a group by m;

分组表示m列的行数；

过滤分组+排序：having

Select m,count(*) from a group by m having count(*)>=2 order by m;

10.使用子查询

利用子查询进行过滤：

Cityone 表中存放的是用户id及姓名；people 表存放的是用户的年龄。

根据id查名字，再根据名字查年龄：

Select name from cityone where id=2;查出name=bb

Select age from people where name in(‘bb’);

相当于子查询的：Select age from people where name in(select name from cityone where id=2);

作为计算字段使用子查询：

Cityone 表中存放的有用户姓名；people表存放的也有用户的姓名。

利用子查询查出people表中的name在cityone中出现的次数：

Select name,(select count(*) from cityone where cityone.name=people.name) as sum from people;

11.联结表

创建联结：返回多个表中的数据

Select m,n from a,b where a.id=b.id;

其中m列在a表中，n列在b表中

内联结：

Select m,n from a inner join b on a.id=b.id;(查询效果同上)

联结多个表：

Select m,n,p from a,b,c where a.id=b.id and b.id=c.id;

12.创建高级联结

使用表别名：(oracle中不用as直接用表名别名)

Select m,n,p from name as a,age as e,high as h where a.id=e.id and e.id=h.id;

自联结

这是利用子查询查找b表中id 为2的name，在a表中查出年龄：

Select name,age from a where name=(select name from b where id=2);

这是使用自联结：

Select name,age from aaaa as a,bbbb as b where a.name=b.name and b.id=2;

外联结：left或right指的是outer join左右的表

Select name,age from aaaa left outer join bbbb on aaaa.id=bbbb.id and bbbb.name=’cc’;

与内联结相反，外联结是显示除了name=’cc’以外的信息，left是指从bbbb左边选择所有行；

13.组合查询

创建组合查询:默认是合并重复的行的，若要全部显示用union all

Select m,p from a where n in (‘aa’,‘bb’);

Select m,p from b where q=‘cc’;

相当于：Select m from a where n in (‘aa’,‘bb’) union Select p from b where q=‘cc’order by m;(排序只能在最后使用一个order by)

14.插入数据

插入行：

不够安全的写法：Insert into 表名 values(各个列的值)；

安全的写法：insert into 表名（列名） values(对应前面列的值)；

插入检索出的数据：

Insert into a(m,n,p,q) select m,n,p,q from b;

从一个表复制到另一个表：

Select * into 新表名 from 原表名；

或

Create table 新表名 as select * from 原表名；

15.更新和删除数据

更新数据：

Update 表名 set 列名=更改值,列名=更改值 where 限制条件；

例：update a set m=’aa’，n=’bb’ where id=1;

删除数据：删除整行

Delete from 表名 where 限制条件；

例： delete from a where id=3;

16.创建和操纵表

创建表：default为设置默认值，可选

Create table 表名（列名数据类型 not null或null default 默认值）；

例： create table a(m int not null,n varchar null);mysql中varchar改为text

删除列，添加列：

alter table 表名 add column 列名 varchar(30);

alter table 表名 drop column 列名;

删除表：

Drop table 表名；

Mysql重命名表名：

Rename table 旧表名 to 新表名；

17.创建视图

创建视图：

Create view 视图名 as select m,n from a,b,where a.id=b.id

使用视图查询：

Select name from 视图名 where id=1;

使用视图格式化检索的结果：

Create view 视图名 as select concat(name,’(’,age,’)’) as b from a;

Select * from 视图名；

删除视图：

Drop view 视图名；

18.管理事务处理

Sql server:

Begin transaction

Sql语句

Save transaction delete1； //设置保留点delete1

If …… //判断条件

Rollback transaction delete1; //回退到保留点delete1

Commit transaction

例：begin transaction

Delete m where name=’aa’