mysql中的SQL语句

mysql基本语句:

• mysql -u root -p --登入语句回车后输入密码即可登入

• Show databases; --浏览数据库目录

• Show tables; --浏览某一个数据库中的所有表目录

• show create database 库名; --查看建库时的语句（并验证数据库库使用的编码）

• desc 表名； --查询建表语句

• use database; – 数据库名;进入某个数据库

• create database 数据库名 charset utf8; --创建数据库,且定义编码类型为utf-8,可以预防中文乱码

• create table 表名(
  列名 数据类型, --创建数据库中的表
  列名 数据类型,
  …
  );

• drop database if exists 库名; --删除数据库

• drop table 表名; --删除库中的表

drop、delete、truncate之间的区别？

drop用于删除库和表，不能用于删除表记录
delete和truncate是用于删除表记录，不能用于删除库和表本身
delete用于删除表记录，可以删除表中的某一部分记录，也可以删除表中的所有记录，而且删除时，是一条一条删除。
truncate也是用于删除表记录，只能删除所有记录，删除时不是一条一条删除，而且将整张表摧毁重建

一、增删改SQL

插入记录：insert into 表名(列1，列2，列3…) values(值1，值2，值3…);
简化版: insert into 表名values(值1，值2，值3…);

删除数据: delete from 表名 where [where条件] ;

例子:delete from teacher where name="大锤";
	delete from teacher where id>4;

修改数据: update 表名 set 列=值,列=值,列=值…;
update 表名 set 列=值,列=值[where条件];

例子: update teacher set score=88 where name="海涛";

二、基本查询

1. where 条件查询
   格式:select 要查询的列 from 表名 where 条件;

   条件可以是赋值(id=2),比较(sal>2000),当有多个条件可以用and或者or,但是不能用别名.

2. 查询某几个数据
   select 要查询的列 from 表名where 列名 in(具体值,1具体值2, . . .);

   例子:查询emp表中薪资为 1400、1600、1800的员工，显示员工姓名和薪资
   select *from emp where sal  in(1400,1600,1800);
   
   例子:查询薪资不为1400、1600、1800的员工
   select *from emp where sal not in(1400,1600,1800);
   

3. 查询在某一段区间的数据
   select 要查询的列 from 表名where 列名 between . . . and . . .;

   例子:查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。
   select name,sal from emp where sal not between 2000 and 4000;
   

4. Like模糊查询
   /* like进行模糊查询，"%" 表示通配，表示0或多个字符。"_"表示一个任意的字符 */

   查询emp表中姓名中以"刘"开头的员工，显示员工姓名。
   	select * from emp where name like '刘%';	
   	
   查询emp表中姓名中包含"涛"员工，显示员工姓名。
   	select * from emp where name like '%涛%';
   	
   查询emp表中姓名以"刘"开头并且姓名为2个字的员工，显示员工姓名。
   	select * from emp where name like '刘_';
   

三、分组查询、聚合函数、排序查询

• 分组查询
  格式: select 查询的列 from 表名 group by 列名;

  聚合函数:放在放在要查询的列里

  • Count(列名|*)
  • Max(列名)
  • Sum(列名)
  • Avg(列名) 求平均
  • ifnull(列名,值):处理列中的null值

  	例子:查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。
  	select name,sal,ifnull(bonus,0) from emp where sal>3000 or ifnull(bonus,0)<600;
  

2. 排序查询
   格式: select 查询的列 from 表名order by 排序的列 asc或者默认; 为升序
   select 查询的列 from 表名order by 排序的列 desc; 为降序

四、索引

1. 索引分类:

   • 主键索引: 不能重复 如id 不能为null.
   • 唯一索引: 不能重复 如 表单中的选填项email: 可以为null.
   • 单值索引： 单列， age ;一个表可以多个单值索引,name,grade等。
   • 复合索引： 多个列构成的索引（相当于二级目录 z: zhao） (name,age) (a,b,c,d,…,n)

2. 创建索引：

   • 在建表时创建:
     create table stu(
     id int primary key,–主键
     id int primary key auto_increment,–主键自增
     …
     );

     create table user(
     email varchar(50) unique ,–唯一
     …
     );

     create table stu(
     gender varchar(2) not null,–非空
     …
     );

   2. 在建表后添加创建:**

      1. 方式一：
         create 索引类型 索引名 on 表(字段)
         单值：
         create index dept_index on student(dept);
         create primary key id_index on student(id);
         唯一：
         create unique index name_index on student(name) ;
         复合索引
         create index dept_name_index on student(dept,name);

      2. 方式二：alter table 表名 索引类型 索引名（字段）

         单值：
         alter table student add index dept_index(dept) ;
         唯一：
         alter table student add unique index name_index(name);
         复合索引
         alter table student add index dept_name_index(dept,name);

         注意：如果一个字段是primary key，则改字段默认就是 主键索引

         建表时创建主键:

         • create table stu(
           id int primary key,–主键
           id int primary key auto_increment,–主键自增
           …
           );

         建表后添加主键:

         - alter table student add primary key id_index(id);
         alter table student add primary key (id);

3. 删除索引：
   删除一般索引:
   drop index 索引名 on 表名 ;
   drop index name_index on student;
   删除主键索引:
   alter table 表名drop primary key ;
   alter table student drop primary key ;

4. 查询索引：
   show index from 表名 ;
   show index from 表名 \G

四关联查询、外连接查询

准备一张班级表格:
Create table class(id int primary key,teacherName varchar(50));
添加数据
这段粘贴复制时,张老师字段会报错,原因:复制后会出现空格,标点等格式的错误解决方法:自己重写一遍’张老师’即可
Insert into class values(1,’张老师’);
Insert into class values(2,’王老师’);
Insert into class values(3,’李老师’);

1. 创建外键

   1. 建表时创建外键:
      create table student(
      id int primary key auto_increment, – 学生编号
      name varchar(20), – 学生姓名
      Class_id int , – 班级编号
      foreign key(class_id) references class(id) – 指定外键
      );
   2. 建表后创建:
      alter table student add foreign key(class_id) references class(id) ; --指定外键

2. 删除外键:

   1. show create table 数据表名;

   2. 找到CONSTRAINT 外键名 FOREIGN KEY (xxxx) REFERENCES xxxxxx (id)

   3. alter table drop foreign key 外键名;

      错误写法示范:
      alter table drop foreign key class_id; --class_id不是外键

3. 关联查询
   select * from student,class where class.id=class_id;
   
   (1) 左外连接查询：是将左边表中所有数据都查询出来, 如果在右边表中没有对应的记录, 右边表显示为null即可。
   select * from class left join student on class.id=class_id;

   (2) 右外连接查询：是将右边表中所有数据都查询出来, 如果在左边表中没有对应的记录, 左边表显示为null即可。
   select * from class right join student on class.id=class_id;

五、子查询、多表查询

• 子查询:查询语句里面还有查询语句(可以是一句或者多句).
  select *from student where class_id=(select id from class where teacherName=’张’);

• 多表查询:联合多张表查询.
  Select student.name ,class.teacherName from student,class where class.id= student.class_id;
  

补充:
SQL语句编写过程顺序：
select dinstinct …from …join …on …where …group by …having …order by …limit …

SQL语句解析过程顺序：
from … on… join …where …group by …having …select dinstinct …order by limit …
where和having都用于筛选过滤，但是：
(1) where用于在分组之前进行筛选, having用于在分组之后进行筛选
(2) 并且where中不能使用列别名, having中可以使用别名
(3) where子句中不能使用列别名(可以使用表别名), 因为where子句比select先执行!!