MySQL 入门

于 2018-10-08 11:12:07 发布
阅读量608 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_34515002/article/details/80498898
版权

MySQL基础

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

WINDOWS下安装MySQL

  1. 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

 

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

整型

 

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

 

浮点型

 

类型大小范围（有符号）范围（无符号）用途
FLOAT4 字节(-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)单精度
浮点数值
DOUBLE8 字节(-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的值小数值

日期时间型

 

 

类型大小
(字节)		范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4

1970-01-01 00:00:00/2038

结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

YYYYMMDD HHMMSS混合日期和时间值，时间戳

字符串类型

 

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

 

二进制类型

 

 

 

类型大小用途
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
BLOB0-65 535字节二进制形式的长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
LONGBLOB0-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);

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\SOMEALL
>,>=最小值最大值
<,<=最大值最小值
=任意值 
<>,!= 任意值


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.youkuaiyun.com/sugang_ximi/article/details/6664748

自定义函数

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

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

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

CREATE FUNCTION function_name

RETURNS

{STRING|INTEGER|REAL|DECIMAL}

routine_body

函数体：

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

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

 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语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

优点：

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

创建存储过程

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值