修改一个数据库的字符集
alter database 数据库名 character set 字符集名;
修改一个数据库的校对规则
alter database 数据库名 collate 校对规则名;
删除一个数据库
drop database 数据库名;
练习:
1.将数据库mydb2的字符集改成utf8
2.将数据库mydb3的校对规则改成utf8_bin
3.删除mydb1数据库
创建数据表
create table 数据表名(
列名 列类型,
列名 列类型,
列名 列类型
);
MySQL中的数据类型:
MySQL中的数据类型和Java中的数据类型并不是完全一一对应的关系.
数值类型
tinyint
smallint
MEDIUMINT
int (最常用)
bigint
时间和时间戳的区别:
date:只包含日期,如果传入数据的时候,没有指定值,将是null
timestamp:包含日和时间,如果传入数据时,没有指定值,将是当前的系统时间
字符串类型:
char:(character)定长,例如:指定长度为100,但是实际传入的值只有20,剩余的字符用空格补全.
varchar:(variable character):可变长度,例如:指定长度为100,但是实际传入的值只有20,真实存储的就是20个字符
创建一个没有约束的员工表:
create table employee(
id int,
name varchar(20),
gender varchar(10),
birthday date,
entry_date date,
job varchar(50),
salary double,
resume text
);
查看表信息:
show create table employee;//显示建表信息
desc employee;//格式化显示
单表约束:
非空约束:not null
name varchar(20) not null
唯一约束:unique
id int unique
在MySQL中,唯一列,允许有null值,null并不等于null
主键约束:primary key
相当于非空约束 + 唯一约束
uid int primary key auto_increment
auto_increment //表示此列的值如果不传入的话,系统自动赋值,在前一条记录基础上加1(通常用在int型主键上)
练习:
创建表employee2,加上各种约束
create table employee2(
id int primary key auto_increment,
name varchar(10) not null,
gender varchar(10) not null,
birthday date not null,
entry_date date not null,
job varchar(50) not null,
salary double not null,
resume text
);
删除表:
drop table tbl_name;
修改表:
添加列:
alter table employee2
add email varchar(20) not null;
删除列:
alter table employee2
drop email;
修改列:
alter table employee2
modify job varchar(40);
alter table employee2
change salary money float not null;
修改表名:
alter table employee2
rename to emp;
rename table emp to employee2;
alter table employee2 drop money;
alter table employee2 drop resume;
插入数据到数据表
insert into employee2 (id,name,gender,birthday,entry_date) values(1,'tom','male','2010-10-10','2018-3-4');
把一个表中的所有列都显示出来
select * from employee2;
insert into employee2 (id,name,gender,birthday,entry_date,job) values(2,'toms','male','2000-10-10','2018-2-5','boss');
当插入的列是一个表的所有列的时候,此时有简化写法:
insert into employee2 values(3,'toms','male','2005-10-10','2018-2-10','clear');
insert into employee2 values(4,'toms','male','2005-10-10','2018-2-10');//列数量不匹配
insert into employee2 values(null,'toms','male','2005-10-10','2018-2-10',null);//主键列可以传null,系统自动维护
//删除数据(记录)//删除表:drop 删除列:drop
delete from 表名 [where id = 2];
delete from employee2 where id = 2;
delete from employee2 ;
更新数据:update
update 表名 set 列名 = 值 [where记录过滤条件];
update employee2 set gender = 'female';
update employee2 set gender = 'male' where id = 1;
查询:select
select 列名1,列名2... from 表名;
select * from emp;
select id,name from emp;
中文乱码
insert into employee2 values(null,'张飞','男','2010-10-10','2018-2-10',null);
第一种解决方法:
临时:
mysql --default-character-set=gbk -uroot -proot;
修改配置文件
my.ini中修改
default-character-set=gbk
重启服务生效
更新练习:
练习:
1.将tom的id改成50
update employee2 set id = 50 where name = 'tom';
2.将tyson的id改成60,job改成HR
update employee2 set id = 60,job = 'HR' where name = 'tyson';
3.将toms的salary在原有基础上增加1000
update employee2 set salary = salary + 1000 where name = 'toms';
4.把所有人的salary增加500
update employee2 set salary = salary + 500;
truncate和delete的区别
truncate相当于delete不加where控制条件(清空表)
truncate的操作是:先把表删除(drop table ...),然后重新创建一个一样的表.
delete是一条一条数据的删除
练习:
1.删除表中id=2的记录
delete from employee2 where id = 2;
2.删除表中所有记录
delete from employee2 ;
3.使用truncate删除所有记录
truncate employee2;
查询操作
select * from employee;
select name , id from employee;
select name ,id from stu where id > 3;//把id>3的记录的name和id列的值显示
select distinct name from stu;
create table stu(
id int primary key auto_increment,
name varchar(10),
ch int not null,
math int not null,
team varchar(10)
);
insert into stu values(null,'tom',70,70,'first');
insert into stu values(null,'tom',80,80,'first');
insert into stu values(null,'toms',90,90,'first');
insert into stu values(null,'tyson',70,70,'second');
insert into stu values(null,'tyson',80,80,'second');
insert into stu values(null,'toy',90,90,'second');
insert into stu values(null,'tyson',70,70,'thrid');
insert into stu values(null,'tyson',80,80,'thrid');
insert into stu values(null,'toy',90,90,'thrid');
//查询出的列可以进行运算
select math + 10 from stu;
//别名
select math + ch as sum from stu;
select math + ch sum from stu;//as 可以省略
select * from stu where id > 3;
select * from stu where id <= 5;
select * from stu where id != 5;
select * from stu where id <> 5;
//选择一组和二组的记录
select * from stu where team in('first','second');
select * from stu where team = 'first' or team = 'second';
//区间
select * from stu where ch >= 70 and ch <= 85;
select * from stu where ch between 70 and 85;
//模糊查询
select * from stu where name like 'to%';
%占位符,表示任意多个字符
select * from stu where name like 'to_';
_占位符,表示一个任意字符
select * from stu where name like '%m%';
练习:
1.查询成绩在80-90之间的记录:between and
select * from stu where ch between 80 and 90;
2.查询70,90分的记录
select * from stu where ch in(70,90);
3.查询所有姓张的同学的记录
insert into stu values(null,'张三',80,90,'first',null);
insert into stu values(null,'张三丰',80,90,'first',null);
insert into stu values(null,'张飞',80,90,'first',null);
select * from stu where name like '张%';
4.查询所有名字为两个字的记录
select * from stu where name like '__';
//排序order by
select name,ch from stu order by ch asc;//按照ch列升序排序,asc可以省略
select name,ch from stu order by ch;
select name,ch from stu order by ch desc;
练习:(使用测试数据的表stu)
1.查询各个学生总成绩,并按总分从高到底排序
select ch + math from stu order by ch + math desc;
select ch + math sum from stu order by sum desc;
2.查询学生成绩,先按ch升序,ch相同,按math降序排序
select * from stu order by ch asc, math desc;
3.将所有姓张的同学的ch成绩降序排列
select ch from stu where name like '张%' order by ch desc;
聚合函数
count
查询某列有多少行?
select count(name) from stu;
select count(*) from stu; //查询表中的记录条数,不论某列是否有null值
查询stu中math成绩>80的记录条数
select count(*) from stu where math > 80;
查询stu中总成绩>150的记录条数
select * from stu where (ch + math) > 150;
select count(*) from stu where (ch + math) > 150;
sum
查询stu中math的总成绩
select sum(math) from stu;
查询各科总成绩
select sum(ch),sum(math) from stu;
查询整个班级总成绩
两种实现方式:
1.先求出个人的总成绩,再加起来
select sum(ch + math) from stu;
select sum(ifnull(ch,0) + ifnull(math,0)) from stu;
2.先求出整个班级单科总成绩,在加起来
select sum(ch) + sum(math) from stu;
当记录中某列有null值,进行算术运算,结果都为null.
如果使用聚集函数,就会把null值当0
解决办法:使用ifnull函数
查询math的平均分
select sum(math) / count(*) from stu;
查询math的平均分
select avg(math) from stu;
查询整个班级的平均分
select avg(ifnull(math,0) + ifnull(ch,0)) from stu;
//select avg(sum(ch) + sum(math)) from stu; 出错!!!
求math最高分
select max(math) from stu;
求ch最低分
select min(ch) from stu;//排除null值
create table orders(
id int,
product varchar(20),
price double
);
insert into orders values(1,'洗衣机',900);
insert into orders values(2,'洗衣机',900);
insert into orders values(3,'电视',700);
insert into orders values(4,'电视',900);
insert into orders values(5,'吹风机',20);
insert into orders values(6,'游戏机',9);
//求每种产品的销售总额
select product , sum(price) from orders group by product;
select product , sum(price) as sum from orders group by product order by sum desc;
//对分组后的数据再次进行过滤 (where)
select product , sum(price) as sum from orders group by product where sum > 1000;//不能用where进行过滤
select product , sum(price) as sum from orders group by product having sum > 1000;
对group by分组之后的结果再次进行过滤,使用关键字having
统计单价在100以上的产品销售总额大于1000的,降序排序
select product,sum(price) sum from orders where price > 100 group by product having sum > 1000 order by sum desc;
s f w g h o
笛卡尔积
表A 表B
1 aa1 1 bb1
2 aa2 2 bb2
3 bb3
select * from a,b;
1 aa1 1 bb1
1 aa1 2 bb2
1 aa1 3 bb3
2 aa2 1 bb1
2 aa2 2 bb2
2 aa2 3 bb3
create table dept(
did int primary key auto_increment,
dname varchar(20)
);
create table emp(
eid int primary key auto_increment,
ename varchar(20),
dno int
);
insert into dept values(null,'财务部');
insert into dept values(null,'研发部');
insert into dept values(null,'后勤部');
insert into emp values(null,'toms',1);
insert into emp values(null,'tyson',1);
insert into emp values(null,'tom',2);
insert into emp values(null,'lucy',3);
insert into emp values(null,'lily',4);
//内连接
select * from emp inner join dept on emp.dno = dept.did;
select * from emp,dept where emp.dno = dept.did; (常用)
