sql常用命令

sql，用于操作关系型数据库数据，–增删改查，sql最早是由ansi提出，后交给ISO，最终成为数据库行业标准语言，分为多个版本：sql87、sql92、sql99等。sql由绝大多数的关系型数据库支持。sql分为两种方式，交互模式（客户端输入一行点击回车，服务器执行一行。临时性的查看数据）和脚本模式（客户端要执行的命令编写在一个文本文件中一次性的提交给服务器）适用于批量在增删改查。
MySQL –uroot < c:/xxx/01.sql 回车
sql语法规范
每条语句跨越多行，必须以英文分号结束。见分号结束。
若第N条语句处错误，及所在的及后边所有语句都不会执行，
sql命令不区分大小写，关键字都用大写，非关键字都用小写。
sql命令中使用注释，分为单行#，多行注释：/* …*/。
日常开发sql命令
丢弃指定数据库如果存在的话，
drop database if exists tedu;
(2)创建新的数据库
create database tedu;
(3)进入创建好的数据库
USE tedu;
(4)创建数据表
CREATE TABLE student(
sid INT, #integer
name VARCHAR(8), #variable charactor
gender VARCHAR(1),#M/F
score INT
);
(5)插入数据
INSERT INTO student VALUSE(‘1’,’tom’,’M’,’65’);
(6)查询数据
SELECT * FROM student;
(7)删除数据表中的数据
DELETE FROM student WHERE sid=2;
SQL语句的分类：
1 DDL：Data Define Language 定义数据
CREATE/DROP/ALERT(修改)/TRUNCATE(截断)
2 DML：data manipulate language 操作数据
Insert/delete/update
3 DOL:data query language 查询数据
Select
4 DCL：data control language 控制用户权限
GRANT(授权)/REVOKE(收权)
计算机中如何存储英文字符
ASCll：总共有128个，对所有英文字符以及标点符号进行了编码。
Latin-1：总共有256个，兼容ASCll，同时对欧洲符号进行编码。Mysql默认使用latin-1编码。
计算机如何存储中文字符
GB2312：对常用的六千多个中文字符进行了编码，兼容ASCll码。
GBK：对2万多个汉字行了编码，兼容ASCll码。
BIG5：台湾 繁体中文进行了编码，兼容ASCll码。
Unicode：对世界上主流语言常用的字符进行了编码，，兼容ASCll码，不兼容GB2312.GBK，BIG5等系列。具体分为UTF-8，UTF-16，UTF-32等存储方案。
乱码产生的原因
Mysql中默认使用Latin-1编码，而Latin-1不存在中文编码。
解决MySQL数据库中乱码三个地方统一为UTF-8
（1）.sql脚本文件另存为UTF-8编码
（2）客户端连接服务器端使用的编码为UTF8
（3）服务器端，在创建数据库是存储所使用的编码为UTF8
列类型
创建数据表的时候，指定的列可以存储的数据类型
CREATE TABLE student（name 列类型）
（1） 数值类型 可以不加引号
TINYINT 微整型 占一个字节 存储范围-128~127
SMALLINT 小整型 占二个字节 存储范围-32768~32767
INT 整型 占4个字节 存储范围-2147483648~2149483647
BIGINT 大整型 占8个字节 存贮范围
FLOAT（M，D）单精度浮点型，占四个字节，最大是3.4E38，存储范围远比INT大，可能产生四舍五入。M表示总的有效位数（不包含小数点），D表示小数点后边有多少位。
DOUBLE（M,D）双精度浮点型，占8个字节比BIGINT存储范围大的多。
DECIMAL（M,D）定点小数，不会产生计算误差。 999999.99 DECIMAL（8,2）
Bool 布尔型，只能是TRUE（1）和FALSE（0）两个值。
TB GB MB KB BYTE BIT 1BYTE=8BIT
（2） 日期时间类型 必须添加引号
DATE日期类型 2018-08-02
TIME时间类型14：51：25
DATETIME日期时间类型2018-01-02 14:51:52
（3） 字符串类型 必须添加引号
VARCHAR（M） 变长字符串 不会造成空间的浪费，操作速度相对比较慢
M的最大存储65535
CHAR（M） 定长字符串 可能产生空间的浪费，操作速度远比VARCHAR快得多。常用于存储固定长度的数据，如 手机号 ，电话号，身份证号等。
M的最大存储255
TEXT 大兴变长字符串，最多存储到2G
变长字符串和定长的区别VARCHAR（5）CHAR(5)aa\0a\0\0\0\0AbAb\0ab\0\0\0
列约束
Constraint 约束； MySQL可以对插入的数据进行特定的验证，只有满足条件才会允许插入到数据表中，否则被认为非法的数据，禁止插入。
例如：员工的年龄范围18-65，性别只能是男或者女。
在MySQL中一共有6种列约束
主键约束-PRIMARY KEY
声明了主键列上的值不能出现重复，表中查询的记录会按着主键上的值由小到大进行排序。
注意：一个表里只允许一个主键；通常是设置在编号上。
唯一约束-UNIQUE
声明了唯一约束的列不能插入重复的值，但允许插入NULL值，而且允许插入多个NULL值。
默认值约束-DEFAULT
可以使用默认值DEFAULT来为指定的列设置默认值；
使用方法：
INSERT INTO user VALUES(1,’tom’,DEFAULT);
NSERT INTO user (uid,uname) VALUES(2,’jierry’)
非空约束-NOT NULL
所插入的值不允许为空
检查约束-CHECK
检查约束是对输入的数据范围进行检验
CREATE TABLE student(age TINYINT CHECK(age>=0 AND age<=100))
注意：MySQL不支持检查约束
外键约束 ROREIGN KEY
声明了外键的列，取值必须在另一个表的主键列上出现过
允许使用NULL作为外键的值
ROREIGN KEY（列名称）references 另一个表（主键）
MySQL的自增列
AUTO_INCREMENT:自动增长，如果设置一个列为自增列，无需手动赋值，赋值为NULL，就会获取当前的最大值，新纪录在当前的基础之上加1
注意：只适用于整数型的主键上，同时允许手动赋值

简单查询
只查询特定的列
select eid,ename from emp;
查询所有的列
SELECT * FROM emp 多个列用逗号隔开
给列取别名
SELECT enname AS 姓名，birthday AS 生日，salary AS 收入 FROM emp；
SELECT enname 姓名，birthday 生日，salary 收入 FROM emp；
只显示不同的记录(合并相同的项)
SELECT DISTINCT deptld FROM emp;
在查询时执行计算
SELECT 2+35/10;
示例：查询所有员工的姓名和年薪
SELECT enname,salary12 FROM emp;
查询结果集的排序
SELECT * FROM emp ORDER BY salary ASC;#ascendant升序（默认）
SELECT * FROM emp ORDER BY salary DESC;descendant 降序
说明：排序默认升序排列
条件查询
SELECT * FROM emp WHERE eid=5;查询编号为5的员工信息
SELECT eid,salary,birthday FROM emp WHERE ename=‘king’;
SELECT * FROM emp WHERE birthday>=‘1991-1-1’;
！=（不等于）
SELECT * FROM emp WHERE deptid IS NULL;查询暂时没有部门的员工信息
NULL和任何值都不能进行相等/不等判定，可以使用IS NULL和IS NOT NULL
SELECT * FROM emp WHERE salary BETWEEN 5000 AND 5999;
查询工资在5000-5999之前的员工信息。Between and,>=5000 and <=5999
多条件拼接；
AND 并且 两个都满足
OR 或者 两个满足其一
SELECT * FROM emp WHERE salary<4000 OR salary>8000;查询大于8000，小于4000工资的员工
查询在1991年出生的员工信息
SELECT * FROM emp WHERE birthday >=‘1991-1-1’AND birthday <=‘1991-12-30’;
SELECT * FROM emp WHERE birthday between ‘1991-01-01’AND’1991-12-30’;
SELECT * FROM emp WHERE deptid IN(10,30);
查询10和30部门员工信息
模糊条件查询：
查询姓名中包含字母E的员工所有信息
SELECT * FROM emp WHERE ename LIKE’%E%’;
%可以匹配任意多个字符 》=0,_ 下划线可以匹配一个字符 =1
上述两个符号不能和=连用，只能使用like连用
SELECT * FROM emp WHERE ename LIKE ‘%E’;
SELECT * FROM emp WHERE ename LIKE’%E_';
分页查询
查询出所有的男员工，对查询的结果集，按照工资升序排序
SELECT * FROM emp WHERE sex=1 ORDER BY salary ASC;

分页显示：
查询的结果集记录太多，一次显示不完，可以一页一页的显示。分页查询语句在不同的数据库中实现的方法不一样，MYSQL是最简单的。
SELECT * FROM emp LIMIT start count;
Start:从结果集中开始读取的值
Count:指定读取的最多行数
第一页：SELECT * FROM emp LIMIT 0,5;#页码-1*每页数量
第二页：SELECT * FROM emp LIMIT 5,5；
第三页：SELECT * FROM emp LIMIT 10,5;
第N页：SELECT * FROM emp LIMIT (n-1)*5;

联系：分页查询所有员工信息，假设每页最多显示6条记录，写出查询钱4页的sql 语句。
SELECT * FROM emp LIMIT 0,6;
SELECT * FROM emp LIMIT 6,6;
SELECT * FROM emp LIMIT 12,6;
SELECT * FROM emp LIMIT 18,6;
注意：LIMIL 后面的两个值不能添加引号。

复杂查询
1.sql语句中复杂查询分为-----聚合查询/分组查询
示例：查询出所有员工的数量
SELECT COUNT() FROM emp; 推荐写法
SELECT COUNT(ename) FROM emp;
SELECT COUNT(eid) FROM emp;
SELECT COUNT(deptld) FROM emp;
函数：FUNCTION,具有特定功能，需要接受若干个数据，最终导出特定的计算结果。函数后面带括号。
MySQL中提供了5个聚合函数
COUNT()/SUM()/AVG()/MAX()/MIN()
练习：查询出10号部门员工的数量
SELECT COUNT(ename) FROM emp WHERE deptid=10;
查询出10号部门所有工资的总和
SELECT SUM(salary) FROM emp WHERE deptid=10;
查询出女员工工资的总和
SELECT SUM(salary) FROM emp WHERE sex=0;
查询出所有男员工的平均工资
SELECT SUM(salary)/COUNT() FROM emp WHERE sex=1;
SELECT AVG(salary) FROM emp WHERE sex=1;
查询出1990年出生的员工的工资最大值和最小值
SELECT MAX(salary),MIN(salary) FROM emp WHERE birthday>=‘1990-1-1’ AND birthday<=‘1990-12-31’;
分组查询
练习：查询出每个部门员工数量
SELECT deptid,COUNT() FROM emp GROUP BY deptid;
注意：分组查询中SELECT 后面只能跟分组条件，或者其他列的聚合函数；
查询出每个部门的平均工资，最大工资，最小工资
SELECT deptid,AVG(salary),MAX(salary),MIN(salary) FROM emp GROUP BY deptid;
查询男员工和女员工的人数是多少
SELECT sex,COUNT() FROM emp GROUP BY sex;
复杂查询----子查询
查询出工资比tom 高的员工的所有信息
步骤：先查询出tom的工资是多少
SELECT salary FROM emp WHERE ename=‘tom’;
步骤：查询出工资比6000高的信息
SELECT * FROM emp WHERE salary>=6000;
综合：SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE ename=‘tom’);
查询出研发部所有的员工信息
SELECT * FROM emp WHERE deptid=(SELECT did FROM dept WHERE dname=‘研发部’);
复杂查询----多表查询/跨表查询
查询出所有的员工姓名及其所在部门的名称
SELECT ename,dname FROM emp,dept WHERE deptid=did;
笛卡尔积
注意：为了防止出现笛卡尔积，在多表查询的时候，需要添加查询条件。
问题：无法查询出没有部门的员工，也无法查询出没有员工的部门。
SQL99中的多表查询
1.内连接 INNER JOIN … ON---------和之前的作用一样
SELECT ename,dname FROM emp INNER JOIN dept ON deptid=did;
2.左外连接 LEFT OUTER JOIN … ON----显示左侧所有的记录3.
SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptid=did;
3.右外连接 RIGHT OUTER JOIN…ON
SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptid=did;
说明;outer 关键字可以省略的。
4.全连接 FULL JOIN
mysql 不支持全连接

UNION/UNION ALL
union all 两组数据出现了相同项不合并
union 两组数据出现了相同项进行合并