数据库基本操作：
DDL:
create database db1 character set utf8 collate utf8_general_ci;
show character set;
show collation;
show create database db1;
show databases;
drop database db1; 删除db1
use db1; 使用db1，进行操作
select database(); 显示正在使用的数据库
alter database db2 character set gbk; 改变db2的字符编码为gbk
制表:
java:byte short int long float double char String boolean
sql: tinyint smallint int bigint float double char(1),varchar(1) char(2),varchar(2) bit(0) =false;
blob(字节流) text(字符流)
create table employee (
id int,
name varchar(20),
gender varchar(10),
birthday date,
entry_date date,
job varchar(100),
salary float,
resume varchar(255)
);
show tables;
desc employee; 查看表结构
show create table employee;
drop table employee;
-- 主键约束，非空唯一且自动增长，一个表中只能有一个主键约束
create table employee2 (
id int primary key auto_increment,
-- age int primary key,
name varchar(20) not null,
salary float unique
);
alter table employee2 add (gender varchar(10), grade int not null);
alter table employee2 modify grade int;
alter table employee2 drop grade;
alter table employee2 change name username varchar(10) not null;
alter table employee2 character set utf8;
DML: select * from employee2;
insert into employee2 (id,username,salary,gender)
values(1,'dkan',323,'boy');
insert into employee2 values(3,'ahaha',1233,'boy');
show variables like 'character%';
update employee2 set gender='girl';
update employee2 set gender='girl' where username='dkan';
update employee2 set salary=salary+1000 where username='dkan';
delete from employee2;
delete from employee2 where username='ahaha';
truncate employee2;
DQL: 查询操作，并不会改变数据内容,只是显示出来而已,不同命令按不同方式显示出来
书写顺序： select from where group by having order by;
s f w g h o
解析顺序： f w g h s o
select * from employee2;
select distinct name from employee2;
select id,name from employee2;
select id,id+name as ids from employee2;
select * from employee2 where id=1;
select * from employee2 where id >=1 and id <=2;
select * from employee2 where id between 1 and 2;
select * from employee2 where id in(1,2,3);
select * from employee2 where name like 'd%'; %是通配字符,0个或多个任意字符; _代表1个字符 and优先级高于or
select * from employee2 order by id desc; descend 降序 ascend 升序 默认是升序
select count(*) from employee2 where salary>1000; 统计salary>1000的个数
select sum(math) as 数学总分, sum(chinese) 语文总分 from employee2;
select sum(math)/count(*) 数学均分 from employee2;
select avr(math) from employee2;
select max(english),min(english) from employee2;
//--------------------------------------------------
create table orders(
id int,
product varchar(20),
price int
);
insert into orders values(1,'电视',900);
insert into orders values(2,'洗衣机',100);
insert into orders values(3,'洗衣粉',90);
insert into orders values(4,'橘子',9);
insert into orders values(5,'洗衣粉',90);
select * from orders group by product desc;
select * from orders group by product having sum(price)>100;
select product,sum(price) from orders group by product having sum(price)>100; 购买了几类商品,且每类价格>100
where 在分组前进行操作,后面不可以接sum等聚合函数，having在分组后进行操作.
