修改一个数据库的字符集 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; (常用)