MYSQL知识（三）

更新表

# 添加列 ALTER TABLE 表名 ADD 列名 数据类型 参数 # 修改列 ALTER TABLE 表名 MODIFY 列名 新类型 新参数 ALTER TABLE 表名 CHANGE 旧列名 新列名 新类型 新参数 # 修改表名 ALTER TABLE 当前表名 RENAME TO 修改后的表名 RENAME TABLE 当前表名 TO 修改后的表名 # 删除列 ALTER TABLE 表名 DROP 列名

对数据的增删改查

# 插入完整的行 INSERT INTO 表名(字段) VALUES(值); INSERT INTO 表名 VALUES(必须插入全部字段的数据); # 插入多行 INSERT INTO 表名(字段) VALUES (        ), (        ), (        ); # 删除全部 DELETE FROM 表名; # 删除部分 DELETE FROM 表名 WHERE 字句; # 清空表 TRUNCATE TABLE 表名; DELETE FROM 表名; # 更新表内数据 UPDATE 表名 SET 更新字段 WHERE 字句;

Truncate 和delete 区别

Truncate：先将整个表删除，然后再创建一个同样数据类型和约束的空表 Delete：查询所有数据，然后一一删除 Truncate的执行效率高于delete

SQL语言的分类

DML:数据库操作语言，增删改 DDL:数据库描述语言，基于表，数据库 DCL:数据控制语言，权限，grant

查询语句

#查询所有 SELECT 字段 FROM 表名; #多字段查询 SELECT 字段1,字段2,字段3 FROM 表名; #使用where字句 SELECT * FROM 表名 WHERE 字句; #避免查询到重复内容 SELECT DISTINCT 列名 FROM 表名; #limit完成特定行的提取 SELECT * FROM 表名 LIMIT n; # 表示提取前n行 SELECT * FROM 表名 LIMIT n,m;  #从n行开始，取m行内容，行号从0开始 #对查询结果排序  desc 降序  Asc 升序（默认） SELECT * FROM 表名 ORDER BY age DESC LIMIT 1; 注： Order by a,b (先以a排序，再以b排序) Select * from 表名 order by age,id desc;(多个字段，排序关键字asc,desc只对靠近它的字段有作用)

Where子句使用的操作符

>= <= = != <> Between  a  and  b :包含边界值

逻辑连接符

And Or （）   优先级：（）>and >or

In 操作符

In操作符：指定条件范围，范围内的每个条件都可以进行匹配 Select 列名 from 表名 where 列名 in（value1，value2...）; 特点： In操作符的语法清晰直观 计算次序容易管理 In操作符一般比or的执行速度更快 In 包含其他select语句，使得动态建立where子句

Not操作符

否定他之后所跟的任何条件

Like操作符

% 跟Linux的*一样 _  跟Linux的？一样 占用资源一般不进行使用

列的别名

Select 列_1 as 列1,列_2 as 列2 from text as 表

Concat ( )函数

用于多个字符串连接成一个字符串 Select concat（‘m’,’y’,’s’,’q’,’l’）;

MySQL支持的运算符

+ - * /

MySQL支持的函数

文本处理：处理字符串 日期，时间处理函数 数值处理函数 系统函数：返回数据库的信息的系统函数

文本处理函数

Length（） 返回字符串的长度 Lower（）  转为小写 Upper（） 转为大写 Substring（cust_name,4） 返回字串从第4个开始到末尾的 Substring（cust_name,4，3） 返回字串从第4个开始取3个字符的子串 Trim( )  去掉字符串左右两边的空格 Ltrim( )  去掉左边的空格 Rtrim( )  去掉右边的空格

日期与时间函数

获取当前日期和时间 Curdate( ) Curtime( ) Now( ) 时间日期计算函数 Adddate( ) 增加一个日期 获取时间日期一部分 Date ( ）返回日期时间的日期部分 Year（ ） Month( ) Day( )

数值处理函数

Round（x,y) 返回参数x的四舍五入的有Y位小数的值 Truncate(x,y) 返回参数x截断为y位小数的结果 Abs(x) 返回x的绝对值 Rand( ) 返回0-1内的随机数

系统函数

Version（ ）   select version( ) Database( ) User( )

聚集函数

Avg( ） 平均值 Count（ ) 行数 Max( ) Min( ) Sum( )

查询结果分组操作

把数据表中的数据按照某个字段，分为多组，字段值相同的为一组，分组是为了便于对每个组进行聚集计算 Group by Select name from employee group by bwben; (分组默认显示是每一个分类的第一个数据) 注： Select 后面只能查看group by 子句后有的列 Group by 可以根据多个列进行分组

过滤分组 having

Group by ....having 通常加聚合函数 Where 过滤指定的行 Having 过滤指定的分组

Select语句中子句的顺序

Select >from>where>group by>having>order by >limit

子查询

子查询：嵌套在其他查询中的查询 外层查询：父查询，主查询 内层查询：子查询，从查询 注：处理时先处理内查询，由内向外的处理 规则： 子查询select总是用圆括号括起来 最多嵌套32层 任何使用表达式都可以用子查询。只要它返回的是单个的值 如果某个表只出现在子查询而不出现在外部查询，那么该表的列就无法包含在输出中

关系表

可以有效和方便处理 设计原则：保证把信息分解为多个表，通过某些值关联起来

笛卡尔乘积

没有联结条件的表关系返回的表 检索行数=第一个表行数   *  第二个表行数 格式：select * from 表1 cross join 表2；

联结

第一个表中每一行与第二个表中每一行配对 多表联结格式： Select 字段1，字段2...from 表1，表2，表3 ...where 表1.字段=表2.字段 And 表2.字段=表3.字段 处理消耗资源，联结的表越多，性能下降的越厉害

内部联结：等值联结

Select 字段... from 表1 inner join 表2 on 联结条件

给表指定别名

目的： 压缩sql语句 运行单表，select语句多次使用相同的表 给计算结果指定别名（创建临时表）   Select A.cust_id from (select cust_id from customers where cust_email is null) as A,(select cust_id from orders where date (order_date)> ’2015-10-01’) as B where A.cust_id=B.cust_id;

自联结

同一个表中做联结操作 自联结的执行效率高于子查询 Select b.* from shopping as a,shopping as b where a.name=’慧慧’and a.price < b.price;

外部联结

左外部联结：以左边表为基准，按照过滤条件查找右边表的记录             匹配到，组合成一行，显示结果             没匹配到，只显示左边表的字段，右边表不存在的值=null Select *from book left join booktype on book.booktype_id=booktype.booktype_id 右外部联结：以右边表为基准，去匹配左边的表，如果左边表字段为空，用空值null表示 Select *from book right join booktype on book.booktype_id=booktype.booktype_id

索引

提高数据查询的速度 但会降低增删改数据的速度 普通索引：这是最基本的索引，没有任何的限制 Create index cust_name_index on customer(cust_name); 唯一索引：索引值必须唯一，但允许有空值 Create unique index cust_name_index on customers(cust_name);

视图

虚拟的表

存储过程

一组为了完成特定功能的SQL语句集，用户可以通过调用存储过程实现相应的操作 #1. 创建储存过程productpring，这个过程不带参数 DELIMITER $$ #用$$代替MySQL默认的结束符 CREATE PROCEDURE productpricing() BEGIN SELECT AVG(prod_price) AS priceaverage FROM products; END $$ CALL productpricing(); # 调用存储过程productpring #2. 创建带参数的存储过程，out表示参数是从存储过程传出来的 DELIMITER $$ CREATE PROCEDURE productprice(OUT pricemin INT) BEGIN SELECT MIN(prod_price) INTO pricemin FROM products; END $$ CALL productprice(@pricemin); 调用存储过程productprice SELECT @princemin; #3. 查看所有存储过程 SHOW PROCEDURE STATUS; #4. 删除存储过程 DROP PROCEDURE xxx;

备份数据库

Mysqldump -u root -p 数据库名>database.sql Mysqldump -u root -p crashcourse>/home/charles/crashcourseback.sql

备份表

Mysqldump -u root - p 数据库名 表名>table.sql