MySQL 入门

最新推荐文章于 2022-09-04 19:45:55 发布

qq_34515002

最新推荐文章于 2022-09-04 19:45:55 发布

阅读量549

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_34515002/article/details/80498898

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL基础

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。
MySQL 是最流行的关系型数据库管理系统之一， RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
MySQL 软件采用了双授权政策，分为社区版和商业版。

WINDOWS下安装MySQL

MSI安装

官网：https://www.mysql.com/

下载：

V5.5

安装：Typical,

安装目录：c:\program files\MySQL\MySQL Server5.5

\bin\MySQL...Config.exe,配置向导。

选择Standard Configuration,root密码：root,选择复选框；

启动服务：CMD: services.msc

MYSQL的配置选项

配置文件\my.ini

1、修改编码方式

[mysql]

defalut-character-set=utf-8

[mysql]

character-set-server=utf-8

启动关闭服务

net start mysql

net stop mysql

MySQL的登录与退出

1、登录

mysql -V

mysql -uroot -p -P3306 -h127.0.0.1

2、退出

exit;

quit;

\q;

修改MySQL提示符

1、mysql -uroot -proot --prompt 提示符

2、prompt 提示符

\D 完整日期

\d 当前数据库

\h 当前服务器名称

\u 当前用户

PROMPT \u@\h \d>

常用命令

显示当前服务器版本：SELECT VERSION();

显示当前日期时间：SELECT NOW();

显示当前用户：SELECT USER();

语句的规范

关键字与函数名称全部大写

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

SQL语句以分号结尾

数据库操作

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET utf8;

CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET gbk;

查看数据库列表

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]

SHOW DATABASES;

查看警告

SHOW WARNINGS;

查看创建数据库的指令

SHOW CREATE DATABASE db1;

修改数据库

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

ALTER DATABASE db2 CHARACTER SET= utf8;

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

数据类型

参考网页：http://www.runoob.com/mysql/mysql-data-types.html

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

整型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

浮点型

类型	大小	范围（有符号）	范围（无符号）	用途
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期时间型

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
NVARCHAR		变长字符串
TINYTEXT	0-255字节	短文本字符串
TEXT	0-65 535字节	长文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
ENUM('value1','value2',...)	1或2个字节，取决于枚举值得个数（最多65,535个值）	枚举
SET('value1','value2'...)	1、2、3、4或者8个字节，取决于set成员的数目（最多64个成员）	集合

二进制类型

类型	大小	用途
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
BLOB	0-65 535字节	二进制形式的长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name (

column_name data_type,

...

);

CREATE TABLE employee(

username varchar(20),

age TINYINT UNSIGNED,

salary FLOAT(8,2) UNSIGNED

);

打开数据库

USE database_name;

查看数据表列表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

查看数据表结构

SHOW COLUMNS FROM tbl_name;

记录的插入

INSERT [INTO] tbl_name [(col_name,...)] VALUES (val,...);

INSERT employee VALUES('Tom',25,7863.25);

INSERT employee(username,salary) VALUES('John',4500.69);

记录的查找

SELECT expr,... FROM tbl_name;

select * from employee;

空值与非空

NULL,字段值可以为空

NOT NULL，字段值禁止为空；

CREATE TABLE tb2(

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED NULL

);

insert tb2 VALUES('Tom',NULL);

insert tb2 VALUES(NULL,26);->报错；

自动编号

AUTO_INCREMENT

必须与主键组合使用

默认情况，起始值为1，增量为1

CREATE TABLE tb3(

id SMALLINT UNSIGNED AUTO_INCREMENT,

username VARCHAR(20) NOT NULL

);

-->报错：ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE TABLE tb3(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20) NOT NULL

);

insert tb3(username)values('Tom');

insert tb3(username)values('John');

insert tb3(username)values('Rose');

主键约束

PRIMARY KEY

每个数据表只能存在一个主键，可以是联合主键

保证记录唯一，且不为NULL

可以配合自增，也可不自增。

CREATE TABLE tb4(

id SMALLINT UNSIGNED PRIMARY KEY,

username VARCHAR(20) NOT NULL

);

insert tb4 values(5,'Tom');

insert tb4 values(22,'John');

唯一约束

UNIQUE KEY

记录唯一，可以为空值（NULL）

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

CREATE TABLE tb5(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20) NOT NULL UNIQUE KEY,

age TINYINT UNSIGNED

);

INSERT tb5(username,age) VALUES('Tom',25);

ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'

默认约束

DEFAULT

自动赋值

CREATE TABLE tb6(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20) NOT NULL UNIQUE KEY,

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

);

INSERT tb6(username) VALUES('Tom');

SELECT * FROM tb6;

约束

1、保证数据的完整性和一致性。

2、分为：表级约束和列级约束。

3、类型：

NOT NULL(非空约束)

PRIMARY KEY(主键约束）

UNIQUE KEY(唯一约束)

DEFAULT(默认约束)

FOREIGN KEY(外键约束)

外键约束FOREIGN KEY

实现一对一或一对多关系。

外键约束的要求：

1、数据表的存储引擎只能为InnoDB.

2、父表和子表必须使用相同的存储引擎，而且禁止使用临时表

3、外键列和参照列必须具有相似的数据类型。

其中数字的长度与是否有符号位必须相同；

字符串的长度则可以不同；

4、外键列和参照列必须创建索引。

如果外键列不存在索引，MySQL将自动创建索引

CREATE TABLE province(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

pname VARCHAR(20) NOT NULL

);

SHOW CREATE TABLE province;

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid BIGINT,

FOREIGN KEY(pid) REFERENCES province(id)

);

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid SMALLINT,

FOREIGN KEY(pid) REFERENCES province(id)

);

报错： ERROR 1005 (HY000): Can't create table 'db1.users' (errno: 150)

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid SMALLINT UNSIGNED,

FOREIGN KEY(pid) REFERENCES province(id)

);

查看表的索引

SHOW INDEXES FROM tb_name;

SHOW INDEXES FROM province\G;

SHOW INDEXES FROM users\G;

设置默认存储引擎

MySQL的配置文件

default-storage-engine=INNODB

外键约束的参照操作

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

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

如果使用该选项，必须保证子表列没有设置NOT NULL约束

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

4、NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同。

CREATE TABLE users1(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid SMALLINT UNSIGNED,

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

);

INSERT province(pname) values('A');

INSERT province(pname) values('B');

INSERT province(pname) values('C');

INSERT province(pname) values('D');

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

INSERT users1(username,pid) VALUES('Jhon',1);

INSERT users1(username,pid) VALUES('Rose',3);

DELETE FROM province WHERE id=3;

select * from users1;

表级约束与列级约束

列级约束：

对一个数据列建立的约束；可以在列定义时声明，也可以在列定义后声明。

表级约束：

对多个数据列建立的约束；只能在列定义后声明；

非空与默认值为列级约束，其他三个可以作为列级约束也可以为表级约束。

修改数据表

添加单列\添加表的字段

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

ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

SELECT * FROM users1;

ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;

SHOW COLUMNS FROM users1;

ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;

添加多列

ALTER TABLE tbl_name ADD [COLUMN]

(col_name column_definition ,...);

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name;

ALTER TABLE tbl_name DROP [COLUMN] col_name,DROP [COLUMN] col_name;

ALTER TABLE users1 DROP truename;

ALTER TABLE users1 DROP password,DROP age;

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...)

CREATE TABLE users2(

username VARCHAR(10) NOT NULL,

pid SMALLINT UNSIGNED

);

SHOW CREATE TABLE users2;

ALTER TABLE users2 ADD id SMALLINT UNSIGNED;

ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

SHOW COLUMNS FROM users2;

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ...);

ALTER TABLE users2 ADD UNIQUE (username);

SHOW COLUMNS FROM users2;

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name(col_name);

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES province(id);

SHOW CREATE TABLE users2;

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;

SHOW COLUMNS FROM users2;

ALTER TABLE users2 ALTER age SET DEFAULT 15;

SHOW COLUMNS FROM users2;

ALTER TABLE users2 ALTER age DROP DEFAULT;

SHOW COLUMNS FROM users2;

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

ALTER TABLE users2 DROP PRIMARY KEY;

SHOW COLUMNS FROM users2;

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;

ALTER TABLE users2 DROP INDEX username;

SHOW COLUMNS FROM users2;

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

SHOW CREATE TABLE users2;

ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1

查看索引的名字

SHOW INDEXES FROM tbl_name;

SHOW INDEXES FROM users2\G;

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

SHOW COLUMNS FROM users2;

ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL FIRST;

SHOW COLUMNS FROM users2;

修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST| AFTER col_name];

ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

SHOW COLUMNS FROM users2;

数据表更名

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 to new_tbl_name2...];

ALTER TABLE users2 RENAME users3 ;

SHOW TABLES;

RENAME TABLE users3 TO users2;

SHOW TABLES;

插入记录

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

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20) NOT NULL,

password VARCHAR(32) NOT NULL,

age TINYINT UNSIGNED NOT NULL DEFAULT 10,

sex BOOLEAN

);

INSERT users VALUES(NULL,'Tom','123',25,1);

INSERT users VALUES(NULL,'John','456',25,1);

INSERT users VALUES(DEFAULT,'TOM','456',25,1);

INSERT users VALUES(DEFAULT,'TOM','456',3*7+2,1);

INSERT users VALUES(DEFAULT,'TOM','456',DEFAULT,1);

INSERT users VALUES(DEFAULT,'TOM','456',DEFAULT,1)

,(DEFAULT,'Rose',md5('456'),DEFAULT,0)

,(DEFAULT,'Lily',md5('456'),DEFAULT,0);

SELECT * FROM users;

插入记录

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

与第一种方式的区别：此方法可以使用子查询；

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

CREATE TABLE test(

id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20)

);

INSERT test (username) SELECT username FROM users WHERE age>=30;

SELECT * FROM test;

更新记录（单表更新）

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}...][ WHERE where_condition]

UPDATE users set age = age+5;

UPDATE users set age = age-id,sex=0;

删除记录（单表删除）

DELETE FROM tbl_name [WHERE where_condition]

DELETE FROM users WHERE id = 6;

INSERT users VALUES(NULL,'111','222',33,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}]

]

子查询

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

例如：

SELECT * FROM t1 WHERE col1 in (SELECT col2 FROM t2);

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

SELECT col2 FROM t2 称为SubQuery

使用比较运算符的子查询

运算符：

=,>,<,>=,<=,<>,!=,<=>

语法结构

operand comparison_operator subquery

SELECT AVG(goods_price) avgPrice FROM tdb_goods;

SELECT ROUND(AVG(goods_price),2) avgPrice FROM tdb_goods;

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=
( SELECT ROUND(AVG(goods_price),2) avgPrice FROM tdb_goods);

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

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

ERROR 1242 (21000): Subquery returns more than 1 row

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

operand comparison_operator ANY (subquery);

operand comparison_operator SOME (subquery);

operand comparison_operator ALL (subquery);

关键字运算符	ANY\SOME	ALL
>,>=	最小值	最大值
<,<=	最大值	最小值
=	任意值
<>,!=		任意值

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

使用[NOT] IN的子查询

语法结构：

operand comparison_operator [NOT] IN(subQuery)

=ANY运算符与IN等效

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

使用[NOT] EXISTS 的子查询

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

CREATE TABLE IF NOT EXISTS tdb_goods_cates(

    cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    cate_name VARCHAR(40)

);

INSERT...SELECT

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

SELECT ...

SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

select * from tdb_goods_cates;

多表更新

UPDATE table_references

SET col_name1 = {expr1|DEFAULT}

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

[WHERE where_condition]

update tdb_goods INNER JOIN tdb_goods_cates on goods_cate = cate_name

SET goods_cate = cate_id;

select * from tdb_goods\G;

连接类型

INNER JOIN ，内连接；在MySQL中：JOIN,CROSS JOIN,INNER JOIN是等价的。

LEFT [OUTER] JOIN,左外连接

RIGHT [OUTER] JOIN,右外连接

CREATE...SELECT

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

CREATE TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

select_statement

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

select * from tdb_goods\G;

CREATE TABLE tdb_goods_brands(

brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

brand_name VARCHAR(40) NOT NULL

)

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

SELECT * FROM tdb_goods_brands;

select * from tdb_goods\G;

UPDATE tdb_goods INNER JOIN tdb_goods_brands on tdb_goods.brand_name = tdb_goods_brands.brand_name

SET tdb_goods.brand_name =tdb_goods_brands.brand_id;

ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

SHOW COLUMNS FROM tdb_goods;

连接JOIN

MySQL在SELECT,UPDATE,DELETE语句中可以使用JOIN.

table_reference

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

table_reference

ON conditional_expr

连接条件ON

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

通常使用ON设定连接条件，使用WHERE进行结果集记录的过滤。

内连接

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

   INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

   INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

外连接OUTER JOIN

左外连接LEFT OUTER JOIN

SELECT goods_id,goods_name,cate_name,brand_id,goods_price FROM tdb_goods AS g

LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id;

右外连接RIGHT OUTER JOIN

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

RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

无线级分类表设计

-- 无限分类的数据表设计

   CREATE TABLE tdb_goods_types(
     type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     type_name VARCHAR(20) NOT NULL,
     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

多表删除

DELETE tbl_name[.*][,tbl_name[.*]]...

FROM table_references

[WHERE where_condition]

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;

字符串函数

函数	功能
concat(s1, s2, ... , sn)	连接s1, s2, ..., sn 为一个字符串
concat_ws(separator,str1,str2,...)	使用指定的分隔符进行字符连接
insert(str, x, y, instr)	将字符串str从第x位置开始， y个字符长度的子字符串替换为字符串instr
FORMAT(X,D[,local])	数字格式化,以格式“#，###，###.##”的格式化数字X
lower(str)	将字符串str中所有的字符转换为小写
upper(str)	将字符串str中所有的字符转换为大写
left(str, x)	返回字符串str最左边的x个字符
right(str, y)	返回字符串str最右边的y个字符
lpad(str, n, pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
rpad(str, n, pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
LENGTH()	取得字符串长度
ltrim(str)	去掉str中最左边的空格
rtrim(str)	去掉str中最右边的空格
repeat(str, x)	返回str中重复出现x次的结果
[NOT] LIKE	模式匹配
replace(str, a, b)	将字符串str中的a更换为b
strcmp（s1, s2）	比较字符串s1, s2
trim(str)	去掉字符串str两边的空格
substring(str, x, y)	返回字符串str x位置开始y个字符长度的字符串

CONCAT('A','-','MySQL');
SELECT CONCAT(first_name,last_name) fullname FROM test;
SELECT CONCAT_WS('|','A','B','C');
--数字格式化，保留2位小数；
SELECT FORMAT(12560.75,2);
--小写
 SELECT lower('MySQL');
--大写
 SELECT UPPER('MySQL');
--取左2位字符串
SELECT LEFT('MySQL',2);

--取右2位字符串
SELECT RIGHT('MySQL',2);

--取得字符串长度
SELECT LENGTH('MySQL');
--去掉左空格
SELECT LTRIM('   MySQL   ');
--去掉右空格
SELECT RTRIM('   MySQL   ');
--去掉两端空格
SELECT TRIM('   MySQL   ');
SELECT TRIM(BOTH,'?','??My??SQL???');
SELECT TRIM(LEADING,'?','??My??SQL???');
SELECT TRIM(TRAILING,'?','??My??SQL???');
SELECT REPLACE('??My??SQL???','?','ooo');
--截取字符串
SELECT SUBSTRING('MySQL',1,2);
SELECT SUBSTRING('MySQL',-1);--获取最后1位；
--模式匹配
SELECT 'MySQL' LIKE 'M%';--%代表任意N个字符,_代表任意1个字符。
SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';

--TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)：

从源字符串str中去掉两端、前缀或后缀字符remstr并返回；

如果不指定remstr，则去掉str两端的空格；

不指定BOTH、LEADING、TRAILING ，则默认为 BOTH。

可参考网页：https://www.cnblogs.com/geaozhang/p/6739303.html

数值运算符与函数

名称	描述
CEIL()	进一取整
DIV	整数除法
FLOOR()	舍一取整
MOD	取余数（取模）
POWER()	幂运算
ROUND()	四舍五入
TRUNCATE()	数字截取

--进一取整
SELECT CEIL(3.01);
--整数除法
SELECT 3/4;
SELECT 3 DIV 4;
--舍一取整
SELECT FLOOR(3.99);
--取余数（取模）
SELECT 5%3;
SELECT 5 MOD 3;
--幂运算
SELECT POWER(3,3);
--四舍五入
SELECT ROUND(3.652,1);
SELECT ROUND(125.88,0);
--数字截取
SELECT TRUNCATE(125.88,2); 
SELECT TRUNCATE(125.88,0);
SELECT TRUNCATE(125.88,-1);

比较运算符与函数

名称	描述
[NOT] BETWEEN ... AND ...	[不]在范围内
[NOT] IN()	[不]在列出值范围内
IS [NOT] NULL	[不]为空

SELECT 15 BETWEEN 1 AND 20;
SELECT 25 BETWEEN 1 AND 20;
SELECT 10 IN (5,10,15,20);
SELECT 1 IN (5,10,15,20);
SELECT 1 IS NULL;

日期时间函数

CURDATE()或CURRENT_DATE()	当前的日期
CURTIME()或CURRENT_TIME()	当前的时间
DATE_ADD(date ,INTERVAL int keyword)	日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATEDIFF()	两个日期之间的差值
DATE_FORMAT(date,fmt)	依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword)	日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date)	date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)	date是一个月的第几天(1~31)
DAYOFYEAR(date)	date是一年的第几天(1~366)
DAYNAME(date)	date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt)	根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time)	time的小时值(0~23)
MINUTE(time)	time的分钟值(0~59)
MONTH(date)	date的月份值(1~12)
MONTHNAME(date)	date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW()	当前的日期和时间
QUARTER(date)	date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date)	日期date为一年中第几周(0~53)
YEAR(date)	日期date的年份(1000~9999)

SELECT NOW();
SELECT CURDATE();
SELECT DATE_ADD(CURDATE(),INTERVAL 365 DAY);
SELECT DATE_ADD(CURDATE(),INTERVAL -365 DAY);
SELECT DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
SELECT DATEDIFF('2013-3-12','2014-3-12');
SELECT DATE_FORMAT('2014-03-12','%m/%d/%Y');

系统信息函数

DATABASE()	返回当前数据库名
BENCHMARK(count,expr)	将表达式expr重复运行count次
CONNECTION_ID()	返回当前客户的连接ID
FOUND_ROWS()	返回最后一个SELECT查询进行检索的总行数
LAST_INSERT_ID()	最后插入记录的ID号；
USER()或SYSTEM_USER()	返回当前登陆用户名
VERSION()	返回MySQL服务器的版本

SELECT CONNECTION_ID();
SELECT DATABASE();
SELECT LAST_INSERT_ID();
SELECT USER();
SELECT VERSION();

CREATE TABLE test(
first_name varchar(20) NOT NULL,
last_name varchar(10) NOT NULL
);
ALTER  TABLE test ADD id SMALLINT PRIMARY KEY AUTO_INCREMENT FIRST;
DESC test;
INSERT test (first_name,last_name) values('11','22');
SELECT * FROM test;
SELECT LAST_INSERT_ID();

聚合函数

常用于GROUP BY从句的SELECT查询中

AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果

加密函数

AES_ENCRYPT(str,key)	返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key)	返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,key)	使用key作为密钥解密加密字符串str
ENCRYPT(str,salt)	使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
ENCODE(str,key)	使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
MD5()	计算字符串str的MD5校验和
PASSWORD(str)	返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。修改密码：SET PASSWORD = PASSWORD('123');
SHA()	计算字符串str的安全散列算法(SHA)校验和

可参考网页：https://blog.csdn.net/sugang_ximi/article/details/6664748

自定义函数

用户自定义函数（user-defined function,UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。

两个必要条件：参数、返回值

函数可以返回任意类型的值，同样可以接收这些类型的参数

CREATE FUNCTION function_name

RETURNS

{STRING|INTEGER|REAL|DECIMAL}

routine_body

函数体：

由合法的SQL语句构成；
可以是简单的SELECT或INSERT语句；
如果为复合结构则使用BEGIN...END语句；
复合结构可以包含声明，循环，控制结构；

创建不带参数的自定义函数

 SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时:%i分:%s秒');
 CREATE FUNCTION f1() RETURNS VARCHAR(30)
 RETURN  DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时:%i分:%s秒');

创建带有参数的自定义函数

 CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
 RETURNS FLOAT(10,2) UNSIGNED
 RETURN (num1+num2)/2;

SELECT f2(10,11);

创建具有复合结构函数体的自定义函数

DELIMITER //

CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
 INSERT test(username) VALUES(username);
 RETURN LAST_INSERT_ID();
END
//

SELECT addusers("Rose");

DELIMITER ;

SELECT adduser('Tom');

存储过程

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

优点：

增强SQL语句的功能和灵活性
实现较快的执行速度
减少网络流量

创建存储过程

CREATE

[DEFINER = {user|CURRENT_USER}]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic] routine_body

proc_parameter:

[IN|OUT|INOUT] param_name type

参数：

IN:表示该参数的值必须在调用存储过程时指定

OUT:表示该参数的值可以被存储过程改变，并且可以返回

INOUT,表示该参数在调用时指定，并且可以被改变和返回。

特性

COMMENT 'string'

|{CONTAINS SQL|NO SQL|READS SQL DATA | MODIFIES SQL DATA}

|SQL SECURITY{DEFINER|INVOKER}

COMMENT:注释

CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句

NO SQL:不包含SQL语句

READS SQL DATA:包含读数据的语句

MODIFIES SQL DATA:包含写数据的语句

SQL SECURITY{DEFINER|INVOKER}:指明谁有权限来执行

过程体：

过程体由合法的SQL语句构成；
过程体可以是任意SQL语句；
过程体如果为复合结构则使用BEGIN...END语句；
复合结构可以包含声明，循环控制结构；

创建不带参数的存储过程

CREATE PROCEDURE sp1() SELECT VERSION();

调用存储过程

CALL sp_name([parameter[,...]])

CALL sp_name[()]

call sp1;

call sp1();

创建带有IN类型参数的存储过程

DESC users;

 DELIMITER //
 CREATE PROCEDURE removeUserById (IN id INT UNSIGNED)
 BEGIN
 DELETE FROM users WHERE id= id;
 END
 //
DELIMITER ;
CALL removeUserById(3);
SELECT * FROM users;

修改存储过程

ALTER PROCEDURE sp_name [characteristic...]

COMMENT 'string'

|{CONTAINS SQL|NO SQL|READS SQL DATA | MODIFIES SQL DATA}

|SQL SECURITY{DEFINER|INVOKER}

注意：不能修改存储过程的过程体

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

INSERT INTO users (username,password,age,sex) VALUES('A',MD5('A'), FLOOR( 50 * RAND()), FLOOR( 2 * RAND()));

DELIMITER //

CREATE FUNCTION adduser2(username VARCHAR(20))

RETURNS INT UNSIGNED

BEGIN

INSERT INTO users (username,password,age,sex) VALUES(username ,MD5(username), FLOOR( 50 * RAND()), FLOOR( 2 * RAND()));
RETURN 1;

END

DELIMITER ;

SELECT adduser2('C');
SELECT adduser2('D');
SELECT adduser2('E');
SELECT adduser2('F');
SELECT adduser2('G');
SELECT adduser2('H');
SELECT adduser2('I');
SELECT adduser2('J');
SELECT adduser2('K');
SELECT adduser2('L');
SELECT adduser2('M');
SELECT adduser2('N');
SELECT adduser2('O');
SELECT adduser2('P');
SELECT adduser2('Q');
SELECT adduser2('R');
SELECT adduser2('S');
SELECT adduser2('T');
SELECT adduser2('U');
SELECT adduser2('V');

DELIMITER ;

--创建存储过程

DROP PROCEDURE removeUserById;

DELIMITER //

CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id=p_id;

END

DELIMITER ;

CALL removeUserById(22);

SELECT * FROM users;

创建带有IN和OUT类型参数的存储过程

DELIMITER //

CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id=p_id;

SELECT count(id) FROM users INTO userNums;

END

DELIMITER ;

SELECT COUNT(id) FROM users;

CALL removeUserAndReturnUserNums(27,@nums);

SELECT @nums;

SET @i=7;

创建带有多个OUT类型参数的存储过程

desc test;

select * from test;

INSERT test(username) values('A'),('B'),('C');

select row_count();

UPDATE test SET username= CONCAT(username,'--OOO') where id<=2;

select row_count();

--获取影响的行记录数

DELIMITER //

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED ,OUT userCounts SMALLINT UNSIGNED)

BEGIN

DELETE FROM users WHERE age=p_age;

SELECT ROW_COUNT() INTO deleteUsers;

SELECT count(id) FROM users INTO userCounts;

END

DELIMITER ;

select * from users;

select count(id) from users where age =48;

CALL removeUserByAgeAndReturnInfos(48,@a,@b);

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

存储过程实现的功能要复杂一些；而函数的针对性更强；

存储过程可以返回多个值；函数只能有一个返回值；

存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现；

存储引擎

SHOW TABLES;
SHOW CREATE TABLE users;

MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。

每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

并发控制

当多个连接记录进行修改时保证数据的一致性和完整性。

锁

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

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

锁颗粒

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

行锁，是一种开销最大的锁策略

事务

用于保证数据库的完整性

特性（ACID）

原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）

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

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

各种存储引擎的特点

特点	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	有	无
事物安全	-	支持	-	-
支持索引	支持	支持	支持	-
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持	-	-	支持
支持外键	-	支持	-	-

CSV:逗号分隔

BlackHole:黑洞引擎，写入的数据都会消失，一般用于做数据复制的中继。

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

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

修改存储引擎的方法：

修改MySQL配置文件（my.ini）实现:default-storage-engine = engine;

创建数据表命令实现：CREATE TABLE table_name(...)ENGINE = engine;

CREATE TABLE tp1(
s1 VARCHAR(10)
)ENGINE = MyISAM;
SHOW CREATE TABLE tp1;

通过修改数据表命令实现：ALTER TABLE table_name ENGINE [=] engine_name;

ALTER TABLE tp1 ENGINE = InnoDB;
SHOW CREATE TABLE tp1;

常用的管理工具

PHPMyAdmin

Navicat

MySQL Workbench

qq_34515002

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 入门

MySQL基础MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。 MySQL 是最流行的关系型数据库管理系统之一， RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。 MySQL 软件采用了双授权政策，分为社区版和商业版。WINDOWS下安装MySQLMS...
复制链接

扫一扫

专栏目录