MySQL之一

sql格式

String sql=”select * from t_shop where user_id=”+user.getUserId()+” and id=”+goods.getShopId()+””;
String sql=”select * from t_goods where name like ‘%”+search+”%’and status!=’已下架’ order by sale DESC,id DESC”;
String sql = “update t_user set userName=’”+user.getUserName()+”‘,email=’”+user.getEmail()+”‘,number=’”+user.getNumber()+”‘ where id=”+user.getUserId()+””;
String sql = “insert into t_order (status,user_id,totalPrice) values(“+order.getStatus()+”,”+user.getUserId()+”,”+order.getTotalPrice()+”)”;
String sql=”delete from t_cart where user_id=”+userId+””;
String sql2 = “update t_user set password=SUBSTRING(md5(‘“+user.getPassword()+”‘),1,16) where userName=’”+user.getUserName()+”‘“;
```
//截取第一位到第16位共16位密文存入数据库，否则长度32位太长无法插入
```

String status=”已下架”;

String sql = "update t_goods set status='"+status+"' where id="+goodsId+"";

获取刚插入记录的id

sql = "insert into t_role (name) values('"+role+"')"; 
pstmt = con.prepareStatement(sql);  
//获取插入后生成的角色ID，但只能是mysql自增长的
pstmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
rs = pstmt.getGeneratedKeys();
if(rs.next()) {
    roleId=rs.getInt(1); //第一个就是刚生成的id
}
//代码实例：market2/AdminDao/417行

/**
 * @Author haien
 * @Description 获取刚添加用户的id（根据上下文，不会因并发而出错） 
 * @Date 2019/1/17
 * @Param []
 * @return int
 **/
@Query("select last_insert_id from users")
int getLastInsertId();

MySQL代码操作

直接插入格式：

String sql=”insert into t_book value(null,’java牛逼’,888,’B哥’,1)”;

String sql = “insert into t_book value(null,’”+bookName+”‘,”+price+”,’”+author+”‘,”+bookTypeId+”)”;
sql语句注意点
where前面不要加“,”
表名和字段名不要用关键字，通常在表名之前加t_是为了防止用到关键字。MySQL关键字
where column=’直接写值而不是变量’：注意要加个单引号
MySQL将查找结果按时间降序排序
sql=”select * from t_order where user_id=”+userId+” and status=1 order by orderTime DESC,id DESC”;
sql的一些功能
用IN指定范围(NOT IN 不在该范围内的)
以上可用BETWEEN 21 AND 23代替（NOT BETWEEN AND）则表示不在范围内的
查询字段为空或不为空的
去重复，相同字段不显示，比如，都是一年级就只显示一次一年级
concat 聚合
查询每个年级人数
查询年级人数大于3的年级
with rollup 加一个总计，数字的话直接累加，字符的话全部显示
从第一条开始查询5条数据
使用占位符?
String sql2 = “insert into t_user values(null,?,?)”; //id值要设好，写(?,?)第一坑变成id
pstmt = con.prepareStatement(sql2);
pstmt.setString(1, user.getUserName());//给第一个问号设值
pstmt.setString(2, user.getPassword());
pstmt.executeUpdate(); //括号里不能有sql

检索某个数据是否已存在

//验证用户是否已存在
String sql="select * from t_user where userName=?";
PreparedStatement pstmt =null;
pstmt=con.prepareStatement(sql);
//检索用户名
pstmt.setString(1, user.getUserName());
User resultUser=null;
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
    resultUser=new User();
    resultUser.setUserName(rs.getString("userName"));
    resultUser.setPassword(rs.getString("password"));
}
//若用户已存在
if(resultUser!=null) {
    return false;
}

完整sql语句请点击
执行mysql语句返回的int值，是指当前执行的sql语句所影响的行数n。如果执行的是update语句，那么就说明有n行被update了；若是删除操作，那么就说明有n条记录被删除了。
MySQL语句where=后面这个东西如果是个字符串一定要写好格式啊，写好了字符串会变成黑色，格式是’”+字符串+”‘’
查询结果按中文姓名顺序排序
要把数据库查询到的记录按照人名排序，通常是按姓氏的拼音排序，可以采用MySQL的排序方式
```
@Query(value = "select * from user order by convert(name using gbk) asc",nativeQuery = true)
public List<T> find();
```
注意：nativeQuery = true一定要写，否则sql的一些符号Spring认不出
name是排序的依据，using gbk是因为数据库已经被认为指定采用utf-8编码，但是只有gbk编码本身采用的是按拼音排序的方式（只有一级汉字是这样，二级汉字不是，但二级是生僻字也没事）
代码实例：JpaDemo/entity/Identity、Task、Project,repository/GenericDao/ProjectRepository,test/ProjectRepositoryTest
MySQL快捷键
命令行：上下键切换命令
图形工具：f5刷新
MySQL数据类型
char(10)，存放2位，但实际分配10位空间
varchar(10),存多少分配多少空间

函数

concat(str1,str2)：拼接字符串

select concat(str1,str2) from t_user; //str1、2为表字段

有一个str为null则函数返回null
ifnull：判断是否为null并赋予一个默认值

select concat(ifnull(str1,"defaultValue"),str2) from t_user; //str1为null则用defaultValue代替str1

由于concat只能传入二个参数，且返回的依旧是str，所以当需要拼接多个字符串时可以嵌套使用
substring(str,startIndex,len)：字符串截取，len可省略，首位为1，从第startIndex位开始取，为负数则从倒数开始往右取。
substring_index(str,delim,count)：从第count个字符串delim开始截取之前所有字符。

//从第2个.开始截取之前all字符：www.example
substring_index('www.example.com', '.', 2);
//从倒数第2个.开始截取之后all字符:example.com
substring_index('www.example.com', '.', -2);
//找不到delim则返回all字符串: www.example.com
substring_index('www.example.com', '.coc', 1);

left(str, length): 从左边开始截取length位字符串。

//exa
left('example.com', 3);

right(str, length): 从右边开始。

//com
right('example.com', 3);

mysql字符串截取

新建用户并设置权限

cmd直接在根目录运行mysql -uroot -p密码,登录mysql.

新建thz用户，密码111111，指定它只能访问thz数据库的所有表，但拥有所有访问权限

grant all privileges on thz.* to thz@"%" identified by "111111"; 
// %表示允许远程登录，只许本机登录的话这里换成localhost或127的那个

不想给所有权限的话all privileges可以替换成select,insert,update,delete这样用逗号连接，表示只能对表数据进行增删查改，不能改变表结构（删表、建表等）
对表结构进行更改的话是用create,drop等权限，还有几个权限自查吧！

程序配置了该账户则拥有了这些权限：

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/shiro"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
</bean>

计算时间差

看个例子，将查询结果按给定时间从近到远排序

<select id="findAllOrderByTime" resultMap="GoodsResult">
    select *,ABS(TIMESTAMPDIFF(second,#{_parameter},Report_Date_Time)) as diffTime from goods order by diffTime ASC
</select>

ABS()：计算绝对值
TIMESTAMPDIFF(day/our/minute/… , str , str)：计算按时间格式输入的字符串的时间差
datediff(now(), ‘2015-04-20 00:00:00’): 计算时间差，返回结果以天为单位，不超过一天返回0
字符串与时间互转
jdbc.url连接数据库
用localhost:3306固然可以，但实际数据库需要解析localhost，会比较耗时，最好用127.0.0.1，这是本地ip，会比较快。

远程连接

spring.datasource.url=jdbc:mysql://120.77.144.151/llcweb

这是师兄的服务器的数据库地址，省略了3306
连接阿里云服务器上的远程数据库
默认情况下，CentOS的MySQL数据库是不允许被远程访问的，所以我们需要开启权限
登录数据库，使用MySQL系统自带的mysql数据库，它带有用户表
```
use mysql;
```
更新用户，允许所有ip访问，当然也可以设置特定的ip(一般是本地计算机的公网ip，不过不是127.0.0.1那个)，然后host就要改成特定的ip
```
update user set host='%' where user='root';
ERRER 1062(23000): Duplicate entry '%-root' for key 1 //报错不要紧的
```
查询我们是否创建访问的host成功了
```
select host,user,password fron user;
```

enum

MySQL建表语句

//选择一个数据库
drop database if exists shiro;
create database shiro;
use shiro;

//开始建表
CREATE TABLE `award` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
   `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
   `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
   `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
   available bool default false,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

约束

constraint：约束，主要是给约束取一个别名，否则系统会默认随机一个约束名

CREATE TABLE `users` (
    `id` BIGINT(20) NOT NULL primary key AUTO_INCREMENT, //在这里声明主键
    PRIMARY KEY (`id`), //或在这里
    constraint pk_users primary key(id) //或这里，用constraint起别名，便于删除或修改此约束
)

//声明外键时也可以用constraint
CONSTRAINT FK_post_author FOREIGN KEY (author_id)
  REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT
//简单形式
FOREIGN KEY (author_id)REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT

代码实例：ideaProjects/shiroHelloWorld/shiro.sql

起别名

select 字段1 as 字段1别名，字段2 as 字段2别名 from 表名 as 表别名

MySQL之一

sql格式

MySQL代码操作

sql语句注意点

MySQL将查找结果按时间降序排序

sql的一些功能

使用占位符?

检索某个数据是否已存在

查询结果按中文姓名顺序排序

MySQL快捷键

MySQL数据类型

函数

新建用户并设置权限

计算时间差

jdbc.url连接数据库

连接阿里云服务器上的远程数据库

enum

MySQL建表语句

约束

起别名

谢谢大爷~