mysql基本操作

仅供和我一样曾经痛苦挣扎过的人儿参考。

一、入门
1.create database if not exists it_one character set utf8 collate utf8_general_ci;
2.show create database it_one;
3.show databases;
4.drop database it_one;
5.修改数据库的字符集和校对规则，注意不能修改数据库名称
 alter database it_one character set gb2312;
6.备份并恢复数据库(注意，数据库恢复时，不能恢复数据库，只能恢复数据库中的数据)
 备份:mysqldump -uroot -p it_one>e:/1.sql     (window 命令)
 恢复(1):source e:/1.sql  (sql 命令)
 恢复(2):mysql -uroot -p it_one<e:/1.sql (window命令)
 
7.修改表结构
 (1)添加列
  alter table mytable add name varchar(10);
 (2)删除列
  alter table mytable drop name ;
 (3)修改列
  alter table mytable modify name varchar(40);
 (4)修改表名
  rename table mytable to user;
 (5)修改表的字符集
  alter table user character set utf8;
 (6)修改表名
  alter table user change column id stu_id int;
  
 create table employee(
  id int,
  name varchar(20),
  gender varchar(10),
  birthday date ,
  salary float,
  hireddate date,
  resume text
 ); 
  
二、CRUD
1.Insert  
 (1)insert into employee(id,name,gender,birthday,salary,hireddate) values(1,'aaa','male','1980-09-09',1000,'1985-02-02') ;
  insert into employee(id,name,gender,birthday,salary,hireddate) values(2,'张三','male','1980-09-09',1000,'1985-02-02') ;
 
 (2)查看变量
  show variables like 'character%';
 (3)修改变量
  set character_set_client = gbk;      (告诉数据库客户端的编码是gbk)
  set character_set_results = gbk; (显示时，用gbk显示结果)
  set names gbk;
2.update (注意:更新时留意where条件，否则会全部更新)
 (1)alter table employee add job varchar(20);
 (2)update employee set salary=5000,job='abc' where name='aaa';
 (3)update employee set salary=salary+1000 where name='aaa';
 (4)update employee set gender=null where 1;
3.delete(truncate) 
 (1)delete from employee where name='张三'; (删除每条记录)
 (2)delete from employee ;         (删除所有记录)
 (3)整个表删除，先摧毁表，再重建表结构，数据多时可以选用，效率高于delete(一条一条删除),但是不能删除某条记录
  truncate employee;
  
4.select
  准备:
  create table student(
   id int,
   name varchar(20),
   chinese float,
   english float,
   math float
  );

  insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
  insert into student(id,name,chinese,english,math) values(2,'李进',67,98,56);
  insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
  insert into student(id,name,chinese,english,math) values(4,'李一',88,98,90);
  insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
  
  (1)select * from student;
  (2)select id,name from student;
  (3)select distinct english from student;   select distinct name,english from student;(name和english都要一样才过滤)
  (4)select name,english+10 as english from student;
  (5)select name as 姓名,(math+english+chinese) as 总分 from student;
  
  where字句
  (6)select * from student where name = '李进';
  (7)select * from student where english >90;
  (8)select * from student where (math+english+chinese) > 250;
  (9)select * from student where english >80 and english <90;
  (10)select * from student where english =78 or english =98;
     select * from student where english in(78,98);
  (11)select * from student where name like '李_';
     select * from student where name like '李%';
     
  order by字句
  (12)select * from student order by math desc;
     select name as 姓名,(math+chinese+english) as 总分 from student order by 总分 desc;
     select * from student where name like '李%' order by math desc;
     
  group by字句
  (13)select * from orders group by product;
  (14)select product,sum(price) from orders group by product;
  
  having字句：having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。
  (15)select * from orders group by product having sum(price) >100;

     
三、函数
 1.count()
    (1)select count(*) as总人数 from student ;
    (2)select count(*) as 总人数 from student where math >=90;
    (3)select count(*) as 总人数 from student where (math+chinese+english) >250;
    
  2.sum()
    (1)select sum(math) from student;
    (2)select sum(math),sum(chinese),sum(english) from student;
    (3)select sum(chinese+math+english) from student;
    (4)select sum(chinese)/count(chinese) from student;
    
  3.avg
    (1)select avg(math) from student;
    (2)select avg(math+chinese+english) from student;
    
  4.Max()、Min()
  (1)select Max(chinese),Min(chinese) from student;
  
  
四、表的约束
  1.主键约束
  create table demo1(
   id int primary key,
   username varchar(10),
   password varchar(10)
  );
  
  insert into demo1(username) values('aaa');
  
 2.自动增长约束
  create table demo2(
   id int primary key auto_increment,
   username varchar(10),
   password varchar(10)
  );
  insert into demo2(username) values('aaa');
  
 3.唯一约束
  create table demo3(
   id int primary key auto_increment,
   username varchar(10) unique,
   password varchar(10)
  );
  
  insert into demo3(username) values('aaa');
 
 4.非空约束
  create table demo4(
   id int primary key auto_increment,
   username varchar(10) unique not null,
   password varchar(10)
  );
 
 5.外键约束
  create table husband(
   id int primary key ,
   name varchar(10)
  );
  
  insert into husband values(1,'aaa');
  
  create table wife(
   id int primary key,
   name varchar(10),
   husband_id int ,
   constraint husband_id_FK foreign key (husband_id) references husband(id)
  );
  
  insert into wife values(1,'bbb',1);
  
  
五、表的设计
 (1)一对一
  create table person(
   id int primary key,
   name varchar(20)
  );
  
  create table idCard(
   id int primary key,
   address varchar(20),
   constraint idCard_fk foreign key(id) references person(id)
  );
  
  
 (2)多对一，一对多(看的角度不同)
  一个部门对应多个员工，多个员工对应一个部门
  create table department(
   id int primary key ,
   name varchar(20)
  );
  
  create table employees(
   id int primary key ,
   name varchar(20),
   constraint department_id_fk foreign key(id) references department(id)
  );
  
  alter table employees modify id int primary key;
  
 (3)多对多,利用中间表
  
  create table student(
   id int primary key,
   name varchar(20)
  );
  
  中间表
  create table student_teacher(
   student_id int ,
   teacher_id int ,
   primary key(student_id,teacher_id),--联合主键
   constraint student_id_fk foreign key(student_id) references student(id),
   constraint teacher_id_fk foreign key(teacher_id) references teacher(id)
  );
  
  create table teacher(
   id int primary key,
   name varchar(20)
  );