mysql直接对表操作_MySQL对表的操作

对表的整体操作

创建表

use mydb1;

create table employee(id int, name varchar(40),sex  varchar(2),birthday date,entry_date date,job varchar(40),salary decimal(8,2),resume text);

查看表

show tables;

show create table employee;

desc employee;

修改表

alter table employee add image blob;

alter table employee modify sex varchar(4);

alter table employee drop sex;

alter table employee character  utf8;

alter table employee change column name xingming varchar(40);

rename table employee to emp;

删除表

Drop table emp;

对表的细操作

插入数据：

Insert into employee

(id,name,sex,birthday,entry_date,job,salary,resume) values

(1, 'john', 'a', '1990-10-01', '2014-10-1', 'ceo',10000, 'hello');

insert into employee values(1, 'john', 'a','1999-09-09','1999-09-09','bbb',90,'aaaaa');

插入遇到的问题

ERROR 1300 (HY000): Invalid utf8 character string: '\xA1\xAFaaa\xA1\xAF'

解决办法：引号问题’ ’这种是GB2312 , '  '这种是utf-8

ERROR 1366 (HY000): Incorrect string value: '\xC4\xD0' for column 'sex' at row 1

解决办法：show variables like 'chara%';

set character_set_client=gb2312;

该处查看会显示乱码

解决方法：set character_set_results=gb2312;

修改表数据：

update employee set salary=10;

update employee set salary=10000 where name='john';

update employee set salary=salary+1000 where name='aaa';

删除表的记录

delete from employee where sex='null';

delete from employee;  //删除表的内容，但不删除定义，不释放空间

truncate table employee;//删除内容、释放空间，但不删除定义

Drop table emp;       //删除表的内容、定义和释放空间

查询表数据

select * from student;

条件查询

select * from student where name='王五';

select name,english from student;

select distinct english from student;

select name,(chinese+english+math) from student;

select name,(chinese+english+math)+10 from student;

select name as 姓名,(chinese+english+math)+10 as 总分 from student;  //别名

select name 姓名,(chinese+english+math)+10  总分 from student;

select * from student where english>'90';

select name from student where (chinese+english+math)>200;

select name from student where (chinese+english+math)> '200'  //这里用单引号也是可以的

select name from student where english>80 and english<90;

select name from student where English between 80 and 90;  //这句跟上面那句是一样的

select * from student where math in(89,90,91);

模糊查询

select * from student where name like '李_'; (2个字符)

select * from student where name like '李%';  (2个字符以上)

排顺查询

select name,math from student order by math;

select name 姓名,(chinese+english+math) 总分 from student order by (chinese+english+math) desc;

select name 姓名,(chinese+english+math) 总分 from student order by 总分 desc;

select * from student where name like '李%' order by (chinese+english+math) desc;

统计

select count(name) from student;

select count(*) from student;

select count(*) from student where (chinese+english+math)>250;

求和

select sum(chinese),sum(english),sum(math) from student;

select sum(chinese+english+math) from student;

求平均

select avg(chinese) from student;

select avg(chinese+math+english) from student;