mysql学习笔记

查询表和数据库

SHOW DATABASES;
SHOW TABLES FROM database_name;
SHOW COLUMNS FROM table_name;

查询记录

SELECT exper…. FROM TABLES;

非空约束

null与not null

主键约束

PRIMARY KEY
AUTO_INCREMENT 必须与主键组合使用，但主键不必须与AUTO_INCREMENT使用。
每张数据表可以存在多个唯一约束；但只可以有一个主键约束；
UNIQUE KEY
注意，语句要在英文状态下输入，尤其是标点符号，很容易出现错误。

默认约束

当我们没有为某个字段赋值时，系统会赋予默认值；

外键约束

FOREIGN KEY
子表和父表使用相同的存储引擎，而且禁止使用临时表
数据表的存储引擎只能是InnoDB
外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同。

外键列和参照列必须创建索引，如果外键列不存在索引的话，mysql将自动创建索引。外键列则不会。
编辑数据表的默认存储引擎 default-storage-engine=INNODB

展示索引列
SHOW INDEX FROM tab_name;

一般不使用物理外键，使用逻辑外键，因为逻辑外键必须要是INNODB引擎。
列级约束既可以在列定义时声明，也可以在列定义后声明。
表级约束只能在列定义后声明。
按照能够定义的数目，分为列级定义和表级定义。
DEFAULT与NOT NULL 不能是表级约束.

修改数据表
添加单列
ALTER TABLE table_name ADD [COLUMN] col_name column_defination [FIRST |AFTER col_name]
添加多列 ALTER TABLE table_name ADD [COLUMN] (col_name column_defination )
删除列
ALTER TABLE table_name DROP col_name;(删除多列，就以逗号分隔，添加多个DROP),例如：ALTER TABLE table_name DROP col_name,DROP col_name;
ALTER TABLE table_name DROP col_name,ADD col_name;

添加主键约束

ALTER TABLE table_name ADD PRIMARY KEY(col_name);
ALTER TABLE table_name DROP PRIMARY KEY;

添加唯一约束
删除唯一约束，先查看约束的名字

ALTER TABLE table_name ADD UNIQUE (col_name,...)
ALTER TABLE table_name DROP {INDEX|KEY} index_name;

添加外键约束ALTER TABLE table_name ADD FOREIGN KEY (index_col_name) reference_definition
删除外键约束ALTER TABLE table_name DROP FOREIGN KEY fk_symbol；
删除默认约束 ALTER TABLE table_name ALTER col_name {SET DEFAULT exp|DROP DEFAULT}

修改列定义 ALTER TABLE table_name MODIFY col_name colum_definition [FIRST|AFTER col_name]
修改列的名称ALTER TABLE table_name CHANGE old_col_name new_col_name column definition [FIRST|AFTER col_name]

在客户端以GBK方式显示数据SET NAMES gbk;

插入数据
在创建表的同时插入数据CREATE SELECT

 CREATE TABLE tdb_goods_brand(
    -> brand_id smallint unsigned primary key auto_increment,
    -> brand_name VARCHAR(40) NOT NULL
    -> )
    -> SELECT brand_name from tdb_goods GROUP BY brand_name;

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

INSERT tab_name [(columns)] VALUES（……）
插入记录的另一种INSERT[INTO] tbl_name SET col_name={expr|default},...
更新记录UPDATE tbl_name SET colname where...
多表更新

update tdb_goods AS a INNER JOIN tdb_goods_brands as b ON a.bran_name=b.brand_name
    -> SET a.brand_name=brand_id;

连接

on关键字设定连接条件

多表连接

 select goods_id,goods_name,cate_name from tdb_goods AS a INNER JOIN tdb_goods_cates AS b ON a.cate_id =b.cate_id INNER JOIN tdb_goods_brands AS c ON a.brand_id =c.brand_id;

多表删除

 DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2  ON t1.goods_name = t2.goods_name  WHERE t1.goods_id > t2.goods_id;

插入多条记录记录另一种

INSERT tdb_goods_cates(cate_name) SELECT goods_cate from tdb_goods group by goods_cate;
INSERT user(username) SELECT age FROM client2 WHERE username='JUE';

删除记录DELETE FROM tbl_name [WHERE where_condition]
删除空 DELETE FROM user WHERE name is null;
查询记录

SELECT select_expr [,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name|position} [ASC|DESC],...]
[HAVING where_condition]
[ORDER BY { col_name|expr|position} [ASC|DESC],...]
[LIMIT {[offset,] row_count|row_count OFFSET offset}]
]

select语句字段出现的顺序，和字段的别名也会影响结果，可以使用别名SELECT age AS ag,username AS um FROM client2;
可以使用where条件对select的记录进行过滤select username,age from client2 where age>40;
可以使用group by对查询结果进行分组select username,age from client2 where age>40 GROUP BY age ORDER BY age DESC;
可以使用HAVING where_condition设置分组条件select username,age from client2 where age>40 GROUP BY age HAVING age>50 ;
并且HAVING的col_name字段必须出现在select里面
ORDER BY对结果进行排序，可以定义多个列select username,age from client2 ORDER BY username,age DESC;
Limit可以限制结果数量【mysql的记录是从0开始编号】

SELECT * FROM client LIMIT 2

只有一位数字时，表示返回的结果数量。

SELECT *FROM client LIMIT 2,3;

两个数字时，第一位表示开始的位置，第二位是数量

INSERT [INTO] tbl_name [(col_name,...)] SELECT...

与第一种方式的区别在于此方法可以使用子查询（SubQuery）
记录操作

INSERT
UPDATE
DELETE
SELECT

前三个为写操作，后一个为读操作

1. 子查询(Subquery)是指出现在其它sql语句内的select子句
2. 子查询指嵌套在查询内部，且必须始终出现在圆括号内。
3. 子查询可以包含多个关键字或条件。
4. 子查询的外层查询可以是：SELECT,INSERT,UPDATE,SET或DO
5. 子查询返回值可以是标量，一行，一列或子查询。

比较运算符的子查询=，！=，>,>=,<,<=
语法结构operand comparison_operator [NOT] IN (subquery)
=ANY 运算符与IN等效
！=ALL运算符与NOT IN 等效
还可以用ANY,SOME或ALL修饰比较运算符，【SOME和ANY是等价的】

1. `operand comparison_operator ANY (subquery)
2. operand comparison_operator SOME (subquery)
3. operand comparison_operator ALL (subquery)`
   例：

SELECT goods_id,goods_name,goods_price FROM  tdb_goods WHERE goods_price> ANY(SELECT goods_price FROM tdb_goods where goods_cate='超级本');

SELECT goods_id,goods_name,goods_price FROM  tdb_goods WHERE goods_price> ALL(SELECT goods_price FROM tdb_goods where goods_cate='超级本');





内置函数
====

用户自定义函数`UDF`

自定义函数

存储过程

特性

举例：CREATE PROCEDURE show_name () SELECT VERSION;

调用存储过程

第二种情况，如果存储过程没有入参，那么不用带括号。

修改存储过程：无法修改过程体。只能重写

存储过程与自定义函数的区别

1. 存储过程实现的功能要复杂一些，而函数的针对性更强
2. 存储过程可以返回多个值，函数只能有一个值
3. 存储过程一般独立的来执行，而函数可以作为其他sql语句的组成部分来实现。

修改存储过程只能修改一些简单的特性，不能修改过程体，如果要修改，只能删除重来。

DROP PROCEDURE [IF EXISTS] sp_name;

更改mysql的默认结尾符DELIMITER //

存储引擎

1. MyISAM
2. InnoDB
3. Memory
4. CSV
5. Archive

修改引擎通过默认配置default-storage-engine=engine
修改引擎通过创建表的时候CREATE TABLE table_name(
..................
............)ENGINE=engineName
通过修改数据表命令实现ALTER TABLE table_name ENGINE[=]engine name;