MySQL数据库CRUD语句飞快入门,使用Jorm轻松的增加

2019-06-21 作者:数据库   |   浏览(162)

MySql连接查询精解

MySQL数据库CRUD语句快速入门

> 准备
以MySQL为例,执行下面的sql建立数据表
CREATE TABLE `t_user` (               
        `id` int(11) NOT NULL,              
        `name` varchar(50) DEFAULT NULL,    
        `sex` char(4) DEFAULT NULL,         
        `age` int(11) DEFAULT NULL,         
        `career` varchar(100) DEFAULT NULL, 
        PRIMARY KEY (`id`)                  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

> 引入jar或maven依赖,需要jar包
gerald-jorm-1.0.5.jar 最新版本下载:
commons-logging-1.1.1.jar
log4j-1.2.14.jar
mysql-connector-java-5.1.6.jar
javassist-3.11.0.GA.jar 或 cglib-nodep-2.2.2.jar (根据实际情况选择性加入)

出于对知识的总结和分享,对常见的查询做了练习和总结。

SQL语句

> 配置文件
在你的java工程的classpath下建立config.properties和jdbc.cfg.xml文件
config.properties内容:
# 下面路径可以根据实际情况指定,为相对classpath的路径地址
jdbc.config.path=jdbc.cfg.xml

数据准备,包括两个表,tuser和ttable。tuser和ttable为1对多关系。

sql语句,一种用于操作数据库的语言

jdbc.cfg.xml内容:
<?xml version='1.0' encoding="UTF-8"?>
<jdbc-configuration>

tuser表结构如下:

 

  <constant name="show_sql" value="true" />
  <constant name="jdbc.batch_size" value="600" />
  <constant name="bytecode.provider" value="cglib" />
 
  <connections default="simple">
 
    <connection name="simple">
      <property name="connection.implementation">org.javaclub.jorm.jdbc.connection.impl.SimpleConnection</property>
      <property name="connection.dialect">MySQLDialect</property>
      <property name="connection.driver">com.mysql.jdbc.Driver</property>
      <property name="connection.jdbcurl">jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8</property>
      <property name="connection.database">test</property>
      <property name="connection.username">root</property>
      <property name="connection.password">root</property>
    </connection>

名称  www.2cto.com  

数据库, 简单地理解为硬盘上的文件, 可以存放数据

    <connection name="c3p0">
      <property name="connection.implementation">org.javaclub.jorm.jdbc.connection.impl.PooledConnection</property>
      <property name="connection.dialect">MySQLDialect</property>
      <property name="connection.driver">com.mysql.jdbc.Driver</property>
      <property name="connection.jdbcurl">jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8</property>
      <property name="connection.database">test</property>
      <property name="connection.username">root</property>
      <property name="connection.password">root</property>
      <property name="connection.pool.min">1</property>
      <property name="connection.pool.max">8</property>
      <property name="connection.test.sql">select 1</property>
    </connection>
   
  </connections>

类型

 

</jdbc-configuration>

备注

sql 语句大致可以分类两大类

> 实体类User.java
@PK(value = "id")
@Entity(table="t_user")
public class User {
   
    @Id
    private int id;

id                                                                       

 

    private String name;

Integer                                                     

进入数据库的方法:  www.2cto.com  

    private String sex;

主键                                                         

 

    private Integer age;

name

  1. 命令行代码:

    private String career;
   
    @NoColumn
    private int kvalue;
   
    public User() {
        super();
    }

Varchar(50)

 

    public User(String name, String sex, Integer age, String career) {
        super();
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.career = career;
    }

用户名称

进入数据库C:mysql -hlocalhost -P3306 -uroot -p123456

    public User(Integer id, String name, String sex, Integer age, String career) {
        super();
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.career = career;
    }

password

 

    public int getId() {
        return id;
    }

Varchar(50)

启动数据库 net start mysql

    public void setId(int id) {
        this.id = id;
    }

用户密码

 

    public String getName() {
        return name;
    }

Sex

停止数据库net stop mysql

    public void setName(String name) {
        this.name = name;
    }

Varchar(20)

 

    public String getSex() {
        return sex;
    }

用户性别

一、针对数据库database和表table的操作

    public void setSex(String sex) {
        this.sex = sex;
    }

 

1、database和table的常见操作

    public Integer getAge() {
        return age;
    }

ttable表机构如下:

创建create

    public void setAge(Integer age) {
        this.age = age;
    }

名称

 

    public String getCareer() {
        return career;
    }

类型

查看show

    public void setCareer(String career) {
        this.career = career;
    }

备注

 

    public int getKvalue() {
        return kvalue;
    }

id                                                                       

修改alter

    public void setKvalue(int kvalue) {
        this.kvalue = kvalue;
    }

Integer                                                     

 

    public String toString() {
        StringBuffer sb = new StringBuffer();
        sb.append("[" id ", " name ", " sex ", " age ", " career "]");
        return sb.toString();
    }

主键                                                          

删除drop

}

name

 

这里数据库字段和java实体类User的属性在命名上是一致的,如果不一致,比如如果表创建sql为:
CREATE TABLE `t_user` (               
        `user_id` int(11) NOT NULL,              
        `user_name` varchar(50) DEFAULT NULL,    
        `sex` char(4) DEFAULT NULL,         
        `col_age` int(11) DEFAULT NULL,         
        `career_job` varchar(100) DEFAULT NULL, 
        PRIMARY KEY (`id`)                  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Varchar(50)

// 创建一个数据库

那么对应的实体User应该写成:
@PK(value = "id")
@Entity(table="t_user")
public class User {
   
    @Id
    @Column("user_id")
    private int id;

名称

 

    @Column("user_name")
    private String name;
       
    // 与数据库字段命名一致,可以不指定@Column
    private String sex;

userid

create database mydb;

    @Column("col_age")
    private Integer age;

Integer 

 

    @Column("career_job")
    private String career;
   
    @NoColumn
    private int kvalue;
   
    public User() {
        super();
    }

用户id

// 查看所有的数据库库

    public User(String name, String sex, Integer age, String career) {
        super();
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.career = career;
    }

 

 

    public User(Integer id, String name, String sex, Integer age, String career) {
        super();
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.career = career;
    }

建表语句:

show databases;

    public int getId() {
        return id;
    }

[sql] 

 

    public void setId(int id) {
        this.id = id;
    }

<span style="font-size:18px;">Create table tuser(id Integer primary key,  

// 删除 mydb

    public String getName() {
        return name;
    }

                   name varchar(50),  

 

    public void setName(String name) {
        this.name = name;
    }

               password varchar(50),  

drop database mydb;

    public String getSex() {
        return sex;
    }

                   sex  varchar(20)  

 

    public void setSex(String sex) {
        this.sex = sex;
    }

                   );  

// 删除 user 表

    public Integer getAge() {
        return age;
    }

Create table ttable(id Integer primary key,  

 

    public void setAge(Integer age) {
        this.age = age;
    }

               name varhcar(50),  

drop table user;

    public String getCareer() {
        return career;
    }

                   userid Integer  

 

    public void setCareer(String career) {
        this.career = career;
    }

);    www.2cto.com  

针对表中的记录的操作  www.2cto.com  

    public int getKvalue() {
        return kvalue;
    }

</span>  

 

    public void setKvalue(int kvalue) {
        this.kvalue = kvalue;
    }

 

增 insert

    public String toString() {
        StringBuffer sb = new StringBuffer();
        sb.append("[" id ", " name ", " sex ", " age ", " career "]");
        return sb.toString();
    }

 

 

}

插入数据语句

删 delete

> 对User的增删查改,UserCrudTest.java,记得引入junit-4.8.2.jar
public class UserCrudTest {

[sql] 

 

    static Session session;
   
    @BeforeClass
    public static void before() {
        session = Jorm.getSession();
    }
   
    @AfterClass
    public static void after() {
        Jorm.free();
    }
   
    @Test
    public void save_user() {
        session.clean(User.class);
        User user = null;
        for (int i = 0; i < 600; i ) {
            String sex = (i % 2 == 0 ? "男" : "女");
            user = new User(Strings.fixed(5), sex, Numbers.random(98), Strings.random(8));
            session.save(user);
        }
    }
   
    @Test // 批量保存
    public void batch_save_user() {
        session.clean(User.class);
        JdbcBatcher batcher = session.createBatcher();
        User user = null;
        for (int i = 0; i < 600; i ) {
            String sex = (i % 2 == 0 ? "男" : "女");
            user = new User(Strings.fixed(5), sex, Numbers.random(98), Strings.random(8));
            batcher.save(user);
        }
        batcher.execute();
    }
   
    @Test
    public void loadUser() {
        User user = session.read(User.class, 1);
        // 这里user是一个代理对象,因为@Entity(table="t_user", lazy = true)
        System.out.println(user.getCareer());// 发出查询sql
    }
   
    @Test
    public void deletUser() {
        User user = session.read(User.class, 1);
        if(null != user) {
            session.delete(user);
        }
        user = session.read(User.class, 1);
        System.out.println(user);
    }
   
    @Test
    public void test_update_proxy() {
       
        User u;
        u = session.read(User.class, 2);
        Assert.assertNotNull(u);
        Assert.assertTrue(u instanceof JormProxy);
       
        u.setName("Gerald.Chen");
        session.update(u);
        System.out.println(u.getName());
        u = session.read(User.class, 2);
        Assert.assertTrue("Gerald.Chen".equals(u.getName()));
    }
   
    @Test
    public void queryUser() {
        SqlParams<User> params = new SqlParams<User>();
        params.setObjectClass(User.class);
        params.setFirstResult(8);
        params.setMaxResults(20);
        List<User> users = session.list(params);
        System.out.println(users.size());
        System.out.println(users);
    }
   
}

<span style="font-size:18px;">Insert into tuser(id, name, password,sex)values(1,’中文’,’测试’,’m’);  

改 update

 作者“风故故,也依依”  

Insert into tuser(id, name, password,sex)values(2,’中文’,’测试’,’m’);  

 

以MySQL为例,执行下面的sql建立数据表 CREATE TABLE `t_user` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, `sex` char(4) DEFAULT NULL, `age` int(11)...

  

查 select

Insert into ttable(id,name, userid)values(1,’1’,1);  

 

Insert into ttable(id,name, userid)values(2,’2’,2);  

2 操作数据库

Insert into ttable(id,name, userid)values(3,’2’,2);  

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

</span>  

 

 

create database mydb1;

1、  左外连接

 

select * from ttablet left join tuser u on t.userid=2 and t.userid=u.id;结果如下:

创建一个使用utf-8字符集的mydb2数据库。

数据库 1  

 

以左侧表(ttable)为基础,满足条件数据全部查询出来,右表(tuser)不满足条件出现null补齐

create database mydb2 character set utf8;

 

 

2、右外连接

创建一个使用utf-8字符集,并带校对规则的mydb3数据库。

 select * from ttable t right join tuser u on t.userid=2 and t.userid=u.id出现如下结果:  www.2cto.com  
数据库 2  

 

以右表为基础(tuser)满足条件的数据全部查询出来,左表(ttable)不满足条件出现null补齐

create database mydb3 character set utf8 collate utf8_general_ci;

 

 

3、内连接

查看当前数据库服务器中的所有数据库

select * from ttable,tuser;出现结果如下:

 

数据库 3  

show databases;

以笛卡尔积的方式展现。设A、B为集合,用A中的元素x作第一元素,B中的元素y作第二元素,构成有序对,所有这样的有序对组成的集合,叫做A和B的笛卡儿积,记做A×B。 

 

在数据库中,设有关系A为 <学号、姓名> ,具体内容为{ <1,张三> , <2,李四> };关系B为 <学号、年龄> ,具体内容为{ <1,20> <2,22> }。 

查看前面创建的mydb2数据库的定义信息,字符信息

则A×B={ <1,张三,1,20> , <1,张三,2,22> , <2,李四,1,20> , <2,李四,2,22> } 

 

如果再做第一列=第三列的选择,再做只保留第一、第二、第三列的投影,即得{ <1,张三,20> , <2,李四,22> }这样,通过关系代数的三个运算,我们查到了每个人的年龄。

show create database mydb2;

 

 

 

删除前面创建的mydb3数据库

作者 柳波

 

出于对知识的总结和分享,对常见的查询做了练习和总结。 数据准备,包括两个表,tuser和ttable。tuser和ttable为1对多关系...

drop database mydb3;

 

将mydb2的字符集修改为gbk

 

alter database mydb2 character set gbk;

 

// 备份数据库

 

use mydb2;

 

create table a

 

(

 

name varchar(20)

 

);

 

// 插入数据  www.2cto.com  

 

insert into a (name) values("zhangsan");

 

insert into a (name) values("wangwu");

 

// 备份数据库

 

mysqldump -uroot -proot mydb1 > d:a.sql

 

这条命令跟mysql是一个级别的,所以不能在mysql下面使用。

 

// 删除 mydb2

 

drop database mydb2;

 

// 恢复数据库

 

create database mydb22;

 

source d:a.sql

 

// source 命令

 

指定一个sql脚本

 

关闭报错声音

 

Mysql –uroot –p123456 -b

 

3、操作表

  1. 创建表

 

创建员工表

 

Id 整形

 

name 字符型

 

sex 字符型或bit型

 

brithday 日期型

 

Entry_date 日期型

 

job 字符型

 

Salary 小数型

 

resume 大文本型

 

创建表:

 

create table employee

 

(

 

id int,

 

name varchar(20),

 

gender varchar(6),

 

brithday date,

 

entry_date date,

 

job varchar(20),

 

salary double,

 

reshme text

 

);

 

  1. 修改表  www.2cto.com  

 

在上面员工表的基础上增加一个image列

 

alter table employee add image blob;

 

修改job列,使其长度为60

 

alter table employee modify job varchar(60);

 

删除sex列。

 

alter table employee drop sex;

 

表名改为users。

 

rename table employee to user;

 

修改表的字符集为utf-8

 

alter table user character set utf8;

 

列名name修改为username

 

alter table user change column name username varchar(20);

 

查看所有表

 

show tables;

 

查看表的创建语句

 

show create table user;

 

查看表结构

 

desc user;

 

删除 user 表

 

drop table user;

 

4、针对表中数据的增删改查

针对表中的数据操作无外乎四个:增删改查 crud create

 

  1. insert语句  www.2cto.com  

 

employee.sql

 

create table employee

 

(

 

id int,

 

name varchar(20),

 

sex varchar(10),

 

birthday date,

 

salary float,

 

entry_date date,

 

resume text

 

);

 

// 向员工表插入三条记录

 

Insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,'zhangsan','male','1987-11-23',1500,'2010-2-18','good boy');

 

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'wangwu','male','1988-11-23',1200,'2010-2-18','good boy');

 

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'xiaohong','female','1983-11-23',1800,'2010-2-18','good girl');

 

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(4,'赵楠','男','1986-11-23',3800,'2011-2-18','绝种好男人');

 

// 查看表的所有记录

 

select * from employee;

 

// 查看数据使用的所有编码

 

show variables like 'character%';

 

// 修改客户端的编码 为 gbk

 

set character_set_client=gbk;

 

// 修改结果集的编码为gbk

 

set character_set_results=gbk;

 

// insert 语句的其他写法

 

create table a

 

(

 

username varchar(20),

 

password varchar(20)

 

);

 

insert into a values('zs','111');

 

insert into a values('wangwu','2222'),('lisi','3333');

 

insert a values('aaa','bbb');

 

insert a (username) values('aaa');

 

  1. update语句  www.2cto.com  

 

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

 

update employee set salary=5000;

 

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

 

update employee set salary=3000 where name='zhangsan';

 

将姓名为’lisi’的员工薪水修改为4000元,sex改为female。

 

update employee set salary=4000,sex='female' where name='wangwu';

 

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

 

update employee set salary=salary 1000 where name='xiaohong';

 

  1. delete语句

 

删除表中name为’赵楠’的记录。

 

delete from employee where name='赵楠';

 

删除表中所有记录。Delete是一条条的删,如果条目很多的话,会效率很低

 

delete from employee;

 

使用truncate删除表中记录。 摧毁表再创建表 尽量用这个

 

truncate employee;

 

  1. select语句

 

student.sql

 

create table student(

 

id int,

 

name varchar(20),

 

chinese float,

 

english float,

 

math float

 

);

 

insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);

 

insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95);

 

insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);

 

insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92);

 

insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);

 

insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);

 

insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);

 

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

 

select * from student;

 

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

 

select name,english from student;

 

过滤表中重复数据。

 

select distinct english from student;

 

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

 

select name,english 10,chinese 10,math 10 from student;

 

统计每个学生的总分。

 

select name,english chinese math as sum from student;

 

使用别名表示学生分数。

 

where 子句

 

查询姓名为李一的学生成绩

 

select * from student where name='李一';

 

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

 

select * from student where english>90;

 

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

 

select name,english chinese math sum from student where english chinese math>200;

 

此处可以不用as

 

运算符  www.2cto.com  

 

查询英语分数在 80-90之间的同学。

 

select * from student where english between 65 and 85;

 

查询数学分数为89,90,91的同学。

 

select name,math from student where math in(89,90,91);

 

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

 

select * from student where name like '李%';

 

// 查询姓李的两个字的学生

 

select * from student where name like '李_';

 

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

 

select * from student where math>80 and chinese>80;

 

查询英语>80或者总分>200的同学

 

select *,chinese math english from student where english>80 or chinese english math>200;

 

order by 子句

 

对数学成绩排序后输出。

 

select * from student order by math;

 

对总分排序后输出,然后再按从高到低的顺序输出

 

select *,chinese math english from student order by chinese math english desc;

 

对姓李的学生成绩排序输出 order从句是需要放在where从句的后面

 

select *,chinese math english from student where name like '李%' order by chinese math english;

 

合计函数

 

count

 

统计一个班级共有多少学生?

 

select count(*) from student;

 

统计数学成绩大于90的学生有多少个?

 

select count(*) from student where math>90;

 

统计总分大于230的人数有多少?

 

select count(*) from student where chinese math english>230;

 

sum

 

统计一个班级数学总成绩?

 

select sum(math) from student;

 

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

 

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

 

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

 

select sum(math chinese english) from student;

 

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

 

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

 

缺考的不参与计算

 

select sum(chinese)/count(chinese) from student;

 

avg

 

语文平均分

 

select avg(chinese) from student;

 

max/min

 

语文最 高分

 

select max(chinese) from student;

 

select语句(6)

 

group by

 

订单表

 

create table orders(

 

id int,

 

product varchar(20),

 

price float

 

);

 

insert into orders(id,product,price) values(1,'电视',900);

 

insert into orders(id,product,price) values(2,'洗衣机',100);

 

insert into orders(id,product,price) values(3,'洗衣粉',90);

 

insert into orders(id,product,price) values(4,'桔子',9);

 

insert into orders(id,product,price) values(5,'洗衣粉',90);

 

将商品归类

 

select * from orders group by product;

 

显示单类商品总结

 

select *,sum(price) from orders group by product;

 

商品分类 显示单类商品总价大于100的

 

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

 

// 将单价大于20 的商品进行归类显示 按照价格排序

 

select * from orders where price>20 group by product order by price;

 

5、表的约束

表的约束就是在定义表时,我们可以在创建表的同时为字段增加约束,对将来插入的数据做一些限定

  www.2cto.com  

一、表的约束

 

表的约束就是在定义表时,为表中的字段加一些约束条件,对将来插入的数据做一些限定

 

  1. 唯一约束 unique

 

create table a

 

(

 

id int,

 

username varchar(20) unique,

 

password varchar(20)

 

);

 

insert into a (id,username,password) values(1,'zhangsan','1111');

 

insert into a (id,username,password) values(2,'wangwu','1111');

 

  1. 非空约束 not null

 

create table b

 

(

 

id int not null,

 

name varchar(20)

 

);

 

insert into b (id,name) values (1,'aaaa');

 

insert into b (id,name) values (1,'bbbb');

 

  1. 主键约束 相当于 唯一约束 非空约束

 

数据库中的每张表都应该至少有一个主键,通常是id

 

create table c

 

(

 

id int primary key,

 

name varchar(20)

 

);

 

insert into c(id,name) values (1,'aaaa');

 

insert into c(id,name) values (2,'bbbb');

 

create table d

 

(

 

firstname varchar(20),

 

lastname varchar(20),

 

primary key(firstname, lastname)

 

);

 

insert into d (firstname, lastname) values ('tom', 'cat');

 

insert into d (firstname, lastname) values ('tom', 'hks');

 

create table e

 

(

 

id int ,

 

name varchar(20)

 

);

 

insert into e (id,name) values(1,'aaa');

 

为表加上主键约束

 

alter table e add primary key(id);

 

此处修改的命令:

 

修改job列,使其长度为60。

 

alter table employee modify job varchar(60);

 

删除主键约束

 

alter table e drop primary key;

  www.2cto.com  

  1. 定义主键自动增长

 

这个的作用就是让id键随着条目的增加,自动往上增长。

 

注意主键类型必须是int,只有int类型的才可以自增

 

create table f

 

(

 

id int primary key auto_increment,

 

name varchar(20)

 

);

 

insert into f(name) values ('aaa');

 

insert into f(id,name) values (11,'bbb');

 

mysql> select * from f;

 

---- ------

 

| id | name |

 

---- ------

 

| 1 | aaa |

 

| 2 | aaa |

 

| 3 | aaa |

 

| 4 | aaa |

 

| 11 | bbb |

 

| 12 | aaa |

 

| 13 | aaa |

 

| 14 | aaa |

 

| 15 | aaa |

 

| 16 | aaa |

 

---- ------

 

外键约束

 

约束力: 插入的外键值必须为被参照列存在的值

 

被参照表中被参照的数据不允许删除

 

注意:外键约束并没有非空和唯一的约束力

 

创建丈夫表和妻子表

 

create table husband

 

(

 

id int primary key auto_increment,

 

name varchar(20)

 

);

 

create table wife

 

(

 

id int primary key auto_increment,

 

name varchar(20),

 

husbandid int,

 

constraint husbandid_FK foreign key(husbandid) references husband(id)

 

);

  www.2cto.com  

外界约束:Constraint是定义外键约束的,foreign key是外键的名称 参照某张表的主键

 

分别添加两条记录

 

insert into husband(name) values ('laobi');

 

insert into husband(name) values ('laoyu');

 

insert into wife(name,husbandid) values ('fengjie',2);

 

insert into wife(name,husbandid) values ('furongjie',1);

 

fengjie 找老公

 

select * from husband where id=(select husbandid from wife where name='fengjie');

 

delete from husband where name='laoyu';

 

6 表的关系

1 多对一

 

创建部门表

 

create table department

 

(

 

id int primary key auto_increment,

 

name varchar(20)

 

);

 

添加三个部门信息

 

insert into department(name) values('开发部');

 

insert into department(name) values('销售部');

 

insert into department(name) values('人事部');

 

创建员工表

 

create table employee

 

(

 

id int primary key auto_increment,

 

name varchar(20),

 

departmentid int,

 

constraint departmentid_FK foreign key(departmentid) references department(id)

 

);

 

添加六个员工信息

 

insert into employee(name,departmentid) values ('张三',1);

 

insert into employee(name,departmentid) values ('李四',1);

 

insert into employee(name,departmentid) values ('王五',2);

 

insert into employee(name,departmentid) values ('赵六',3);

 

insert into employee(name,departmentid) values ('田七',3);

 

insert into employee(name,departmentid) values ('周八',null);

 

多表的查询

 

查出1号部门所有的员工

 

select * from employee where departmentid=1;

数据库, 

查出开发部所有的员工

 

select * from employee where departmentid=(select id from department where name='开发部');

 

查出赵六在那个部门

 

select * from department where id=(select departmentid from employee where name='赵六');

 


  www.2cto.com  

联合查询

 

查出开发部所有的员工

 

select * from department,employee;

 

---- -------- ---- ------ --------------

 

| id | name | id | name | departmentid |

 

---- -------- ---- ------ --------------

 

| 1 | 开发部 | 1 | 张三 | 1 |

 

| 2 | 销售部 | 1 | 张三 | 1 |

 

| 3 | 人事部 | 1 | 张三 | 1 |

 

| 1 | 开发部 | 2 | 李四 | 1 |

 

| 2 | 销售部 | 2 | 李四 | 1 |

 

| 3 | 人事部 | 2 | 李四 | 1 |

 

| 1 | 开发部 | 3 | 王五 | 2 |

 

| 2 | 销售部 | 3 | 王五 | 2 |

 

| 3 | 人事部 | 3 | 王五 | 2 |

 

| 1 | 开发部 | 4 | 赵六 | 3 |

 

| 2 | 销售部 | 4 | 赵六 | 3 |

 

| 3 | 人事部 | 4 | 赵六 | 3 |

 

| 1 | 开发部 | 5 | 田七 | 3 |

 

| 2 | 销售部 | 5 | 田七 | 3 |

 

| 3 | 人事部 | 5 | 田七 | 3 |

 

| 1 | 开发部 | 6 | 周八 | NULL |

 

| 2 | 销售部 | 6 | 周八 | NULL |

 

| 3 | 人事部 | 6 | 周八 | NULL |

 

---- -------- ---- ------ --------------

 

笛卡尔集 多张表的所有记录的排列组合

 

笛卡尔集当中有很多部匹配的数据(参照表外键列的值和被参照表主键的值不一致) 为了方便 以下称为废数据

  www.2cto.com  

加条件去掉废数据

 

select * from department,employee where employee.departmentid=department.id;

 

加条件做查询

 

select * from department,employee where employee.departmentid=department.id and department.name='开发部';

 

优化 给表加别名 只保留需要的数据

 

select e.* from department d,employee e where e.departmentid=d.id and d.name='开发部';

 

查出赵六在那个部门

 

select d.* from department d,employee e where e.departmentid=d.id and e.name='赵六';

 

  1. 多对多

 

创建老师表

 

create table teacher

 

(

 

id int primary key auto_increment,

 

name varchar(20)

 

);

 

添加三个老师

 

insert into teacher(name) values ('老方'),('老余'),('老毕');

 

create table student

 

(

 

id int primary key auto_increment,

 

name varchar(20)

 

);

 

添加三个学生

 

insert into student(name) values('大毛'),('二毛'),('三毛'),('四毛'),('五毛'),('六毛'),('七毛'),('八毛'),('小毛');

  www.2cto.com  

创建中间表描述关系

 

create table tea_stu

 

(

 

teaid int,

 

stuid int,

 

primary key(teaid,stuid),

 

constraint teaid_FK foreign key(teaid) references teacher(id),

 

constraint stuid_FK foreign key(stuid) references student(id)

 

);

 

添加数据

 

insert into tea_stu (teaid,stuid) values (1,1),(1,2),(1,4),(1,5),(1,6),(1,8),(2,1),(2,3),(2,5),(2,7),(2,9),(3,2),(3,3),(3,4),

 

(3,5),(3,6),(3,7),(3,8);

 

查询2号老师教过的学生

 

select s.* from student s,tea_stu ts where ts.stuid=s.id and ts.teaid=2;

 

查询老余教过的所有的学生

 

select s.* from student s,tea_stu ts,teacher t where ts.stuid=s.id and ts.teaid=t.id and t.name='老余';

 

多表联合查询

 

查询的结果为笛卡尔集

  www.2cto.com  

n张表联合查需要n-1个条件来去掉废数据

 

去掉废数据的条件 参照表的外键列=被参照表的主键列

 

再加上查询条件即可得到结果

 

  1. 一对一

 

create table person 

    id int primary key auto_increment, 

    name varchar(20) 

);

 

create table idcard 

    id int primary key, 

    location varchar(20), 

    constraint personid_FK foreign key(id) references person(id) 

);

  www.2cto.com  

insert into person (name) values('zhangsan'); 

insert into person (name) values('lisi');

 

insert into idcard (id,location) values(2,'天津');

 

insert into idcard (id,location) values(1,'上海');

 

查李四的身份证 

select idcard.* from person,idcard where idcard.id=person.id and person.name='lisi';

SQL语句 sql语句,一种用于操作数据库的语言 数据库, 简单地理解为硬盘上的文件, 可以存放数据 sql 语句大致...

本文由www.bifa365365.com发布于数据库,转载请注明出处:MySQL数据库CRUD语句飞快入门,使用Jorm轻松的增加

关键词: www.bifa3653