MySQL基础语法总结

SQL语言

## 简介

1. MySQL: Oracle公司， 08年被Sun公司收购，09年Sun被Oracle收购，开源产品，原MySQL创始人们离开Oracle创办了MariaDB，市场占有率第一
2. Oracle：Oracle公司产品，闭源。 性能最高价格最贵，拉里埃里森，市场占有率第二
3. SQLServer：微软公司产品，应用在微软整套解决方案中，闭源，市占率第三
4. DB2: IBM公司产品，应用在自己完整解决方案中，闭源产品
5. SQLite： 轻量级数据库，安装包只有几十k。只提供了基础的正删改查操作，主要应用在嵌入式设备和移动设备中
一、SQL（Structured Query Language）:结构化查询语言
  SQL分为:
	数据查询语句（DQL）： select
	  用来查询所需要的数据，使用最广泛、语法最灵活、最复杂的语句
	数据操作的语句（DML）：insert update delete
	  用来改变数据库表中的数据
	数据定义语句（DDL）:create drop  alter
	  用来创建、删除、修改数据库或数据库对象
	事物控制语句（TCL）：commit rollback savepoint
	  用来创建用户数据库中数据的一致性
	数据控制语句（DCL）：create user grant revoke
	  用来创建用户、执行权限的授予和回收

二、MySQL数据类型
数值类型
（1）整型
    tinyint  1字节
    smallint  2字节
    mediumint 3字节
    int     4字节
    bigint 8字节
    如果使用无符号整数时，在数据类型后加unsigned
	如：score tinyint unsigned
（2）小数
  浮点数：float 4字节     double 8字节
   精确小数：decimal（len，p）  如： salary decimal（7，2）
alter table stu_ba add salary decimal(9,2);
字符串
char（n） 定长字符串   n 最大255
text(n) 变长字符串 最大65535 
varchar(n)  变长字符串 n的最大值和字符集有关
		（建议在0-255内用它，否则用text）
日期时间类型( 单引号 )
 date   3字节  ' YYYY-MM-DD' '1000-01-01' ～' 9999-12-31'
 time   3字节 'hh:ii:ss'  '-838:59:59'～'838:59:59'
 datetime 8个字节 'YYYY-MM-DD HH:II;SS'
		'1000-01-01  00:00:00' ～' 9999-12-31 23:59:59'
 timestamp默认值当前系统时间 最大2038年1月19

三、约束类型
（1）主键约束 primary key 非空 唯一   一个表中只能有一个主键
（2）唯一约束 unique  唯一
（3）非空约束 not null
（4）默认约束 default 
（5）检查约束 check --目前MySQL不支持检查约束
	age int check(age>=15 and age<=65)
 (6)外键约束 foregin key
	外键字段
	从表（子表）：外键所在的表
语法：constraint 约束名 foreign key（外键字段）eferences 主表(字段)
     -- 创建表时，一般先创建主表，再创建从表
(7)设置自增长字段语法
  字段名 数据类类型 auto_increment
  自增长字段的数据类型必须是整型 ，并且必须设置为主键

四、数据库语句
1. 数据库相关
- 查询所有 show databases; 
- 创建数据库create database db1 character set utf8/gbk;
- 查询详情 show create database db1;
- 删除数据库 drop database db1;
- 使用数据库 use db1;
2. 表相关
- 创建表：create table 表名（
	字段名 1  数据类型；
	字段名 2  数据类型；
	....
	字段名 4  数据类型；
）；
 create table t1(name varchar(10),age int)charset=utf8/gbk;
- 查询所有 show tables;
- 查询详情 show create table t1;
- 查询字段 desc t1;
- 添加字段(默认最后面添加)alter table t1 add gender varchar(10);
 在最前面 alter table t1 add gender varchar(10) first;
 在xxx之 后alter table t1 add gender varchar(10) after xxx;
- 删除字段 alter table t1 drop age;
- 修改字段 alter table t1 change 原名 新名 新类型;
- 修改表名 rename table t1 to t2;
- 删除表 drop table t1;

- 查询  select * from t1 where 条件;
- 修改  update t1 set xxx=xxxx where 条件;
- 删除  delete from t1 where 条件;
1.复制表结构 
	语法：create table 新表名 like 源表名；
     	带结构但是不带数据
2.使用select语句创建表
	create table 新表名 select语句;

五、导入sql文件
- Linux:
	source /home/soft01/桌面/emp.sql;
- windows:
	source d:/emp.sql;
select * from emp; 如果出现乱码执行 set names gbk;
六.DML语句
1.insert语句
     作用 向指定表中插入一行数据
    insert into 表名[(字段列表)] values(值列表);
  （1）  其中，值列表和字段列表的数量、类型、顺序必须一致
    缺省字段列表时，相当于提供包含表结构中所有字段的字段列表
- 插入  insert into t1(name,age) values(xxx,xxx),(xxx,xxx);
-  全表插入格式： insert into 表名 values(值1,值2,值3);
	insert into person values(1,'Tom',20);
-  指定字段格式： insert into 表名(字段1,字段2) values(值1,值2);
	insert into person(id,name) values(2,'Jerry');
-  批量插入：
 	insert into person values(3,'aaa',10),(4,'bbb',11);
  (2)使用insert...select语句插入结果集
   1)语法
    insert into 目标表名[(字段列表1)] 
          select 字段列表2 from 源表名 ...;
    其中，字段列表1和字段列表2的字段的数量和类型必须匹配
  示例
    将学生表student中的数据一次性插入stu1中
    insert into stu1 select * from student;
2. update语句
 作用 根据条件修改表中符合条件的数据行
   update 表名 set 字段名=新值[,字段名=新值,...]
       [where 条件表达式];
   1) 每人加5分(没有where子句的update语句)
    update exam set exam_score = exam_score+5;
   2) 成绩超过100，改为100(带有where子句的update语句)
    update exam set exam_score=100 where exam_score>100;
   3) 成绩处于[55,59]之间，改为60(多个条件)
    update exam set exam_score=60 
        where exam_score>=55 and exam_score<=59; 
   4) 更改多个字段
    update exam set exam_score=26,regular_score=60
        where stuno=4;
3. delete语句
    作用  根据条件删除表中符合条件的数据行
   delete from 表名 [where 条件表达式];
   -- 删除exam表中不及格的信息
   delete from exam where exam_score<60;
   -- 删除exam表中全部数据
   delete from exam;
4.truncate语句  
truncate table 表名
功能上等效于没有where字句的delete语句
##和delete 的区别
  (1)truncate 语句不能应用到主表
   (2)在自增长字段中 使用truncate截断表后，在次插入数据自增长字段的值恢复到初始值 
  (3)truncate 语句执行的操作不能回滚
七.select语句
1.1 select 字段列表--指定要检索的字段
from 表名--表或视图
[where 条件表达式]--用于指定数据行的过滤条件
 [group by 分组字段--根据分组字段把数据分成若干个组，并进行汇总统计  
    [ having 条件表达式]] --对分组后的结果进行筛选
       [order by 排序] --对结果集进行排序
  (1)使用select子句指定字段列表 
     * 数据源中全部字段
     表名.*  多表查询中，指定某张表中的全部字段
     表达式   表达式中可以包含算数运算、函数等
     表名.字段   多表查询时，指定某个表中的某个字段
   -1- 检索Mysql 的版本 服务器当前时间
    select version(),now();
   -2-别名
      字段或表达式 [as] 别名
   select version() 版本号,now() as 服务器时间;
1.2 语法
 	select *from student;
    (1) 使用表达式：
	select stu_no 学号,exam_score 成绩,exam_score*0.5 from score;
    (2) 使用distinct排重
	select distinct 字段列表 from 表名 ...;
    (3) 使用limit限制返回行数(实现分页)
	select 字段列表 from 表名
	  limit [start],length; 
    --start 表示低级行开始检索 默认为0 表示第一行
    --length 表示要检索的行数
1.3 多表查询
   一. 内连接：符合连接条件的数据行被检索出来，不符合连接条件的被过滤掉
	select 字段列表
      from 表1 [inner] join 表2 on 连接条件; 
  （1）在AB表中加入两条数据 在次查询新增数据没有关联
  （2）表的别名   表名[as]别名
SELECT class_name 班级 ,student_no 学号 ,student_name 姓名 FROM classes 
JOIN student
ON classes.classs_no=student.class_no;
***如果关联的两张表中，字段名没有重名的，可以省略字段名前的表名或别名
   重名字段前必须加表名或别名修饰
 练习：列出教师及其所授课程的信息，包括教师的工号、姓名、课程名和人数
SELECT t.teacher_no,t.teacher_name,c.course_name,c.up_limit
FROM course c 
JOIN teacher t 
ON t.teacher_no=c.teacher_no;
  (3)三表内连接
	select 字段列表
	from 表1 join 表2 on 关联条件
	 join 表3 on 关联条件2；
SELECT s.student_name,s.student_no,c.classs_no,cl.score
FROM  student s 
JOIN classes c 
ON s.class_no=c.classs_no 
JOIN choose cl
ON cl.student_no = s.student_no ;
  二. 外连接：外连接的结果集 = 内连接的结果集 + 匹配不上的数据行
  (1)左外连接
      左外连接的结果集 = 内连接的结果集 + 左表中匹配不上的数据行
  select 字段列表from 左表 left [outer] join 右表 on 连接条件;
  (2) 右外连接
      左外连接的结果集 = 内连接的结果集 + 右表中匹配不上的数据行
 select 字段列表 from 左表 right [outer] join 右表 on 连接条件;
  (3)全外连接
   全外连接的结果集 = 内连接的结果集 + 两表中匹配不上的数据行
 select 字段列表  from 左表 full [outer] join 右表 on 连接条件;
	--Mysql 不支持此语法
3.where 子句
  作用:根据条件筛选数据行 复合条件的数据行被筛选出来，不符合条件的被过滤掉
  where 条件表达式	
  3.1 比较运算符
	>   <    >=   <=	=   !=(<>)
  3.2 使用where子句实现内连接
   select 字段列表 from 表1,表2  where 连接条件;
	eg:
SELECT s.student_no,s.student_name,c.score 
FROM student s JOIN choose c 
ON s.student_no = c.student_no;
       等效于
SELECT s.student_no,s.student_name,c.score
FROM student s,choose c 
WHERE s.student_no=c.student_no;
 3.3 SQL提供的运算符
   (1) between and
    作用：判断一个字段或表达式是否处于给定的闭区间
      字段或表达式 between 值1 and 值2
SELECT s.student_no,s.student_name,c.score 
FROM choose c JOIN student s 
On s.student_no = c.student_no 
WHERE score  
between 70 and 90;
   (2)in
    作用:判断字段或表达式是否出现在给定的列表中
  where 字段或表达式 in()
SELECT s.student_no,s.student_name,c.score 
FROM choose c JOIN student s 
On s.student_no = c.student_no 
WHERE score  
IN (70,90);
   (3)like  -- 模糊查询
   作用判断字段或表达式的值是否符合给定的模式(格式)
     where 字段或表达式 like '模式'
   '模式'中的通配符
     %       任意长度的任意字符
     _       一个任意字符
 	1. 以x开头      x%
	2. 以x结尾      %x
	3. 包含x        %x%
	4. 第二个字符是x   _x%
	5. 倒数第三个字符是x   %x__
	6. 以x开头倒数第二个是y    x%y_
SELECT  student_name FROM student WHERE student_name like '%三%';
SELECT  student_name FROM student WHERE student_name like '_三%';
### 匹配的模式中包含通配符(%  _),必须进行转义
   a:使用\
	SELECT  student_name FROM student WHERE student_name like '三\_%';
   b:使用 escape自定义转义字符
    select table_name from information_schema.tables
      where table_name like 'user#_%' escape '#';
   (4)is null  is not null 
      作用：判断表达式是否为空
	where 字段或表达式 is null
select *from student where class_no is not null;
    (5)逻辑运算符 and  or  not(!)
     特殊用法 使用where子句实现三表连接
	select 字段列表 from 表1，表2，表3 
	   where 关联条件1 and 关联条件2;
	eg:  in(20,30) 等效于 20 or 30
	在in（1，2，null)可以   在not in（1，2）中不可以含null值
    (6) 运算符取反：
  <                    >=
  >                    <=
  =                    !=
  between ..and        not between ... and
  in                   not in(注意NULL值)
  like                 not like
  is null              is not null         

##排序 order by
- 格式： order by 字段名 asc/desc 升序/降序
- 多字段排序
1. 查询员工的姓名，工资和部门编号 按照部门编号升序排序，如果部门编号相同则按照工资降序排序
	select ename,sal,deptno from emp order by deptno,sal desc;

###聚合查询（聚合函数）
- 对查询的多条数据进行统计查询    平均值 最大值 最小值 求和 计数
1. 平均值 avg(字段名)
2. 最大值 max(字段名)
3. 最小值 min(字段名)
4. 求和sum(字段名)
5. 计数count(字段名)
 查询程序员人数
select count(*) from emp where job='程序员';
####分组查询
- 格式： group by 字段名
- 以某个字段相同值为一组进行统计查询
1. 查询每个部门的平均工资
	select deptno,avg(sal) from emp group by deptno;
###having 
- where后面只能写普通字段的条件，如果有聚合函数的条件需要使用having
- having要结合group by使用  
- 各个关键字的顺序：	
select ..... from 表名  where .... group by .... having ..... order by ......limit .....;
1. 查询每个部门的平均工资 只查询平均工资大于2000的部门 
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
2. 查询每个部门的平均工资，只查询工资在1000到3000之间信息，并且过滤掉平均工资低于2000的部门
select deptno,avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000;