mysql及sql基础语法笔记

一、mysql初始工作

安装卸载

服务启动停止

登陆退出 dos里面 mysql -u root -p 123321 ; quit

 

语言规范： 1.SQL 语言大小写不敏感（数据大小写是敏感的）。

2.SQL 可以写在一行或者多行

3.关键字不能被缩写也不能分行

4.各子句一般要分行写。

5.使用缩进提高语句的可读性。

6.大多数情况下不区分单双引号

 

ready知识：

1、单行注释 两种方式 # --， 多行注释，和java一样

2、基本命令

SHOW DATABASES; user 库名 SHOW TABLES;

 

二、DQL:（Data QueryLanguage）语句：

数据查询语言，属于DQL的一种，比较重要，涉及知识点比较多，单列出来

1、基础查询：

（1）select * 查询所有字段

（2）可以当作输出语句 select 字符串或者表达式 [ from dual ] dual是伪表可省略

（3）去重使用 distinct 关键字 比如 SELECT DISTINCT salary

（4）补充小知识点： a、任何数据和null做运算结果为null

b、别名可以省略关键字AS，别名中有空格的话，必须用引号引起来

2、过滤和运算符：

2.1、过滤使用关键字where一般搭配含有运算符的表达式

（1）比较运算符，相等 “=”，不能“<>”也可以“！=”

（2）其他比较运算符：（between and ；in（set）;like ; is [not] null ）;

（3）逻辑运算符：and ；or； not

2.2、模糊查询使用like

（1）%表示任意个字符，_表示一个字符

（2）转义字符，可以使用 比如 \_ 表示字符"_"

可以自定义转义字符 比如 ^% ESCAPE "^"，^表示转义字符

3、排序

（1）使用 order by ，desc表示降序， asc 表示升序，默认是升序，

（2）二级排序：ORDER BY 字段1 ASC , 字段2 DESC;

（3）如果按别名排序，如果别人有空格，则order by的后面的别名也要用引号引起来

（4）可以按表达式排序比如 order by salary*3

4、多表查询和联合查询union

（1） 三种连接 （需理解）

内连接 vs 外连接

自连接 vs 非自连接

等值连接 vs 非等值连接

（2）可以多层连接

select ..... from 表名1 join 表名2 on 连接条件 join 表名3 on 连接条件 ....... where 过滤条件 order by .......

（3）mysql不支持满外连接，但是可以通过左外连接 和右外连接的联合查询达到满外连接的效果

#满外连接(mysql不支持) SELECT e.`first_name`,d.`department_name` FROM employees e LEFT JOIN departments d ON e.`department_id`=d.`department_id` UNION#union将两张表的数据进行合并（去重） union all不去重 SELECT e.`first_name`,d.`department_name` FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;

5、函数

5.1、单行函数（一行输入对应一条结果）

（1）大小写函数 lower(String) upper(String)

（2）字符控制函数

CONCAT('Hello', 'World') # HelloWorld SUBSTR('HelloWorld',1,5) # Hello LENGTH('HelloWorld') # 10 INSTR('HelloWorld', 'W') # 6 LPAD(salary,10,'*') # *****24000 RPAD(salary, 10, '*') # 24000***** TRIM('H' FROM 'HelloWorld') # elloWorld REPLACE('abcd','b','m') # amcd

（3）数字函数 round truncate mod

（4）now( )返回日期时间

5.2、通用函数 case表达式

case 字段名 when 值1 then 返回值1 when 值2 then 返回值2 when 值3 then 返回值3 ....... else 返回值 end

5.3、组函数（聚合函数）

AVG() SUM() #这两个函数只能用在数值上 MAX() MIN() COUNT() /* COUNT(字段名)：统计该字段不为null的数据的个数。 COUNT(*)：整张表中所有的数据有多少条。 count(数值)：和count(*)效果一样。count(数值)比count(*)的效率高一些。 */

6、分组及分组中的过滤

（1）select后面一旦出现组函数那么就不能再写其它的字段，除非该字段出现在group by的后面。

（2）where和having的区别？

1.位置 ：where是在group by的前面，having是在group by的后面

2.使用 ：where后面不能跟组函数。having后面可以跟组函数。

3.其他功能都相似；但是一般where中能过滤都先过滤掉，这样效率更高

7、子查询

（1）子查询先执行

（2）分类：

单行子查询 ：子查询的结果只有一行：

多行子查询 : 子查询的结果有多行。适用运算符 in any all ；（查询结果也相当于一个表，可以用来和别的表链接）

8、分页limit

分页语句一定要放到最后

SELECT * FROM employees LIMIT 起始索引 , 数据数量;

 

三、DML（Data Manipulation Language）语句：

数据操纵语句，对数据的增、删、改、查

1、插入操作

（插入操作要注意，数据类型匹配，个数匹配）

（1）如果插入的是全数据那么表名后面的字段可以省略。但是要注意顺序

insert into 表名(字段名1，字段名2....) values(数据1，数据2.....),(数据1，数据2.....) ......

（2）将查询结果插入表中

INSERT INTO person2(id,NAME) SELECT employee_id,last_name FROM myemployees.`employees`;

2、删除：

delete from 表名 [where 过滤条件]#省略过滤条件就是全删除

3、更新：

update 表名 set 字段名1=值1，字段名2=值2 .... [where 过滤条件]#不加过滤条件就是全部修改

 

四、DDL（Data Definition Languages）语句：

数据定义语句，涉及数据库中的表、索引、表中的列，约束，视图，存储过程等

1、库的操作

（1）创建库

create database [IF NOT EXISTS] 库名 [CHARACTER SET '编码集']# 最好带上IF NOT EXISTS，不然如果库存在时会报错

修改库的默认编码集（一般不建议这种操作，修改安装目录下的my.ini文件）

（2）查看库的信息

SHOW CREATE DATABASE demo2;

（3）删库（一般没这种操作）

DROP DATABASE [if exists] 库名;#如果不带 if exists，库不存在时报错

（4）#修改库的编码集（一般不这样操作，对于有数据的库会形成乱码）

ALTER DATABASE demo2 CHARACTER SET 'gbk';

2、表的操作

（1）创建表三种方式

第一种白手起家，if not exists 和 设置编码也是可选的，默认编码和库的编码一样

第二种：基于现场的表结构（只有表结构，没有数据）

CREATE TABLE 表名 LIKE 现有的表名

第三种：基于查询结果创建（有结构，也有数据，当然也可能存在没数据的情况）

CREATE TABLE st2 SELECT employee_id,salary FROM myemployees.employees;

（2）查看表信息

SHOW CREATE TABLE student2;

（3）查看表结构

DESCRIBE student; DESC student;

（4）删除表

DROP TABLE [IF EXISTS] 表名;

3、数据类型介绍

INT	从-2^31到2^31-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度。D<=M<=255,0<=D<=30，默认M+D<=15
DATE	日期型数据，格式’YYYY-MM-DD’

4、对列的操作

关键字COLUMN 可以省略

#添加一列 ALTER TABLE person ADD COLUMN age INT; #删除一列 ALTER TABLE person DROP COLUMN age; #修改列的名字 ALTER TABLE person CHANGE COLUMN age age2 INT; #修改列的类型 ALTER TABLE person MODIFY COLUMN age2 VARCHAR(20);#modify可以修改列的类型，大小以及默认值 #修改表的名字 ALTER TABLE person RENAME TO person2;

5、约束

（1）六种约束：

NOT NULL 非空约束，规定某个字段不能为空

UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

PRIMARY KEY 主键(非空且唯一)，设置主键自增使用关键字AUTO_INCREMENT

FOREIGN KEY 外键

CHECK 检查约束 （mysql不支持，可以使用，但是没有任何效果）

DEFAULT 默认值

（2）约束分类：

列级约束 ：单独某一列进行约束 （ not null 和 default 只有列级约束）

表级约束：可以同时对多个列进行组合约束：使用关键字 CONSTRAINT

#添加表级约束 CREATE TABLE stu3( username VARCHAR(20), pwd VARCHAR(20), #CONSTRAINT 索引名 UNIQUE/primary key (NAME,PASSWORD) CONSTRAINT stu3_a_b UNIQUE (username,pwd) );

（3）创建表的时候添加约束（掌握）

（4）表创建好之后使用alter修改约束

了解就行，使用关键字alter配合 add ，drop，modify实现约束的操作

（5）外键约束

CONSTRAINT 索引名

FOREIGN KEY: 在表级指定子表中的列

REFERENCES: 标示在父表中的列

ON DELETE CASCADE(级联删除): 可选，不建议选，当父表中的列被删除时，子表中相对应的列也被删除

ON DELETE SET NULL(级联置空): 可选，不建议选子表中相应的列置空

 

五、TCL（Transaction Control Language）语句

事务控制语句

（1）事务语法

#禁止自动提交（开启事务）
SET autocommit=FALSE;
DELETE FROM person2;
#提交-一旦提交不能回滚
COMMIT;
#回滚
ROLLBACK;
#允许自动提交
SET autocommit=TRUE;

（2）好处

使用COMMIT 和 ROLLBACK语句,我们可以:

确保数据完整性。

数据改变被提交之前预览。

将逻辑上相关的操作分组。

（3）清空表的两条命令区别

#清空表 --不是删除表。 delete from 表名 ： 可以事务回滚 TRUNCATE TABLE 表名 ：不可以事务回滚 注意：如果确定数据要被清空那么可以使用truncate table因为效率高。