mysql数据库入门_MySQL数据库基础学习笔记

启动/关闭mysql服务

启动mysql服务：net start mysql

停止mysql服务：net stop mysql

(所有windows的服务都可以通过这两个命令实现)

错误处理：

1.net start mysql服务正在启动或停止中，请稍候片刻后再试一次，

解决办法：任务管理器-进程-结束mysql相关的进程，重新net start mysql即可。

2.安装完mysql没有my.ini文件

解决办法：将my-default.ini文件重命名为my.ini

3.创建数据库，报1044

解决办法：1.关闭服务 2.在mysqld语句下，增加 skip_grant_tables

mysql登录与退出

版本号:mysql -v

退出: exit;

quit;

\q;

打开数据库test

mysql -uroot -proot test

MYSQL语法规范：

1.关键字和函数名称全部大写

2.数据库名称、表名称、字段名称全部小写

3.SQL语句必须以分号结尾

操作MYSQL语据库

创建数据库语法：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[DEFAULT] CHARACTER SET [=] charset_name

{} 必选项

| 选择，两项或三项中选择

[] 有或者没有，称为可选项

例：

CREATE DATABASE t1; 创建数据库t1

CREATE DATABASE IF NOT EXISTS t1; 忽略存在错误

查看当前服务器下的数据表列表:

SHOW {DATABASE | SCHEMAS}

[LIKE 'pattern' | WHERE expr]

例：

SHOW WARNINGS; 查看警告

CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET utf8; //创建utf8编码的数据库

修改数据库

ALTER {DATABASE | SCHEMA} [db_name];

[DELETE] CHARACTER SET [=] charset_name;

例：

ALERT DATABASE t2 CHARACTER SET = gbk; //修改t2表字符编码

SHOW CREATE DATABASE t2; //查看t2表创建信息

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

例：

DROP DATABASE t1; //删除数据库表1

SHOW DATABASES; //查看数据库列表，已成功删除

DROP DATABASE IF EXISTS t1; //再次删除 不显示错误提示

SHOW WARNINGS; //显示错误提示

总结：

MySQL默认的端口号是：3306

MySQL中的超级账号是：root

创建数据库：CREATE DATABASE

修改数据库：ALTER DATABASE

删除数据库：DROP DATABASE

数据类型与数据表操作

数据类型：

整数型

浮点型

日期时间型

字符型

CHAR

VARCHAR

数据表(表)

打开数据库

SHOW DATABASES; //有哪些数据库

USE test; //打开test表

SELECT DATABASE(); //当前打开的数据库

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name (

column_name data_type,

...(最后一个不加逗号)

)

例：

CREATE TABLE tb1(

username VARCHAR(32), //用户名字符型，有字符长度限制使用VARCHAR(32)

age TINYINT UNSIGNED, //年龄整型，有长度限制，且不能为负

salary FLOAT(8,2) UNSIGNED //薪水整型，有长度限制，且不能为负

);

查看数据表

SHOW TABLES [FROM db_name] //db_name数据库名

[LIKE 'pattern' | WHERE expr]

查看数据表结构

SHOW COLUMNS FROM tbl_name

插入记录

INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...)

例：

INSERT tb1 VALUES('Tom', 24, 1200.34); //正确插入记录

INSERT tb1 VALUES('Tammy', 26); //报错,语法错误。

INSERT tb1(username,salary) VALUES('John', 1200.90); //正确

记录查找

SELECT expr,... FROM tb1_name //tb1_name:表名

例：SELECT FROM tb1; // 表示全部的字段

记录完善：

空值与非空：NULL 字段值可以为空；NOT NULL 字段值禁止为空；

例：创建数据表

CREATE TABLE tb2(

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED NULL,

salary FLOAT(8,2) UNSIGNED NOT NULL

);

查看数据表

SHOW TABLES tb2;

插入记录

INSERT tb2 VALUES('Tom',NULL); //报错

INSERT tb2 VALUES('Tom',NULL,23000.00); //正确插入记录

选择记录

SELECT FROM tb2; //正确查出结果

记录唯一性 AUTO_INCREMENT

自动编号，且必须与主键组合使用(但是主键不一定非要与AUTO_INCREMENT一起使用)

默认情况下，起始值为1，每次的增量为1

这个字段必须是整型或浮点型，且起始必须为0

例：

CREATE TABLE tb3(

id SMALLINT UNSIGNED AUTO_INCREMENT,

username VARCHAR(30) NOT NULL

);

//报错：... it must be defined as a key 必须定义为主键

主键 PRIMARY KEY

主键约束

每张数据表只能存在一个主键

主键保证记录的唯一性

主键自动为NOT NULL

例：

CREATE TABLE tb3(

id SMALLINT NOSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(30) NOT NULL

); //正确创建数据表tb3

INSERT tb3 VALUES('Tammy');//正确

INSERT tb3 VALUES('Tom');//正确

SELECT FROM tb3;//查找记录

创建一张表只有主键，没有AUTO_INCREMENT可不可以

CREATE TABLE tb4(

id SMALLINT NOSIGNED PRIMARY KEY,

username VARCHAR(30) NOT NULL

); //创建成功

INSERT tb4 VALUES(4,'Tom');//插入记录

INSERT tb4 VALUES(0,'Tommy'); //插入记录

SELECT FROM tb4; //查找记录

UNIQUE KEY

唯一约束

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空值(NULL)

每张数据表可以存在多个唯一约束

例：

CREATE TABLE tb5(

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(30) NOT NULL UNIQUE KEY,

age TINYINT UNSIGNED

);

INSERT tb5(username, age) VALUES('Tammy', 24); //正确

INSERT tb5(username, age) VALUES('Tom', NULL); //正确

INSERT tb5(username, age) VALUES('Tom', 26); //报错：Duplicate entry 'Tom' for key 'username' ,username 唯一性

默认值 DEFAULT

插入记录时，如果没有时确为字段赋值，则自动赋予默认值

例：

CREATE TABLE tb6(

id SMALLINT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(30) NOT NULL UNIQUE KEY,

sex ENUM('1','2','3') DEFAULT '3'

);

SHOW COLUMNS FROM tb6;

INSERT tb6(username) VALUES('Tammy'); //默认赋值sex---

约束

外键约束FOREIGN KEY

保持数据一致性，完整性

实现一对一或一对多的关系(关系型数据库)

要求：

1.父表与子表必须使用相同的存储引擎，而且禁止使用临时表；

子表:具有外键列的表称为子表

父表:子表参照的表称为父表

2.数据表的存储引擎只能为InnoDB;

3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

4.外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

例：

USE test;

CREATE TABL provinces(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20) NOT NULL

);

SHOW CREATE TABLE provinces; //确定表存储引擎是InnoDB

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid BIGINT, //外键列数据类型与provinces中id的参数列数据类型不同

FOREIGN KEY (pid) REFERENCES provinces (id)

); //报错,未创建成功

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid SMALLINT UNSIGNED,

FOREIGN KEY (pid) REFERENCES provinces (id)

); //正确

解析：

users子表

provinces父表

FOREIGN KEY 有外键的表称为子表

users外键表

provinces参照表

id参数列 必须创建索引 PRIMARY KEY自动创建索引

pid外键列 必须创建索引

SHOW INDEXES FROM provinces; //查看索引

SHOW INDEXES FROM provinces\G; //查看索引回车

外键约束的参照操作

1.CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行

2.SET NULL：从父表删除或更新行，并设置子表中的外键列

3.RESTRICT:拒绝对父表的删除或更新操作

4.NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同

CREATE TABLE prov(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(10)

);

CREATE TABLE city(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(30),

pid SMALLINT UNSIGNED,

FOREIGN KEY (pid) REFERENCES prov(id) ON DELETE CASCADE

);

//ON DELETE CASCADE 父表删除一条记录，子表中参数列所在的行也会被删除

插入记录，必须先在父表中插入然后才能在子表中插入，原因子表参照父表中的信息

父表中插入记录：

INSERT provinces(name) VALUES('a');

INSERT provinces(name) VALUES('b');

INSERT provinces(name) VALUES('c');

查看记录：

SELECT FROM provinces;

子表中插入记录：

INSERT users(username,pid) VALUES('Tom',3);

INSERT users(username,pid) VALUES('Tammy', 2);

父表中删除一条记录

DELETE FROM provinces WHERE id =1;

查看记录：

SELECT FROM provinces; //删除成功

SELECT FROM user1; //子表中跟父表相关的记录也删除了

修改数据表

添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

例：

SHOW COLUMNS FROM city; //查看数据表结构

ALTER TABLE city ADD code TINYINT UNSIGNED NOT NULL DEFAULT '0000'; //添加code列

ALTER TABLE city ADD email VARCHAR(60) NOT NULL; //添加email列

SHOW COLUMNS FROM city; //查看数据表结构

ALTER TABLE city ADD tel TINYINT NOT NULL AFTER pid; //在pid列之后

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

例

ALTER TABLE ctiy DROP tel; //删除tel列

SHOW COLUMNS FROM city; //查看删除后的表结构

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY index_type

例:创建user2数据表

CREATE TABLE user2(

username VARCHAR(20) NOT NULL,

pid SMALLINT(5) UNSIGNED DEFAULT NULL

);

ALTER TABLE user2 ADD id SMALLINT UNSIGNED; //成功添加

ALTER TABLE user2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id); //成功添加主键约束

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]

(index_col_name,...)

例：设置唯一约束

ALTER TABLE users2 ADD UNIQUE (username); //设置username为唯一约束

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,...)

reference_definition

例：添加外键约束

ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provinces (id); //设置外键约束

SHOW CREATE TABLE users2;

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_mame

{SET DEFAULT literal | DROP DEFAULT}

例：

ALTER TABLE users2 ADD email VARCHAR(60) NOT NULL DEFAULT 1; //添加数据列设置默认值为1

SHOW COLUMNS FROM users2;//查看数据表结构

ALTER TABLE users2 ALTER email DROP DEFAULT; //删除默认约束

ALTER TABLE users2 ALTER email SET DEFAULT 12; //添加默认值为12

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

//没有指定字段？原因：一个表只有一个主键约束

例：

SHOW COLUMNS FROM users2;//查看主键约束

ALTER TABLE users2 DROP PRIMARY KEY; //删除主键约束 UNIQUE

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

//为什么有{INDEX|KEY}？原因：一个表可以有多个唯一约束

例：

SHOW INDEXES FROM users2;//查看唯一约束

ALTER TABLE users2 DROP INDEX username; //删除唯一约束，并不是删除字段

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

例：

SHOW CREATE TABLE users2; //查看外键约束,系统赋予的'users2_ibfk_1'

ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1; //删除外键约束

SHOW CREATE TABLE users2;//查看外键约束,已删除外键

ALTER TABLE users2 DROP INDEX pid//字段上的索引依然存在，删除索引

SHOW CREATE TABLE users2;//查看数据表,已删除索引

修改列定义：数据列的名字没有问题，位置或是类型有问题

ALTER TABLE tbl_name MODIFY [COLUMN] col_name

column_definition [FIRST | AFTER col_name]

例：修改列位置

ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED FIRST; //将id改到数据表第一位

SHOW COLUMNS FROM users2; //查看修改结果,id字段排在第一位

ALTER TABLE users2 MODIFY id TINYINT UNSIGNED; //修改id字段类型

SHOW COLUMNS FROM users2; //注意修改类型会导致数据出问题

修改列名称

ALERT TABLE tbl_name CHANGE [COLUMN] old_col_name

new_col_name column_definition [FIRST|AFTER col_name]

例：修改列名称 pid类型修改-tinyint 名称也修改p_id

ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;//列名称修改了，列定义也修改了。

//功能比modify更强大

修改数据表名称：不建议更改，会导致引用该表的视图出错

方法1：

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

方法2：

RENAME TABLE tbl_name TO new_tbl_name

[,tbl_name2 TO new_tbl_name2] ...

例：users2改成test;

ALTER TABLE users2 RENAME TO test; //修改表名称

SHOW TABLES;//查看所有表，已更改

总结：

约束：

功能：

NOT NULL(非空约束)

PRIMARY KEY(主键约束)

UNIQUE KEY(唯一约束)

DEFAULT(默认约束)

FOREIGN KEY(外键约束)

数据列的数目：

列级约束

表级约束

修改数据表：

针对字段的操作：添加、删除字段，修改列定义，修改列名称等

针对约束的操作：添加、删除各种约束

针对数据表的操作：数据表更名(两种方式)

记录的操作

插入记录1：

INSERT [INTO] tbl_name [(col_name,...)] {VALUES|VALUE}

({expr | DEFAULT},...),(...),...

例：

CREATE TABLE user(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20) NOT NULL,

password VARCHAR(32) NOT NULL,

age TINYINT UNSIGNED DEFAULT 18,

sex BOOLEAN

);

INSERT user VALUES(NULL,'A', '123',24, 1); //NULL:AUTO_INCREMENT

INSERT user VALUES(DEFAULT,'B','123',24, 1); //DEFAULT:AUTO_INCREMENT

INSERT user VALUES(DEFAULT,'C','123',24); //报错，缺少字段

INSERT user VALUES(DEFAULT,'D','123',DEFAULT, 1); //age字段记录DEFAULT默认值

INSERT user VALUES(DEFAULT,'E','123',46-2, 1); //字段可以是表达式，也可以是函数

INSERT user VALUES(DEFAULT,'Tammy','abc',28,2),(NUll,'Tommy',md5('abc'),30,1); //插入多条

插入记录2:与上一种的区别在于，此方法可以使用子查询(SubQuery),一次只能插入一条

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},...

例：

INSERT user SET username='Ben',password='456';

更新记录(单表更新)UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET

col_name={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}]...

[WHERE where_condition]

例：更新操作

UPDATE user SET age = age +5; //省略条件，表中所有记录的年龄加5

UPDATE user SET age = age - id,sex = 0; //多条记录修改

UPDATA user SET age = age + 10 WHERE id % 2 = 0;//加限制条件，偶数列年龄在原先基础上加10

删除操作(单表删除)DELETE

删除后，再次插入记录id号在原来记录上+1

DELETE FROM tbl_name [WHERE where_condition]

例：

DELETE FROM user WHERE id = 6; //删除第六条记录查询记录SELECT (占数据表操作的80%)

SELECT select_expr[,select_expr,...]

[

FROM table_references

[WHERE where_codition]

[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 VERSION();

SELECT NOW();

SELECT 35;

SELECT id,username FROM user; //从数据表中选取id,username两列，返回的结果中只包含这两列

SELECT username,id FROM user; //返回的结果中username列排在前面

SELECT FROM user;//表示所有列

SELECT user.id,user.sex FROM user; //列名称前跟上表名，用于查询多个数据表操作

查询表达式select_expr

每一个表达式表示想要的一列，必须有至少一个

多个列之间以英文逗号分隔

星号()表示所有列。tbl_name.可以表示命名表的所有列

查询表达式可以使用[ AS ] alias_name为其赋予别名

别名可用于GROUP BY,ORDER BY或HAVING子句

例：所谓别名

SELECT id AS userID,username AS userName FROM user; //AS关键字强烈建议写上

条件表达式WHERE

对记录进行过虑，如果没有指定WHERE子句，则显示所有记录

在WHERE表达式中，可以使用MySQL支持的函数或运算符

查询结果分组GROUP BY

[GROUP BY {col_name | position} [ASC | DESC],...]

ASC 默认升序

DESC 降序

例：

SELECT sex FROM user GROUP BY sex; //以性别为组查询

SELECT sex FROM user GROUP BY 1; //1表示SELECT中出现的第一个字段名称

分组条件HAVING

[HAVING where_condition]

例：

SELECT sex FROM user GROUP BY 1 HAVING age>20; //出错，HAVING后面的字段必须出现在SELECT中

SELECT sex,age FROM user GROUP BY 1 HAVING age>20;

//正确，HAVING后面跟聚合函数(max最大值,min最小值,平均值,计数,绝对值,求和等等)

对查询结果进行排序ORDER BY

[ORDER BY {col_name | expr | position} [ASC | DESC],...]

例：

SELECT FROM user ORDER BY id DESC; //按照id降序排列

SELECT FROM user ORDER BY age, id DESC; //按照 age升序，id降序 排序

限制查询结果返回的数量LIMIT

分页效果

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

例：

SELECT FROM user LIMIT 2; //返回两条记录

SELECT FROM user LIMIT 3,2; //从0计数，第三条起两条记录

SELECT FROOM user LIMIT 2,2; //第2条开始2条记录

SELECT * FROM user ORDER BY id DESC LIMIT 2,2; // id DESC不起作用

插入记录2与SELECT运用：

将查询结果插入到另一表中：

例:

CREATE TABLE tb1(

id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(32)

);

INSERT tb1(username) SELECT username FROM user WHERE age>=28;

//将user表中年龄大于28的usernamed插入到tb1表中

总结：

记录操作最重要的SELECT操作

子查询

定义和条件：

子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。

查询:这里的查询指所有SQL语句返回的结果

例如

SELECT FROM t1 WHERE col1 = (SELECT col2 FROM t2);

其中SELECT FROM t1,称为Outer Query / Outer Statement

SELECT col2 FROM t2,称为SubQuery

子查询指嵌套在查询内部，且必须始终出现在圆括号内。

子查询可以包含多个关键字或条件

如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等

子查询的外层查询可是SELECT、INSERT、UPDATE、SET或DO。

子查询结果

使用比校运算符的子查询

=、>、=、<=、<>、!=、<=>

语法结构

operand comparison_operator subquery

例：

查询商品平均价格

SELECT AVG(goods_price) FROM test_goods;

//AVG求平均值函数

SELECT ROUND(AVG(goods_price),3) FROM test_goods;

//ROUND四舍五入保留3位小数,结果为6326.500

//22个商品中哪些大于平均值

SELECT goods_id,goods_name,goods_price FROM test_goods WHERE goods_price >= 6326.500;

//6326.500上一个查询的结果

使用子查询的写法：

SELECT goods_id,goods_name,goods_price FROM test_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),3) FROM test_goods);

用ANY、SOME或ALL修饰的比较运算符

operand comparison_operator ANY(subquery)

operand comparison_operator SOME(subquery)

operand comparison_operator ALL(subquery)

如果不加修改，报错：Subquery returns more than 1 row;

ANY、SOME、ALL关键字返回的结果：

、>=: ANY:最小值 SOME:最小值 ALL:最大值

=: ANY:任意值 SOME:任意值 ALL:无

<>、!=: ANY:无 SOME:无 ALL:任意值

查询超级本类型商品的价格

SELECT goods_price FROM test_goods WHERE goods_cate = '超级本';

SELECT * FROM test_goods WHERE goods_price >= SOME(SELECT goods_price FROM test_goods WHERE goods_cate = '超级本');

使用[NOT] IN 子查询

语法结构

operand comparison_operator [NOT] IN (Subquery)

=ANY运算符与IN等效

!=ALL或<>ALL运算符与NOT IN等效

使用[NOT] EXISTS的子查询

如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE。

例：

CREATE TABLES IF NOT EXISTS test_goods_cates(

cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

cate_name VARCHAR(40) NOT NULL

);

//记录来源：test_goods中的分类

SELECT goods_cate FROM test_goods GROUP BY goods_cate;

INSERT ... SELECT

将查询结果写入数据表

INERT [INTO] tbl_name [(col_name,...)]

SELECT...

例：将查询结果写入到新的数据表中

INSERT test_goods_cates(cate_name) SELECT goods_cate FROM test_goods GROUP BY goods_cate;

多表更新

UPDATE table_references

SET col_name1={expr1|DEFAULT}

[,col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

例：更新test_goods中的goods_cate字段

UPDATE test_goods INNER JOIN test_goods_cates ON goods_cate = cate_name

SET goods_cate = cate_id;

SELECT * FROM test_goods\G;

//可以看到test_goods中的goods_cate全部更新成test_goods_cates中的cata_id值

CREATE...SELECT

创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name

[(create_definition),...]

select_statement

例：

CREATE TABLE test_goods_brands(

brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

brnd_name VARCHAR(40) NOT NULL

)

SELECT brand_name FROM test_goods GROUP BY brand_name;

SHOW COLUMNS FROM test_goods;

//结果显示test_goods表中数据更新了但是结构未更新

ALTER TABLE test_goods

CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

//将test_goods表中的结构更新成新创建的表中的结构。

事实外键

FROM子句中的子查询

语法结构

SELECT...FROM (Subquery) [AS] name ...

说明：

名称必选项，且子查询的列名称必须唯一

连接

MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作

语法结构：

table_references

{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}

table_references

ON conditional_expr

连接类型

INNER JOIN 内连接

在MySQL中，JOIN,CROSS JOIN和INNER JOIN是等价的

LEFT [OUTER] JOIN,左外连接

RIGHT [OUTER] JOIN,右外连接

内连接：显示左表与右表符合连接条件的记录

外连接：显示左表的全部记录及右表符合连接条件的记录

连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替

通常使用ON关键字来设置连接条件

使用WHERE关键字进行结果集记录的过滤

多表连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM test_goods AS g

INNER JOIN test_boods_cate AS c ON g.cate_id = c.cate_id,

INNER JOIN test_goods_brands AS b ON g.brand_id = b.brand_id;

外连接

A LEFT JOIN B join_condition

数据表B的结果集依赖数据表A

数据表A的结果集根据左连接条件依赖所有数据表(B表除外)

左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)

如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行

如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接着条件的记录后停止搜索更多的行。

例：

无限级表分类

一级分类 二级分类 三级分类 。。。

自身连接

同一个数据表对其自身进行连接

例：在同一个表里面查出子类和父类

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p

ON s.parent_id = p.type_id;

-uroot -proot

(MySQL自定义函数

SELECT NOW();

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%s秒')

MySQL支持的存储引擎

存储引擎：

MyISAM

InnoDB

Memory

CSV

Archive

相关知识点

1.并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性

两个用户同时登录，其中一个用户删除id=22的记录，另外一个用户则要读取id=22的记录，发生的结果可能是正在读取的用户报错而退出，也有可能得到一条与实际记录不符的信息

解决这类问题的经典方案是并发控制。

锁：

共享锁(读锁)：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。

排他锁(写锁)：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

锁颗粒：

表锁，是一种开销最小的锁策略

行锁，是一种开销最大的锁策略 支持最大并发

事务：事务用于保证数据库的完整性

用户A ----> 转账200元 ----> 用户B

实现步骤：

1.从A账户减掉200元(账户余额大于等于200)

2.在B账号上增加200元

事务的特性：

原子性(Atomicity)

一致性(Consistency)

隔离性(Isolation)

持久性(Durability)

外键：是保证数据一致性的策略

索引：是对数据表中一列或多列的值进行排序的一种结构

普通索引

唯一索引

全文索引

btree索引

hash索引

...

各种存储引擎的特点

MyISAM:

存储限制：256TB 事务安全：- 支持索引：支持 锁颗粒：表锁 数据压缩：支持 支持外键：-

InnoDB:

存储限制：64TB 事务安全：支持 支持索引：支持 锁颗粒：行锁 数据压缩：- 支持外键：支持

Memory:

存储限制：有 事务安全：- 支持索引：支持 锁颗粒：表锁 数据压缩：- 支持外键：-

Archive:

存储限制：无 事务安全：- 支持索引： 锁颗粒：行锁 数据压缩：支持 支持外键：-

CSV:

为每个表创建csv后缀文件

应用的最多的存储引擎：

MyISAM : 适用于事务处理不多的情况

InnoDB : 适用于事务处理比校多，需要有外键支持的情况。

设置存储引擎：

1.通过修改MySQL配置文件实现

default-storage-engine = engine_name

//engine_name高版本默认是InnoDB;

2.通过创建数据表命令实现

CREATE TABLE table_name(

...

) ENGINE = engine_name;

3.通过修改数据表命令实现

ALTER TABLE table_name ENGINE [=] engine_name;

MySQL管理工具：

phpMyAdmin

Navicat