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);
