数据库mysql使用

数据库连接方式

Spring.datasource.url=jdbc:mysql://location/mydb

Spring.datasource.username=root

Spring.datasource.password=

 

显示数据库:show databases;

 

创建数据库:create database mydb;

 

使用数据库:use mydb;

 

显示表:show tables;

 

创建表:

       create table 表名 (

              列名1 数据类型,

              列名2 数据类型

       );

 

       常用数据类型:

              int、varchar(长度)--string

 

插入数据

       insert into 表名(列名1,列名2……) values(值……)

show databases;

drop database;

charset=UTF-8;

use 数据库名;

show table;

desc 表名;

int varchar;

drop table if exists user;

create table user(

id int primary key auto_increment,

name varchar(20),

password varchar(20)

);

Insert into user(name,password) values (‘avc’,’asdsf’);

Select book.id ,book.name from book join user on user_id = user.id;

Select book.id,user.name from book left join user on user_id=user.id;左连接

Select * from book order by user_id desc;降序排列 asc升序排列

Where条件中<>表示不等于

数据库中空值有巨大的性能问题,所以默认不处理,

Select * from user where password is not null;密码不为空

Select * from user where password is null;密码为空

Mysql数据库默认不区分大小写

Select * from user where binary name=‘aBc’;匹配大小写

Select * from user where not binary name=‘aBc’;不匹配大小写

Select id,name,ifnull(password,‘空密码’)as userpassword from user;当密码为空时替换空值为空密码;

Select id,name ,password from user where id between 2 and 3;查询id为2到3的数据;

Select id,name,password from user where id in(2,3,5);查询id为2或者3或者5的数据;取反not in();

Select id,name,password from user where name like‘%a’;名字以a结尾的数据,‘%a%’名字包含a的数据,‘a%’以a开头的数据;

Select concat(id,name,password)from user;查询出来的数据会将三个数据拼接在一起;

Select id,name,if(id<4,’小’,‘大’) from user;将id以大小代替;

数据库日期有date,time,datetime 以now()插入;

取可以取day(datetime)

Count()求多少条数据,sum()求和

Not null 非空;空字符串和空不一样;password varchar(20) not null default ‘’当数据为空时插入空字符串;

Id int primary key auto_increment ;id自动递增;unique 值唯一;

Sex enum(‘男’,‘女’,‘人妖’);枚举;

User_id int,foreign key(user_id) references user(id) 外键约束

<Select id=”get” resultMap=”getMap”>

Select book.id,name,address,bn.id as number_id ,number

Join book.number bn on book.id = bn.book_id where book.ic=#{id}

</select>

<resultMap id=”getMap” type=”com.yvdedu.address_book.modek.Book”>

<id property=”id” column=”id”/>

<result property=”name” column=”name”/>

<result property=”address” column=”address”/>

<collection property=”numbers” ofType=”com.yvdedu.address_book.model.BoolNumber”/>

<id property=”id” column=”number_id”/>

<result property=”number” column=”number”/>

<association property=”type” select=”......mapper.NumberTypeMapper.get” column=”type_id” 如果查询参数不对名称对不上用column=”{id=type_id}”/>

</collection>list<book>参数

<association property=”book” javaType=”....model.Book”>

<id property=”id” column=”book_id” />

<result property=”name” column=”name”/>

<result property=”address’ column=”address”/>

</association>book参数

</resultMap>

 

 

Select book_name....

From book_number

Join book b on book_number.book_id = b.id

Join number_type nt on book_number.type_id = nt.id

Where book_number.id=#{id}

posted @ 2020-06-08 18:16  鱼3秒  阅读(116)  评论(0编辑  收藏  举报