常用sql语句（mysql测试）

DB数据库，Database
DBMS数据库管理系统，DatabaMemanagmentSystem
SQL结构化查询语言，structure Query Language

开启服务
net start mysql80

登录
mysql -u root -p

显示数据库

show databases;

使用数据库
use mysql;

显示数据库表
show tables;
show tables from mysql;

显示数据库
select database();

desc stuinfo;

创建数据库
create database test;

创建数据库并设置字符编码

create database test default character set utf8 collate utf8_general_ci;

创建数据库表

create table customer(id int,name varchar(20),password varchar(20),email varchar(30));

复制数据库表

create table type select * from test1.type;

删除某列

alter table customer drop id;

插入一条数据
insert into stuinfo(id,name) values(1,'John');

查询
select * from stuinfo;

按时间查询：

select * from test where date_format(createtime,'%Y-%m-%d') between '2020-01-01' and '2021-01-01';

select * from test where createtime between '2020-01-01 00:01:01' and '2021-01-01 01:01:01';

select * from test where createtime between '2020-01-01 00:01:01' and '2021-01-01 01:01:01';

修改
update stuinfo set name='jack' where id=1;

删除字段

 alter table songinfo drop id;

删除值
delete from stuinfo where id=1;

删除某个表的所有数据

 delete from videos;

或

truncate table videos;

插入新字段在...之后（增加字段）

alter table songinfo add id int primary key auto_increment after score;

插入新字段最前面

alter table songinfo add id int primary key auto_increment first;

插入布尔型

alter table post add essence bit;

重命名数据库表

rename table oldname to newname;

重置id

alter table news auto_increment=1;

 修改字段名

alter table groupcategory change title grouptitle varchar(10);

修改字段类型

alter table t_hp_new modify column thumb blob;

升序/倒序查询

select * from user order by id asc;

select * from user order by id desc;

显示版本
select version();

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

或者

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

复制数据库表到另一个表

create table gg_admin_upload select * from guessgame.gg_admin_upload;

查询用户

select user,host from mysql.user;

创建用户

create user '用户名'@'localhost' identified by '123456';

授权用户/授予用户权限

grant all privileges on 数据库名.* to '用户名'@'%';

grant all privileges on 数据库名.* to "用户名"@"localhost";

授权并创建用户

grant all privileges on 数据库名.* to '用户名'@'%' identified by '123456' with grant option;
统计数量

select count(ins_id) from test where ins_id=1;

 忘记密码

打开my.cnf

在[mysqld]里加入，skip-grant-tables

重启mysql,在输入密码的时候直接回车免密码登录；

运行 use mysql

update user set authentication_string=password('yourPassword') where user='root'

一些旧的数据库版本可能是：UPDATE user SET Password = password ( 'yourPassword' ) WHERE User = 'root' ;

在删除my.conf里面的skip-grant-tables

导出数据库

mysqldump -uroot -p密码 database_name>d:/database_name.sql

导入数据库

create database database_name

use database database_name

source /home/database_name.sql

保存图片为二进制数据流

 创建表：create table pic_bin(id INT PRIMARY KEY AUTO_INCREMENT,filename VARCHAR(100),data MEDIUMBLOB);

java存储代码

try {
                conn = JdbcUtil.connection();
                String sql1="insert into pic_bin(filename,data) values(?,?)";
                ps = conn.prepareStatement(sql1);
                ps.setObject(1,"a.jpg");

                InputStream in=new FileInputStream("C:\\Users\\Administrator\\Desktop\\test.jpg");
                ps.setObject(2,in);

                ps.execute();
            }catch (Exception e){
                e.printStackTrace();
            }finally {

java读取代码

try {
                conn = JdbcUtil.connection();
                String sql1="select filename,data from pic_bin where id=?";
                ps = conn.prepareStatement(sql1);
                ps.setObject(1,"1");

                rs=ps.executeQuery();

                if(rs.next()){
                    String filename=rs.getString("filename");


                    InputStream in=rs.getBinaryStream("data");

                    OutputStream out=new FileOutputStream("f:\\"+filename);
                    out.write(in.readAllBytes());

                    out.close();
                }

 一些配置

Incorrect integer value: '' for column 'loginIP' at row 1

修改my.ini

;sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

重启即可

设置浮点数

update table_name set column=floor(1 + rand()*9);