mysql笔记 txt_MySQL必知必会--学习笔记.txt

最新推荐文章于 2022-08-03 16:10:03 发布

胡晓晴

最新推荐文章于 2022-08-03 16:10:03 发布

阅读量79

点赞数

文章标签： mysql笔记 txt

本文链接：https://blog.csdn.net/weixin_36304806/article/details/113285596

版权

#具体例子网页

https://www.cnblogs.com/alex3714/articles/5950372.html

http://www.cnblogs.com/wupeiqi/articles/5713323.html

=======================================SHOW====================================================

show columns from role;

desc role;

SHOW STATUS，用于显示广泛的服务器状态信息；

SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句；

SHOW GRANTS，用来显示授予用户(所有用户或特定用户)的安全权限；

SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。

==================================SELECT=============================================

DISTINCT关键字(除重)

SELECT DISTINCT role_id FROM role;

LIMIT 5指示MySQL返回不多于5行 (LIMIT 3,4 意思是从行3开始的4 行)

SELECT DISTINCT role_id FROM role LIMIT 5;

SELECT role_id,role_name,sex FROM role order by role_id DESC LIMIT 1;

#排序

SELECT role_id,role_name,sex FROM role order by role_id;

#下面两条语句执行结果一样

SELECT role_id,role_name,sex FROM role order by 1,2;

SELECT role_id,role_name,sex FROM role order by role_id,role_name;

#降序排序

SELECT role_id,role_name,sex FROM role order by role_id DESC;

SELECT role_id,role_name,sex FROM role order by role_id DESC, role_name;

=============================WHERE=========================================================

#使用WHERE子句

SELECT role_id,role_name FROM role WHERE role_id = 9437185;

操作符说明

= 等于

<> 不等于

!= 不等于

<= 小于等于

> 大于

>= 大于等于

BETWEEN 在指定的两个值之间

SELECT role_id,role_name FROM role WHERE role_id BETWEEN 9437185 AND 9437285;

#空值检查

SELECT role_id FROM role WHERE role_name IS NULL;

SELECT role_id,role_name FROM role WHERE role_id = 9437185 AND role_name = "莉诺卡娜";

SELECT role_id,role_name FROM role WHERE role_id = 9437185 or role_id = 9437285;

SELECT role_id,role_name FROM role WHERE (role_id = 9437185 or role_id = 9437285) AND role_name = "莉诺卡娜";

#IN操作符

SELECT role_id,role_name FROM role WHERE role_id IN (9437185,9437285) ORDER BY role_name;

SELECT role_id,role_name FROM role WHERE role_id NOT IN (9437185,9437285) ORDER BY role_name;

# LIKE操作符 (%通配符可以匹配任何东西，但有一个例外，即NULL。)

SELECT role_id,role_name FROM role WHERE role_id LIKE '%4371%';

SELECT role_id,role_name FROM role WHERE role_id LIKE '9%5';

#下划线(_)通配符下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

_ 总是匹配一个字符，不能多也不能少

==============================使用MySQL正则表达式============================================

SELECT role_id,role_name FROM role WHERE role_id REGEXP '^000$';

SELECT role_id,role_name FROM role WHERE role_id LIKE '000';

#以上两条SQL语句，是等同的

SELECT role_id,role_name FROM role WHERE role_id REGEXP '000';

SELECT role_id,role_name FROM role WHERE role_id REGEXP '.000';

SELECT role_id,role_name FROM role WHERE role_id REGEXP '1000|2000';

SELECT role_id,role_name FROM role WHERE role_id REGEXP '[123] 000' #[123]定义一组字符，它的意思是匹配1或2或3

#匹配1到5

SELECT role_id,role_name FROM role WHERE role_id REGEXP '[1-5] 000'

#为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.

SELECT role_id,role_name FROM role WHERE role_id REGEXP '\\.' ORDER BY role_name;

元字符说明

\\f 换页

\\n 换行

\\r 回车

\\t 制表

\\v 纵向制表

#匹配字符类

类说明

[:alnum:] 任意字母和数字(同[a-zA-Z0-9])

[:alpha:] 任意字符(同[a-zA-Z])

[:blank:] 空格和制表(同[\\t])

[:cntrl:] ASCII控制字符(ASCII 0到31和127)

[:digit:] 任意数字(同[0-9])

[:graph:] 与[:print:]相同，但不包括空格

[:lower:] 任意小写字母(同[a-z])

[:print:] 任意可打印字符

[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符

[:space:] 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v]) [:upper:] 任意大写字母(同[A-Z])

[:xdigit:] 任意十六进制数字(同[a-fA-F0-9])

#匹配多个实例

元字符说明

* 0个或多个匹配

+ 1个或多个匹配(等于{1,})

? 0个或1个匹配(等于{0,1})

{n} 指定数目的匹配

{n,} 不少于指定数目的匹配

{n,m} 匹配数目的范围(m不超过255)

#sticks?匹配stick和sticks(s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现)

SELECT role_id,role_name FROM role WHERE role_id REGEXP '\$[0-9] sticks?\$' ORDER BY role_name;

+---------------+

| role_id |

+---------------+

| TNT (1 stick) |

| TNT (1 sticks)|

+---------------+

#[[:digit:]]{4}匹配连在一起的任意4位数字

SELECT role_id,role_name FROM role WHERE role_id REGEXP '[[:digit:]]{4}' ORDER BY role_name;

#拼接(concatenate) 将值联结到一起构成单个值

SELECT Concat(role_id,'(',role_name,')') FROM role ORDER BY role_id LIMIT 3;

+-----------------------------------+

| Concat(role_id,'(',role_name,')') |

+-----------------------------------+

| 9437185(莉诺卡娜) |

| 9437186(安弗雷德) |

| 9437187(镇哥) |

+-----------------------------------+

#Trim函数 MySQL除了支持RTrim()(正如刚才所见，它去掉串右边的空格)，还支持LTrim()(去掉串左边的空格)以及 Trim()(去掉串左右两边的空格)

#别名用AS关键字赋予

SELECT Concat(RTrim(role_id),'(',RTrim(role_name),')') AS id_name FROM role ORDER BY role_id LIMIT 3;

+-----------------------+

| id_name |

+-----------------------+

| 9437185(莉诺卡娜) |

| 9437186(安弗雷德) |

| 9437187(镇哥) |

+-----------------------+

#执行算术计算

SELECT prod_id,

quantity,

item_price,

quantity*item_price AS expanded_price

FROM orderitems

WHERE order_num = 20005;

操作符说明

+ 加

- 减

* 乘

/ 除

======================SQL聚集函数================

函数说明

AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

SELECT AVG(prod_price) AS avg_price FROM customers ;

SELECT AVG(prod_price) AS avg_price FROM customers WHERE vend_id = 1003;

SELECT Sum(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

SELECT COUNT(*) AS num_items,

MIN(prod_price) AS price_min,

MAX(prod_price) AS price_max,

AVG(prod_price) AS price_avg,

FROM products;

#分组数据

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

WHERE过滤行，而HAVING过滤分组

SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;

SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;

#利用子查询进行过滤

#一个子查询

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'INT2');

#两个子查询

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id ='TNT2'));

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE order.cust_id = customers,cust_id) AS orders FROM customers ORDER BY cust_name;

语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id：

order.cust_id = customers,cust_id

#联结表 (主键和外键)

SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;

#内部联结 ( INNER JOIN语法 )

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

#联结多个表

SELECT cust_name,cust_contact FROM customers

WHERE cust_id IN (SELECT cust_idFROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id ='TNT2'));

#自联结

SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

#外部联结

============================创建组合查询==========================================

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集.

#使用UNION(去重复)

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5

UNION

SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

#使用UNION(全部输出)

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5

UNION ALL

SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

#使用UNION后，排序

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5

UNION

SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

ORDER BY vend_id,prod_price;

====================================================理解全文本搜索=================================

#创建一个表(MySQL根据子句FULLTEXT(note_text)的指示对它进行索引)

CREATE TABLE productnotes

(

note_id int NOT NULL AUTO_INCREMENT,

prod_id char(10) NOT NULL ,

note_date datetime NOT NULL,

note_text text NULL,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

) ENGINE=MyISAM;

#进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

#事实是刚才的搜索可以简单地用LIKE子句完成

SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';

#布尔文本搜索

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit' IN BOOLEAN MODE);

布尔操作符说明

+ 包含，词必须存在

- 排除，词必须不出现

> 包含，而且增加等级值

() 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)

~ 取消一个词的排序值

* 词尾的通配符

"" 定义一个短语(与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语)

如前所述，仅在MyISAM数据库引擎中支持全文本搜索。

========================================插入数据=================================================

CREATE TABLE productnotes

(

note_id int NOT NULL AUTO_INCREMENT,

prod_id char(10) NOT NULL ,

note_date datetime NOT NULL,

note_text text NULL,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

) ENGINE=MyISAM;

INSERT INTO productnotes VALUES('1','0001','2018-01-02','HLS'),('2','0002','2018-01-01','HC');

=====================================更新数据====================================================

#不要省略WHERE子句在使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行 (上天的节奏)

UPDATE customers SET cust_email = 'hls@163.com' WHERE cust_id = 1005;

#更新多个列数据

UPDATE customers SET cust_name = 'hls',cust_email = 'hls@163.com' WHERE cust_id = 1005;

#为了删除某个列的值，可设置它为NULL(假如表定义允许NULL值)。

UPDATE customers SET cust_email = NULL WHERE cust_id = 1005;

=======================================删除数据=================================================

#(上天的节奏) 如果省略WHERE子句，它将删除表中每个客户。

DELETE FROM customers WHERE cust_id = 10005;

1、如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据)

2、在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确

3、除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。

4、保证每个表都有主键

主键自增：

CREATE TABLE test

(

id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(15) NOT NULL

) AUTO_INCREMENT = 100;

可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。

#真实生成的建表

Create Table: CREATE TABLE `role` (

`role_inc_id` bigint(20) NOT NULL AUTO_INCREMENT,

`role_id` int(11) NOT NULL DEFAULT '0',

`role_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`avatar` tinyint(4) NOT NULL DEFAULT '0',

`sex` tinyint(4) NOT NULL DEFAULT '0',

`scene_id` int(11) NOT NULL DEFAULT '0',

`last_scene_id` int(11) NOT NULL DEFAULT '0',

`level` int(11) NOT NULL DEFAULT '0',

`professional` int(11) NOT NULL DEFAULT '0',

`create_time` bigint(20) NOT NULL DEFAULT '0',

`online_time` int(11) NOT NULL DEFAULT '0',

`is_online` tinyint(4) NOT NULL DEFAULT '0',

`last_save_time` bigint(20) NOT NULL DEFAULT '0',

`country` tinyint(4) NOT NULL DEFAULT '0',

`is_chongzhi` tinyint(4) NOT NULL DEFAULT '0',

`is_micro_pc` tinyint(4) NOT NULL DEFAULT '0',

`plat_spid` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`role_inc_id`),

KEY `role_name_INX` (`role_name`) USING BTREE,

KEY `ROLE_ID_INX` (`role_id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

#CHARACTER SET utf8 COLLATE utf8_bin

(字符集(CHARACTER SET)和校对集(COLLATE))

mysql>show collation;

#USING BTREE (索引结构)

#如果你仅想在一个表不存在时创建它，应该在表名后给出IFNOT EXISTS

==================================修改表结构===========================

为了使用ALTER TABLE更改表结构，必须给出下面的信息：

1、在ALTER TABLE之后给出要更改的表名(该表必须存在，否则将出错)

2、所做更改的列表

#给表添加一个列

ALTER TABLES vendors ADD vend_phone CHAR(20);

#删除刚刚添加的列

ALTER TABLES vendors DROP COLUMN vend_phone;

MySQL修改字段类型的命令是：

mysql> alter table 表名 modify column 字段名类型;

特别留意：

小心使用ALTER TABLE 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份(模式和数据的备份)。数据

库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失

该列中的所有数据

==============================删除表============================

DROP TABLE customers2;

==============================重命名表============================

#单表重命名

RENAME TABLE customers2 TO customers;

#对多个表重命名

RENAME TABLE customers1 TO customers1_backup,

customers2 TO customers2_backup,

customers3 TO customers3_backup;

=================================使用视图====================================

1、视图用CREATE VIEW语句来创建。

2、使用SHOW CREATE VIEW viewname；来查看创建视图的语句

3、用DROP删除视图，其语法为DROP VIEW viewname;。

4、更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创

建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

======================================使用存储过程======================================================

简单、安全、高性能

===========================================字符集和校对顺序==============================================================

show character set;

show variables like 'character%';

show variables like 'collation%';

======================================行数据库维护========================

#ANALYZE TABLE，用来检查表键是否正确。

mysql> check table role;

+-------------+-------+----------+----------+

+-------------+-------+----------+----------+

+-------------+-------+----------+----------+

#CHECK TABLE用来针对许多问题对表进行检查

mysql> analyze table role;

+-------------+---------+----------+----------+

+-------------+---------+----------+----------+

+-------------+---------+----------+----------+

胡晓晴

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql笔记 txt_MySQL必知必会--学习笔记.txt

#具体例子网页https://www.cnblogs.com/alex3714/articles/5950372.htmlhttp://www.cnblogs.com/wupeiqi/articles/5713323.html=======================================SHOW============================================...
复制链接

扫一扫