sql基本点

最基本操作

创建:create table customers(id int identity primary key not null, name varchar(15)not null); identity主键自增长

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

示例:

create table people (id int identity primary key ,姓名 char(50) not null,年龄 int not null,地址 char(50) not null)

select * from people

insert into people (姓名,年龄,地址) values('张三',15,'西安')

delete from people where id = 7

update people set 年龄 = 18 where id = 1

一、数据库

1.创建数据库

create database [if not exists] db_name [character set xxx] [collate xxx]

*创建一个名称为mydb1的数据库。

create database mydb1;

*创建一个使用utf8字符集的mydb2数据库。

create database mydb2 character set utf8;

*创建一个使用utf8字符集，并带校对规则的mydb3数据库。

create database mydb3 character set utf8 collate utf8_bin ;

2.查看数据库

show databases;查看所有数据库

show create database db_name; 查看数据库的创建方式

3.修改数据库

alter database db_name [character set xxx] [collate xxxx]

4.删除数据库

drop database [if exists] db_name;

5.使用数据库

切换数据库 use db_name;

查看当前使用的数据库 select database();

二、表

1.创建表

create table tab_name(

field1 type,

field2 type,

...

fieldn type

)[character set xxx][collate xxx];

****java和mysql的数据类型比较

String  ----------------------  char(n) varchar(n) 255  65535

byte short int long float double -------------- tinyint  smallint int bigint float double

boolean ------------------ bit 0/1

Date ------------------ Date、Time、DateTime、timestamp

FileInputStream FileReader  ------------------------------ Blob Text

*创建一个员工表employee

create table employee(

id int primary key auto_increment ,

name varchar(20),

gender bit default 1,

birthday date,

entry_date date,

job varchar(20),

salary double,

resume text

);

约束:

primary key

unique

not null

auto_increment 主键字段必须是数字类型。

2.查看表信息

desc tab_name 查看表结构

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

show create table tab_name 查看当前数据库表建表语句

3.修改表结构

(1)增加一列

alter table tab_name add [column] 列名 类型;

(2)修改一列类型

alter table tab_name modify 列名 类型;

(3)修改列名

alter table tab_name change [column] 列名 新列名 类型;

(4)删除一列

alter table tab_name drop [column] 列名;

(5)修改表名

rename table 表名 to 新表名;

(6)修该表所用的字符集

alter table student character set utf8;

4.删除表

drop table tab_name;

三、表操作

1.insert into（增）

insert into tab_name (field1,filed2,.......) values (value1,value2,.......);

insert into tab_name values(value1,value2,......);

*插入的数据应与字段的数据类型相同。

*数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

*在values中列出的数据位置必须与被加入的列的排列位置相对应。

*字符和日期型数据应包含在单引号中'zhang' '2013-04-20'

*插入空值：不指定某列的值或insert into table value(null)，则该列取空值。

*如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表insert into tab_name values(value1,value2,......);

*示例

insert into emp (name,birthday,entry_date,job,salary) values ('张飞','1990-09-09','2000-01-01','打手',999);

insert into emp values (7,'黄忠',1,'1970-05-05','2001-01-01','战神',1000,null);

2.update（改）

update tab_name set field1=value1,field2=value2,......[where 语句]

*UPDATE语法可以用新值更新原有表行中的各列。

*SET子句指示要修改哪些列和要给予哪些值。

*WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

*示例：

将所有员工薪水修改为5000元。

update emp set salary=5000;

将姓名为’zs’的员工薪水修改为3000元。

update emp set salary=3000 where name='zs';

将姓名为’ls’的员工薪水修改为4000元,job改为ccc。

update emp set salary=4000,job='ccc' where name='zs';

将wu的薪水在原有基础上增加1000元。

update emp set salar=salary+4000 where name='wu';

3.delete（删）

delete from tab_name [where ....]

*如果不跟where语句则删除整张表中的数据

*delete只能用来删除一行记录，不能只删除一行记录中的某一列值（这是update操作）。

*delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop

*同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

*TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在事务中恢复。

*示例：

删除表中名称为’zs’的记录。

delete from emp where name='黄忠';

删除表中所有记录。

delete from emp;

使用truncate删除表中记录。

truncate table emp;

4.select（查）

（1）select [distinct] *|field1，field2，......  from tab_name 其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列列表明确指定要查找的列，distinct用来剔除重复行。

*示例：

查询表中所有学生的信息。

select * from exam;

查询表中所有学生的姓名和对应的英语成绩。

select name,english from exam;

过滤表中重复数据。

select distinct english from exam;

（2）select 也可以使用表达式，并且可以使用 as 别名

在所有学生分数上加10分特长分显示。

select name,english+10,chinese+10,math+10 from exam;

统计每个学生的总分。

select name,english+chinese+math from exam;

使用别名表示学生总分。

select name,english+chinese+math as 总成绩 from exam;

select name,english+chinese+math 总成绩 from exam;

select name english from exam;

（3）使用where子句，进行过滤查询。

*示例：

查询姓名为XXX的学生成绩

select * from exam where name='张飞';

查询英语成绩大于90分的同学

select name,english from exam where english>90;

查询总分大于200分的所有同学

select name,math+english+chinese as 总成绩 from exam where math+english+chinese>200 ;

*where字句中可以使用：

*比较运算符：

> < >= <= <> 

between 10 and 20 值在10到20之间 

in(10,20,3)值是10或20或30

like '张pattern' pattern可以是%或者_，如果是%则表示任意多字符，此例中张三丰 张飞 张abcd ，如果是_则表示一个字符张_，张飞符合。张

Is null

*逻辑运算符

在多个条件直接可以使用逻辑运算符 and or not

*示例

查询英语分数在 80－100之间的同学。

select name ,english from exam where english between 80 and 100;

查询数学分数为75,76,77的同学。

select name ,math from exam where math in (75,76,77);

查询所有姓张的学生成绩。

select * from exam where name like '张%';

查询数学分>70，语文分>80的同学。

select name from exam where math>70 and chinese >80;

查找缺考数学的学生的姓名

select name from exam where math is null;

（4）Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。

Asc 升序、Desc 降序，其中asc为默认值

ORDER BY 子句应位于SELECT语句的结尾。

*示例：

对数学成绩排序后输出。

select * from exam order by math;

对总分排序按从高到低的顺序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam order by 总成绩 desc;

对姓张的学生成绩排序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam where name like '张%' order by 总成绩 desc;

（5）聚合函数：技巧,先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。

count(列名)

统计一个班级共有多少学生？先查出所有的学生，再用count包上

select count(*) from exam;

统计数学成绩大于70的学生有多少个？

select count(math) from exam where math>70;

统计总分大于250的人数有多少？

select count(name) from exam where (ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))>250;

sum(列名)

统计一个班级数学总成绩？先查出所有的数学成绩，再用sum包上

select sum(math) from exam;

统计一个班级语文、英语、数学各科的总成绩

select sum(math),sum(english),sum(chinese) from exam;

统计一个班级语文、英语、数学的成绩总和

select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 总成绩 from exam;

统计一个班级语文成绩平均分

select sum(chinese)/count(*) from exam ;

注意：sum仅对数值起作用，否则会报错。

AVG(列名)：

IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。

IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

求一个班级数学平均分？先查出所有的数学分，然后用avg包上。

select avg(ifnull(math,0)) from exam;

求一个班级总分平均分

select avg((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam ;

Max、Min

求班级最高分和最低分（数值范围在统计中特别有用）

select Max((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;

select Min((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;

（6）group by字句，其后可以接多个列名，也可以跟having子句对group by 的结果进行筛选。

练习：对订单表中商品归类后，显示每一类商品的总价

select product，sum(price) from orders group by product;

练习：查询购买了几类商品，并且每类总价大于100的商品

select product，sum(price) from orders group by product having sum(price)>100;

!~having 和 where 的差别： where语句用在分组之前的筛选，having用在分组之后的筛选，having中可以用合计函数，where中就不行。使用where的地方可以用having替代。

示例:查询商品列表中除了橘子以外的商品，每一类商品的总价格大于500元的商品的名字

select product,sum(price) from orders where product<>'桔子'group by product having sum(price)>500;

（7）重点：Select from where group by having order by

Mysql在执行sql语句时的执行顺序：from where select group by having order by

*分析：

select math+english+chinese as 总成绩 from exam where 总成绩 >250; ---- 不成功

select math+english+chinese as 总成绩 from exam having 总成绩 >250; --- 成功

select math+english+chinese as 总成绩 from exam group by 总成绩 having 总成绩 >250; ----成功

select  math+english+chinese as 总成绩 from exam order by 总成绩;----成功

select * from exam as 成绩 where 成绩.math>85; ---- 成功

四、约束

1.创建表时指定约束：

create table tb(

id int primary key auto_increment,

name varchar(20) unique not null,

ref_id int,

foreign key(ref_id) references tb2(id)

);

create table tb2(

id int primary key auto_increment

);

2.外键约束：

（1）增加外键：

可以明确指定外键的名称，如果不指定外键的名称,mysql会自动为你创建一个外键名称。

RESTRICT : 只要本表格里面有指向主表的数据， 在主表里面就无法删除相关记录。

CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。

alter table book add [constraint FK_BOOK] foreign key(pubid) references pub_com(id) [on delete restrict] [on update restrict];

（2）删除外键

alter table 表名 drop foreign key 外键（区分大小写，外键名可以desc 表名查看）;

3.主键约束：

（1）增加主键（自动增长，只有主键可以自动增长）

Alter table tb add primary key(id) [auto_increment];

（2）删除主键

alter table 表名 drop primary key

（3）增加自动增长

Alter table employee modify id int auto_increment;

（4）删除自动增长

Alter table tb modify id int;

五、多表设计

一对一（311教室和20130405班级，两方都是一）:在任意一方保存另一方的主键

一对多、多对一（班级和学生，其中班级为1，学生为多）：在多的一方保存一的一方的主键

多对多（教师和学生，两方都是多）：使用中间表，保存对应关系

六、多表查询

create table tb (id int primary key,name varchar(20) );

create table ta (

id int primary key,

name varchar(20),

tb_id int

);

insert into tb values(1,'财务部');

insert into tb values(2,'人事部');

insert into tb values(3,'科技部');

insert into ta values (1,'刘备',1);

insert into ta values (2,'关羽',2);

insert into ta values (3,'张飞',3);

mysql> select * from ta;

+----+------+-------+

| id | name | tb_id |

+----+------+-------+

|  1 | aaa  |    1 |

|  2 | bbb  |    2 |

|  3 | bbb  |    4 |

+----+------+-------+

mysql> select * from tb;

+----+------+

| id | name |

+----+------+

|  1 | xxx  |

|  2 | yyy  |

|  3 | yyy  |

+----+------+

1.笛卡尔积查询：两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录，其中很多错误数据

select * from ta ,tb;

mysql> select * from ta ,tb;

+----+------+-------+----+------+

| id | name | tb_id | id | name |

+----+------+-------+----+------+

|  1 | aaa  |    1 |  1 | xxx  |

|  2 | bbb  |    2 |  1 | xxx  |

|  3 | bbb  |    4 |  1 | xxx  |

|  1 | aaa  |    1 |  2 | yyy  |

|  2 | bbb  |    2 |  2 | yyy  |

|  3 | bbb  |    4 |  2 | yyy  |

|  1 | aaa  |    1 |  3 | yyy  |

|  2 | bbb  |    2 |  3 | yyy  |

|  3 | bbb  |    4 |  3 | yyy  |

+----+------+-------+----+------+

2.内连接：查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

select * from ta ,tb where ta.tb_id = tb.id;

select * from ta inner join tb on ta.tb_id = tb.id;

mysql> select * from ta inner join tb on ta.tb_id = tb.id;

+----+------+-------+----+------+

| id | name | tb_id | id | name |

+----+------+-------+----+------+

|  1 | aaa  |    1 |  1 | xxx  |

|  2 | bbb  |    2 |  2 | yyy  |

+----+------+-------+----+------+

3.外连接

（1）左外连接：在内连接的基础上增加左边有右边没有的结果

select * from ta left join tb on ta.tb_id = tb.id;

mysql> select * from ta left join tb on ta.tb_id = tb.id;

+----+------+-------+------+------+

| id | name | tb_id | id  | name |

+----+------+-------+------+------+

|  1 | aaa  |    1 |    1 | xxx  |

|  2 | bbb  |    2 |    2 | yyy  |

|  3 | bbb  |    4 | NULL | NULL |

+----+------+-------+------+------+

（2）右外连接：在内连接的基础上增加右边有左边没有的结果

select * from ta right join tb on ta.tb_id = tb.id;

mysql> select * from ta right join tb on ta.tb_id = tb.id;

+------+------+-------+----+------+

| id  | name | tb_id | id | name |

+------+------+-------+----+------+

|    1 | aaa  |    1 |  1 | xxx  |

|    2 | bbb  |    2 |  2 | yyy  |

| NULL | NULL |  NULL |  3 | yyy  |

+------+------+-------+----+------+

（3）全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

select * from ta full join tb on ta.tb_id = tb.id; --mysql不支持全外连接

select * from ta left join tb on ta.tb_id = tb.id

union

select * from ta right join tb on ta.tb_id = tb.id;

mysql> select * from ta left join tb on ta.tb_id = tb.id

    -> union

    -> select * from ta right join tb on ta.tb_id = tb.id; --mysql可以使用此种方式间接实现全外连接

+------+------+-------+------+------+

| id  | name | tb_id | id  | name |

+------+------+-------+------+------+

|    1 | aaa  |    1 |    1 | xxx  |

|    2 | bbb  |    2 |    2 | yyy  |

|    3 | bbb  |    4 | NULL | NULL |

| NULL | NULL |  NULL |    3 | yyy  |

+------+------+-------+------+------+

七、分页查询

1，针对于SQLServer

1）

SELECT TOP 页大小 * FROM B_USER  WHERE (  id NOT IN  (SELECT TOP ((页码-1)*页大小) id  FROM B_USER ORDER BY id)) ORDER BY ID

select top 10 * from B_USER order by id 查询前十条数据（1—10）

select top 10 * from B_USER where id not in(select top 10 id from B_USER order by id) order by id 查询前十条数据（11—20）

select top 10 * from B_USER where id not in(select top 20 id from B_USER order by id) order by id 查询前十条数据（21—30）

可用，主键id可以是任意类型

2）

SELECT TOP 页大小 *  FROM (  SELECT  ROW_NUMBER() OVER (ORDER BY id) AS RowNo  FROM B_USER  ) AS A  WHERE RowNo> " + (页码-1)*页大小

select top 10 *

from

(

select row_number() over(order by id) as rownumber,* from B_USER

) A

where rownumber > 10

可用，主键id可以是任意类型

3）

select top 页大小 * from table1 where id> (select isnull(max(id),0) from (select top ((页码-1)*页大小) id from table1 order by id) as T  ) order by id

select top 10 * from B_USER

where id >

(

    select isnull(max(id),0)

    from

       (

      select top 20 id from B_USER order by id

        ) A

)

order by id

可用，主键id可以是任意类型

4）

Select top 10 * from test where id>10

可用，主键id可以只能是int型

2，MySQL

MySQL数据库实现分页比较简单，提供了 LIMIT函数。一般只需要直接写到sql语句后面就行了。

LIMIT子句可以用来限制由SELECT语句返回过来的数据数量，它有一个或两个参数，如果给出两个参数，第一个参数指定返回的第一行在所有数据中的位置，从0开始（注意不是1），第二个参数指定最多返回行数。

例如：

select * from table WHERE … LIMIT 10; #返回前10行

select * from table WHERE … LIMIT 0,10; #返回前10行

select * from table WHERE … LIMIT 10,20; #返回第10-20行数据 

八，模糊查询

正常数据库的SQL查询语句：

select * from [Project].[dbo].[InstallInfo_Gj] where CarCode like '5%'

Android中（SQLlite）模糊查询的使用方法

Cursor c = db.rawQuery("SELECT carnum FROM CarNum where carnum like ?", new String[] { key+"%" }); 这条语句是正确的

Cursor c = db.rawQuery("SELECT carnum FROM CarNum where carnum like ?%", new String[] { key }); 这条是错误的

九，使用MyBatis时的分页查询以及多参数传递

1，对于传递多参数，以Map的形式

<!-- 在MyBatis的select、insert、update、delete这些元素中都提到了parameterType这个属性。MyBatis现在可以使用的parameterType有基本数据类型和JAVA复杂数据类型

基本数据类型：包含int,String,Date等。基本数据类型作为传参，只能传入一个。通过#{参数名} 即可获取传入的值

复杂数据类型：包含JAVA实体类、Map。通过#{属性名}或#{map的KeyName}即可获取传入的值

-->

参考与上面的解说，使用#{map的KeyName}即可获取传入的值；

示例：

<select id="SelectRadioBymap" parameterType="java.util.Map" resultType="_RadioBean">

select * from radio where id > #{id} and type = #{type}

</select>

//传递多参数，以Map的形式

@Test

public void testSelectRadioBymap() {

SqlSession sqlSession = getSqlSession();

Map<String, Object> paramMap = new HashMap<>();

paramMap.put("id", 5);

paramMap.put("type", "computer");

List<RadioBean> c = sqlSession.selectList("com.examination.mapping.RadioBeanMapper.SelectRadioBymap", paramMap);

System.out.println(c);

}

注意，这里使用的是Map<String, Object>，故而可以传递所有类型的参数约束。

当然，对于传递多参数约束也可以使用JavaBean，只不过为了传递参数而专门建个bean，有点浪费

2，对于分页

当SQL语句中使用top时，在该关键字的后面如果跟着参数，这是将不能使用#而应该使用$,当然对于其他的参数调用则只能使用#，如果使用$则会报错

//分页查询

@Test

public void testSelectRadioBypage() {

SqlSession sqlSession = getSqlSession();

Map<String, Object> paramMap = new HashMap<>();

paramMap.put("page", 2);

paramMap.put("size", 10);

List<RadioBean> c = sqlSession.selectList("com.examination.mapping.RadioBeanMapper.SelectRadioBypage", paramMap);

System.out.println(c);

}

<!-- select top 10 * from radio where id > (select isnull(max(id),0) from (select top ((2-1)*10) id from radio order by id) as T ) order by id -->

<select id="SelectRadioBypage" parameterType="java.util.Map" resultType="_RadioBean">

select top ${size} * from radio where id > (select isnull(max(id),0) from (select top ((${page}-1)*${size}) id from radio order by id) as T ) order by id 

</select>

完整示例：

Mapper：

<mapper namespace="com.examination.mapping.RadioBeanMapper">

<select id="SelectRadioByid" parameterType="String" resultType="_RadioBean">

select * from radio where id = #{id}

</select>

<select id="SelectRadioall" resultType="_RadioBean">

select * from radio

</select>

<!-- select top 10 * from radio where id > (select isnull(max(id),0) from (select top ((2-1)*10) id from radio order by id) as T ) order by id -->

<select id="SelectRadioBypage" parameterType="java.util.Map" resultType="_RadioBean">

select top ${size} * from radio where id > (select isnull(max(id),0) from (select top ((${page}-1)*${size}) id from radio order by id) as T ) order by id 

</select>

<select id="SelectRadioBymap" parameterType="java.util.Map" resultType="_RadioBean">

select * from radio where id > #{id} and type = #{type}

</select>

</mapper>

java：

//获取SqlSession

public SqlSession getSqlSession(){

//mybatis的配置文件

        String resource = "mybatisConfig.xml";

        //使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）

        InputStream is = MyBatisTest.class.getClassLoader().getResourceAsStream(resource);

        //构建sqlSession的工厂

        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);

        //使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）

        //Reader reader = Resources.getResourceAsReader(resource);

        //构建sqlSession的工厂

        //SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

        //创建能执行映射文件中sql的sqlSession

        SqlSession session = sessionFactory.openSession();

        return session;

}

//根据id查询

@Test

public void testSelectRadioByid() {

SqlSession sqlSession = getSqlSession();

RadioBean c = sqlSession.selectOne("com.examination.mapping.RadioBeanMapper.SelectRadioByid", "1");

System.out.println(c);

}

//查询全部

@Test

public void testSelectRadioall() {

SqlSession sqlSession = getSqlSession();

List<RadioBean> c = sqlSession.selectList("com.examination.mapping.RadioBeanMapper.SelectRadioall");

System.out.println(c);

}

//分页查询

@Test

public void testSelectRadioBypage() {

SqlSession sqlSession = getSqlSession();

Map<String, Object> paramMap = new HashMap<>();

paramMap.put("page", 2);

paramMap.put("size", 10);

List<RadioBean> c = sqlSession.selectList("com.examination.mapping.RadioBeanMapper.SelectRadioBypage", paramMap);

System.out.println(c);

}

//传递多参数，以Map的形式

@Test

public void testSelectRadioBymap() {

SqlSession sqlSession = getSqlSession();

Map<String, Object> paramMap = new HashMap<>();

paramMap.put("id", 5);

paramMap.put("type", "computer");

List<RadioBean> c = sqlSession.selectList("com.examination.mapping.RadioBeanMapper.SelectRadioBymap", paramMap);

System.out.println(c);

}

//where中>和in

select * from B_USER where create_date > '2016-03-27 21:30:31.103'

UPDATE b_user set amount = 0 WHERE id IN ('16cb318719314418af80947ee88173f4',

'1dbfe1dccdb74148b1f6cbfc866beb47','e1655a888d424e3a87767abb15e72a0a')