mysql1

显示数据库：show databases;

创建数据库：create database mydb;

使用数据库：use mydb;

显示表：show tables;

 

sub queries
exists ; not exists;
in; not in;
any/some、all
group by 分组按什么分，写在最后, 然后组函数count条件要用having count(id)>1不能用where，且写在group by后面
select * from 表名称 group by 列名 [having 聚合函数条件]
count(id)统计多少条、max(book_id)、min(book_id)、sum(book_id)和、avg(book_id)平均值
常用函数
concat() 、lower()、upper()
left()、right()、substring()
if()

select distinct只返回不同的值 it.* from book_item
join item_type it
on book_item.type_id = it.id;

select* from book_item
where id not查询结果取反 in(1,2,3,4)只查id为1,2,3,4的

子查询
select if(id>1,"大"，"小" from book_item
where id not查询结果取反 in(select distinct type_id from user)查询结果作为查询条件
select* from (select distinct type_id from user) as xx
where id not查询结果取反 in(1,2,3,4)查询结果作为查询条件
select* from item_type
join(select distinct type_id from user) as xx
where item_type.id=xx.id
select distinct *,(select distinct value from book_item as bi where bi.type_id=item_type.id limit 1)
from item_type

创建表：
create table 表名 (
列名1 数据类型,
列名2 数据类型
);

常用数据类型：
int、varchar(长度)--string

插入数据
insert into 表名(列名1，列名2……) values(值……)
<resultMap id="result" type="com.yvdedu.orderingsystem.model.modelClass.Dishes">
<result property="typeId" column="type_id"/>
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="pictureAddress" column="picture_address"/>
</resultMap>
<select id="select" resultMap="result">
select type_id,id,name,price,picture_address from dishes
<where>
<if test="typesId>0"> type_id=#{typesId} </if>
<if test="id>0"> id=#{id} </if>
<if test="Name!=null"> name like concat('%',#{Name},'%') </if>
</where>
</select>

select * from book left join user on user_id=user.id做链接
order by user.id desc降序排列asc升序
select * from book order by user_id asc ，id desc
select * from user where binary name=‘aBc’；强制区分大小写
select id，name，ifnull（password，‘空密码’）as userpassword from user；空值取代
select id，name，password from user where id in（2,3,5）；查询范围
select concat（id，name，password） from user；字符串链接
select id，name，if（id<4,'小‘，’大‘） from user；查询判断
like concat('%',#{Name},'%')
time，date，datetime 时间类型
Primary key
auto_increment(递增)
unique（唯一）
Not null
Default其后跟值表示默认值
Enum(枚举)
enum(true,false)
Foreign外键链接
foreign key (列名) references 参照表(参照列)

<resultMap id="getMap" type="....">
<id property="id" column="id" />
<result property="number" column="number"/>
<association property="book" javaType="....book">

version:"3"
service:
mysql:
image:mysql:5.5
container_name:mysql
environment:
- MYSQL_ALLOW_EMPTY_PASSWORD=true
volumes:
- ...config\:/etc/mysql/
- ...data\:/var/lib/mysql/
ports:
- 3306:3306